This article summarizes how to store images in SQL Server and read and display them in .Net WinForm and .Net WebForm (asp.net).
1. Use asp.net to upload the image and store it in SQL Server, then read it from SQL Server and display it:
1) Upload and save to SQL Server
Database structure
create table test
{
ididentity(1,1),
FImage image
}
Related stored procedures
Create proc UpdateImage
(
@UpdateImage Image
)
As
Insert Into test(FImage) values(@UpdateImage)
GO
Add the following to the UpPhoto.aspx file:
<input id="UpPhoto" name="UpPhoto" runat="server" type="file">
<asp:Button id="btnAdd" name="btnAdd" runat="server" Text="Upload"></asp:Button>
Then add the click event processing code of the btnAdd button in the code-behind file UpPhoto.aspx.cs:
private void btnAdd_Click(object sender, System.EventArgs e)
{
//Get the image and convert the image to byte[]
HttpPostedFile upPhoto=UpPhoto.PostedFile;
int upPhotoLength=upPhoto.ContentLength;
byte[] PhotoArray=new Byte[upPhotoLength];
Stream PhotoStream=upPhoto.InputStream;
PhotoStream.Read(PhotoArray,0,upPhotoLength);
//Connect to database
SqlConnection conn=new SqlConnection();
conn.ConnectionString="Data Source=localhost;Database=test;User Id=sa;Pwd=sa";
SqlCommand cmd=new SqlCommand("UpdateImage",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@UpdateImage",SqlDbType.Image);
cmd.Parameters["@UpdateImage"].Value=PhotoArray;
//If you want to add pictures without using stored procedures, change the above four lines of code to:
//string strSql="Insert into test(FImage) values(@FImage)";
//SqlCommand cmd=new SqlCommand(strSql,conn);
//cmd.Parameters.Add("@FImage",SqlDbType.Image);
//cmd.Parameters["@FImage"].Value=PhotoArray;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
}
2) Read from SQL Server and display it. Add the following code where the picture needs to be displayed:
<asp:image id="imgPhoto" runat="server" ImageUrl="ShowPhoto.aspx"></asp:image>
ShowPhoto.aspx body code:
private void Page_Load(object sender, System.EventArgs e)
{
if(!Page.IsPostBack)
{
SqlConnection conn=new SqlConnection()
conn.ConnectionString="Data Source=localhost;Database=test;User Id=sa;Pwd=sa";
string strSql="select * from test where id=2";//Assume here that the image with id 2 is obtained
SqlCommand cmd=new SqlCommand(strSql,conn);
conn.Open();
SqlDataReader reader=cmd.ExecuteReader();
reader.Read();
Response.ContentType="application/octet-stream";
Response.BinaryWrite((Byte[])reader["FImage"]);
Response.End();
reader.Close();
}
}
2. Save the picture to SQL Server in WinForm, read it from SQL Server and display it in picturebox
1), store in SQL Server
The database structure and stored procedures used are the same as above. First, add an OpenFileDialog control to the form and name it ofdSelectPic;
Then, add an open file button on the form and add the following click event code:
Stream ms;
byte[] picbyte;
//ofdSelectPic.ShowDialog();
if (ofdSelectPic.ShowDialog()==DialogResult.OK)
{
if ((ms=ofdSelectPic.OpenFile())!=null)
{
//MessageBox.Show("ok");
picbyte=new byte[ms.Length];
ms.Position=0;
ms.Read(picbyte,0,Convert.ToInt32(ms.Length));
//MessageBox.Show("Reading completed!");
//Connect to database
SqlConnection conn=new SqlConnection();
conn.ConnectionString="Data Source=localhost;Database=test;User Id=sa;Pwd=sa";
SqlCommand cmd=new SqlCommand("UpdateImage",conn);
cmd.CommandType=CommandType.StoredProcedure;
cmd.Parameters.Add("@UpdateImage",SqlDbType.Image);
cmd.Parameters["@UpdateImage"].Value=picbyte;
conn.Open();
cmd.ExecuteNonQuery();
conn.Close();
ms.Close();
}
}
2) Read and display in picturebox First, add a picturebox named ptbShow
Then, add a button and add the following response event:
SqlConnection conn=new SqlConnection();
conn.ConnectionString="Data Source=localhost;Database=test;User Id=sa;Pwd=sa";
string strSql="select FImage from test where id=1";
SqlCommand cmd=new SqlCommand(strSql,conn);
conn.Open();
SqlDataReader reader=cmd.ExecuteReader();
reader.Read();
MemoryStream ms=new MemoryStream((byte[])reader["FImage"]);
Image image=Image.FromStream(ms,true);
reader.Close();
conn.Close();
ptbShow.Image=image;
Original address: http://stewen.cnblogs.com/archive/2005/12/20/300587.aspx