Data Access Knowledge Framework

ado
Two namespaces, three classes.
One or two namespaces:
Using System.Data;
Using System.Data.SqlClient;

Two or three class:
SqlConnection SqlCommand SqlDataReader

(i) SqlConnection link class
1. Construction
The notation of the link string: server= server IP or server name; database= database name; uid= user name; pwd= password
Server=.; Database=mydb;uid=sa;pwd=123
Data Source=.;i Nitial Catalog=mydb; Persist Security info=true; User Id=sa; password=***********

SqlConnection conn = new SqlConnection ();
SqlConnection conn = new SqlConnection ("link string");

2. Properties
connectionstring:string, link string

3. Methods
Open ()
Close ()
CreateCommand ()

(ii) SqlCommand command class
1. Construction
SqlCommand cmd = new SqlCommand (["SQL statement", linked object, transaction object]);
SqlCommand cmd = conn. CreateCommand ();
2. Properties
Commandtext:string the name of the SQL statement or stored procedure to execute
Connection:sqlconnection specify which link to access the database
The parameters:sqlparametercollection type that specifies the value of the variable in the SQL text. AddWithValue ("name", "value")
The Commandtype:commandtype enumeration type, specifying whether the CommandText is an SQL statement (TEXT) or a stored procedure (StoredProcedure).
Transaction:sqltransaction type, the transaction object that is required to execute the command
3. Methods
ExecuteNonQuery ()
ExecuteReader ()
ExecuteScalar ()

(iii) SqlDataReader reader class
1. Construction
Note: You cannot directly new out. Only one way to build
SqlDataReader dr = cmd. ExecuteReader ();
2. Properties
Hasrows:bool, check if the data is detected.
3. Methods
Read (): bool, read data, read succeeded to true, otherwise false
4. Syntax
while (Dr. Read ())
{
dr[index number]
dr["column name"]
}


Iii. Business
(i) Intra-link transactions
1. Create a transaction object.
2. Attach to command
3. Committing or rolling back a transaction
(ii) Distributed transactions (cross-linked transactions)
1. Start the DTC service
2. Add System.Transactions Reference
3. Adding a using System.Transactions namespace
4. Use the TransactionScope class to manipulate the data.



1. Build DbConnection
Constant to make a link string
2. Create entity class
Encapsulates a database's table wrapper class, enclosing the table's fields as member variables and attributes
3. Creating data Access Classes
A. Declaration of three members: Sqlconnection,sqlcommand,sqldatareader
B. In the constructor, instantiate SqlConnection and SqlCommand.
C. Do a series of methods of adding, deleting, changing and checking.
4.Main function
(1).
A. Call the data access class to get the data.
B. Use for display on the interface.
(2).
A. The input data is obtained from the interface, and there are variables.
B. Call the data access class, the variable into the corresponding method of adding and deleting, to achieve the corresponding operation of the database.

Data Access Knowledge Framework