What ADO wants to know.

What is ADO?

Ado. NET is a set of class libraries, which allows us to access the database through the program, just like System.IO class operation file, System.Data. This group of classes is used to manipulate the database (not just the MSSQL Server), It provides a unified programming interface that allows operation of other databases (Access, Oracle, etc.) in a manner consistent with the operation of MSSQL server.

ADO Purpose : Access the database through a program.

ADO consists of:

data Providers ( common classes):

Connection, used to connect to the database;

Command to execute the SQL statement;

DataReader read-only, forward-only result set, one read data (StreamReader, XmlReader Microsoft's class library these readers use the same way);

DataAdapter, an object that encapsulates the above 3 objects, a dataset (DataSet), a staging database, and a disconnected data operation.

Other common classes:

connectionstringbuilder//automatic generation of connection strings;

parameter//SQL statements with parameters;

transaction//the use of transactions in ADO;

Class associated with the dataset:

dataview//view class, data in a DataTable is viewed in a different perspective

The rows in the Datarowview//dataview.

Data tables in DataTable//dataset

Rows in the Datarow//datatable

Columns in the Datacolumn//datatable

The relationship between Datarealation//datatable and DataTable

Constraints established in the Constraint//datatable

How to access data by ADO:

(1)

1. Connecting Data with Connection

2. Execute SQL statement command

3. After execution, the result is returned in one article. DataReader

(2)

Using Dataadapter+dataset, this method essentially takes the data out by connection, Command, and DataReader and puts it in the dataset. Look at the DataAdapter constructor.

Connection

How do I make the application connect to the database?

The Connection object. Connection is like reading database data before you create a path to read SQL Server database use.

Steps to connect data:

• Create a SqlConnection object
• Get connection string
  • VS View-Server Explorer-database connection point right-add connection in new database point right-click Property with connection string
  • Using SqlConnectionStringBuilder to help get a connection string
  • Use the Selectedobject property of the PropertyGrid control with the SqlConnectionStringBuilder.
• Open the connection. (You can only open it once and close it multiple times.) Available when testing is open: ConnectionState enumeration)
• Close connection//equivalent to set up a roadblock
• Release the resources//equivalent to take the road demolished, this piece of land can be built.
  • Call Connection.dispose () "Inherit from method of Component Class" method, internally called Close ()
  • Connection cannot be opened repeatedly.

　　Connection pool: Innerconnection. Open connection with connection pool: Data source=.\\sqlexpress;initial catalog=myschool;integratedsecurity=true;

Ado. NET creates a connection to SQL Server through the SqlConnection class, SqlConnection represents a database connection, ADO. NET, such as the connection of the IDisposable interface, you can use using for resource management.

SqlConnection always keep it open in the program, OK?

For a database, connectivity is a valuable resource and must be used up close, Dispose. "Close can then be placed in the pool, and other links can be used again." 】

Command

The operations SQL Server database uses the SqlCommand object, SqlCommand represents a command that is submitted to the server (SQL statement, and so on), and the CommandText property is the SQL statement to execute.

To create a SqlCommand object:

1. Create with the New keyword

2. Created using the Idbconnection.createcommand () method (polymorphic) When writing common code

Three common methods:

ExecuteNonQuery () performs additions and deletions to the database, returns the number of rows affected, suitable for: INSERT, DELETE, update (returns 1 for other statements)

ExecuteScalar () executes the query and returns to the first row column

ExecuteReader () Executes the query, returning the DataReader object

Statementcompleted Event:

Fires after each SQL statement is executed. Multiple statements are executed at the same time (separated by semicolons), how can I get the number of rows affected by each statement? The actual return value is the number of rows affected by each statement.

ExecuteScalar

The SqlCommand ExecuteScalar method is used to execute the query and return the first column of the first row in the result set returned by the query, because the type of the return value cannot be determined, so the return value is the object type .

　　get the primary key value for the autogrow field , plus the output inserted before the values keyword. ID, where ID is the primary key field name. Executes the result to try to insert the primary key value, uses the ExecuteScalar to perform most conveniently. (The output statement uses inserted, deleted two temporary tables)

Cmd.commandtext = "INSERT into class (cname,cdescription) output inserted.classid values (' High 31 class ', ' description ')"; int i = Convert.ToInt32 (cmd. ExecuteScalar ());

The original wording:

Cmd.commandtext = "INSERT into Class (Cname,cdescription) VALUES (' High 31 class ', ' description '); select @ @identity"; int i = Convert.ToInt32 (cmd . ExecuteScalar ()); @ @IDENTITY can return the last identity value generated in all tables in the current session.

Execute Query

Executes a executereader with multiple rows of result sets.

SqlDataReader reader = cmd. ExecuteReader ();..

while (reader. Read ()) {Console.WriteLine (reader. GetString (1));}

Reader's strong-type GetString (), GetInt32, GetFloat (), getdouble () and other methods only accept integer parameters, that is, the sequence number, with the GetOrdinal method according to the column name dynamic get serial number, more simple method reader[' Uusername '].

Use reader to read column data instead of column names based on the column index (the column name is also converted to an index);//generally it is recommended to use an index to get column information if there is no special case, and do not use column names (less efficient). The better way to get data based on column names is: int c1=reader. GetOrdinal ("Lie1"); object OBJ1=READER[C1]; Reader. Getdatatypename ()//The data type of the column.

Why use using?

Close: Can be opened after closing. Dispose: Destroyed directly and cannot be used again. The using of Dispose, which calls Dispose,sqlconnection, SqlDataReader, and so on after the scope has been scoped, makes the judgment that there is no close, and if not, close then dispose.

DataReader Hasrow Implementation://Judge if close, throw an exception directly.

Public override boolhasrows {get {if} (this. IsClosed) {throw ADP. Datareaderclosed ("HasRows"); } return this._hasrows; } }

Note: DataReader must have a single connection. (Unless the Allow Mars, multi-active result set is set, in the connection string).

SqlDataReader Precautions for use

Where is the data after returning to reader? Database Server Cache

When using DataReader, the connection must be guaranteed to be open;

Reader is read-only (cannot modify data through reader.) ), only into the reader each time read one to release a piece so can only forward can not back;

Because of the limited function, the reading speed is very fast, which is suitable for reading large amount of data from database;

The Hasrow property returns whether there are rows. IsDbNull () Determines whether the data is null;

The type in the database is not the same as in C #, and the float in the database has to be obtained by C # getdouble () ;

If more than one result set is returned, use the Nexresult () method. Pass the Do-while cycle test. For strongly typed conversions, you need to use IsDBNull () to determine whether the obtained data is empty.

Reader is required to get output parameters by executing the ExecuteReader () method. Close () to be available later.

ADO Connection Pool

Because each normal connection database performs at least 3 operations (1. Log on to database server 2. Perform operation 3. Log off the user), so it is time consuming to request a connection to the database server through connection. "Connection pooling is enabled by default in ADO."

How do I empty the connection pool? Connection static method Clearallpools (), Clearpool ().

Under what circumstances do I need to disable connection pooling?

are generally not used. In particular, programs such as ASP. N are frequently accessed by multiple users, but most users access the same connection string. However, if an application has more than one client, each client accesses its own connection string, and if a connection pool is used, each time the connection is opened is faster, but because of a "pool" problem, multiple open connection objects are also saved.

Note: most common connections in a project are only a few.

ADO Connection Pooling Usage Summary:

1. Opening a connection for the first time creates a connection object.

2. When the connection is closed (when the close () method is called) The current connection object is placed in the pool.

3. Next Connection object, if the connection string is exactly (case sensitive) to the connection string of an existing connection object in the pool, the existing connection in the pool is used instead of recreating one.

4. Only the object is placed in the pool when it is called close (), and if a connection object is always in use, the next time a connection object is created and a new connection object is not found in the pool, it will be created again. The connection object in the pool is automatically destroyed if it has not been accessed for some time.

Query Parameters

The SQL statement uses @parameterrname to "replace with arguments here" to add parameters to SqlCommand's parameters. The parameter is not a simple string substitution inside SQL Server, and SQL Server directly compares the data with the added value, so there is no injection vulnerability attack. (A stored procedure is called inside the SQL statement with parameters.)

A problem with parameters (small bug):

SqlParameter p1=new SqlParameter ("@age", 0);//Only 0 unexpected problems occur. Another overload is called when it is 0: SqlParameter (string Parametername,sqldbtype dbType), and object before 0, where overloaded objects can be applied.

DataSet (ADO break-down data access)

What is a dataset?

A collection of data, a staging database, and an in-memory database. (b/S program and C/s program to the dataset of different processing methods).

Comparison of datasets and SqlDataReader:

SqlDataReader is connection related, the query result in SqlDataReader is not put in the program, but placed in the database server, SqlDataReader just equivalent to put a pointer (cursor), can only read the current cursor point to the row, Once the connection is broken, it cannot be read again. The advantage of this is that no matter how many query results there are, there is little impact on the memory that the program occupies.

SqlDataReader for Speed, read only, forward only, limited function. Ado. NET provides a mechanism for the dataset to populate the query results into local memory so that the connection is broken and the server disconnects without affecting the reading of the data.

　　Datasets Pass data between multi-tiered applications. (now mostly with list<t>)

Statement: DataSet DataSet = new DataSet (); SqlDataAdapter adapter = new SqlDataAdapter (cmd); Adapter. Fill (DataSet);

SqlDataAdapter is a bridge between a dataset and a database. The dataset DataSet contains several tables datatable,datatable contains several rows of DataRow. foreach (DataRow row in DataSet. Tables[0]. Rows) row["Name"].

You can use different SqlDataAdapter to update the DataTable, and if you use SqlCommandBuilder automatically generated command objects, you must provide SelectCommand, but you can not fill them.

1. What is a dataset?

Data collection, staging database, memory database. But the dataset is populated by DataReader.

2. Through DataAdapter filling Dataset,fill () can also be paged (not efficient, will be on the server side of the data query out, and then use the DataReader skip the previous data, only the data from the back, The real paging should be to query only the data of the current page in the database. ）

3. Disconnect data access and call the update () method when the operation is complete.

What type of data can datasource bind to?

IList interface, including one-dimensional arrays. IListSource interfaces, for example, DataTable and DataSet classes. IBindingList interface, for example, BindingList (T) class. IBindingListView interface, for example, the BindingSource class.

Note the point:

1. Populate the table in the dataset with the Fill method of the DataAdapter.

2. When you create a DataAdapter, you simply specify the connection string and query statement, and the SelectCommand is generated automatically.

3. Automatically create InsertCommand, DeleteCommand, UpdateCommand through SqlCommandBuilder, and then call adapter's update () method to Data in the DataTable is updated into the database.

3.1 When creating a command object through SqlCommandBuilder, you must ensure that the query for the primary key is included in the SELECT statement when the adapter is created

4. You can also specify the command yourself, and our operation on the DataTable is marked only for the RowState state that is not in the Rows collection, and does not actually delete the row from the Rows collection.

Multi-Conditional search when where 1=1

When stitching multiple where conditions, it is sometimes not known whether the conditions in front of the and|or exist. Tend to be followed by a where 1=1, but this method is not efficient. If this is the case, a full table scan will be performed in the database (each row of data is scanned for comparison. ), you will not be able to use policies that optimize queries such as indexes. And the index will be invalidated.

Workaround: List+string.join

ADO the stored procedures in

Create a thing from a connection object

SqlTransaction Tran=con. BeginTransaction ();

Tran.commit ();//Commit a transaction

Tran.rollback ();//ROLLBACK TRANSACTION