CREATE TABLE [dbo].[ImageData]
(
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[ImageData] [image] NULL,
CONSTRAINT [PK_ImageData] PRIMARY KEY CLUSTERED
(
[ImageID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
In this example I am going to use Four(4) Stored Procedures call ReadAllImage, ReadAllImageIDs, ReadImage, SaveImage and use below SQL scripts to create those Procedures.
CREATE proc [dbo].[ReadAllImage] as
SELECT * FROM ImageData
GO
CREATE proc [dbo].[ReadAllImageIDs] as
SELECT ImageID FROM ImageData
GO
CREATE proc [dbo].[ReadImage] @imgId int as
SELECT ImageData FROM ImageData
WHERE ImageID=@imgId
GO
CREATE proc [dbo].[SaveImage] @img image as
INSERT INTO ImageData(ImageData)
VALUES (@img)
GO
FileStream FS = new FileStream(@fop.FileName, FileMode.Open, FileAccess.Read);
byte[] img = new byte[FS.Length];
FS.Read(img, 0, Convert.ToInt32(FS.Length));
if (con.State == ConnectionState.Closed)
con.Open();
SqlCommand cmd = new SqlCommand("SaveImage", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@img", SqlDbType.Image).Value = img;
cmd.ExecuteNonQuery();
loadImageIDs();
SqlConnection con = new SqlConnection(DBHandler.GetConnectionString());
SqlCommand cmd = new SqlCommand("ReadImage", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@imgId", SqlDbType.Int).Value =
Convert.ToInt32(cmbImageID.SelectedValue.ToString());
SqlDataAdapter adp = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
try
{
if (con.State == ConnectionState.Closed)
con.Open();
adp.Fill(dt);
if (dt.Rows.Count > 0)
{
MemoryStream ms = new MemoryStream((byte[])dt.Rows[0]["ImageData"]);
picImage.Image = Image.FromStream(ms);
picImage.SizeMode = PictureBoxSizeMode.StretchImage;
picImage.Refresh();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error",
MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (con.State == ConnectionState.Open)
con.Close();
}