Asp. NET how to access SQL Server database pictures

asp.net|server| Data | database

SQL Server provides a special data type: image, which is a type that contains binary data. The example below shows you how to put text or photos into a database. In this article we want to see how to store and read pictures in SQL Server.

1. Establish a table:

A table that establishes such a structure in SQL Server:

Column Name Type purpose
ID Integer primary Key ID
Imgtitle Varchar (50) The title of the picture
Imgtype Varchar (50) picture type. asp.net to the type of identification
Imgdata Image is used to store binary data

2. Storing pictures in SQL Server database

To be able to store them in a table, you first upload them to your Web server, and you can develop a Web form that is used to bring pictures of the TextBox Web control in the client to your Web server. Set your Enctype property to: Myltipart/formdata.

Stream imgdatastream = File1.PostedFile.InputStream;
int imgdatalen = File1.PostedFile.ContentLength;
string imgtype = File1.PostedFile.ContentType;
string imgtitle = TextBox1.Text;
byte[] Imgdata = new Byte[imgdatalen];
int n = imgdatastream. Read (Imgdata,0,imgdatalen);
String connstr= ((NameValueCollection) context.getconfig ("AppSettings")) ["ConnStr"];

SqlConnection connection = new SqlConnection (CONNSTR);

SqlCommand command = new SqlCommand
("INSERT into Imagestore (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, from the database to restore the Read

Now let's read the data we put in SQL Server! We're going to output the picture to your browser, and you can also store it where you want it.

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 imagestore 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 ();
}

It is response.binarywrite, not Response.Write, that should be noted.

Here is a program for storing and reading C # WinForm. Please compare yourselves to each other! (For convenience, I simplified the database field to 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
{
<summary>
Summary description of the Form1.
</summary>
public class Form1:System.Windows.Forms.Form
{
Private System.Windows.Forms.Button button1;
<summary>
The required designer variable.
</summary>
Private System.ComponentModel.Container components = null;
private string ConnectionString = "Integrated security=sspi;initial catalog=;D ata 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 ()
{
//
Required for Windows Forms Designer support
//
InitializeComponent ();
conn = new SqlConnection (ConnectionString);

//
TODO: Add any constructor code after the InitializeComponent call
//
}

<summary>
Clean up all resources that are in use.
</summary>
protected override void Dispose (bool disposing)
{
IF (Conn. state = = ConnectionState.Open)
Conn. Close ();
if (disposing)
{
if (Components!= null)
{
Components. Dispose ();
}
}
Base. Dispose (disposing);

}

[1] [2] Next page