Programs that use SQL Server to save images
Introduction:
This article is about saving and reading images from SQL servers using. net.
Tools used:
SQL Server 2000
Microsoft. netversion 1.1
C # (Windows Forms Based Application)
Save image:
Creating a table in an SQL Server 2000 database requires at least one image field.
The SQL statement used here is as follows:
Create Table [DBO]. [tblimgdata] (
[ID] [int] not null,
[Name] [varchar] (50) Collate SQL _latin1_general_cp1_ci_as null,
[Picture] [Image] Null
) On [primary] textimage_on [primary]
In fact, the image field only stores fields that contain binary data parameters, so we must convert the image to a byte type.
String strfn = This. openfiledialog1.filename;
By using the fileinfo class, I got the file size: fileinfo fiimage = new fileinfo (strfn );
Declare an array of the corresponding size:
This. m_limagefilelength = fiimage. length;
M_barrimg = new byte [convert. toint32 (this. m_limagefilelength)];
Use the filestream object to populate the array:
Filestream FS = new filestream (strfn, filemode. Open,
Fileaccess. Read, fileshare. Read );
Int ibytesread = FS. Read (m_barrimg, 0,
Convert. toint32 (this. m_limagefilelength ));
FS. Close ();
Call the Image Code:
Protected void LoadImage ()
{
Try
{
This. openfiledialog1.showdialog (this );
String strfn = This. openfiledialog1.filename;
This. picturebox1.image = image. fromfile (strfn );
Fileinfo fiimage = new fileinfo (strfn );
This. m_limagefilelength = fiimage. length;
Filestream FS = new filestream (strfn, filemode. Open,
Fileaccess. Read, fileshare. Read );
M_barrimg = new byte [convert. toint32 (this. m_limagefilelength)];
Int ibytesread = FS. Read (m_barrimg, 0,
Convert. toint32 (this. m_limagefilelength ));
FS. Close ();
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
}
Save the byte array data to the database.
CREATE Command text to insert record. This. sqlcommand1.commandtext =
"Insert into tblimgdata (ID, name, picture)" +
"Values (@ ID, @ name, @ picture )";
Create parameters. This. sqlcommand1.parameters. Add ("@ ID ",
System. Data. sqldbtype. Int, 4 );
This. sqlcommand1.parameters. Add ("@ name ",
System. Data. sqldbtype. varchar, 50 );
This. sqlcommand1.parameters. Add ("@ picture ",
System. Data. sqldbtype. Image );
Note that "@ picture" has "sqldbtype. Image" because it is an image-type field.
Provided parameter values:
This. sqlcommand1.parameters ["@ ID"]. value = This. editid. text;
This. sqlcommand1.parameters ["@ name"]. value = This. editname. text;
This. sqlcommand1.parameters ["@ picture"]. value = This. m_barrimg;
"This. m_barrimg" is a byte array, which we have filled just now.
Run the SQL statement to save the record to the database:
Int iresult = This. sqlcommand1.executenonquery ();
Save the Image Code:
Collapseprivate void btnsave_click (Object sender, system. eventargs E)
{
Try
{
This. sqlconnection1.open ();
If (sqlcommand1.parameters. Count = 0)
{
This. sqlcommand1.commandtext = "insert into tblimgdata (ID," +
"Name, picture) values (@ ID, @ name, @ picture )";
This. sqlcommand1.parameters. Add ("@ ID ",
System. Data. sqldbtype. Int, 4 );
This. sqlcommand1.parameters. Add ("@ name ",
System. Data. sqldbtype. varchar, 50 );
This. sqlcommand1.parameters. Add ("@ picture ",
System. Data. sqldbtype. Image );
}
This. sqlcommand1.parameters ["@ ID"]. value = This. editid. text;
This. sqlcommand1.parameters ["@ name"]. value = This. editname. text;
This. sqlcommand1.parameters ["@ picture"]. value = This. m_barrimg;
Int iresult = This. sqlcommand1.executenonquery ();
MessageBox. Show (convert. tostring (iresult ));
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
This. sqlconnection1.close ();
}
}
Read image:
Reading images from a database is a precise reverse operation for saving images.
First, create a text command to read records.
Sqlcommand cmdselect = new sqlcommand ("select picture" +
"From tblimgdata where id = @ ID ",
This. sqlconnection1 );
Statement creation parameters:
Cmdselect. Parameters. Add ("@ ID", sqldbtype. Int, 4 );
Parameter Value
Cmdselect. Parameters ["@ ID"]. value = This. editid. text;
Open the database connection and execute "executescalar", because we only need the data returned under the "image" field.
Byte [] barrimg = (byte []) cmdselect. executescalar ();
The data of the "object" type is returned by running the command in stages, and we convert it into a byte array.
Save data to a temporary file:
String strfn = convert. tostring (datetime. Now. tofiletime ());
Filestream FS = new filestream (strfn, filemode. createnew, fileaccess. Write );
FS. Write (barrimg, 0, barrimg. Length );
FS. Flush ();
FS. Close ();
Then, display the image at the position you want to display:
Picturebox1.image = image. fromfile (strfn );
Complete the image read code:
Private void btnload_click (Object sender, system. eventargs E)
{
Try
{
Sqlcommand cmdselect = new sqlcommand ("select picture" +
"From tblimgdata where id = @ ID", this. sqlconnection1 );
Cmdselect. Parameters. Add ("@ ID", sqldbtype. Int, 4 );
Cmdselect. Parameters ["@ ID"]. value = This. editid. text;
This. sqlconnection1.open ();
Byte [] barrimg = (byte []) cmdselect. executescalar ();
String strfn = convert. tostring (datetime. Now. tofiletime ());
Filestream FS = new filestream (strfn,
Filemode. createnew, fileaccess. Write );
FS. Write (barrimg, 0, barrimg. Length );
FS. Flush ();
FS. Close ();
Picturebox1.image = image. fromfile (strfn );
}
Catch (exception ex)
{
MessageBox. Show (ex. Message );
}
Finally
{
This. sqlconnection1.close ();
}
}