The data provider is a set of components that include the Connection, Command, DataReader, and DataAdapter Objects. A data type is an attribute that specifies the type of data that the object can hold. Image is a Datatype in SQL Server that stores variable length binary data from 0 through 2A31-1 (2,147,483,647) bytes. The following C# program shows how to insert an Image in SQL Server.
The following sql script help you to create a table with Image Datatype column.
CREATE TABLE [dbo].[imgtable]( [id] [int] NULL, [img] [image] NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
The above script will create a table named as imgtable and define two columns , first column is id ,an integer datatype and second column is image, an image(img) datatype.
The following C# source code read the image from phisical path D:\picfile.jpg and stores it to a byte array and then insert it into database.
using System;
using System.Data;
using System.Drawing;
using System.IO;
using System.Windows.Forms;
using System.Data.SqlClient ;
namespace WindowsFormsApplication1
{
public partial class Form1 : Form
{
string fName ;
SqlConnection cnn ;
string connectionString = null;
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
connectionString = "Data Source=servername; Initial Catalog=databasename; User ID=sa; Password=password";
cnn = new SqlConnection(connectionString);
fName = "D:\\picfile.jpg";
if (File.Exists(fName))
{
int id = 2;
byte[] content = ImageToStream(fName);
cnn.Open();
SqlCommand cmd = new SqlCommand("insert into imgtable (id,img) values ( @id,@img)", cnn);
cmd.Parameters.AddWithValue("@id", id);
cmd.Parameters.AddWithValue("@img", content);
cmd.ExecuteNonQuery();
cnn.Close();
MessageBox.Show ("Image inserted");
}
else
{
MessageBox.Show(fName + " not found ");
}
}
private byte[] ImageToStream(string fileName)
{
MemoryStream stream = new MemoryStream();
tryagain:
try
{
Bitmap image = new Bitmap(fileName);
image.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg);
}
catch (Exception ex)
{
goto tryagain;
}
return stream.ToArray();
}
}
}