SQL Server Data Access

Two types of data access methods: 1. lightweight: SqlConnection, SqlCommand, SqlDataReader2. heavyweight: SqlConnection, SqlDataAdapter, DataTable five objects: link object, command object, reader object, adapter object, dataset (data table) object introduction: i. namespace: Operate the SQLServer database, I.

Two types of data access methods: 1. lightweight: SqlConnection, SqlCommand, SqlDataReader 2. important: SqlConnection, SqlDataAdapter, DataTable five objects: link object, command object, reader object, adapter object, dataset (data table) object Introduction: 1. namespace: Operating SQLServer database, 1

Two types of data access methods:
1. Lightweight: SqlConnection, SqlCommand, SqlDataReader
2. heavyweight: SqlConnection, SqlDataAdapter, DataTable

Five objects:
Link object, command object, reader object, adapter object, dataset (data table)

Object introduction:
I. namespace:
To operate an SQLServer database, two namespaces are generally used:
Using System. Data;
Using System. Data. SqlClient;

Ii. connection object (SqlServer): SqlConnection
(1) Construction:
SqlConnection ()
SqlConnection (connection string)

Connection string: more than one method.
First: handwriting "server =.; database = mydb; uid = sa; pwd = sa ";
Type 2: Use the server resource manager to generate a link object by adding a link and obtain the connection string by viewing the properties.

(2) attributes:
ConnectionString: string used to set or obtain the connection string of a link object.
State: ConnectionState Enumeration type. The current connection status is returned.

(3) method:
Open (): void, Open connection
Close (): void, Close the connection
CreateCommand (): SqlCommand to create a command object. (The advantage is that you do not need to set the Connection attribute for the command object separately)

(4) important code:
SqlConnection conn = new SqlConnection ("server =.; database = mydb; uid = sa; pwd = sa ");
Try
{
Conn. Open ();
....
}
Finally
{
Conn. Close ();
}

Ii. Command object (SqlServer): SqlCommand
(1) Construction:
SqlCommand ()
SqlCommand (SqlConnection, SqlString)
Conn. CreateCommand ()

(2) attributes:
CommandText: string, the SQL command to be executed-SQL statement, stored procedure name.
CommandType: CommandType Enumeration type, CommandType. Text-SQL-statement; CommandType. StoredProcedure-Stored Procedure
Connection: SqlConnection object. The connection object used to run the command.
Parameters: SqlParameterCollection. This attribute is used to assign values to variables when you use commandto query variables in CommandText.

(3) method:
ExecuteNonQuery (): int. Execute the SQL command to return the number of affected rows. It is generally used for adding, deleting, and modifying
ExecuteReader (): SqlDataReader. Execute the SQL command and return the data reader. Generally used for query
ExecuteScalar (): object. Execute the SQL command and return the first column of the First line. It is generally used to execute statistics (count (), sum (), avg (), max (), min ()) query.

(4) important code:
SqlConnection conn = new SqlConnection ("server =.; database = mydb; uid = sa; pwd = sa ");
// SqlCommand cmd = new SqlCommand ();
// Cmd. Connection = conn;
SqlCommand cmd = conn. CreateCommand (); // create a command object
// Set the command type
Cmd. CommandType = CommandType. Text; // cmd. CommandType = CommandType. StoredProcedure;
// Specify the SQL command
Cmd. CommandText = "insert into nation values (@ code, @ name );
// Add Command Parameters
Cmd. Parameters. AddWithValue ("@ code", "n005 ");
Cmd. Parameters. AddWithValue ("@ name", "Zhuang ");
// SqlParameter paramCode = new SqlParameter ("@ code", "n005 ");
// Cmd. Parameters. Add (paramCode );
// SqlParameter paramName = new SqlParameter ("@ name", "Zhuang ");
// Cmd. Parameters. Add (paramName );

Try
{
Conn. Open ();
Cmd. ExecuteNonQuery ();
}
Finally
{
Conn. Close ();
}

Note:
1. The stored procedure should be called (for example)
2. Logon: use statistical queries and ExecuteScalar () to determine whether the logon is successful?
3. SQL injection attacks, principles and countermeasures (replace single quotes and use SQL local variables ).
4. query, add, delete, modify, and statistical query.

3. Reader object (SqlServer): SqlDataReader
Read-only, only reading objects from forward data.
It is not a result set, and its memory only occupies one data space. Each time you read the next entry, the content of the current entry in the memory is washed out.

(1) Construction:
Cannot be new directly. The constructor is non-public.
Only the unique generation method is: cmd. ExecuteReader ([CommandBehavior. CloseConnection]);

(2) attributes:
HasRows: bool, whether data is found in the reader

(3) method:
Read (): Read the next data to the memory.
Close (): Close the reader. Generally, the link is not closed. However, if the CommandBehavior enumeration parameter is included in the ExecuteReader () method of the Command object when the reader generates the Command object, the connection may also be closed.

(4) important code:

Note:
1. logon judgment.
The SqlDataReader is returned for the query. The HasRows attribute is used to determine whether the log is found and whether the logon is successful.
SqlDataReader dr = cmd. ExecuteReader ();
If (dr. HasRows)
{
}
2. query multiple rows of data to demonstrate multi-row display
SqlDataReader dr = cmd. ExecuteReader ();
While (dr. Read ())
{
Console. writeLine (dr ["column name"]. toString () + (bool) dr [1]); // dr [two common methods]; dr [0], dr ["column name"] returns all objects
}

Iv. DataTable/DataSet
DataSet is equivalent to a database in memory, including multiple able.
DataTable is equivalent to a data table in memory. It includes two most important attributes: Rows (DataRowCollection) and Columns (DataColumnCollection ). A table shard represents the table's row set and column set.
(1) manually create a DataTable
// Create a table
DataTable table = new DataTable ();
// Create columns
DataColumn col1 = new DataColumn ("column name", "type", "length ");
DataColumn col2 = new DataColumn ("column name", "type", "length ");
DataColumn col3 = new DataColumn ("column name", "type", "length ");
Table. Columns. Add (col1 );
Table. Columns. Add (col2 );
Table. Columns. Add (col3 );
// Create a line
DataRow row1 = table. NewRow (); // generate a line Object
Row1 [0] = "";
Row1 ["column name"] = "";
Row1 [2] = "";
Table. Rows. Add (row1 );
DataRow row2 = table. NewRow (); // generate a line Object
Row2 [0] = "";
Row2 ["column name"] = "";
Row2 [2] = "";
Table. Rows. Add (row2 );
DataRow row3 = table. NewRow (); // generate a line Object
Row3 [0] = "";
Row3 ["column name"] = "";
Row3 [2] = "";
Table. Rows. Add (row3 );

// Assume that data has been found in the table. How can I obtain a certain data?
For example, if you find the data in a table.
1. I want to operate 3rd rows and 5th columns of data. (Assume the column number and row number I start from 1)
String s = table. Rows [2] [4]. ToString (); // Value
Table. Rows [2] [4] = "hello world"; // value assignment

2. I want to operate on all the data
For (int I = 0; I
{
For (int j = 0; j
{
Console. Write (table. Rows [I] [j]. ToString ());
}
}

3. Control binding:
Xxx. DataSource = table;

(2) Use a visual interface to create
Screenshot description.

V. data adapter object: SqlDataAdapter
The compliance object contains four SqlCommand attributes:
SelectCommand
InsertCommand
DeleteCommand
UpdateCommand

Two methods:
Fill (DataTable/DataSet)
Update (DataTable/DataSet)

(1) handwriting code operations (easy to understand)

(2) visualized interface operations. (Screenshot display)

* ** Transactions and exception handling are neglected by most developers.
What is a transaction? Examples.
Four features of transactions: (A -- atomicity, C -- consistency, I -- Isolation, D -- persistence)

Two types of transactions:
1. Connected Transactions
The transaction that is added during database operations before the connection is closed.
SqlTransaction object
Using System. Transaction;
1. Create a transaction object:
Conn. Open ();
SqlTransaction trans = conn. BeginTransaction ();

2. Mount the transaction to the command object
Cmd. Transaction = trans;

3. Execute Command object.
Cmd. CommandText = "";
Cmd. ExecuteNoneQuery ();
.....

4. Submit the transaction
Trans. Commit ();

5. roll back the transaction: Put it in catch
Trans. Rollback ();

Add code, for example, screenshot description. Successful, unsuccessful

Ii. distributed transactions
Set transactions outside of connection opening and closing. All operations can be controlled.
1. Enable the DTC service
2. import System. Transaction.
3. Use using System. Transaction to import the namespace
4. Write the transaction code.

TransactionScope class

Using (TransactionScope ts = new TransactionScope ())
{
Database Operations.

Ts. Complete ();
}

Add code, for example, screenshot description. Successful, unsuccessful

Exception Handling: try... [catch...] [finally...]
1. Capture exceptions
If you do not add try... catch..., the program will be immediately terminated once an error occurs. If you add try... catch..., it indicates that the exception has been handled in the program. The program only terminates the operation of the try part and transfers the exception to catch. The whole program does not terminate the operation.

1. try part: the execution part. If an error occurs, the execution of try part is terminated and the catch is used. The program will not be terminated.
2. catch part: Exception Handling. If an error occurs, the catch part is used to process the catch part (log writing, interface display, and exception packaging replacement)
If catch (Exception ex ). Ex. Message is commonly used to obtain error information.
A try can be followed by multiple catch, and each catch gets different types of exception objects.

3. finally: The final part, regardless of whether the program is executed incorrectly or not, always runs finally. Even if the return, break, and other keywords are encountered in the try part, finally is still executed.

Ii. Throw an exception
(1) exception object creation:
Exception ex = new Exception ("Exception error message ");
Throw ex;

(2) derive your own exceptions: (fewer)
Class MyException: Exception
{
}

Other data access

(1) Construction:

(2) attributes:
(3) method:
(4) important code: