HOW TO: Read and Write BLOB Data by Using ADO.NET Through ASP.NET

Requirements

• Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
• Microsoft Visual Studio .NET
• Microsoft SQL Server

Create the Project

1. To create a new ASP.NET Web Application project in Microsoft Visual Basic .NET, follow these steps:
   1. Start Visual Studio .NET.
   2. On the File menu, point to New, and then click Project.
   3. In the New Project dialog box, click Visual Basic Projects under Project Types, and then click ASP.NET Web Application under Templates.
2. Drag two Button controls from the Web Forms section of the toolbox to the default Web form.
3. In the Properties window, change the Text property of Button1 to Save File to Database, and then change the Text property of Button2 to Load File from Database.
4. Add the following code to the top of the code-behind window:

         Imports System.Data.SqlClient
         Imports System.IO
   					

5. Double-click Button1, and then add the following code to the Button1_Click event handler:

           Dim con As New SqlConnection("Server=yileiw2;uid=sqlauth;pwd=sqlauth;database=pubs")
           Dim da As New SqlDataAdapter("Select * From pub_info", con)
           Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
           Dim ds As New DataSet()
   
           da.MissingSchemaAction = MissingSchemaAction.AddWithKey
           con.Open()
           da.Fill(ds, "Pub_info")
   
           Dim fs As New FileStream _
            ("C:\SomePath\MyPhoto.jpg", FileMode.OpenOrCreate, _
             FileAccess.Read)
           Dim MyData(fs.Length) As Byte
           fs.Read(MyData, 0, fs.Length)
           fs.Close()
           ds.Tables("Pub_info").Rows(0)("logo") = MyData
           da.Update(ds, "Pub_info")
   
           fs = Nothing
           MyCB = Nothing
           ds = Nothing
           da = Nothing
   
           con.Close()
           con = Nothing
           Response.Write("File saved to database")
   					

6. Double-click Button2, and then add the following code to the Button2_Click event handler:

           Dim con As New SqlConnection("Server=yileiw2;uid=sqlauth;pwd=sqlauth;database=pubs")
           Dim da As New SqlDataAdapter("Select * From pub_info", con)
           Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
           Dim ds As New DataSet()
   
           con.Open()
           da.Fill(ds, "Pub_info")
           Dim myRow As DataRow
           myRow = ds.Tables("Pub_info").Rows(0)
   
           Dim MyData() As Byte
           MyData = myRow("logo")
           
           Response.Buffer = True
           Response.ContentType = "Image/JPEG"
           Response.BinaryWrite(MyData)
   
           MyCB = Nothing
           ds = Nothing
           da = Nothing
   
           con.Close()
           con = Nothing
   					

7. Press F5 to compile and to run the application.
8. Click Save File to Database to save MyPhoto.jpg in the SQL Server Image field. After you receive the confirmation message that the image has been saved, check your table to verify.
9. Click Load File from Database to load the data from the SQL Server Image field to the Web browser.

Additional Information

Response.ContentType = "Image/JPEG"
				

Response.AddHeader("Content-Disposition", "attachment;filename=blob.doc")
Response.ContentType = "application/msword"