Hi All,
Welcome again.. I always wanted to use SQL Server as a storage area to save the files using .NET. Below is a simple example using Windows forms and SQL Server express. Do let me know what you think in the comments section.
Step 1 Create the tables in SQL Server
I have created a small file with as few columns as possible:
CREATE TABLE [dbo].[Test_FileTable](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](max) NULL,
[Data] [varbinary](max) NULL,
[Size] [int] NULL,
[Description] [varchar](max) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH
(PAD_INDEX
= OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS
= ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
Step 2 Create the windows project with two forms. In my example, I have used three forms. One as a menu, one for uploading files and other for downloading. Here is the settings file:
xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="ConnectionString" value="Data Source=LocalHost\SQLExpress;Initial
Catalog=SampleDB;Integrated Security=SSPI"/>
<add key="InsertQuery" value="Insert into Test_FileTable (Name,Size,Data,Description)
values(@strName,@intSize,@binData,@strDescription)"/>
<add key="SelectQuery" value="Select ID,Name as FileName,Size,Description from
Test_FileTable"/>
<add key="DataQuery" value="Select Data from Test_FileTable where ID=@i_ID"/>
<add key="FileNameQuery" value="Select Name as FileName from Test_FileTable where ID=@i_ID" />
<add key="TableName" value="Test_FileTable"/>
<add key="TempPath" value="D:\Temp\"/>
</appSettings>
</configuration>
Step 3 : Do the actual code for saving/retrieving the files.
Saving to DB:
private static void
UploadFile(string FilePath, string FileDescription)
{
SqlConnection
oConn ;
SqlCommand
oCmd;
string
ConnectionString,CommandString;
byte[]
BytBuffer;
try
{
if (File.Exists(FilePath))
{
ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
CommandString =
System.Configuration.ConfigurationManager.AppSettings["InsertQuery"].ToString();
oConn = new
SqlConnection(ConnectionString);
oConn.Open();
/*
Parameters(@strName,@intSize,@binData,@strDescription)
*
*/
oCmd = new
SqlCommand(CommandString, oConn);
oCmd.CommandText = CommandString;
oCmd.CommandType = System.Data.CommandType.Text;
oCmd.Parameters.Add("@strName", System.Data.SqlDbType.VarChar );
oCmd.Parameters.Add("@intSize", System.Data.SqlDbType.Int);
oCmd.Parameters.Add("@binData", System.Data.SqlDbType.VarBinary);
oCmd.Parameters.Add("@strDescription", System.Data.SqlDbType.VarChar);
oCmd.Parameters["@strName"].Value =
FilePath.Substring(FilePath.LastIndexOf(@"\")+1);
BytBuffer = File.ReadAllBytes(FilePath);
oCmd.Parameters["@intSize"].Value = BytBuffer.Length;
oCmd.Parameters["@binData"].Value = BytBuffer;
oCmd.Parameters["@strDescription"].Value =
FileDescription;
oCmd.ExecuteNonQuery();
MessageBox.Show("File uploaded to DB", "Sucess!", MessageBoxButtons.OK,
MessageBoxIcon.Information);
/*
* Warning: This code might fail if you
attempt to store
* very large files in to the database.
* For that, just modify the buffer
variable such that it reads a
* block of approx. few megabytes
* from the database at once.
*
On Most modern computers, this code should work just fine
*/
}
}
catch (Exception ex)
{
System.Console.WriteLine(ex.Message);
}
}
Retrieving from DB:
private byte[] GetFileFromDB(int
id)
{
byte[]
Return = null;
SqlConnection
oConn;
SqlCommand
oCMD;
DataTable
oDT;
try {
oConn = new
SqlConnection();
//Get the
connection string from DB
oConn.ConnectionString =
System.Configuration.ConfigurationManager.AppSettings["ConnectionString"].ToString();
oConn.Open();
oCMD = new
SqlCommand();
oCMD.Connection = oConn;
oCMD.CommandType = System.Data.CommandType.Text;
oCMD.CommandText =
System.Configuration.ConfigurationManager.AppSettings["DataQuery"].ToString();//
oCMD.Parameters.Add(new SqlParameter("@i_ID", id));
//I have
structured this query such that you it will just return the file data
oDT = new
DataTable();
Return = (byte[])
oCMD.ExecuteScalar();
/*
* Warning: This code might fail if you
attempt to store
* very large files in to the database.
* For that, just modify the buffer
variable such that it reads a
* block of approx. few megabytes
* from the database at once.
*
On Most modern computers, this code should work just fine
*/
}
catch (Exception ex)
{
System.Console.WriteLine(ex.Message);
}
return
Return;
}
Screen shots:
Menu Form:
Uploading a file:
Downloading a file:
