Storing Images and large documents in SQL Server 2005 Database Using ASP.Net

Storing Images(Binary data) using ASP.NET 2.0:

As a developer, you might face few requirements where you want to upload large documents, PDF’s and images from your application. Then how do you manage and store such large data? Usually, traditional approach was to store those large files on web server’s file system. But you also have database approach which allows you to store those large documents and PDF’s as binary data directly in the database itself. Let’s elaborate on Database approach a bit further. How do we usually store large data objects in Databases like SQL Server 2000? Ok, SQL server 2000 supports exclusive image data type to hold image data. Now SQL Server 2005 supports another new data type varbinary which allows storing binary data up to 2GB in size.

Even with new data types, we still need to understand that working with binary data is not the same as straight forward working with text data. So, we are here to discuss how to use ASP.NET 2.0 SqlDataSource control to store and retrieve image files directly from a database.

We will create application which allows user to upload images and display the uploaded pictures. The uploaded images will be stored in database as binary data. To hold image data, we need to create new table called PictureTable as shown below

Schema script for PictureTable

This table records details of pictures and content. The PictureTable table's MIMEType field holds the MIME type of the uploaded image (image/jpeg for JPG files, image/gif for GIF files, and so on); the MIME type specifies to the browser how to render the binary data. The Image column holds the actual binary contents of the picture. 

<asp:Label ID="Label1"  runat="server" Text="Upload Image"</asp:Label>

<asp:Label ID="Label2" runat="server" Text="Title"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Label ID="Label3" runat="server" Text="Image"></asp:Label>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Upload"/>

Uploading Images

As shown above, we are using Fileupload control to browse picture files on hard disk. FileUpload control is a composite control which includes a textbox and browse button together. To add this control, simply drag and drop FileUpload control from Toolbox as shown below

Toolbox


 Once user selects appropriate picture file using FileUpload control, Click upload button which inserts selected image into PictureTable as new record. The logic to insert the image into PictureTable is handled in Click event of Upload button as shown below

Protected Sub Upload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Upload.Click

        Dim fileUpload1 As FileUpload = CType(Me.FindControl("fileUpload1"), FileUpload)
        'Make sure a file has been successfully uploaded
        If fileUpload1.PostedFile Is Nothing OrElse String.IsNullOrEmpty(fileUpload1.PostedFile.FileName) OrElse fileUpload1.PostedFile.InputStream Is Nothing Then
           Label1.Text = "Please Upload Valid picture file"
           Exit Sub
        End If
        'Make sure we are dealing with a JPG or GIF file
        Dim extension As String = System.IO.Path.GetExtension(fileUpload1.PostedFile.FileName).ToLower()
        Dim MIMEType As String = Nothing
        Select Case extension
         Case ".gif"
         MIMEType = "image/gif"
         Case ".jpg", ".jpeg", ".jpe"
         MIMEType = "image/jpeg"
          Case ".png"
         MIMEType = "image/png"
         Case Else
         'Invalid file type uploaded
         Label1.Text = "Not a Valid file format"
         Exit Sub
       End Select
      'Connect to the database and insert a new record into Products
      Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ImageGalleryConnectionString").ConnectionString)
         Const SQL As String = "INSERT INTO [Pictures] ([Title], [MIMEType], [Image]) VALUES (@Title, @MIMEType, @ImageData)"
           Dim myCommand As New SqlCommand(SQL, myConnection)
           myCommand.Parameters.AddWithValue("@Title", TextBox1.Text.Trim())
            myCommand.Parameters.AddWithValue("@MIMEType", MIMEType)
            'Load FileUpload's InputStream into Byte array
           Dim imageBytes(fileUpload1.PostedFile.InputStream.Length) As Byte
            fileUpload1.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length)
            myCommand.Parameters.AddWithValue("@ImageData", imageBytes)
            myConnection.Open()
            myCommand.ExecuteNonQuery()
            myConnection.Close()
       End Using
   End Sub

Once the user has selected a file and posted back the form by clicking the "Upload" button, the binary contents of the specified file are posted back to the web server. From the server-side code, this binary data is available through the FileUpload control's PostedFile.InputStream property

This event handler starts off by ensuring that a file has been uploaded. It then determines the MIME type based on the file extension of the uploaded file. You can observe how @ImageData parameter is set. First, a byte array named imageBytes is created and sized to the Length of the InputStream of the uploaded file. Next, this byte array is filled with the binary contents from the InputStream using the Read method. It's this byte array that is specified as the @ImageData's value.

Displaying binary Data:

Regardless of what technique you employ to store the data in the database, in order to retrieve and display the binary data we need to create a new ASP.NET page. This page, named DisplayPicture.aspx, will be passed ImageID through the Querystring and return the binary data from the specified product's Image field. Once completed, the particular picture can be viewed by browsing the following link to view uploaded images. For example
http://localhost:3219/BinaryDataVb/Displaypicture.aspx?ImageID=5.

Therefore, to display an image on a web page, we can use an Image control whose ImageUrl property is set to the appropriate URL.

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

      Dim ImageID As Integer = Convert.ToInt32(Request.QueryString("ImageID"))
        'Connect to the database and bring back the image contents & MIME type for the specified picture
       Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnection").ConnectionString)
            Const SQL As String = "SELECT [MIMEType], [Image] FROM [PictureTable] WHERE [ImageID] = @ImageID"
            Dim myCommand As New SqlCommand(SQL, myConnection)
            myCommand.Parameters.AddWithValue("@ImageID", ImageID)
            myConnection.Open()
            Dim myReader As SqlDataReader = myCommand.ExecuteReader
            If myReader.Read Then
              Response.ContentType = myReader("MIMEType").ToString()
               Response.BinaryWrite(myReader("Image"))
            End If
            myReader.Close()
            myConnection.Close()
       End Using
    End Sub
Code listing for DisplayPicture.aspx



Displaying picture using QueryString parameter

The DisplayPicture.aspx does not include any HTML markup in the .aspx page. In the code-behind class's Page_Load event handler, the specified Pictures row's MIMEType and Image are retrieved from the database using ADO.NET code. Next, the page's ContentType is set to the value of the MIMEType field and the binary data is emitted using Response.BinaryWrite(Image): When DisplayPicture.aspx page complete, the image can be viewed by either directly visiting the URL

Hence, this article gives an introduction of techniques involved in uploading and retrieving large sized binary data using ASP.NET technologies

ASP.Net/SQL Server/.Net Framework Interview questions

Following are .Net Framework/ASP.Net/SQL Server questions which tests your mettle in these technologies. I quickly gathered these questions from different sources. Probably, i guess I should provide answers to these questions. I'll do that in my next post.

Where are the value types contained inside a reference type stored? Say for example if there is a class Employee and a member 'int roll no', where will the roll number be stored?

1. In the stack
2. In the heap
3. In the harddisk

What is faster?

1. Accessing the heap
2. Accessing the stack
3. Accessing the hard disk

If you declare String a = "Minecode" and 'a' points to an address 1000, executing a = "MINECODE01" will not change the address. Is this true?

• True
• False

Which of the following datatypes cannot be set to null?

1. string
2. custom classes
3. DateTime

What are the interfaces related to USING block in C#?

1. IComparer
2. IDisposable
3. ISerializable

Which of the following is true?

1. A static method can access all private properties of a class.
2. A static method can access only the public properties of a class.
3. None of the above

Which of the following is true?

1. A delegate uses an event
2. An event uses a delegate
3. None of the above

If we have a try block and three catch blocks, the first catch block catches Exception, the second one catches ArgumentException and the third one catches ArgumentNullException, Which block will catch an ArgumentNullException thrown from the try block?

1. Catch Exception
2. Catch ArgumentException
3. Catch ArgumentNullException

LogIn is bad on?

1. A write intensive table
2. A read intensive table
3. None

You can use an insert statement on a view.

• True
• False

You can insert into a table without providing values for all the non null columns.

• True
• False

Group by takes precedency over Order by.

• True
• False

Which of the following can not be rolled back?

1. Truncate
2. Delete
3. Update
4. None of the above

Referential integrity is related to.

1. Views
2. Foriegn keys
3. LogIn

Which of the following is true.

1. There can be only one unique column in a table
2. There can be only one primary key in a table
3. There can be only one foriegn key in a table

Which SQL statement is used to return only different values?

1. select distinct
2. select unique
3. select different

If there are two tables Employees and Orders an inner join between employees and orders would fetch employee rows?

1. Even if there is no order for the employee.
2. only if there is an order for the employee

If there are two tables Employees and Orders an left join between employees and orders would fetch employee rows

1. Even if there is no order for the employee.
2. only if there is an order for the employee.

I have a table called products that has the list of all products. I have a table called stores which has all the stores in the market. If I want to track the sales for a store and product combination, what kind of join will I use?

1. Inner Join
2. Right Outer Join
3. Cross Join
4. Left Outer Join

Can you explicitly update an Identity column?

• Yes
• No

In Asp.Net, interceptions like authentication and session information are handled

using

1. Pages
2. handlers
3. Modules

The page class implements

1. IHttpModule
2. IHttpHandler
3. IHttpContext

Managing Session state in memory

1. Decreases scalability
2. Has no effect on scalability
3. Increases scalability

Page remembers the state of the various controls across postbacks because of

1. Application state
2. Session state
3. Cache
4. View State

In which file do we handle the application start and end event?

1. the aspx file
2. Web.config
3. application.asax
4. global.asax

Which of the following is not a stage of page - Load Life cycle?

1. Load()
2. Init()
3. PreRender()
4. Render(),PostRender()
5. Unload()

Interface does not come with an implementation. If there are multiple classes that want to implement the same interface and want to implement the same way, they are helpless. What is the correct way to work around this problem?

1. Inheritance
2. Composition
3. Encapsulation
4. Polymorphism

A contract that could possibly come with a default implementation

1. Abstract Base class
2. Interface
3. Sealed class
4. Partial class

What is the scope of connection pooling?

1. Appdomain
2. Process
3. Machine

Which of the following prevent SQL injection?

1. Use of Stored Procedures
2. Use of dynamic queries by using SqlParameter objects.
3. Use of dynamic Queries by concatinating strings.

I have a million rows in a database and I want to read the records and show them in the UI. What is the prefered way to read the data?

1. DataReader
2. DataSet
3. RecordSet

When a web service receives an invalid input it should throw

1. SoapFault with Fault code set to Client
2. InvalidArgumentException
3. SoapFault with Fault code set to Server
4. Argument Exception

ASP.NET uses _________ to generate a WSDL out of your class

1. Polymorphism and Serialization
2. Reflection and serialization
3. Encapsulation and serialization
4. ISDASM and Serialization

WSDL is a

1. contract in xml form
2. contract in binary form
3. Protocol to access a web service
4. Directory to discover a web service

Where would you store the connection strings that are used in the ASP.NET application?

1. Web.config
2. Machine.config
3. In your page class
4. in global.asax

Which config file takes precedence?

1. Web.Config
2. Machine config

What is the extension used for ASP.NET HTTP Handlers?

1. ASHX
2. ASCX

Response.Redirect redirects to a new URL

1. With the knowledge of the browser
2. without the knowledge of the Browser

Changing Authentication mode in SQL Server 2005

SQL Server authentication mode is usually configured during SQL Server installation. By default, Windows Authentication mode is configured. Ok, What if we want to override the mode configured during installation.

As you may know, SQL Server 2000/2005 supports three authentication modes:

1) Windows Authentication Mode
2) SQL Authentication Mode
3) Mixed Mode Authentication (SQL/Windows)

Here're following steps to switch mode

In SQL Server Management Studio Object Explorer, right-click your server, and then click Properties.

On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

Make sure, you restart the server to effect the changes.

You can restart the server from SQL Server Configuration Manager.