HOME   ARTICLES   SEARCH   DIRECTORY   CONTACT   ABOUT   MORE .NET SITES   ADVERTISE   MSDN Community 
Article Menu
superdotnet originals
Intro Lessons
Tips and Tricks
Data Access
Code-Behind/Controls
Quick Code
General ASP.NET
Windows Forms

All Articles
Most Viewed Articles
Search Articles

55,000+ developers and growing

Language Specs
Browse our HTML conversion of the C# Language Specification

Resource Options
Latest Web Additions
Alphabetically
Search
Directory

Hosts
.NET Hosts

More Resources
Namespace List
More MSDN Links
QuickStart Lists

Add Resources
Add Listing
Add Host



Using Images and Files with SQL Server: Part 1

Article Categories: Data Access | Quick Code
Rated: | Number Of Ratings 11 | Add Rating/View Ratings


This article is the first in a series of using images and files with SQL Server and focuses first on uploading binary data to the database. The code is actually pretty straight forward, but before talking about the code, let's setup our table.

Create a table in your database called "TestFiles" with the following columns/data types (you can alter these as you see fit for length of filename and file type):

  • ID - Identity (Auto Increment).
  • MyFileName - Varchar(35).
  • MyFile - Image.
  • FileType - Varchar(25).

We're storing the FileName in "MyFileName", the actual file in "MyFile" and the type of file in "FileType".

The code below creates a form which has inputs for a filename, and the actual file to be uploaded. After the file is submitted we use the HtmlInputFile Class property PostedFile to get access to the file posted by our Web browser. Once we've done that we can access the HttpPostedFile Class properties and methods to read data, save data, and get other information about the file.

In our case we won't be using the method SaveAs because this method writes a file to the Web server. Since we want to store the information in our database, we need to use the property InputStream which initializes a stream for reading our data. At the same time we're using the ContentLength property to get the size of the file in bytes for reading the data and using the property ContentType to store the type of file we're uploading. What's nice about using that property is the user doesn't have to worry about selecting a file type (for instance from a drop down menu) and possibly not selecting the right one, resulting in an error when the file is read back from the database.

Once we have that information we can create a new byte array and read our file stream into it. From there we simply upload the data into our Image column.

Below is the full code for the page with more notes and information following:

<% @Page Language="C#" %>
<% @Import Namespace="System.IO" %>
<% @ Import Namespace="System.Data" %>
<% @ Import Namespace="System.Data.SqlClient" %>

<script runat="server">
public void UploadBtn_Click (Object sender, EventArgs e){

//Get the posted file
Stream fileDataStream = MyFile.PostedFile.InputStream;

//Get length of file
int fileLength = MyFile.PostedFile.ContentLength;

//Create a byte array with file length
byte[] fileData = new byte[fileLength];

//Read the stream into the byte array
fileDataStream.Read(fileData,0,fileLength);

//get the filename
string fileTitle = MyFileName.Value;

//get the file type
string fileType = MyFile.PostedFile.ContentType;

//now create the connection, the sql statement, and the sql parameters
SqlConnection connection = new SqlConnection("server=localhost; uid=; pwd=; Database=mydb");
SqlCommand command = new SqlCommand ("INSERT INTO TestFiles (MyFileName,MyFile,FileType)" + 
"VALUES (@MyFileName,@MyFile,@FileType)", connection);

SqlParameter paramTitle = new SqlParameter ("@MyFileName", SqlDbType.VarChar,35); 
paramTitle.Value = fileTitle;
command.Parameters.Add(paramTitle);

SqlParameter paramData = new SqlParameter ("@MyFile", SqlDbType.Image);
paramData.Value = fileData;
command.Parameters.Add(paramData);

SqlParameter paramType = new SqlParameter ("@FileType", SqlDbType.VarChar,25); 
paramType.Value = fileType;
command.Parameters.Add(paramType);

//open the connection and execute the query
connection.Open();
command.ExecuteNonQuery();
connection.Close();

//now do some cleanup
Message.Text="Your file has uploaded";
MyFileName.Value = "";
}
</script>

<hr>
<asp:label id="Message" Text="Select a file and filename" runat="server"/>
<hr>

<form method="post"
            enctype="multipart/form-data" 
            runat="server">
			
		 <b>File Name:</b><br>
		 <input id="MyFileName" type="text" runat="server">
         <P>
		 <b>File:</b><br>
		 <input id="MyFile" 
                type="file" 
                runat="server"> 
	<br><br>
         <input type=submit 
                value="Upload!"
                OnServerclick="UploadBtn_Click" 
                runat="server">
         <br><br><br>
</form>

Notice that we're allowing the user to set a File Name through the form itself. However this can also be accomplished through using the HttpPostedFile Class property FileName. The one thing to remember however is that this will return the fully qualified name of the actual file on the user's computer as in C:\Inetpub\wwwroot\images\map.gif, so if you use this property to get the name of the file you may want to manipulate the string before inserting it into your database. Also note that for these examples we're not doing any error checking for brevity of code.

As you can see, compared to classic ASP this is much easier and allows easy storing of binary data into a SQL database. In Part 2 we'll look at different ways to retrieve images and files from the database and write them to the browser as well as forcing "Save As" dialog boxes with a filename and extension.


Copyright 2002,2003 superdotnet.com, all rights reserved.
Original articles and site content may not be reproduced from our
site without consent from superdotnet.com.
      Privacy Policy and User Site Agreement