SQL Server provides a special data type: Image, which is a type containing binary data. This example will show you how to put text or photos in the database. In this article we have to see how to store and read images in SQL Server.
1. Create a table:
Create a table of such a structure in SQL Server:
Column Type Destination ID Integer Primary Key Id Imgtitle Varchar (50) Title IMGTYPE VARCHAR (50) Picture Type. ASP.NET To be used to store binary data with identified type IMGDATA Image
2, store pictures to the SQL Server database
In order to be stored in the table, you first go upload them on your web server, you can develop a web form, which is used to enter the image in the TextBox Web Control in your web server. Set your ENCTYPE attribute to: MyLtipart / Formdata.
Stream IMGDataStream = file1.postedfile.inputstream;
INT IMGDATALEN = file1.postedfile.contentLENGTH;
String ImgType = file1.postedfile.contenttype;
String emgtitle = textbox1.text;
Byte [] IMGData = New byte [IMGDATALEN];
INT n = imgdatathastream.read (IMGDATA, 0, IMGDATALEN);
String connStr = (NameValueCollection) Context.getConfig ("appsettings")) ["connStr"];
SqlConnection Connection = New SqlConnection (connStr);
Sqlcommand command = new SQLCOMMAND
("INSERT IMAGTORE (Imgtitle, ImgType, IMGDATA)
VALUES (@Imgtitle, @ imgtype, @ imgdata) ", connection);
Sqlparameter paramtitle = new sqlparameter
("@Imgtitle", SqldbType.varchar, 50);
Paramtitle.Value = imgtitle;
Command.Parameters.add (paramtitle);
Sqlparameter paramdata = new sqlparameter ("@imgdata", sqldbtype.image);
Paramdata.value = IMGDATA;
Command.Parameters.Add (paramdata);
SQLParameter Paramtype = New Sqlparameter ("@ImgType", SqldbType.varchar, 50);
ParamType.Value = ImgType;
Command.Parameters.Add (paramtype);
Connection.open ();
INT NumrowSaffected = Command.executenonQuery ();
Connection.Close ();
3. Restore read from the database
Let us now read the data we put into the data from SQL Server! We will output pictures to your browser, you can also store it to your location. Private Void Page_Load (Object Sender, System.EventArgs E)
{
String IMGID = Request.QueryString ["IMGID"];
String connStr = ((NameValueCollection)
Context.getConfig ("appsettings")) ["connStr"];
String SQL = "SELECT IMGDATA, IMGTYPE FROM Image" WHERE ID = " IMGID;
SqlConnection Connection = New SqlConnection (connStr);
Sqlcommand command = new SQLCOMMAND (SQL, Connection);
Connection.open ();
SqlDataReader DR = command.executeReader ();
IF (Dr.Read ())
{
Response.ContentType = DR ["iMgType"]. TOSTRING ();
Response.binaryWrite ((byte []) DR ["IMGDATA"]);
}
Connection.Close ();
}
Note that Response.BinaryWrite is not response.write.
Let's give you a deposit and read programs for C # WinForm. All of them please compare themselves! (For convenience, I simplify the database field into two: imgtitle and imgdata.
Using system;
Using system.drawing;
Using system.collections;
Using system.componentmodel;
Using system.windows.forms;
Using system.data;
Using system.io;
Using system.data.sqlclient;
Namespace WindowsApplication21
{
///
/// Form1 summary description.
///
Public Class Form1: System.Windows.Forms.form
{
Private system.windows.Forms.Button button1;
///
/// The required designer variable.
///
Private system.componentmodel.Container Components = NULL;
Private string connectionstring = "integrated security = sspi; initial catalog =; data source = localhost;"
Private sqlconnection conn = null;
Private sqlcommand cmd = NULL;
Private system.windows.Forms.Button Button2;
Private system.windows.forms.picturebox pic1;
Private system.windows.forms.openfiledialog openfiledialog1; private string sql = null;
Private system.windows.Forms.Label label2;
Private string nowid = NULL;
Public Form1 ()
{
//
// Windows Form Designer Support
//
InitializationComponent ();
CONN = New SqlConnection (Connectionstring);
//
// Todo: Add any constructor code after INITIALIZECOMPONENT call
//
}
///
/// Clean all the resources being used.
///
Protected Override Void Dispose (Bool Disposing)
{
IF (Conn.State == ConnectionsState.Open)
CONN.CLOSE ();
IF (Disposing)
{
IF (Components! = NULL)
{
Components.dispose ();
}
}
Base.dispose (Disposing);
}
#Region Windows Form Designer Generated Code
///
/// Designer supports the required method - do not use the code editor to modify
/// This method is content.
///
Private vidinitiRizeComponent ()
{
This.button1 = new system.windows.Forms.Button ();
THIS.PIC1 = New System.windows.Forms.PictureBox ();
This.Button2 = new system.windows.Forms.Button ();
This.openfileDialog1 = new system.windows.Forms.openFiledialog ();
THIS.LABEL2 = New System.windows.Forms.label ();
THIS.SUSPENDLAYOUT ();
//
// Button1
//
This.Button1.Location = new system.drawing.point (0, 40);
This.button1.name = "button1";
This.button1.size = new system.drawing.size (264, 48);
this.button1.tabindex = 0;
THIS.BUTTON1.TEXT = "Add a new picture";
This.Button1.click = new system.eventhandler (this.button1_click);
//
// pic1
//
This.Pic1.Location = new system.drawing.point (280, 8);
THIS.PIC1.NAME = "PIC1";
This.pic1.size = new system.drawing.size (344, 264);
THIS.PIC1.TABINDEX = 3;
THIS.PIC1.TABSTOP = FALSE;
//
// Button2
//
This.Button2.Location = new system.drawing.point (0, 104); this.button2.name = "button2";
This.button2.size = new system.drawing.size (264, 40);
this.button2.tabindex = 4;
This.Button2.Text = "Restore images from the database";
This.button2.click = new system.eventhandler (this.button2_click);
//
// OpenFileDialog1
//
This.openfileDialog1.filter = "/" image file (* .jpg, *. BMP, *. gif) | * .jpg | * .bmp | * .gif / ""
//
// label2
//
THIS.LABEL2.LOCATION = New System.drawing.Point (0, 152);
THIS.LABEL2.NAME = "label2";
This.label2.size = new system.drawing.size (264, 48);
THIS.Label2.tabindex = 5;
//
// Form1
//
THIS.AUTOSCALEBASESIZE = New System.drawing.size (6, 14);
THIS.CLIENTSIZE = New System.drawing.size (632, 273);
this.controls.addrange (new system.windows.forms.control] {
THIS.LABEL2,
this.button2,
THIS.PIC1,
THIS.BUTTON1});
THIS.NAME = "Form1";
THIS.TEXT = "Form1";
This.Load = New System.EventHandler (this.form1_load);
This.ResumeLayout (false);
}
#ndregion
///
/// The main entry point for the application.
///
[Stathread]
Static void
Main
()
{
Application.run (New Form1 ());
}
Private void Button1_Click (Object Sender, System.Eventargs E)
{
OpenFileDialog1.showdialog ();
IF (OpenFileDialog1.FileName.trim ()! = "")
{
FileInfo Fi = New FileInfo (OpenFiledialog1.FileName);
String Imgtitle = OpenFiledialog1.FileName;
INT IMGDATALEN = (int) FI.LENGTH;
Byte [] IMGData = New byte [IMGDATALEN];
Stream IMGDataStream = FI.OpenRead ();
INT n = imgdatathastream.read (IMGDATA, 0, IMGDATALEN);
IF (Conn.State == ConnectionsState.Open)
CONN.CLOSE ();
Connectionstring = "Integrated Security = SSPI;" "Initial Catalog = mydb;" "Data Source = localhost;"; conn.connectionstring = connectionstrument;
Try
{
String myselectquery = "Insert Imagestore (Imgtitle, IMGDATA) VALUES (@Imgtitle, @Imgdata);
// String myselectQuery = "Update imagestore set imgtitle = @ imgtitle, IMGDATA = @ imgdata";
Sqlcommand mycommand = new sqlcommand (MySelectQuery, conn);
Sqlparameter paramtitle = new sqlparameter ("@ imgtitle", sqldbtype.varchar, 50);
Paramtitle.Value = imgtitle;
MyCommand.Parameters.Add (paramtitle);
Sqlparameter paramdata = new sqlparameter ("@imgdata", sqldbtype.image);
Paramdata.value = IMGDATA;
MyCommand.Parameters.Add (paramdata);
Cn.open ();
INT NumrowSaffected = MyCommand.executenonQuery ();
CONN.CLOSE ();
}
Catch (Exception Err)
{
Messagebox.show ("You entered the name may exist or enter empty in the database, please check!" Err.Tostring ());
}
Finally
{}
}
}
Private Void Form1_Load (Object Sender, System.EventArgs E)
{
}
Private void button2_click (Object Sender, System.Eventargs E)
{
// Open the database connection
IF (Conn.State == ConnectionsState.Open)
CONN.CLOSE ();
CONNECTIONSTRING = "Integrated security = SSPI;" "initial catalog = mydb;" "Data Source = localhost;"
Conn.connectionstring = connectionstring;
// Create a data adapter
String SQL = "SELECT * FROMAGESTORE";
Sqlcommand command = new SQLCOMMAND (SQL, CONN);
Try
{conn.open ();
Catch (Exception Newerr)
{
Messagebox.show ("Cannot turn on the data join!");
}
Finally
{}
SqlDataReader DR = command.executeReader ();
IF (Dr.Read ())
{
FileInfo Fi = New FileInfo ("Temp"); filestream mystream = FI.Open (filemode.create);
BYTE [] mydata = ((byte []) DR ["IMGDATA"]);
//label2.text= "You see now:" DR ["irstitle"]. TOSTRING ();
Foreach (Byte a in mydata)
{
MyStream.writebyTe (a);
}
MyStream.close ();
Image myimage = image.fromfile ("temp");
Pic1.image = MyImage;
Pic1.Refresh ();
Dr.close ();
}
Else
{
Messagebox.show ("There is no successful reading data!");
}
CONN.CLOSE ();
}
}
}
Author: huanghai
Source: Yesky
