Click here to Skip to main content
Click here to Skip to main content

Storing Images in MySQL using ASP.NET

, 18 Apr 2004
Rate this:
Please Sign up or sign in to vote.
This article describes on how to set up a MySQL database to store images with ASP.NET and display it back to the browser.

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.

MySql Control Center

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.

MySql Control Center Table View

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.Length>;  
        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!");
 
    }
}

MySql Control Center Table View

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.

View.aspx

License

This article, along with any associated source code and files, is licensed under The Apache License, Version 2.0

About the Author

Fahad Azeem
Web Developer
United States United States
My name is Fahad Azeem. I am interested in distributed software development. Currently I am working for a software consulting company in Chicago which developes software in .NET platform.
 
My Blog: http://fahadaz.blogspot.com

Comments and Discussions

 
Questionerror in project Pinmembercsp56729-Aug-12 5:52 
QuestionRe: error in project PinmemberVittal ks4-Nov-12 20:58 
QuestionSave image in mysql workbench and retrieve in gridview in asp.net PinmemberMember 881523127-Jul-12 2:17 
GeneralFull code needed Pinmemberlitiso16-Sep-09 7:43 
Generalwhere to learn Pinmembernotexpert14-Sep-09 11:11 
GeneralRe: where to learn PinmemberFahad Azeem14-Sep-09 12:06 
GeneralRe: where to learn Pinmembernotexpert14-Sep-09 12:49 
Generaltype or namespace name 'Bitmap' could not be found Pinmembernotexpert14-Sep-09 9:38 
GeneralRe: type or namespace name 'Bitmap' could not be found PinmemberFahad Azeem14-Sep-09 9:40 
GeneralRe: type or namespace name 'Bitmap' could not be found Pinmembernotexpert14-Sep-09 9:57 
QuestionHELP: Buffer cannot be null. Parameter name: buffer Pinmemberjack package14-Jul-09 22:49 
Questionhow to slove Pinmemberglitto8-Oct-07 1:12 
QuestionHow overcome below error Pinmemberbochkari19-Aug-07 20:30 
GeneralParameter is not Valid Pinmemberderektaprell10-Apr-07 15:48 
GeneralRe: Parameter is not Valid PinmemberFahad Azeem12-Apr-07 5:30 
GeneralRe: Parameter is not Valid Pinmembermadis8221-May-10 5:48 
GeneralUploading iamges to mysql database Pinmemberv.venkannababu1-Jun-06 0:49 
GeneralWhere do you referred for this Topic Pinmembervivekthangaswamy27-Nov-05 19:37 
GeneralRe: Where do you referred for this Topic PinmemberFahad Azeem28-Nov-05 3:48 
GeneralHTTP 405 Pinmemberfavila9-Jun-05 16:46 
GeneralRe: HTTP 405 PinmemberFahad Azeem12-Jun-05 7:23 
GeneralRe: HTTP 405 Pinmemberfavila13-Jun-05 6:39 
GeneralAuto-increment fields in database Pinmembercyber0ne14-Feb-05 5:00 
GeneralRe: Auto-increment fields in database Pinmemberdaddion16-Feb-05 7:23 
GeneralRe: Auto-increment fields in database Pinmembercyber0ne16-Feb-05 9:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 19 Apr 2004
Article Copyright 2004 by Fahad Azeem
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid