Shulou(Shulou.com)06/02 Report--

This article is to share with you about the concepts that are easy to be confused in ADO.NET. The editor thinks it is very practical, so I share it with you to learn. I hope you can get something after reading this article.

1. DataTable

DataTable represents a table of data in memory, which exists entirely independently in memory and contains all the information about the table. The DataTable can be a table read from the database through a connection, and once the content is read into the DataTable, the DataTable can be disconnected from the data source and exist independently, or it can be a table created entirely by the program itself through code.

◆ DataColumn

A table is a two-dimensional structure consisting of rows and columns. The structure of a table is composed of a collection of DataColumn objects, which can be obtained from the DataTable.Columns property. The schema of the table is determined by defining the data type of each column, similar to the table defined in the database. After defining the structure of the table, you can generate DataRow based on the structure, and use the DataTable.NewRow () method to generate new rows of this DataTable structure.

A DataTable is made up of a collection of DataRow, which can be accessed by the DataTable.Rows property.

DataTable can also create some columns with expressions for the Expression attribute from existing columns.

1. Create the calculated column

For example: there is already a table structure, the table has a collection of DataColumn, in which there is a column called UnitPrice, you can create a new DataColumn, set ColumnName, and then set the expression for this column, DataColumn.Expression= "UnitPrice * 0.086". The value of this column is calculated by the column named UnitPrice. When creating the expression, use the ColumnName attribute to reference the column.

2. The second use is to create aggregation columns

Aggregate column aggregation is usually performed along the relationship (for a description of the relationship, see the DataRelation section below). If the order table is named a child table called detail, a relationship is established between the two tables through two columns: order.orderid and detail.orderid. The DataRelation object is called "order2detail", and an aggregate column can be established in the main table order. The sum of the prices of all item contained in each order in the detail table is calculated: DataColumn.Expression = "sum (child (order2detail) .price)", child (order2detail) represents the child table contacted through the relational order2detail, and child (order2detail) .price represents the price column of the child table.

◆ DataRow

The DataRow object does not have a constructor that is used directly in the code. It is common to create a new DataRow object using the NewRow () method from a DataTable with a certain structure. A DataRow has different states depending on whether it is independent or belongs to a DataTable, whether it has been modified, whether it has been deleted by DataTable, and so on. It is exposed by the DataRow.RowState attribute, as shown in the following table:

Member name description

The Added line has been added to the DataRowCollection and AcceptChanges has not been called. The Deleted line has been deleted through the Delete method of DataRow.

The Deleted line has been deleted through the Delete method of DataRow.

The Detached line has been created but does not belong to any DataRowCollection. DataRow is in this state immediately after it is created before it is added to the collection or after it is removed from the collection.

The Modified line has been modified and AcceptChanges has not been called.

The Unchanged line has not changed since the last call to AcceptChanges.

Just after a DataRow object is created, its state is Detached, which is an isolated existence, so after the establishment of the DataRow, the cells in the DataRow are filled with data, and after the DataTable.Rows.Add (DataRow) method is used to add the DataRow to the DataTable,DataRow to the DataTable, the state of the DataRow changes to Added. When the DataRow is modified, the DataRow state changes to Modified, and when the DataRow is deleted with the DataRow.Delete () method, the DataRow status changes to Deleted. However, this line still exists in the DataTable, but the status has changed. Use DataTable.Rows.Count to check the number of rows, which is the same as before deletion. The DataRow is removed from the DataTable only after the DataTable.Remove (DataRow) method is called, and the state is returned to the Detached orphaned state.

Once the DataTable.AcceptChanges () method is called, all rows will be processed differently according to different states, Added, Modified, and Unchanged will retain the current values, the rows of Deleted will be removed from the DataTable, and all rows will be set to Unchanged. When the DataTable is populated from the DataAdapter.Fill (DataSet,DataTable) method, the Fill () method automatically calls the AcceptChanges () method, setting the row state of the DataTable to Unchanged. Also, if the DataTable specified in the Fill method does not exist in the DataSet to be populated, a DataTable with the same structure as the data source table is generated and the data is populated.

◆ DataRelation

Represents the parent / child relationship between two DataTable objects. It can be compared to the relationship between tables in a database. A parent table is equivalent to a table whose relationship is listed as a primary key, and a child table is equivalent to a table whose relationship is listed as a foreign key. The DataRelation constructor is generally: DataRelation (String, DataColumn, DataColumn), string is the relationship name, * DataColumn is the parent table column of the relationship, and the second DataColumn is the child table column of the relationship. The DataType values of the two columns of the relationship must be the same.

Once the relationship is established, you must add the relationship to the ParentRelations or ChildRelations attribute of the DataTable, which contains all the relationships with the parent table and the child table of the table. Add the relationship to the ChildRelations collection if the table is the parent of the relationship, otherwise to the ParentRelations collection.

II. DataView

DataView represents a custom view of bindable data for DataTable for sorting, filtering, searching, editing, and navigation. DataView can be compared to the view of a database, but it is a little different. The view of a database can be built across tables, while DataView can only build a view on a particular DataTable. DataView is typically created through the DataTable.DefaultView attribute, and then a subset of the DataTable is established through the RowFilter attribute and the RowStateFilter attribute.

The RowFilter property is used to filter the expression for which rows to see in DataTable, which is the same as the expression that creates the computed column mentioned above. For example: "LastName = 'Smith'", which is to view only those rows of data whose column LastName has a value of' Smith'.

The RowStateFilter property is used to set the row status filter in DataView. When introducing DataRow, the above describes the status of DataRow. A DataRow may have five states, which can be used by RowStateFilter to filter the rowset to view. In fact, there are not only five states of DataRow, but also version problems with DataRow. For example, when the status of DataRow is Modified, that is, the line has been modified, there will be two versions of the DataRow, the Current version and the Original version (before modification). In fact, the RowStateFilter attribute is filtered by combining the status and version of DataRow (the RowStateFilter value is CurrentRows) as shown in the following table:

Member name / description

Added: a new line.

CurrentRows: includes current lines that have not been changed, new lines, and modified rows.

Deleted: deleted rows.

ModifiedCurrent: current version, modified version of the original data (see ModifiedOriginal).

ModifiedOriginal: the original version (although it was later modified and exists as ModifiedCurrent).

None: none.

OriginalRows: includes original rows that have not been changed and deleted.

Unchanged: the row that has not changed.

The count obtained by the DataView.Count attribute is the number of records in the DataView after RowFilter and RowStateFilter are applied.

DataView is based on DataTable, and the DataView.Table attribute can get the DataTable corresponding to this DataView. The line of DataView is called DataRowView, and the corresponding DataRow of this DataRowView can be obtained directly from DataRowView through the DataRowView.Row attribute.

III. DataGrid

The DataGrid here is the DataGrid in winform, which is usually bound with DataView to display the data in DataTable and modify the data in DataTable.

DotNet's DataGrid is powerful, but its use is different from previous habits, and it is sometimes troublesome, so many people feel a little confused about this DataGrid, and there is a feeling that they don't know how to start. In fact, a lot of problems will be easily solved by figuring out some concepts.

DataGrid binds the data source it wants to display through the DataSource and DataMember properties. Data sources are generally DataTable, DataView, DataSet, and so on, but when you bind these data sources to DataGrid, they are actually bound DataView. If the data source is DataTable, it is actually the DefaultView bound to this DataTable. If the data source is DataSet, you can set a string to the DataMember property that specifies the table to be bound to, and then bind the DefaultView of the DataTable specified by DataMember to DataGrid.

So DataGrid actually displays DataTable's filtered DataView.

How does ◆ DataGrid display DataView data

After the DataGrid is bound to a DataView, the collection of DataGridTableStyle objects in the DataGrid.TableStyles controls which columns of the DataView are displayed, how wide the columns are, what the text of the column header is, and so on. Make sure that the DataGrid.TableStyles does not contain any objects, and DataGrid will display all the columns in the order of the DataView columns. In general, TableStyles is set up to control the content and format of the display.

For example, DataGrid is bound to a DataTable called order, which contains OrderID, CustomerID, OrderDate, ShipName, ShipAddress and other fields. You can see that DataGrid will display all the columns in the order of the DataView column.

We only want to display the three fields OrderID, CustomerID, and OrderDate, and we want to display the list header of OrderID as "order number", CustomerID as "customer number", and OrderDate as "order date", which is controlled by TableStyles.

Create a new TableStyle and map this TableStyle.MappingName attribute to the name of the DataTable that the TableStyle wants to control:

DataGridTableStyle myTableStyle = new DataGridTableStyle ()

MyTableStyle.MappingName = "myDateTable"

Create three more DataGridColumnStyle to control the three columns that will be displayed:

DataGridColumnStyle myColumnStyle1 = new DataGridTextBoxColumn (); myColumnStyle1.MappingName = "OrderID"; myColumnStyle1.HeaderText = "order number"; DataGridColumnStyle myColumnStyle2 = new DataGridTextBoxColumn (); myColumnStyle2.MappingName = "CustomerID"; myColumnStyle2.HeaderText = "customer number"; DataGridColumnStyle myColumnStyle3 = new DataGridTextBoxColumn (); myColumnStyle3.MappingName = "OrderDate"; myColumnStyle3.HeaderText = "order date"; add the three DataGridColumnStyle to TableStyle: myTableStyle.GridColumnStyles.Add (myColumnStyle1); myTableStyle.GridColumnStyles.Add (myColumnStyle2); myTableStyle.GridColumnStyles.Add (myColumnStyle3)

* add TableStyle to DataGrid:

DataGrid1.TableStyles.Add (myTableStyle)

After adding TableStyle to DataGrid, bind the data source.

Editing and modification of ◆ DataGrid

DataGrid supports editing and modification of the DataTable displayed by DataGrid. As long as the ReadOnly attribute of DataGrid is False, you can directly modify the contents of the unit in DataGrid. After modification, the data will be directly reflected to the DataTable unit corresponding to this DataGrid.

If this DataTable is created through vs.net 's visual data designer, and generated SelectCommand, InsertCommand, UpdateCommand, DeleteCommand these four commands, using DataAdapter's Fill method to get, then things are simple, the modified DataTable you can directly use DataAdapter's UpDate method to write back to the database. Let's take a look at the InsertCommand command generated by vs.net 's visual data dataset:

This.sqlInsertCommand1.CommandText = @ "INSERT INTO Customers (CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone,Fax) VALUES (@ CustomerID, @ CompanyName, @ ContactName, @ ContactTitle,@Address, @ City, @ Region, @ PostalCode, @ Country, @ Phone, @ Fax); SELECT CustomerID, CompanyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country, Phone,Fax FROM Customers WHERE (CustomerID = @ CustomerID)"; this.sqlInsertCommand1.Connection = This.sqlInsertCommand1.Parameters.Add (new System.Data.SqlClient.SqlParameter ("@ CustomerID", System.Data.SqlDbType.NVarChar, 5, "CustomerID"); this.sqlInsertCommand1.Parameters.Add ("@ CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName"); this.sqlInsertCommand1.Parameters.Add ("@ ContactName", System.Data.SqlDbType.NVarChar, 30, "ContactName")) This.sqlInsertCommand1.Parameters.Add (new System.Data.SqlClient.SqlParameter ("@ ContactTitle", System.Data.SqlDbType.NVarChar, 30, "ContactTitle"); this.sqlInsertCommand1.Parameters.Add ("@ Address", System.Data.SqlDbType.NVarChar, 60, "Address"); this.sqlInsertCommand1.Parameters.Add ("@ City", System.Data.SqlDbType.NVarChar, 15, "City")) This.sqlInsertCommand1.Parameters.Add (new System.Data.SqlClient.SqlParameter ("@ Region", System.Data.SqlDbType.NVarChar, 15, "Region"); this.sqlInsertCommand1.Parameters.Add ("@ PostalCode", System.Data.SqlDbType.NVarChar, 10, "PostalCode"); this.sqlInsertCommand1.Parameters.Add ("@ Country", System.Data.SqlDbType.NVarChar, 15, "Country")) This.sqlInsertCommand1.Parameters.Add (new System.Data.SqlClient.SqlParameter ("@ Phone", System.Data.SqlDbType.NVarChar, 24, "Phone"); this.sqlInsertCommand1.Parameters.Add ("@ Fax", System.Data.SqlDbType.NVarChar, 24, "Fax"))

DataAdapter's SelectCommand is used to populate the DataTable with the DataAdapter.Fill () method, and the datasheet rowset selected by SelectCommand is populated into the DataTable, and then DataGrid displays it.

After DataGrid has been edited and modified, the five states described above may appear in the corresponding DataTable of DataGrid, which may be newly added (Added), modified (Modified) or deleted (Deleted). The DataAdapter.UpDate () method will insert the row with the status of Added into the database by calling the InsertCommand command, UpdateCommand will modify the row with the status of Modified in the database, and DeleteCommand will delete the row with the status of Deleted in the database.

If you do not create a new DataAdapter through vs.net 's visual data designer and do not automatically generate four commands: SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand, you may need to write InsertCommand, UpdateCommand, and DeleteCommand commands yourself. In one case, when SelectCommand returns at least one primary key column or unique column, you can automatically generate three other update commands based on the SelectCommand command through SqlCommandBuilder, for example:

SqlConnection myConn = new SqlConnection (myConnection); SqlDataAdapter myDataAdapter = new SqlDataAdapter (); myDataAdapter.SelectCommand = new SqlCommand (mySelectQuery, myConn); / / create the SelectCommand command SqlCommandBuilder custCB = new SqlCommandBuilder (myDataAdapter) for DataAdapter; / / create the CommandBuilder for this DataAdapter, / / so that the system will automatically generate InsertCommand, UpdateCommand, DeleteCommand commands for this DataAdapter.

Otherwise, if you want to update the database with the DataAdapter.UpDate () method, you have to write the three commands InsertCommand, UpdateCommand, and DeleteCommand yourself. You can refer to the InsertCommand command automatically generated by vs.net given above.

Synchronization of ◆ data binding

Many controls in WinForm can be bound to a data source, and binding can be done in two ways:

Simple data binding

Simple data binding refers to the ability to bind a control to a single data element, such as a value in a column of a dataset table. This is a typical binding type for controls, such as TextBox controls or Label controls, that is, controls that typically display only a single value. In fact, any property on the control can be bound to a field in the database.

Complex data binding

Complex data binding refers to the ability to bind a control to multiple data elements, usually to multiple records in a database, or to multiple bindable data elements of any other type, typically to a DataView. Examples of controls that support complex binding are DataGrid, ListBox, and ErrorProvider controls.

Generally speaking, DataGrid controls are bound to a DataView. Data binding of DataGrid is complex because it is bound to a table with multiple records. DataGrid has two properties related to data binding:

DataGrid.DataSource property: gets or sets the data source for the data displayed by the DataGrid. It is generally bound to DataTable, DataView, and DataSet. If DataSource is set to DataSet, then the reference contains more than one table, and you must set a string to the DataMember property that specifies the table to bind to.

DataGrid.DataMember property: gets or sets a specific list in DataSource, that is, when the above DataSource is set to DataSet, set this property to specify the table to bind to.

There is often this demand, there is a DataGrid in a form, showing some data, there are also some TextBox controls on the form, which are used to display the data of the current row in DataGrid, a TextBox control corresponds to a column of the DataGrid row, and when the current row of the DataGrid moves, the value in the TextBox control will also follow the display of the current row of the changed DataGrid.

To ensure that these data-bound controls remain synchronized, it is necessary to have a unified data-binding mechanism to ensure the synchronization of these controls. BindingManagerBase is responsible for data synchronization in DotNet, which is used to manage data sources. Data-bound controls bound to the same data source can be managed by BindingManagerBase. BindingManagerBase is available from the Form.BindingContext.Item property, which is overloaded in two ways:

Public BindingManagerBase this [object DataSource] / / get the BindingManagerBase public BindingManagerBase this [object DataSource, string DataMember] / / associated with the specified data source / / get a BindingManagerBase associated with the specified data source and data member

The data sources of all data-bound controls are the same as the objects passed when the BindingManagerBase is created, and will belong to this BindingManagerBase management. For example, create a BindingManagerBase like this:

BindingManagerBase myBindingManagerBaseParent = this.BindingContext [myDataSet, "customers"]

If there is a DataGrid DataGrid.DataSource = myDataSet;DataGrid.DataMember = "customers" on Form, then the data source for this DataGrid is under the management of myBindingManagerBaseParent.

Similarly, the DataSource of a simple data-bound control is the same as the DataSource of BindingManagerBase. When DataMember is a column of the table specified by BindingManagerBase's DataMember, the data source of this control is also managed by this myBindingManagerBaseParent:

DataGrid1.DataSource = myDataSet; dataGrid1.DataMember = "customers"; textCustomerId.DataBindings.Add (new Binding ("Text", myDataSet, "customers.customerid")); / / the Text property of TextBox is bound to the customerid field of the customers table of / / myDataSet

The data source controlled by BindingManagerBase has the concept of current row. Once the control is bound to the data source, DataGrid will display all the data of the data source table, but there is a black triangular arrow in the row header of DataGrid to indicate the current row. The values displayed in the simply bound control will be the contents of the current row of the data source.

So, as long as we change the pointer of BindingManagerBase, you can change the current line on the interface by clicking on the line you want to go to, or you can change the settings of the current line in the program:

MyBindingManagerBaseParent.Position = 10

A change in the BindingManagerBase.Position property causes a change in the current row of the BindingManagerBase, that is, a change in the current row of the DataGrid bound to the data source, and the display of the simply bound control changes.

The DataSource of a BindingManagerBase can be a DataSet,DataSet with multiple DataTable, and these DataTable can be linked together through a DataRelaton (relationship) to form a parent / child table relationship. For example, again in the example above, a DataGrid displays the Customer table, and you also want a DataGrid to display all the order of the current Customer. In this way, we will need two BindingManagerBase, one BindingManagerBase corresponds to the Customer table, and the other BindingManagerBase corresponds to the order table, and this order table also takes into account the relationship with the Customer table.

We have already established the BindingManagerBase for Customer. Let's create the BindingManagerBase for order:

First, we need to establish the relationship between the Customer table and the order table myRelation:

DataColumn ParentColumn = myDataSet.Tables ["customers"] .Columns ["customerid"]; / / the column of the parent table to establish the relationship is equivalent to the primary key DataColumn ChildColumn = myDataSet.Tables ["orders"] .Columns ["customerid"]; / / the column of the child table to establish the relationship is equivalent to the foreign key DataRelation myRelation = new DataRelation ("myRelation", ParentColumn,ChildColumn,false); / / establish the relationship based on the related columns of the parent table and child table

Then, through the relationship, establish the BindingManagerBase of the corresponding order table:

MyBindingManagerBaseChild = this.BindingContext [myDataSet, "customers.myRelation"]; / / this data source will be resolved to all order corresponding to the customer in a parent table.

In this way, when the current line of the BindingManagerBase corresponding to Customer changes, the BindingManagerBase of the corresponding order will also change, and the relationship between them is determined by myRelation.

◆ accesses the contents of DataGrid in the program

There is a row DataRow in DataTable, but there is no such object in DataGrid, which makes people feel unaccustomed and unnatural. In DataTable, the hierarchical structure of a table is very clear. The DataTable.Rows attribute can get the rowset of all the rows contained in the table, a specific DataRow can be obtained through the rowset index DataRowCollection [index], and the data row index DataRow [index] can get the contents of a specific column of this row.

It is not so convenient in DataGrid. DataGrid only has two properties available, the DataGrid.CurrentCell property, which returns a structure of type DataGridCell, and the DataGridCell structure indicates the line number and column number of the Cell. There is also a DataGrid.Item property, which has two overloads:

Public object this [DataGridCell] / / gets or sets the value of the specified DataGridCell

Public object this [int, int] / / gets or sets the value of the cell in the specified row and column

It can be seen that the access in DataGrid is for a certain Cell. Often, we need to get the DataRow corresponding to this Cell from the current Cell, for example, the interface may first select a certain line of DataGrid, or a certain Cell, and then click a button to pop up a new window, which displays the contents of all the cells in this line, and allows you to modify the value of the unit, and * * save and close the window. This requires finding the row and column of the corresponding DataGrid from the cell where the current DataTable is located.

The data displayed in DataGrid may be filtered by the DataView.RowFilter attribute and DataView.RowStateFilter attribute of DataView, or sorted by DataGrid itself according to the forward and reverse direction of each column, so there is a great chance that the row index indicated by the CurrentRowIndex attribute of DataGrid is different from its corresponding DataTable row index, and it is not possible to get the corresponding DataTable rows according to the CurrentRowIndex of DataGrid.

At this point, the BindingManagerBase will work again, and we can first create a BindingManagerBase corresponding to the data source bound by the DataGrid, so that the BindingManagerBase can manage the data source.

/ / set the data source of DataGrid dataGrid1.DataSource = myDataSet; dataGrid1.DataMember = "customers"; / / create BindingManagerBase BindingManagerBase myBindingManagerBaseParent = this.BindingContext [myDataSet, "customers"] with the same data source as DataGrid

Once the BindingManagerBase is established, you can get the records of the current data source through the properties of the current row of the BindingManagerBase:

/ / the Current of BindingManagerBase returns the object of the data source. For the data source bound to DataView, this object needs to be explicitly

Convert / / to DataRowView type

DataRowView myDataRowView = (DataRowView) myBindingManagerBaseParent.Current

In this way, we can get the DataRowView,DataRowView where the Cell is located from the current Cell and get the DataRow through the DataRowView.Row attribute and its convenience.

If you want to go further, if you want to get the specific unit of the DataTable corresponding to this Cell, you need to know not only the DataRow, but also the column corresponding to this Cell.

This is divided into two situations:

First, DataGrid does not use TableStyles to set the columns and formats to be displayed by DataGrid, and all columns of data source DataView will be displayed in the order of DataView itself, so that the corresponding column index can be directly obtained:

/ / gets the column index of the current DataGrid unit, which is the same as the DataTable index

Int ColumnNumber = DataGrid.CurrentCell.ColumnNumber

Another situation is that DataGrid uses TableStyles to set the columns and formats to be displayed by DataGrid, so that the column index of the DataGrid unit may not be the same as that of DataTable, which will use the TableStyles of DataGrid:

Int ColumnNumberDataGrid = DataGrid.CurrentCell.ColumnNumber; / / get the column index of the current DataGrid unit Int ColumnNumberDataTable = DataGrid.TableStyles [0] .GridColumnStyles [ColumnNumberDataGrid] .MappingName is the concept that is easy to be confused in GridColumnStyles.MappingName is what the editor believes that some knowledge points may be seen or used in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.