In this example I am storing files like word document, text file and excel files into database and retrieving back from database and saving back into a folder.
So lets start creating a Table in SQL Server database in which we are going store our uploaded files, in this table I am creating following columns.
DocumentID int primary key Identity yes
DocumentName varchar(100) name of the document with extension
Document varbinary(MAX) binary column for saving file in Binary format.
Open SQL Server Management Studio and open Northwind database and run following script for creating Table
USE [Northwind]
GO
/****** Object: Table [dbo].[Documents]
Script Date: 06/12/2009 12:11:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Documents](
[DocumentID] [int] IDENTITY(1,1) NOT NULL,
[DocumentName] [varchar](100)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Document] [varbinary](max) NOT NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Now we are ready with our Database creation so lets create a web application
Open Visual Studio and create a Web Application and in Default.aspx add following controls.
One upload control
One Button for uploading
One DropDownList for displaying List of files which are in database already
One Button for opening selected document.
Please refer following HTML for design.
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Stroring and Retrieving File from Database</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Select a Document(.txt,.doc,.xls)<asp:FileUpload ID="FileUpload1"
runat="server" />
<asp:Button ID="btnUpload" runat="server" onclick="btnUpload_Click"
Text="Upload" />
<br />
<br />
Select a Document to Open<asp:DropDownList ID="DropDownList1"
runat="server" AutoPostBack="True">
</asp:DropDownList>
<asp:Button ID="btnOpen" runat="server" onclick="btnOpen_Click" Text="Open" />
</div>
</form>
</body>
</html>
In Page_Load event we have to retrieve all documents which are already stored in database before so I have written a method called FillDropDown to fill the DropDownList
protected void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
FillDropDownList();
}
}
private void FillDropDownList()
{
//Create Data Adapter for reading all documents in database
SqlDataAdapter sqlDa = new SqlDataAdapter(
"Select DocumentID,DocumentName From Documents",
"Server=localhost;Database=Northwind;Trusted_Connection=yes;");
//Create a DataSet and fill the documents information in it
DataSet dsDocuments = new DataSet();
sqlDa.Fill(dsDocuments, "Documents");
//Assign the data source to DropDownList
DropDownList1.DataSource = dsDocuments.Tables[0];
DropDownList1.DataTextField = "DocumentName";
DropDownList1.DataValueField = "DocumentID";
DropDownList1.DataBind();
}
Once user selected a file to upload and click on Upload button we have to store that file in database in Binary format so I am using InputStream of uploaded file and reading into byte array and connection to database and storing the data, Add following code in Upload button's click event
protected void btnUpload_Click(object sender, EventArgs e)
{
try
{
if (FileUpload1.HasFile)
{
//Calculate the size of uploading file
int fileSize = FileUpload1.PostedFile.ContentLength;
//Create an array of byte with file size
byte[] fileBytes = new byte[fileSize];
//Read file as bytes in to fileBytes array
FileUpload1.PostedFile.InputStream.Read(fileBytes, 0, fileSize);
//Create a new SQL Connection to the database
SqlConnection sqlCon = new
SqlConnection("Server=localhost;Database=Northwind;Trusted_Connection=yes;");
//Create a command object for inserting data into Database
SqlCommand sqlCom = new SqlCommand(
"Insert Into Documents(DocumentName,Document)" +
" Values (@DocumentName,@Document)",
sqlCon);
//Add parameters with Values
sqlCom.Parameters.AddWithValue("@DocumentName",
Path.GetFileName(FileUpload1.PostedFile.FileName));
sqlCom.Parameters.AddWithValue("@Document", fileBytes);
//Open Connection
sqlCon.Open();
//Execute Insert command
int retValue = sqlCom.ExecuteNonQuery();
//Dispose memory of used objects
sqlCon.Close(); sqlCom.Dispose(); sqlCon.Dispose(); fileBytes = null;
FillDropDownList();
Response.Write("File uploaded Sucessfully");
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Once user selects one uploaded file click on open I am retrieving the binary data of that file from database and using FileStream object I am saving the a folder, I am created a Folder called Documents in the solution itself and storing files in that folder, if a file already present in the folder it will be over written. please add following code in open button click event.
protected void btnOpen_Click(object sender, EventArgs e)
{
try
{
string strFileName = string.Empty;
//Create a new SQL Connection to the database
SqlConnection sqlCon = new
SqlConnection("Server=localhost;Database=Northwind;Trusted_Connection=yes;");
//Create a command object for reading document from Database
SqlCommand sqlCom = new SqlCommand(
"Select DocumentName,Document from Documents Where DocumentID="
+ DropDownList1.SelectedItem.Value,
sqlCon);
sqlCon.Open();
//Read data into DataReader
SqlDataReader sqlDr = sqlCom.ExecuteReader();
while (sqlDr.Read())
{
strFileName = sqlDr["DocumentName"].ToString();
//Read binary Data into Bytes Array
byte[] fileBytes = (byte[])sqlDr["Document"];
//Create FileStream Object from for saving file
FileStream oFileStream = new FileStream(
Server.MapPath("Documents")
+ @"\" + strFileName,
FileMode.Create);
//SaveFile
oFileStream.Write(fileBytes, 0, fileBytes.Length);
//Dispose the memory of used objects
oFileStream.Close(); oFileStream.Dispose();
fileBytes = null;
}
//Close Reader and Connection
sqlDr.Close(); sqlCon.Close();
//Dispose Memory
sqlDr.Dispose(); sqlCon.Dispose(); sqlCom.Dispose();
if (strFileName != string.Empty)
{
Response.Redirect(@"Documents/" + strFileName);
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
Now we are ready with our code just build, run and test.
Hope this helps.
SatishKumar J
Microsoft MVP(ASP.NET)
|
No responses found. Be the first to respond and make money from revenue sharing program.
|