ASP.NET2.0 the code for manipulating data with the Gridview control
- 2020-05-17 05:17:27
- OfStack
Among them, in the data control aspect, added many controls, among them Gridview control function 10 minutes powerful. In this article, we will explore some of the features and USES of the Gridview control. If you don't know much about the Gridview control, you can get an idea of the Gridview control by using the Gridview control in ASP.NET 2.0.
1. Insert a new record using Gridview
In the Gridview control, it is possible to insert a new record (see the Gridview control in ASP.NET 2.0), but if you want to do this in Gridview, in the last line of the Gridview control, it is convenient to provide a blank line for the user to enter the record to be entered. The implementation is described below.
First of all, we plan to let the user select. When the user needs to add 1 record, he/she will click the "add" button. Then, in the last 1 line of Gridview, a blank line will be displayed and the user will enter it by field, as shown in the following figure:
When the user decides not to enter a new blank record, he or she can press the "cancel" button to return and the blank line disappears. To achieve this effect, we can make full use of Gridview's footer templates for customization. Since there are three columns, the footer templates for each column are defined as follows:
The above code for Gridview, as you can see, is in column 1,2 < foottemplate > In the column, customerid and companyname are provided with two text boxes for user input, respectively, in column 3 < footertemplate > In the column, contracttitle. Is displayed as dropdownlistbox. Notice the event codes for the add and cancel buttons in footertemplate in column 3
The cancel button event is used to cancel the display of Gridview footer template, so the showfooter property is set to false, while the addbutton1 button is selected when the user decides to add a new record. At this time, the showfooter property is set to true to display foottemplate of each column, so as to display a new blank line.
In the event of the update code button1_click, the method Gridview1.footerrow.findcontrol will be used to extract the value of each field newly added by the user, and then assign the value to the insertparameters collection of sqldatasource (note 11 corresponding). Finally, the insert method of sqldatasource will be used to successfully add a new record to the database.
In addition, in order to display the data of customers table in database northwind when the form is loaded, the properties of sqldatsource1 need to be set as follows:
You must set the insertcommand and selectcommand properties, set the statements for data extraction and insertion, and set the type and name of each field in the insertparameters collection.
2. Update all Gridview records once
It is not uncommon to encounter situations where, of all the records listed in Gridview, multiple records have to be modified at the same time and saved to the database. So how do you do that in Gridview? In Gridview, there are two implementation methods, which are described below:
Let's start with the first method, which USES sqldatasource to update all records, but this method is slow because each record update requires data connection and updatecommand execution, which can affect performance. Its main code is as follows:
In the code above, we must first specify the updateparameters parameter set, which indicates which fields to update and what their types are. Then point out the updatecommand statement for sqldatasource. In the CLICK event of the update button button1, the for loop will be used in the form of traversal to check each line in Gridview, and the content of each updated text box will be put into sqldatasouce's updateparameters parameter. Finally, sqldatasource's update method will be called to complete the update.
Method 2 first iterates through every line in Gridview and connects the contents to be updated with SQL statement, and then finally updates them with command.ExecuteNonQuery (), which is more efficient. The main code is as follows:
One thing to note in particular is that, starting with vs.net 2005 beta 2, if you are using the database connection string configuration in web.config, you should write:
Then read in the program as follows:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString);
1. Insert a new record using Gridview
In the Gridview control, it is possible to insert a new record (see the Gridview control in ASP.NET 2.0), but if you want to do this in Gridview, in the last line of the Gridview control, it is convenient to provide a blank line for the user to enter the record to be entered. The implementation is described below.
First of all, we plan to let the user select. When the user needs to add 1 record, he/she will click the "add" button. Then, in the last 1 line of Gridview, a blank line will be displayed and the user will enter it by field, as shown in the following figure:
When the user decides not to enter a new blank record, he or she can press the "cancel" button to return and the blank line disappears. To achieve this effect, we can make full use of Gridview's footer templates for customization. Since there are three columns, the footer templates for each column are defined as follows:
<asp:Gridview ID="Gridview1" Runat="server" DataSourceID="SqlDataSource1" DataKeyNames="CustomerID" AutoGenerateColumns="False" ShowFooter="True">
<Columns>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="CustomerIDLabel" Runat="Server"><%# Eval("CustomerID") %></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="CustomerIDTextBox" Runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<ItemTemplate>
<asp:Label ID="CompanyNameLabel" Runat="Server"><%# Eval("CompanyName") %></asp:Label>
</ItemTemplate>
<FooterTemplate>
<asp:TextBox ID="CompanyNameTextBox" Runat="server"></asp:TextBox>
</FooterTemplate>
</asp:TemplateField>
<asp:TemplateField>
<FooterTemplate>
<asp:DropDownList ID="ContactTitleDropDownList" Runat="server" DataSourceID="SqlDataSource2" DataTextField="ContactTitle" DataValueField="ContactTitle">
</asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource2" Runat="server" SelectCommand="SELECT DISTINCT [ContactTitle] FROM [Customers]"
ConnectionString="server=localhost;uid=sa;password=xxx;database=northwind">
</asp:SqlDataSource>
<asp:Button ID="Button1" Runat="server" Text="Add" OnClick="Button1_Click" />
<asp:Button ID="CancelButton1" Runat="server" Text="Cancel" OnClick="CancelButton1_Click" />
</FooterTemplate>
<ItemTemplate>
<asp:DropDownList ID="ContactTitleDropDown" SelectedValue='<%# Bind("ContactTitle") %>' Runat="Server" DataSourceID="SqlDataSource3" DataTextField="ContactTitle" DataValueField="ContactTitle" ></asp:DropDownList>
<asp:SqlDataSource ID="SqlDataSource3" Runat="server" SelectCommand="SELECT DISTINCT [ContactTitle] FROM [Customers]"
ConnectionString="server=localhost;uid=sa;password=xxxx;database=northwind" EnableCaching="True">
</asp:SqlDataSource>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:Gridview>
The above code for Gridview, as you can see, is in column 1,2 < foottemplate > In the column, customerid and companyname are provided with two text boxes for user input, respectively, in column 3 < footertemplate > In the column, contracttitle. Is displayed as dropdownlistbox. Notice the event codes for the add and cancel buttons in footertemplate in column 3
<script runat="server">
void CancelButton1_Click(object sender, EventArgs e)
{
Gridview1.ShowFooter = false;
}
void AddButton1_Click(object sender, EventArgs e)
{
Gridview1.ShowFooter = true;
}
// point add Button to update the new record 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();
}
</script>
The cancel button event is used to cancel the display of Gridview footer template, so the showfooter property is set to false, while the addbutton1 button is selected when the user decides to add a new record. At this time, the showfooter property is set to true to display foottemplate of each column, so as to display a new blank line.
In the event of the update code button1_click, the method Gridview1.footerrow.findcontrol will be used to extract the value of each field newly added by the user, and then assign the value to the insertparameters collection of sqldatasource (note 11 corresponding). Finally, the insert method of sqldatasource will be used to successfully add a new record to the database.
In addition, in order to display the data of customers table in database northwind when the form is loaded, the properties of sqldatsource1 need to be set as follows:
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
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">
<InsertParameters>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
</InsertParameters>
</asp:SqlDataSource>
You must set the insertcommand and selectcommand properties, set the statements for data extraction and insertion, and set the type and name of each field in the insertparameters collection.
2. Update all Gridview records once
It is not uncommon to encounter situations where, of all the records listed in Gridview, multiple records have to be modified at the same time and saved to the database. So how do you do that in Gridview? In Gridview, there are two implementation methods, which are described below:
Let's start with the first method, which USES sqldatasource to update all records, but this method is slow because each record update requires data connection and updatecommand execution, which can affect performance. Its 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>
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>Untitled Page</title>
</head>
<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>
</asp:TemplateField>
<asp:TemplateField SortExpression="CompanyName" HeaderText="CompanyName">
<ItemTemplate>
<asp:TextBox Runat="server" Text='<%# Bind("CompanyName") %>' ID="TextBox2"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField SortExpression="ContactName" HeaderText="ContactTitle">
<ItemTemplate>
<asp:TextBox Runat="server" Text='<%# Bind("ContactTitle") %>' ID="TextBox3"></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:Gridview>
<asp:SqlDataSource ID="SqlDataSource1" Runat="server"
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">
<UpdateParameters>
<asp:Parameter Type="String" Name="CompanyName"></asp:Parameter>
<asp:Parameter Type="String" Name="ContactTitle"></asp:Parameter>
<asp:Parameter Type="String" Name="CustomerID"></asp:Parameter>
</UpdateParameters>
</asp:SqlDataSource>
<asp:Button ID="Button1" Runat="server" Text="Button" OnClick="Button1_Click" />
</div>
</form>
</body>
</html>
In the code above, we must first specify the updateparameters parameter set, which indicates which fields to update and what their types are. Then point out the updatecommand statement for sqldatasource. In the CLICK event of the update button button1, the for loop will be used in the form of traversal to check each line in Gridview, and the content of each updated text box will be put into sqldatasouce's updateparameters parameter. Finally, sqldatasource's update method will be called to complete the update.
Method 2 first iterates through every line in Gridview and connects the contents to be updated with SQL statement, and then finally updates them with command.ExecuteNonQuery (), which is more efficient. 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("';\n");
}
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString);
SqlCommand command = new SqlCommand(query.ToString(), con);
con.Open();
command.ExecuteNonQuery();
con.Close();
}
}
One thing to note in particular is that, starting with vs.net 2005 beta 2, if you are using the database connection string configuration in web.config, you should write:
<connectionStrings>
<add name="NorthwindConnectionString" connectionString="Data Source=LIAO;Initial Catalog=Northwind;User ID=sa;Password=xxxx" providerName="System.Data.SqlClient"/>
</connectionStrings>
Then read in the program as follows:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AppConnectionString1"].ConnectionString);