How to Store Images in SQL Server from an ASP.NET Page
1. PhotoID Numeric Identity Column
2. Photo Image
The image will be stored in the Photo Field which is of type Image and later on can be retrieved with the help of some another process which I will discuss in another post.
Step 1. Create an interface in ASP.NET Page for the UI. I have created a same and giving a snapshot for the same.
Step 2. Write down the code in the code behind of button having label “Upload Photo” :
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
String vFileName = FileUpload1.PostedFile.FileName;
String vCompletedFileName = Server.MapPath("~/Att/" + vFileName);
FileUpload1.SaveAs(vCompletedFileName);
FileStream fs=new FileStream(Server.MapPath("~/Att/"+ vFileName),FileMode.Open);
byte[] img = new byte[fs.Length];
fs.Read(img, 0, (int)fs.Length);
fs.Close();
SqlConnection vConn = new SqlConnection("server=localhost; database = Rohit; user id=sa; pwd=xxxxxx);
vConn.Open();
String vQuery = "Insert into MyPhoto (Photo) values (@image)";
SqlParameter vParameter = new SqlParameter("@Image",SqlDbType.Image, img.Length);
vParameter.Value = img;
SqlCommand vComm = new SqlCommand(vQuery, vConn);
vComm.Parameters.Add(vParameter);
vComm.ExecuteNonQuery();
Label1.Text="Record Saved in database";
vConn.Close();
File.Delete(Server.MapPath("~/Att/"+vFileName));
}
}
I have used an “Att” folder under the WebSite Folder for temporary copying of the image and then storing in the database.
In the above code the following points are to be noted:
1. Opened the database connection first.
2. Used a SQL Command to create a command for insert statement and also used a SQL Parameter.
3. This parameter is for the image. We have to specify the datatype, size of the image etc.
4. Because the image is stored in the form of Image, we must use the bytes format with the help of FileStream to actually store bytes in the image field.
5. And at last the insert statement is executed in the SQL Server and stores the bytes in the image field.
Hope this helps in your development work. I will come with another article that introduces the concept of Generic Handler in ASP.NET
Normally there is a requirement in the dynamic web sites where the user uploads an image for a specific purpose like profile, product image etc. and it has to be saved in the database. This article discusses the step by step process of storing the image chosen in the ASP.NET Page into SQL Server Database. For this I have taken a small table called as “MyPhoto” which contains two fields having following details:
1. PhotoID Numeric Identity Column
2. Photo Image
The image will be stored in the Photo Field which is of type Image and later on can be retrieved with the help of some another process which I will discuss in another post.
Step 1. Create an interface in ASP.NET Page for the UI. I have created a same and giving a snapshot for the same.
Step 2. Write down the code in the code behind of button having label “Upload Photo” :
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
String vFileName = FileUpload1.PostedFile.FileName;
String vCompletedFileName = Server.MapPath("~/Att/" + vFileName);
FileUpload1.SaveAs(vCompletedFileName);
FileStream fs=new FileStream(Server.MapPath("~/Att/"+ vFileName),FileMode.Open);
byte[] img = new byte[fs.Length];
fs.Read(img, 0, (int)fs.Length);
fs.Close();
SqlConnection vConn = new SqlConnection("server=localhost; database = Rohit; user id=sa; pwd=xxxxxx);
vConn.Open();
String vQuery = "Insert into MyPhoto (Photo) values (@image)";
SqlParameter vParameter = new SqlParameter("@Image",SqlDbType.Image, img.Length);
vParameter.Value = img;
SqlCommand vComm = new SqlCommand(vQuery, vConn);
vComm.Parameters.Add(vParameter);
vComm.ExecuteNonQuery();
Label1.Text="Record Saved in database";
vConn.Close();
File.Delete(Server.MapPath("~/Att/"+vFileName));
}
}
I have used an “Att” folder under the WebSite Folder for temporary copying of the image and then storing in the database.
In the above code the following points are to be noted:
1. Opened the database connection first.
2. Used a SQL Command to create a command for insert statement and also used a SQL Parameter.
3. This parameter is for the image. We have to specify the datatype, size of the image etc.
4. Because the image is stored in the form of Image, we must use the bytes format with the help of FileStream to actually store bytes in the image field.
5. And at last the insert statement is executed in the SQL Server and stores the bytes in the image field.
Hope this helps in your development work. I will come with another article that introduces the concept of Generic Handler in ASP.NET
Comments
login failed for user 'CHANCHAL-PC\Chanchal'
microsoft error 18456
However while installation i have enabled windows authentication.
I also checked services from administrative tools
and i found that MSSQLSERVER was started and started automatically
still i am not able to connect my database engine .
however evrything works fine for other services such as Integration services,Analysis services and all.
I checked it on internet but found no solution some of them have suggested to install service pack3 which i had already done.
Please help me out.