I have to insert a image file into sqlserver 2005 in asp.net with c sharp.
please provide me with the detailed code
coollife -4 Junior Poster in Training
sknake 1,622 Senior Poster Featured Poster
The table:
Create Table Picture
(
ID int identity(1000, 1) PRIMARY KEY,
Picture image
)Code behind:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
namespace daniweb.web
{
public partial class PagePost : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//Getting the main page
if (Request.QueryString["image"] == null)
{
Image1.ImageUrl = "PagePost.aspx?image=blah.bmp";
//Make sure it exists before we link to it
//This is inefficient because it calls the database twice for one image
byte[] picture = GetImage();
if (picture == null)
{
Image1.Visible = false;
}
}
else
{
string imageStr = Convert.ToString(Request.QueryString["image"]);
if (string.Compare(imageStr, "blah.bmp", true) == 0)
{
byte[] picture = GetImage();
Response.ContentType = "image/bmp";
Response.BinaryWrite(picture);
}
}
}
private static byte[] GetImage()
{
byte[] result = null;
const string query = @"Select Top 1 Picture From Picture";
using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
using (DataTable dt = new DataTable())
{
dt.Load(dr);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
if (row["Picture"] != DBNull.Value)
{
try
{
result = (byte[])row["Picture"];
}
catch { }
}
}
}
}
}
}
return result;
}
private static string BuildSqlNativeConnStr(string server, string database)
{
return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
}
protected void Button1_Click(object sender, EventArgs e)
{
byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
const string query = @"Insert Into Picture (Picture) Values (@Picture)";
using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
parm.Value = bytes;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();
}
}
}
}
}The page:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="PagePost.aspx.cs" Inherits="daniweb.web.PagePost" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Image ID="Image1" runat="server" />
<asp:Button ID="Button1" runat="server" Text="Button" onclick="Button1_Click" />
</form>
</body>
</html> achristov 12 Junior Poster in Training
coollife -4 Junior Poster in Training
sir i saw the code but i couldnt figure out how it works.
Please can u post comments .
I am a newbie .
One more doubt are u converting it into binary before storing into sql table . Thanks in advance
sknake 1,622 Senior Poster Featured Poster
The File.ReadAllBytes() takes the image as binary and includes it in the database as binary.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
namespace daniweb.web
{
public partial class PagePost : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
//Getting the main page
if (Request.QueryString["image"] == null)
{
//since the query string is null this means they did a GET /PagePost.aspx
Image1.ImageUrl = "PagePost.aspx?image=blah.bmp";
//Make sure it exists before we link to it
//This is inefficient because it calls the database twice for one image
byte[] picture = GetImage();
if (picture == null)
{
//no pictures exist in the database so make the image invisible instead of displaying
//a broken image
Image1.Visible = false;
}
}
else
{
//This means they did GET /PagePost.aspx?image=blah.jpg -- so instead of serving a webpage
//we actually need to serve an image. We send a binary image down the response stream
//instead of this pages' HTML markup
string imageStr = Convert.ToString(Request.QueryString["image"]);
if (string.Compare(imageStr, "blah.bmp", true) == 0)
{
byte[] picture = GetImage();
Response.ContentType = "image/bmp";
Response.BinaryWrite(picture);
}
}
}
private static byte[] GetImage()
{
byte[] result = null;
const string query = @"Select Top 1 Picture From Picture";
//creating a connection to the server
using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
{
conn.Open();
//using our select query with an open connection to fetch an image
using (SqlCommand cmd = new SqlCommand(query, conn))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
using (DataTable dt = new DataTable())
{
//load the query results to get at the byte[] array
dt.Load(dr);
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
//Make sure the image isn't a NULL value in the database
if (row["Picture"] != DBNull.Value)
{
try
{
//read the binary image from the database.
result = (byte[])row["Picture"];
}
catch { }
}
}
}
}
}
}
return result;
}
private static string BuildSqlNativeConnStr(string server, string database)
{
return string.Format("Data Source={0};Initial Catalog={1};Integrated Security=True;", server, database);
}
protected void Button1_Click(object sender, EventArgs e)
{
//This reads the image file as BINARY to store it in the database
byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
const string query = @"Insert Into Picture (Picture) Values (@Picture)";
using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(query, conn))
{
//include the binary data in the DB
SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
parm.Value = bytes;
cmd.Parameters.Add(parm);
cmd.ExecuteNonQuery();
}
}
}
}
} riyas_26 -1 Light Poster
u no need to store image in sql.
Just strore the URL of the image that u added in your project as just like a normal string.. it ll works
achristov 12 Junior Poster in Training
1. This is a VS template. Install it first.
2. Open VS and create a web site using the newly installed template.
3. The main folder of your new web site contains (amongst the others) the file Handler.ashx. It is responsible for handling requests for fetching images from your site. (In fact the SQL Server as an underlying image storage.)
4. In the App_Code folder you'll find the support this handler needs (and not only!) - all the image manipulation methods you do and you also might need: fetching, inserting and deleting of albums and images. It also contains a couple of helper routines you may find useful.
Look at the code carefuly, try to understand it, then modify it to suit your needs.
And as usual, "use the source, Luke!"
Good Luck.
sknake 1,622 Senior Poster Featured Poster
OK, lets review the code. First I have c:\picture.bmp. It is a bitmap image.
At this point lets answer a few questions?
* Is this image in "image format"? Yes, it is a Bitmap.
* Is this image binary? Yes, bitmaps are stored as logical pixels in binary on disk.
Now we need to read the image in to C# using code.
byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");Now we have a byte array.
* Is this byte array binary? Yes it is
* Is this byte array in "image format"? Yes, binary image format.
OK, now at this point we have an image as binary. Lets stuff it in the SQL Server!
{
//our much discussed binary data
byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");
//out query to insert the data
const string query = @"Insert Into Picture (Picture) Values (@Picture)";
//lets open a connection to the sql server
using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
{
conn.Open();
//next we need a command to fire off the insert
using (SqlCommand cmd = new SqlCommand(query, conn))
{
//next we need a "binary sql" variable. You can't very well embed binary in a plaintext TSQL query. So lets use parameters.
SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image);
//Give the parameter the binary "image format" byte array.
parm.Value = bytes;
cmd.Parameters.Add(parm);
//go go gadget
cmd.ExecuteNonQuery();
}
}
}OK at this point if you open notepad.exe and do a "File -- Open" on c:\picture.bmp you will see the exact same data as you do in the SQL Server.
At this point lets review where we are:
* Is the image inside of the sql server binary? Yes. It is in the binary "image format", the exact same as you would see on your harddrive.
So now we need to get the image out so we can display it!
private static byte[] GetImage()
{
//this is a place holder for the binary image so we can convert it to an image
byte[] result = null;
//we need a query to fetch the image from the database
const string query = @"Select Top 1 Picture From Picture";
using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs")))
{
conn.Open();
//need a select command
using (SqlCommand cmd = new SqlCommand(query, conn))
{
//datareader to load the results of the select
using (SqlDataReader dr = cmd.ExecuteReader())
{
//load in to a datatable so its easy to work with. You could call dr.Read() if you want to.
using (DataTable dt = new DataTable())
{
dt.Load(dr);
//make sure the query returned at least one result
if (dt.Rows.Count > 0)
{
DataRow row = dt.Rows[0];
//make sure the value isn't "null" inside the database
if (row["Picture"] != DBNull.Value)
{
try
{
//Moves the binary image from the SQL Server to our local byte array.
result = (byte[])row["Picture"];
//at this point we have the binary image in memory
}
catch { }
}
}
}
}
}
}
return result;
}Now we have the binary image in memory and you want to display it on the web page. You have a few ways to go about this. What I chose to do is write the image back down the response stream:
//get our binary image
byte[] picture = GetImage();
Response.ContentType = "image/bmp";
//send the binary image down stream
Response.BinaryWrite(picture);I have a feeling you're concerned that image was never saved to the hard drive as binary. So here is how you would do that:
byte[] picture = GetImage();
Image img;
using (System.IO.MemoryStream ms = new System.IO.MemoryStream(picture))
{
ms.Position = 0;
ms.Seek(0, System.IO.SeekOrigin.Begin);
img = Image.FromStream(ms);
}
img.Save("C:\whatever.bmp");Now you could link to this image on your website. When the webserver gets a request a for an image it reads the image in to a byte[] array and sends it down stream. There is no reason to write the image to disk in what you asked.
peter_budo commented: Nice examples +22
kameswari commented: Very clear explaination.......... Nice!!!!!!! +1
textbox 0 Light Poster
Yeah, just create a directory called 'images' and store a string in the database which would be the path to the image. On the save command then upload the image to the directory. Probably should append a datetime to the filename so there isnt a chance of duplicate filenames.
deepas 0 Newbie Poster
thanks sknake,its a v good reply for the asked help.i also read it & found useful.but what i've problem is i want to first show the image name that is stored in database, and when user clicks in the image name,it would be opened in new web page.actually i am doing with pdf files.with same way i could upload pdf files in db too.but don't have idea to display it. when user clicks on the file name, pdf file should open in browser.do u know how to do so.i'm new to web development ,seek help to do so. how can we pass image id while opening in new page.
thanks anyway.
OK, lets review the code. First I have c:\picture.bmp. It is a bitmap image.
At this point lets answer a few questions?
* Is this image in "image format"? Yes, it is a Bitmap.
* Is this image binary? Yes, bitmaps are stored as logical pixels in binary on disk.Now we need to read the image in to C# using code.
byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp");Now we have a byte array.
* Is this byte array binary? Yes it is
* Is this byte array in "image format"? Yes, binary image format.OK, now at this point we have an image as binary. Lets stuff it in the SQL Server!
{ //our much discussed binary data byte[] bytes = File.ReadAllBytes(@"C:\picture.bmp"); //out query to insert the data const string query = @"Insert Into Picture (Picture) Values (@Picture)"; //lets open a connection to the sql server using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs"))) { conn.Open(); //next we need a command to fire off the insert using (SqlCommand cmd = new SqlCommand(query, conn)) { //next we need a "binary sql" variable. You can't very well embed binary in a plaintext TSQL query. So lets use parameters. SqlParameter parm = new SqlParameter("@Picture", SqlDbType.Image); //Give the parameter the binary "image format" byte array. parm.Value = bytes; cmd.Parameters.Add(parm); //go go gadget cmd.ExecuteNonQuery(); } } }OK at this point if you open notepad.exe and do a "File -- Open" on c:\picture.bmp you will see the exact same data as you do in the SQL Server.
At this point lets review where we are:
* Is the image inside of the sql server binary? Yes. It is in the binary "image format", the exact same as you would see on your harddrive.So now we need to get the image out so we can display it!
private static byte[] GetImage() { //this is a place holder for the binary image so we can convert it to an image byte[] result = null; //we need a query to fetch the image from the database const string query = @"Select Top 1 Picture From Picture"; using (SqlConnection conn = new SqlConnection(BuildSqlNativeConnStr("apex2006sql", "Bugs"))) { conn.Open(); //need a select command using (SqlCommand cmd = new SqlCommand(query, conn)) { //datareader to load the results of the select using (SqlDataReader dr = cmd.ExecuteReader()) { //load in to a datatable so its easy to work with. You could call dr.Read() if you want to. using (DataTable dt = new DataTable()) { dt.Load(dr); //make sure the query returned at least one result if (dt.Rows.Count > 0) { DataRow row = dt.Rows[0]; //make sure the value isn't "null" inside the database if (row["Picture"] != DBNull.Value) { try { //Moves the binary image from the SQL Server to our local byte array. result = (byte[])row["Picture"]; //at this point we have the binary image in memory } catch { } } } } } } } return result; }Now we have the binary image in memory and you want to display it on the web page. You have a few ways to go about this. What I chose to do is write the image back down the response stream:
//get our binary image byte[] picture = GetImage(); Response.ContentType = "image/bmp"; //send the binary image down stream Response.BinaryWrite(picture);I have a feeling you're concerned that image was never saved to the hard drive as binary. So here is how you would do that:
byte[] picture = GetImage(); Image img; using (System.IO.MemoryStream ms = new System.IO.MemoryStream(picture)) { ms.Position = 0; ms.Seek(0, System.IO.SeekOrigin.Begin); img = Image.FromStream(ms); } img.Save("C:\whatever.bmp");Now you could link to this image on your website. When the webserver gets a request a for an image it reads the image in to a byte[] array and sends it down stream. There is no reason to write the image to disk in what you asked.
sknake 1,622 Senior Poster Featured Poster
deepas: You should create new threads for asking your own questions. The code provided serves as an example to get binary from the database down to a web browser.
Regarding having the PDF display instead of download -- are you wanting to use a browser plugin to have the PDF rendered inside part of your page or are you wanting to open a new tab that shows only the PDF with the adobe plugin? If you want to show the document in its' own tab then use the HTTP header's content-disposition to control the browser behavior for known MIME types:
using (RptFullPage rpt = new RptFullPage(acct, prof))
{
rpt.CreateDocument();
rpt.ExportOptions.Pdf.DocumentOptions.Application = rptName;
rpt.ExportOptions.Pdf.DocumentOptions.Author = rptName;
rpt.ExportOptions.Pdf.DocumentOptions.Keywords = rptName;
rpt.ExportOptions.Pdf.DocumentOptions.Subject = rptName;
rpt.ExportOptions.Pdf.DocumentOptions.Title = rptName;
using (MemoryStream ms = new MemoryStream())
{
rpt.ExportToPdf(ms);
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
Response.ContentType = "application/pdf";
Response.AddHeader("Content-Disposition", "inline; filename = " + FileName);
Response.AddHeader("Content-Length", ms.Length.ToString());
Response.BinaryWrite(ms.ToArray());
Response.Flush();
}
}
peter_budo 2,532 Code tags enforcer Team Colleague Featured Poster
thanks sknake,its a v good reply for the asked help.i also read it & found useful.but what i've problem is i want to first show the image name that is stored in database, and when user clicks in the image name,it would be opened in new web page.actually i am doing with pdf files.with same way i could upload pdf files in db too.but don't have idea to display it. when user clicks on the file name, pdf file should open in browser.do u know how to do so.i'm new to web development ,seek help to do so. how can we pass image id while opening in new page.
thanks anyway.
Please do not hijack old post with your own question but rather create new thread for your own question.
Unfortunately in this case I cannot move your post because it is dependant on earlier reply to original question therefore I have to close this thread.
mostafarafi 0 Newbie Poster
tanks
Be a part of the DaniWeb community
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.