Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
522 views
in Technique[技术] by (71.8m points)

c# - How to insert BLOB datatype

I'm using the following code to insert into a blob field:


MySql.Data.MySqlClient.MySqlConnection conn;
MySql.Data.MySqlClient.MySqlCommand cmd;

conn = new MySql.Data.MySqlClient.MySqlConnection();
cmd = new MySql.Data.MySqlClient.MySqlCommand();

string SQL;
int FileSize;
byte[] rawData;
FileStream fs;

conn.ConnectionString = "server=192.168.1.104;uid=root;" +
        "pwd=root;database=cady234;";

fs = new FileStream(@"d:Untitled.gif", FileMode.Open, FileAccess.Read);
FileSize = (int)fs.Length;

rawData = new byte[FileSize];
fs.Read(rawData, 0, FileSize);
fs.Close();

conn.Open();

string strFileName = "test name";
SQL = "INSERT INTO file (file_name, file_size, file) VALUES ('" + strFileName + "', "+FileSize+", '"+rawData+"')";

cmd.Connection = conn;
cmd.CommandText = SQL;

cmd.ExecuteNonQuery();
conn.Close();

The insertion is ok but the image is not getting displayed while using "Open value in viewer":

enter image description here

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The binary data isn't being properly passed to your insert as you are using string concatenation - you'll get rawData.ToString() which probably just prints out the TypeName (hence your binary data being 13 bytes in length compared to the filesize of > 3000 bytes); try this instead:

byte[] rawData = File.ReadAllBytes(@"d:Untitled.gif");
FileInfo info = new FileInfo(@"d:Untitled.gif");

int fileSize = Convert.ToInt32(info.Length);

using(MySqlConnection connection = new MySqlConnection("server=192.168.1.104;uid=root;pwd=root;database=cady234;"))
{
    using(MySqlCommand command = new MySqlCommand())
    {
        command.Connection = connection;
        command.CommandText = "INSERT INTO file (file_name, file_size, file) VALUES (?fileName, ?fileSize, ?rawData);";
        MySqlParameter fileNameParameter = new MySqlParameter("?fileName", MySqlDbType.VarChar, 256);
        MySqlParameter fileSizeParameter = new MySqlParameter("?fileSize", MySqlDbType.Int32, 11);
        MySqlParameter fileContentParameter = new MySqlParameter("?rawData", MySqlDbType.Blob, rawData.Length);

        fileNameParameter.Value = "test name";
        fileSizeParameter.Value = fileSize;
        fileContentParameter.Value = rawData;

        command.Parameters.Add(fileNameParameter);
        command.Parameters.Add(fileSizeParameter);
        command.Parameters.Add(fileContentParameter);

        connection.Open();

        command.ExecuteNonQuery();

    }
}

I've introduced several concepts for you here; firstly, if you are going to load all of the binary data at once, simply use the static method File.ReadAllBytes - it's a lot less code.

Secondly, there is no need to use the fully qualified namespace each time - use the using directive

Thirdly, (slightly confusingly) there is a also a using statement in C#. This ensures that any object that implements IDisposable is properly cleaned up after itself. In the case of the connection, it will explicitly call Close and Dispose if you command succeeds or fails.

Finally, I've parameterized your query. Parameters are useful for many reasons; they help protect against SQL Injection, and, in this instance, they should also ensure that your data types are handled correctly. You can read more about SqlParameter (which, like, MySqlParameter is a database specific implementation but uses the same principles).

Tested as working with MySQL 5.5.15, MySQL Connector 5.2.7 running under .Net 4


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...