Reading and Writing Binary Data with SQL Server

[ Team LiB ] Recipe 9.11 Reading and Writing Binary Data with SQL Server Problem You need to read and write binary data from and to a SQL Server 2000 database. Solution Use the techniques from the following example
Nội dung trích xuất từ tài liệu:
Reading and Writing Binary Data with SQL Server[ Team LiB ]Recipe 9.11 Reading and Writing Binary Data with SQL ServerProblemYou need to read and write binary data from and to a SQL Server 2000 database.SolutionUse the techniques from the following example.The schema of table TBL0911 used in this solution is shown in Table 9-3. Table 9-3. TBL0911 schema Column name Data type Length Allow nulls?Id int 4 NoDescription nvarchar 50 YesBlobData image 16 YesThe sample code contains nine event handlers:Form.Load Creates a DataAdapter to read and update the Id and Description fields from table TBL0911. A TextBox is bound to the Id column and another TextBox is bound to the Description field. A DataSet is filled with all records from TBL0911. The BindingManager is retrieved for the table in the DataSet. A handler is attached to the BindingManager.PositionChanged event. Finally, the display is updated for the current record in the table.BindingManagerBase.PositionChanged Clears the image displayed in the PictureBox on the form. The ID of the current record is retrieved. A connection is created to select the field BlobData—an image—from TBL0911 corresponding to the current record. The image is retrieved using a DataReader. A MemoryStream is created from the image retrieved and the MemoryStream is loaded into the PictureBox using the Image.FromStream( ) method passing the image in the MemoryStream as an argument.Select Image Button.Click Opens a file dialog to allow an image to be selected by the user. The image is retrieved using a FileStream and loaded into the PictureBox using the Image.FromStream( ) method.Clear Image Button.Click Clears the selected image from the PictureBox on the form.Update Button.Click Gets the ID of the current record from the BindingManager and builds a SQL statement to update the image in the field BlobData. A connection is created and a stored procedure command is created to update the image. A parameter for the image is added to the stored procedure command. The ExecuteNonquery( ) method of the Command object is used to update the image in the database. The DataAdapter is used to update the other data—the Description field in this sample. (move next) Button.Click Moves to the next record by setting the Position property of the BindingManager to one more than the current value.>> (move last) Button.Click Moves to the last record by setting the Position property of the BindingManager to one less than the number of records.The C# code is shown in Example 9-14.Example 9-14. File: BinaryDataForm.cs// Namespaces, variables, and constantsusing System;using System.Configuration;using System.Drawing;using System.Windows.Forms;using System.IO;using System.Data;using System.Data.SqlClient;private const String TABLENAME = TBL0911;private DataSet ds;private SqlDataAdapter da;private BindingManagerBase bm;private Byte[] image;// . . .private void BinaryDataForm_Load(object sender, System.EventArgs e){ // Create the DataSet. ds = new DataSet( ); // Define select and update commands for the DataAdapter. String selectCommand = SELECT Id, Description FROM + TABLENAME; String updateCommand = UPDATE + TABLENAME + + SET Description = @Description + WHERE Id = @Id; // Create the DataAdapter. da = new SqlDataAdapter(selectCommand, ConfigurationSettings.AppSettings[Sql_ConnectString]); da.UpdateCommand = new SqlCommand(updateCommand, da.SelectCommand.Connection); da.UpdateCommand.CommandType = CommandType.Text; da.UpdateCommand.Parameters.Add(@Id, SqlDbType.Int, 0, Id); da.UpdateCommand.Parameters.Add(@Description, SqlDbType.NVarChar, 50, Description); // Fill the schema and the data from the table. da.FillSchema(ds, SchemaType.Source, TABLENAME); da.Fill(ds, TABLENAME); // Bind all of the controls to the DataSet. idTextBox.DataBindings.Add(Text, ds, TABLENAME + .Id); descriptionTextBox.DataBindings.Add(Text, ds, TABLENAME + .Description); // Get the binding manager base for the parent table. bm = BindingContext[ds, TABLENAME]; // Handler to update the correct image in response to // each record reposition bm.PositionChanged += new EventHandler(bm_PositionChanged); // Update the display for the first record. bm_PositionChanged(null, null);}private void bm_PositionChanged(Object sender, EventArgs e){ // Handler for the binding manager record change // Clear the image and picture box. image = null; imagePictureBox.Image = null; // Get the ID for the record from the binding manager. int Id = (int)ds.Tables[TABLENAME].Rows[bm.Position][ID]; // Create the connection. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings[Sql_ConnectString]); // Create the command to retrieve the image from the database. String sqlText = SELECT BlobData FROM + TABLENAME + WHERE Id = + Id; SqlCommand cmd = new SqlCommand(sqlText, conn); // Retrieve the image to a stream. conn.Open( ); try { int bufferSize = 100; byte[] outbyte = new byte[bufferSize]; long retVal = 0; long startIndex = 0; SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SequentialAccess); dr.Read( ); // Check to see if the field is DBNull. if (!dr.IsDBNull(0)) { // Create the memory stream to hold the output. MemoryStream ms = new MemoryStream( ); // Read the bytes into outbyte. ...