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);
}
}
}
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
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.
I thing this is because the data is not a string but another for like zip as mentioned above (This is at least in python)