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



The information in this article applies to:

  • Microsoft ADO.NET (included with the .NET Framework 1.1)
  • Microsoft ADO.NET (included with the .NET Framework) 1.0
  • Microsoft ASP.NET (included with the .NET Framework 1.1)
  • Microsoft ASP.NET (included with the .NET Framework) 1.0

This article was previously published under Q326502
This article refers to the following Microsoft .NET Framework Class Library namespaces:
  • System.Data.SqlClient
  • System.IO

IN THIS TASK

SUMMARY

This article describes how to use the FileStream object and a byte array in ASP.NET to write binary large object (BLOB) data to Microsoft SQL Server. This article also describes how to load BLOB data from the SQL Server database and how to copy this data to a downloadable file through the .aspx page.

back to the top

Requirements

The following list outlines the recommended hardware, software, network infrastructure, and service packs that are required:
  • Microsoft Windows 2000 Professional, Windows 2000 Server, Windows 2000 Advanced Server, or Windows NT 4.0 Server
  • Microsoft Visual Studio .NET
  • Microsoft SQL Server
back to the top

Create the Project

This sample project connects to the SQL Server Pubs database. This project uses the existing Pub_info table and stores a .jpg file in the logo field of this table. The data type of the logo field is Image.
  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.
back to the top

Additional Information

Although this sample uses a .jpg file, you can download any type of file from your Web application to the client browser. For example, to use a Microsoft Word document instead of a .jpg file, replace the following code
Response.ContentType = "Image/JPEG"
				
with this code:
Response.AddHeader("Content-Disposition", "attachment;filename=blob.doc")
Response.ContentType = "application/msword"
				
For a list of other content types and the code that is associated with each content type, visit the following AspTutorial.info Web site:

Active server pages - ContentType
http://www.asptutorial.info/sscript/ContentType.asp

back to the top

REFERENCES

For additional information about how to read and write BLOB data with ADO.NET and Visual Basic .NET, click the article numbers below to view the articles in the Microsoft Knowledge Base:

308042 HOW TO: Read and Write BLOB Data by Using ADO.NET with Visual Basic .NET

316887 HOW TO: Read and Write a File to and from a BLOB Column by Using ADO.NET and Visual Basic .NET

317034 HOW TO: Read and Write a File to and from a BLOB Column by Using Chunking in ADO.NET and Visual Basic .NET

back to the top

Modification Type:MajorLast Reviewed:9/3/2003
Keywords:kbHOWTOmaster kbSqlClient kbSystemData KB326502 kbAudDeveloper