How to save a SQL Blob in C#
Tags:
This post is part of the SQLStringBuilder project. For the latest version of the code i suggest to take the source code from the CVS.
Today at work i had my daily dilemma: how can i save a blob field in a MySQL DB with c#??? The solution it’s pretty simple, but it can be hard to find.
The Blob is a binary field where data can be saved: the problem is that we must write these binary data in a textual SQL string. To resolve this problem we can convert the binary data in an hexadecimal representation in order to write the data in a textual format.
The resulting SQL will look like that:
INSERT INTO tableWithBlob VALUES(1,0xab425fa52d3e13);
Where 0x… are our binary data.
To start, we must first convert the data structure that we want to put in the database in a byte array (byte[]), or in a format in which we can access all structure’s bytes.
At this time to create the blob field to insert in the SQL string, we can use the following code stub:
byte [] blob = // binary data to save in the db
// create a StringBuider with the required space to allocate the hexadecimal blob.
// plus the first two characters (0x)
StringBuilder blobBuilder = new StringBuilder(blob.Length + 2);
blobBuilder.Append(”0x”);
// convert every byte in a hexadecimal char and append it to the StringBuilder
for (int i = 0; i < blob.Length; i++)
blobBuilder.Append(String.Format(”{0:x2}”, blob[i]));
string blobString = retBlob.ToString();
Simply, it’s not??
Se sei interessato a questo post, potresti anche provare a leggere:
- No related posts
11 Jun 2007 dzamir
this way just sucks, lots of cpu cycles to the trash. Coding is not just coding, is an art.
hint, use statements.
DO NOT USE THIS EXAMPLE. IT IS VERY AMATEURISH, AND WASTES TIME…
@elvena:
Lots of cpu cycles to the trash???? Explain me why statements are better.
@sponky:
Why?? Explain me a better method first, and I will add a note to the post .
if you dont like this solution, then can you tell us a better way please. because i want to insert a byte array in c# into the mysql database’s blob field. i didnt try this example but i couldnt find any other way yet
From the Mysql website saving an Image into a blob field, and it works…
MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;
conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();
string SQL;
UInt32 FileSize;
byte[] rawData;
FileStream fs;
conn.ConnectionString = “server=127.0.0.1;uid=root;” +
“pwd=12345;database=test;”;
try
{
fs = new FileStream(@”c:\image.png”, FileMode.Open, FileAccess.Read);
FileSize = fs.Length;
rawData = new byte[FileSize];
fs.Read(rawData, 0, FileSize);
fs.Close();
conn.Open();
SQL = “INSERT INTO file VALUES(NULL, ?FileName, ?FileSize, ?File)”;
cmd.Connection = conn;
cmd.CommandText = SQL;
cmd.Parameters.Add(”?FileName”, strFileName);
cmd.Parameters.Add(”?FileSize”, FileSize);
cmd.Parameters.Add(”?File”, rawData);
cmd.ExecuteNonQuery();
MessageBox.Show(”File Inserted into database successfully!”,
“Success!”, MessageBoxButtons.OK, MessageBoxIcon.Asterisk);
conn.Close();
}
catch (MySql.Data.MySqlClient.MySqlException ex)
{
MessageBox.Show(”Error ” + ex.Number + ” has occurred: ” + ex.Message,
“Error”, MessageBoxButtons.OK, MessageBoxIcon.Error);
}
Oh yeah, the:
“cmd.Parametes.Add”
Part is deprecated
Use instead:
MySqlParameter pFile = new MySqlParameter(”?File”, imgfile);
cmd.Parameters.Add(pFile);
And if you making a website/webservice, remove this part:
MessageBox.Show();
This is the perfect and one of the best solution to achieve this. Off course it will take resources, well not for mobile application. But calling it “AMATEURISH” will be ignorance.
Imho, good way - System.Convert.ToBase64String
Usando