Image Upload and View on SQL Server in ASP.Net With Automatic ID Generation

This article helps you to upload your images to an ASP.NET website and store all the details regarding the image in a SQL Server database.

Step 1: Creating Database to store image information

Create a database ImagesDB on SQL Server with Windows Authentication and create a table [ImagesTB] with this structure: 

Column Name	Column Type
ImageId	Varchar(50)
ImageName	Varchar(50)
UploadDate	Varchar(50)
UploadTime	Varchar(50)

Step 2: Creating a Web form to upload image

Now we need to create a webpage to get the image details from the user that looks like:



I created a method [createid()] to generate an automatic imageid.

private string createid()
{
      SqlConnection con = new SqlConnection();
      con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["imageconnection"].ConnectionString;
      string qry = "select top 1 ImageId from ImageTB order by ImageId desc";
      SqlCommand cmd = new SqlCommand(qry, con);
      string id = "";
      try
      {
            con.Open();
            id = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
      }
      catch (SqlException ex)
      {
            Response.Write("<script>alert('" + ex.Message.ToString() + "')</script>");
      }
      if (id == "")
      {
            id = "pic001";
      }
      else if (id != "")
      {
            string id1 = id.Substring(3, id.Length - 3);
            int i = Convert.ToInt32(id1);
            i++;
            if (i >= 1 && i < 10)
            {
                  id = "pic00" + i;
            }
            else if (i >= 10 && i < 100)
            {
                  id = "pic0" + i;
            }
            else if (i >= 100)
            {
                  id = "pic" + i;
            }
      }
      return id;
}

And another methed to upload the image to the web server. My website has a folder called UploadedImages that contains all the uploaded images.

protected void Button1_Click(object sender, EventArgs e)
{
      string fn = "";
      string id = "";
      String fileExtension;
      Boolean fileOK;
      String[] allowedExtensions = { ".gif", ".png", ".jpeg", ".jpg", ".bmp" };

      fileExtension = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower();
      fileOK = false;

      for (int i = 0; i < allowedExtensions.Length; i++)
      {
            if (fileExtension == allowedExtensions[i])
            {
                  fileOK = true;
            }
      }

      if (fileOK)
      {
            fn = TextBox1.Text + fileExtension;
            if (TextBox2.Text == "")
            {
                  TextBox2.Text = TextBox1.Text;
            }

            SqlConnection con = new SqlConnection();
            con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["imageconnection"].ConnectionString;
            SqlCommand cmd = new SqlCommand("insert into imageTB(ImageId,ImageName,ImagePath,UploadDate,UploadTime) values(@a,@b,@c,@d,@ee)", con);
            cmd.Parameters.Add("@a", SqlDbType.VarChar, 50).Value = TextBox1.Text;
            cmd.Parameters.Add("@b", SqlDbType.VarChar, 50).Value = TextBox2.Text;
            cmd.Parameters.Add("@c", SqlDbType.VarChar, 50).Value = "~/UploadedImages/" + TextBox1.Text+fileExtension;
            cmd.Parameters.Add("@d", SqlDbType.VarChar, 50).Value = Label5.Text;
            cmd.Parameters.Add("@ee", SqlDbType.VarChar, 50).Value = Label7.Text;

            try
            {
                  con.Open();
                  int status=cmd.ExecuteNonQuery();
                  if (status > 0)
                  {
                        String path = Server.MapPath("~/UploadedImages/");
                        FileUpload1.PostedFile.SaveAs(path + fn);
                        Label9.Text = TextBox2.Text + " is uploaded";
                        Label9.ForeColor = System.Drawing.Color.Green;
                        TextBox1.Text = createid();
                        TextBox2.Text = "";
                        Label5.Text = System.DateTime.Now.ToShortDateString();
                        Label7.Text = System.DateTime.Now.ToShortTimeString();
                  }
                  else
                  {
                        Label9.Text = "Image is not uploaded";
                        Label9.ForeColor = System.Drawing.Color.Red;
                  }


            }
            catch (SqlException ex)
            {
                  Response.Write("<script>alert('" + ex.Message.ToString() + "')</script>");
            }
            finally
            {
                  con.Close();
            }
      }
      else
      {
            Response.Write("<script>alert('Invalid file format')</script>");
            TextBox2.Text = "";
            Label5.Text = System.DateTime.Now.ToShortDateString();
            Label7.Text = System.DateTime.Now.ToShortTimeString();
      }
}

Step 3: Creating a Web form to View image

Now we are creating a webform to view images. All we need to do is to load all the image names from the database to a listbox, then by selecting an image name from the listbox we need to display the image in an asp:image control.

 

1. Loading an image name to a listbox at Page_Load event:
   

   protected void Page_Load(object sender, EventArgs e)
   {
         if (!IsPostBack)
         {
               SqlConnection con = new SqlConnection();
               con.ConnectionString System.Configuration.ConfigurationManager.ConnectionStrings["imageconnection"].ConnectionString;
   
               SqlDataAdapter ad = new SqlDataAdapter("Select * from imagetb", con);
   
               DataTable dt = new DataTable();
   
               ad.Fill(dt);
   
               for (int i = 0; i < dt.Rows.Count; i++)
               {
                     ListBox1.Items.Add(dt.Rows[i][1].ToString());
               }
         }
   }

2. Loading an image to an image control:
   

   protected void ListBox1_SelectedIndexChanged(object sender, EventArgs e)
   {
         SqlConnection con = new SqlConnection();
         con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["imageconnection"].ConnectionString;
         con.Open();
         SqlDataAdapter ad = new SqlDataAdapter("Select * from imagetb", con);
         con.Close();
         DataTable dt = new DataTable();
   
         ad.Fill(dt);
   
         Image1.ImageUrl= dt.Rows[ListBox1.SelectedIndex][2].ToString();
   
         dt.Dispose();
   }