Database storage image (MSSQL/Oracle/Access

Source: Internet
Author: User
Tags oracleconnection connectionstrings

Next I will summarize how to save images to sqlserver, Oracle, and access databases.
First of all, we need to understand that the image is saved in the database in binary format, so the steps to save the image to the database generally include these steps
1. Convert the image to a binary array (byte []);
2. Pass the converted binary array (byte []) as a parameter to the command to be executed;
3. Execute Command;

First, how to convert an image to byte []. If you are using ASP. net2.0, you can use the fileupload control to implement
Byte [] filedata = This. fileupload1.filebytes;
If you are using ASP. net1.1 or you are creating a winform, you can use the following method to convert the image to byte []

Public byte [] getbytes (string filepath)
{
System. Io. filestream FS = new system. Io. filestream (filepath, system. Io. filemode. Open );
Byte [] imgdata = new byte [fs. Length];
FS. Read (imgdata, 0, (INT) fs. Length );
Return imgdata;
} Next, we need to pass the obtained byte [] as a parameter to the command object.

1. SQL Server database. Sqlserver has the image field type and can store up to 2 GB of data. Byte [] filedata = This. fileupload1.filebytes;

String SQL = "insert into t_img (IMG) values (@ IMG )";
String strconn = system. configuration. configurationmanager. connectionstrings ["fengdongdb"]. tostring ();
Sqlconnection sqlconn = new sqlconnection (strconn );
Sqlcommand sqlcomm = new sqlcommand (SQL, sqlconn );
Sqlcomm. Parameters. Add ("@ IMG", sqldbtype. Image); // Add Parameters
Sqlcomm. Parameters ["@ IMG"]. value = filedata; // assign a value to the parameter

Sqlconn. open ();
Sqlcomm. executenonquery ();
Sqlconn. Close ();
2. Oracle database. In Oracle databases, we can use BLOB fields to store up to 4 GB of data.
Byte [] filedata = This. fileupload1.filebytes;

String SQL = "insert into t_img (imgid, imgdata) values (100,: imgdata )";
String strconn = system. configuration. configurationmanager. connectionstrings ["connectionstringfororaclE "]. tostring ();
Oracleconnection oraconn = new oracleconnection (strconn );
Oraclecommand oracomm = new oraclecommand (SQL, oraconn );
Oracomm. Parameters. Add (": imgdata", oracletype. Blob); // Add a parameter
Oracomm. Parameters [": imgdata"]. value = filedata; // assign a value to the parameter

Oraconn. open ();
Oracomm. executenonquery ();
Oraconn. Close ();
Note: Here I need to explain that you should be careful when passing parameters through a dedicated Oracle connection. You will know the preceding SQL statement, to add a ':' before the parameter name, the following error occurs: oracleexception: orA-01036: Invalid variable name/number ". Pay attention to this. In addition, when I reference system. data. the oracleclient namespace does not exist by default and must be added to the system. data. oracleclient reference. I remember that if oracleclient is installed in vs2003, it can be introduced without adding references. Pay attention to it here.

3. access the database. In access, we use the OLE object field type, which supports up to 1 GB of data.

Byte [] filedata = This. fileupload1.filebytes;

String SQL = "insert into t_img (imgdata) values (?) ";
String strconn = system. configuration. configurationmanager. connectionstrings ["connectionstringforaccesS "]. tostring ();

Oledbconnection oleconn = new oledbconnection (strconn );
Oledbcommand olecomm = new oledbcommand (SQL, oleconn );
Olecomm. Parameters. Add ("imgdata", oledbtype. Binary );
Olecomm. Parameters ["imgdata"]. value = filedata;

Oleconn. open ();
Olecomm. executenonquery ();
Oleconn. Close ();

Now we have saved all the images to the database. Next we will talk about how to read the images from the database. In fact, this is the opposite process of the insert operation: first report the image data obtained from the database into an array, and then convert the array into an image. There is no big difference between different data. Here I will only list the data read from the Oracle database for reference.

Private byte [] getimagedatafromoracle ()
{
String SQL = "select imgdata from t_img where imgid = 100 ";
String strconn = system. configuration. configurationmanager. connectionstrings ["connectionstringfororaclE "]. tostring ();
Oracleconnection oraconn = new oracleconnection (strconn );
Oraclecommand oracomm = new oraclecommand (SQL, oraconn );

Oraconn. open ();
Byte [] filedata = (byte []) oracomm. executescalar ();
Oraconn. Close ();

Return filedata;
}
When we get the data, the process of converting byte [] into an image is the same.

Private system. Drawing. Image convertbytetoimg (byte [] imgdata)
{
System. Io. memorystream MS = new system. Io. memorystream (imgdata );
System. Drawing. Image IMG = system. Drawing. image. fromstream (MS );
Return IMG;
} If you are developing a winform application, you can directly save or display the returned results to picturebox. If you are using ASP. net, You can output images on a separate page, and direct the imageurl attribute of the image control to the image output page on another page.
For example, the output page getimg. aspxCodeProtected void page_load (Object sender, eventargs E)
{
String SQL = "select imgdata from t_img where imgid = 100 ";
String strconn = system. configuration. configurationmanager. connectionstrings ["connectionstringfororaclE "]. tostring ();
Oracleconnection oraconn = new oracleconnection (strconn );
Oraclecommand oracomm = new oraclecommand (SQL, oraconn );

Oraconn. open ();
Byte [] filedata = (byte []) oracomm. executescalar ();
Oraconn. Close ();

System. Io. memorystream MS = new system. Io. memorystream (filedata );
System. Drawing. Image IMG = system. Drawing. image. fromstream (MS );
IMG. Save (response. outputstream, system. Drawing. imaging. imageformat. JPEG );
}

Note: MSSQL and access pass the test. Oracle lacks an application platform that has not been tested.

Learn to share!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.