Using .NET / C# to Save and Retrieve files to/from SQL Server

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:

Source Code for the project : https://docs.google.com/open?id=0B1z9Rc2ld5VPQklmcnVqZXE0aU0