Save file to DB

This article show how to save a file to DB.

In order to enable progress indicator you will have to follow this steps:

• Handle server-submit event.

  private void btnUpload_ServerClick(object sender, System.EventArgs e)
  {
  	if(FileUp.PostedFile!=null&&FileUp.PostedFile.FileName!="")
  	{
  		SaveFileToDatabase(FileUp.PostedFile);
  	}
  }

• Save a file to DB (Way 1):  use the SqlBlobStream class to read and write data from BLOB (LongVarBinary) columns in a database table.

  private void SaveFileToDatabase(Mediachase.FileUploader.Web.McHttpPostedFile	postedFile)
  {
  	string strConnectionString = "data source=(local);user id=sa;password=;Initial Catalog=FileDB;";
  
  
  	SqlConnection	sqlConnection = new SqlConnection(strConnectionString);
  	sqlConnection.Open();
  	sqlTran = sqlConnection.BeginTransaction();
  	try
  	{
  		//Step 1. Create New Record with BLOB Field and return FileId
  		SqlCommand cmdCreateNewFile = new SqlCommand("INSERT INTO [FILE] (FileName) VALUES(@FileName);SELECT @retval = @@identity", sqlConnection,sqlTran);
  
  		SqlParameter prmFileName =	new SqlParameter("@FileName",SqlDbType.NVarChar,256);
  		prmFileName.Direction = ParameterDirection.Input;
  		prmFileName.Value = postedFile.FileName;
  
  		SqlParameter prmRetval =	new SqlParameter("@retval",SqlDbType.Int);
  		prmRetval.Direction = ParameterDirection.Output;
  
  		cmdCreateNewFile.Parameters.Add(prmFileName);
  		cmdCreateNewFile.Parameters.Add(prmRetval);
  
  		cmdCreateNewFile.ExecuteNonQuery();
  
  		//Step 2. Upload Binary Data Field
  		using(SqlBlobStream stream = new SqlBlobStream(sqlTran,
  			"FILE",
  			"Data",
  			SqlBlobAccess.Write,
  			new SqlParameter("@FileId",prmRetval.Value)))
  		{
  			using(System.IO.BinaryReader br = new System.IO.BinaryReader(postedFile.InputStream))
  			{
  
  				Byte [] Buffer = br.ReadBytes(BUFFER_LENGTH);
  				while(Buffer.Length > 0)
  				{
  					stream.Write(Buffer,0,Buffer.Length);
  				}
  			}
  		}
  
  
  		sqlTran.Commit();
  	}
  	catch(Exception ex)
  	{
  		sqlTran.Rollback();
  		throw;
  	}
  	finally
  	{
  		sqlConnection.Close();
  	}
  }
  

• Save a file to DB (Way 2): use the Microsoft SQL Server READTEXT and UPDATETEXT statements to read and write data from BLOB (LongVarBinary) columns in a database table.
  
  For More Information, visit the following Microsoft Web site:
  HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual C# .NET
  HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual VB .NET
  Obtaining BLOB Values from a Database
  
  

  private void SaveFileToDatabase(Mediachase.FileUploader.Web.McHttpPostedFile	postedFile)
  {
  	int BUFFER_LENGTH = 32768; // Chunk size.
  
  	SqlTransaction	sqlTran = null;
  
  	SqlConnection	sqlConnection = new SqlConnection("data source=(local);user id=sa;password=;Initial Catalog=FileDB;");
  	sqlConnection.Open();
  	sqlTran = sqlConnection.BeginTransaction();
  	try
  	{
  		//Step 1. Create New Record with BLOB Field and return FileId
  		SqlCommand cmdCreateNewFile = new SqlCommand("INSERT INTO [FILE] (FileName) VALUES(@FileName);SELECT @retval = @@identity", sqlConnection,sqlTran);
  
  		SqlParameter prmFileName =	new SqlParameter("@FileName",SqlDbType.NVarChar,256);
  		prmFileName.Direction = ParameterDirection.Input;
  		prmFileName.Value = postedFile.FileName;
  
  		SqlParameter prmRetval =	new SqlParameter("@retval",SqlDbType.Int);
  		prmRetval.Direction = ParameterDirection.Output;
  
  		cmdCreateNewFile.Parameters.Add(prmFileName);
  		cmdCreateNewFile.Parameters.Add(prmRetval);
  
  		cmdCreateNewFile.ExecuteNonQuery();
  
  		//Step 2. Upload Binary Data Field
  
  		// Step 2.1. Prepare  [1/21/2004]
  		SqlCommand cmdGetPointer = new SqlCommand("SET NOCOUNT ON;UPDATE [FILE] SET Data = 0x0 WHERE FileId = @FileId;SELECT @Pointer = TEXTPTR(Data) FROM [FILE] WHERE FileId = @FileId", sqlConnection,sqlTran);
  
  		SqlParameter PointerOutParam  = cmdGetPointer.Parameters.Add("@Pointer", SqlDbType.VarBinary, 100);
  		PointerOutParam.Direction = ParameterDirection.Output;
  		cmdGetPointer.Parameters.Add("@FileId",prmRetval.Value);
  
  		cmdGetPointer.ExecuteNonQuery();
  		// Step 2.2. Upload [1/21/2004]
  
  		SqlCommand cmdUploadBinary = new SqlCommand("UPDATETEXT [FILE].Data @Pointer @Offset @Delete WITH LOG @Bytes", sqlConnection,sqlTran);
  
  		SqlParameter PointerParam  = cmdUploadBinary.Parameters.Add("@Pointer", SqlDbType.Binary, 16);
  		SqlParameter OffsetParam= cmdUploadBinary.Parameters.Add("@Offset", SqlDbType.Int);
  		SqlParameter DeleteParam = cmdUploadBinary.Parameters.Add("@Delete", SqlDbType.Int);
  		DeleteParam.Value = 1;  // delete 0x0 character
  		SqlParameter BytesParam  = cmdUploadBinary.Parameters.Add("@Bytes", SqlDbType.Binary, BUFFER_LENGTH);
  
  		using(System.IO.BinaryReader br = new System.IO.BinaryReader(postedFile.InputStream))
  		{
  			int Offset = 0;
  			OffsetParam.Value = Offset;
  			// Read buffer full of data and execute UPDATETEXT statement.
  
  			Byte [] Buffer = br.ReadBytes(BUFFER_LENGTH);
  			while(Buffer.Length > 0)
  			{
  				PointerParam.Value = PointerOutParam.Value;
  				BytesParam.Value = Buffer;
  				cmdUploadBinary.ExecuteNonQuery();
  				DeleteParam.Value = 0; //Do not delete any other data.
  				Offset += Buffer.Length;
  				OffsetParam.Value = Offset;
  				Buffer = br.ReadBytes(BUFFER_LENGTH);
  			}
  		}
  
  		sqlTran.Commit();
  	}
  	catch(Exception ex)
  	{
  		sqlTran.Rollback();
  		throw;
  	}
  	finally
  	{
  		sqlConnection.Close();
  	}
  }