Reading and writing BLOB data by using Ado.net in C #

ado| data

This article references the following Microsoft. NET Framework Class Library namespaces: System.Data.SqlClient
System.IO

The content of this task
• overview
• Requirements
• Create Project

Profile
In Ado.net, the DataReader column, DataSet column, or Command parameter cannot use the GetChunk and AppendChunk methods. This article describes how to use Visual C #. NET to read and write binary large object (BLOB) fields.

Back to the top of the page
Requirements
The following list lists the recommended hardware, software, network structure, and required Service pack: Microsoft Windows Professional, Windows SQL Server, Windows Advance D Server or Windows NT 4.0 Server
Microsoft Visual Studio. NET
Microsoft SQL Server
Back to the top of the page
Create a project
1. Add a table named MyImages in your SQL Server Northwind database. The table contains the following fields: • Identity field, named "ID", and type Int.
• Field, called "Description", the type is VarChar and the length is 50.
• Field, named "Imgfield", and the type is Image.

2. Start visual Studio. NET, and then create a new Visual C # Windows application project.
3. Drag two Button controls from the Toolbox onto the default form Form1.
4. In the Properties window, change the Button1 Text property to save to a database (from a file) and change the Button2 Text property to save to a file (from the database).
5. Add the following code to the top of the code window:
Using System.Data;
Using System.Data.SqlClient;
Using System.IO;
6. Double-click Button1, and then add the following code to the Button1_Click event handler:
{
SqlConnection con = new SqlConnection ("Server=darkover;uid=sa;pwd=password1;database=northwind");
SqlDataAdapter da = new SqlDataAdapter ("select * from MyImages", con);
SqlCommandBuilder MYCB = new SqlCommandBuilder (DA);
DataSet ds = new DataSet ("MyImages");

Da. MissingSchemaAction = MissingSchemaAction.AddWithKey;
FileStream fs = new FileStream (@ "C:\winnt\Gone Fishing.BMP", FileMode.OpenOrCreate, FileAccess.Read);

Byte[] Mydata= new Byte[fs. Length];
Fs. Read (MyData, 0, System.Convert.ToInt32 (FS). Length));

Fs. Close ();

Da. Fill (ds, "MyImages");

DataRow Myrow;
Myrow=ds. tables["MyImages"]. NewRow ();

myrow["Description"] = "This would is Description text";
myrow["Imgfield"] = MyData;
Ds. tables["MyImages"]. Rows.Add (Myrow);
Da. Update (ds, "MyImages");

Con. Close ();

}
7. Double-click Button2, and then add the following code to the Button2_Click event handler:
{
SqlConnection con = new SqlConnection ("Server=darkover;uid=sa;pwd=password1;database=northwind");
SqlDataAdapter da = new SqlDataAdapter ("select * from MyImages", con);
SqlCommandBuilder MYCB = new SqlCommandBuilder (DA);
DataSet ds = new DataSet ("MyImages");

Byte[] Mydata= new byte[0];

Da. Fill (ds, "MyImages");
DataRow Myrow;
Myrow=ds. tables["MyImages"]. Rows[0];

MyData = (byte[]) myrow["Imgfield"];
int arraysize = new int ();
ArraySize = mydata.getupperbound (0);

FileStream fs = new FileStream (@ "C:\winnt\Gone Fishing2.BMP", FileMode.OpenOrCreate, FileAccess.Write);
Fs. Write (MyData, 0,arraysize);
Fs. Close ();
}
8. Press the F5 key to compile and run the application.
9. Click Save to database (from file) to load the image located in C:\WinNT\Gone fishing.bmp into the SQL Server image field.
10. Click Save to file (from database) to save the data in the SQL Server Image field back to the file.