一.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 = ; i < strs.Length; i++)
{
resultDataList.Add(new ResultData(i + , 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(, "FullName", fileInfo.FullName));
resultDataList.Add(new ResultKeyValueData(, "DirectoryName", fileInfo.DirectoryName));
resultDataList.Add(new ResultKeyValueData(, "Name", Path.GetFileNameWithoutExtension(fileInfo.FullName)));
resultDataList.Add(new ResultKeyValueData(, "Extension", fileInfo.Extension));
resultDataList.Add(new ResultKeyValueData(, "IsReadOnly", fileInfo.IsReadOnly.ToString()));
resultDataList.Add(new ResultKeyValueData(, "CreationTime", fileInfo.CreationTime.ToString()));
resultDataList.Add(new ResultKeyValueData(, "LastAccessTime", fileInfo.LastAccessTime.ToString()));
resultDataList.Add(new ResultKeyValueData(, "LastWriteTime", fileInfo.LastWriteTime.ToString()));
resultDataList.Add(new ResultKeyValueData(, "Length", fileInfo.Length.ToString()));
resultDataList.Add(new ResultKeyValueData(, "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 = ; i < strs.Length; i++)
{
resultDataList.Add(new ResultData(i + , 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) != )
{
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 = ; i < strs.Length; i++)
{
resultDataList.Add(new ResultData(i + , 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 = ; i < strs.Length; i++)
{
resultDataList.Add(new ResultData(i + , 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) != ) {
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 ("用户名", "域名", "密码", , , ref phToken);
}
}