How ASP.NET accesses SQL Server database images

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

1. Create a table:

Create a table with this structure in SQL SERVER:

2. Store pictures in the SQL SERVER database.

In order to store them in the table, you must first upload them to your WEB server. You can develop a web form, which is used to import pictures from the TextBox web control in the client. Your WEB server comes up. 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. Resume reading from the database.

Now let us read the data we put in from SQL Server! We will output the image to your browser, you can also store it to the location you want. .

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

}

Note It is Response.BinaryWrite instead of Response.Write.

Here is a storage and reading program for C# Winform. Please compare the differences yourself! (For convenience, I have simplified the database fields 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

{

///

/// Summary description of Form1

///

public class Form1 : System.Windows.Forms.Form

{

private System.Windows .Forms.Button button1;

///

/// Required designer variables

///

private System.ComponentModel.Container components = null;

private string ConnectionString = "Integrated Security=SSPI;Initial Catalog=;DataSource=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

//

}

///

/// Clean up any resources in use.

///

protected override void Dispose( bool disposing )

{

if (conn.State == ConnectionState.Open)

conn.Close();

if( disposing )

{

if (components != null)

{

components.Dispose();

}

}

base.Dispose( disposing );

}

#region Windows Form Designer generated code

///

/// The designer supports the required method - do not use the code editor to modify

/// the contents of this method.

///

private void InitializeComponent()

{

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 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 image from database";

this.button2.Click += new System.EventHandler(this.button2_Click);

//

// openFileDialog1

//

this .openFileDialog1.Filter = ""Image files (*.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);

}

#endregion

///

/// The main entry point of the application.

///

[STAThread]

static void Main()

{

Application.Run(new Form1());

}

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