. NET image access technology in SQL Server

Server|sql

This article summarizes how to save a picture in SQL Server and read the displayed method in. NET WinForm and. NET WebForm (asp.net).


1. Use asp.net to upload and store pictures in SQL Server, and then read and display them from SQL Server:


1) Upload and save to SQL Server


Database Structure


CREATE TABLE Test


{


ID Identity (1,1),


fimage Image


}


-related stored procedures


Create proc UpdateImage


(


@UpdateImage Image


)


as


Insert into Test (fimage) VALUES (@UpdateImage)


Go


is added to the upphoto.aspx file as follows:


<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 handling code for the Btnadd button in the post code file UpPhoto.aspx.cs:


private void btnAdd_Click (object sender, System.EventArgs e)


{


//Get image and convert 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);


//Connection 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 a picture without using a stored procedure, change the above four code to read:


//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 and display from SQL Server


Add the following code where you want the picture to appear:


<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";//here assume that you get the picture with ID 2


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. Store pictures in SQL Server in WinForm and read from SQL Server and display in PictureBox
1, save SQL Server
database structure and stored procedures used, as above
First, Add a OpenFileDialog control to the form named Ofdselectpic;
then add an open File button on the form to 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 (Read all!);
//Connection 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, addAdd a PictureBox, named Ptbshow
Then, add a button to 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