Getting the identity of the most recently added record

The built-in functions @@Identity() and Scope_Identity() are designed to retrieve the most recently added record's autoincrement identity value from Access and Sql Server respectively. Here are some usage examples.
Access and @@Identity()

The Jet 4.0 provider supports @@Identity(), which means that developers no longer need to use Select Max(ID) or some other contrived method of obtaining new ID values. The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately. It is also therefore possible, though not necessary, to create a new Command object to run the Select @@Identity query. The following code shows this in action where the Connection object is opened, then the first query is executed against cmd, followed by changing the CommandText property of cmd to "Select @@Identity" and running that.

[c#]
string query = "Insert Into Categories (CategoryName) Values (?)";
string query2 = "Select @@Identity";
int ID;
string connect = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=|DataDirectory|Northwind.mdb";
using (OleDbConnection conn = new OleDbConnection(connect))
{
using (OleDbCommand cmd = new OleDbCommand(query, conn))
{
cmd.Parameters.AddWithValue("", Category.Text);
conn.Open();
cmd.ExecuteNonQuery();
cmd.CommandText = query2;
ID = (int)cmd.ExecuteScalar();
}
}

[VB]
Dim query As String = "Insert Into Categories (CategoryName) Values (?)"
Dim query2 As String = "Select @@Identity"
Dim ID As Integer
Dim connect As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=|DataDirectory|Northwind.mdb"
Using conn As New OleDbConnection(connect)
Using cmd As New OleDbCommand(query, conn)
cmd.Parameters.AddWithValue("", Category.Text)
conn.Open()
cmd.ExecuteNonQuery()
cmd.CommandText = query2
ID = cmd.ExecuteScalar()
End Using
End Using

A quick word about the absence of conn.Close() in all these examples: in all the snippets, the Connection object is instantiated within a 'using' block. At the end of the 'using' block, Dispose() is automatically called on objects created in the beginning of the block. If you do not employ 'using' blocks, make sure you explicitly call conn.Close() as soon as you are done with the connection.
Sql Server and Scope_Identity()

While Sql Server also supports @@Identity(), the recommended method for obtaining identity values on this platform is Scope_Identity(), which retrieves the last identity value created in the current scope. 'Scope' is a single module, which can be a stored procedure, batch, function or trigger. This can be used in a number of ways. Sql Server supports batch statements which would have you append Select Scope_Identity() to the end of the Insert statement, optionally separating the two statements with a semicolon, and just using ExecuteScalar() against the batch command to return the single value:

[C#]
string query = "Insert Into Categories (CategoryName) Values (@CategoryName);" +
"Select Scope_Identity()";
int ID;
string connect = @"Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" +
"Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.Parameters.AddWithValue("@CategoryName", Category.Text);
conn.Open();
ID = (int)cmd.ExecuteScalar();
}
}

[VB]
Dim query As String = "Insert Into Categories (CategoryName) Values (@CategoryName);" & _
"Select Scope_Identity()"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;AttachDbFilename=|DataDirectory|Northwind.mdf;" & _
"Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.Parameters.AddWithValue("@CategoryName", Category.Text)
conn.Open()
ID = cmd.ExecuteScalar()
End Using
End Using

Alternatively, you may prefer to use an output parameter from a stored procedure, and ExecuteNonQuery().

[C#]
string query = "AddCategory";
int ID;
string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Category", Category.Text);
cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID");
cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
ID = (int)cmd.Parameters["@CategoryID"].Value;
}
}

[VB]
Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Category", Category.Text)
cmd.Parameters.Add("@CategoryID", SqlDbType.Int, 0, "CategoryID")
cmd.Parameters("@CategoryID").Direction = ParameterDirection.Output
conn.Open()
cmd.ExecuteNonQuery()
ID = cmd.Parameters("@CategoryID").Value
End Using
End Using

And the procedure...

CREATE PROCEDURE AddCategory
-- Add the parameters for the stored procedure here
@Category nvarchar(15),
@CategoryID int OUTPUT
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Insert Into Categories (CategoryName) Values (@Category)
Set @CategoryID = Scope_Identity()
END

Finally, you can create a stored procedure that contains no output parameter, but ends with 'Select Scope_Identity()'. This version requires ExecuteScalar(), and requires less ADO.NET code and a shorter Stored Procedure.

[C#]
string query = "AddCategory";
int ID;
string connect = @"Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;";
using (SqlConnection conn = new SqlConnection(connect))
{
using (SqlCommand cmd = new SqlCommand(query, conn))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Category", Category.Text);
conn.Open();
ID = (int)cmd.ExecuteScalar();
}
}

[VB]
Dim query As String = "AddCategory"
Dim ID As Integer
Dim connect As String = "Server=.\SQLExpress;Database=Northwind;Trusted_Connection=Yes;"
Using conn As New SqlConnection(connect)
Using cmd As New SqlCommand(query, conn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.AddWithValue("@Category", Category.Text)
conn.Open()
ID = cmd.ExecuteScalar()
End Using
End Using

The (slightly) smaller procedure:

CREATE PROCEDURE AddCategory
-- Add the parameters for the stored procedure here
@Category nvarchar(15)
AS
BEGIN
SET NOCOUNT ON;

-- Insert statements for procedure here
Insert Into Categories (CategoryName) Values (@Category)
Select Scope_Identity()
END

So which method should you use with SQL Server? ExecuteNonQuery and an output parameter? Or no parameter and ExecuteScalar? The answer to that is whichever way you prefer. There is no real performance difference between the methods. My personal preference is for a stored procedure and ExecuteScalar. It requries the least amount of code :)
DataSource controls
Sql Server

And finally, for those that like to use the SqlDataSource, here's how to use the Insert() method to retrieve Scope_Identity() from SQL Server. I am grateful to Motley (Robert McKee), an MVP who regularly hangs out at the forums at asp.net for providing this solution.

The SqlDataSource control internally performs an ExecuteNonQuery() when it is called on to perform an Insert, so by just appending SELECT SCOPE_IDENTITY() to the InsertCommand and trying to retrieve the return value will not work. This is because ExecuteNonQuery() returns either the number of rows affected by an Update, Insert or Delete operation, or -1 for all other operations, which will overwrite any resultset produced from the appended Select statement. The way to work around this is to use a parameter value. This can be achieved declaratively using the following steps:

1. First, add the following to the InsertCommand: "SET @NewId = Scope_Identity()", making sure it is separated from the first part of the command by a semicolon
2. Select the SqlDataSource control in Design view
3. Hit F4 to bring up its properties
4. Click "InsertQuery"
5. Click the elipses (...)
6. In the Command and Parameter Editor, click "Add Parameter"
7. Give the new parameter a name (such as NewId) and click "Show advanced properties"
8. Change the Type to Int32 and the Direction to Output
9. Click OK to seal the deal.

Now if you switch to Source view, you will see that an additional parameter has been added to the InsertParameters collection:

<asp:Parameter Direction="Output" Name="NewId" Type="Int32" />

To retrieve the value, we need to use the SqlDataSource.Inserted event. Back in Design view, bring up the properties of the SqlDataSource again, if they are not still showing, and click the lightning bolt to bring up the events list. Double click the Inserted event, which will create an event handler in code-behind, and associate the SqlDataSource with the event handler. To retrieve the value from the event handler is straightforward:

[C#]
protected void SqlDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
int newid = (int)e.Command.Parameters["@NewId"].Value;
Response.Write(newid.ToString());
}

[VB]
Protected Sub SqlDataSource1_Inserted(ByVal sender As Object,
ByVal e As SqlDataSourceStatusEventArgs)
Handles SqlDataSource1.Inserted
Dim newid As Integer = e.Command.Parameters("@NewId").Value
Response.Write(newid.ToString())
End Sub

Access

The above approach won't work with Access, whether you use a SqlDataSource control or an AccessDataSource control. This is because Access is unable to accept batch commands. An alternative that will work with Access and the DataSource controls is to reuse the control's Connection object against a new Command object to retrieve @@Identity:

[C#]
protected void AccessDataSource1_Inserted(object sender, SqlDataSourceStatusEventArgs e)
{
string query = "SELECT @IDENTITY";
OleDbCommand cmd = OleDbCommand(query, e.Command.Connection);
int newid = (int)cmd.ExecuteScalar();
Response.Write(newid.ToString());
}

[VB]
Protected Sub AccessDataSource1_Inserted(ByVal sender As Object,
ByVal e As SqlDataSourceStatusEventArgs)
Handles AccessDataSource1.Inserted
Dim query As String = "SELECT @@IDENTITY"
Dim cmd As New System.OleDb.Command(query, e.Command.Connection)
Dim newid As Integer = cmd.ExecuteScalar()
Response.Write(newid.ToString())
End Sub

Scope_Identity() for Sql Server will not work with the above, because the change in command object changes the scope from the original command.

Handle Session Timeouts and Forms Authentication Timeouts in ASP.NET 2.0

Problems arise when we attempt to set the forms authentication timeout to a long period of time so that users don’t have to log in every time they return to our site. In this case we tend to run into issues with users being logged in even after their session has expired.

It would be much simpler if we just set the forms authentication timeout to a time that was less than the session timeout. In this case, if the user was inactive for too long, they would be redirected to the log in page. Upon redirection to the login page you could clean up and end their session. However, if we want to allow the user to be automatically logged in on return visits, this isn’t a very good solution. In addition there are other unpleasant scenarios that can occur in this model. Take for instance the following example.

You have a user that logs into your web application and goes into edit mode on one of your webforms. When the user enters edit mode, you store a business class object in a session variable for use while they’re working with that form. The user then gets a phone call and begins a lengthy conversation about selling their house or something. While they are on the phone, the forms authentication ticket expires. The user gets off the phone and enters data into more of the fields on form and then hits save. Boom, the user gets redirected to the login page because their authentication ticket expired. They loose the data they had finished entering and aren’t very happy about it.

The solution I implemented to handle these scenarios more gracefully, is to automatically end the users session, log them out and send them to the login page when their session is about to expire.

This solution is specific to the use of a master page throughout your application. If you’re not using a master page, it would be possible to create a user control that you drop on every page to implement the same functionality. However, why not just use a master page.

In my Default.master.vb I have the following two variables declared at a module level.

Protected mintTimeout As Integer
Protected mstrLoginURL As String

Then in the Page_Init of the Default.master.vb I set the two variables.

Dim intRedirectTime As Integer = 2
mintTimeout = (Session.Timeout - intRedirectTime) * 60000
mstrLoginURL = ResolveUrl("~/endsession.aspx")

The variable intRedirectTime represents how many minutes prior to the session timeout that I want to force the redirect to the login page.

60000 represents 1minute in milliseconds. I’ll be using the window.setTimeout method so I need time in milliseconds. Every time there is a postback in the application the above code is run and sets the timeout variable to 2 minutes less than the session timeout setting. I also set the mstrLoginURL with a resolved URL for my endsession.aspx page.

Then on the Default.master just before the end body tag , I put the following JavaScript section.

In the above javascript section the setTimeout method fires 2 minutes before the session is about to end. The endSession function is called which pops up an alert to notify the user that their session has ended. At this point the session is still active, but will be ended regardless once the Ok button is clicked on the alert dialog. In addition the user will be logged out using the FormsAuthentication.SignOut() method.

The page that the user is actually sent to is a blank page with the following code in the Page_Load. I have named the page endsession.aspx.

If User.Identity.IsAuthenticated = True Then
FormsAuthentication.SignOut()
End If
Session.Abandon()
Response.Redirect("~/Login.aspx")

In the above code I check to see if the user is authenticated before trying to sign them out. I then call the Session.Abandon method and redirect them to the login page.

The solution shows what is possible with a little creativity for handling timeout issues in ASP.NET 2.0 web applications. Every application is different and may require unique solutions for handling issues that arise from the new security model in ASP.NET 2.0. I hope this article inspires ideas for others to solve the session issues they may face.

If you have any thoughts, ideas, or input on this subject, please tell me about it by leaving a comment.

How do I get the IDENTITY / AUTONUMBER value for the row I inserted?

SQL Server

With SQL Server 2000, there are a couple of new functions that are better than @@IDENTITY. Both of these functions are not global to the connection, which is an important weak point of @@IDENTITY. After doing an insert, you can call:

PRINT IDENT_CURRENT('table')

This will give the most recent IDENTITY value for 'table' - regardless of whether you created it or not (this overrides the connection limitation of @@IDENTITY -- which can be useful).

Another thing you can do is:

PRINT SCOPE_IDENTITY()

This will give the IDENTITY value last created within the current stored procedure, trigger, etc.

If you using a version of SQL Server prior to 2000 (or you are in compatibility mode < 80), the best way is to use a single stored procedure that handles both the INSERT and the IDENTITY retrieval using @@IDENTITY.

Here is sample code for the stored procedure:

CREATE PROCEDURE myProc
@param1 INT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO someTable
(
intColumn
)
VALUES
(
@param1
)
SELECT NEWID = SCOPE_IDENTITY()
END

And you would call this from ASP as follows:

<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<conn string<"
set rs = conn.execute("EXEC myProc @param1=" & fakeValue)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%<

If you are using SQL Server 7.0, simply change the line in the stored procedure from ...

SELECT NEWID = SCOPE_IDENTITY()

... to ...

SELECT NEWID = @@IDENTITY

The reason SCOPE_IDENTITY() is preferred over @@IDENTITY is that if you perform an INSERT, and that table has an INSERT TRIGGER which then, in turn, inserts into another table with an IDENTITY column, @@IDENTITY is populated with the second table's IDENTITY value. So, if you are stuck using SQL Server 7.0 and need a workaround to retrieving the @@IDENTITY value because you have a trigger that also inserts into another IDENTITY-bound table, you're in luck. You can add this code to the first line of the trigger, but you will have to update all of your application and stored procedure code to deal with this new SELECT:

CREATE TRIGGER triggerInsert_tablename ON tablename FOR INSERT AS
BEGIN
SELECT @@IDENTITY
-- rest of trigger's logic...
END
GO

With that said, there are also potential cases where SCOPE_IDENTITY() can fail, but I think this possibility is more remote than with @@IDENTITY. Observe this repro, provided by David Portas:

CREATE TABLE Table1
(
i INTEGER IDENTITY(1,1) PRIMARY KEY,
x INTEGER NOT NULL UNIQUE
)
GO

CREATE TRIGGER trg_Table1 ON Table1
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
INSERT INTO Table1 (x)
SELECT x FROM Inserted
END
GO

INSERT INTO Table1 (x) VALUES (1)
GO

SELECT SCOPE_IDENTITY(), IDENT_CURRENT('Table1')

Result:

------ ------
NULL 1

This is because the actual INSERT happened outside of the scope of the caller, so SCOPE_IDENTITY() was not populated there. I have requested that the documentation for SCOPE_IDENTITY() be updated to reflect the above scenario.

Access

Jet/OLEDB provider now supports @@IDENTITY! See KB #232144 for more info, and see Article #2126 to ensure you are using a Jet/OLEDB connection string.

So with that new information, here is the technique for obtaining this value using Access:

<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<conn string<"
sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _
VBCrLf & " SELECT @@IDENTITY"
set rs = conn.execute(sql)
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%<

If you are unable to use JET 4.0, you can do a more risky hack like this:

<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<conn string<"
conn.execute "INSERT someTable(IntColumn) values(" & fakeValue & ")"
set rs = conn.execute("select MAX(ID) from someTable")
response.write "New ID was " & rs(0)
rs.close: set rs = nothing
conn.close: set conn = nothing
%<

This is more risky because it is remotely possible for two people to "cross" inserts, and receive the wrong autonumber value back. To be frank, if there is a possibility of two or more people simultaneously adding records, you should already be considering SQL Server (see Article #2182). However, if you're stuck with Access and need more security that this won't happen, you can use a Recordset object with an adOpenKeyset cursor (this is one of those rare scenarios where a Recordset object actually makes more sense than a direct T-SQL statement):

<%
fakeValue = 5
set conn = CreateObject("ADODB.Connection")
conn.open "<conn string<"
set rs = CreateObject("ADODB.Recordset")
rs.open "SELECT [intColumn] from someTable where 1=0", conn, 1, 3
rs.AddNew
rs("intColumn") = fakeValue
rs.update
response.write "New ID was " & rs("id")
rs.close: set rs = nothing
conn.close: set conn = nothing
%>


You can also check out KB #221931, which has an officially endorsed code sample for retrieving the AUTOINCREMENT value from an Access database.

Source:http://databases.aspfaq.com/general/how-do-i-get-the-identity/autonumber-value-for-the-row-i-inserted.html

Storing Uploaded Files in a Database or in the File System with ASP.NET 2.0

A common requirement in web sites is handling client files that are uploaded through the browser. Whether these files come from a protected Admin section where content managers can upload files, or from a public section doesn't matter; you need a way to retrieve and store these files in your system somewhere so they are available for viewers.

Allowing a user to upload a file is easy; just drop a FileUpload control on your page and .NET will handle the rest for you. However, it's after the file gets uploaded to the server where things get interesting. One of the things you have to take into account is where you're going to store the uploaded file. Two common places are the hard drive of your server and a database. There has been a lot of debate on the Internet about the best way to store your files. Some say the file system is the only acceptable option, while others really like the database solution.

In this article I'll show you how to store files both ways. I'll discuss the pros and cons of each solution, and show you the code you need to save your uploaded files. As a sample application, I'll build a small web site that allows you to upload files and store them at a location that you determine. You can decide where the files are stored through a simple setting in the web.config file. Depending on this setting, files are either stored directly at the hard drive of your server or in a database. You can even change this setting at run-time without affecting existing files.

Introduction

UPDATE 2006/01/17: I added the VB version of the code to the Downloads section

Right now, the article and the code download are in C# only, but I am working on a Visual Basic version of the application as well. Send me a nice e-mail (1) if you want me to hurry up...

I converted the code to Visual Basic .NET using DotNetTaxi's Code converter (2) and then manually fixed a few remaining issues. I tested the application and everything seems to work fine. If you do find an issue, please let me know (3). The full VB code is available in the Downloads section at the end of this article.

Besides showing how to store files in the database and retrieve them again, this article also presents a little demo application that demonstrates the concepts from this article. Instead of performing all the code directly in the code behind of the ASPX pages, this application uses a number of separate classes that each perform a limited set of tasks. If you're not interested in the design of the application or the pros and cons of storing files in the database, you skip directly to the code that shows you how to save files in the database (4) and retrieve them again (5).

Before we start looking at the application and it's code, it's a good idea to briefly look at the pros and cons of both solution. Since so many people have a strong favor for one of the two solutions, there must be interesting differences between the two. Let's look at storing the files in the file system first.

File System - Advantages

One of the main benefits of storing the file on disk is that it's very easy to do. Just call SaveAs on a FileUpload control and you're pretty much done.

Another advantage is that files on disk are easy to backup; you just copy the files to another location. This also makes it easier to do incremental backups; files that have already been backed up don't need to be copied again.

FileSystem - Disadvantages

Storing your files in the file system has a few disadvantages as well. Probably the most problematic issue is the loosely coupled nature of the files on disk. They have no strong relation with a record in the database. So, when you delete, say, a product from the database, you may end up with an orphaned product image. There is no direct way to do an INNER JOIN between the product table and your images folder to determine what orphaned files you have left. This means that a page developer is responsible for writing code that deletes the file from disk whenever the associated database records gets deleted.

Also, to store uploaded files on disk, your web server needs permissions (6) to write to the file system. This is easy to come by (7) when you run your own server, but may prove to be more problematic in an ISP scenario.

Database - Advantages

Of course some of the advantages of a database are the exact opposite of the disadvantages of saving them as physical files: since they are stored in the database, they're easy to relate to other records. They can be retrieved in JOIN style queries, and even be deleted automatically with cascading delete turned on. You also don't need additional permissions on the server; if you can write to the database, you can store files in it.

But another advantage of storing your files in a database is the fact that all data is contained in a single location. Make a backup of your database, and you have everything you need. That makes it a lot easier to move your data to another server; other than the database, you don't need to copy files, set up permissions and so on.

Database - Disadvantages

At the top of the list of disadvantages of storing your files in a database is probably performance. While I don't have any hard figures to support this, the "word is" that it's slow. How slow may depend on your situation, the type of files you have, the server, and so on.

Another downside is the lack of easy access to the files. When you store them on disk, it's easy to download them to your desktop machine and batch process them; for example, use an imaging program to scale or rotate all your images. When you use a database, you need to "materialize" them to disk first and upload them again afterwards.

A final problem with the database is backups. Whenever you make a full backup of your database, all the files are included, whether they have been changed or not. If you copy your backups to a different machine or network for safety reasons, this could be problematic as you need to move the entire backup file. With a file based solution, you can use diff programs that can determine which files have been changed since the last backup, and only download those.

Realizing that both methods have their pros and their cons, the question is: what to choose? Personally, I favor storing them on disk. The accessibility of the files on disk, combined with the simple coding model makes the disk solution a better one in many situations. However, I have also used the database solution at places where that made more sense. Bottom line is that you need to carefully examine your scenario and determine what works for you.

The solution I am presenting in this article shows you how to do both; with a simple option in the web site's config file, you can switch between a file based and a database solution.

Storing the Files

When you store the files on disk, things are pretty easy. The SaveAs method on the PostedFile does all the work for you, provided you set the right security settings (8). Storing them in a database isn't too hard either, but you need to be aware of a few things. First of all, you need to know what data type to use to store your files. With SQL Server 2000, you can use the image data type. Although its name suggests you can only use it to store image files, this is not true. You can store text files, Word documents, spread sheets or any other file type you have. With SQL Server 2005, you can use the new varbinary(max) data type. The sample application that comes with this article uses a SQL Server 2005 Express database and thus the varbinary(max) data type. If you want to use SQL Server 2000 instead, simply replace each occurrence of varbinary(max)with the image data type, including the FileData column in the Files table.

Additionally, you need to know the code to access the database to store and retrieve the files. You'll see a lot more of that right after I discussed the demo application and its feature set.

The Demo Application - A Simple File Manager

The demo application used in the article is a simple web site that allows you to upload, download and view files. With an optional switch in the web.config file, you can determine whether the files are stored in the database or on disk. The code for the entire application is available in the Download Files (9) section at the end of the article.

The application contains four .cs files in the App_Code folder, a SQL Server 2005 Express database, four .ASPX files with code behind and a web.config file, all of them visible in Figure 1.

Figure 1 - The Solution Explorer for the Project

In this article, I'll show what each file is used for and what code it contains. I won't discuss each and every line of code in the application, but instead focus on the important concepts. You're encouraged to download (10) the application so you can see the full source, and play around with it.

To keep the discussion focused, the design of the application is extremely simple. It features a page with a standard GridView that displays the files that have been upload:

Figure 2 - A GridView Showing the Uploaded Files

Each uploaded file always has a Download link to allow a user to download the file from the server. For files that can be viewed in the browser, there's also a View link. This works only for files like images, Word documents and text files in the demo application, but it's easy to extend this to other files, like Excel spreadsheets.

If you click the Add New File button, you can upload a new file which is then shown in the GridView in Figure 2.

The Demo Application - Class Design

Instead of doing all the code logic in the page's code behind, the application uses a class based design, where each class has a strong focus on a single task. Let's take a look at the class design for the application:

Figure 3 - The Class Diagram for the Sample Application

The class diagram in Figure 3 contains three classes and one enumeration. I'll discuss all four of them in the following section.

The File class

The File class represents an uploaded file and is used to store and retrieve uploaded files on disk and in a database. The database is used in both scenario's. Even when you decide to save the files on disk, some meta data of the file is still stored in the database. The File class has properties to store the ContentType of a file (like image/jpeg, application/msword etc), the date it was created (DateCreated), its unique ID in the database and the original name of the file when it was uploaded (OriginalName). It also contains a FileUrl which is used when files are stored on disk, and a FileData property which contains the actual file when it's retrieved from the database. The last property of the File class is ContainsFile, which indicates whether the file holds the bytes for the uploaded file in FileData, or that it contains a virtual path to the file on disk in FileUrl.

It also contains two constructors and two methods: one to get a file (from the database or from disk) and one to save the file. This Save method has two public overloads and one private version that performs the actual save operation.

The FileInfo class

The FileInfo class serves as a lightweight summary object for the file. A FileInfo object basically contains the meta data of the file, but without the actual file or virtual path to it. This is useful in scenarios where you want to display a list of files, as in Figure 2. In such a case, it doesn't make much sense to have each item in the GridView contain the actual file, as you aren't doing anything with it. By using a lightweight FileInfo object, you can save some overhead and speed up your application.

The AppConfiguration Class

If you're familiar with my latest book ASP.NET 2.0 Instant Results (11), you should be familiar with this class. It's a static class that's essentially a wrapper around some settings in the web.config. While you can certainly access the web.config file from code directly, I prefer to wrap them in a static class with static properties, so I have easy access to them and get Intelli Sense on them.

The ConnectionString property defines the connection string to the SQL Server database (a SQL Server 2005 Express database in the demo application), while UploadsFolder contains a virtual path to the folder where uploaded files are stored (for example: ~/Uploads)

The final property of this class is DataStoreType which determines the location where to store the files. The DataStoreType enumeration is discussed next.

The DataStoreType Enum

The DataStoreType enumeration contains two members: Database and FileSystem. When the application is configured to use Database, all files are stored in the SQL Server 2005 database, When FileSystem is specified, then the meta data is still stored in the database, while the actual files are saved to disk.

Uploading Files

OK, enough for the theory, let's look at some code.

When you click the Add New File button you see in Figure 2, you're taken to UploadFile.aspx. The markup of this page is extremely simple and only contains an control and a Button. When you select a file and then click the Upload File button, the following code is executed:

protected void btnUpload_Click(object sender, EventArgs e)
{

  if (FileUpload1.HasFile)
{
 string contentType = FileUpload1.PostedFile.ContentType;

 // Get the bytes from the uploaded file
 byte[] fileData = new byte[FileUpload1.PostedFile.InputStream.Length];
 FileUpload1.PostedFile.InputStream.Read(fileData, 0, fileData.Length);

 // Get the name without folder information from the uploaded file.
 string originalName = Path.GetFileName(FileUpload1.PostedFile.FileName);

 // Create a new instance of the File class based on the uploaded file.
 File myFile = new File(contentType, originalName, fileData);

 // Save the file, and tell the Save method what data store to use.
 switch (AppConfiguration.DataStoreType)
 {
   case DataStoreType.Database:
     myFile.Save();
     break;
   case DataStoreType.FileSystem:
     myFile.Save(Server.MapPath(Path.Combine(
     AppConfiguration.UploadsFolder, myFile.FileUrl)));
     break;
 }
 Response.Redirect("~/");
}  

}

(From: UploadFile.aspx.cs)

The code first checks if a file has been uploaded by looking at the HasFile property. If that's the case, its content type is derived from the ContentType property of the PostedFile.

Next, a byte array is created from the uploaded file. First, the array is dimensioned to the length of the uploaded file and then it's filled by calling Read on the InputStream of the PostedFile. At this stage, fileData contains the actual bytes of the file the user has uploaded.

Then the original name is retrieved. By default, PostedFile.FileName contains the full path and name of the file at the client's computer. Since we're only interested in the file's name (and extension) and not in the original path, Path.GetFileName is used to strip off the path.

Then a new File object is constructed with a constructor that accepts the content type, the file name and the bytes for the file. Inside this constructor, the parameters are stored in the class's backing variables, like this:

public File(string contentType, string originalName, byte[] fileData)
{
this.id = Guid.NewGuid();
this.contentType = contentType;
this.fileData = fileData;
this.originalName = originalName;

string extension = Path.GetExtension(originalName);
string fileName = this.Id.ToString() + extension;

this.fileUrl = fileName;
}

(From: File.cs)

Notice how the constructor builds up the unique fileUrl for the file, by adding a Guid and the file's extension together. This way, each file ends up with a unique name so you don't have to worry about files being accidentally overwritten. Since the original name of the file is always stored in the database, it's easy to use that name again when the file is downloaded. You'll see how this works later.

Once the new File instance is ready, it's saved by calling Save(). There are two public overloads of the Save method; a parameterless version that saves the file in the database and an overloaded version that accepts the physical location of where the file must be saved. Notice that the correct overload is chosen based on the DataStoreType:

// Save the file, and tell the Save method what data store to use.
switch (AppConfiguration.DataStoreType)
{
case DataStoreType.Database:
 myFile.Save();
 break;

case DataStoreType.FileSystem:
 myFile.Save(Server.MapPath(Path.Combine(
       AppConfiguration.UploadsFolder, myFile.FileUrl)));
 break;
}

(From: UploadFile.aspx.cs)

Both of the two public versions of the Save method call another, private overload that accepts a DataStoreType and the physical location of the file. This version of the Save method is discussed next.

Saving the File

The Save method contains a lot of code, but most of it is pretty standard ADO.NET code:

private bool Save(DataStoreType dataStoreType, string filePath)
{
using (SqlConnection mySqlConnection = new
       SqlConnection(AppConfiguration.ConnectionString))
{
 // Set up the Command object
 SqlCommand myCommand = new SqlCommand(
       "sprocFilesInsertSingleItem", mySqlConnection);
 myCommand.CommandType = CommandType.StoredProcedure;

 // Set up the ID parameter
 SqlParameter prmId = new SqlParameter("@id",
     SqlDbType.UniqueIdentifier);

 prmId.Value = id;
 myCommand.Parameters.Add(prmId);

 // Set up the FileData parameter
 SqlParameter prmFileData = new SqlParameter("@fileData ",
       SqlDbType.VarBinary);
 // If we need to store the file in the database,
 // pass in the actual file bytes.
 if (dataStoreType == DataStoreType.Database)
 {
   prmFileData.Value = fileData;
   prmFileData.Size = fileData.Length;
 }
 else
 {
   prmFileData.Value = DBNull.Value;
 }
 myCommand.Parameters.Add(prmFileData);

 // Set up the FileUrl parameter
 SqlParameter prmFileUrl = new SqlParameter("@fileUrl",
     SqlDbType.NVarChar, 255);
 // If we need to store the file on disk, save the fileUrl.
 if (dataStoreType == DataStoreType.FileSystem)
 {
   prmFileUrl.Value = fileUrl;
 }
 else
 {
   prmFileUrl.Value = DBNull.Value;
 }
 myCommand.Parameters.Add(prmFileUrl);

 // Set up the OriginalName parameter
 SqlParameter prmOriginalName = new SqlParameter("@originalName",
       SqlDbType.NVarChar, 50);
 prmOriginalName.Value = originalName;
 myCommand.Parameters.Add(prmOriginalName);

 // Set up the ContentType parameter
 SqlParameter prmContentType = new SqlParameter("@contentType",
       SqlDbType.NVarChar, 50);
 prmContentType.Value = contentType;
 myCommand.Parameters.Add(prmContentType);

 // Execute the command, and clean up.
 mySqlConnection.Open();
 bool result = myCommand.ExecuteNonQuery() > 0;
 mySqlConnection.Close();

 // Save the file to disk if necessary; shown later

 return result;
}
}

(From: File.cs)

This code creates a SqlConnection and a SqlCommand object and then creates a number of parameters. Most of them are pretty straight forward, but the fileUrl and fileData parameters need a bit more explanation (highlighted in the code above). When the DataStoreType passed in equals Database, it means the actual file is stored in the database. In that case, the fileData property is assigned to the value of the prmFileData parameter. The type of that parameter is SqlDbType.VarBinary, which corresponds to the data type of the column in the database. (When you're using SQL Server 2000, you should use SqlDbType.Image instead.) Since the file has already been converted to a byte array in the calling code, all you need to do here is assign the fileData variable to the parameter's Value property.

In the else clause (when the FileSystem option is used), DBNull.Value is passed instead, so null is stored in the database.

The FileUrl parameter takes the opposite approach and only passes its value when DataStoreType is FileSystem.

Finally, the Command object is executed against an open database connection and the result of the ExecuteNonQuery call is stored in a temporary variable.

Notice that when the FileSystem is used, the Save method also saves the file to disk:

// Database update is done; now store the file on disk if we need to.
if (dataStoreType == DataStoreType.FileSystem)
{
const int myBufferSize = 1024;
Stream myInputStream = new MemoryStream(fileData);
Stream myOutputStream = System.IO.File.OpenWrite(filePath);
         byte[] buffer = new Byte[myBufferSize];
int numbytes;
while ((numbytes = myInputStream.Read(buffer, 0, myBufferSize)) > 0)
{
 myOutputStream.Write(buffer, 0, numbytes);
}
myInputStream.Close();
myOutputStream.Close();
} 

(From: File.cs)

To save the file, the byte array is converted back to a Stream again, which is then written to disk using another Stream object. For the file name, this code uses the filePath parameter passed to the Save method.

As an alternative to this solution, you can decide to call the SaveAs method on the FileUpload control in the code behind of UploadFile.aspx. This is easier, as the SaveAs method takes care of it all; all you need to do is pass a file name. However, it also means you're spreading logic across your application. By encapsulating all the code into a single File class, your application becomes easier to maintain and extend.

As another alternative, you could pass the entire FileUpload control to the Save method, and have that method call its SaveAs method. Personally, I don't like that solution too much, as it ties the File class to a WebControl, which makes it harder to reuse it in other type of applications.

The stored procedure that is used in the Save method is really simple; it performs a simple INSERT operation in the File table:

CREATE PROCEDURE sprocFilesInsertSingleItem

@id uniqueidentifier,
@fileUrl nvarchar(255),
@fileData varbinary (max),
@originalName nvarchar(50),
@contentType nvarchar(50)

AS

INSERT INTO Files
(
Id,
FileUrl,
FileData,
OriginalName,
ContentType
)
VALUES
(
@id,
@FileUrl,
@FileData,
@originalName,
@contentType
)

(From: sprocFilesInsertSingleItem)

Again, the varbinary(max) data type is used for the fileData parameter, to line up with the SqlParameter and the column for the file in the database as you saw earlier.

With the Save operation done, the next thing to look at is displaying files.

Displaying the Files

To display files in a web browser, there are two scenario's to consider: displaying lists of files, and displaying or downloading a single file. I'll look at the list first, followed by the single file.

Viewing a List of Files

The page Default.aspx, that displays a list of files that can be downloaded or viewed, contains a simple GridView with a few columns that display the file's unique Id, original name, content type and the date and time it was uploaded. It also contains two columns that allow you to download a file and - in certain circumstances - view the file in-line in the browser. In Figure 2 you can see that only some files have the View link enabled. Some code in the code behind looks at the content type of the file in each row, and enables the View link when the file can be viewed in-line in the browser. That's the case for images (jpg, gif for example) and other files that the browser can usually handle (Word files, Excel spreadsheets). Other files that can't be viewed directly, but need a separate application (Zip and Rar files for example) can only be downloaded. You'll see the code for this a little later.

The GridView gets its data from an ObjectDataSource control that is tied to the FileInfo class with its GetList method. You could opt to implement the GetList method in the File class and remove the entire FileInfo class. However, I have chosen to implement it like this for performance reasons. In most scenarios, when you display a list of files, you don't need the actual files; all you need is some meta data like the name and unique ID. By implementing a simple, and read-only class FileInfo, you get all the data you need without the overhead of retrieving all the files from the database.

Because all the work is done by the GetList method of the FileInfo class, the ObjectDataSource is very simple:

   

(From: Default.aspx)

When this control gets its data, it calls the static GetList method that returns a generics List of FileInfo objects:

public static List GetList()
{
List myList = null;

using (SqlConnection mySqlConnection = new SqlConnection(
           AppConfiguration.ConnectionString))
{
 SqlCommand myCommand = new SqlCommand("sprocFileInfoSelectList",
           mySqlConnection);
 myCommand.CommandType = CommandType.StoredProcedure;

 mySqlConnection.Open();

 using (SqlDataReader myReader = myCommand.ExecuteReader())
 {
   if (myReader.HasRows)
   {
     myList = new List();
     while (myReader.Read())
     {
          myList.Add(new FileInfo(myReader));
     }
   }
 }

 mySqlConnection.Close();
}
return myList;
}   

(From: FileInfo.cs)

Again, most of this code is pretty straight forward. A SqlCommand executes the stored procedure sprocFileInfoSelectList to get the data from the database. Each record retrieved from the database is transformed into a FileInfo object that is added to a generics List. To make the code a bit more readable, the FileInfo class has a constructor that accepts a SqlDataReader. This way, you only need a single line of code in the while loop to create a FileInfo instance and add it to the list. The constructor that accepts the SqlDataReader looks like this:

public FileInfo(SqlDataReader myReader)
{
id = myReader.GetGuid(myReader.GetOrdinal("Id"));
dateCreated = myReader.GetDateTime(myReader.GetOrdinal("DateCreated"));
originalName = myReader.GetString(myReader.GetOrdinal("OriginalName"));
contentType = myReader.GetString(myReader.GetOrdinal("ContentType"));
}

(From: FileInfo.cs)

Each of the four fields of the FileInfo class are filled with a value from the SqlDataReader. Since all fields are required in the database, there's no need to check for null values.

At the end of the GetList method, the list with FileInfo objects is returned to the calling code where it's bound to the GridView. For each FileInfo object that is added to the GridView, it fires its RowDataBound event that is used to determine whether the View link should be enabled or not:

protected void GridView1_RowDataBound(object sender,
         GridViewRowEventArgs e)
{
switch (e.Row.RowType)
{
 case DataControlRowType.DataRow:
   FileInfo myFileInfo = (FileInfo) e.Row.DataItem;
   switch (myFileInfo.ContentType.ToLower())
   {
     case "image/pjpeg":
     case "image/gif":
     case "application/msword":
     case "text/plain":
       // Do nothing. When the row contains a viewable type,
       // we want the View link to be enabled.
       break;
   default:
     // Find the View link and disable it.
     HyperLink myLink = (HyperLink)e.Row.FindControl("lnkView");
     myLink.Enabled = false;
     break;
   }
 break;
}
}

(From: Default.aspx.cs)  

Whenever a DataRow is added, a FileInfo object is retrieved from the DataItem property of e.Row. The FileInfo's ContentType is then used to determine whether the file is viewable in the browser or not. In the example above, this is only done for .jpg, .gif, .doc and .txt files but you can easily add additional case statements to the switch block.

For all other files, the link is retrieved with the FindControl method of the row and then its Enabled property is set to false.

Downloading or Viewing a Single File

When you click the Download or View link, you're take to DownloadFile.aspx or ViewFile.aspx respectively. The code behind of these files are pretty similar, but there are a few interesting differences worth looking at.

Both pages start by getting a File object from the database by calling File.GetItem. This method is pretty straight forward, and uses the same principle as the FileInfo class by implementing a constructor that accepts a SqlDataReader object to fill the private fields:

public File(SqlDataReader myReader)
{
id = myReader.GetGuid(myReader.GetOrdinal("Id"));
dateCreated = myReader.GetDateTime(myReader.GetOrdinal("DateCreated"));
originalName = myReader.GetString(myReader.GetOrdinal("OriginalName"));
contentType = myReader.GetString(myReader.GetOrdinal("ContentType"));

if (!myReader.IsDBNull(myReader.GetOrdinal("FileData")))
{
 fileData = (byte[])myReader[myReader.GetOrdinal("FileData")];
 containsFile = true;
}
else
{
 fileUrl = myReader.GetString(myReader.GetOrdinal("FileUrl"));
 containsFile = false;
}
}  

(From: File.cs)  

The first four fields are retrieved directly from the reader. The code then checks if the column FileData has a value that isn't null. If that's the case, the value is casted to a byte array and assigned to the fileData field. Notice that containsFile is also set to true to indicate that the File instance contains the actual file bytes. The public ContainsFile property is used in the download and view pages to determine whether the File instance contains the actual file bytes, or that they should search for the files on the server's hard drive.

In the else clause, the fileUrl (with the virtual path to the file on disk, starting from the virtual Uploads folder) is set, and containsFile gets a value of false.

Once the File object is returned, DownloadFile.aspx sets Response.ContentType to application/x-unknown and appends an additional header that contains the original name of the file. Then it uses either the BinaryWrite or the WriteFile method of the Response object, based on the fact whether the File object contains the actual file data:

Response.ContentType = "application/x-unknown";
Response.AppendHeader("Content-Disposition",
   "attachment; filename=\"" + myFile.OriginalName + "\"");
if (myFile.ContainsFile)
{
Response.BinaryWrite(myFile.FileData);
}
else
{
Response.WriteFile(Path.Combine(AppConfiguration.UploadsFolder, myFile.FileUrl));
}

(From: DownloadFile.aspx.cs)

In both cases, this forces the browser to display a File Download dialog that allows the user to save the file to disk :

Figure 4 - The File Download Dialog

The ViewFile.aspx page takes a similar approach, but first sets the ContentType that it retrieves from the File instance. It then switches between BinaryWrite and Redirect to send the file to the browser where it's displayed in line:

Response.ContentType = myFile.ContentType;
if (myFile.ContainsFile)
{
Response.BinaryWrite(myFile.FileData);
}
else
{
Response.Redirect(Path.Combine(AppConfiguration.UploadsFolder, myFile.FileUrl));
}

(From: ViewFile.aspx.cs)  

With the code to download or view an uploaded file, we've come full circle. You can now upload files to the server and store them on disk or in a database depending on your own preferences or requirements. You can list the files in a GridView and offer your users a way to download or display them.

Summary

Storing your uploaded files in a SQL Server database can be very convenient. It allows you to easily relate the uploaded files to other records in the database. However, there are also some disadvantages that you need to be aware of. One is performance, while another is increased backup-time. Whether you should save your files on disk or in a database depends on your own preferences and requirements.

This article showed you how to upload your files and store them in a database or on disk depending on a simple configuration switch. When you store them in a database, you can use the Image or the varbinary(max)data type. When you store them on disk, you need to ensure that the account used by the web server has sufficient permissions to write to the Uploads folder.

At the end of the article I showed you the code to either download a file or view it directly in the browser. Viewing it in the browser is not possible for every type of file. For example, .zip files cannot be viewed directly so they can only be downloaded. The code in the code behind of Default.aspx determines whether the View link must be enabled based on the file's ContentType property.

Source: http://imar.spaanjaars.com/QuickDocId.aspx?quickdoc=414

Storing Binary Files Directly in the Database Using ASP.NET 2.0

---

Introduction

In building a data-driven application, oftentimes both text and binary data needs to be captured. Applications might need
to store images, PDFs, Word documents, or other binary data. Such binary data can be stored in one of two ways:
on the web server's file system, with a reference to the file in the database; or directly in the database itself.

Text data - things like
strings, numbers, dates, GUIDs, currency values, and so on - all have appropriate and corresponding data types defined in
the database system being used. With Microsoft SQL Server, for example, to store an integer value you'd use the int
data type; to store a string value you would likely use a column of type varchar or nvarchar.
Databases also have types defined to hold binary data. In Microsoft SQL Server 2000 and earlier, use the
image data type; for SQL Server 2005, use
the varbinary(MAX) data type. In either
case, these data types can hold binary data up to 2GB in size.

When storing binary data directly in the database, a bit of extra work is required to insert, update, and retrieve the
binary data. Fortunately, the complex, low-level T-SQL needed to perform this work is neatly abstracted away through
higher-level data access libraries, like ADO.NET. Regardless, working with binary data through ADO.NET is a bit different
than working with text data. In this article we will examine how to use ADO.NET and the ASP.NET
2.0 SqlDataSource control
to store and retrieve image files directly from a database. Read on to learn more!

Storing Data in the Database vs. Storing it in the File System

As mentioned in the Introduction, when capturing binary data in an application the binary data can either be stored directly
in the database or saved as a file on the web server's file system with just a reference to the file in the database. In my
experience, I've found that most developers prefer storing binary data on the file system for the following reasons:

• It requires less work - storing and retrieving binary data stored within the database involves a bit more code than
  when working with the data through the file system. It's also easier to update the binary data - no need for talking to
  the database, just overwrite the file!


• The URL to the files is more straightforward - as we'll see in this article, in order to provide access to binary
  data stored within a database, we need to create another ASP.NET page that will return the data. This page is typically passed
  a unique identifier for the record in the database whose binary data is to be returned. The net result is that to access
  the binary data - say an uploaded image - the URL would look something like http://www.yourserver.com/ShowImage.aspx?ID=4352,
  whereas if the image were stored directly on the file system, the URL would be more straightforward, such as:
  http://www.yourserver.com/UploadedImages/Sam.jpg.



• Better tool support for displaying images - if you're using ASP.NET 2.0, the ImageField can be used in the GridView
  or DetailsView to display an image given the path to the image from the database. The ImageField, unfortunately, will not
  display image data directly from the database (since it requires an external page to query and return that data).


• Performance - since the binary files are stored on the web server's file system rather than on the database,
  the application is accessing less data from the database, reducing the demand on the database and lessening the network congestion
  between the web and database server.

The main advantage to storing the data directly in the database is that it makes the data "self-contained". Since all of the
data is contained within the database, backing up the data, moving the data from one database server to another,
replicating the database, and so on, is much easier because there's no worry about copying over or backing up the binary content
stored in the file system.

As always, what choice you make depends on the use case scenarios and business needs. For example, I've worked with one client
where the binary data had to be stored in the database because the reporting software they used could only include binary data
in the report if it came from the database. In another case, a colleague of mine worked on a project where the binary files
needed to be available to the web application and available via FTP, which necessitated storing the binary data in
the file system.

Creating a Database Table to Store Binary Data


The remainder of this article explores a simple ASP.NET 2.0 image gallery application I wrote that uses Microsoft SQL Server 2005
Express Edition illustrate the concepts involved in storing and retrieving binary data directly from a database. The working
demo application - along with the complete source code and database files - is available to download at the end of this article.

The image
gallery application's data model consists of one table, Pictures, with a record for each picture in the gallery.
The Pictures 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 ImageData column holds the actual binary contents
of the picture.

Uploading an Image and Using ADO.NET Code to Store the Binary Data

The image gallery allows visitors to upload picture files - GIFs, JPGs, and PNGs - to the application. Once uploaded, a new record
is added to the Pictures table and the image file's contents are stored in that new record's ImageData column.
To upload files from the web browser to the web server in ASP.NET 2.0, use the FileUpload
control. Working with the FileUpload control is a walk in the park - just drag it onto your page from the Toolbox. The FileUpload
control renders as the standard file upload in the user's browser - a Browse button that, when clicked, allows the user to select a single
from from their hard drive to upload to the web server.

For example, to create an interface for adding a new image, I used a TextBox to capture the picture's title and a FileUpload
control to allow the user to specify the image to upload:

&lt;b>Title:</b> <asp:TextBox ID="PictureTitle" runat="server" /> <br /> <b>Picture:</b> <asp:FileUpload ID="UploadedFile" runat="server" /> <br /> <asp:LinkButton ID="btnInsert" runat="server" Text="Insert" /> <asp:LinkButton ID="btnCancel" runat="server" Text="Cancel" />

This results in a page from which the user can specify a file from their hard drive to upload to the web server.

Once the user has selected a file and posted back the form (by clicking the "Insert" button, for example), 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, as the following markup and code illustrates:

Protected Sub btnInsert_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnInsert.Click 'Make sure a file has been successfully uploaded If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then ... Show error message ... Exit Sub End If 'Make sure we are dealing with a JPG or GIF file Dim extension As String = Path.GetExtension(UploadedFile.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 ... Show error message ... 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], [ImageData]) VALUES (@Title, @MIMEType, @ImageData)" Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@Title", PictureTitle.Text.Trim()) myCommand.Parameters.AddWithValue("@MIMEType", MIMEType) 'Load FileUpload's InputStream into Byte array Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length) myCommand.Parameters.AddWithValue("@ImageData", imageBytes) myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() End Using End Sub

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. (See the Internet Assigned Numbers Autority's

MIME Media Types listing for a formal list of MIME types.)

The key lines of code to note are those where the @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.

Uploading an Image and Using an ASP.NET 2.0 Data Source Control Code to Store the Binary Data

While the ADO.NET approach will work in an ASP.NET 2.0 application, you can also use ASP.NET 2.0's data source controls
to store binary data in a database, which requires writing no ADO.NET code. The download available at the end of this article
provides an example of using a SqlDataSource control and a DetailsView for adding new pictures to the gallery. (See
Accessing Database Data for more information on using
ASP.NET 2.0's SqlDataSource control.) The SqlDataSource control in this demo contains an InsertCommand and
parameters for the Title, MIMEType, and ImageData values:

<asp:SqlDataSource ID="UploadPictureDataSource" runat="server" ConnectionString="..." InsertCommand="INSERT INTO [Pictures] ([Title], [MIMEType], [ImageData]) VALUES (@Title, @MIMEType, @ImageData)"> <InsertParameters> <asp:Parameter Name="Title" Type="String" /> <asp:Parameter Name="MIMEType" Type="String" /> <asp:Parameter Name="ImageData" /> </InsertParameters> </asp:SqlDataSource>

Note that the ImageData parameter does not have a Type specified. If you attempt to use the GUI
wizard to build the SqlDataSource's syntax, it will likely assign it Type="Object". However, the Type="Object"

results in a parameter type of sql_variant.
sql_variants, however, cannot be used to store image or varbinary(MAX) data types
because the sql_variant's underlying data cannot exceed 8,000 bytes of data. (If you leave in Type="Object"
and then attempt to save binary data that exceeds 8,000 bytes, an exception will be thrown with the message:
Parameter '@ImageData' exceeds the size limit for the sql_variant datatype; if you attempt to add binary
data less than 8,000 bytes, the exception's message will read: Implicit conversion from data type sql_variant to varbinary(max) is not allowed. Use the CONVERT function to run this query..)

The DetailsView contains two TemplateFields - one with a TextBox for the Title column and one with a FileUpload
control for the ImageData column. The net result is a user interface that looks just like the one shown in
the "Uploading an Image and Using ADO.NET Code to Store the Binary Data" section. When the DetailsView's Insert button is clicked, it's Inserting event
fires, at which point the binary data must be taken from the FileUpload control, read into a byte array, and assigned to
the appropriate parameter:

Protected Sub UploadPictureUI_ItemInserting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewInsertEventArgs) Handles UploadPictureUI.ItemInserting 'Reference the FileUpload control Dim UploadedFile As FileUpload = CType(UploadPictureUI.FindControl("UploadedFile"), FileUpload) 'Make sure a file has been successfully uploaded If UploadedFile.PostedFile Is Nothing OrElse String.IsNullOrEmpty(UploadedFile.PostedFile.FileName) OrElse UploadedFile.PostedFile.InputStream Is Nothing Then ... Show error message ... e.Cancel = True Exit Sub End If 'Make sure we are dealing with a JPG or GIF file Dim extension As String = Path.GetExtension(UploadedFile.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 ... Show error message ... e.Cancel = True Exit Sub End Select 'Specify the values for the MIMEType and ImageData parameters e.Values("MIMEType") = MIMEType 'Load FileUpload's InputStream into Byte array Dim imageBytes(UploadedFile.PostedFile.InputStream.Length) As Byte UploadedFile.PostedFile.InputStream.Read(imageBytes, 0, imageBytes.Length) e.Values("ImageData") = imageBytes End Sub

Like with the Insert button's Click event handler in the ADO.NET example from the "Uploading an Image and Using ADO.NET Code to Store the Binary Data" section,
the DetailsView's Inserting event handler performs the same logic with a few minor syntactical differences.
First off, since the FileUpload control is within a template it must be programmatically referenced using the

FindControl("controlID") method. Once it's been referenced, the same checks are applied to ensure that
a file has been uploaded and that its extension is allowed. One small difference with the DetailsView's Inserting event handler
is that if something is awry, we need to inform the DetailsView to stop the insert workflow. This is accomplished by setting the
e.Cancel property to True.

After the checks pass, the MIMEType and ImageData parameters are assigned using the sytnax

e.Values("parameterName") = value. Just like in the ADO.NET example, the binary data is first
read into a byte array and then that byte array is assigned to the parameter.

Displaying the Binary Content

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 ShowPicture.aspx, will be passed a PictureID through the querystring and
return the binary data from the specified product's ImageData field. Once completed, the a particular picture
can be viewed by visiting /ShowPicture.aspx?PictureID=picutreID. Therefore, to display an image on a web page,
we can use an Image control whose ImageUrl property is set to the appropriate URL.

The ShowPicture.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 ImageData

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(ImageData):

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim PictureID As Integer = Convert.ToInt32(Request.QueryString("PictureID")) 'Connect to the database and bring back the image contents & MIME type for the specified picture Using myConnection As New SqlConnection(ConfigurationManager.ConnectionStrings("ImageGalleryConnectionString").ConnectionString) Const SQL As String = "SELECT [MIMEType], [ImageData] FROM [Pictures] WHERE [PictureID] = @PictureID" Dim myCommand As New SqlCommand(SQL, myConnection) myCommand.Parameters.AddWithValue("@PictureID", PictureID) myConnection.Open() Dim myReader As SqlDataReader = myCommand.ExecuteReader If myReader.Read Then Response.ContentType = myReader("MIMEType").ToString() Response.BinaryWrite(myReader("ImageData")) End If myReader.Close() myConnection.Close() End Using End Sub

With the ShowPicture.aspx page complete, the image can be viewed by either directly visiting the URL or through
an Image web control (or via static <img src="ShowPicture.aspx?ProductID=productID" ... /> markup).
The first screen shot below shows an image when viewed directly through ShowPicture.aspx; the second screen shot
shows the Default.aspx page of the image gallery, which uses an Image Web control within a

FormView control, permitting the
user to page through the pictures in the gallery.

Conclusion

When building data-driven applications where binary data must be captured, developers must decide whether to save the binary
on the file system or to store it directly in the database. There are pros and cons to each choice, as discussed in this article.
If you choose to save the binary data within the database, you'll need to take a little extra effort to insert, update,
and retrieve the data. In this article we looked at how to upload image files directly to a database using both ADO.NET code
and the ASP.NET 2.0 SqlDataSource.

Happy Programming!

By Scott Mitchell

Uploading,Saving and Retrieving Binary (image)Files With SQL Server, ASP.Net ,VB

Overview

SQL Server supports the ability for clients to store objects within tables. Generically known as Binary Large Objects (BLOBs), these objects can be complex data types and represent physical file objects. Common uses for this capability within the database layer include storing photos or thumbnail information for personnel databases, and storing specific content for Web sites such as images, or documents that can be retrieved and stored. The benefit of storing this information in binary format in the database is that the data is returned as part of the Tabular Data Stream. This eliminates file-level access and simplifies the overall physical implementation architecture. Also, the binary data can be backed up and restored along with the rest of the database.

Another major advantage of storing documents in the database is SQL Server’s built in capability to Full-Text Index the documents. This provides applications and clients the ability to search for words and phrases inside of a document stored within a table using the Full-Text search predicates built into TSQL (i.e., the CONTAINS operator). When a binary column is Full-Text Indexed in SQL Server, developers can write code using stored procedures that will search within the document, as well as meta data about the document.

To achieve the goal of storing, searching, and retrieving documents from SQL Server, it is important for developers to be able to write interfaces that enable users to place documents into the database as well as retrieve them. The retrieval methods should be provided through application searching interfaces (i.e., Web forms or Windows Forms and stored procedures).

This part of the Saving and Retrieving Binary Files with SQL Server series will focus on the database configuration portion of the solution. The second part of the series will expound upon the application configuration and coding portion of the solution.

SQL Server Setup

In establishing the data layer for allowing an application to store and retrieve binary objects, Microsoft recommends using several data types designed to support binary and text data. Developers will need to know how to best choose the correct data type for the data the application will store.

Data Type Selection

Binary data can be stored in several ways within a SQL Server table. Which storage methods are optimal depends on how much data storage is required for the application. The data types include binary, image, and varbinary, and the difference between these is primarily in their storage capabilities. A binary data type column must hold one row’s worth of data in a SQL Server table (equivalent to 8KB for each row). Similarly, the varbinary data type only allocates the amount of space required for the storage of the object up to 8KB. The relationship between the binary and varbinary is similar to the relationship between the char and varchar data type, in that the varchar column will expand to store the data in the column up to its specified limit.

The image data type is designed to hold data that is greater in size than 8KB. The image data type is the data type of choice for developers interested in storing complex binaries within the database.

Data Model

Since data will be stored in an image data type within a table, developers should give thought to storing additional data along with the binary document. Meta data such as file name, a description of the file, and the name of the user who uploaded the document is particularly valuable information that can be maintained and provided to the application to help users navigate the document store within the database. Additionally, the Full-Text Index feature of SQL Server requires the storage and specification for each document of its file extension to allow for searching.

Typically, in a Third Normal Form schema, this information will exist in an entity of its own, and can potentially be related to other entities within the database. Stored procedures should provide the capability to retrieve and store this information.

Binary Storage and Retrieval Stored Procedures

Binary document storage and retrieval can be accomplished through a two-layer interface from the database perspective. In essence, there should be a layer that solely provides the capability to upload and retrieve documents, and a layer that allows users to search for documents to be retrieved.

The document storage and retrieval stored procedures should be quite compact and elementary. The save stored procedure, for example, should accept an image parameter and other basic parameters dealing with the document meta data. This stored procedure conducts an insert into the table that stores the document and can be written as a simple insert statement using the parameters supplied.

The retrieval methodology should include a stored procedure that contains a series of methods to enable users to search for the document. One method involves maintaining a table of details (an independent table of meta data linked to the document table). The stored procedure can search through these details using the LIKE operator in TSQL. The other approach is to maintain a Full-Text Index on the documents table to allow searching using the CONTAINS operator within the documents. The stored procedure can combine these approaches with direct searches on the meta data using TSQL LIKE operators (i.e., users can search based on their name to see all documents they uploaded, the documents file name, upload dates, and so on). A bit-driven interface into the stored procedure can reflect which type of search should be conducted.

The aforementioned stored procedure should return information about the documents found and the primary key from the documents table. Then, the application can use this key to retrieve the specified document later in the application workflow. Using this approach, a user can search for documents without the overhead of bringing back multiple documents and their associated data. This allows the application to retrieve only the documents that are needed by the search. The application will pass the document primary key into a third stored procedure designed specifically for document data retrieval based specifically on the table’s primary key. The outline of the database objects are as follows:

Database Object	Description
Search Stored Procedure	Returns document search information by searching the meta data in the documents table or associated entities. This stored procedure uses a combination of Full-Text Indexing (by specifying the CONTAINS operator) or traditional TSQL searches (by specifying the LIKE operator). It searches on document-related data including upload dates, file names, and descriptions. This procedure returns the search information along with the document primary key for subsequent document retrieval.
Document Save Stored Procedure	Inserts document data as image data into the database table along with associated meta data, including the file type, file name, upload date/time, and so on.
Document Retrieval Stored Procedure	Retrieves the document from the database table by selecting the image column based on the document primary key input parameter.
Document Table	Contains an image column as well as other traditional columns to store the document binary data as well as supporting meta data. For Full-Text Indexing this column must store the document extension.
Supporting Tables	Tables that are related to the documents table via referential integrity that contain diversified data that can be searched in addition to the document meta data for document retrieval.

Table 1.1: Database Objects for document binary storage.

Implementation

Configuring and developing the database portion of the solution is relatively straight-forward. Much of the complexity of managing the conversion of binaries into the image data type is managed by the application. As a result, creating the data model and the stored procedures to facilitate the movement of documents in and out of the database is as simple as searching for a document via its primary key and returning the image column, or inserting a row from a stored procedure with an image parameter.

The searching component provided by the database is where SQL Server will be relied upon to conduct most of the work. By designing a versatile stored procedure, the application will be able to provide users with multiple methods of document location capability. It is important to undergo the traditional database development lifecycle and take into account concepts such as domain integrity, entity integrity and referential integrity, while paying strict attention to indexing and optimization of search queries. Because searching for documents requires much less flexibility than designing a search in a traditional OLTP application, developers have the luxury of avoiding dynamic SQL.

Additionally, developers and administrators must become familiar with all aspects of Full-Text indexing, including its architecture, the concept of incremental and full population and their scheduling requirements and other important aspects of maintaining a Full-Text Index.

The application structure is defined by multiple layers, each responsible for masking unnecessary complexity from the next highest layer. As a result, the implementation outline is a singular class which is responsible for all transmission to and from the database, and a consuming Web form responsible for obtaining the binary from the user’s machine.

Once the database layer has been established, varying types of applications can make use of the document storage and retrieval capabilities of the data model. This article will concentrate on .NET solutions, and speak abstractly about Web forms.

The general structure of the .NET Solution should revolve around utilizing a class for all document related operations, including binary storage and retrieval as well as searching. The Documents class can then be leveraged across all elements of the solution in this fashion. This class should also leverage a solution-wide implementation of a unified data layer which is responsible for conducting database operations, managing connections and returning formatted results sets in (i.e., DataSets, DataReaders, etc.). The Microsoft Data Access Application Block is a freely available data access layer that can be downloaded from Microsoft. This block will abstract the database interaction with in the Documents class and allow the passing of SqlParameters to the application block’s SqlHelper class.

The goal of the solution will be to obtain the document as a stream (System.IO.Stream) in .NET and serialize the stream to a SqlParameter of the VarBinary type. The Documents class will be responsible for this activity. The purpose of the Web form will be to provide an interface to allow the user to upload the document into an HttpPostedFile object.

Documents Class

The Documents class will serve as the primary facilitator for the transmission of the document to and from the database, as well as the returning of search results based on criteria provided by the user. The Documents class will directly interface with the SqlHelper class provided by the Microsoft Data Access Application Block. The first method of the class will be responsible for adding the document to the database. This method will be called by the Web form when the HttpPostedFile is obtained from the Web page’s upload control. The method will accept a System.IO.Stream which will be read into a System.Byte array. When declaring the Byte variable, the length of the array should be the length of the uploaded document Stream object.

As outlined in the example above, the docData parameter for the function is read into the FileData Byte array using the Read method of the Stream object. It is important to close the docData stream and then assign the FileData Byte array to the value of the DocumentData SqlParameter (defined as a public object in the class). Additional values are assigned to SqlParameters, representing document meta data, including the filename. The file name is later split in the stored procedure to independently store the file extension in a separate column in the documents table. This is a key element in enabling the Full-Text indexing of the document by informing SQL Server of the document type.

Additionally, the function provides the return value from the SQL Server Identity primary key column in the documents table corresponding to the row that was just inserted. This value is returned to the calling Web form and can be used for further processing (i.e., uploading document details and relating them to the data).

The corresponding method to return a specified document from the database based on the document table’s primary key (returned by the search results and selected by the user on the Web interface) utilizes the Byte array as well. The overall strategy employed by this method is to obtain the document as a column and row from the database using a stored procedure into a data reader and place it into a Byte array.

This method accepts the document primary key as an integer and assigns its value to a SqlParameter which is passed to the stored procedure designed to return the document data from the documents table. The stored procedure selects the image column by using the document primary key in its WHERE clause. The method then opens the DataReader returned from the SqlHelper class and defines a Byte array by getting the length of the document in the DataReader. This is accomplished by using the GetBytes method of the DataReader docFileReader to determine its length in bytes.

Once FileData is properly dimensioned, the next step is to read the data from the image column into the FileData array. Using the GetBytes method of the DataReader docFileReader again, we fill the Byte array with the data from the image column housed within the DataReader and return the Byte array FileData to the calling function. This method makes use of a try catch block within the class in order to ensure that the data reader is closed as well as to abstract the logic of Byte array construction from the calling method. The calling function (the Web form) will also encapsulate this method call in a try catch block in order to catch the exception bubbled up from the GetDocument function in its own catch block.

A third method of this class is the simple file search using the aforementioned stored procedure to return a DataSet comprised of search results to the Web form. The Web form will bind this DataSet to a grid which will allow users to select a specific document from their search results and obtain the document using the GetDocument method of the Documents class. This will be an event driven process using the data grid’s item command event to interpret the document selected by the user. The document primary key will be obtained from the DataSet bound to the grid and passed into the GetDocument method in order to retrieve the specified document as a download through the user’s browser.

Web Form

The Web form will contain a method that calls the SaveDocument and GetDocument functions in the Documents class. For facilitating the upload of documents through the Web, the Web form will have a HTML file control that will be run as a server control. The file control will allow the user to browse their local file system and transmit the selected file to the Web server when the Web form posts. To accomplish this task, the Web form houses a private function which is called from the upload control’s post back event. This event accepts the HttpPosted file and instantiates the class. The function then calls the SaveDocument method of the class which sends the document to the database.

            'Load the document to the database

     Dim fileStream As System.IO.Stream

     'Obtain a file stream to send to the stored procedure
     fileStream = upLoad.InputStream

     'Call the class method to save to the database
     documentID = Convert.ToString(doc.AddDocument(fileStream, fn))

Because the SaveDocument method in the Documents class is expecting a System.IO.Stream object, the HttpPostedFile must be converted into this format. As a result, the Web form’s private function to pass the binary to the Documents class assigns the result of the HttpPostedFile.InputStream to a System.IO.Stream variable. The function then returns the document table primary key of the row just inserted into the documents table in the database for further processing.

Implementation

The architecture of the solution is relatively simple. However, attention must be paid to the size of the SQL Server database once users begin to upload documents. Because the binary format of the document is a direct representation of the physical size of the document on the user’s computer, the database which stores the documents can grow quite rapidly. Planning for capacity and storage of the document is required, as well as thea adjustment of several database settings. Chief among those is the assurance that the database growth factor is set appropriately so that SQL Server does not have to grow the database in order to accommodate for increased file sizes (a costly operation).

Additionally, there should be a concerted effort to create and maintain an up-to-date Full-Text Index of all the documents. The Full-Text index is dissimilar to other forms of indexing in that SQL Server does not explicitly manage the Full-Text Index. The database developer must scheduled full and incremental index populations and be vigilant in ensuring that it includes all documents that have been uploaded.

--