C # connection with SQL: operations performed by the GridView control on the database

Differences between GridView and DataGrid

The GridView is the successor control of the DataGrid. In. net framework 2, although the DataGrid still exists, the GridView has been on the foreground of history, and the trend of replacing the DataGrid is unstoppable. Similar to the DataGrid function, the GridView displays data in the data source on the web page. A row of data in the data source, that is, a record, is displayed as a row in the output table on the web page.

Compared with the DataGrid, The GridView has the following advantages and features, because it provides a smart tag Panel (that is, the show smart tag) for ease of use and convenience, common sorting, paging, update, and deletion operations can be implemented without code! With the PagerTemplate attribute, you can customize the user navigation page, that is, the page control is more free. The GridView and DataGrid are also different in the event model. The DataGrid Control triggers a single event, while the GridView control triggers two events, one before the operation and the other after the operation, multiple ** ing events before an operation and multiple ** ed events after an operation, such as Sorting events, sorted events, RowDeleting, and RowDeleted events.

GridView operation preliminary

1. display data in the data source

Drag the GridView control from ToolBox to the page, right-click, select Show Smart Tag, and select New Data Source from Choose Data Source. Data Source Configuration Wizard appears, and select the connection string, you can choose to store it on the web. in config, you can select whether to use the stored procedure or select data from the table or view.

In this step, the Where Statement on the Left can specify the query condition. Click Where and Add Where Clause appears. Select the column to set the condition, and select whether the operator is equal to or like or other, and then select Source, that is to say, the value of the condition to be specified can be Control, Session, Form, Cookie, QueryStirng, etc. If Control is selected, select the Control on the right, you can also set the default value. After the setting is complete, the system automatically generates an SQL expression and a value expression. Click the Add button to Add the condition. The newly added condition appears under Where Cluase. If you do not click Add, it is easy to set conditions, but it does not work because it is not added to the Where clause.

In this step, the Order By on the left allows us to set the sorting columns, that is, What sort is used for the records we retrieve. We can set three columns: ascending or descending.

In this section, Advanced on the Left can be set to Advanced SQL Generation Options. Here we can generate the Insert, update, and Delete statements for this query. Of course, the prerequisite is that the selected field must contain the primary key. If you want to edit or Delete a table without writing any code in the GridView, Insert, Update, and Delete statements must be generated here When configuring the data source. The zero code for editing and deleting operations in the GridView is to update and delete the data source based on the statements automatically generated when the data source is configured.

In the last step, you can test whether the query you just generated is correct, and click Finish. The data has already appeared on the page, and press Ctrl + F5 to run it.

Congratulations! You have used Asp.net to display data in the database.

2. Enable pagination for the GridView

The GridView displays the data, but it is not appropriate to list multiple records on one page. We should paging the data. I still remember how troublesome paging was in the asp era. I had to write a lot of code and various paging components came into being. In the GridView, you will find that pagination is so simple, you just need to tap the mouse, In the Show Smart Tag, select Enable Paging, the Paging operation of the table has been completed, is it So Easy.

Not all data sources can enable automatic Paging for the GridView. For example, if the DataSourceMode is DataReader, automatic paging cannot be implemented. In addition, only ObjectDataSource supports pagination at the interface level. Similar to the commonly used SqlDataSource, all records are extracted first, and only the records to be displayed on this page are displayed, and the remaining records are discarded, it's a waste of resources!

When the AllowPaging attribute of the GridView is set to True, we implement pagination. We can also customize paging settings. Common attributes include PageIndex, which is used to set the current page of data display. The default value is 0, which is the homepage of data. PageSize-that is, the number of records displayed on a page. The default value is 10. In PagerSettings, you can also set the paging navigation button in detail. In the Mode attribute, you can set Numeric-default, which is represented by numbers on pages, 1, 2, 3 ....... NextPrevious, nextpreviusfirstlast, and NumericFirstLast can be used as their names to display previous pages, next pages, home pages, and last pages. When Mode is not set to Numeric, you can set attributes such as FirstPageText and LastPageText to display text prompts on the homepage, last page, next page, and top page.

If you want to implement full automatic control of the page interface, you can also right-click the GridView, select Edit template-PagerTemplate, and add several Button controls to the template, set the CommandName attribute of the Button control to Page, and set the CommandArgument attribute to First, Last, Prev, Next, or a number to implement paging.

3. Editing, deleting, and sorting in the GridView

After the data is extracted from the data source and displayed on the webpage, you do not need to write any code to edit, update, or delete the data, use the built-in functions of the GridView.

In intelligent tag, click Edit column. In Avaliable Fields, select ComandField, and double-click Edit, update, cancel, and Delete. The Edit and Delete functions are added to the GridView. If the Insert, update, and delete statements have been generated when configuring the data source, we can execute the program now. Click Edit on the page, and the Update and Cancel buttons are displayed. Besides the primary key columns, the values of the current row are all placed in a text box. You can Edit them and click Update to save the changes. Click Delete to Delete the current row record. Have you been impressed by the powerful functions of the GridView?

In the Show Smart Tag, select Enable Sorting. At this time, the headers of all columns are changed to a hyperlink. In fact, these are a LinkButton control and run the Code. In the data table generated on the webpage, click the column name in the first row to sort by the current column. Click again to sort the column in reverse order.

If you only need to sort several columns, you can select Edit columns in Intelligent tag, select the columns to be sorted, and find the SortExpression attribute in the attribute on the right, then, select the field to be sorted from the drop-down list, which is usually the current field. If you do not need this column to be sorted, you only need to delete the value after the SortExpression attribute of this column, that is, set it to an empty string. Have you tried sorting?

Custom columns in the GridView

The GridView can automatically generate columns based on the data source. However, if we need to customize the column display mode, we can completely control the columns in the GridView, we need to use a special column-TemplateField. Because the columns generated by the GridView are all a field column, what if we need to combine the two fields into a column for display? We can use the template column. We can specify templates that contain tags and controls and customize the layout and behavior of columns. We can create a new template column, you can also directly convert generated columns into template columns for personalized settings.

Right-click the GridView, select Edit template, and select the column to be edited from the pop-up menu. The edit screen of the column template is displayed. HeaderTemplate-the content displayed in the header of the Custom column, and FooterTemplate-which of the following operations is displayed in the footer? /SPAN> ItemTemplate-displays the data of this column after the webpage is opened. EditItemTemplate-displays the data of this column when it is being edited. AlternatingItemTemplate-displays the alternate items, that is to say, in order to display the effect, you can display the results in different styles on different rows.

Example 1:

Assume that there is a table with a field username. Now we generate a Custom column that contains a photo of this person, at the same time, we assume that the photo path is image/username.jpg. Right-click the GridView, select the edit column in the Smart Tag, add a template column, edit the ItemTemplate in the template, add an Image control, and right-click the Image control, select Edit DataBindings and set Field Binding in ImageUrl. First, I want a column in the Bound to data source. Because the paths and formats of all images are the same, only the names are different, so here we select the username field. In the format, we need to define its own format. Input image/02.16.jpg, and {0} indicates the field bound above, there is a check box for Two Way DataBinding, which indicates whether to bind Two-Way data. If one-Way binding is used, Eval is generally used. That is to say, the value is only transmitted from the data source to the page. If Two-Way binding is performed, that is, if Bind is used, data modification can be uploaded back to the data source.

When executing a web page, different rows have different usernames, and the image names are replaced accordingly. Click "OK" and then execute the current webpage. the user's photo is displayed in our custom column.

Example 2:

In databases, when storing gender data, bit data is generally used, and the storage is True or False. When a column is automatically generated by the GridView, The CheckedBoxField column is generally used to display bit data, displayed on the webpage is a single token. if selected, it is Checked, it is male, otherwise it is female. This looks unintuitive. Next we will display gender as male and female through custom columns.

In the Show Smart Tag of the GridView, select Edit column, double-click TemplateFields, add a template column, click OK, right-click Edit template, and select the newly added column. Add a DropListDown control to ItemTemplate, Edit its data binding, Edit DataBinding, and bind the SelectedValue attribute to the Gender column.

In the DropListDown control, select Edit Item to Edit the items in the drop-down list. We add two items. One Text attribute is male and the Value is set to True, the Text attribute is set to female, and the Value attribute is set to False. So far, do you understand? Because the displayed text and value of the DropDownList control can be different, we use the data to bind the value of the Gender column, which is True or False and then reflected on the DropDownList control, if the Value is True, because the Text attribute is True for the male Item, we are running the webpage now, in the newly added column, it does not show anything that has no meaning, such as "single-digit", "True", or "false", but displays whether the current user is a male or female by pulling the list below.

Data Update in custom Columns

Assume that the database has a "permission" field. If the value is 0, it indicates that the user is not audited. If the value is 1, it indicates that the user is normal. If the value is 9, it indicates the administrator user. According to the preceding method of customizing columns, by binding DropListDown, the permission is displayed as "Administrator" on the webpage instead of the number 9. The problem arises. If we adjust the user permissions, for example, change the general user to the Administrator. In the drop-down list of the user permissions in the editing template, how to return its value to the data source to complete the update operation.

The DropListDown control we set in EditItemTemplate must select Two Way DataBinding, that is, Two-Way help of data, in order to return data. As mentioned above, in the GridView, events are not single, but two. One is before and the other is after, because we need to transfer the permission value of the drop-down list before data update, We need to encode GridView1_RowUpdating, the encoding is as follows:

Copy codeThe Code is as follows: protected void GridView1_RowUpdating (object sender, GridViewUpdateEventArgs e)
{
// Which line is being edited?
Int index = GridView1.EditIndex; // gets the GridViewRow object of the currently edited row.
GridViewRow gvr = GridView1.Rows [index]; // search for the DropListDown control in the current row
DropDownList dp = (DropDownList) gvr. FindControl ("editdrop"); // assign the DropListDown value to the permission field in the NewValues set.
E. NewValues ["rights"] = dp. SelectedValue;
}

RowDataBound event

When creating a gridView control, you must first create a GridViewRow object for each row of the GridView. When creating each row, A RowCreated event is triggered. When the row is created, each row of GridViewRow is bound to the data in the data source. When the binding is complete, the RowDataBound event is triggered. If we can use the RowCreated event to control the controls bound to each row, we can also use the RowDataBound event to control the data bound to each row, that is, how to present the data to everyone.

For example, there is a gender column in the data table. The DataBounding of the DropListDown control is used to indicate the gender in Chinese, but it is not very beautiful after all, now we can use the Label control and RowDataBound event to achieve a perfect Chinese gender display. RowDataBound,

First, set the Gender column as the template column, add a Label control, and bind the Label control to the gender segment of the data source, double-click RowDataBound In the event list of the GridView control property to generate the following event:

Example:

Copy codeThe Code is as follows: protected void GridView1_RowDataBound (object sender, GridViewRowEventArgs e)
{
// Determine whether the current row is a data row
If (e. Row. RowType = DataControlRowType. DataRow)
{
// Use FindControl to find the Label control in the template
Label lb1 = (Label) e. Row. FindControl ("Label1 ");
// Because RowDataBound occurs after data binding, we can
// Determine the data bound to the Label. If it is True, change its text attribute to male.
If (lb1.Text = "True ")
Lb1.Text = "male ";
Else
Lb1.Text = "female ";
}
}

RowType

RowType can be used to determine the type of the row in the GridView. RowType is a value in DataControlRowType. RowType can be set to DataRow, Footer, Header, EmptyDataRow, Pager, and Separator. Most of the time, we need to determine whether the current row is a data row and use the following code to determine whether the current row is a data row:

If (e. Row. RowType = DataControlRowType. DataRow) RowDeleting and RowDeleted events

RowDeleting occurs before data is deleted, and RowDeleted occurs after data is deleted.

With the RowDeleting event, you can confirm whether to delete it again before deleting it. You can set GridViewDeleteEventArgs. cancel = True to Cancel the deletion. It can also be used to determine the number of records in the current database. If there is only one record left and the database cannot be blank, a prompt is displayed and the deletion operation is canceled.

When you use the RowDeleted event, you can use the Exception attribute of GridViewDeletedEventArgs to determine whether an Exception occurs during the deletion process. If no Exception exists, a message similar to "1 Records deleted" can be displayed.

Example:

Copy codeThe Code is as follows: protected void GridView1_RowDeleting (object sender, GridViewDeleteEventArgs e)
{
// Obtain the current row number and obtain the GridViewRow object of the current row
Int index = e. RowIndex;
GridViewRow gvr = GridView1.Rows [index]; // retrieves the text in the second cell of the current row.
Str1 = gvr. Cells [1]. Text; // prompt
Message. Text = "you will delete a user whose name is" + str1;
}
Protected void gridviewinclurowdeleted (object sender, GridViewDeletedEventArgs e)
{
// If no exception occurs, a message is displayed, indicating that the deletion is successful. Otherwise, a message is displayed, indicating that the deletion failed.
If (e. Exception = null)
Message. Text + = "<br> you have successfully deleted" + str1;
Else
Message. Text + = "deletion failed. Please contact the Administrator ";
}

RowEditing event

The RowEditing event is triggered before the row in the GridView enters the editing mode. If you need to perform some preprocessing before editing the record, you can perform operations here. To Cancel editing the current row, set the Cancel attribute of the GridViewEditEventArgs object to true.

Example:

Copy codeThe Code is as follows: protected void GridView1_RowEditing (object sender, GridViewEditEventArgs e)
{
// Use NewEidIndex to get the row number currently edited, and then obtain the gridviewrow object
GridViewRow gvr = GridView1.Rows [e. NewEditIndex];
// Judge. If the name column of the current editing row is admin, the editing of the current row is canceled.
If (gvr. Cells [1]. Text = "admin ")
E. Cancel = true;
}

RowUpdating and RowUpdated events

The RowUpdating event occurs before the data source is updated, and the RowUpdated event occurs after the data source is updated.

We can use RowUpdating for preprocessing before record updates. For example, when changing a password, the password is hashed because it is not stored in plain text in the database. Therefore, before updating the password, the hash value should be generated before the update operation. RowUpdated checks whether the update is successful.

Example:

Copy codeThe Code is as follows: protected void GridView1_RowUpdating (object sender, GridViewUpdateEventArgs e)
{
GridViewRow gvr = GridView1.Rows [GridView1. EditIndex]; // search for the control that inputs the password
TextBox tb1 = (TextBox) gvr. FindControl ("tb_password"); // after the text in this control is hashed, the password is saved to the NewValues dictionary.
E. NewValues ["password"] = tb1.Text. GetHashCode (). ToString ();
}
Protected void gridviewinclurowupdated (object sender, GridViewUpdatedEventArgs e)
{
// If no exception occurs, the update is successful.
If (e. Exception = null)
Message. Text + = "updated successfully! ";
}

Keys, OldValues, and NewValues

The Keys dictionary generally stores the corresponding values of the key and value of the primary key field in the data source. If the primary key is composed of multiple fields, Keys adds the field names and values for each key field. OldValues stores the field name and original value of the row to be updated. Each field is one of them. NewValues stores the field names and modified values of the rows to be updated. Each field is one of them. Note that primary key fields are only stored in the keys set.

Each item in these three sets is a DictionaryEntry type object. We can use DictionaryEntry. Key to determine the field name of an item, and use DictionaryEntry. Value to determine the Value of an item.

In the preceding example, we used the NewValues field to reset the value of key to password in order to encrypt the plaintext of the password and store it in the database. To ensure security, we encode all values in NewValues in html before updating the data:

Example1:

Copy codeThe Code is as follows: protected void GridView1_RowUpdating (object sender, GridViewUpdateEventArgs e)
{
// Traverse NewValues and obtain each pair of DictionaryEntry objects

Foreach (DictionaryEntry de in e. NewValues)

// De. key is the field name. If you update a field separately, you can enter the field name directly,
// E. NewValues ["password"]

E. NewValues [de. Key] = Server. HtmlEncode (de. Value. ToString ());
}

Example2:

Copy codeThe Code is as follows: protected void GridView1_RowUpdating (object sender, GridViewUpdateEventArgs e)
{

// Use Keys, OldValues, and NewValues to obtain the primary key name, original data, and updated data respectively.

Message. text = e. keys ["username"] + "email address from" + e. oldValues ["email"] + "changed to" + e. newValues ["email"];

}

The operation of the GridView control is described here.