What are the common objects in ado.net? Describe them separately.

Command object

1. Command object Overview

The command object can communicate directly with the data source using database commands. Its Attributes are as follows:

Name: command objectProgramName. InCodeTo reference the command object.

Connection: a reference to a connection object. The command object uses this object to communicate with the database.

Commandtype: Text | storeproduce | tabledirect.

Commandtext: SQL statement | storeproduce.

Parameters: parameters contained in the command object.

You can create a command object in the following three ways:

Method 1

String strconn, strsql;

Strconn = "provider = sqloledb; Data Source = (local) \ netsdk; initial catalog = northwind; trusted_connection = yes ;";

Strsql = "select customerid, companyName from customers ";

Oledbconnection Cn = new oledbconnection (strconn );

CN. open ();

Oledbcommand cmd;

 

Cmd = cn. createcommand ();

Cmd. commandtext = strsql;

Method 2

String strconn, strsql;

Strconn = "provider = sqloledb; Data Source = (local) \ netsdk; initial catalog = northwind; trusted_connection = yes ;";

Strsql = "select customerid, companyName from customers ";

Oledbconnection Cn = new oledbconnection (strconn );

CN. open ();

Oledbcommand cmd;

 

Cmd = new oledbcommand ();

Cmd. commandtext = strsql;

Cmd. Connection = cn; Method 3

String strconn, strsql;

Strconn = "provider = sqloledb; Data Source = (local) \ netsdk; initial catalog = northwind; trusted_connection = yes ;";

Strsql = "select customerid, companyName from customers ";

Oledbconnection Cn = new oledbconnection (strconn );

CN. open ();

Oledbcommand cmd;

 

Cmd = new oledbcommand (strsql, CN );

 

2. Command object parameters

Common attributes are as follows:

Parametername: parameter name, for example, "@ catid ".

Dbtype, sqltype, oledbtype: Data Type of the parameter.

Direction: parameterdirection enumeration value.

Parameterdirection. Input (default) | parameterdirection. InputOutput |

Parameterdirection. Output | parameterdirection. returnvalue

 

3. Execute

Four Execution Methods:

Executenonquery () returns the number of lines affected by the command.

Executescalar () returns the first column of the First row (used with the set function ). Aggregate functions such as Count (*), sum, and AVG.

Executereader () returns a datareader object. If the SQL statement is not used to query the SELECT statement, an EOF type system. Data. sqlclient. sqldatareader is returned ).

Executexmlreader () returns an xmlreader object.

4. parameterized Query

Example

String strconn, strsql;

Strconn = "provider = sqloledb; Data Source = (local) \ netsdk; initial catalog = northwind; trusted_connection = yes ;";

Oledbconnection Cn = new oledbconnection (strconn );

CN. open ();

Strsql = "select orderid, customerid, employeeid, orderdate from order where customerid =? ";

Oledbcommand cmd = new oledbcommand (strsql, CN );

Cmd. Parameters. Add ("@ customerid", oledbtype. wchar, 5 );

Cmd. Parameters [0]. value = "alfki ";

Oledbdatareader RDR = cmd. executereader ();

Bytes ----------------------------------------------------------------------------------------------------------------------------

The connection object can be used to connect to a database to manage database transactions.

1. Create a connection object

Four Database Connection Methods:

Oledb: system. Data. oledb. oledbconnection

SQL: system. Data. sqlcilent. sqlconnection

ODBC: system. Data. ODBC. odbcconnection

ORACLE: system. Data. oracleclient. oracleconnection

 

Let's take a look at an instance created by oledbconnection. There are two methods in total.

First, create an uninitialized oledbconnection object and use connectionstring to initialize it.

Create a connection object

String strconn;

Strconn = "provider = sqloledb; Data Source = {local) \ netsdk; initial catalog = northwind; trusted_connection = yes ;";

Oledbconnection CN;

CN = new oledbconnection ();

CN. connectionstring = strconn;

 

Type 2: Use the constructor to initialize the oledbconnection object.

Create a connection object

String strconn;

Strconn = "provider = sqloledb; Data Source = {local) \ netsdk; initial catalog = northwind; trusted_connection = yes ;";

Oledbconnection CN;

CN = new oledbconnection (strconn );

 

2. connection string

Several common parameters used to connect strings:

Provider: this property is used to set or return the name of the connection provider. It is only used for oledbconnection objects.

Connection timeout or connect Timeout: Wait for the connection time (in seconds) to connect to the server before the attempt is aborted and an exception occurs ). The default value is 15 seconds.

Initail catalog: name of the database.

Data source: the name of the SQL server used when the connection is opened, or the file name of the Microsoft Access database.

Password: the logon password of the SQL Server account.

User ID: The Logon account of SQL Server.

Integrated Security or trusted connection: this parameter determines whether the connection is a secure connection. Possible values include true, false, and sspi (sspi is synonymous with true ).

Persist Security info: when it is set to false, if the connection is opened or has been opened, the security sensitive information (such as the password) will not be returned as part of the connection. Setting the attribute value to true may pose security risks. False is the default value.

 

3. Open and Close the connection

Open Method: Open the connection

Close method: Close the connection

Note: If a connection pool is used, closing the connection object will not actually close the connection to the data source.

If you only use dataadapter, you do not need to explicitly open or close the connection. When you call these object methods (fill method, update method), the system automatically checks whether the connection is enabled.

Bytes ----------------------------------------------------------------------------------------------------------------------------

Datareader object

1. datareader object Overview

The datareader object can only perform top-down access to the queried dataset, but it is highly efficient. If you only access data, you can use datareader. However, datareader must be connected all the time. Therefore, a small part of the result is first stored in the memory and then read from the database, which is equivalent to a cache mechanism. This is obvious for millions of query results.

Datareader objects have the following features:

Quick access to data. Because the datareader object is read-only and read-only, the overhead is relatively small and the speed is faster than that of dataset.

Read-only and read-only. Data cannot be processed. Only data can be displayed.

Manage connections by yourself. The dataadapter object can automatically open and close the connection. The datareader object must explicitly open and close the connection.

Use fewer server resources.

 

2. Create a datareader object

Procedure:

(1) Create and open a database connection.

(2) create a command object.

(3) create a datareader from the command object.

(4) execute the executereader object.

(5) use the datareader object.

(6) Close the datareader object.

(7) Close the connection object.

 

Datareader object creation example

Example of datareader object usage

// Open connection and create command

Sqlconnection conn = new sqlconnection ("Data Source = localhost; Integrated Security = true; initial catalog = pubs ;");

Conn. open ();

Sqlcommand extends authors = new sqlcommand ("select * from authors", Conn );

 

// Create a datareader object and read data

Sqldatareader Dr;

Dr = login authors. executereader ();

While (dr. Read ())

{

ListBox. Items. Add (Dr ["au_lname"] + "," + Dr ["au_fname"]);

}

 

// Close datareader and Connection

Dr. Close ();

Conn. Close ();

 

When using the datareader object for connection, you need to use the try... catch... finally statement to ensure that the connection will be closed if a failure occurs in some aspects. Otherwise, the connection remains open indefinitely.

 

Capture Error

Try

{

Conn. open ();

Dr = login authors. executereader ();

// Use the data returned by datareader

}

Catch

{

// Handle errors

}

Finally

{

Dr. Close ();

Conn. Close ();

}

3. read data from datareader

Call the read method for each record: You can call the read method to access a record in the dataread object, because the default position in the datareader object is before the first record, therefore, you must call the read method before accessing any data. When no record is available, the read method returns a null value.

Example of calling the read Method

While (dr. Read ())

{

Lbname. Text + = Dr ["au_name"];

}

Access Field: You can access a field by sequential location, name, or calling the appropriate get method. The get method includes getdatetime, getdouble, getint32, or getstring.

Call the get method to access data

Dr. Read ();

Lbname. Text = dr. getstring (1) + "," + dr. getstring (2 );

Reference the data field of the current record by name

Dr ["au_fname"];

 

Bytes ----------------------------------------------------------------------------------------------------------------------------

The function of dataset in ADO. NET is to provide a disconnected storage for the data source without having to worry about the data source. You can only perform operations in dataset.

Important objects in Dataset:

Tablescollection object: tables in dataset are represented by datatable. a dataset can contain multiple datatable tables, which constitute a tablescollection object. Each able contains a columnscolleciton and a rowscollection object.

Relationscollection object: the relationship between various datatables is expressed by datarelation. The set composed of these datarelation is the relationscollection object.

Extendedproperties object: This object is used to define specific information, such as passwords and update times.

 

1. datatable object

Create a datatable:

Datatable mytable;

Mytable = new datatable ("test ");

Mytable. casesensitive = false; // case sensitive

Mytable. minimumcapacity = 100; // minimum database record Space

 

Create a table column

Datatable mytable;

Datacolumn mycolumn;

Mytable = new datatable ("table name ");

Mycolumn = mytable. Columns. Add ("column name", typeof (string ));

Mycolumn = mytable. Columns. Add ("column name", typeof (INT ));

Create expression Column

Example

// Method 1

Datacolumn tax = new datacolumn ();

Tax. datatype = typeof (currency );

Tax. Expression = "Total * rate * 0.20 ";

// Method 2

Mytable. Columns. Add ("Tax", typeof (currency), "Total * rate * 0.20 ");

 

2. dataview object

Dataview provides an external mode for the database structure. At the same time, dataview can also provide the data binding function for Form Controls and Web controls. A dataview is built in each able to be datatable. defaultview ().

Create dataview:

Dataview sortedview = new dataview (datatable );

Sort dataview:

Datatable. defaultview. Sort = "lastname ";

Datatable. defaultview. Sort = "lastname, firstname DESC ";

Filter and sort dataview:

Use rowfilter attribute settings to filter data

Dataview DV = new dataview (Ds. Tables ["Authors"]);

DV. rowfilter = "state = 'CA '";

DV. Sort = "au_lname ";

 

3. datacolumn object

Example

Datacolumn colcustomerid = dtcustomers. Columns. Add ("customerid", typeof (int32 ));

Colcustomerid. allowdbnull = false;

Colcustomerid. Unique = true;

 

4. datarow object

Call the newrow method to create a new datarow object.

Create a datarow object

Datarow drnewemployee = dtemployees. newrow ();

// Use the index or column name to operate the new row

Drnewemployee (0) = 11;

Drnewemployee (1) = "Smith ";

// Call the add method to Add rows to datarowcollection.

Dtemployees. Rows. Add (drnewemployee );

Batch modify rows:

Beginedit () starts to change, endedit () ends the change, and writes the change result to dataset, canceledit (), cancel the change

For example:

Row. beginedit ();

Modify row

Row. endedit ();

 

Delete A datarow object from datatable:

I. Remove Method of datarowcollection object

Example

Datarow dremployee = dtemployees. Rows (3 );

Dtemployees. Rows. Remove (dremployee );

Ii. Delete method of datarow object

Example

Dremployee. Delete;

Comparison: When the remove method is used, datarow is deleted from datarowcollection, while the dalete method only marks the deleted row.

The datarow class includes the rowstate attribute. The rowstate attribute value indicates whether the row is changed, how it is changed, and how it is changed, starting from the first creation of the able (or loading the datatable from the database. Optional attribute value: Modified | detached | added.

 

5. Create a table relationship

Example

// Create datarelation

Datarelation Dr;

Datacolumn parentcol;

Datacolumn childcol;

 

Parentcol = Ds. Tables ["MERs"]. Columns ["customerid"];

Childcol = Ds. Tables ["orders"]. Columns. ["customerid"];

Dr = new datarelation ("custorders", parentcol, childcol );

DS. relations. Add (DR );

 

Currentparentrow = Ds. Tables ["MERs"]. Rows [maid. selectedindex];

Foreach (datarow R in currentparentrow. getchildrow ("custorders "))

{

Lable1.text + = R ["orderid"] + ",";

}

6. Bind data

Example 1

Gridview. datasource = Ds;

Gridview. datamember = "Authors ";

Gridview. databind (); example 2

Gridview. datasource = Ds. Tables ["Authors"];

Gridview. databind (); example 3

Dataview DV = new dataview (Ds. Tables ["Authors"]);

DV. rowfilter = "state = 'CA '";

Gridview. datasource = DV;

Gridview. databind ();

 

A data adapter object is an object that acts as a bridge between a DataSet object and an actual data source. A DataSet object is a non-connected object and has nothing to do with the data source. Dataadapter is responsible for filling it and submitting its data to a specific data source. It can be used with dataset to perform operations such as adding, querying, modifying, and deleting data.

The dataadapter object is a two-way channel used to read data from the data source to a memory table and write the data in the memory back to a data source. In both cases, the data sources used may be the same or different. These two operations are called fill and update respectively ).
Common attributes of dataadapter objects are shown in table 1.

Table 1 attributes of a sqldataadapter object

attributes	description
deletecommand	get or set a statement or stored procedure, to delete records from a dataset
insertcommand	get or set a statement or stored procedure, to insert a new record to the data source
selectcommand	get or set a statement or stored procedure, used to select records from the data source
updatebatchsize	obtain or set the number of rows processed during each round-trip to the server / TD>
updatecommand	get or set a statement or stored procedure, used to update records in the data source

Common Methods for dataadapter objects are shown in table 2.

Table 2 methods of sqldataadapter objects

method	description
dispose	Delete this object
Fill	Fill in data rows read from the source data to the DataSet object
fillschema	Add a able to the specified dataset, and configure the table mode
getfillparameters	returns an array composed of dataparameter objects for the SELECT command
Update	update the data source after the data in the DataSet object is changed.

For the fill and update Methods Commonly Used in dataadapter objects, the following uses sqldataadapter (bridge between dataset and SQL Server) as an example.

When the sqldataadapter object fills the DataSet object through the fill method, it creates required tables and columns for the returned data (if these tables and columns do not exist ). However, unless the missingschemaaction attribute is set to addwithkey, the primary key information is not included in the implicitly created architecture. You can also use the fillschema method to create the schema of the DataSet object for the sqldataadapter object and include the primary key information before filling it with data.