Although you can programmatically add column fields to the Columns collection, it is easier to list the column fields declaratively in the GridView control and then use the Visible property of each column field to show or hide each column field.
If the Visible property of a column field is set to false, the column is not displayed in the GridView control and the data for the column does not make a round trip to the client. If you want the data for a column that is not visible to make a round trip, add the field name to the DataKeyNames property.
Step 1. Create a C# "ASP.NET Empty Web Application" in Visual Studio 2010 or Visual Web Developer 2010. Name it as "CSASPNETGridView ". The project includes two web form pages for demonstrating two ways to bind data source with the GridView, name them as "GridView.aspx", "DataInMemory.aspx".
Step 2. Before we start to write code, we need install SqlServer 2008 R2 Express and create a database file as the data source of GridView control. Add an Asp.net folder "App_Data" and create a Sql Server Database,"GridView.mdf". Add "Person" table with three fields "PersonID","FirstName","LastName", PersonID is the primary key of the table, and you can insert some default values in Person table.
Step 3. Drag and drop a GridView control, two LinkButton controls, two TextBox controls and a Panel control into DataFromDataBase.aspx page. The GridView is used to display, edit and delete the data of database file, the TextBox and LinkButton are used to insert new items to the data table. In the first step, check your controls and rename them and set some basic properties of the GridView, such as GridView's templates and events.
public partial
class GridView
: System.Web.UI.Page
{
protected
void Page_Load(object
sender, EventArgs e)
{
// The
Page is accessed for the first time.
if
(!IsPostBack)
{
//
Enable the GridView paging option and
//
specify the page size.
gvPerson.AllowPaging = true;
gvPerson.PageSize = 15;
//
Enable the GridView sorting option.
gvPerson.AllowSorting = true;
//
Initialize the sorting expression.
ViewState["SortExpression"] = "PersonID ASC";
//
Populate the GridView.
BindGridView();
}
}
private
void BindGridView()
{
// Get
the connection string from Web.config.
// When
we use Using statement,
// we
don't need to explicitly dispose the object in the code,
// the
using statement takes care of it.
using
(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
//
Create a DataSet object.
DataSet
dsPerson = new DataSet();
// Create a SELECT
query.
string
strSelectCmd = "SELECT
PersonID,LastName,FirstName FROM Person";
//
Create a SqlDataAdapter object
//
SqlDataAdapter represents a set of data commands and a
// database
connection that are used to fill the DataSet and
//
update a SQL Server database.
SqlDataAdapter
da = new SqlDataAdapter(strSelectCmd,
conn);
//
Open the connection
conn.Open();
//
Fill the DataTable named "Person" in DataSet with the rows
//
returned by the query.new n
da.Fill(dsPerson, "Person");
//
Get the DataView from Person DataTable.
DataView
dvPerson = dsPerson.Tables["Person"].DefaultView;
//
Set the sort column and sort order.
dvPerson.Sort = ViewState["SortExpression"].ToString();
//
Bind the GridView control.
gvPerson.DataSource = dvPerson;
gvPerson.DataBind();
}
}
protected
void gvPerson_PageIndexChanging(object sender, GridViewPageEventArgs
e)
{
// Set
the index of the new display page.
gvPerson.PageIndex =
e.NewPageIndex;
// Rebind
the GridView control to
// show
data in the new page.
BindGridView();
}
protected
void gvPerson_RowCancelingEdit(object sender, GridViewCancelEditEventArgs
e)
{
}
protected
void gvPerson_RowDataBound(object sender, GridViewRowEventArgs
e)
{
if
(e.Row.RowType == DataControlRowType.DataRow)
{
//
Make sure the current GridViewRow is either
// in
the normal state or an alternate row.
if
(e.Row.RowState == DataControlRowState.Normal
|| e.Row.RowState == DataControlRowState.Alternate)
{
// Add client-side
confirmation when deleting.
((LinkButton)e.Row.Cells[1].Controls[0]).Attributes["onclick"] = "if(!confirm('Are
you certain you want to delete this person ?')) return false;";
}
}
}
protected
void gvPerson_RowDeleting(object sender, GridViewDeleteEventArgs
e)
{
using
(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
//
Create a command object.
SqlCommand
cmd = new SqlCommand();
//
Assign the connection to the command.
cmd.Connection = conn;
//
Set the command text
//
SQL statement or the name of the stored procedure
cmd.CommandText = "DELETE FROM Person WHERE PersonID = @PersonID";
//
Set the command type
//
CommandType.Text for ordinary SQL statements;
//
CommandType.StoredProcedure for stored procedures.
cmd.CommandType = CommandType.Text;
//
Get the PersonID of the selected row.
string
strPersonID = gvPerson.Rows[e.RowIndex].Cells[2].Text;
//
Append the parameter.
cmd.Parameters.Add("@PersonID", SqlDbType.Int).Value
= strPersonID;
//
Open the connection.
conn.Open();
//
Execute the command.
cmd.ExecuteNonQuery();
}
// Rebind
the GridView control to show data after deleting.
BindGridView();
}
protected
void gvPerson_RowEditing(object sender, GridViewEditEventArgs
e)
{
// Make the GridView
control into edit mode
// for
the selected row.
gvPerson.EditIndex =
e.NewEditIndex;
// Rebind
the GridView control to show data in edit mode.
BindGridView();
// Hide
the Add button.
lbtnAdd.Visible = false;
}
protected
void gvPerson_RowUpdating(object sender, GridViewUpdateEventArgs
e)
{
using
(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString()))
{
//
Create a command object.
SqlCommand
cmd = new SqlCommand();
//
Assign the connection to the command.
cmd.Connection = conn;
//
Set the command text
//
SQL statement or the name of the stored procedure
cmd.CommandText = "UPDATE Person SET LastName = @LastName, FirstName =
@FirstName WHERE PersonID = @PersonID";
// Set the
command type
//
CommandType.Text for ordinary SQL statements;
//
CommandType.StoredProcedure for stored procedures.
cmd.CommandType = CommandType.Text;
//
Get the PersonID of the selected row.
string
strPersonID = gvPerson.Rows[e.RowIndex].Cells[2].Text;
string
strLastName = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox1")).Text;
string
strFirstName = ((TextBox)gvPerson.Rows[e.RowIndex].FindControl("TextBox2")).Text;
//
Append the parameters.
cmd.Parameters.Add("@PersonID", SqlDbType.Int).Value
= strPersonID;
cmd.Parameters.Add("@LastName", SqlDbType.NVarChar,
50).Value = strLastName;
cmd.Parameters.Add("@FirstName", SqlDbType.NVarChar,
50).Value = strFirstName;
//
Open the connection.
conn.Open();
//
Execute the command.
cmd.ExecuteNonQuery();
}
// Exit
edit mode.
gvPerson.EditIndex = -1;
// Rebind
the GridView control to show data after updating.
BindGridView();
// Show
the Add button.
lbtnAdd.Visible = true;
}
protected
void gvPerson_Sorting(object
sender, GridViewSortEventArgs e)
{
string[]
strSortExpression = ViewState["SortExpression"].ToString().Split(' ');
// If the
sorting column is the same as the previous one,
// then
change the sort order.
if
(strSortExpression[0] == e.SortExpression)
{
if
(strSortExpression[1] == "ASC")
{
ViewState["SortExpression"] = e.SortExpression + " " + "DESC";
}
else
{
ViewState["SortExpression"] = e.SortExpression + " " + "ASC";
}
}
// If sorting column is another column,
// then
specify the sort order to "Ascending".
else
{
ViewState["SortExpression"] = e.SortExpression + " " + "ASC";
}
// Rebind
the GridView control to show sorted data.
BindGridView();
}
}
}
No comments:
Post a Comment