Manipulating data with the GridView control in asp.net2.0

Asp.net| Control | data

In ASP.net 2.0, many new functions and controls have been added, which have greatly improved in all respects compared to asp.net 1.0/1.1. Among them, in the data control aspect, has added many controls, in which the GridView control function is very formidable. In this article, you will explore some of the features and usages of the GridView control, and if you are not familiar with the GridView control, you can have a preliminary understanding of the GridView control using the "GridView Control in asp.net 2.0" article.

1. Insert new record with GridView

In the GridView control, you can implement an action to insert a new record (see Using the GridView control in ASP.net 2.0), but if you want to implement the last line of the GridView control in the GridView, It is certainly convenient to provide a blank line for the user to enter records to enter. The implementation method is described below.

First, we're going to let the user make the choice, when the user needs to add a record, click the New button, then in the last line of the GridView, display a blank line, let the user enter by field, as shown in the following figure:
　　

　　　
When the user decides not to enter a new blank record, you can press the Cancel button to return and the blank line disappears. To achieve this effect, we can make full use of the footer template feature of the GridView, since there are 3 columns, so in the footer template for each column, define the following:

<%# Eval ("CustomerID")%>

<%# Eval ("CompanyName")%>

Connectionstring= "Server=localhost;uid=sa;password=xxx;database=northwind" >

connectionstring= "Server=localhost;uid=sa;password=xxxx;database=northwind" enablecaching= "True"

Above is the code for the GridView, as you can see, in the first, two columns of columns, each provided the CustomerID and CompanyName two text boxes for user input, in the third column of the column, Displays the Contracttitle in the form of Dropdownlistbox. Note that the Add and cancel two buttons in the FooterTemplate in the third column have the following event code

When the Add button is clicked, the new record is updated to the database
void Button1_Click (object sender, EventArgs e)
{
TextBox CustomerID = Gridview1.FooterRow.FindControl ("Customeridtextbox") as TextBox;
TextBox companyName = Gridview1.FooterRow.FindControl ("Companynametextbox") as TextBox;
DropDownList ContactTitle = Gridview1.FooterRow.FindControl ("Contacttitledropdownlist") as DropDownList;
sqldatasource1.insertparameters["CustomerID"]. DefaultValue = Customerid.text;
sqldatasource1.insertparameters["CompanyName"]. DefaultValue = Companyname.text;
sqldatasource1.insertparameters["ContactTitle"]. Defaultvalue=contacttitle.selectedvalue;
Sqldatasource1.insert ();
}

An event in which the Cancel button is used to suppress the footer template of the GridView, so the Showfooter property is set to False and the Addbutton1 button is selected when the user determines the new record. The Showfooter property is set to True to display the foottemplate of each column to display a new blank row.

In the update Code button1_click event, You will first use the Gridview1.footerrow.findcontrol method to extract the values of each field that the user has added, and then assign the values to the InsertParameters collection of SqlDataSource (note that each corresponds), and finally use the SQLDATASOURC E's Insert method, you can successfully add a new record to the database.

In addition, to display data for the Customers table in the database Northwind when the form is loaded, you need to set the Sqldatsource1 properties, as follows:

insertcommand= INSERT into [Customers] ([CustomerID], [CompanyName], [ContactTitle]) VALUES (@CustomerID, @CompanyName , @ContactTitle) "
Selectcommand= "SELECT Top 5 [CustomerID], [CompanyName], [ContactTitle] from [Customers]"
Connectionstring= "Server=localhost;uid=sa;password=xxxxx;database=northwind" >

Where you must set the InsertCommand and SelectCommand properties, set up the statements for data extraction and insertion, and set the type and name of each field in the InsertParameters collection.
2. Update all the GridView records in a one-time

We often encounter situations where in all the records listed in the GridView, we sometimes have to modify multiple records at the same time and save them to the database. So how should it be implemented in the GridView? In the GridView, there are two methods of implementation, described separately below:

Let's look at the first method, which uses SqlDataSource to update all the records, but this method is slow, because each update record has to establish a data connection and perform UpdateCommand, which can affect performance. The main code is as follows:

<script runat= "Server" >
void Button1_Click (object sender, EventArgs e)
{
for (int i = 0; I Gridview1.Rows.Count i++)
{
GridViewRow row = Gridview1.rows[i];
Sqldatasource1.updateparameters[0]. DefaultValue = ((TextBox) row. Cells[0]. FindControl ("TextBox2")). Text;
SQLDATASOURCE1.UPDATEPARAMETERS[1]. DefaultValue = ((TextBox) row. CELLS[1]. FindControl ("TextBox3")). Text;
SQLDATASOURCE1.UPDATEPARAMETERS[2]. DefaultValue = Gridview1.datakeys[i]. Value.tostring ();
Sqldatasource1.update ();
}
}
</script>
<title> Untitled Page </title>
<body>
<form id= "Form1" runat= "Server"
<div>
<asp:gridview id= "Gridview1" runat= "Server" datasourceid= "SqlDataSource1" datakeynames= "CustomerID" Autogeneratecolumns= "False" >
<Columns>
<asp:templatefield sortexpression= "CustomerID" headertext= "CustomerID"
<ItemTemplate>
<asp:textbox runat= "Server" text= ' <%# Bind ("CustomerID")%> ' id= ' TextBox1 ' > </asp:TextBox>
</ItemTemplate>

selectcommand= "SELECT [CustomerID] , [CompanyName], [ContactName], [ContactTitle] from [Customers] "
updatecommand=" UPDATE [Customers] SET [CompanyName ] = @CompanyName, [ContactTitle] = @ContactTitle WHERE [CustomerID] = @CustomerID "
connectionstring=" server= Localhost;uid=sa;password=xxxx;database=northwind ">










 

In the above code, we must first specify a collection of updateparameters parameters, which indicates which fields to update, and what their types are. Then it points out the SqlDataSource UpdateCommand statement. In the Click event of the Update button button1, in the form of traversal, A for loop is used to check each row in the GridView, and the contents of each updated text box are placed in the sqldatasouce updateparameters parameter. Finally, call the SqlDataSource Update method to complete the update.

Method 2 uses the first traversal of each row in the GridView, and uses the SQL statement to connect the content to be updated, and then use the command last. ExecuteNonQuery () to update, the efficiency is high, the main code is as follows:

protected void Page_Load (object sender, EventArgs e)
{

if (! Page.IsPostBack)
{
SqlConnection con = new SqlConnection (configurationmanager.connectionstrings["AppConnectionString1"). ConnectionString);
SqlCommand command = new SqlCommand ("SELECT [CustomerID], [CompanyName], [ContactName], [ContactTitle] from [Customers]" , con);
Con. Open ();
Gridview1.datasource = command. ExecuteReader ();
Gridview1.databind ();
Con. Close ();
}
}
protected void Button1_Click (object sender, EventArgs e)
{
StringBuilder query = new StringBuilder ();
for (int i = 0; I Gridview1.Rows.Count i++)
{
GridViewRow row = Gridview1.rows[i];
String value1 = ((TextBox) row. Cells[0]. FindControl ("TextBox2")). Text.replace ("'", "" ");
String value2 = ((TextBox) row. CELLS[1]. FindControl ("TextBox3")). Text.replace ("'", "" ");
String value3 = Gridview1.datakeys[i]. Value.tostring ();
Query. Append ("UPDATE [Customers] SET [CompanyName] = '"). Append (value1). Append ("', [contacttitle] = '")
. Append (value2). Append ("' WHERE [CustomerID] = '"). Append (VALUE3). Append ("'; \ \");
}

SqlConnection con = new SqlConnection (configurationmanager.connectionstrings["AppConnectionString1"). ConnectionString);
SqlCommand command = new SqlCommand (query). ToString (), con);
Con. Open ();
Command. ExecuteNonQuery ();
Con. Close ();
}
}

One particular note is that, at the beginning of Vs.net 2, if you use the configuration of a database connection string in web.config, you should write as follows:

Then read the following in the program:

SqlConnection con = new SqlConnection (configurationmanager.connectionstrings["AppConnectionString1"). ConnectionString);