Published 4/30/2007 2:14:58 AM  in  MySQL

This exampe now uses the table we created in this article

The DDL to create it looks like this:

 CREATE TABLE `cust_file` (                  
             `id` int(11) NOT NULL auto_increment,     
             `customer_id` int(11) default NULL,       
             `filename` varchar(255) default NULL,     
             `filedata` blob,                          
             `contenttype` varchar(255) default NULL,  
             `length` int(11) default NULL,            
             PRIMARY KEY  (`id`)                       
           ) ENGINE=InnoDB DEFAULT CHARSET=latin1   

Here's the C# code:

        public void Mysql_File_Save(string sConnString, int nCustId, byte[] bData, string sName, string sContentType, int nContentLength)
        {
            using (MySql.Data.MySqlClient.MySqlConnection oConn = 
                new MySql.Data.MySqlClient.MySqlConnection(sConnString))
            {
                oConn.Open();

                MySql.Data.MySqlClient.MySqlCommand oCommand = oConn.CreateCommand();
                oCommand.Connection = oConn;


                //Add new 
                oCommand.CommandText = "insert into cust_file(customer_id, filename, filedata, contenttype, length) " + 
                    "values( ?in_customer_id, ?in_filename, ?in_filedata, ?in_contenttype, ?in_length)";
                //oCommand.CommandType = CommandType.StoredProcedure;

                MySql.Data.MySqlClient.MySqlParameter oParam = 
                    oCommand.Parameters.Add("?in_customer_id",
                        MySql.Data.MySqlClient.MySqlDbType.Int64);
                oParam.Value = nCustId;
                oParam = oCommand.Parameters.Add("?in_filename",
                    MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                oParam.Value = sName;
                oParam = oCommand.Parameters.Add("?in_filedata",
                        MySql.Data.MySqlClient.MySqlDbType.Blob);
                oParam.Value = bData;
                oParam = oCommand.Parameters.Add("?in_contenttype",
                    MySql.Data.MySqlClient.MySqlDbType.VarChar, 255);
                oParam.Value = sContentType;
                oParam = oCommand.Parameters.Add("?in_length",
                    MySql.Data.MySqlClient.MySqlDbType.Int64);
                oParam.Value = nContentLength;

                oCommand.ExecuteNonQuery();
                oConn.Close();

            }
        }

And to call it we must use the helper function FileToArray and also  GetMimeType. Also - when in a web scenario it might be helpful to see how to get Fileupload data into byte array.

Anyway it allows us to write code such as:

            byte[] bData = FileToArray("f:\\n1jpg.jpg");
            Mysql_File_Save("Database=sakila;Data Source=192.168.10.4;User id=stefan;Password=pekka",
                          1, bData, "N1jpg", MimeType("f:\\n1jpg.jpg"), bData.Length);