|
This article is written by a request from a member. In this article we will demonstrate how to upload a file to a database using the OleDb .NET Data Provider. Using the Sql .NET Data Provider is very similar with one exception and that is how to insert the Byte array into the database using OleDb. The following code example demonstrates how to do this. The first bit of code is SQL that should be used to create a new table in the Nortwind database named images. After that is the web form code, and that is followed by the code behind for the web form.
|
|
SQL Code:
|
CREATE TABLE [dbo].[Images] (
[ImageID] [int] IDENTITY (1, 1) NOT NULL ,
[Image] [image] NULL ,
[ContentType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ImageDescription] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ByteSize] [int] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
|
|
Web Form Code:
|
<%@ Page Inherits="UploadSample.Main" src="/Content/admin/DBE57109-11C3-41D9-9791-99B55953605B/upload.cs""%>
<HTML>
<BODY>
<FORM ENCTYPE="multipart/form-data" RUNAT="server" ID="Form1">
<h1>
File Upload To Database Using <FONT COLOR="BLUE">System.Data.OleDb</FONT>
</h1>
<TABLE RUNAT="server" WIDTH="700" ALIGN="left" ID="Table1">
<TR>
<TD>
<B>Upload File</B>
</TD>
<TD>
<INPUT TYPE="file" ID="UP_FILE" RUNAT="server" STYLE="Width:320" ACCEPT="text/*" NAME="UP_FILE">
</TD>
</TR>
<TR>
<TD>
<b>Description of File</b>
</TD>
<TD>
<asp:TextBox RUNAT="server" WIDTH="239" ID="txtDescription" MAINTAINSTATE="false" />
</TD>
</TR>
<TR>
<TD>
<asp:Label RUNAT="server" ID="txtMessage" FORECOLOR="red" MAINTAINSTATE="false" />
</TD>
<TD>
<asp:Button RUNAT="server" WIDTH="239" ONCLICK="Button_Submit" TEXT="Upload Image" />
</TD>
</TR>
</TABLE>
</FORM>
</BODY>
</HTML>
|
|
Web Form Code Behind Code:
|
// created on 9/23/2001
namespace UploadSample {
public class Main : System.Web.UI.Page {
protected System.Web.UI.HtmlControls.HtmlInputFile UP_FILE;
protected System.Web.UI.WebControls.TextBox txtDescription;
protected System.Web.UI.WebControls.Label txtMessage;
protected System.Int32 FileLength = 0;
protected void Button_Submit(System.Object sender, System.EventArgs e) {
System.Web.HttpPostedFile UpFile = UP_FILE.PostedFile;
FileLength = UpFile.ContentLength;
try {
if (FileLength == 0) {
txtMessage.Text = "<b>* You must pick a file to upload</b>";
} else {
System.Byte[] FileByteArray = new System.Byte[FileLength];
System.IO.Stream StreamObject = UpFile.InputStream;
StreamObject.Read(FileByteArray,0,FileLength);
System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
"Integrated Security=SSPI;Initial Catalog=northwind");
System.String SqlCmd = "INSERT INTO Images (Image, ContentType, ImageDescription, ByteSize) VALUES (?, ?, ?, ?)";
System.Data.OleDb.OleDbCommand OleDbCmdObj = new System.Data.OleDb.OleDbCommand(SqlCmd, Con);
OleDbCmdObj.Parameters.Add("@Image", System.Data.OleDb.OleDbType.Binary, FileLength).Value = FileByteArray;
OleDbCmdObj.Parameters.Add("@ContentType", System.Data.OleDb.OleDbType.VarChar,50).Value = UpFile.ContentType;
OleDbCmdObj.Parameters.Add("@ImageDescription", System.Data.OleDb.OleDbType.VarChar,100).Value = txtDescription.Text;
OleDbCmdObj.Parameters.Add("@ByteSize", System.Data.OleDb.OleDbType.VarChar,100).Value = UpFile.ContentLength;
Con.Open();
OleDbCmdObj.ExecuteNonQuery();
Con.Close();
txtMessage.Text = "<p><b>* Your image has been uploaded</b>";
}
} catch (System.Exception ex) {
txtMessage.Text = ex.Message.ToString();
}
}
}
}
|
Most of the code here is strait forward! What Ill be talking about is the Data Access code below:
|
System.Data.OleDb.OleDbConnection Con = new System.Data.OleDb.OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" +
"Integrated Security=SSPI;Initial Catalog=northwind");
System.String SqlCmd = "INSERT INTO Images (Image, ContentType, ImageDescription, ByteSize) VALUES (?, ?, ?, ?)";
System.Data.OleDb.OleDbCommand OleDbCmdObj = new System.Data.OleDb.OleDbCommand(SqlCmd, Con);
OleDbCmdObj.Parameters.Add("@Image", System.Data.OleDb.OleDbType.Binary, FileLength).Value = FileByteArray;
OleDbCmdObj.Parameters.Add("@ContentType", System.Data.OleDb.OleDbType.VarChar,50).Value = UpFile.ContentType;
OleDbCmdObj.Parameters.Add("@ImageDescription", System.Data.OleDb.OleDbType.VarChar,100).Value = txtDescription.Text;
OleDbCmdObj.Parameters.Add("@ByteSize", System.Data.OleDb.OleDbType.VarChar,100).Value = UpFile.ContentLength;
Con.Open();
OleDbCmdObj.ExecuteNonQuery();
Con.Close();
|
|
First, an OleDbConnection is made to Microsoft SQL Servers Northwind database. Next a SQL INSERT command is created. Notice that instead of using the syntax @ParamterName as you do when using the SQL .NET Data Provider, you use traditional OleDb syntax, a ?. An important thing to note is when you create your parameter objects you have to create them in the exact order of the SQL statement. So lets get to what this article is about! Notice the first parameter is the BLOB (Binary Large Object) When using the SQL Provider you can simply use the following code:
|
|
MySqlCmd.Parameters.Add("@Image", SqlDbType.Image).Value = FileByteArray
|
|
This is because the limitation for the Image data type is 2,147,483,647 and most people will not be uploading that size of file into a database, but because there is not OleDbType.Image we have to use OleDbType.Binary DbType that has an 8000 Byte limitation and conversion fails if your byte array exceeds it. So to add the object you must explicitly set the objects size in this example we have the file size held in the FileLength variable:
|
|
OleDbCmdObj.Parameters.Add("@Image", System.Data.OleDb.OleDbType.Binary, FileLength).Value = FileByteArray;
|
|
Easy as pie. In next weeks article well demonstrate how to show the database held image in a web page!
|
|
Read part 2 Displaying Database Stored Images using OleDb .NET Data Provider
|
|