C # 27 Datasets and DataAdapter

DataAdapter provides a bridge that connects DataSet objects and data sources. DataAdapter uses the command object to execute SQL commands in the data source to load data into the dataset and to keep changes in the data in the dataset consistent with the data source.

The dataset is one of the core members of ADO, and is a variety of. NET platform-based programming languages such as vb.net, c#.net, C + +. NET) database applications develop the classes that are most frequently contacted, because the dataset works by extracting data from the database in the ADO. After data extraction, a dataset is where the data is stored, which is the cache of data in the computer's memory for various data sources (SQL Server, OLE DB, and so on), so sometimes a dataset can be thought of as a data container (also known as a dataset). The client achieves an equivalent operation on the data source by reading, updating, and so on by the dataset's data set.

The biggest advantage of a dataset is offline (disconnected) and connected. Datasets can manipulate data in a database either offline or in a live connection. The benefit is that the server-side database connection thread is greatly reduced, which greatly reduces the operating pressure on the server side. So, with a small amount of data, using a dataset is the best choice.

Focus:

? The concept and use of data adapters

? The role of the data set

? Data sets and pros and cons

? How to use datasets

? Operating Data Sheet

Preview Lessons:

? How to use the data adapter

? What is a data set

? What are the advantages and disadvantages of data sets

? How to use datasets

? How to manipulate data tables

4.1 Concept and use of data adapters

DataAdapter provides a bridge that connects DataSet objects and data sources. DataAdapter uses the command object to execute SQL commands in the data source to load data into the dataset and to keep changes in the data in the dataset consistent with the data source.

Fun to understand DataAdapter is like a water pipe, through the engine, the water is transported from the water to the reservoir for preservation.

1. Create SqlDataAdapter

(1) Initializes a new instance of the SqlDataAdapter class.

1. String constring = "Data source=127.0.0.1;database=test;user
id=sa;password=123456 ";

2. SqlConnection myconnection = new SqlConnection (constring);

3. SqlCommand cmd = Myconnection.createcommand ();

4. Cmd.commandtext = "SELECT * from P_product";

5. DataSet ds = new DataSet ();

6. Myconnection.open ();

7. SqlDataAdapter adapter = new SqlDataAdapter ();

8. Adapter. SelectCommand = cmd;

9. Adapter. Fill (ds, "DS");

Myconnection.close ();

(2) Initializes a new instance of the SqlDataAdapter class with the specified SqlCommand.

1. string constring = "Data source=127.0.0.1;database=test;user
Id=sa;password= ";

2. SqlConnection myconnection = new SqlConnection (constring);

3. DataSet ds = new dataset ();

4. SqlCommand cmd = Myconnection.createcommand ();

5. Cmd.commandtext = "SELECT * from P_product";

6. Myconnection.open ();

7. SqlDataAdapter adapter = new SqlDataAdapter (CMD);

8. Adapter. Fill (ds, "DS");

9. Myconnection.close ();

(3) Initializes a new instance of the SqlDataAdapter class using the SelectCommand string and the SqlConnection object.

1. string constring = "Data source=127.0.0.1;database=test;user
Id=sa;password= ";

2. string strSQL = "SELECT * from P_product";

3. SqlConnection myconnection = new SqlConnection (constring);

4. DataSet ds = new dataset ();

5. Myconnection.open ();

6. SqlDataAdapter adapter = New SqlDataAdapter (strSQL, MyConnection);

7. Adapter. Fill (ds, "DS");

8. Myconnection.close ();

(4) Initializes a new instance of the SqlDataAdapter class using the SelectCommand string and a connection string.

1. string constring = "Data source=127.0.0.1;database=test;user
Id=sa;password= ";

2. string strSQL = "SELECT * from P_product";

3. DataSet ds = new dataset ();

4. SqlDataAdapter adapter = New SqlDataAdapter (strSQL, constring);

5. Adapter. Fill (ds, "DS");

2. DataAdapter and SqlConnection, SqlCommand to establish associations

The 1:dataadapter method is established when the parameter is constructed.

Mode 2: Established through the SelectCommand property.

1. SqlDataAdapter adapter = New SqlDataAdapter ();

2. Adapter. SelectCommand = new SqlCommand (strSQL, MyConnection);

4.2 Concepts of Datasets

Image, the dataset is a temporary database in memory. How do you understand this concept? If the database is in the pool, then the dataset is your home tank, and if the database is a supermarket, then the data set is your home refrigerator. If you're lazy you can buy a week's shopping home and put it in the fridge so that you avoid running to the supermarket every time you are hungry. If you go to the supermarket, more people will inevitably have to line up, even if the supermarket people are not many, you have to walk a certain way, walk also said, if the road again encounter unexpected events---such as pick up a dime such things, is bound to delay a long time ... anyway, running to the supermarket will certainly reduce your "eating" efficiency. For database access is the same, if each user's request to extract data from the server to satisfy, the same situation as above, if the server request too much then your request to queue, even if not queued, when the request server suddenly failure, such as natural disasters will affect the performance of your program.

Datasets are temporary data containers for databases that can be accessed in a disconnected type. At this point the database is the data source of the dataset, you can load the required data into the data set once, and then update to the database after the operation, this is the data set broken access mode. In addition, the data source for a dataset is not necessarily a database, and the data source for the dataset can be text, XML files, and so on, regardless of the data source from which the data set contains the data. NET provides a consistent programming model, which is a powerful place for datasets.

The classes that define a dataset and its data tables, data columns, and rows of data are in the system's System.Data namespace, with relationships such as:

Properties	Description
Columns	A collection of columns in a data table, DataColumnCollection type
Rows	A collection of rows in a data table, DataRowCollection type
DataSet	Gets the dataset to which this data table belongs
TableName	Gets or sets the primary key name of the data table
PrimaryKey	Gets or sets the primary key of the data table
Constrains	Gets the collection of this data table constraint, contraintcollection type
Method	Description
AcceptChanges	Commit all changes made to the data table
Clear	Clear all data in the data table
NewRow	Creates a new row with the same schema as the data table

The definition of a column is done using the DataColumn class, and here are the important properties and methods of the class:

Properties	Description
AllowDBNull	Gets or sets a value that indicates whether this column of the data table allows null values, which by default is True
AutoIncrement	Sets whether an identity column
AutoIncrementSeed	Identity column initial value (also called seed)
AutoIncrementStep	Increment of automatically generated column values
ColumnName	Column Name
DataType	Specifies the data type of the column, which can be a base in the. Net Framework Data type, default to String type
DefaultValue	Set or get the default value for this column
ReadOnly	Sets whether the column is read-only, true to set the column to read-only, default to non-read-only
Table	The DataTable to which the column belongs
Unique	Sets whether the value in each row of the column must be unique, or true to indicate that the column value cannot be duplicated, which is unique, and that the default is non-unique

4.3 Data set synthesis operations

Each dataset is a collection of one or more DataTable objects (the DataTable corresponds to a table in the database), which consists of data rows (DataRow), data Columns (DataColumn), field names (column name), The data grid (Item), together with the constraint (Constraint) and the relationship between the data in the DataTable Object (relations) and the data display sort (DataView) information.

DataView is used to provide sorting and filtering capabilities when observing data. DataColumn is used to restrict the data values in a table. For example, what column of data is the default value, what column data value is the range, which is the primary key, whether the data value is read-only, and so on.

Because there may be multiple tables in a dataset, these tables may have association relationships and are therefore expressed in parentrelations and ChildRelations. The ParentRelations table is the parent table, ChildRelations is the child table, and the child table is a reference to the parent table, which causes a row in one table to be associated with a row in another table or even an entire table.

We will discuss the operation of these collections from the "additions and deletions" four.

Tables collection of 4.3.1 datasets

L Increase:

The simplest way to add a DataTable to a dataset is to call the Add method of the IList interface, such as adding two data tables named "Person" and "Books" to the dataset:

Ds. Tables.add ("person");

Ds. Tables.add ("Books");

L Delete:

Remove a DataTable from the dataset using the IList interface's remove and RemoveAt methods:

DataSet ds=new DataSet ();

Datatabledtperson=ds. Tables.add ("person");

Ds.      Tables.removeat (0); Delete the table with index 0 from the dataset, which is Dtperson

Ds. Tables.remove (Dtperson);//delete Dtperson from data set

L Change:

The DataTable in the dataset can only be added and deleted, and cannot be modified.

L Check:

To get the DataTable in the dataset, you can use the indexer:

DataSet ds=new DataSet ();

Ds. Tables.add ("person");

DataTable Dt=ds. tables[0];//get a DataTable by Digital index

DataTable Dt=ds. tables["Person"]; Get a DataTable by table name

Columns collection of 4.3.2 DataTable

Increase:

Adding columns to a data table as we mentioned earlier, the addition is also an add () that uses the IList interface. The following are the most common ways to add columns:

DTPERSON.COLUMNS.ADD ("Psnno", typeof (String));

By deleting:

Deleting columns in a data table is also a remove or RemoveAt method that uses the IList interface:

DtPerson.Columns.RemoveAt (0); Delete columns by index, this is the first column to delete

DtPerson.Columns.Remove ("Psnno"); Delete a column by its name

Change:

Modifying a column in a DataTable can be obtained first through the indexer and then modified:

datacolumnc=dtperson.columns["Psnname"];

C.allowdbnull=false;

Check:

Getting a column from a DataTable is also a way to use indexers:

datacolumnc=dtperson.columns["Psnname"]; Get the Column object by column name

Datacolumnc=dtperson.columns[0]; Get the Column object by column index, here is the first column

The following program completes the column names of the columns that traverse the person table:

foreach (Datacolumndc in DS. tables["Person"]. Columns)

{

Console.WriteLine ("Column name: {0}", DC.) ColumnName);

}

The Rows collection of the 4.3.3 DataTable:

Increase:

There are two ways to add data rows to a DataTable using the Add () method. One way to get a blank line before joining a row is to use the NewRow method of the DataTable:

Datarowr=dtperson.newrow ();

DTPERSON.ROWS.ADD (R);

In the second way, you can construct an array of objects based on the column structure of the table, in such a way that you construct an array that corresponds to the column structure of the table:

Object[]r=new object[]{"001", "Pavarotti", "male", 22, "Zhengzhou, China"}

By deleting:

To delete a row of data, you can use the Remove and removeat two methods of the IList interface, and you can use the Delete method of the row to delete the row using the RemoveAt or Delete method, which is commonly used:

Datarowr=dtperson.newrow ();

DTPERSON.ROWS.ADD (R);

R.delete (); Delete r rows

DtPerson.Rows.Remove (R); Delete r rows

DtPerson.Rows.RemoveAt (0); Delete rows by row index, this is the first row deleted

Change:

Because the actual data of the dataset is saved in the row, modifying the row data is the most common operation in the dataset, and modifying the row is the first to use the table's indexer to get the row before using the row indexer to modify it:

DataRow R=dtperson.rows[0];

r["Psnsex"]= "female"; Modify the value of the row by column name

R[0]= ""; Modify rows by column index, here is the first column of data that modifies the row

Check:

To get a row of a table using the indexer of a table, get a row of a column value using the index of the row, the way we introduced the modification of the row has been introduced, you can use the data set directly using the index to get the value of a row of a column, note that the returned value is of type object, A type conversion is also required to obtain a specific value:

Gets the data for the second row of the person table psnname column:

String Name=ds. tables["Person"]. ROWS[1]. columns["Psnname"]. ToString ();

Gets the column data with the second row index of 4 for the person table, noting that the index starts at 0:

Stringaddress=ds. tables["Person"]. ROWS[1]. COLUMNS[4]. ToString ();

Gets the data for the first table in the DataSet and the fifth column in the second row:

Stringsex=ds. Tables[0]. ROWS[1]. COLUMNS[4]. ToString ();

Of course, you can also iterate through the data of all the rows in the table:

foreach (Datarowr in dtperson.rows)

{

foreach (DataColumn C in Dtperson.columns)

Console.WriteLine ("{0}", R[c.columnname]);

}

4.4 DataView Class

(1) Get DataView.

DataView dv = ds. Tables[0]. DefaultView;

Or

DataView dv = new DataView (ds. tables["Product"], "ID >", "ID DESC",

DataViewRowState.CurrentRows);

(2) Get the DataView data.

foreach (DataRowView rowview in DV)

{

for (int i = 0; i < DV. Table.Columns.Count; i++)

{

Response.Write (Rowview[i] + "
");

}

}

(3) Sort the result set filtering.

1. DataView dv = ds. Tables[0]. DefaultView;

2. dv. RowFilter = "ID >";

3. dv. Sort = "ID DESC";

4. int c = dv. Count;

5. if (C > 51)

6. {

7. for (int n =; n < C; n++)

8. {

9. dv. Delete (n);

10.}

11.}

this. DataGrid1.DataSource = DV;

C # 27 Datasets and DataAdapter