I applied several different methods to insert data into SQL Server (for FileStream). What is the best way to insert FileStream objects? The main difference between the approaches below was that the insert was inserted and the other in the place holder for the FileStream object.
One approach is that they directly inserted the document through C # through the insert:
Link: FileStream
con.Open(); string sql = "INSERT INTO MyFsTable VALUES (@fData, @fName, default)"; SqlCommand cmd = new SqlCommand(sql, con); cmd.Parameters.Add("@fData", SqlDbType.Image, fileData.Length).Value = fileData; cmd.Parameters.Add("@fName", SqlDbType.NVarChar).Value = fi.Name; cmd.ExecuteNonQuery(); con.Close();
In another approach, they inserted a row, but left the document (FileStream Column) null. I had to enter a dummy value because when the FileStream column was empty, the Get File Path Null call returned:
Link: FileStream
5: if (FileUpload1.FileContent.Length > 0) 6: { 7: SqlConnection objSqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); 8: objSqlCon.Open(); 9: SqlTransaction objSqlTran = objSqlCon.BeginTransaction(); 10: 11: SqlCommand objSqlCmd = new SqlCommand("FileAdd",objSqlCon,objSqlTran); 12: objSqlCmd.CommandType = CommandType.StoredProcedure; 13: 14: SqlParameter objSqlParam1 = new SqlParameter("@SystemNumber", SqlDbType.Int); 15: objSqlParam1.Value = "1"; 16: 17: SqlParameter objSqlParam2 = new SqlParameter("@FileType", SqlDbType.VarChar,4); 18: objSqlParam2.Value = System.IO.Path.GetExtension(FileUpload1.FileName); 19: 20: SqlParameter objSqlParamOutput = new SqlParameter("@filepath", SqlDbType.VarChar, -1); 21: objSqlParamOutput.Direction = ParameterDirection.Output; 22: 23: objSqlCmd.Parameters.Add(objSqlParam2); 24: objSqlCmd.Parameters.Add(objSqlParam1); 25: objSqlCmd.Parameters.Add(objSqlParamOutput); 26: 27: 28: objSqlCmd.ExecuteNonQuery(); 29: 30: string Path = objSqlCmd.Parameters["@filepath"].Value.ToString(); 31: 32: objSqlCmd = new SqlCommand("SELECT GET_FILESTREAM_TRANSACTION_CONTEXT()", objSqlCon, objSqlTran); 33: 34: byte[] objContext = (byte[])objSqlCmd.ExecuteScalar(); 35: 36: 37: SqlFileStream objSqlFileStream = new SqlFileStream(Path, objContext, FileAccess.Write); 38: 39: objSqlFileStream.Write(buffer, 0, buffer.Length); 40: objSqlFileStream.Close(); 41: 42: objSqlTran.Commit();
source share