Full operation of the ASP.net DataTable

A DataTable represents a memory-related datasheet that can be created and used using control drag-and-drop in a toolbar, and can be created and used independently of the programming process as needed, most commonly as a member of a dataset. In this case, you need to use the dynamic creation of the data table as needed during the programming process. In section 8.4, the data table of a DataTable and its operation are mainly written in a coded way.

1 Code to create DataTable data table

As mentioned in section 8.3 above, creating a datasheet directly in the dataset by adding an object can be done by adding a DataTable to the dataset by using the Add method, which is the operation of adding a DataTable using the control's visualization, so how do you create a DataTable datasheet in your code?

You can use the corresponding DataTable constructor to create a DataTable object in your program. Create a datasheet with a table named TableName, and the implementation code looks like this:

DataTable newtable = new DataTable (tablename);

You can also create a DataTable object by using either the Fill method of the DataAdapter object or the FillSchema method in the dataset, which is used in connection with the database operation. The implementation code looks like this:

Database Join string

String connectionString =

"Data source=local;initial catalog=northwind;integrated security=true; Userid=sa; Password= ";

SQL statement Query

String CommandString = "SELECT * from Customers";

Create the SqlDataAdapter object and execute the SQL command

SqlDataAdapter dataAdapter = new SqlDataAdapter (CommandString, connectionString);

Creating DataSet Datasets

DataSet DataSet = new DataSet ();

To add a datasheet to a data set

DataTable datatable = Dataset.tables ("Customers");

Populating data in a dataset

DataAdapter.Fill (DataSet, "Customers");

Note: After you add a DataTable as a member to the Tables collection of a dataset, you cannot add it to the table collection of any other dataset.

When you first create a DataTable using the DataTable constructor, there is no schema (that is, structs, no columns). DataTable data without a schema represents no way to use it, so to define the schema of the table before using the DataTable datasheet, you must create the DataColumn object and add it to the Columns collection of the table. How to use code to create a columns column is described in later sections of this chapter.

When you create a DataTable, you do not need to provide a value for the TableName property, you can specify the property at another time, or leave it blank, which does not affect the use of the DataTable. It should be noted that when a table with no tablename value is added to the dataset, the table gets a default name Tablen that is incremented from "table" (representing TABLE0).

The following example creates an instance of a DataTable object and assigns the name "Customers" to it. The implementation code looks like this:

DataTable worktable = new DataTable ("Customers");

The following code adds the created DataTable Instance Customers table to the Tables collection of the dataset. The implementation code looks like this:

DataSet customers = new DataSet ();

DataTable customerstable = Customers. Tables.add ("customerstable");

Or

DataSet customers = new DataSet ();

DataTable customerstable = new DataTable ("Customers")

Customers. Tables.add (Customers);

2 Adding a DataTable column in a programmatic way

You've learned to create a DataTable using code, but when you first create a DataTable using the DataTable constructor, there is no schema (that is, structs, no columns). To define a schema for a table, you must create a DataColumn object and add it to the Columns collection of the table. You can also define a primary key column for a table, and you can create a constraint constraint object and add it to the table's constraints constraint collection.

The DataColumn type represents a column on a DataTable. In general, a collection of all DataColumn types bound to a DataTable represents a table.

The DataTable contains a collection of DataColumn objects that are referenced by the table's Columns property. The collection of this column, along with any constraints, defines the schema (that is, the structure) of the table.

You can create a DataColumn object within a table by using the DataColumn constructor, or by calling the Add method of the table's Columns property. The Add method accepts optional ColumnName, datatype parameters, and creates a new DataColumn as a member of the collection. It also accepts an existing DataColumn object and adds it to the collection, and returns a reference to the added DataColumn based on the request.

The following example adds four columns to the DataTable. The implementation code looks like this:

DataTable worktable = new DataTable ("Customers");

DataColumn Workcol = WorkTable.Columns.Add ("CustID");

WORKTABLE.COLUMNS.ADD ("Custlname");

WORKTABLE.COLUMNS.ADD ("Custfname");

WORKTABLE.COLUMNS.ADD ("purchases");

Code Description:

Q custid,custlname,custfname,purchases: The name of the column in the datasheet customers.

3 Setting the primary key of the DataTable datasheet

A common rule of database development is that a table must have at least one column as the primary key. A PRIMARY KEY constraint is used to uniquely identify a record (row) in a given table. Suppose that you now need to create a new DataColumn column to represent the Empid field and that you want to use the column as the primary key for the table, it must have the allowdbnull and unique attributes, and the implementation code looks like this:

DataTable worktable = new DataTable ("Customers");

DataColumn Workcol = WorkTable.Columns.Add ("CustID", typeof (Int32));

Workcol.allowdbnull = false;

Workcol.unique = true;

WORKTABLE.COLUMNS.ADD ("Custlname", typeof (String));

WORKTABLE.COLUMNS.ADD ("Custfname", typeof (String));

WORKTABLE.COLUMNS.ADD ("Purchases", typeof (String));

Code Description:

The properties used in the CustID column in the example are set to disallow DBNull values and to constrain values to unique. However, if you define the CustID column as the primary key column for the table, the AllowDBNull property is automatically set to false and the unique property is automatically set to true.

4 Set the data type of the column

Now that you know how to add a column to a new datasheet by learning from the above 8.4.2 section, let's take a look at how to set the data type of the column for the added column. A data type is a data type attribute that indicates a column of data, depending on the needs you can create different columns in the DataTable datasheet, and you can set different data types for different columns to meet your needs. Continue with the example above Customers table to add data types for the columns you create:

The implementation code looks like this:

Create a datasheet customers

DataTable customerstable = new DataTable ("Customers");

Creating a Int32 type name is the CustID column, which sets the column as the primary key and is not allowed to be empty.

DataColumn Customerscol = CustomersTable.Columns.Add ("CustID", typeof (Int32));

Customerscol.allowdbnull = false;

Customerscol.unique = true;

Create three string columns of type custlname,custfname,purchases

CUSTOMERSTABLE.COLUMNS.ADD ("Custlname", typeof (String));

CUSTOMERSTABLE.COLUMNS.ADD ("Custfname", typeof (String));

CUSTOMERSTABLE.COLUMNS.ADD ("Purchases", typeof (String));

Code Description:

In the example, the CustID column is defined as the primary key column for the table. The data type specified by the CustID column is int32,custlname column, custfname column, purchases column, the data type specified by the column is string, and of course you can not set the data type of the column. At this point the DataColumn DataType property defaults to the string type, and of course you can set the column data type as needed when creating the column name.

5 Enable autoincrementing fields

In 8.4.4, you learned how to set the data type of the DataColumn column, and after you set the data type of the DataColumn column, you can set the column to be automatically incremented as the Sql-server database table. Simply put, adding columns automatically ensures that when a new row is added to a given table, the value of the column can be automatically specified based on the current increment step value. This feature is especially useful when a column is not a duplicate worthy primary key. This function can be controlled in a DataTable using AutoIncrement (whether the column automatically increments the column value), AutoIncrementSeed (starting value, seed value), and AutoIncrementStep (step) properties.

The following is an example of creating a DataColumn column that supports automatic increments. The seed value is used to mark the starting value of the column, and the step value indicates the value of increasing the seed value when incremented, as shown in the following code:

Create a new column

DataColumn MyColumn = new DataColumn ();

Mycolumn.columnname = "CustID";

Mycolumn.datatype = System.Type.GetType ("System.Int32");

Set auto Increment

Mycolumn.autoincrement = true;

mycolumn.autoincrementseed = 0;

Mycolumn.autoincrementstep = 1;

Code Description:

Q AutoIncrement: Whether the column automatically increments the value of the column, True indicates automatic increment, false indicates that it cannot be automatically incremented.

Q AutoIncrementSeed: The starting value of the seed value, the starting value of the column with the AutoIncrement property set to True.

Q autoincrementstep: Step size, recursive increment, and AutoIncrement property set to True for the length of the column.

Create a CustID column with a data type of Int32, set the column AutoIncrement property to True for the effect of automatically increasing the value of this field, and AutoIncrementSeed set the seed value to 0, which is counting starting from 0 At the same time, set the automatically increased step autoincrementstep to 1, one at a time. Because the seed value is set to 0, the preceding 5 values should be 0, 1, 2, 3, and 4.

You can add this DataColumn in a previous DataTable to test it. Then add some new rows to the table and, of course, automatically dump the values in the CustID column, as shown in the following code:

Implement columns to automatically add functionality

protected void Button1_Click (object sender, EventArgs e)

{

Create a new data column, Name: CustID, Data type: Int32.

DataColumn MyColumn = new DataColumn ();

Mycolumn.columnname = "CustID";

Mycolumn.datatype = System.Type.GetType ("System.Int32");

Automatically increases the newly created column setting, with a seed of 0 and a step size of 1.

Mycolumn.autoincrement = true;

mycolumn.autoincrementseed = 0;

Mycolumn.autoincrementstep = 1;

Add this column to the Customers table.

DataTable customerstable = new DataTable ("Customers");

MyTable. Customerstable.add (MyColumn);

Add 20 new rows.

DataRow R;

for (int i = 0; i < i++)

{

R = Customerstable.newrow ();

CUSTOMERSTABLE.ROWS.ADD (R);

}

Displays the data values for each row.

String temp = "";

DataRowCollection rows = customerstable.rows;

for (int i = 0; i < CustomersTable.Rows.Count; i++)

{

DataRow currrow = rows[i];

Temp + + currrow["CustID"] + "";

}

Show all values on Label1 surface

Label1.Text = label1.text+temp;

}

If you write the above code in the established Web program, and then click on the "button" button, you will get a series of 1 with an initial value of 0, and the result is shown in figure: 8.4.1.

6 Adding a DataTable row in a programmatic way

After you have defined the schema for the DataTable, that is, after you have set the desired column name, you can add the data rows to the table by adding the DataRow object to the table's Rows collection. Similar to adding DataColumn, you can create DataRow objects within a table either by using the DataRow constructor, or by calling the Add method of the table's Rows property.

The DataColumn object collection represents the schema (schema) of the table. The DataTable saves all columns in the table through the internal datacolumncollection type. Instead, the DataRow type collection represents the actual data in the table. This way, if you have 10 records in the Customers table, you can use 10 DataRow types to represent them. Use members of the DataRow class to insert, delete, evaluate, and manipulate values in a table.

To create an object that DataRow data rows, the implementation code looks like this:

Create a Customers Datasheet

DataTable customerstable = new DataTable ("Customers");

Create a new row of data

DataRow Arow = Customerstable.newrow ();

Set the value of a row

Arow[columnname] = DataValue;

Add data rows to the customers datasheet created

CUSTOMERSTABLE.ROWS.ADD (Arow);

Function Description:

Creates a new row of Arow and datavalue the row to a column name, and then adds the row to the Customers table. Using DataRow is a bit different from using DataColumn, because it is not possible to create an instance of this type directly, but to obtain a reference from a given DataTable. For example, suppose you want to add a new row to the Customers table, the Datatable.newrow () method gets the next empty space, and then populates the data for each column.

The implementation code looks like this:

Create a data table

DataTable customerstable = new DataTable ("Customers");

Declaring row and column variables for a datasheet

DataColumn column;

DataRow Row;

Create a new column, set the column's data and column names, and add the new column to the Customers table

Column=new DataColumn ();

Column. DataType = System.Type.GetType ("System.Int32");

Column. ColumnName = "CustID";

CUSTOMERSTABLE.COLUMNS.ADD (column);

and create a new column

column = new DataColumn ();

Column. DataType = Type.GetType ("System.String");

Column. ColumnName = "Custlname";

CUSTOMERSTABLE.COLUMNS.ADD (column);

Create a new line and add this row to the Customers table

for (int i = 0; i < i++)

{

row = Customerstable.newrow ();

row["CustID"] = i;

row["custlname"] = "item" + i.tostring ();

CUSTOMERSTABLE.ROWS.ADD (row);

}

Function Description:

Create two columns to the Customerstable table, which are CustID, Custlname, and then loop to produce 10 rows and append values to the Customerstable table.

7 operation DataTable: Updating rows

In the previous section, how to add a DataRow line, the next thing you need to know about the DataTable is how to update existing DataRow rows with new values. The following is an introduction to the problem with the DataTable update row.

One of the most common methods is to use the Select () method to obtain a row that matches a given filter condition. Once these DataRow are obtained, they are modified accordingly. For example, suppose you have a new button that searches for rows in the DataTable for all EmployeeID column values of 5 after the click. Once these items are identified, the EmployeeID column corresponding to the value 5 can be changed to 6. The instance code is as follows:

Database Join string

String connectionString =

"Data source=local;initial catalog=northwind;integrated security=true; Userid=sa; Password= ";

The SQL statement pulls the data information from the Orders table

String CommandString = "SELECT * from Orders";

SqlDataAdapter dataAdapter = new SqlDataAdapter (CommandString, connectionString);

DataSet DataSet = new DataSet ();

Populating data sets

DataAdapter.Fill (DataSet, "Orders");

Populating data tables

DataTable datatable = Dataset.tables ("Orders");

Create a variable that filters out rows that have a EmployeeID column value of 5

String filterstr = "employeeid=5";

string stremployeeid = null;

Query out all rows with the EmployeeID column value of 5.

Datarow[] employeeid= datatable.select (FILTERSTR);

EmployeeID column corresponds to the value 5 change to 6

for (int i = 0; i < employeeid.length; i++)

{

DataRow temp = EmployeeID [i];

Stremployeeid + + temp["EmployeeID"] = 6;

EmployeeID [i] = temp;

}

8 Deleting a DataTable row in a programmatic way

There are two ways to remove a DataRow object from a DataTable object: The Remove method of the DataRowCollection object and the Delete method of the DataRow object.

Both the Remove method and the Delete method can remove the row DataRow of a DataTable, but the former removes the DataRow from the DataRowCollection, and the latter only marks the row for deletion. The actual deletion occurs when the application invokes the AcceptChanges method. By using Delete, you can programmatically check which rows are marked for deletion before the actual deletion. If a row is marked for deletion, its RowState property is set to deleted.

When you use a dataset or DataTable with a DataAdapter and relational data source, remove the row with the DataRow delete method. The Delete method simply marks the row as deleted in the dataset or DataTable and does not remove it. When DataAdapter encounters a row marked as deleted, it executes its DeleteCommand method to delete the row in the data source. You can then permanently remove the row using the AcceptChanges method. If you delete the row by using remove, the line is completely removed from the table, but DataAdapter does not delete the row in the data source.

The DataRowCollection Remove method takes DataRow as a parameter and removes it from the collection, and the implementation code looks like this:

DataTable customerstable = new DataTable ("Customers");

DataRow Workrow = Emptable.newrow ();

CustomersTable.Rows.Remove (Workrow);

In contrast, the following example shows how to call the Delete method on DataRow to change its rowstate to deleted. The implementation code looks like this:

DataTable customerstable = new DataTable ("Customers");

DataRow Workrow = Emptable.newrow ();

Workrow.delete ();

If a row is marked for deletion, and the AcceptChanges method of the DataTable object is invoked, the row is removed from the DataTable. By contrast, if you call RejectChanges, the RowState of the row reverts to the state before it was marked as deleted.

9 populating the DataTable with data

Ado. NET is a memory-resident representation of data that provides a consistent programming model that is independent of the data source. The dataset represents the entire dataset, which contains the relationships between tables, constraints, and tables. Because the dataset is independent of the data source, the dataset can contain data that is local to the application, or it can contain data from multiple data sources. Interactions with existing data sources are controlled by DataAdapter.

The DataAdapter SelectCommand property is a command object that is used to retrieve data from the data source. The DataAdapter InsertCommand, UpdateCommand, and DeleteCommand properties are also command objects used to manage updates to data in the data source by modifying the data in the dataset.

The DataAdapter Fill method is used to populate the DataSet with the results of the DataAdapter SelectCommand. Fill the DataSet and DataTable object that will be populated (or the name of the DataTable to be populated with the rows returned from SelectCommand) as its arguments.

The Fill method uses the DataReader object to implicitly return the column names and types that are used to create a table in the dataset and the data used to populate the table rows in the dataset. Tables and columns are created only when they are not present, otherwise, fill uses the existing dataset schema.

The following code example uses the SqlDataAdapter object to get data from a Microsoft SQL Server Northwind database to a Customers table in a customers dataset.

The implementation code looks like this:

Populate the Customers table with the DataAdapter Fill method

String connectionString =

"Data source= local;initial catalog=northwind;integrated security=true; Userid=sa; Password= ";

String CommandString = "SELECT * from Customers";

SqlDataAdapter dataAdapter = new SqlDataAdapter (CommandString, connectionString);

DataSet customers = new DataSet ();

DataAdapter.Fill (Customers, "customers");

10 query functions similar to SQL statements in a DataTable

Implement queries that resemble SQL statements in a dataset that is detached from the database.

That is, select ... from tablename where ... ) such a feature.

The data that is queried from the dataset is saved as rows into the Arow.

The implementation code looks like this:

DataSet ds= new DataSet ();

datarow[] Arow = ds. Tables[tablename]. Select ("" + columnsname+ "=" "+ DataValue +" ");

Code Description:

Qtablename: Table name in Data set DS.

Qcolumnsname:tablename a column name in a table.

Qdatavalue: The value of the parameter corresponding to the column name.

Function Description:

The above statement function is equivalent to the query statement in the SQL statement

Select * FROM TableName where columnsname= DataValue

Statistics of data records in one DataTable

When working with databases such as Sql-server and Oracle, you can easily count or compute related results by counting and calculating functions such as Sum, aver, and count, so can you use the same dataset in the DataTable dataset that has been retrieved? A DataTable that is dynamically created in a program is not able to use SQL statements for query statistics, for example, you cannot use a SELECT statement to get query statistics. So how do you make statistics in a DataTable?

This section describes a simple way to easily get the results of records in a DataTable. The simple approach is to invoke the function compute of a powerful DataTable.

The functions are as follows:

public Object Compute (string expression,string filter)

Parameter description:

The Qexpression parameter requires an aggregate function, and the expression string to be evaluated is essentially similar to a statistical expression in SQL Server. For example, the following is a valid expression: Count (ID).

Qfilter: A statistical filter string that only records that satisfy this filter condition are counted, that is, which rows are used in an expression.

The following example sql-server the orders datasheet in the Northwind database in the database, describing order information, including fields: Order Number (OrderID), customer number (CustomerID), employee number (EmployeeID), Order Time (OrderDate), ship number (ShipVia), freight (Freight).

1. Statistics on the number of EmployeeID for all employees: 5

Table.compute ("Count (*)", "employeeid=5");

2. Statistics of all freight freight The number of freight is greater than 100

Table.compute ("Count (*)", "freight>100");

3. Average of statistical freight freight

Table.compute ("Aver (Freight)", "true");

4. Total freight for statistics of employee number EmployeeID 5:

Table.compute ("Sum (Freight)", "employeeid=5");

All of these are calculated totals for each column, so to add a row to the total you can use the following method:

Create a DataSet

DataSet customers = new DataSet ();

Add a datasheet named Customerstable in the dataset

DataTable customerstable = Customers. Tables.add ("customerstable");

Create a new row and add it to the Customerstable datasheet

DataRow DataRow = new DataRow ();

Datarow= Customerstable.newrow ();

And then there's the statistic, declaring the int variable i: The loop variable, the number of columns in the Colcnt:customerstable table.

int i;

int colcnt;

colcnt = CustomersTable.Cols.Count;

For (i=0;i< colcnt-1;i++)

Find and assign the result to the new DataRow data row

DataRow (i) = Customerstable.compute ("Sum" ("+i.tostring () +"), "true");

Adding data rows to the Customerstable datasheet

CUSTOMERSTABLE.ROWS.ADD (DataRow);

Summary of some applications of DataTable and XML

Ado.net and XML are tightly knit in asp.net2.0, and in chapter 7th the data in Ado.net is easily accessible in XML through a dataset, so can a DataTable manipulate an XML data file like it does in a dataset? The answer is yes, the following is an operation that writes data from a DataTable to an XML file, and the instance code looks like this:

Create a Customers Datasheet

DataTable dt = new DataTable ("Customers");

Add two columns Custid,custlname data types are int32,string and added to datasheet

DataColumn DC1 = new DataColumn ("CustID", Type.GetType ("System.Int32"));

DataColumn DC2 = new DataColumn ("Custlname", Type.GetType ("System.String"));

Dt. Columns.Add (DC1);

Dt. Columns.Add (DC2);

Use loops to create rows of data and assign values to add to a datasheet

for (int i = 0; i < al. Count; i++)

{

DataRow dr = dt. NewRow ();

Ds_option DSO = (ds_option) al[i];

dr["CustID"] = dso.id;

dr["Custlname"] = DSO. Name;

Dt. Rows.Add (DR);

}

Write data from a datasheet to an XML file

String Xmlstr;

System.IO.StringWriter writer = new System.IO.StringWriter ();

Dt. WriteXml (writer);

Xmlstr = writer. ToString ();

return xmlstr;

The operation of writing data to an XML file, of course, can also be read from the XML file to the DataTable data table, the method tired is similar, but to build a good structure of the DataTable, or you will be wrong.

The instance code looks like this:

string tbxml = Xmlinfo;

DataTable dt = new DataTable ("Customers");

DataColumn DC1 = new DataColumn ("CustID", Type.GetType ("System.Int32"));

DataColumn DC2 = new DataColumn ("Custlname", Type.GetType ("System.String"));

Dt. Columns.Add (DC1);

Dt. Columns.Add (DC2);

System.IO.StringReader reader = new System.IO.StringReader (tbxml);

Dt. READXML (reader);