Keywords DataTable, DataView, DataGrid, Data Components, Data Access
First, DataTable
DataTable indicates a table in memory, which is completely existed in memory, including all information of this table. DataTable can be from a table that is formed from the database through the connection, once this DataTable can be opened independently with the data source, it can also be created by the programself by the program. A table.
◆ Datacolumn
A table is a two-dimensional structure consisting of rows and columns. The structure of the table is composed of a collection of DataColumn objects. The DataColumn object collection can be obtained from the DataTable.Columns property that determines the schema in the table by defining the data type of each column. Defining the structure of the table can generate DATAROW according to the structure, use the DataTable.newrow () method to generate a new line of this DataTable structure.
A DATATABLE is composed of DataRow's collection, and the collection of DataRow can be accessed by the DataTable.Rows property.
DataTable can also create some columns through expressions with existing Expression properties.
1. Create a calculated column
For example, there is already a table structure. There is a collection of Datacolumn in the table. There is a column called Unitprice. You can create a new Datacolumn, set the columnName, set this column, Datacolumn.Expression = "Unitprice * 0.086, the value of this column is calculated by the column named Unitprice. When creating an expression, use the columnName property to reference columns.
2, the second use is to create a polymerization column
Polymeric column aggregation is typically performed along the relationship (see the DataRelation section below related to the relationship), if the ORDER table has a sub-table named Detail, two tables build a relationship DataReLATION object with two columns. Name "Order 2detail", you can create a gale column in the primary table ORDER, and calculate the price of all Items included in the Detail table: Datacolumn.Expression = "SUM (Child (Order2detail) .price ", Child (ORDER2DETAIL) means Child (ORDER2DETAIL). Price represents the Price column of the subthech through the subthelet that is contacted by the relationship order2detail.
◆ DATAROW
The DATAROW object does not use the constructor used in the code, which is generally created from a new DataRow object with a new () method from a DATATABLE with a certain structure. A DATAROW is different from whether it is independent or a DATATABLE, whether it is modified, whether it is different from DataTable deletion, etc., is disclosed by the DATAROW.ROWSTATE attribute, as shown in the following table:
Member name
Added The row has been added to DataRowCollection, and AcceptChanges has not been called. DELETED This line has been deleted through the DELETE method of DATAROW.
DELETED This line has been deleted through the DELETE method of DATAROW.
Detached This line has been created, but does not belong to any DataRowCollection. DataRow is immediately in this case: Before being created, it is added to the collection; or remove it from the collection. Modified has been modified, and AcceptChanges has not been called.
Unchanged This line has not changed since the last call AcceptChanges.
A DATAROW object is just created, its status is detached, is an isolated one existence, so after the DataRow is created, the data is filled with the data in DataRow and add this DataRow to DataTable. After DATAROW is added to DataTable, the status of this DATAROW is converted to Added. When this DATAROW is modified, this DATAROW status is converted to modified. When DataRow.delete () method is deleted, the DATAROW status will be turned to deleted, but this line still exists in the DataTable, but the state changes, at this time Use DataTable.Rows.count to view the number of rows, before the deletion is the same. This DATAROW is only removed from the DataTable and the status is also removed from the DataTable.Remove (DataRow) method.
Once the DataTable.AcceptChanges () method is called, all rows will do different processing, add, modified, unchange according to different states, and the DELETED's line will be removed from the DataTable, and the status of all rows are set. To unchanged. When DataTable is formed from a DataAdapter.Fill (DataSet, DataTable) method, the Fill () method will automatically call the AcceptChanges () method, set the DataTable's row status to unchanged. And, if the DataTable specified in the Fill method does not exist when the Dataset to be filled, a DataTable with the same structure as the data source table is generated and populated.
◆ DataRelation
Represents the parent / sub-relation between two DataTable objects. You can class more than the relationship between the tables in the database, the parent table is equivalent to the table that is the primary key, and the sub-table is equivalent to the table of the foreign key. The Dataralation constructor is generally: Dataralation (String, Datacolumn, Datacolumn), String is a relationship name, the first Datacolumn is the parent table column of the establishment of a relationship, the second Datacolumn is the two columns of the establishment of a relationship, two columns The DATATYPE value must be the same.
It has been established, and this relationship must be added to the DataTable ParentRelations property or the childrelations property, which contains all the relationships of this table and the relationship of the follower table. If this table is a parent table, this relationship is added to the Childrelations collection, otherwise join the ParentRelations collection.
Second, DATAVIEW
DataView represents a custom view of DataTable for sorting, filtering, searching, editing, and navigation. DataView can be used with the database of the database, but the database's view can create views across tables, and DataView can only create views for a DataTable. DataView generally creates a subset of this DataTable by using the DataTable.defaultView property. The RowFilter property is used to filter the expressions to see which rows in DataTable, which is the same as the expression of the columns above. For example: "LastName = 'Smith'", this is the data line that only views the value of the column LastName is 'Smith'.
The ROWSTATEFILTER property is used to set the row status filter in the DataView. When you introduce DataRow, you will have five states, a DATAROW may have five states, and RowStateFilter is to filter the rows you want to view through these states. In fact, DATAROW is not only five states, but also has version problems, such as the status of DATAROW is modified, that is, this line has been modified. At this time, this DataRow will have two versions, Current version and Original version (before modifying of). In fact, the ROWSTATEFILTER attribute is to filter status and version of the DataRow (RowStateFilter's value is currentrows) See below:
Member name
ADDED a new line.
Currentrows includes current rows that have not been changed, new row, and modified rows.
DELETED has been deleted.
ModifiedCurrent Current Version, Original Data (see Modifiedoriginal) modified version.
Modifiedoriginal Original version (although it has been modified and exists in modifiedcurrent).
None none.
OriginalRows includes raw rows that have not been changed and deleted.
Unchanged has not changed.
The count obtained by the DataView.count property is to get the number of records recorded in the DataView after applying RowFilter and RowStateFilter.
DataView is based on DataTable, and the dataview.table property can get the DataTable corresponding to this DataView. DataView's row is called DataRowView, you can get this DataRowView corresponding to DataRowView from DataRowView.Row property.
Third, DataGrid
The DataGrid here is the DataGrid in WinForm, which is generally binded to DataView to display data in the DataTable, and modify the data in the DataTable.
DotNet's DataGrid is powerful, but in use, it is not too kind. Sometimes it is more troublesome, so many people feel that some people feel some of them can't touch the mind, there is a feeling of unlikely, it is actually clear some concepts. Many problems will be solved.
DataGrid Binds the data source to be displayed through the DataSource and DataMember properties. Data sources are generally DataTable, DataView, DataSet, etc., but the data source is bound to DataGrid is actually binding DataView. If the data source is DATATABLE, it is actually a defaultView that is bound to this DataTable. If the data source is DataSet, you can set a string to the DataMber property, which specifies the table to be bound, and then DataMember The specified DATATABLE DEFAULTVIEW is bound to the DataGrid. So DataGrid actually displays DataTable's screening DataView.
◆ What kind of way DATAGRID displays data of DataView
After DataGrid binds to a DataView, the collection of DataGridTableEle objects in DataGrid.TableStyles controls which columns of this DataView want to display, the width of the column, what is the text of the column header, etc. Individual data does not contain any objects in DataGrid.TableStyles, and the DataGrid will appear all columns in the order of the DataView column. TableStyles will be set in general applications to control the contents and formats displayed.
For example, DataGrid is bound to a DataTable called Order. This DataTable contains the fields of ORDERID, Customerid, ORDERDATE, SHIPNAME, SHIPADDRESS. If you do not need toStyles to control the displayed columns and formats, you will get the following display results:
Figure-1
You can see that DataGrid will display all columns in the order of the DataView column.
We only want to display the three fields of ORDERID, CUSTOMERID, ORDERDATE, and want to display the list of ORDERIDs as "Order Number", and CustomerID is displayed as "Customer Number", and ORDERDATE is displayed as "order date", which is to use tablestyles. Controlled.
Create a new tableStyle, the name of this tableStyle.mappingName property corresponding to the DataTable of this tableStyle to control:
DataGridTableStyle MyTableStyle = New DataGridTableStyle ();
MyTableStyle.mappingName = "MyDateTable";
Built again, three DataGridColumnStyle is used to control three columns to 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 these three DataGridColumnStyle to TableStyle:
MyTableStyle.GridColumnStyles.Add (mycolumnstyle1);
MyTableStyle.GridColumnStyles.Add (mycolumnstyle2);
MyTableStyle.GridColumnStyles.Add (mycolumnstyle3);
Finally, add tableStyle to DataGrid:
DataGrid1.tables.add (MyTables);
After adding tableStyle to DataGrid, then bind the data source, then we will see such a data display:
Figure-2
◆ DataGrid editing modification
DataGrid supports the editing modification of the DataTable displayed by DataGrid, as long as the DataGrid's readOONLY property is false, you can directly modify the contents in the DataGrid. After the modification, the data will directly react directly to the DataTable unit corresponding to this DataGrid.
If this DataTable is newly built DataAdapter through VS.NET, and generates SELECTCOMMAND, INSERTCOMMAND, UPDATECOMMAND, DeleteCommand, with DataAdapter's Fill method, then things is simple, modified DataTable You can write back to the database with DataAdapter's Update method. Let's take a look at the INSERTCOMMAND command generated by VS.NET's visual data.
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.sqlConnection2;
This.SqlinsertCommand1.Parameters.add (new system.data.sqlclient.sqlparameter ("@ Customerid", System.Data.sqldbtype.nvarchar, 5, "Customerid");
this.sqlInsertCommand1.Parameters.Add (new System.Data.SqlClient.SqlParameter ( "@ CompanyName", System.Data.SqlDbType.NVarChar, 40, "CompanyName")); this.sqlInsertCommand1.Parameters.Add (new System.Data . Sqlclient.sqlparameter ("@ ContactName", System.Data.sqldbtype.nvarchar, 30, "contactName"));
This.SqlinsertCommand1.Parameters.add (New System.data.sqlclient.sqlparameter ("@ contacttitle", system.data.sqldbtype.nvarchar, 30, "contactttitle");
This.SqlinsertCommand1.Parameters.add (New System.data.sqlclient.sqlparameter ("@ address", system.data.sqldbtype.nvarchar, 60, "address"));
This.SqlinsertCommand1.Parameters.add (new system.data.sqlclient.sqlparameter ("@ 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 (New System.Data.sqlclient.sqlparameter ("@ posthalcode", system.data.sqldbtype.nvarchar, 10, "postalcode");
This.SqlinsertCommand1.Parameters.add (new system.data.sqlclient.sqlparameter ("@ 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 (New System.data.sqlclient.sqlparameter ("@ fax", system.data.sqldbtype.nvarchar, 24, "fax"));
DataAdapter's selectCommand is used to fill DataAdapter.Fill () methods, and the data table line set selected by SelectCommand will be filled into the DataTable, then DataGrid is displayed.
DataGrid After editing, the five states in the corresponding DataTable may have the five states described above, probably, may be modified, may be deleted ( DetaAdapter.Update () method will insert the status of the statue to the database by calling the insertCommand command, updateCommand will modify the state of the model MODIFIED in the database, and DeleteCommand will state the state of the DELETED truly delete. If you don't create a DataAdapter from VS.NET, you may need to automatically generate SELECTCOMMAND, INSERTCOMMAND, and you may need to write InsertCommand, updateCommand, deleteCommand command yourself, there is a situation to return at least when SELECTCOMMAN When a primary key list or unique column, you can generate another three update commands from the SQLCommandbuilder from the selectcommand command, for example:
SqlConnection myconn = new sqlConnection (MyConnection);
SqldataAdapter mydataadapter = new sqldataadapter ();
MyDataAdapter.selectCommand = New SQLCOMMAND (MySelectQuery, MyConn); // Establish DataAdapter SelectCommand Command
Sqlcommandbuilder Custcb = New SqlcommandBuilder (MyDataAdapter); // Establish this DataAdapter CommandBuilder,
// This system will automatically generate three commands for this DataAdapter to generate INSERTCOMMAND, UPDATECOMMAND, and DeleteCommand.
Otherwise, use the DataAdapter.Update () method to update the database to write INSERTCOMMAND, UPDATECOMMAND, DELETECOMMAND three commands, you can refer to the write method of the INSERTCOMMAND command that is given above.
◆ Synchronization of data binding
Many controls in WinForm can be bound to the data source, binding and divided into two cases:
Simple data binding
Simple data binding refers to the ability to bind a control to a single data element (such as the value in the column of the data set table). This is a typical binding type for controls, such as TextBox controls, or Label controls (ie, only controls that only display a single value). In fact, any properties on the control can be bound to fields in the database.
Complex data binding
Complex data binding refers to the ability to bind a control to multiple data elements, typically bind to multiple records in the database, or bind to multiple other types of binding data elements, usually binding to A DataView. Examples of support for complex binding controls include DataGrid, Listbox, and ErrorProvider controls.
General DataGrid controls are binding with a DataView, DataGrid's data bindings are complex bindings because it is bound to a table with multiple records, and the DataGrid has two attributes related to data binding:
DataGrid.DataSource Properties: Gets or sets the data source of data displayed by DataGrid. It is generally binding to DataTable, DataView, DataSet, if the DataSource is set to DataSet, then the included table is not only one, then you must set a string to the DataMber property, which specifies the table to be bound. DataGrid.Datamember Properties: Get or set a specific list in DataSource, which is the above DataSource to set this property to specify the table to be bound.
There is often this requirement, there is a DataGrid in a form, showing some data, and some TextBox controls on the form, which uses data in the DataGrid, a TextBox control corresponding to a column of the DataGrid line, When the current row of DataGrid moves, the value in the TextBox control will also follow the current row of the DataGrid that changes the change.
To ensure that these data binding controls keep synchronization, you must have a unified management data binding mechanism to ensure synchronization of these controls. The data synchronization is bindingmanagerbase in the DOTNET, which is used to manage data sources, bind to the same data. The source data binding control can be managed by BindingManagerBase. BindingManagerBase can be obtained by form.bindingcontext.Item property, this property has two overloads:
Public BindingManagerBase this [Object Datasource] // Get bindingmanagerbase associated with specified data sources
Public BindingManagerBase this [Object DataSource, String DataMember] // Get a BindingManagerBase associated with the specified data source and data
All data binding controls of the data origin are the same as the objects that are passed when BindingManagerBase, will belong to this BindingManagerBase management, such as building a BindingManagerBase:
Bindingmanagerbase mybindingmanagerbaseparent = this.bindingcontext [myDataSet, "Customers"];
If there is a DataSource = MyDataSetSource = MyDataSet, DataGrid.DataMember = "Customers", then this DataGrid's data source is under the management of MyBindingManagerBaseparent.
The same simple data binding control is also the same as the DataSource of BindingManagerBase. When DataMember is a list of the table specified by BindingManagerBase, the data source of this control is also under this MyBindingManagerBaseParent management:
DataGrid1.datasource = MyDataSet;
DataGrid1.DataMember = "Customers";
TextCustomerid.Database.d (New Binding ("Text", MyDataSet, "Customers.customerid"); // TextBox's text attribute
// myDataSet's Customerid field binding
BindingManagerBase control has a current line concept. 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 triangle arrow in the DataGrid's row head to indicate the current line. . The value displayed in the simple binding control will be the content of the current line of the data source. So, as long as we change the BindingManagerBase's pointer, this can change the current row by clicking on the line you want, or change the current line settings in the program:
mybindingmanagerbaseparent.position = 10;
BindingManagerBase.Position Attribute changes will cause changes in BindingManagerBase the current row, that is, the current line of DataGrid binding this data source, the display content of simple binding controls will change, as shown below.
Figure-3
The current line is the first line, three above
The TextBox control also shows the data of the first line
Figure-4
The current line is the fourth line, the above three
The TextBox control also shows the data of the fourth line
BindingManagerBase's DataSource can be a DataSet, which can have multiple DataTable in DataSet, which can be associated with DataRelaton (relationship) to form the relationship between the parent table / subtaby. For example, still the example mentioned above, a DataGrid displays the Customer table, but also wants to have a DataGrid to display all of the current Customer all ORDER. This way we will need two BindingManagerBase, a BindingManagerBase corresponds to the Customer table, and the other BindingManagerBase corresponds to the Order table, and this Order Table also considers the relationship with the Customer table.
We have already established on the BindingManagerBase of Customer, and let's establish a BindingManagerBase corresponding to Order:
First we have to build the relationship between the Customer Table and the Order Table MYRELATION:
Datacolumn ParentColumn = mydataset.tables ["customers"]. Column ["customerid"]; // To establish a column of the parent table of the relationship, equivalent to the primary key
Datacolumn ChildColumn = MyDataSet.tables ["Orders"]. Column ["Customerid"]; // To establish a column of the relationship subtray, equivalent to foreign keys
Dataralation MyRelation = New DataRelation ("MyRelation", ParentColumn, ChildColumn, false; // establishment of relationships according to the relevant column of the child table
Then, through the relationship, establish BindingManagerBase corresponding to the Order table:
mybindingmanagerbasechild = this.bindingContext [MyDataSet, "CUSTOMERS.MYRELATION"]; // This data source will resolve all of the Order corresponding to the customer in a parent table
In this way, when the current row of BindingManagerBase is changed, the BindingManagerBase corresponding to the Order will also follow, the relationship between them is determined by MyRelation, as shown below: Figure-5
The DataGrid of the corresponding sub-table ORDER displays all of the ORDER of the Alfki client in the parent table, when clicked in the "Master Table" button, the current customer in the parent table changes to anaTr, the child table also changes to the display corresponding ANATR All ORDER
◆ Visit the contents of DataGrid in the program
DataBle has data line DATAROW, and there is no such thing in DataGrid, which makes people feel very uncomfortable, and it feels not natural. In DataTable, the hierarchy of a table is clear, the DataTable.Rows property can get the rowset of all rows contained in this table, and you can get a specific DataRow, data line by the index of the rowset DataRowCollection [Index]. Index DATAROW [INDEX] can also get the specific column of this line.
It is not so convenient in DataGrid. DataGrid is only available, the dataGrid.currentcell property, this property returns a structure of a DataGridcell type, and the DataGridcell structure indicates the line number and column number of this Cell. There is also a DataGrid.Item property, this property has two overloads:
Public Object this [DataGridcell] / / Get or set the value of the specified DataGridcell
Public Object this [int, int, int, "Gets or sets the value of cells located in the specified row and column
It can be seen that the access to the DataGrid is performed for a CELL. Often, we need to get the DataRow corresponding to this Cell from the current Cell, such as the interface you may select first, or a Cell, then click on a button, pop up a new window, show this line in the window. The content of all units, and allows the value of the unit to last, and finally the shutdown window is saved. This requires the lines and columns of the DataTable corresponding to the unit where the current DataGrid is located.
The data displayed in the DataGrid may pass through the DataView's DataView.rowfilter property, the data of the DataView.RowStateFilter property, may also pass through the DataGrid itself according to the forward and reverse sorting of each column, so the row index indicated by the DataGrid's CURRENTROWINDEX attribute The corresponding DataTable row is a big chance to be different, and it is not possible to get the corresponding DataTable row according to the DataGrid's currentrowindex.
At this time, BindingManagerBase will work, we can build a BindingManagerBase that corresponds to the data source bound to this DataGrid, so this BindingManagerBase can manage this data source.
/ / Set the data source of DataGrid
DataGrid1.datasource = MyDataSet;
DataGrid1.DataMember = "Customers";
// BindingManagerBase that establishes the same data source as DataGrid
Bindingmanagerbase mybindingmanagerbaseparent = this.bindingcontext [myDataSet, "Customers"];
Once this BindingManagerBase is established, you can get records of the current data source through the properties of BindingManagerbase: // BindingManagerBase's current returns the object of the data source, and you need to explicit this object for the data source bound to the DataView.
// Convert to DATAROWVIEW type
DataRowView MyDataRowView = (DATAROWVIEW) MyBindingManagerBaseParent.current
In this way, we can get the DataRowViewView of this Cell from the current Cell, and DataRowView can get DataRowView.Row properties and its convenient to get DATAROW.
If necessary, if you want to get the specific unit of the DataTable corresponding to this Cell, it is not only to get DataRow, and you have to know the column corresponding to this Cell.
This is divided into two cases:
First, DataGrid does not use TableStyles to set the column and format to be displayed. All columns of data source DataView will be displayed in the order of DataView itself, which can directly obtain the corresponding column index:
// Get the column index of the current DataGrid unit, this index is the same as the index of DataTable
INT columnnumber = datagrid.currentcell.columnnumber;
Another situation is that DataGrid uses tablestyles to set the columns and formats to be displayed, so that the column index of the DataGrid unit may be different with the index of DataTable, which is to use the DataGrid's TableStyles:
INT ColumnNumberDataGrid = DataGrid.currentcell.columnNumber; // Get the column index of the current DataGrid unit
INT ColumnNumberDataTable = DataGrid.tables [0] .GridColumnStyles [ColumnNumberDataGrid]. Mappingname
