C to SQL connection: operation of the GridView control on the database

The similarities and differences between GridView and DataGrid

GridView is the successor control of DataGrid. In.net framework 2, although DataGrid still exists, GridView has come to the front of history, and the trend of replacing DataGrid is unstoppable. GridView and DataGrid have similar functions. They both display the data in the data source on the web page. One row of data in the data source, namely one record, is displayed as one row in the output table on the web page.

Compared with DataGrid, GridView has the following advantages and more functions, because it provides a smart tag panel (show smart tag), which is easier to use and convenient. Common sorting, paging, update, delete and other operations can be realized by zero code! With the PagerTemplate property, you can customize the user navigation page, which means that the control of paging is more arbitrary. There are many differences between GridView and DataGrid in the event model. The DataGrid control raises a single event, while the GridView control raises two events, one before and one after the operation. The pre-operation event has multiple ***ing events, and the post-operation event has multiple ***ed events, such as Sorting event and sorted event, RowDeleting event and RowDeleted event.

GridView operation preliminary

1. Display the data in the data source

Select the GridView control from ToolBox and drag it onto the page, then right-click, select Show Smart Tag, select New Data Source from Choose Data Source, Data Source Configuration Wizard, select the connection string, select ConnectionString already stored in web.config, and then choose whether to use the stored procedure or select data from the table or view.

In this step 1, the left side of the Where statements can specify the query conditions, click Where, appear Add Where Clause, choose the column to set conditions, the operator is equal to or like or other, and then select Source, that is to limit the conditions where the values, can be Control, Session, Form, Cookie, QueryStirng etc, if choose Control, so need to be on the right side, are selected the controls, and then you can also set the default value, After setting, the system will automatically generate expressions of Sql expressions and values. At this time, click Add button to complete the addition of conditions. Under Where Cluase, the conditions just added will appear. If Add is not clicked, it is easy to set the condition, but since it is not added to the Where clause, it does not work.

At this step, Order By on the left, allows us to set the order, the order in which we're going to take the records, and we can set the three columns, ascending or descending.

In part 1, Advanced on the left, you can set Advanced Sql Generation Options, where you can generate the Insert, update, Delete statements for this query, provided, of course, that the field you select contains the primary key. When you want to edit, delete, and so on tables without writing any code in GridView, you must generate Insert, Update, Delete statements here when configuring the data source. In GridView, the zero code for editing and deleting the data source is used to update and delete the data source according to the statements automatically generated when configuring the data source.

In the last step, you can test 1 to see if the query you just generated is correct. Finally, click finish and the data will appear on the page. Press Ctrl+F5 to run.

Congratulations to you! You are already using Asp.net to display the data in the database.

2. Make GridView pageable

GridView displays the data, but it is not appropriate to list so many records on one page. We should page through the data. Remember back in the days of asp, paging was a lot of trouble, a lot of code had to be written, and various paging components were created. In GridView, you will find that paging is so simple that you only need to click the mouse to select Enable Paging in Show Smart Tag, and the paging operation of the table is completed, is it So Easy?

Not every data source can make GridView automatically paginate. For example, if DataSourceMode is DataReader, it cannot be automatically paginated. Moreover, only ObjectDataSource supports pagination at the interface level. Similar to the common SqlDataSource, all the records are extracted first, then only the records that need to be displayed on this page are displayed, and then the rest of the records are discarded, which is a bit of a waste of resources!

Paging is implemented when GridView's AllowPaging property is set to True, and we can personalize the paging as well. Common properties include: PageIndex, which sets the current page of the data display to 0 by default, which is the first page of the data. The PageSize display is the number of records per page. The default is 10. In PagerSettings, the navigation buttons for paging can also be set in detail. In the Mode property, Numeric can be set to: Numeric default, paging is represented by Numbers, 1,2,3... . NextPrevious, NextPreviousFirstLast, NumericFirstLast can all be as the name implies, showing the previous page, the next page, the first page, the last page, etc. When the setting of Mode is not Numeric, you can set the properties of FirstPageText, LastPageText and so on to realize pagination navigation. When you go to the first page, the last page, the next page and the previous page, the text prompt will be displayed.

If you want to achieve full automatic control of paging interface, you can also right-click GridView, select edit template -PagerTemplate to achieve, add several Button controls in the template, and then set CommandName property of Button control to Page, CommandArgument property to First, Last, Prev, Next or 1 number, to achieve paging operation.

3. Edit, delete and sort in GridView

After the data is extracted from the data source and displayed on the web page, if we need to edit, update, delete and other operations on the data, we still do not need to write any code, which can be realized by using the built-in functions of GridView.

In the smart tag, click edit column, in Avaliable Fields, select ComandField, and then double-click Edit,update,cancel and Delete. We add edit and delete to GridView. If we had generated Insert, update, delete statements when configuring the data source, we could now execute the program. Click Edit on the page, and the buttons Update and Cancel appear. Besides the primary key, the values of the current row are all placed in a text box, which can be edited. Then click Update to save. Click Delete to delete the current row record. Are you impressed by the power of GridView?

In Show Smart Tag, select Enable Sorting, then all the columns of Header become a hyperlink, in fact, these are an LinkButton control, run the code, in the data table generated by the web page, click the column name in line 1, you can sort according to the current column, click again to sort in reverse.

If you only need to sort a few columns, in the smart tag, select edit column, select the column to be sorted, then find the SortExpression property in the properties on the right, and select which field to sort by from the drop-down box. If you do not need this column to participate in the sorting, simply remove the value after the column's SortExpression property, which is set to an empty string. Try 1. Is the sorting all under control?

Custom columns in GridView

GridView can automatically generate columns based on the data source, but if we need to customize the way columns are displayed so that GridView columns are completely under our own control, we need to use a special column called TemplateField. Since the columns generated by GridView are 1 field 1 column, what if we need to combine the two fields into 1 column? We can use template columns. We can specify templates that contain tags and controls, customize the layout and behavior of the columns, create a new template column, or directly convert the generated columns to template columns to personalize the Settings.

Right-click on GridView, select edit template, select the column to edit from the pop-up menu, and the edit screen of the column template will appear. Where the header part of the HeaderTemplate custom column displays the content, and the footer part of the FooterTemplate custom column displays the content. / SPAN > ItemTemplate displays the contents of this column of data when the page is opened, EditItemTemplate displays the contents of this column when the column is edited, and AlternatingItemTemplate displays the contents of alternate items. This means that for the purpose of rendering, separate rows can be displayed in a different style.

Example 1:

We now assume that we have a table with a field of username, we now generate a custom column that contains the photo of that person, and we assume that the path of the photo is image/ username.jpg. We first right click GridView, in the smart tag, choose to edit, add a template column, then edit ItemTemplate in the template, add 1 Image controls, then right-click the Image controls, select Edit DataBindings, set in the ImageUrl Field Binding, first of all, I I want Bound to certain columns of data source, because all the path and the format is the same of the picture, only the name is different, So we selected username fields, in format, we want to define the format of the input image / {0} jpg, {0} is binding on the field, there are 1 Two Way DataBinding checkbox, whether is the meaning of the bidirectional binding, if one-way binding, using Eval 1, that is only from the data source to the page, if the bidirectional binding, namely adopt Bind, to modify data can be returned to the data source.

When the web page is executed, the name of the image will be replaced for different lines because username is different. By clicking ok, and then executing the current page, we can see that the user's photo is displayed in our custom column.

Example 2:

In the database, when storing gender, 1 usually USES bit data type, which is stored as True or False. When GridView automatically generates columns, 1 usually USES CheckedBoxField column to display bit data, which is displayed as a single box on the web page. If selected, it is Checked, which is male, otherwise it is female. It doesn't look intuitive, but let's show the gender as male and female in a custom column.

First, select edit column in GridView's Show Smart Tag, then double-click TemplateFields to add a template column. Once confirmed, right-click to select edit template and select the newly added column. Add an DropListDown control to ItemTemplate, and then edit its data binding, Edit DataBinding, to bind the SelectedValue property to the gender column.

In the DropListDown control, select Edit Item, which is the item in the drop-down list. We add two Item, one with Text as male, one with Value as True, one with Text as female, and one with Value as False. So far, do you understand? Because DropDownList controls the display of the text and its value is not 1 sample, we use data binding take gender this 1 column values, or True False, and then to reflect on DropDownList controls, if the value is True, because for man Item Value True Text attribute, so now we run web pages, in the newly added columns, that is no longer a single box or True, false these without meaning, Instead, the drop-down shows whether the current user is male or female.

Custom data updates in columns

Suppose there is a "permission" field in the database with a value of 0 for unaudited users, 1 for 1-like users, and 9 for administrator users. According to the custom column method mentioned above, through the binding of DropListDown, the permission is displayed as "administrator" in the web page, instead of the number 9. The problem arises, if we adjust the user permissions, such as changing the user 1 to administrator, how to return its value to the data source in the drop-down list of user permissions in the editing template to complete the update operation.

The DropListDown control we set in EditItemTemplate must have Two Way DataBinding selected, which is the bidirectional data binding, in order to return data. As we mentioned earlier, in GridView, events are not single, but two, one before and one after. Since we need to send the permission value of the drop-down list before data update, we need to encode GridView1_RowUpdating as follows:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)  
{   
// What line is currently being edited?            
int index = GridView1.EditIndex;     // Gets the current edit line GridViewRow object            
GridViewRow gvr = GridView1.Rows[index];     // In the current line, look for DropListDown controls           
DropDownList dp = (DropDownList)gvr.FindControl("editdrop");     // will DropListDown The value is assigned to NewValues Permission fields in the collection.           
e.NewValues["rights"] = dp.SelectedValue;  
} 

RowDataBound event

When creating an gridView control, you must first create an GridViewRow object for each row of GridView. Each row of GridView will raise an RowCreated event. When the rows are created, each row of GridViewRow is bound to the data in the data source, and when the binding is completed, an RowDataBound event is raised. If we can use the RowCreated event to control the control that is bound to each row, we can also use the RowDataBound event to control the data that is bound to each row, that is, how the data is presented to us.

For the same example, in the data table, there is a gender column. Above, we use DropListDown control DataBounding to represent the Chinese gender, but after all, it is not very beautiful. Now we can use Label control and RowDataBound event to achieve a perfect Chinese gender display. RowDataBound,

First, set the gender column as the template column, and add an Label control to bind the Label control to the gender segment of the data source. Then we double-click RowDataBound in the event list of the GridView control properties to generate the following event:

Example:

protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)   
{   
// Determines whether the current row is a data row           
if (e.Row.RowType == DataControlRowType.DataRow)           
{  
// with FindControl Method to find the template Label controls    
Label lb1= (Label)e.Row.FindControl("Label1");  
// because RowDataBound It happens after data binding, so we can   
// judge Label Bind the data, if yes True , just change it text Properties for male                   
if (lb1.Text== "True")                         
lb1.Text = " male ";                  
else                    
lb1.Text = "female";      
}      
}  

RowType

RowType can determine the type of line in GridView, and RowType is a value in the mei variable DataControlRowType. The values of RowType can include DataRow, Footer, Header, EmptyDataRow, Pager, Separator. In many cases, we need to determine whether the current data is a row or not by following the following code:

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

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

With the RowDeleting event, you can confirm again whether to delete before the actual deletion. You can cancel the deletion by setting GridViewDeleteEventArgs.Cancel =True; It can also be used to determine the number of current database records. If there is only one record left and the database cannot be empty, the deletion operation will be prompted and cancelled.

The RowDeleted event can be used to determine whether there is an exception during the deletion process through the Exception attribute of GridViewDeletedEventArgs. If there is no exception, a prompt message like "1 Records deleted" can be displayed.

Example:

 protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)   
 {   
 // Gets the current line number and gets the current line GridViewRow object           
 int index=e.RowIndex ;          
 GridViewRow gvr=GridView1.Rows[index];   // Gets the current line number 2 The text in a cell    
 str1 = gvr.Cells[1].Text;   // For prompt        
 Message.Text  =" You will delete 1 User whose name is "+str1 ;      
 }      
 protected void GridView1_RowDeleted(object sender, GridViewDeletedEventArgs e)  
 {   
 // If no exception occurs, the deletion is prompted for success, otherwise the deletion is prompted for failure            
 if (e.Exception == null)              
 Message.Text += "< br> You successfully deleted it "+str1 ;      
 else         
 Message.Text += " Deletion failed, please contact the administrator "; 
 } 

RowEditing event

The RowEditing event is raised before the rows in GridView go into edit mode, where you can do some preprocessing before editing the record. If you want to unedit the current row, you can set the Cancel property of the GridViewEditEventArgs object to true.

Example:

 protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e) 
 {   
 // with NewEidIndex Gets the line number of the current edit, and then gets gridviewrow object           
 GridViewRow gvr = GridView1.Rows[e.NewEditIndex];     
 // Judge if the current edit line name column is admin User, unedit the current row          
 if (gvr.Cells[1].Text =="admin")        
 e.Cancel = true;  
 }   

RowUpdating and RowUpdated events

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

We can use RowUpdating to do some preprocessing before record update. For example, when changing the password, hash is performed because the password is not stored in clear text in the database. Therefore, before updating the password, hash value should be generated and then updated. RowUpdated can verify that the update was successful.

Example:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e) 
{         
GridViewRow gvr = GridView1.Rows[GridView1 .EditIndex  ];   // Looks for a control to enter a password      
TextBox tb1 = (TextBox)gvr.FindControl("tb_password");   // Place the text in this control hash Later, the password deposit NewValues In this dictionary          
e.NewValues ["password"] =tb1.Text .GetHashCode().ToString () ;       
}       
protected void GridView1_RowUpdated(object sender, GridViewUpdatedEventArgs e)  
{  
// If there are no exceptions, the update succeeds    
if (e.Exception == null)  
Message.Text += " Update successful! "; 
}   

Keys, OldValues, NewValues

In the Keys dictionary, 1 normally holds the corresponding values of key and value for the primary key fields in the data source. If the primary key consists of multiple fields, Keys adds the field name and value for each key field. The field name and the original value of the row to be updated are stored in OldValues, and each field is one of them. NewValues stores the field name and modified value of the row to be updated, with each field being one of the items. Note that the primary key field is only stored in the keys collection.

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

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

Example1:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)  
{ 
// traverse NewValues And get each of them 1 right DictionaryEntry object   

foreach (DictionaryEntry de in e.NewValues)   

//de.key This is the field name. If you update a field separately here, you can also fill in the field name directly. 
// Such as  e.NewValues[ " password " ]       

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

Example2:

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)
{  
// Respectively using Keys , OldValues , NewValues Gets the primary key name, the original data, and the updated data   
Message .Text  = e.Keys["username"] + " the email Address from " + e.OldValues["email"] + " Changed to: " + e.NewValues["email"];  
}   

This is the end of the GridView control operation knowledge.