| In order to run the sample code, you'll need to execute the SQL Server script directly below to create the Images table and stored procedures SaveImage and GetImage. Of course, you'll also want to set the appropriate permissions for the database user you use. Then, open up a new console application in Visual Studio.NET and copy and paste the Console Application source code below. You'll notice a variable for the connection string, image path, and image files that will need to be adjusted accordingly. |
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Images]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Images]
GO
CREATE TABLE [dbo].[Images] (
[ImageID] [int] IDENTITY (1, 1) NOT NULL ,
[ImageFile] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetImage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetImage]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SaveImage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SaveImage]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.GetImage
(
@ImageID int
)
as
select ImageFile
from Images
where ImageID = @ImageID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE dbo.SaveImage
(
@ImageID int = 0,
@ImageFile image,
@NewID int output
)
AS
declare @rc int
select @rc = 0
if (@ImageID = 0)
BEGIN
insert Images (ImageFile) values (@ImageFile)
select @NewID = @@identity
if (@@Error <> 0) select @rc=1
Goto OnExit
END
Update Images
Set ImageFile = @ImageFile
where ImageID = @ImageID
if (@@Error <> 0) select @rc=1
Goto OnExit
select @NewID = @ImageID
OnExit:
return @rc
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
|
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace SaveImagesToSQLServer
{
class Class1
{
[STAThread]
static void Main(string[] args)
{
Class1 oMain = new Class1();
string sImagePath = @"c:\temp";
string sConStr = "Data Source=localhost;User ID=myuser;Password=mypwd;Initial Catalog=MYDATABASE";
string sImageFileName1 = "test1.gif";
string sImageFileName2 = "test2.gif";
FileStream oImg;
BinaryReader oBinaryReader;
byte[] oImgByteArray;
int nImageID=0;
DataTable oTable;
try
{
oImg = new FileStream(sImagePath + @"\" + sImageFileName1,FileMode.Open,FileAccess.Read);
oBinaryReader = new BinaryReader(oImg);
oImgByteArray = oBinaryReader.ReadBytes((int)oImg.Length);
oBinaryReader.Close();
oImg.Close();
nImageID = oMain.SaveImage(sConStr,nImageID,oImgByteArray);
if (nImageID > 0)
{
oTable = oMain.GetDataTable("GetImage " + nImageID.ToString(),sConStr);
foreach(DataRow oRow in oTable.Rows)
{
oImgByteArray = (byte[])oRow["ImageFile"];
FileStream oOutput = File.Create(sImagePath + @"\" + sImageFileName2,oImgByteArray.Length);
oOutput.Write(oImgByteArray,0,oImgByteArray.Length);
oOutput.Close();
}
}
}
catch (Exception e) { Console.WriteLine(e.Message); }
Console.ReadLine();
}
public DataTable GetDataTable(string sQuery,string sConnectionString)
{
SqlConnection oConn = new SqlConnection();
DataTable oTable = new DataTable();
try
{
oConn.ConnectionString = sConnectionString;
oConn.Open();
SqlDataAdapter oDA = new SqlDataAdapter("EXEC " + sQuery,oConn);
oDA.Fill(oTable);
oConn.Close();
}
catch (Exception e) { throw new Exception(e.Message);}
finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
return oTable;
}
public int SaveImage(string sConnectionString,int nImageID, byte[] oImage)
{
string sRet="";
int nRet=0;
int nNewID=0;
SqlConnection oConn = new SqlConnection();
try
{
oConn.ConnectionString = sConnectionString;
oConn.Open();
SqlCommand oCmd = new SqlCommand("dbo.SaveImage",oConn);
oCmd.CommandType=CommandType.StoredProcedure;
oCmd.Parameters.Add(new SqlParameter("@RETURN_VALUE",SqlDbType.Int,0));
oCmd.Parameters.Add(new SqlParameter("@ImageID",SqlDbType.Int,0));
oCmd.Parameters.Add(new SqlParameter("@ImageFile",SqlDbType.Image, 2147483647));
oCmd.Parameters.Add(new SqlParameter("@NewID",SqlDbType.Int,0));
oCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
oCmd.Parameters["@NewID"].Direction = ParameterDirection.Output;
oCmd.Parameters["@ImageID"].Value = nImageID;
oCmd.Parameters["@ImageFile"].Value = oImage;
oCmd.ExecuteNonQuery();
sRet = oCmd.Parameters["@RETURN_VALUE"].Value.ToString();
nRet = int.Parse(oCmd.Parameters["@NewID"].Value.ToString());
if ((nRet > 0) && (sRet == "0"))
{
nNewID = nRet;
}
}
catch (Exception e) { Console.WriteLine(e.Message);}
finally { if (oConn.State == ConnectionState.Open) { oConn.Close(); } }
return nNewID;
}
}
}
|