Use the gridview control in ASP. net2.0 to operate data

A new article by younger brother Article The original Article is posted on http://dev.yesky.com/msdn/283/2018783.shtml. for no picture, you can easily extract it as below:
In ASP. NET 2.0, many new functions and controls are added. Compared with Asp.net 1.0/1.1, they are greatly improved in various aspects. In terms of data controls, many controls are added, and the gridview control is very powerful. In this article, we will discuss some functional features and usage of the gridview control. If you do not know much about the gridview control, you can use ASP. NET 2.0 in the gridview control, to have a preliminary understanding of the gridview control.

1. Insert a new record using the gridview

In the gridview control, you can insert a new record (see ASP. NET 2.0 in the gridview control), but if you want to implement in the gridview control, in the last row of the gridview control, provide a blank line to the user to enter the record, it is undoubtedly very convenient. The following describes the implementation method.

First, we plan to ask the user to select a record. When the user needs to add a record, click the Add button and a blank row is displayed in the last row of the gridview, enter the user by field, as shown in:

When you decide not to enter a new blank record, you can press the "cancel" button to return the blank row. To achieve this, we can make full use of the footer template function of the gridview to customize, because there are three columns, so in the footer template of each column, the definition is as follows:

<Asp: gridview id = "gridview1" runat = "server" performanceid = "sqlperformance1" datakeynames = "mermerid" 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" performanceid = "sqlperformance2" datatextfield = "contacttitle" datavaluefield = "contacttitle"> </ASP: dropdownlist> <Asp: sqldatasource id = "sqldatasource2" runat = "server" selectcommand = "select distinct [contacttitle] from [MERs]" Connectionstring = "Server = localhost; uid = sa; Password = xxx; database = northwind"> </ASP: sqldatasource> <Asp: button id = "button1" runat = "server" text = "add" onclick = "button#click"/> <Asp: button id = "cancelbutton1" runat = "server" text = "cancel" onclick = "cancelbutton#click"/> </Footertemplate> <Itemtemplate> <Asp: dropdownlist id = "contacttitledropdown" selectedvalue = '<% # BIND ("contacttitle ") %> 'runat = "server" performanceid = "sql1_c4" datatextfield =" contacttitle "datavaluefield =" contacttitle "> </ASP: dropdownlist> <Asp: sqldatasource id = "sql1_c4" runat =" server "selectcommand =" select distinct [contacttitle] from [MERs]" Connectionstring = "Server = localhost; uid = sa; Password = xxxx; database = northwind" enablecaching = "true"> </ASP: sqldatasource> </Itemtemplate> </ASP: templatefield> </Columns> </ASP: gridview>

the preceding code is the Code of the gridview. You can see that in the column of the first and second columns, the customerid and companyName text boxes are provided for user input. In the column of the third column, the contracttitle is displayed as dropdownlistbox .. Note the ADD and cancel buttons in the footertemplate in the third column. Their Event code is as follows

The cancel button event is used to cancel displaying the footer template of the gridview. Therefore, the showfooter attribute is set to false, and the addbutton1 button is selected when the user decides to add a record, in this case, the showfooter attribute is set to true to display the foottemplate of each column to display a new blank row.

In the update code button#click event, gridview1.footerrow is used first. the findcontrol method extracts the values of the newly added fields, assigns values to the insertparameters set of sqldatasource (one-to-one correspondence must be noted), and finally uses the insert method of sqldatasource, you can add a new record to the database.

In addition, to display the data in the MERs table in the database northwind during form loading, you need to set the sqldatsource1 attribute, as shown in the following code:

<Asp: sqldatasource id = "sqldatasource1" runat = "server" Insertcommand = "insert into [MERs] ([customerid], [companyName], [contacttitle]) values (@ customerid, @ companyName, @ contacttitle )" Selectcommand = "select top 5 [mermerid], [companyName], [contacttitle] from [MERs]" 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 attributes, set the data extraction and insertion statements, and set the type and name of each field in the insertparameters set.
2. update all the gridview records at a time.

We often encounter this situation. In all records listed in the gridview, we sometimes need to modify multiple records at the same time and save them to the database. So how should we implement it in the gridview? In the gridview, there are two implementation methods:

First, let's take a look at the first method. This method uses sqldatasource to update all records, but this method is slow, because every time a record is updated, a data connection is established and the updatecommand is executed, will affect the performance. The main code is as follows:

<SCRIPT runat = "server"> Void button#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" performanceid = "sqlperformance1" 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> selectcommand = "select [customerid], [companyName], [contactname], [contacttitle] from [Customers] " updatecommand =" Update [MERs] 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 the updateparameters parameter set, that is, specify the fields to be updated and their types. Then, the updatecommand Statement of sqldatasource is pointed out. In the Click Event of the update button button1, The for loop will be used in the form of traversal to check each row in the gridview, put the content of each updated text box in the updateparameters parameter of sqldatasouce, and call the update method of sqldatasource to complete the update.

Method 2 uses to traverse each row in the gridview and use an SQL statement to connect the updated content before using command. executenonquery () is updated with high efficiency. 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 button#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 [MERs] 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 (); } }

Note that, starting with vs.net 2005 beta 2, if you use the database connection string configuration in Web. config, you should write it as follows:

<Connectionstrings> <Add name = "northwindconnectionstring" connectionstring = "Data Source = Liao; initial catalog = northwind; user id = sa; Password = XXXX" providername = "system. Data. sqlclient"/> </Connectionstrings>

ThenProgramRead as follows:

Sqlconnection con = new sqlconnection (configurationmanager. connectionstrings ["appconnectionstring1"]. connectionstring );