Click here to Skip to main content
12,254,932 members (59,991 online)
Click here to Skip to main content
Add your own
alternative version

Stats

212.5K views
3.6K downloads
61 bookmarked
Posted

Storing Images in MySQL using ASP.NET

, 18 Apr 2004 Apache
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

Share

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

You may also be interested in...

Comments and Discussions

 
Questionerror in project Pin
csp56729-Aug-12 5:52
membercsp56729-Aug-12 5:52 
QuestionRe: error in project Pin
Vittal ks4-Nov-12 20:58
memberVittal ks4-Nov-12 20:58 
QuestionSave image in mysql workbench and retrieve in gridview in asp.net Pin
Member 881523127-Jul-12 2:17
memberMember 881523127-Jul-12 2:17 
GeneralFull code needed Pin
litiso16-Sep-09 7:43
memberlitiso16-Sep-09 7:43 
Generalwhere to learn Pin
notexpert14-Sep-09 11:11
membernotexpert14-Sep-09 11:11 
GeneralRe: where to learn Pin
Fahad Azeem14-Sep-09 12:06
memberFahad Azeem14-Sep-09 12:06 
GeneralRe: where to learn Pin
notexpert14-Sep-09 12:49
membernotexpert14-Sep-09 12:49 
Generaltype or namespace name 'Bitmap' could not be found Pin
notexpert14-Sep-09 9:38
membernotexpert14-Sep-09 9:38 
GeneralRe: type or namespace name 'Bitmap' could not be found Pin
Fahad Azeem14-Sep-09 9:40
memberFahad Azeem14-Sep-09 9:40 
GeneralRe: type or namespace name 'Bitmap' could not be found Pin
notexpert14-Sep-09 9:57
membernotexpert14-Sep-09 9:57 
QuestionHELP: Buffer cannot be null. Parameter name: buffer Pin
jack package14-Jul-09 22:49
memberjack package14-Jul-09 22:49 
Questionhow to slove Pin
glitto8-Oct-07 1:12
memberglitto8-Oct-07 1:12 
QuestionHow overcome below error Pin
bochkari19-Aug-07 20:30
memberbochkari19-Aug-07 20:30 
GeneralParameter is not Valid Pin
derektaprell10-Apr-07 15:48
memberderektaprell10-Apr-07 15:48 
GeneralRe: Parameter is not Valid Pin
Fahad Azeem12-Apr-07 5:30
memberFahad Azeem12-Apr-07 5:30 
GeneralRe: Parameter is not Valid Pin
madis8221-May-10 5:48
membermadis8221-May-10 5:48 
GeneralUploading iamges to mysql database Pin
v.venkannababu1-Jun-06 0:49
memberv.venkannababu1-Jun-06 0:49 
GeneralWhere do you referred for this Topic Pin
vivekthangaswamy27-Nov-05 19:37
membervivekthangaswamy27-Nov-05 19:37 
GeneralRe: Where do you referred for this Topic Pin
Fahad Azeem28-Nov-05 3:48
memberFahad Azeem28-Nov-05 3:48 
GeneralHTTP 405 Pin
favila9-Jun-05 16:46
memberfavila9-Jun-05 16:46 
GeneralRe: HTTP 405 Pin
Fahad Azeem12-Jun-05 7:23
memberFahad Azeem12-Jun-05 7:23 
GeneralRe: HTTP 405 Pin
favila13-Jun-05 6:39
memberfavila13-Jun-05 6:39 
GeneralAuto-increment fields in database Pin
cyber0ne14-Feb-05 5:00
membercyber0ne14-Feb-05 5:00 
GeneralRe: Auto-increment fields in database Pin
daddion16-Feb-05 7:23
memberdaddion16-Feb-05 7:23 
GeneralRe: Auto-increment fields in database Pin
cyber0ne16-Feb-05 9:33
membercyber0ne16-Feb-05 9:33 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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 | Terms of Use | Mobile
Web02 | 2.8.160426.1 | Last Updated 19 Apr 2004
Article Copyright 2004 by Fahad Azeem
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid