根据数据库帮助类采用事务插入图片到sql server数据库中

我们定义数据库为image类型,然后读取图片为字符流,再保存到数据库中,首先我们定义一个读取图片的公共类,此公共类以后会用到,所以可以建立相应的帮助类

 public static byte[] ReadFile(string sPath)
{
//Initialize byte array with a null value initially.
byte[] data = null; //Use FileInfo object to get file size.
FileInfo fInfo = new FileInfo(sPath);
long numBytes = fInfo.Length; //Open FileStream to read file
FileStream fStream = new FileStream(sPath, FileMode.Open, FileAccess.Read); //Use BinaryReader to read file stream into byte array.
BinaryReader br = new BinaryReader(fStream); //When you use BinaryReader, you need to supply number of bytes to read from file.
//In this case we want to read entire file. So supplying total number of bytes.
data = br.ReadBytes((int)numBytes);
return data;
}

  再定义一下插入图片的语句类

    /// <summary>
/// 保存图片数据库语句
/// WZW
/// 2018-10-09 17:11
/// </summary>
/// <param name="ImageData"></param>
/// <param name="BUitrasoundID"></param>
/// <param name="currentid"></param>
/// <param name="parameter"></param>
/// <returns></returns>
private StringBuilder SaveImg(byte[] ImageData, string BUitrasoundID,int currentid,out SqlParameter parameter)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into imagetabel(");
strSql.Append("ImageData,Time,Type,ImageType,ReferenceID)");
strSql.Append(" values (");
strSql.Append("@ImageData"+currentid+",'" + System.DateTime.Now + "',1,0,@BSID) ");
parameter = new SqlParameter("@ImageData" + currentid, ImageData);
return strSql; }

  主体代码如下

StringBuilder strSql = new StringBuilder();
Random random=new Random();
SqlCommand command = new SqlCommand();
SqlParameter[] parameters = new SqlParameter[3];
strSql.Append("set XACT_ABORT ON ");
strSql.Append(" begin tran t1 ");
StringBuilder strSql2 = new StringBuilder();
strSql.Append(" declare @BSID int ");
strSql2.Append("insert into table(");
strSql2.Append("ID,CheckResult,TestTime,Result,Picture,Type)");
strSql2.Append(" values (");
strSql2.Append("'" + ID + "','" +CheckResult + "','" + TestTime + "','" + FeaturesResult + "','" +BUltrasoundPicture + "','" + BUitrasoundType + "') ");
strSql2.Append(";select @BSID=@@IDENTITY ");
strSql.Append(strSql2);
if (ImageData != null)
{
SqlParameter parameter = null;
strSql.Append(SaveImg(ImageData," ",1, out parameter));
parameters[0] = parameter;
}
if (ImageData2 != null)
{
SqlParameter parameter = null;
strSql.Append(SaveImg(ImageData2, " ",2, out parameter));
parameters[1] = parameter;
}
if (ImageData3 != null)
{
SqlParameter parameter = null;
strSql.Append(SaveImg(ImageData3," ",3, out parameter));
parameters[2] = parameter;
}
strSql.Append("commit tran t1");
return Convert.ToInt32(SqlHelper.ExecuteNonQuery(SqlHelper.connectionString, CommandType.Text, strSql.ToString(),parameters));

  其中图片表的关联id关联主表插入后的id,由语句select @BSID=@@IDENTITY得到,当主键自增时此语句才生效。

上一篇:KnockoutJs学习笔记(十一)


下一篇:c# ThreadPoold使用心得