I have already explained how to save and retrieve files in SQL Server database using ASP.Net in my article Save and Retrieve Files from SQL Server Database using ASP.Net
This article is an extension of the same since was frequently asked on how to save file directly to database using the ASP.Net FileUpload Control
You can upload any files like images, Word document. Excel document, Portable Document Format (PDF), Text Files sand save them to Database
Database Design
Here I have created a Database called dbFiles and it has a table called tblFiles.
It has 4 Fields. The complete description is available in the Figure below
![Table Structure]()
As you can see above for the id field I have set Identity Specification true, so that it automatically increments itself.
|
Field
|
Relevance
|
|
id
|
Identification Number
|
|
Name
|
File Name
|
|
Content Type
|
Content Type for the file
|
|
Data
|
File stored as Binary Data
|
Connection String
Below is the connection string to the database. You can modify it to suit yours
<connectionStrings>
<add name="conString" connectionString="Data Source=.\SQLEXPRESS;database=dbFiles; Integrated Security=true"/>
</connectionStrings >
To start with I have added a FileUpload control, a button and a Label to show messages
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload"
OnClick="btnUpload_Click" />
<br />
<asp:Label ID="lblMessage" runat="server" Text=""
Font-Names = "Arial"></asp:Label>
And here is the snippet which is called on the Upload Button Click event
C#
protected void btnUpload_Click(object sender, EventArgs e)
{
// Read the file and convert it to Byte Array
string filePath = FileUpload1.PostedFile.FileName;
string filename = Path.GetFileName(filePath);
string ext = Path.GetExtension(filename);
string contenttype = String.Empty;
//Set the contenttype based on File Extension
switch(ext)
{
case ".doc":
contenttype = "application/vnd.ms-word";
break;
case ".docx":
contenttype = "application/vnd.ms-word";
break;
case ".xls":
contenttype = "application/vnd.ms-excel";
break;
case ".xlsx":
contenttype = "application/vnd.ms-excel";
break;
case ".jpg":
contenttype = "image/jpg";
break;
case ".png":
contenttype = "image/png";
break;
case ".gif":
contenttype = "image/gif";
break;
case ".pdf":
contenttype = "application/pdf";
break;
}
if (contenttype != String.Empty)
{
Stream fs = FileUpload1.PostedFile.InputStream;
BinaryReader br = new BinaryReader(fs);
Byte[] bytes = br.ReadBytes((Int32)fs.Length);
//insert the file into database
string strQuery = "insert into tblFiles(Name, ContentType, Data)" +
" values (@Name, @ContentType, @Data)";
SqlCommand cmd = new SqlCommand(strQuery);
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename;
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value
= contenttype;
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes;
InsertUpdateData(cmd);
lblMessage.ForeColor = System.Drawing.Color.Green;
lblMessage.Text = "File Uploaded Successfully";
}
else
{
lblMessage.ForeColor = System.Drawing.Color.Red;
lblMessage.Text = "File format not recognised." +
" Upload Image/Word/PDF/Excel formats";
}
}
VB.Net
Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs)
' Read the file and convert it to Byte Array
Dim filePath As String = FileUpload1.PostedFile.FileName
Dim filename As String = Path.GetFileName(filePath)
Dim ext As String = Path.GetExtension(filename)
Dim contenttype As String = String.Empty
'Set the contenttype based on File Extension
Select Case ext
Case ".doc"
contenttype = "application/vnd.ms-word"
Exit Select
Case ".docx"
contenttype = "application/vnd.ms-word"
Exit Select
Case ".xls"
contenttype = "application/vnd.ms-excel"
Exit Select
Case ".xlsx"
contenttype = "application/vnd.ms-excel"
Exit Select
Case ".jpg"
contenttype = "image/jpg"
Exit Select
Case ".png"
contenttype = "image/png"
Exit Select
Case ".gif"
contenttype = "image/gif"
Exit Select
Case ".pdf"
contenttype = "application/pdf"
Exit Select
End Select
If contenttype <> String.Empty Then
Dim fs As Stream = FileUpload1.PostedFile.InputStream
Dim br As New BinaryReader(fs)
Dim bytes As Byte() = br.ReadBytes(fs.Length)
'insert the file into database
Dim strQuery As String = "insert into tblFiles" _
& "(Name, ContentType, Data)" _
& " values (@Name, @ContentType, @Data)"
Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = filename
cmd.Parameters.Add("@ContentType", SqlDbType.VarChar).Value _
= contenttype
cmd.Parameters.Add("@Data", SqlDbType.Binary).Value = bytes
InsertUpdateData(cmd)
lblMessage.ForeColor = System.Drawing.Color.Green
lblMessage.Text = "File Uploaded Successfully"
Else
lblMessage.ForeColor = System.Drawing.Color.Red
lblMessage.Text = "File format not recognised." _
& " Upload Image/Word/PDF/Excel formats"
End If
End Sub
The above code simply reads the uploaded File as Stream and then converts the Stream to Byte array using Binary Reader and then the finally the byte arrays is saved to the database InsertUpdateData method executes the query to save the data in database
The InsertUpdateData function is given below
C#
private Boolean InsertUpdateData(SqlCommand cmd)
{
String strConnString = System.Configuration.ConfigurationManager
.ConnectionStrings["conString"].ConnectionString;
SqlConnection con = new SqlConnection(strConnString);
cmd.CommandType = CommandType.Text;
cmd.Connection = con;
try
{
con.Open();
cmd.ExecuteNonQuery();
return true;
}
catch (Exception ex)
{
Response.Write(ex.Message);
return false;
}
finally
{
con.Close();
con.Dispose();
}
}
VB.Net
Public Function InsertUpdateData(ByVal cmd As SqlCommand) As Boolean
Dim strConnString As String = System.Configuration.
ConfigurationManager.ConnectionStrings("conString").ConnectionString
Dim con As New SqlConnection(strConnString)
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
cmd.ExecuteNonQuery()
Return True
Catch ex As Exception
Response.Write(ex.Message)
Return False
Finally
con.Close()
con.Dispose()
End Try
End Function
This completes the article. You can download the source code in VB.Net and C# from the link below.
Download Code (1.31 mb)
