SQL Server File Operation
Reading and writing files are basic requirements for importing/exporting data through SQL server jobs. The xp_cmdshell stored procedure is often used but not a lot of developers and DBA are comfortable coding with it. The syntax makes the code often ugly, hard to develop and expensive to maintain due to the lack of good documentation, flexibility and understanding.
Some DBA don't recommend and even ban all use of the procedure xp_cmdshell from their environment due to security issues.
Eval SQL.NET allows you to use C# features and objects such as FileInfo and DirectoryInfo directly in T-SQL and improves code readability. It's the safest alternative to replace xp_cmdshell to access external resources by letting impersonate the current user context.
It's safe and easy to use.
-- REQUIRE EXTERNAL_ACCESS permission DECLARE @sqlnet SQLNET = SQLNET::New(' string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); var dir = new DirectoryInfo(path); return dir.GetFiles("*.*").Select(x => x.FullName).OrderBy(x => x).ToList();') .Impersonate() -- SELECT * FROM DesktopFiles ORDER BY File.Fullname EXEC dbo.SQLNET_EvalResultSet @sqlnet
SQL File Operation - Better flexibility
You need to perform file operations but SQL xp_cmdshell limits you.
- Return multiple column result back
- Passing parameter easier
Eval SQL.NET lets you use C# language and makes file operations very easy to perform.
-- REQUIRE EXTERNAL_ACCESS permission DECLARE @FileInfo TABLE ( FilePath VARCHAR(255) , FileContent VARCHAR(MAX) ) DECLARE @sqlnet SQLNET = SQLNET::New(' string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); var dir = new DirectoryInfo(path); return dir.GetFiles("*.*").Select(x => new Tuple<string, string>( x.FullName, File.ReadAllText(x.FullName))) .OrderBy(x => x).ToList();').Impersonate() -- SELECT Path, FileText FROM DesktopFiles ORDER BY File.Fullname INSERT INTO @FileInfo EXEC dbo.SQLNET_EvalResultSet @sqlnet SELECT * FROM @FileInfo
SQL File Operation - Better maintainability
You need to develop a file operation but you're afraid your code will be very hard to maintain.
Eval SQL.NET improves the maintainability by letting you use well known and well documented C# objects such as DirectoryInfo and FileInfo.
-- REQUIRE EXTERNAL_ACCESS permission -- BACKUP all ".txt" files created before 3 days ago -- RETURN the number of files affected SELECT SQLNET::New(' string path = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); string backupPath = Path.Combine(path, "Backup_" + DateTime.Now.ToString("yyyyMMdd")); DirectoryInfo desktopDirectory = new DirectoryInfo(path); DirectoryInfo backupDirectory = new DirectoryInfo(backupPath); // ENSURE the directory exists backupDirectory.Create(); // GET file to backup var backupFiles = desktopDirectory.GetFiles("*.txt") .Where(x => x.CreationTime < DateTime.Now.AddDays(-3)) .ToList(); // COPY files to backup directory backupFiles.ForEach(x => x.CopyTo(x.FullName.Replace(path, backupPath))); // RETURN the number of affected files return backupFiles.Count; ').Impersonate().EvalReadAccessInt() as Result
SQL File Operation - Better readability
You need to perform file operations but the code soon becomes hard to understand and to change.
Eval SQL.NET improves the readability by letting you use well known C# objects that wrap logic such as DirectoryInfo and FileInfo.
SQL File Operation - Better security
You need to perform file operations but you don't want to enable xp_cmdshell in your SQL Server.
Eval SQL.NET lets you perform file operations and change the security context to impersonate the credential of the one who runs the T-SQL statements.
Eval SQL.NET grants you easy access to external resources through the C# language in T-SQL jobs and objects.
It's a safe SQL alternative to xp_cmdshell and offers multiple advantages:
- Improve Flexibility: Access to C# Language in SQL
- Improve Maintainability: FileInfo and DirectoryInfo are well-known/documented objects
- Improve Readability: C# is without a doubt easier to read than T-SQL for complex code
- Improve Security: Impersonate user context