根据数据库帮助类采用事务插入图片到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得到,当主键自增时此语句才生效。