PCB MS SQL 标量函数与表值函数(CLR) 实现文件与目录操作
一.C#写SQL SERVER(CLR)实现文件操作
标量函数: 文件移动 ,复制,检测文件存在,写入新文件文本，读取文本，创建目录，删除目录，检测目录是否存在
/// <summary> /// 将现有文件复制到新文件。允许覆盖同名的文件。 /// </summary> /// <param name="sourceFileName">要复制的文件</param> /// <param name="destFileName">目标文件的名称。不能是目录。</param> /// <param name="overwrite">如果可以覆盖目标文件，则为 true；否则为 false。</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean FileCopy (string sourceFileName, string destFileName, bool overwrite) { try { File.Copy (sourceFileName, destFileName, overwrite); return File.Exists (destFileName); } catch (Exception) { return false; } return false; } /// <summary> /// 将指定文件移到新位置，并提供指定新文件名的选项。 /// </summary> /// <param name="sourceFileName">要复制的文件</param> /// <param name="destFileName">目标文件的名称。不能是目录。</param> /// <param name="overwrite">如果可以覆盖目标文件，则为 true；否则为 false。</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean FileMove (string sourceFileName, string destFileName, bool overwrite) { try { bool isExists = File.Exists (destFileName); if (isExists && overwrite) File.Delete (destFileName); if (isExists && !overwrite) return false; File.Move (sourceFileName, destFileName); return File.Exists (destFileName); } catch (Exception) { return false; } return false; } /// <summary> /// 确定指定的文件是否存在。 /// </summary> /// <param name="FilePath">文件路径</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static SqlBoolean FileExists (string FilePath) { try { return File.Exists (FilePath); } catch (Exception) { return false; } return false; } /// <summary> /// 创建一个新文件，在其中写入指定的字符串，然后关闭文件。如果目标文件已存在，则覆盖该文件。 /// </summary> /// <param name="FilePath">文件路径</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static bool FileWriteText (string FilePath, string Contents) { try { File.WriteAllText (FilePath, Contents); return File.Exists (FilePath); } catch (Exception) { return false; } return false; } /// <summary> /// 读取文本 /// </summary> /// <param name="FilePath">文件路径</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static string FileReadText (string FilePath) { try { return File.ReadAllText (FilePath); } catch (Exception) { return ""; } return ""; } /// <summary> /// 创建目录 /// </summary> /// <param name="FilePath">文件路径</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static bool DirectoryCreateDirectory (string DirPath) { try { Directory.CreateDirectory (DirPath); return Directory.Exists (DirPath); } catch (Exception) { return false; } return false; } /// <summary> /// 删除目录 /// </summary> /// <param name="FilePath">文件路径</param> /// <param name="recursive">是否删除所有子目录与文件</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static bool DirectoryDelete (string DirPath, bool recursive) { try { Directory.Delete (DirPath, recursive); return !Directory.Exists (DirPath); } catch (Exception) { return false; } return false; } /// <summary> /// 目录是否存在 /// </summary> /// <param name="FilePath">文件路径</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static bool DirectoryExists (string DirPath) { try { return Directory.Exists (DirPath); } catch (Exception) { return false; } return false; }
表值函数:读取文本，获取文件信息，获取子目录清单
/// <summary> /// 表值函数 ///--属性 --说明 ///--DataAccess --指示该函数是否涉及访问存储在SQL Server的数据 ///--FillRowMethodName --在同一个类的方法的名称作为表值函数(TVF)，这个参数在表值函数中才会用到，用于指定表值函数的数据填充方法 ///--IsDeterministic --指示用户定义的函数是否是确定性的 ///--IsPrecise --指示函数是否涉及不精确计算，如浮点运算 ///--Name --函数在SQL Server中注册时使用的函数的名称 ///--SystemDataAccess --指示该函数是否需要访问存储在系统目录或SQL Server虚拟系统表中的数据 ///--TableDefinition --如果方法作为表值函数(TVF)，则为一个字符串，该字符串表示表结构的定义 /// </summary> public partial class SQLfunction { /// <summary> /// SQL Server 读取文本转为表 /// </summary> /// <param name="separator"></param> /// <param name="pendingString"></param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "FileReadText2Table", FillRowMethodName = "SqlSplit_FillRow", TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")] public static IEnumerable FileReadText2Table(string path) { string[] strs = { }; strs = File.ReadAllLines(path); List<ResultData> resultDataList = new List<ResultData>(); for (int i = 0; i < strs.Length; i++) { resultDataList.Add(new ResultData(i + 1, strs[i])); } return resultDataList; } /// <summary> /// SQL Server 文件信息获取 /// </summary> /// <param name="path"></param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "FileInfo2Table", FillRowMethodName = "SqlKeyValue_FillRow", TableDefinition = "SerialNumber int,StringKey nvarchar(1024),StringValue nvarchar(1024)")] public static IEnumerable FileInfo2Table(string path) { List<ResultKeyValueData> resultDataList = new List<ResultKeyValueData>(); FileInfo fileInfo = new FileInfo(path); resultDataList.Add(new ResultKeyValueData(1, "FullName", fileInfo.FullName)); resultDataList.Add(new ResultKeyValueData(2, "DirectoryName", fileInfo.DirectoryName)); resultDataList.Add(new ResultKeyValueData(3, "Name", Path.GetFileNameWithoutExtension(fileInfo.FullName))); resultDataList.Add(new ResultKeyValueData(4, "Extension", fileInfo.Extension)); resultDataList.Add(new ResultKeyValueData(5, "IsReadOnly", fileInfo.IsReadOnly.ToString())); resultDataList.Add(new ResultKeyValueData(6, "CreationTime", fileInfo.CreationTime.ToString())); resultDataList.Add(new ResultKeyValueData(7, "LastAccessTime", fileInfo.LastAccessTime.ToString())); resultDataList.Add(new ResultKeyValueData(8, "LastWriteTime", fileInfo.LastWriteTime.ToString())); resultDataList.Add(new ResultKeyValueData(9, "Length", fileInfo.Length.ToString())); resultDataList.Add(new ResultKeyValueData(10, "Attributes", fileInfo.Attributes.ToString())); return resultDataList; } /// <summary> /// SQL Server 获取目录--子目录清单 /// </summary> /// <param name="path"></param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "DirectoryGetFiles", FillRowMethodName = "SqlSplit_FillRow", TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")] public static IEnumerable DirectoryGetFiles(string path) { string[] strs = { }; strs = Directory.GetFiles(path); List<ResultData> resultDataList = new List<ResultData>(); for (int i = 0; i < strs.Length; i++) { resultDataList.Add(new ResultData(i + 1, strs[i])); } return resultDataList; } /// <summary> /// SQL Server 获取目录--文件清单 /// </summary> /// <param name="path"></param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "DirectoryGetDirectories", FillRowMethodName = "SqlSplit_FillRow", TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")] public static IEnumerable DirectoryGetDirectories(string path) { string[] strs = { }; IntPtr admin_token = IntPtr.Zero; if (WinLogonHelper.LogonUser(ref admin_token) != 0) { using (WindowsIdentity wid_admin = new WindowsIdentity(admin_token)) { using (WindowsImpersonationContext wic = wid_admin.Impersonate()) { strs = Directory.GetDirectories(path); } } } List<ResultData> resultDataList = new List<ResultData>(); for (int i = 0; i < strs.Length; i++) { resultDataList.Add(new ResultData(i + 1, strs[i])); } return resultDataList; } } /// <summary> /// 表值函数 ///--属性 --说明 ///--DataAccess --指示该函数是否涉及访问存储在SQL Server的数据 ///--FillRowMethodName --在同一个类的方法的名称作为表值函数(TVF)，这个参数在表值函数中才会用到，用于指定表值函数的数据填充方法 ///--IsDeterministic --指示用户定义的函数是否是确定性的 ///--IsPrecise --指示函数是否涉及不精确计算，如浮点运算 ///--Name --函数在SQL Server中注册时使用的函数的名称 ///--SystemDataAccess --指示该函数是否需要访问存储在系统目录或SQL Server虚拟系统表中的数据 ///--TableDefinition --如果方法作为表值函数(TVF)，则为一个字符串，该字符串表示表结构的定义 /// </summary> public partial class SQLfunction { /// <summary> /// SQL Server 字符串分割方法 /// </summary> /// <param name="separator"></param> /// <param name="pendingString"></param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction( DataAccess = DataAccessKind.Read, IsDeterministic = true, Name = "SqlSplit", FillRowMethodName = "SqlSplit_FillRow", TableDefinition = "SerialNumber int,StringValue nvarchar(1024)")] public static IEnumerable SqlSplit(SqlString separator, SqlString pendingString) { string _separator = string.Empty; string _pendingString = string.Empty; if (pendingString.IsNull) return null; _pendingString = pendingString.ToString(); if (string.IsNullOrEmpty(_pendingString)) return null; _separator = separator.IsNull ? "," : separator.ToString(); _separator = string.IsNullOrEmpty(_separator) ? "," : _separator; string[] strs = _pendingString.Split(new string[] { _separator }, StringSplitOptions.RemoveEmptyEntries); List<ResultData> resultDataList = new List<ResultData>(); for (int i = 0; i < strs.Length; i++) { resultDataList.Add(new ResultData(i + 1, strs[i])); } return resultDataList; } #region 表值变量 Id,Value /// <summary> /// 填充数据方法 /// </summary> /// <param name="obj"></param> /// <param name="serialNumber"></param> /// <param name="stringValue"></param> public static void SqlSplit_FillRow(Object obj, out SqlInt32 SerialNumber, out SqlString StringValue) { ResultData resultData = (ResultData)obj; SerialNumber = resultData.SerialNumber; StringValue = resultData.StringValue; } /// <summary> /// 定义返回类型 /// </summary> public class ResultData { /// <summary> /// 序号，即行号 /// </summary> public SqlInt32 SerialNumber { get; set; } /// <summary> /// 分割后的每个子字符串 /// </summary> public SqlString StringValue { get; set; } public ResultData(SqlInt32 serialNumber, SqlString stringValue) { SerialNumber = serialNumber; StringValue = stringValue; } } #endregion #region 表值变量 ID,Key,Value /// <summary> /// 填充数据方法 /// </summary> /// <param name="obj"></param> /// <param name="serialNumber"></param> /// <param name="stringValue"></param> public static void SqlKeyValue_FillRow(Object obj, out SqlInt32 SerialNumber, out SqlString StringKey, out SqlString StringValue) { ResultKeyValueData resultData = (ResultKeyValueData)obj; SerialNumber = resultData.SerialNumber; StringKey = resultData.StringKey; StringValue = resultData.StringValue; } /// <summary> /// 定义返回类型 /// </summary> public class ResultKeyValueData { /// <summary> /// 序号，即行号 /// </summary> public SqlInt32 SerialNumber { get; set; } /// <summary> /// 键 /// </summary> public SqlString StringKey { get; set; } /// <summary> /// 值 /// </summary> public SqlString StringValue { get; set; } public ResultKeyValueData(SqlInt32 serialNumber, SqlString stringKey, SqlString stringValue) { SerialNumber = serialNumber; StringKey = stringKey; StringValue = stringValue; } } #endregion }
二.SQL服务器CLR配置（允许SQL调用.net程序）
sp_configure 'show advanced options', 1; RECONFIGURE WITH override GO sp_configure 'clr enabled', 1; RECONFIGURE WITH override GO Sp_changedbowner 'sa',true --sa改为当前登入用户名 alter database [dbname] set trustworthy on --bbname 改为自己的库名
三.注册 CLR 程序集
create ASSEMBLY SQLfunctionAssembly FROM 'D:\SQLClr.dll' --改为自己C#写的dll路径填写 WITH PERMISSION_SET = UNSAFE;
创建的.net程序集数据会写入下表：
select * from sys.assemblies select * from sys.assembly_files
四.创建标量函数与表值函数(分别2类函数举例）
1.标量函数----文件复制
CREATE FUNCTION [dbo].[FileCopy](@sourceFileName [nvarchar](max), @destFileName [nvarchar](max), @overwrite [bit]) RETURNS [bit] WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[FileCopy]
2.表值函数----获取文件信息
CREATE FUNCTION [dbo].[FileInfo2Table](@path [nvarchar](max)) RETURNS TABLE ( [SerialNumber] [int] NULL, [StringKey] [nvarchar](max) NULL, [StringValue] [nvarchar](max) NULL ) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLfunctionAssembly].[SQLClr.SQLfunction].[FileInfo2Table]
五.测试文件操作函数
测试3个函数
说明一下：D:\features文件是指服务器上的D盘features文件，而不是客户端的文件哦.
DECLARE @file VARCHAR(MAX) SET @file = 'D:\features'
--1.读取文件属性到Table select * from dbo.FileInfo2Table(@file)
--2.读取文本到Table select * from dbo.FileReadText2Table(@file)
-- 3.读取文本 select dbo.FileReadText(@file)
测试结果：
六.SQL SERVER访问共享目录方法
采用SQL SERVER操作文件是不允许操作局域网中的共享文件的，若想实现的话需Windows模拟域帐号登入，另一篇文章有讲到的。PCB 工程系统 模拟windows域帐号登入
以文件复制为例代码如下：
/// <summary> /// 读取文本 /// </summary> /// <param name="FilePath">文件路径</param> /// <returns></returns> [Microsoft.SqlServer.Server.SqlFunction] public static string FileReadText (string FilePath) { try { IntPtr admin_token = IntPtr.Zero; if (WinLogonHelper.LogonUser (ref admin_token) != 0) { using (WindowsIdentity wid_admin = new WindowsIdentity (admin_token)) { using (WindowsImpersonationContext wic = wid_admin.Impersonate ()) { return File.ReadAllText (FilePath); } } } } catch (Exception) { return ""; } return ""; } public class WinLogonHelper { /// <summary> /// 模拟windows登录域 /// </summary> [DllImport ("advapi32.DLL", SetLastError = true)] public static extern int LogonUser (string lpszUsername, string lpszDomain, string lpszPassword, int dwLogonType, int dwLogonProvider, ref IntPtr phToken); public static int LogonUser (ref IntPtr phToken) { return WinLogonHelper.LogonUser ("用户名", "域名", "密码", 2, 0, ref phToken); } }
作者：pcbren 微信号:yadnfku QQ号: 254566449
QQ交流群：
博客地址：https://www.cnblogs.com/pcbren/
声明：本博客原创文字只代表本人工作中在某一时间内总结的观点或结论，与本人所在单位没有直接利益关系。非商业，未授权，贴子请以现状保留，转载时必须保留此段声明，且在文章页面明显位置给出原文连接。
如果大家感觉我的博文对大家有帮助，请推荐支持一把。
