1.Sqlserver数据库中保存二进制的字段[varbinary](max),建一个表
CREATE TABLE [dbo].[FileDemoModel](
[ID] [int] NULL,
[demoFileName] [varchar](100) NULL,
[demoFile] [varbinary](max) NULL
)
2.创建一个测试Demo
3.创建表对应的model
public class FileDemoModel
{
public Nullable<int> ID { get; set; }
public string demoFileName{ get; set; }
public byte[] demoFile { get; set; }
}
4.SQLHelper
/// <summary>
/// 执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql)
{
try
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
int result = cmd.ExecuteNonQuery();
conn.Close();
return result;
}
catch (Exception ex)
{
return -1;
}
}
/// <summary>
/// 返回一个数据集的查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static SqlDataReader GetReader(string sql)
{
try
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
//MessageBox.Show(ex.ToString());
throw;
}
}
#region 执行带参数的SQL语句
public static int Update(string sql, SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
cmd.Parameters.AddRange(parameters);//添加参数
int result = cmd.ExecuteNonQuery();
return result;
}
catch (Exception ex)
{
//写入日志...
throw ex;
}
finally
{
conn.Close();
}
}
#endregion
5.数据访问
public class FileDemoServer
{
#region 添加实体
public int Insert(FileDemoModel model)
{
try
{
StringBuilder sbSqlParent = new StringBuilder();
sbSqlParent.Append("Insert into FileDemoModel(ID,demoFileName,demoFile)VALUES(@ID,@demoFileName,@demoFile)");
//执行并返回
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@ID",model.ID),
new SqlParameter("@demoFileName",model.demoFileName),
new SqlParameter("@demoFile",model.demoFile),
};
return SQLHelper.Update(sbSqlParent.ToString(), param);
}
catch (Exception)
{
throw;
}
}
#endregion
#region 获取实体
public FileDemoModel GetModel(FileDemoModel model)
{
try
{
StringBuilder sbSqlParent = new StringBuilder();
sbSqlParent.Append($"select * from FileDemoModel where ID ={model.ID} ");
SqlDataReader objReader = SQLHelper.GetReader(sbSqlParent.ToString());
FileDemoModel p = new FileDemoModel();
while (objReader.Read())
{
p.ID = Convert.ToInt32(objReader["ID"]);
p.demoFileName = objReader["demoFileName"].ToString();
p.demoFile = objReader["demoFile"] as byte[];
}
objReader.Close();
return p;
}
catch (Exception ex )
{
throw;
}
}
#endregion
}
6.最上层的调用
[TestClass]
public class FileDemo
{
#region 上传
[TestMethod]
public void UpLoadFile()
{
String filePath = @"C:\Users\Liu_N\Desktop\LN_Demo\LN.Application.Test\LN.Application.UnitTest\bin\Debug\1Test.dll";
byte[] File2Bytes = FileBinaryConvertHelper.File2Bytes(filePath);
FileDemoModel model = new FileDemoModel
{
ID = 1,
demoFileName = "Test.dll",
demoFile = File2Bytes
};
FileDemoServer fileDemoServer = new FileDemoServer();
int res = fileDemoServer.Insert(model);
}
#endregion
#region 下载
[TestMethod]
public void DownloadFile()
{
String filePath = @"C:\Users\Liu_N\Desktop\LN_Demo\LN.Application.Test\LN.Application.UnitTest\bin\Debug\1Test.dll";
FileDemoServer fileDemoServer = new FileDemoServer();
//=>获取实体
FileDemoModel model = fileDemoServer.GetModel(new FileDemoModel { ID=1});
//=>转化
FileBinaryConvertHelper.Bytes2File(model.demoFile, filePath);
}
#endregion
}
7.工具类:文件与二进制流间的转换
/// <summary>
/// 工具类:文件与二进制流间的转换
/// </summary>
public class FileBinaryConvertHelper
{
/// <summary>
/// 将文件转换为byte数组
/// </summary>
/// <param name="path">文件地址</param>
/// <returns>转换后的byte数组</returns>
public static byte[] File2Bytes(string path)
{
if (!System.IO.File.Exists(path))
{
return new byte[0];
}
FileInfo fi = new FileInfo(path);
byte[] buff = new byte[fi.Length];
FileStream fs = fi.OpenRead();
fs.Read(buff, 0, Convert.ToInt32(fs.Length));
fs.Close();
return buff;
}
/// <summary>
/// 将byte数组转换为文件并保存到指定地址
/// </summary>
/// <param name="buff">byte数组</param>
/// <param name="savepath">保存地址</param>
public static void Bytes2File(byte[] buff, string savepath)
{
if (System.IO.File.Exists(savepath))
{
System.IO.File.Delete(savepath);
}
FileStream fs = new FileStream(savepath, FileMode.CreateNew);
BinaryWriter bw = new BinaryWriter(fs);
bw.Write(buff, 0, buff.Length);
bw.Close();
fs.Close();
}
}
代码如下:
https://pan.baidu.com/s/12wUNAw8Ri-MU_EFo78y1lA
vjxd