Storing Images in MySQL using ASP.NET

• Download demo project - 25.1 Kb

Introduction

This article describes how to upload images to a web server using ASP.NET. All the data is stored in a MySQL database. This article is targeted towards a little more intermediate programmer who has some understanding of ASP.NET with C#, SQL and relational databases. First section describes how to set up the database and second part describes how to code uploading the files and how to view them back as a gallery. Code has been tested with JPEG and GIF files.

Setting Up the Database

The database that I have used is MySQL. Information on how to obtain and install MySQL can be obtained from here. To make the process of creation and interacting with the database, I used MySQL control center. This allows a visual way for creating and interacting with a MySQL database.

MySQL comes with a default database called test. And I will be using this database.

The next thing is to create a table called file with the following columns.

• ID � Select it to be a timestamp to create it as the primary key of the table.
• Extension � Select it as varchar.
• Data � Select it as longblob.

To connect to the MySQL database, MySQL ODBC driver has to be downloaded. The version of MySQL ODBC driver is 3.51 that I have used for this article. All the code to interact with database is placed in a DataAccess class.

public class DataAccess
{
    private string _strConn = 
        @"Driver=   {MySQLODBC 3.51 Driver};SERVER=localhost;DATABASE=test;"; 

    private OdbcConnection _objConn;

    public DataAccess()
    {
        this._objConn = new OdbcConnection(this._strConn);  
    }
    // This function adds the Images to database


    public string addImage(byte [] buffer,string extension)
    {
        string strSql = "SELECT * FROM File";
        DataSet ds = new DataSet("Image");
        OdbcDataAdapter tempAP = new OdbcDataAdapter(strSql,this._objConn);
        OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP);
        tempAP.Fill(ds,"Table");

        try
        {
            this._objConn.Open();
            DataRow objNewRow = ds.Tables["Table"].NewRow();
            objNewRow["Extension"] = extension;
            objNewRow["Data"] = buffer;
            ds.Tables["Table"].Rows.Add(objNewRow);
            // trying to update the table to add the image

            tempAP.Update(ds,"Table"); 
        }
        catch(Exception e){return e.Message;}
        finally{this._objConn.Close();}
        return null;
    }
    // This function to get the image data from the database


    public byte [] getImage(int imageNumber)
    {
        string strSql = "SELECT * FROM File";
        DataSet ds = new DataSet("Image");
        OdbcDataAdapter tempAP = new OdbcDataAdapter(strSql,this._objConn);
        OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP);
        tempAP.Fill(ds,"Table");

        try
        {
            this._objConn.Open();
            byte [] buffer = (byte [])ds.Tables["Table"].Rows[imageNumber]["Data"];
            return buffer;
        }
        catch{this._objConn.Close();return null;}
        finally{this._objConn.Close();}            
    }
    // Get the image count


    public int getCount()
    {
        string strSql = "SELECT COUNT(Data) FROM File";
        DataSet ds = new DataSet("Image");
        OdbcDataAdapter tempAP = new OdbcDataAdapter(strSql,this._objConn);
        OdbcCommandBuilder objCommand = new OdbcCommandBuilder(tempAP);
        tempAP.Fill(ds,"Table");

        try>
        {
            this._objConn.Open();
            int count = (int)ds.Tables["Table"].Rows[0][0];
            return count;
        }
        catch{this._objConn.Close();return 0;}
        finally{this._objConn.Close();}
    }

}

Getting the User Uploaded files

To upload the files to the web server, a very simple ASP.NET web form is used, which is composed of a file field and a submit button. The Web form file in the project is Upload.aspx and the code is place in Upload.aspx.cs. The file is obtained and put in the database in the Page_Load function. Now the code takes a look into the Request.Files collection. As the interface allows to upload only one file, therefore, we check it if there is a file pending on IIS. The code checks for the mime type of the file if it is an image it accepts, otherwise it just displays a message that mime type is not supported. If the file is an image, data is read in bytes and inserted into the MySQL database using the DataAccess class object.

private void Page_Load(object sender, System.EventArgs e)
{
    //Checking if there are any files avaiable on IIS.

    if(Request.Files.Count != 0)
    {               
        HttpPostedFile httpFile = Request.Files[0];
        // Checking for extension

        string extension = this.getFileExtension(httpFile.ContentType);
        if(extension == null )
        {
            Response.Write("Mime type not Supported");
            return;
        }
        System.IO.BufferedStream bf = new BufferedStream(httpFile.InputStream);
        byte[] buffer = new byte;  
        bf.Read(buffer,0,buffer.Length);               
        // Creating the database object

        DataAccess data = new DataAccess();
        // Adding files to the database.

        data.addImage(buffer,extension);
        Response.Write("Image Added!");
 
    }
}

Displaying Upload File

Now, to display the uploaded files for the user, another Web form is setup in a file called View.aspx. Getting the image data is done by another file called show.aspx.

private void Page_Load(object sender, System.EventArgs e)
{
    // Put user code to initialize the page here

    Data.DataAccess data = new Data.DataAccess();
    int imagenumber = 0;
    try
    {
        imagenumber = int.Parse(Request.QueryString["image"]);
    }
    catch(System.ArgumentNullException ee)
    {
        imagenumber = 0;
    }

    byte []  buffer = data.getImage(imagenumber);
    System.IO.MemoryStream stream1 = new System.IO.MemoryStream(buffer,true);   
    stream1.Write(buffer,0,buffer.Length);
    Bitmap m_bitmap = (Bitmap) Bitmap.FromStream(stream1,true);
    Response.ContentType = "Image/jpeg";
    m_bitmap.Save(Response.OutputStream,System.Drawing.Imaging.ImageFormat.Jpeg);
}

View.aspx allows the user to go to the next file by clicking next link.