Tuesday, 27 May 2014

How to Store and Retrieve File in SQL Server Database using C# .Net

Description:

In this article I am going to write C# code to insert/save/store any type of file (pdf/txt/image/.zip) into Sql server database and then retrieve/read file from Sql server database using Binary datatype.

In SQL Server we have available datatypes to store string text, int, bool, datatime and even xml. But we don't have any provision to store some complex structured data like ZIP file and PDF file. To overcome this, we have the special datatype varbinary, this is C#'s datatype Byte Array equivalent in SQL Server. In this article, I am going write C# example to convert file into Byte Array and Insert/Store/Save Byte [] into SQL Server table, Read/Retrieve Byte [] data from SQL Server table and Convert into original file.

Summary:


Store/Insert File into SQL Server Database as Binary datatype

 We are doing two processes to store/save file into SQL Server table.
      i. Convert file content into Byte Array(Byte [])
      ii. Insert file content's Byte Array into Sql Server

Consider the text file sample.txt.
How to Store/Insert/Save and Retrieve/Read/Export File in SQL Server using C# .Net

public static void InsertFileintoSqlDatabase()
{
    string filePath = @"C:\sample.txt";

    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

        // create table if not exists 
        string createTableQuery = @"Create Table [MyTable](ID int, [FileData] varbinary(max))";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

        // Converts text file(.txt) into byte[]
        byte[] fileData = File.ReadAllBytes(filePath);

        string insertQuery = @"Insert Into [MyTable] (ID,[FileData]) Values(1,@FileData)";

        // Insert text file Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@FileData", fileData);
        sqlParam.DbType = DbType.Binary;
        insertCommand.ExecuteNonQuery();
    }
}

Retrieve/Read/Export File from SQL Server Database 

Use the below C# code to export/read/retrieve text file from SQL Server table that was stored as binary type and to save/export as new text file.
public static void ExportFileFromSqlDatabase(int ID)
        {
            using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.\SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
            {
                sqlconnection.Open();

                string selectQuery = string.Format(@"Select [FileData] From [MyTable] Where ID={0}"
                                    , ID);

                // Read File content from Sql Table 
                SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
                SqlDataReader reader = selectCommand.ExecuteReader();
                if (reader.Read())
                {
                    byte[] fileData = (byte[])reader[0];
                    // Write/Export File content into new text file
                    File.WriteAllBytes(@"C:\New_Sample.txt", fileData);
                }
            }
        }
New/Exported file output:
How to Store/Insert/Save and Retrieve/Read/Export File in SQL Server using C# .Net

 Varbinary is recommended datatype to store any kind of file in MS Sql Server.

Thanks,
Morgan,
Software Developer

Advertisements
Advertisements

No comments:

Post a Comment