HOW TO UPLOAD AND DISPLAY IMAGES IN ASP.NET USING MYSQL DATABASE

Introduction 

Download WebSite5.zip - 4.94 KB

Hi all,     

I made a lot of research to find out how to upload and display images in asp.net from mysql database.There is already a post for mysql connection in codeproject.  image_asp.aspx

Create a database named "test" in mysql and table named image with parameters as below
img_id[int autoincrement pk]
img_name[varchar[45]]
img_file[longblob]
img_type[varchar[45]]

 Create files as shown below: 

First create the necessary controls from default.aspx  

<form id="form1" runat="server" enctype= "multipart/form-data" >
    <div>
        <input id="Text1" type="text" runat="server" onserverchange="Text1_ServerChange"  />
        <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ErrorMessage="RequiredFieldValidator" ControlToValidate= "Text1"></asp:RequiredFieldValidator></div>
            
        <input id="File1" type="file" runat="server"/>
        <asp:Button ID="Button1" runat="server" OnClick="Button1_Click" Text="Upload Me" />
        <asp:Button ID="Button2" runat="server" OnClick="Button2_Click" Text="Show Image" />
    </form>

 Uploading image to mysql database

Now write code in default.cs to create a connection to mysql database and insert image in it.

Note:This is done using the datatype "longblob" which is similar to image datatype in mssql

We use sessions inorder to store the name of the file to display it in next instance.

  protected void Button1_Click(object sender, EventArgs e)
    {
        Stream img_strm = File1.PostedFile.InputStream;
        int img_len = File1.PostedFile.ContentLength;
        string strtype = File1.PostedFile.ContentType;
        strname = Text1.Value;
        // Response.SetCookie = Text1.Value;
        Session["t"] = strname;
        byte[] imgData = new byte[img_len];
        int n = img_strm.Read(imgData, 0, img_len);
        int result = saveToDb(strname, imgData, strtype);
    }
    private int saveToDb(string imgName, byte[] imgbin, string imgContenttype)
    {



        string db = "server=localhost;database=test;uid=root;password=techsoft";
        MySqlConnection conn = new MySqlConnection(db);
        MySqlCommand cmd = new MySqlCommand("insert into image(img_name,img_file,img_type) values(?img_name,?img_file,?img_type)", conn);

        //MySqlParameter param0 = new MySqlParameter("?img_id", MySqlDbType.Int16, 20);
        //param0.Value = ;
        //cmd.Parameters.Add(param0);

        MySqlParameter param0 = new MySqlParameter("?img_name", MySqlDbType.VarChar, 45);
        param0.Value = imgName;
        cmd.Parameters.Add(param0);

        // MySqlParameter param1 = new MySqlParameter("?img_file", MySqlDbType.VarChar, 45);
        MySqlParameter param1 = new MySqlParameter("?img_file", MySqlDbType.LongBlob, 10);
        param1.Value = imgbin;
        cmd.Parameters.Add(param1);

        MySqlParameter param2 = new MySqlParameter("?img_type", MySqlDbType.VarChar, 45);
        param2.Value = imgContenttype;
        cmd.Parameters.Add(param2);

        conn.Open();

        int num = cmd.ExecuteNonQuery();

        conn.Close();
        return num;
    }
    protected void Button2_Click(object sender, EventArgs e)
    {
        //View v = new View();
        //v.Load();        
        Server.Transfer("view.aspx");} 

 Displaying Image in new page

 Create a new page named view.aspx to display images.
In the below we use the session object to display the appropriate image from mysql database.
Data is retrieved using procedure and displayed using binarywriter.

The image can be furthur produced in a fixed height and width after small modifications.

protected void Page_Load(object sender, EventArgs e)
    {
      
                MySqlConnection conn = new MySqlConnection(db);
                conn.Open();
                string s;
                s = Session["t"].ToString();

                string commantext = "select img_id,img_file,img_type,img_name from image where img_name='"+s+"'";


              //  string commantext = "select img_id,img_file,img_type,img_name from image";
              //  DataSet ds = MySqlHelper.ExecuteDataset(conn, commantext);

               MySqlCommand cmd = new MySqlCommand(commantext,conn);
                cmd.Parameters.Add("?img_id", MySqlDbType.Int32).Value = s;

              //  DataTable dt = ds.Tables[0];
                DataTable dt = GetData(cmd);

                       while(dt !=null )
                       {
                          Byte[] bytes = (Byte[])dt.Rows[0]["img_file"];
                         //  Byte[] bytes = (Byte[])dt.Rows[1][]  ;                    
                           Response.Buffer = true;
                           Response.Charset = "";
                           Response.Cache.SetCacheability(HttpCacheability.NoCache);
                           Response.ContentType = dt.Rows[0]["img_type"].ToString();
                           Response.AddHeader("content-disposition", "attachment;filename="

                           + dt.Rows[0]["img_name"].ToString());
                           
                           Response.BinaryWrite(bytes);
                           Response.Flush();
                           Response.End();
                       }          
                       conn.Close();               
        }


    private DataTable GetData(MySqlCommand cmd)
    {

        DataTable dt = new DataTable();
        //String strConnString = System.Configuration.ConfigurationManager    .ConnectionStrings["conString"].ConnectionString;

        MySqlConnection con = new MySqlConnection(db);
        MySqlDataAdapter sda = new MySqlDataAdapter();

        cmd.CommandType = CommandType.Text;
        cmd.Connection = con;

        try
        {
            con.Open();
            sda.SelectCommand = cmd;
            sda.Fill(dt);
            return dt;
        }

        catch
        {   return null;
        }

        finally
        {   con.Close();
            sda.Dispose();
            con.Dispose();
        }
    }

Any suggestions are welcome on this.