Tim
Life is good Joined: Nov 2007
Location: Kalamazoo

I have looked high and low for good documentation on how to upload BLOB's to MySQL. More over how to do it in code behind with C# and zero stored procs. I would not normally do such a thing, but I was given a project at work to do just such a thing. So sseing as I'm doing it I might as well share the knowledge. I'll outline how to do this and you'll be able to download the code at the end of the article, including the db create script.

First we need to create the table. To truly upload a file or image and be able to pull it back out I always want to gather as much information as possible. For this example we'll pull the following data: file name, file size (or length), file type, and of course the actuall file data.

CREATE TABLE `Files` (
`ID` int(11) NOT NULL auto_increment,
`FileData` longblob,
`FileName` varchar(50) default NULL,
`FileType` varchar(50) default NULL,
`FileLength` int(11) default 0,
`UploadDate` datetime default Now(),
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I'm leaving it up to you to create the table in your MySQL database. Now the you have your table created go ahead and fire up your favorite IDE, for this example I'll be using Visual Studio 2008 (VS) but you can use 2005 or the express edition. Start by creating a new web site. If you are in VS 2005 go ahead and create an AJAX enabled site so we can add in some fun extras later on.

Once in VS you'll want to open your newly created web.config and create the connection string to your db. For ease of use and because ASP.NET makes to ever so easy this will be entered in the web.config as follows:

<add key="cnnMySQLNET" value="port=3306;UID=user;Pwd=Password;Database=FileUpload;
SERVER=localhost;Persist Security Info=True;" />

Now it's time for the actual work. Lets start out with the front end, for this just use the Default.aspx file that VS created for you. Just drop in a FileUpload control, a Label for messages and a Button. When adding the button you will need to add an OnClick event to tie it to the code behind, for this instance it will be called

<div>
    <asp:FileUpload ID="FileUpload1" runat="server" /> 
    <asp:Label ID="lblMsg" runat="server" Visible="false">
    <br /><asp:Button ID="btnUpdate" runat="server" Text="Upload" OnClick="btnUpdate_Click"/>
</div>

Now to build the code behind. In the button click were actually going to be calling another sub. Doing this it will be easier to add multiple FileUpload controls and will then only have to add another CheckAndSaveUpload sub to the btnUpdate_Click event but I'll go into that in the future when some AJAX get added to the mix.

protected void btnUpdate_Click( object sender, EventArgs e)
{
    if (Page.IsValid)
    {
        CheckAndSaveUpload(FileUpload1);
    }
} 

Now that the button click is set, lets add the CheckAndSaveUpload sub. Here we will pull our needed info from the file.

protected void CheckAndSaveUpload(FileUpload oUpload)
{
    //Check to see if the file is empty or null
    if (oUpload.PostedFile != null && oUpload.PostedFile.ContentLength > 0)
    { 
        //Get the file length and data
        byte[] bData = new byte[oUpload.PostedFile.ContentLength];
        oUpload.PostedFile.InputStream.Read(bData, 0, oUpload.PostedFile.ContentLength);
        //Get the file name
        System.IO.FileInfo oInfo = new System.IO.FileInfo(oUpload.PostedFile.FileName);

Create the MySQL connection using your connection string from the web.config. 

        //Create the connection
        MySqlConnection MyConn = new MySqlConnection(ConfigurationManager.AppSettings["cnnMySQLNET"]);
        MySqlCommand MyComm = new MySqlCommand();
        MyComm.CommandType = System.Data.CommandType.Text;
        MyComm.Connection = MyConn;

Create the SQL string

//Create the SQL string and link to the CommandText
        String sSQL = "";
        sSQL = "Insert into Files (FileData, FileName, FileLength, FileType) ";
        sSQL += " values (?FileData,?FileName,?FileLength,?FileType);";
        MyComm.CommandText = sSQL;

Add the Params

        MyComm.Parameters.Add("?FileData", bData);
        MyComm.Parameters.Add("?FileName", oInfo.Name);
        MyComm.Parameters.Add("?FileLength", oUpload.PostedFile.ContentLength);
        MyComm.Parameters.Add("?FileType", oUpload.PostedFile.ContentType);

Open the connection and execute the query the close the connection.

        MyComm.Connection.Open();
        MyComm.ExecuteNonQuery();
        MyComm.Connection.Close();
    }

Now finish up the file empty or null catch.

    else
    {
        lblMsg.Text = "File is empty or there is no file selected, please choose another file";
    }
}

If all went well your file should have been inserted into your database. Now it's time to add a little more to the front end, but I'll save that for another time. Hope this helps. Oh and dont forget to download the site if needed.