Accessing Files through Ado.net

Sometimes we need to store large data objects such as pictures, executables, videos, and documents in a database. In MS SQL Server, this uses the image data type to hold up to 2G of data. Here's a small example of how to do this via ado.net and MS SQL Server.

Create a test data table first.

Enter and execute the following statement in Query Analyzer:

Create table [imgtable] (

[Imgid] [INT] IDENTITY (1,1) not NULL,

[Imgname] [varchar] COLLATE chinese_prc_ci_as NULL,

[Imgdata] [Image] Null

PRIMARY KEY CLUSTERED

（

[Imgid]

) on [PRIMARY]

) on [PRIMARY] textimage_on [PRIMARY]

This will take a table called imgtable in the database you have chosen.

The code in VS is as follows:

Using System;

Using System.Drawing;

Using System.Collections;

Using System.ComponentModel;

Using System.Windows.Forms;

Using System.Data;

Using System.Data.SqlClient;

Using System.IO;

Namespace Ado_demo

{

<summary>

Summary description of the Form1.

</summary>

public class ADO_Demo:System.Windows.Forms.Form

{

Private System.Windows.Forms.Button button1;

Private System.Windows.Forms.Button button2;

Private System.Windows.Forms.PictureBox PictureBox1;

Private System.Windows.Forms.OpenFileDialog OpenFileDialog1;

Private System.Windows.Forms.Button Button3;

<summary>

The required designer variable.

</summary>

Private System.ComponentModel.Container components = null;

Public Ado_demo ()

{

//

Required for Windows Forms Designer support

//

InitializeComponent ();

//

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

{

if (Components!= null)

{

Components. Dispose ();

}

}

Base. Dispose (disposing);

}
Code generated #region the Windows forms Designer

<summary>

Designer supports required methods-do not use the Code editor to modify

The contents of this method.

</summary>

private void InitializeComponent ()

{

This.button1 = new System.Windows.Forms.Button ();

This.button2 = new System.Windows.Forms.Button ();

This.picturebox1 = new System.Windows.Forms.PictureBox ();

This.openfiledialog1 = new System.Windows.Forms.OpenFileDialog ();

This.button3 = new System.Windows.Forms.Button ();

This. SuspendLayout ();

//

Button1

//

This.button1.Location = new System.Drawing.Point (368, 48);

This.button1.Name = "Button1";

This.button1.Size = new System.Drawing.Size (104, 23);

This.button1.TabIndex = 0;

This.button1.Text = "Save Picture";

This.button1.Click + = new System.EventHandler (This.button1_click);

//

Button2

//

This.button2.Location = new System.Drawing.Point (368, 120);

This.button2.Name = "Button2";

This.button2.Size = new System.Drawing.Size (104, 23);

This.button2.TabIndex = 1;

This.button2.Text = "show Picture";

This.button2.Click + = new System.EventHandler (This.button2_click);

//

PictureBox1

//

This.pictureBox1.Location = new System.Drawing.Point (8, 16);

This.pictureBox1.Name = "PictureBox1";

This.pictureBox1.Size = new System.Drawing.Size (312, 288);

This.pictureBox1.TabIndex = 2;

This.pictureBox1.TabStop = false;

//

OpenFileDialog1

//

This.openFileDialog1.FileOk + = new System.ComponentModel.CancelEventHandler (This.openfiledialog1_fileok);

//

Button3

//

This.button3.Location = new System.Drawing.Point (368, 200);

This.button3.Name = "Button3";

This.button3.Size = new System.Drawing.Size (104, 23);

This.button3.TabIndex = 1;

This.button3.Text = "Read file and open";

This.button3.Click + = new System.EventHandler (This.button3_click);

//

Ado_demo

//

This. AutoScaleBaseSize = new System.Drawing.Size (6, 14);

This. ClientSize = new System.Drawing.Size (496, 317);

This. Controls.Add (This.picturebox1);

This. Controls.Add (This.button2);

This. Controls.Add (This.button1);

This. Controls.Add (This.button3);

This. Name = "Ado_demo";

This. Text = "Ado_demo";

This. ResumeLayout (FALSE);

}

#endregion

<summary>

The main entry point for the application.

</summary>

[STAThread]

static void Main ()

{

Application. Run (New Ado_demo ());

}

<summary>

Click on the Open File dialog box to confirm the button and save the file in the database

</summary>

<param name= "Sender" ></param>

<param name= "E" ></param>

private void Openfiledialog1_fileok (object sender, System.ComponentModel.CancelEventArgs e)

{

string filename = This.openFileDialog1.FileName;

SqlConnection conn = new SqlConnection ("server=192.168.2.200;integrated security = Sspi;database = Northwind");

SqlCommand cmd = new SqlCommand ("Insert imgtable values (@imgname, @imgData)", conn);

SqlParameter pm = new SqlParameter ("@imgname", sqldbtype.varchar,100);

Pm. Value = filename;

SqlParameter pm1 = new SqlParameter ("@imgData", sqldbtype.image);

FileStream fs = new FileStream (Filename,filemode.open);

int len = (int) fs. Length;

byte[] Filedata = new Byte[len];

Fs. Read (Filedata,0,len);

Fs. Close ();

PM1. Value = Filedata;

Cmd. Parameters.Add (PM);

Cmd. Parameters.Add (PM1);

Conn. Open ();

Try

{

Cmd. ExecuteNonQuery ();

}

catch (Exception ex)

{

MessageBox.Show (Ex.message);

}

}

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

{

This.openFileDialog1.ShowDialog ();

}

<summary>

Read the bitmap picture from the database and display

</summary>

<param name= "Sender" ></param>

<param name= "E" ></param>

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

{

SqlConnection conn = new SqlConnection ("server=192.168.2.200;integrated security = Sspi;database = Northwind");

SqlCommand cmd = new SqlCommand ("SELECT * from imgtable where imgname like '%bmp% '", conn);

Conn. Open ();

SqlDataReader Dr;

Try

{

Dr = cmd. ExecuteReader ();

Dr. Read ();

System.Data.SqlTypes.SqlBinary sb = Dr. GetSqlBinary (2);

or byte[] ImageData = (byte[]) dr[2];

MemoryStream ms = new MemoryStream (sb.value);//manipulate picture data in memory

Bitmap bmp = New Bitmap (Bitmap.fromstream (ms));

This.pictureBox1.Image = BMP;

Dr. Close ();

}

catch (Exception ex)

{

MessageBox.Show (Ex.message);

}

Finally

{

Conn. Close ();

}

}

<summary>

Read the file and save it to your hard disk, and then open the file

</summary>

<param name= "Sender" ></param>

<param name= "E" ></param>

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

{

SqlConnection conn = new SqlConnection ("server=192.168.2.200;integrated security = Sspi;database = Northwind");

SqlCommand cmd = new SqlCommand ("SELECT * from imgtable where imgname like '%doc '", conn);

Conn. Open ();

SqlDataReader Dr;

Try

{

Dr = cmd. ExecuteReader ();

Dr. Read ();

System.Data.SqlTypes.SqlBinary sb = Dr. GetSqlBinary (2);

or byte[] ImageData = (byte[]) dr[2];

FileStream fs = new FileStream (@ "C:emp.bmp", FileMode.Create);

string filename = @ "C:" + System.IO.Path.GetFileName (dr.getstring (1));

FileStream fs = new FileStream (filename,filemode.create);

Fs. Write (SB.VALUE,0,SB. Value.length);

Fs. Close ();

This.pictureBox1.Image = Image.FromFile (@ "c:emp.bmp");

System.Diagnostics.Process.Start (filename);

Dr. Close ();

}

catch (Exception ex)

{

MessageBox.Show (Ex.message);

}

Finally

{

Conn. Close ();

}

}

}

}

Reading the entire file directly into an array of memory is fine for small files, but large files, especially those that are larger than physical memory, can cause serious memory problems that require segmented reads and are written to the database in fragments.