How to Store and Read Byte Array in SQL Server Database using C# .Net

Description:

Byte is an immutable value type that represents unsigned integers with values that range from 0 to 255. You can almost convert any kind of data into Byte Array(Byte []) like File, Image, Xml and etc..In SQL Server, we have enough 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 Insert/Store/Save Byte [] into SQL Server table and Read/Retrieve Byte [] data from SQL Server table.

Summary:

Insert Byte Array into SQL Server Table using C# .NET

Use the below C# function to store/save Byte [] into SQL Server table as Binary datatype.

public static void InsertByteArrayintoSqlDatabase()
{
    string sampleText = "Hello World!";

    byte[] byteData = Encoding.UTF8.GetBytes(sampleText);

    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, [BinData] varbinary(max))";
        SqlCommand command = new SqlCommand(createTableQuery, sqlconnection);
        command.ExecuteNonQuery();

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

        // Insert Byte [] Value into Sql Table by SqlParameter
        SqlCommand insertCommand = new SqlCommand(insertXmlQuery, sqlconnection);
        SqlParameter sqlParam = insertCommand.Parameters.AddWithValue("@BinData", byteData);
        sqlParam.DbType = DbType.Binary;
        insertCommand.ExecuteNonQuery();
    }
}

Retrieve/Read Byte Array from SQL Server Database using C# .NET

Use the below C# code to read/retrieve Byte [] from SQL Server table that was stored as binary type.

public static void ReadByteArrayFromSqlDatabase(int id)
{
    using (SqlConnection sqlconnection = new SqlConnection(@"Data Source=.SQLExpress; 
Initial Catalog=MorganDB; Integrated Security=SSPI;"))
    {
        sqlconnection.Open();

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

        // Read Byte [] Value from Sql Table 
        SqlCommand selectCommand = new SqlCommand(selectQuery, sqlconnection);
        SqlDataReader reader = selectCommand.ExecuteReader();
        if (reader.Read())
        {
            byte[] byteData = (byte[])reader[0];
            string strData = Encoding.UTF8.GetString(byteData);
            Console.WriteLine(strData);
        }
    }
}

How to Insert and Read Byte Array into SQL Server Table using C# .Net

You can almost save any kind of data using varbinary datatype and even though we have image datatype, varbinary is the recommended datatype to store image in sql server instead of image datatype. check this link: http://msdn.microsoft.com/en-us/library/ms187993.aspx

Thanks,
Morgan,
Software Developer


Advertisement

2 thoughts on “How to Store and Read Byte Array in SQL Server Database using C# .Net”

  1. Hello, I used your method and modified below, but the string returned contained unreadable characters that looks like this: “x�m��j�0\u0010D�\u0003���)|��L\u0002W���\u0014nRo�H���N2+�\t\u0018�{�\u0014�\u0002�c\u0018�l(�5�T�/�]���}\u0003\0PYL”

    public string ReadByte(int id, int seq)
    {
    conn.Open();
    string strData = string.Empty;

    string selectQuery = string.Format(@”Select [TXT_NOTES_BINARY] From [PROFILE_ACTIVITY] Where ID_PROFILE={0} and NUM_SEQ_ACTIVITY={1}”, id,seq);

    // Read Byte [] Value from Sql Table
    SqlCommand selectCommand = new SqlCommand(selectQuery, conn);
    SqlDataReader reader = selectCommand.ExecuteReader();
    if (reader.Read())
    {
    byte[] byteData = (byte[])reader[0];
    strData = Encoding.UTF8.GetString(byteData);
    Console.WriteLine(strData);
    }
    conn.Close();
    return strData;
    }

    What did I do wrong? Thank you so much.

    Reply

Leave a Comment