How to add Mouse Hover Effect on Grid View

Recently, One of My Friend asked me that how can we add Mouse Hover Effect on Grid View. So I want to share it with you too.

Firstly, This is the Sql Table Structure:

Create a New Website by clicking File->New Website.

Then,

Add a connection String to the Web.config file

Then, Add a GridView to the .aspx page

Now Add a style tag in the head tag of the .aspx page. Here is the .aspx page.

Now, In the Code Behind file
Add a class level variable to store Connection String and in the pageload event retrieve the connection string that we stored in the web.config file. For eg:

    static string constring;
    protected void Page_Load(object sender, EventArgs e)
    {
        constring = ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
        BindData();
    }
    protected void BindData()
    {
        using (SqlConnection con = new SqlConnection(constring))
        {
            con.Open();
            SqlDataAdapter adap = new SqlDataAdapter("Select * from MyCustomers", con);
            DataSet ds = new DataSet();
            adap.Fill(ds, "MyCustomers");
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
        }
            foreach (GridViewRow row in GridView1.Rows)
            {
                row.Attributes.Add("onmouseover", "className = 'rowStyle'");
                row.Attributes.Add("onmouseout", "className = ''");
            }
    }

And run the Web site, Output will be shown like this

Conditional Formatting on GridView

In this post i'll show you how we can do conditional formatting on GridView Web Server Control.

Suppose we want to change the background of all the rows having country as "India" to Blue.

Firstly, This is the Sql Table Structure:

Create a New Website by clicking File->New Website.

Then,

Add a connection String to the Web.config file

Then, Add a GridView to the .aspx page and change the DataKeyNames attribute to the ID Column

Now, In the Code Behind file

Add a class level variable to store Connection String and in the pageload event retrieve the connection string that we stored in the web.config file. For eg:

    static string constring;
    protected void Page_Load(object sender, EventArgs e)
    {
        constring = ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
        BindData();
    }
    protected void BindData()
    {
        using (SqlConnection con = new SqlConnection(constring))
        {
            con.Open();
            SqlDataAdapter adap = new SqlDataAdapter("Select * from MyCustomers", con);
            DataSet ds = new DataSet();
            adap.Fill(ds, "MyCustomers");
            GridView1.DataSource = ds.Tables[0];
            GridView1.DataBind();
        }
    }

In the RowCreated event of the Grid View add the following code:
   protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.DataItem != null)
        {
            DataRowView gridrow = (DataRowView)e.Row.DataItem;
            string sex = Convert.ToString(gridrow["Country"]);
            if (sex.Trim() == "India")
            {
                e.Row.BackColor = System.Drawing.Color.LightBlue;
            }
        }
    }

And run the Web site, Output will be shown like this

How to find Duplicate Records from a SQL Table

In this post i'll show you how can we retrieve Duplicate Records from a SQL Table.

The syntax is
Select Column_Name,COUNT(*) from Table_Name GROUP BY Column_Name HAVING COUNT(*)>1 ORDER BY COUNT(*) DESC

WHERE Column_Name is the name of the column in which we are finding the duplicate records.

I am taking Titles table in pubs database as an example.

there are many titles which are published by different different Publishers. So, Suppose we want to find out how many titles have been published by a particular Publishers. To retrieve the desired information we have to write the query below.

select pub_id,COUNT(*) from titles group by pub_id having COUNT(*)>1 order by COUNT(*) desc


this will yield the result as:

Hope this will help you.

Retrieving Images from the database and showing in Datalist control in asp.net.

In this post i will show you How you can retrieve saved images from a SQL Database and show them in a datalist control in an asp.net website.


If you don't know how to save images into the database then you can read my recent post that i've made "Saving Images in a Database in an asp.net Website."

Click here for more details.

I am using C# as programming language and Sql Server as a back end.

Now that we have images in our database, We can easily retrieve those images and show them in a Image control in the Asp.net Page.


So, Here is the Table schema that we're going to use in this exercise.



Drag a Datalist control on to the form.
Set Repeat property to Flow, RepeatDirection Property to Horizontal and RepeatColumns to 2.

The following is the markup of Default.aspx page.



The following is the markup of Default.aspx.cs page.

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
static string constring;
protected void Page_Load(object sender, EventArgs e)
{
constring = ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
if (!IsPostBack)
{
BindData();
}
}

protected void BindData()
{
using (SqlConnection con = new SqlConnection(constring))
{
con.Open();
SqlDataAdapter adap = new SqlDataAdapter("Select EmpFName,EmpLName,EmpPhoto,EmpID from employee", con);
DataSet ds = new DataSet();
adap.Fill(ds, "Employee");
DataList1.DataSource = ds.Tables["Employee"].DefaultView;
DataList1.DataBind();
}
}


Add a connection string tag to the web.config file so that we can retrive that connection information whereever we want.



Now as our images are in the database, and we want to show them in an .aspx page, We need a handler for our images who can handle the retrieval of images.

So, Add an handler.ashx to your website by clicking Website->AddNewItem and Selecting Generic Handler from the AddNewItem Dialog box. Name it as Handler.ashx

and type the following code in your handler.ashx file

using System;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

public class Handler : IHttpHandler
{
static string constring;
public void ProcessRequest (HttpContext context)
{
constring = ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
SqlCommand cmd = new SqlCommand("Select EmpPhoto from Employee where EmpID=@EmpID", con);
cmd.Parameters.AddWithValue("EmpID", context.Request.QueryString["EmpID"].ToString());
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
context.Response.BinaryWrite((byte[])dr["EmpPhoto"]);
dr.Close();
}
}

public bool IsReusable
{
get
{
return false;
}
}
}


Now run your website, and You will see all the images in the DataList Like this.

Saving Images in a Database in Asp.Net

In this post i will show you step by step procedure of how to save images in a SQL Database in Asp.Net.

I am using C# as programming language and Sql Server as a back end.

First of all we need to create a datatable where we will be storing the images in the database.
Use any database where you want to create the datatable.

This is how the Datatable structure looks like:



Now Create the table by typing:

CREATE TABLE [dbo].[Employee]
(
[EmpID] [int] IDENTITY(10001,1) NOT NULL,
[EmpPhoto] [image] NULL
)


Now, create a new Web Site the file menu->New Website option.

Then drag a FileUpload control on to the form.

Then drag a button on the form.

Then drag a Label on the form.

Change the Id of the Button to btnUpload and text to Upload.
Change the Id of the Label to lblMessage.

Double click on the Upload Button to generate the default event handler for browse button and type the following code into it.

protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.PostedFile.FileName != "" && FileUpload1.PostedFile != null)
{
HttpPostedFile file = FileUpload1.PostedFile;
byte[] size = new byte[file.ContentLength];
file.InputStream.Read(size, 0, (int)file.ContentLength);
using (SqlConnection con = new SqlConnection(constring))
{
SqlCommand cmd = new SqlCommand("insert Employee (EmpPhoto) values (@Photo)", con);
cmd.Parameters.Add("Photo", SqlDbType.VarBinary, size.Length).Value = size;
con.Open();
int result = cmd.ExecuteNonQuery();
if (result > 0)
{
lblMessage.Text = "File Uploaded";
}
else
{
lblMessage.Text = "File not uploaded";
}
con.Close();
cmd.Dispose();
con.Dispose();
}
}
}


Now that, you have done all the coding. Just Press F5 to run the Website

Click the browse button to browse a image file.
Now click the upload button to upload the image to the database.
Message will be shown to you stating that your image is uploaded successfully.


Now, We have images stored in the database. We can show them into the Image Control in our Website. We You want to show images in the Gridview, then read my post "Retrieving images from the database"

Click here for more details.

Retrieving Images from the database and showing in Gridview control in asp.net.

In this post i will show you How you can retrieve saved images from a SQL Database and show them in a Gridview control in an asp.net website.

If you don't know how to save images into the database then you can read my recent post that i've made "Saving Images in a Database in an asp.net Website."

Click here for more details.

I am using C# as programming language and Sql Server as a back end.

Now that we have images in our database, We can easily retrieve those images and show them in a Image control in the Asp.net Page.


So, Here is the Table schema that we're going to use in this exercise.



Drag a gridview control on to the form.
Set AutoGeneratecolumns property to false.

The following is the markup of Default.aspx page.




The following is the markup of Default.aspx.cs page.


using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
static string constring;
protected void Page_Load(object sender, EventArgs e)
{
constring = ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
if (!IsPostBack)
{
BindData();
}
}

protected void BindData()
{
using (SqlConnection con = new SqlConnection(constring))
{
con.Open();
SqlDataAdapter adap = new SqlDataAdapter("Select EmpFName,EmpLName,EmpPhoto,EmpID from employee", con);
DataSet ds = new DataSet();
adap.Fill(ds, "Employee");
GridView1.DataSource = ds.Tables["Employee"].DefaultView;
GridView1.DataBind();
}
}
}


Add a connection string tag to the web.config file so that we can retrive that connection information whereever we want.



Now as our images are in the database, and we want to show them in an .aspx page, We need a handler for our images who can handle the retrieval of images.

So, Add an handler.ashx to your website by clicking Website->AddNewItem and Selecting Generic Handler from the AddNewItem Dialog box. Name it as Handler.ashx

and type the following code in your handler.ashx file


using System;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

public class Handler : IHttpHandler
{
static string constring;
public void ProcessRequest (HttpContext context)
{
constring = ConfigurationManager.ConnectionStrings["myConn"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
SqlCommand cmd = new SqlCommand("Select EmpPhoto from Employee where EmpID=@EmpID", con);
cmd.Parameters.AddWithValue("EmpID", context.Request.QueryString["EmpID"].ToString());
con.Open();
SqlDataReader dr = cmd.ExecuteReader();
dr.Read();
context.Response.BinaryWrite((byte[])dr["EmpPhoto"]);
dr.Close();
}
}

public bool IsReusable
{
get
{
return false;
}
}

}

Now run your website, and You will see all the images in the gridview's second column beneath Employee Name in the first column. Like this.

Retrieving Images from the database.

In this post i will show you How you can retrieve saved images from a SQL Database and show them in a picture box.

If you don't know how to save images into the database then you can read my recent post that i've made "Saving Images in a Database."

Click here for more details.

I am using C# as programming language and Sql Server as a back end.

Now that we have images in our database, We can easily retrieve those images and show them in a picture box control in the GUI Application.


So, Here is the Table schema that we're going to use in this exercise.




You can use the previous application that we created while storing images into the database or choose a new application.

If you're creating a new application, then drag a Picturebox, Button and textbox to your form.

Name the Button control as btnShow and textbox as txtID.

Double click the btnShow control to generate the default event handler for this.

and write the following code into it.

SqlConnection con = new SqlConnection("data source=param;initial catalog=post;Integrated Security=True");
SqlCommand command = new SqlCommand("SELECT EmpPhoto FROM Employee WHERE EmpID = @EID", con);
command.Parameters.AddWithValue("@EID", txtID.Text);
SqlDataAdapter adap = new SqlDataAdapter();
adap.SelectCommand = command;
con.Open();
Byte[] storedImage =null;
DataSet ds = new DataSet();
adap.Fill(ds, "Employee");
if (ds.Tables[0].Rows[0].IsNull("EmpPhoto"))
{
MessageBox.Show("Image Not Found");
}
else
{
storedImage = (byte[])(ds.Tables[0].Rows[0][0]);
con.Close();
Image newImage;
using (MemoryStream ms = new MemoryStream(storedImage))
{
newImage = Image.FromStream(ms);
}
pictureBox1.Image = newImage;
}



Now, we're done with coding part.
Run the application.

Form will open to you and insert the id of the employee whose image you want to retrieve and click show button.