Inserting File And Retriving It Again From SQL Server In Asp.Net
Introduction:
Here we will learn how we can insert the file in database and again how we can retrive the file from database.
On this I have seen so many persons who face the problem like inserting file and again retriving that file from database. This article will demonstrate some basic idea and some basic method to perform our task.
Background:
First inserting files to database is not as easy as inserting some string or integer data. For inserting file we must need to collect this data in Binary fields. Whenever we want to insert the files or images we must need to convert this to byte array which will be inserted in Database.
This article also explains you to use some tier archtecture of designing from which we can avoid the unneccesary errors. How to perform this task I will explain each and everything step-wise follw the steps bellow.
Step’s:
Create Database and table named as Articles and TblUploadDownLoad with following script.
Create Table TblUploadDownLoad
(
FileId Int Primary Key,
[FileName]Varchar(200),
Extension char(10),
FileData VarBinary(max)
)
As well create a stored procedures for adding files, getting filename and id and for getting dataof file on the basis of fileid.
1) For adding Files
Create Procedure Sp_AddFiles
(
@fname Varchar(200),
@ext char(10),
@fdata VarBinary(max)
)
As
Begin
Insert Into TblUploadDownLoad(FileName,Extension,FileData)
Values(@fname,@ext,@fdata)
End
2) For retriving filename and id of file.
Create Procedure Sp_GetFileNames
As
Begin
Select FileId,FileName From TblUploadDownLoad
End
3) For Retriving File Data and extension of the file
Create Procedure Sp_GetFile
(
@fid int
)
As
Begin
Select FileData,Extension From TblUploadDownLoad Where FileId=@fid
End
Now our database work has been done. Now we will create our Data Access Layer Class which will transfer our data from our Logic Class to DataBase and retrive the Data From Database. Here are three method written for particuler task we will see how they will work. Before any task cover your application configuration Here I’m storing my connection string in web.confige file. Just go to confige file and change the connection string according to your settings.
Web.confige
<connectionStrings>
<add name="sqlcn" providerName="System.Data.SqlClient" connectionString="Data Source=Server2;User Id=sa;Password=123;DataBase=ARTICLES"/>
</connectionStrings>
1) Declaration:
static SqlConnection sqlcn;
static SqlCommand sqlcmd;
static SqlDataAdapter sqlda;
static SqlDataReader sqldr;
static int _records;
static string _ext;
public static string Ext
{
get { return DataHelper._ext; }
set { DataHelper._ext = value; }
}
static DataHelper()
{
sqlcn = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlcn"].ConnectionString);// this is stored in web.confige file.
}
public static int Records
{
get { return DataHelper._records; }
set { DataHelper._records = value; }
}
2) Method To Execute The Stored Procedure for adding file to our database. Which will take name of the procedure and required parameter as input.
public static void ExecuteProcedure(string _procedurename, SqlParameter[] sqlparam)
{
try
{
sqlcn.Open();//opening connection
sqlcmd = new SqlCommand(_procedurename, sqlcn);//preparing command //object
foreach (SqlParameter p in sqlparam)
{
sqlcmd.Parameters.Add(p);
//attaching parameters required to procedure.
}
sqlcmd.CommandType = CommandType.StoredProcedure;
_records = sqlcmd.ExecuteNonQuery();
//executing the query
}
catch (Exception)
{
throw;
}
finally
{
sqlcn.Close();
}
}
3) Method to retrive resultset only filenames and id from Database which will return dataset object. You might have a doubt about what is ResultSet it’s nothing new when ever we make request for data to Database first Database will prepare Resultset object and it returns this ResultSet object.
public static DataSet GetFileNames(string _sql, SqlParameter[] Param)
{
try
{
sqlcmd = new SqlCommand(_sql, sqlcn);
foreach (SqlParameter p in Param)
{
sqlcmd.Parameters.Add(p);
}
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcn.Open();
DataSet ds = new DataSet();
sqlda = new SqlDataAdapter(sqlcmd);
sqlda.Fill(ds);
return ds;
}
catch (Exception)
{
throw;
}
finally
{
sqlcn.Close();
}
}
4) Method to retrive the filedata which is in binary form from Database. Which will take Name of the procedure and required parameter as input.
public static byte[] GetFileFromDB(SqlParameter[] Param, string _procname)
{
try
{
byte[] file = null;
sqlcn.Open();
SqlCommand sqlcmd = new SqlCommand(_procname, sqlcn);
sqlcmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter para in Param)
{
sqlcmd.Parameters.Add(para);
}
sqldr = sqlcmd.ExecuteReader();
if (sqldr.Read())
{
file = (byte[])sqldr[0];
_ext = sqldr[1].ToString();
}
return file;
}
catch (Exception)
{
throw;
}
finally
{
sqldr.Close();
sqlcn.Close();
}
}
Now our DataHelper Class is also ready. Now we are ready to consume our DataHelper class. In what way you want to consume it you can. Here I’m using it through one more layer class i.e. Upload.cs which also contain methods to transfer the data from our presentation layer to Data Layer. This class contain three methods for particuler task. Like adding file,getting filenames and id and retriving filedata.
This methods perform their work of transfering data only. Now we will see how that methods work.
1) For adding files you can see here how you can prepare parameter which are required for our stored procedure.
public bool UploadFile(string _filename, byte[] _filedata, string _ext)
{
string _spname="Sp_AddFiles";// our Sp name
SqlParameter[] Param = new SqlParameter[3];
Param[0] = new SqlParameter("@fname", SqlDbType.VarChar, 200);
Param[0].Value = _filename;
Param[1] = new SqlParameter("@ext", SqlDbType.Char, 10);
Param[1].Value = _ext;
Param[2] = new SqlParameter("@fdata", SqlDbType.VarBinary);
Param[2].Value = _filedata;
DataHelper.ExecuteProcedure(_spname, Param);
if (DataHelper.Records > 0)
return true;
else
return false;
}
2) For retriving filenames and Id of files. Here you can see how you can collect the Dataset object return by our DataHelper class.
public DataSet GetFileNames()
{
string _sql = "Sp_GetFileNames";
SqlParameter[] Param = new SqlParameter[0];
DataSet ds = DataHelper.GetFileNames(_sql, Param);
return ds;
}
3) This method is for retriving actual file content which are present in binary form in our database. Our DataHelper class only return the byte array of that binary data. This method takes the fileid as input and again return the byte array.
public byte[] GetFile(int _fid)
{
byte[] _file = null;
SqlParameter[] Param = new SqlParameter[1];
Param[0] = new SqlParameter("@fid", SqlDbType.Int);
Param[0].Value = _fid;
_file = DataHelper.GetFileFromDB(Param, "Sp_GetFile");
_ext = DataHelper.Ext;
return _file;
}
Now our business logic class is also ready now we are ready to transfer and to retrive data from DataHelper and Presentation Layer. Now we will move to Presentation Layer.
1) Design Screen to Add files to Data like bellow.
In button click write this code to read the file selected by user in byte array. And pass this data to our business logic class. First create the object of our business logic class like
Upload obj = new Upload();
Button_click event.
try
{
if (FileUpload1.HasFile)
{
Int32 intfileSize = 0;
Stream fileStream = null;
// Gets the Size of the File
intfileSize = FileUpload1.PostedFile.ContentLength;
// Reads the File
fileStream = FileUpload1.PostedFile.InputStream;
byte[] fileContent = new byte[intfileSize + 1];
int intStatus = 0;
intStatus = fileStream.Read(fileContent, 0, intfileSize);
string ext=System.IO.Path.GetExtension(FileUpload1.FileName.ToString());
if (obj.UploadFile(FileUpload1.FileName.ToString(),fileContent,ext))
{
Label1.Visible = true;
Label1.Text = "File Uploaded";
}
}
}
catch (Exception)
{
Throw;
}
This will read the file data and prepare the byte array as well it calls the Method of our business logic class for inserting the data.
Now we will see how we can retrive this uploded file again just design the screen like bellow.
Now in Page_Load bind the DropDownList with filenames and Id of that file like bellow.
if (!IsPostBack)
{
DataSet ds = obj.GetFileNames();
DropDownList1.DataSource = ds.Tables[0];
DropDownList1.DataTextField = "FileName";
DropDownList1.DataValueField = "FileId";
DropDownList1.DataBind();
}
And in button click write following line of code.
try
{
byte[] fileData = obj.GetFile(int.Parse(DropDownList1.SelectedValue));
Response.ClearContent();
Response.AddHeader("Content-Disposition", "attachment; filename=" + DropDownList1.SelectedItem.Text.ToString());
BinaryWriter bw = new BinaryWriter(Response.OutputStream);
bw.Write(fileData);
bw.Close();
Response.ContentType = ReturnExtension(obj.Ext);
Response.End();
}
catch (Exception)
{
throw;
}
It will ask you to what you want to do either open that file or save that file. And take one look toward the ReturnExtension Method
private string ReturnExtension(string fileExtension)
{
switch (fileExtension)
{
case ".htm":
case ".html":
case ".log":
return "text/HTML";
case ".txt":
return "text/plain";
case ".docx":
return "application/ms-word";
case ".tiff":
case ".tif":
return "image/tiff";
case ".asf":
return "video/x-ms-asf";
case ".avi":
return "video/avi";
case ".zip":
return "application/zip";
case ".xls":
case ".csv":
return "application/vnd.ms-excel";
case ".gif":
return "image/gif";
case ".jpg":
case "jpeg":
return "image/jpeg";
case ".bmp":
return "image/bmp";
case ".wav":
return "audio/wav";
case ".mp3":
return "audio/mpeg3";
case ".mpg":
case "mpeg":
return "video/mpeg";
case ".rtf":
return "application/rtf";
case ".asp":
return "text/asp";
case ".pdf":
return "application/pdf";
case ".fdf":
return "application/vnd.fdf";
case ".ppt":
return "application/mspowerpoint";
case ".dwg":
return "image/vnd.dwg";
case ".msg":
return "application/msoutlook";
case ".xml":
case ".sdxl":
return "application/xml";
case ".xdp":
return "application/vnd.adobe.xdp+xml";
default:
return "application/octet-stream";
}
You may wonder of this why we have taken extension field in DataBase and again we are using this extension here because of only to specify the file type either it is text,word or any another file.
Conclusion:
In this way you can prepare your Upload/Download of file to Sql Server. You may thing again why I wrote this article there are so many article already present the reason is how you can do this things in an easy and how you can resist the general errors occuring while inserting or retriving data from DataBase. Any comments and queries are heartly appreciated.
