C#往SQL数据库字段中插入二进制文件的三种方法

时间:2022-10-16 23:56:18

C#往SQL数据库字段中插入二进制文件的三种方法,网络测试程序一别人好象成功了.我怎么都测试不成功.同事解说是INSERTSQL语句是字符串.所以不能把二进制内容跟字段联合起来传递给SQL2000

//创建一个SqlConnection对象
    string strCon = "Initial Catalog='HMMISDATA';Server='192.168.1.180';User ID='用户名';Password='密码';Persist Security Info=True";
    SqlConnection myConn = new SqlConnection ( strCon ) ;    
    //测试方法一:使用SQL语句插入二进制字段,测试失败
    /*
    string insertSQL="INSERT INTO EP_HmSoftOfficeDocList(DocumentType,DocumentDate,DocumentManager,DocumentDepartment,DocumentTitle,DocumentContent,BinaryFileData,BinaryFileType,BinaryFileLength,BinaryFilePath,AddUserName,AddUserTime,AddUserIP) ";
    insertSQL=insertSQL+ " VALUES('"+DocumentType+"','"+DocumentDate+"','"+DocumentManager+"','"+DocumentDepartment+"','"+DocumentTitle+"','"+DocumentContent+"',"+@BinaryFileData+",'"+BinaryFileType+"',"+BinaryFileLength+",'"+BinaryFilePath+"','"+strAddUser+"','"+strAddTime+"','"+strAddIP+"')" ;
    SqlCommand insertCommand= new SqlCommand();    
    insertCommand.Parameters.Add("@BinaryFileData",SqlDbType.Image);
    insertCommand.Parameters["@BinaryFileData"].Value=BinaryFileData;
    insertCommand.CommandType=CommandType.Text;
    insertCommand.CommandText=insertSQL;
    insertCommand.Connection=myConn;
    insertCommand.Connection.Open();
    insertCommand.ExecuteNonQuery();
    */
    //测试方法二:使用存储过程插入二进制字段,测试成功
    /*
    SqlCommand insertCommand = new SqlCommand("sp_HmSoft_OfficeDoc_ADD",myConn);
    insertCommand.CommandType = CommandType.StoredProcedure;
    insertCommand.Parameters.Add(new SqlParameter("@DocumentType", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@DocumentDate", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@DocumentManager", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@DocumentDepartment", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@DocumentTitle", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@DocumentContent", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@BinaryFileData", SqlDbType.Image));
    insertCommand.Parameters.Add(new SqlParameter("@BinaryFileType", SqlDbType.NVarChar, 50));   
    insertCommand.Parameters.Add(new SqlParameter("@BinaryFileLength", SqlDbType.Int));
    insertCommand.Parameters.Add(new SqlParameter("@BinaryFilePath", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@AddUserName", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@AddUserTime", SqlDbType.NVarChar, 50));
    insertCommand.Parameters.Add(new SqlParameter("@AddUserIP", SqlDbType.NVarChar, 50));    
    insertCommand.Parameters["@DocumentType"].Value=DocumentType;
    insertCommand.Parameters["@DocumentDate"].Value=DocumentDate;
    insertCommand.Parameters["@DocumentManager"].Value=DocumentManager;
    insertCommand.Parameters["@DocumentDepartment"].Value=DocumentDepartment;   
    insertCommand.Parameters["@DocumentTitle"].Value=DocumentTitle;
    insertCommand.Parameters["@DocumentContent"].Value=DocumentContent;
    insertCommand.Parameters["@BinaryFileData"].Value=BinaryFileData;
    insertCommand.Parameters["@BinaryFileType"].Value=BinaryFileType;
    insertCommand.Parameters["@BinaryFileLength"].Value=BinaryFileLength;
    insertCommand.Parameters["@BinaryFilePath"].Value=BinaryFilePath;
    insertCommand.Parameters["@AddUserName"].Value=strAddUser;
    insertCommand.Parameters["@AddUserTime"].Value=strAddTime;
    insertCommand.Parameters["@AddUserIP"].Value=strAddIP;
    insertCommand.Connection.Open();
    insertCommand.ExecuteNonQuery();
    */

    //测试方法三:使用DataSet插入二进制字段,测试成功  
    myConn.Open();
    DataSet tempDataSet=new DataSet();
    SqlDataAdapter tempAdapter = new SqlDataAdapter("SELECT * FROM EP_HmSoftOfficeDocList WHERE 1=0", myConn);
    SqlCommandBuilder tempBuilder=new SqlCommandBuilder(tempAdapter);   
    tempAdapter.Fill(tempDataSet);
    //'插入一条记录    
    DataRow tempDataRow = tempDataSet.Tables[0].NewRow();
    tempDataRow["DocumentType"] =DocumentType;
    tempDataRow["DocumentDate"] =DocumentDate;
    tempDataRow["DocumentManager"] =DocumentManager;
    tempDataRow["DocumentDepartment"] =DocumentDepartment;
    tempDataRow["DocumentTitle"] =DocumentTitle;
    tempDataRow["DocumentContent"] =DocumentContent;
    tempDataRow["BinaryFileData"] =BinaryFileData;   
    tempDataRow["BinaryFileType"] =BinaryFileType;
    tempDataRow["BinaryFileLength"] =BinaryFileLength;
    tempDataRow["BinaryFilePath"] =BinaryFilePath;
    tempDataRow["AddUserName"] =strAddUser;
    tempDataRow["AddUserTime"] =strAddTime;
    tempDataRow["AddUserIP"] =strAddIP;
    tempDataSet.Tables[0].Rows.Add(tempDataRow);
    tempAdapter.Update(tempDataSet);
    
    //关闭连接
    myConn.Close ( ) ;