Saving Image in SQL Server 2005 Database with using Typed Dataset and passing parameter as XML from Typed Dataset
Table structure
CREATE TABLE [dbo].[TblTestPhotograph]
(
[TestID] [int] IDENTITY(1,1) NOT NULL,
[TestDesc] [varchar](50),
[TestPhotograph] [image] NOT NULL,
[TestThumbnail] [image] NOT NULL,
CONSTRAINT [PK_TblTestPhotograph] PRIMARY KEY CLUSTERED
(
[TestID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
Aspx page inputs
<asp:TextBox ID="TxtPhotoDesc" Text="Photograph" runat="server"></asp:TextBox>
<asp:FileUpload ID="FileUploadPhoto" runat="server" />
<asp:Button ID="Submit" runat="server" Text="Submit" OnClick="Submit_Click" />
<asp:Image ID="Image1" runat="server" ImageUrl="~/Default.aspx?ImageID=Image1" />
<asp:Image ID="Image2" runat="server" ImageUrl="~/Default.aspx?ImageID=Image2" />
Method One I have used Image datatype as parameter and its working fine.
//Method I
//Save Image using stored procedure with Image datatype as an parameter.
//=================================================================================================================================
//Create sql connection object and assign connection string from web.config file
ObjSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNECTIONSTRING"].ConnectionString.ToString());
//Opening connection
ObjSqlConnection.Open();
//=================================================================================================================================
//=================================================================================================================================
//Create SQL Command object
ObjSqlCommand = new SqlCommand();
//Assign Connection object to SQL Command object
ObjSqlCommand.Connection = ObjSqlConnection;
//Setting Command type as stored procedure to SQL Command object
ObjSqlCommand.CommandType = CommandType.StoredProcedure;
//Assigning stored procdure name to SQL Command object
ObjSqlCommand.CommandText = "TestInsertPhotograph";
//=================================================================================================================================
//=================================================================================================================================
//Add parameter to sql command object and set as image description
ObjSqlCommand.Parameters.Add("@TestDesc", SqlDbType.VarChar).Value = TxtPhotoDesc.Text.ToString();
//=================================================================================================================================
//=================================================================================================================================
//Create file stream object and open slected file from file upload control.
ObjFileStream = new FileStream(FileUploadPhoto.PostedFile.FileName, FileMode.Open, FileAccess.Read);
//Create Binary reader object to read stream from file stream object
ObjBinaryReader = new BinaryReader(ObjFileStream);
//Create Byte array and read bytes in array to File Streams max length
Byte[] ObjByteArr = ObjBinaryReader.ReadBytes((Int32)ObjFileStream.Length);
//Binary reader close
ObjBinaryReader.Close();
//File stream close
ObjFileStream.Close();
//Add parameter to sql command object and set as image from Byte Array.
ObjSqlCommand.Parameters.Add("@TestPhotograph", SqlDbType.Image).Value = ObjByteArr;
//=================================================================================================================================
//Using GetThumbnailImage for creating Thumbnail
//Picture quality is good for Gif.
//=================================================================================================================================
//Create Image object from selected file from file upload control.
System.Drawing.Image ObjImage = System.Drawing.Image.FromFile(FileUploadPhoto.PostedFile.FileName);
//Create Image object to create thumbnail of selected file.
System.Drawing.Image ObjThumbnailImage = ObjImage.GetThumbnailImage(100, 100, new System.Drawing.Image.GetThumbnailImageAbort(ThumbnailCallback), IntPtr.Zero);
//Create Memory Stream object
ObjMemoryStream = new MemoryStream();
//Save Thumbnail image in Memory Stream object
ObjThumbnailImage.Save(ObjMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg);
//Create Byte array and read bytes in array from Memory Stream object
Byte[] ObjByteArrThumbnail = ObjMemoryStream.ToArray();
//Memory Stream object close
ObjMemoryStream.Close();
//Add parameter to sql command object and set as thumbnail Image value from Byte Array.
ObjSqlCommand.Parameters.Add("@TestThumbnail", SqlDbType.Image).Value = ObjByteArrThumbnail;
//=================================================================================================================================
//=================================================================================================================================
//Execute sql command object
ObjSqlCommand.ExecuteNonQuery();
//Close sql connection
ObjSqlConnection.Close();
//=================================================================================================================================
ALTER PROCEDURE [dbo].[TestInsertPhotograph]
@TestDesc AS VARCHAR(50),
@TestPhotograph AS IMAGE,
@TestThumbnail AS IMAGE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO [TblTestPhotograph]([TestDesc],[TestPhotograph],[TestThumbnail])
VALUES(@TestDesc, @TestPhotograph, @TestThumbnail)
END
Method Two, here I am trying to save image in database using typed dataset and getting XML from it and passing to stored procdure with XML data type as input parameter. Inside stored procedure I have used OpneXml method to read incoming xml and insert in table. But when I try to retirve the same its not giveing proper output. Even I tryied to map data inserted with Method I its not same in table even the image is selected same.
//Method II
//Save Image using stored procedure with xml datatype as an parameter.
//=================================================================================================================================
//Create sql connection object and assign connection string from web.config file
ObjSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNECTIONSTRING"].ConnectionString.ToString());
//Opening connection
ObjSqlConnection.Open();
//=================================================================================================================================
//=================================================================================================================================
//Create SQL Command object
ObjSqlCommand = new SqlCommand();
//Assign Connection object to SQL Command object
ObjSqlCommand.Connection = ObjSqlConnection;
//Setting Command type as stored procedure to SQL Command object
ObjSqlCommand.CommandType = CommandType.StoredProcedure;
//Assigning stored procdure name to SQL Command object
ObjSqlCommand.CommandText = "TestInsertPhotographWithXML";
//=================================================================================================================================
//=================================================================================================================================
//Using typed dataset
//Create typed dataset object
DataSetPhotograph ObjDataSetPhotograph = new DataSetPhotograph();
//Set dataset name as ROOT
ObjDataSetPhotograph.DataSetName = "ROOT";
//Set dataset namespace as blank
ObjDataSetPhotograph.Namespace = "";
//Create row object of typed dataset
DataSetPhotograph.TblTestPhotographRow ObjTblTestPhotographRow = ObjDataSetPhotograph.TblTestPhotograph.NewTblTestPhotographRow();
//=================================================================================================================================
//=================================================================================================================================
//Assign column value as image description
ObjTblTestPhotographRow[ObjDataSetPhotograph.TblTestPhotograph.TestDescColumn] = TxtPhotoDesc.Text.ToString();
//=================================================================================================================================
//=================================================================================================================================
//Create file stream object and open slected file from file upload control.
ObjFileStream = new FileStream(FileUploadPhoto.PostedFile.FileName, FileMode.Open, FileAccess.Read);
//Create Binary reader object to read stream from file stream object
ObjBinaryReader = new BinaryReader(ObjFileStream);
//Create Byte array and read bytes in array to File Streams max length
Byte[] ObjByteArr = ObjBinaryReader.ReadBytes((Int32)ObjFileStream.Length);
//Binary reader close
ObjBinaryReader.Close();
//File stream close
ObjFileStream.Close();
//Set Image column value from Byte array
ObjTblTestPhotographRow[ObjDataSetPhotograph.TblTestPhotograph.TestPhotographColumn] = ObjByteArr;
//=================================================================================================================================
//Using GetThumbnailImage for creating Thumbnail
//Picture quality is good for Gif.
//=================================================================================================================================
//Create Image object from selected file from file upload control.
System.Drawing.Image ObjImage = System.Drawing.Image.FromFile(FileUploadPhoto.PostedFile.FileName);
//Create Image object to create thumbnail of selected file.
System.Drawing.Image ObjThumbnailImage = ObjImage.GetThumbnailImage(100, 100, new System.Drawing.Image.GetThumbnailImageAbort(ThumbnailCallback), IntPtr.Zero);
//Create Memory Stream object
ObjMemoryStream = new MemoryStream();
//Save Thumbnail image in Memory Stream object
ObjThumbnailImage.Save(ObjMemoryStream, System.Drawing.Imaging.ImageFormat.Jpeg);
//Create Byte array and read bytes in array from Memory Stream object
Byte[] ObjByteArrThumbnail = ObjMemoryStream.ToArray();
//Memory Stream object close
ObjMemoryStream.Close();
//Set thumbnail Image value from Byte Array.
ObjTblTestPhotographRow[ObjDataSetPhotograph.TblTestPhotograph.TestThumbnailColumn] = ObjByteArrThumbnail;
//=================================================================================================================================
//=================================================================================================================================
//Add row object of typed dataset in typed dataset table
ObjDataSetPhotograph.TblTestPhotograph.AddTblTestPhotographRow(ObjTblTestPhotographRow);
//=================================================================================================================================
//Add parameter to sql command object and set value from typed dataset using GetXml method
ObjSqlCommand.Parameters.Add("@XML", SqlDbType.Xml).Value = ObjDataSetPhotograph.GetXml();
//=================================================================================================================================
//=================================================================================================================================
//Execute sql command object
ObjSqlCommand.ExecuteNonQuery();
//Close sql connection
ObjSqlConnection.Close();
//=================================================================================================================================
ALTER PROCEDURE [dbo].[TestInsertPhotographWithXML]
@XML As XML
AS
BEGIN
SET NOCOUNT ON;
DECLARE @XMLDocumentHandle INT
EXEC sp_xml_preparedocument @XMLDocumentHandle OUTPUT, @XML
INSERT INTO [TblTestPhotograph]([TestDesc],[TestPhotograph],[TestThumbnail])
SELECT [TestDesc], [TestPhotograph], [TestThumbnail]
FROM OPENXML(@XMLDocumentHandle, ' /ROOT/TblTestPhotograph' , 2)
WITH
(
[TestDesc] VARCHAR(50),
[TestPhotograph] IMAGE,
[TestThumbnail] IMAGE
)
EXEC sp_xml_removedocument @XMLDocumentHandle
END
Below code is used to retive image from database and display in Image Control.
//Retrive Image in Image Control
if (Request.QueryString["ImageID"] == null)
{
ObjSqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["CONNECTIONSTRING"].ConnectionString.ToString());
ObjSqlConnection.Open();
ObjSqlCommand = new SqlCommand();
ObjSqlCommand.Connection = ObjSqlConnection;
ObjSqlCommand.CommandType = CommandType.Text;
ObjSqlCommand.CommandText = "TestDisplayPhotograph";
ObjSqlDataAdapter = new SqlDataAdapter();
ObjSqlDataAdapter.SelectCommand = ObjSqlCommand;
ObjDataSet = new DataSet();
ObjSqlDataAdapter.Fill(ObjDataSet);
ObjSqlConnection.Close();
Session["ObjDataSet"] = ObjDataSet;
}
else
{
ObjDataSet = (DataSet)Session["ObjDataSet"];
if (Request.QueryString["ImageID"].ToString() == "Image1")
{
Response.BinaryWrite((Byte[])ObjDataSet.Tables[0].Rows[0][1]);
}
if (Request.QueryString["ImageID"].ToString() == "Image2")
{
Response.BinaryWrite((Byte[])ObjDataSet.Tables[0].Rows[0][2]);
}
}
Stored procedure used to retrive Image data from databse.
ALTER PROCEDURE [dbo].[TestDisplayPhotograph]
AS
BEGIN
SET NOCOUNT ON;
Select a.[TestDesc],a.[TestPhotograph],a.[TestThumbnail]
From TblTestPhotograph As a
END