Ado. NET knowledge collation

1. Database connection

A) Access database (OLE DB connection)

Access2007 Connection

provider=microsoft.ace.oledb.12.0; Userid=admin;data source=[path].accdb; Jet oledb:databasepassword=;

Access2003 Connection

provider=microsoft.jet.oledb.4.0; Mode=readwrite; Persistsecurity info=false;data source=[path]provider=microsoft.jet.oledb.4.0;datasource=[path]; User id=admin; password=;

b) Sql-server Database

Datasource=.;i Nitial catalog=claa;integrated security=trueprovider=sqloledb;datasource=aron1;initial Catalog=pubs; User Id=sa; password=123;

Note: This provides only the OLE DB connection, and depending on the security level there are different ways to connect, as well as the ODBC connection, the connection string has a difference, specific to MSDN.

2. Common Classes

a) Connection connection Database (OleDbConnection, SqlConnection)

? The connection string is an option for a series of name/value pairs separated by a semicolon (;), and the order of these options is not important or case-sensitive.

Oledbconnectionconn = new OleDbConnection ("[Connection string]");

? Place the connection string in the config file for easy preservation and modification at any time

<connectionStrings>  <add name= "Northwind" connectionstring= "microsoft.jet.oledb.4.0;datasource=[Path] ; User id=admin; password=; " />   </connectionStrings>

Get the connection string:

Stringconnstr = configurationmanager.connectionstrings["Northwind"]. ConnectionString;

b) Command execution SQL statement (Oledbcommand,sqlcommand)

Property:

CommandText Gets or sets the SQL statement or stored procedure to execute

CommandTimeout setting the time-out period

CommandType command Type (text/storedprocedure/tabledirect)

Connection is generally null by default and is connected to the data source.

Parameters The arguments to an SQL statement or stored procedure are to use a placeholder in the SQL statement, and then to save the value to the ParameterCollection collection, or to the parameters to pass to the stored procedure when the stored procedure is executed, and the parameters to be received when the result is returned.

Method:

ExecuteNonQuery () executes the database by adding, deleting, and changing, returning the number of rows affected.

ExecuteScalar () Executes the query, returning the first row column.

ExecuteReader () executes the query and returns the DataReader object.

Instantiation mode:

Oledbcommandcmd = new OleDbCommand (SQL. ToString (), conn); oledbcommandcmd = conn. Createcommadn (); cmd.commandtext= SQL. ToString ();

c) DataReader read-only, forward-only result set, one data, connection maintained (OleDbDataReader)

d) datasets in the dataset in memory (without OLE DB and SQL)

Three ways to use datasets:

? Populating Data table data from data sources with DataAdapter Bridges

? Programmatically create DataTable, DataRelation, and Constraint in a dataset and populate tables with data.

? Use XML to load and persist dataset content.

Instantiate a dataset:

You can create an instance of a dataset by calling the DataSet constructor. You can optionally specify a name parameter. If no name is specified for the DataSet, the name is set to "NewDataSet".

Datasetcustomerorders = new DataSet ("CustomerOrders");

e) DataAdapter data Adapter (OleDbDataAdapter, SqlDataAdapter)

DataAdapter fills the dataset:

Sqldataadaptercustadapter = new SqlDataAdapter (  "select * FROM dbo. Customers ", customerconnection); oledbdataadapterordadapter = new OleDbDataAdapter (  " select * from Orders ", orderconnection);D atasetcustomerorders = new DataSet (); Custadapter.fill (CustomerOrders, "Customers"); o Rdadapter.fill (CustomerOrders, "Orders");D atarelationrelation = CustomerOrders.Relations.Add ("CustOrders", customerorders.tables["Customers"]. columns["CustomerID"], customerorders.tables["Orders"]. columns["CustomerID"]);

f) DataTable

DataTable fills a DataSet

Datasetcustomerorders = new DataSet ("CustomerOrders");D atatableorderstable = CustomerOrders.Tables.Add ("Orders");D Atacolumnpkorderid = OrdersTable.Columns.Add ("OrderID", typeof (Int32)); OrdersTable.Columns.Add ("Orderquantity", typeof (Int32)); OrdersTable.Columns.Add ("CompanyName", typeof (String));

orderstable.primarykey= New datacolumn[] {Pkorderid};

Note: Add a new DataTable object to the DataSet, and then add three DataColumn objects to the table. Finally, the code sets a column as the primary key column.

g) DataView Data View, you can find and filter the data in the data table, can be used to pass data to the control.

3. How to access the database

A) DataReader way

The SQL Statement Command command is executed in this manner, and the result is returned after execution.

b) DataAdapter + DataSet mode

The method is to populate the dataset with all the data returned in the database by DataAdapter the bridge.

4. Connection pooling

Although the connection takes a short time, it does take a while, and if you interact more clearly it will affect performance. The connection pool can be solved very well.

Connection pooling guarantees that a database connection is already open that is shared between sessions that use the same data source, which saves time for creating and destroying connections. When a customer requests an open connection with the open () method, the connection is provided directly by the connection pool instead of being created again. When the customer calls the Close () method or the Dispose () method to release the connection, it is not actually freed but is returned to the pool to wait for the next request. A connection pool is one such container: it holds a certain number of physical connections to the database server.

Connection pooling is distinguished by category. That is, the same time the same application domain can have multiple different types of connection pools. In detail, it is identified by the process, application domain, connection string, and the Windows identity (when using integrated security) to form a signature to identify the distinction. However, for the same application domain, it is generally only by the connection string to identify the distinction, that is, the connection string is different, cannot use the same connection pool, a program may have more than one connection pool, a connection pool can have more than one connection.

The behavior of the connection pool can be controlled by a connection string, consisting mainly of four important attributes:

? Connection Timeout: Connection request wait time-out. The default is 15 seconds, in seconds.

? Max Pool Size: The maximum number of connections in the connection pool. The default is 100.

? Min Pool Size: The minimum number of connections in the connection pool. The default is 0.

? Pooling: Whether connection pooling is enabled. Ado. NET is enabled for connection pooling by default, so you need to set Pooling=false manually to disable connection pooling.

SqlConnectionStringBuilder connstr = new SqlConnectionStringBuilder ();           Connstr.datasource = @ ". \SQLExpress";           Connstr.initialcatalog = "Master";           Connstr.integratedsecurity = true;           Connstr.pooling = true; Open Connection pool           connstr.minpoolsize = 0;//set minimum number of connections to 0           connstr.maxpoolsize = 50;//Set maximum number                       of connections Connstr.connecttimeout = 10; Set the time-out to 10 seconds           using (SqlConnection conn = new SqlConnection (connstr.connectionstring)) {...}

Basic principles for efficient use of connection pooling

? Request a connection at the latest time, and release the connection at the earliest.

? Closes the related user-defined transaction first when the connection is closed.

? Ensure and maintain at least one open connection in the connection pool.

? Try to avoid the creation of pool fragments. This includes pool fragmentation resulting from integrated security, and pool fragmentation that results from using many databases.

Tip: Pool Fragmentation is a common problem in many WEB applications, and applications may create a number of pools that will not be released until the process exits. In this way, a large number of connections are opened, consuming a lot of memory, resulting in degraded performance.

Abnormal conditions

Ado. NET requires a manual shutdown of the used connections. An important misconception is that connections are closed when the connection object is outside the local scope. In fact, when you go out of scope, you are releasing only the connection object and not the connection resource. So this time the connection to the pool is still open.

5. Preventing SQL injection attacks

There are many ways to prevent SQL injection attacks, input string length limits, control data validation, and detection of special characters. The better way to do this is to use parameterized commands or use stored procedures.

parameterized commands: a parameterized command is a command that uses placeholders in SQL text, and placeholders represent values that need to be dynamically replaced, which are passed through the Parameters collection of the Command object.

SELECT * from Customerswhere CustomerID = @CustIDcmd. Parameters.Add ("@CustID", Oledbtype.unsignedbigint). Value = link. ClassId, or CMD. Parameters.addwithvalue ("@CustID", link. CLASSID);

stored procedures: secure, easy to maintain, and improve performance.

    String ConnStr =webconfigurationmanager.connectionstrings["Northwind"].    ConnectionString;    SqlConnection conn = newsqlconnection (CONNSTR);    Call the stored procedure, you must specify Command.commandtype SqlCommand cmd = Newsqlcommand ("Insertemployee", conn);    Cmd.commandtype =system.data.commandtype.storedprocedure; Pass parameters to the stored procedure//need to specify exactly "data type" and "size of parameter" to match the details in the database//use the parameter's Value property for the assignment cmd.    Parameters.Add (Newsqlparameter ("@TitleOfCourtesy", sqldbtype.nvarchar,25)); Cmd. parameters["@TitleOfCourtesy"].    Value= Title; Cmd.    Parameters.Add (Newsqlparameter ("@LastName", SqlDbType.NVarChar, 20)); Cmd. parameters["@LastName"].    Value= LastName; Cmd.   Parameters.Add (Newsqlparameter ("@FirstName", SqlDbType.NVarChar, 10)); Cmd. parameters["@FirstName"].    Value = FirstName; The output parameter is also added in the same way as//But you must specify its Direction property as output cmd.   Parameters.Add (Newsqlparameter ("@EmployeeID", SqlDbType.Int, 4)); Cmd. parameters["@EmployeeID"].    Direction = ParameterDirection.Output; Execute database command using (conn) {Conn.        Open (); int RTV = cmd.        ExecuteNonQuery (); Gets the output parameter of the stored procedure int empID = (int) cmd. parameters["@EmployeeID"].    Value; }

6. Summary

There are many solutions to the mapping between the application and the database, often with ADO and ORM. The efficiency of the implementation of ADO is relatively high, but must write their own inside every detail. ORM (Object-Relational mapping objects Relation Mapping) is commonly used nhibernate, eliminating the details inside, for simple operation is also very convenient, but less efficient.

Although there are many types of methods in the ADO, you cannot and do not need to enumerate them, as long as you know the difference between the main classes and some features and classes, then check MSDN. At first I was also ready to understand this thing in detail, but found that really can not write much, many also look at others blog, write a good record down. To write a class library for this, it is not necessary to think carefully. So, when there is a new focus, I will slowly join.

As I write these are first in Word and then, copy into the blog, the format details will be down.

Ado. NET knowledge collation