Rupeng Net learning notes (6) ADO. Net basics, learning notes ado.net

Rupeng Net learning notes (6) ADO. Net basics, learning notes ado.net

ADO. Net Basics

1. Introduction to ADO. Net
1. To use SQL statements to automate database operations, you must use a class library,
The class library must provide methods similar to execute ("insert into...")/executeQuery ("select * from ...")

2. ADO. Net is the standard database access interface provided by. Net. The underlying methods for accessing different DBMS are different. ADO. Net unifies the methods for accessing the database,
The methods for accessing different databases such as MYSql, Oracle, and SqlServer are almost the same.

3. ADO. Net is a specification and is implemented by ADO. Net provided by different database manufacturers. It is called ADO. Net driver. The driver provided by each vendor can be used to operate its own database.

Ii. Connecting to MYSQL using ADO. Net

1. Install MySql's. Net driver mysql-connector-net-***. msi and add it to the project library.
If the installation fails, mysqlnetconnection(v4.52.16.zip.

2. Create a project, add reference -- "extension", and add Mysql. Data. if the version is decompressed directly, add reference to the MySql. Data. dll file directly.
Static void Main (string [] args)
{
String connStr = ConfigurationManager. ConnectionStrings ["connStr"]. ToString ();
Using (SqlConnection conn = new SqlConnection (connStr ))
Using (SqlCommand cmd = conn. CreateCommand ())
{
Conn. Open ();
Cmd. CommandText = "insert into t_fuxi ";
Int count = cmd. ExecuteNonQuery ();
Console. WriteLine (count + "affected ");
Console. ReadKey ();
}
}

Code explanation:
1. MySQLConnection and MySQLCommend implement the IDisposable interface. Therefore, using is used to recycle resources.

2, "Server = localhost; Database = study1; uid = root; pwd = root; Charset = utf8" is called a connection string,
Server is the address of the Mysql Server, Database is the connected Database, uid, pwd is the user name and password, utf8 Encoding

3, conn. Open (); you must Open the database connection before executing MySqlCommand; otherwise, an error is reported.

4. ExecuteNoQuery is used to execute non-query statements such as Update, Insert, and Delete. The returned value is the number of affected rows.


3. ExecuteScalar
Execute the query and return the first column of the First row in the returned result set. ignore other columns. It is generally used to obtain the query results of a free row and a column.

Case 1
Cmd. CommandText = "Select count (*) from T_Users ";
Long count = (long) cmd. ExecuteScalar ();

Case 2
Cmd. CommandText = "Select Password from T_Users where UserName = 'admin '";
String pwd = (string) cmd. ExecuteScalar ();
If (string. isNullOrEmpty (pwd ))
{
Console. WriteLine ("admin not found ");
}
Else
{
Console. WriteLine ("admin Password:" + pwd );
}

4. Execute the query MySqlDataReader.

Cmd. CommandText = "select * from T_Users ";
Using (MySqlDataReader reader = cmd. ExecuteReader ())
{
While (reader. Read ())
{
Long id = reader. GetInt64 ("Id ");
String userName = reader. GetString ("UserName ");
String passWord = reader. GetString ("Password ");
Console. WriteLine ("id =" + id + "; UserName =" + userName + "; Password =" + passWord );
}
}

Note:
Connection is required for Reader traversal and reading. If the Connection is disabled, an error occurs.
You can also obtain the column value based on the column number, which is slightly more efficient, but the program cannot easily read it. You can use reeder. GetOrdinal ("Age") to obtain the serial number of the column name.



Iii. SQL Injection Vulnerability

1. This is due to the splicing of SQL statements,
When receiving user input, the program must consider the impact of user input on database operations,

2. corresponding solution: parameterized Query

Cmd. CommandText = "select count (*) from TUsers where username = @ username and password = @ password ";
Cmd. Parameters. Add (new SqlParameter () {ParameterName = "@ UserName", Value = username });
Cmd. Parameters. Add (new SqlParameter () {ParameterName = "@ Password", Value = password });

Why does this avoid "SQL Injection Vulnerabilities ",
Therefore, you can use parameterized queries to determine and process user input. In essence, parameter values are assigned.

Note:
1. All SQL statements can be passed through parameterized queries. Table names and field names cannot be replaced by parameterization.
2. Do not use the SqlParameter (string parameterName, object value) constructor,
Because ("Age", 0) will be matched to Sqlparameter (string parameterName, SqlDbType dbType) This Constructor

Iv. null Basic Data Type

1. Change the Name and Age columns of the T_Persons table to "null allowed" and insert a row with the Name and Age empty.
After execution, it is found that SqlNullValueException is thrown for the reader. Get ** method of empty data,
It is equivalent to asking the database what the "Name of the current row" is, and the database tells you "don't know ".

How can this problem be solved?
Use ISDBNull to obtain whether the column value of the specified sequence number is null

Int? Age = null;
If (! Reader. IsDBNull (reader. GetOrdinal ("Age ")))
{
Age = reader. GetInt32 ("Age ");
}

V. DataSet

DATAReader is the embodiment of the server result set cursor. All the queried data is on the MySql server.
The advantage is: avoid occupying local memory when the data volume of the query results hits.

However, large query results are avoided in most projects, so the disadvantages are obvious.
The Connection must be maintained during reading, which is not only difficult to use, but also occupies the Connection resources of the MySql server for a long time.

DataSet is an offline result set container that stores the result data in the local memory.
Because the query results may contain multiple Tables, DataSet contains several able (ds. Tables ),
DataTable contains several DataRow (dt. Rows)

Usage 1:
DataSet ds = new DataSet ();
MySqlDataAdapter adapter = new MySqlDataAdapter (cmd );
Adapter. Fill (ds );
DataTable table = ds. Tables [0];

6. DataTable
DataSet can store multiple query result sets to DataTable.

DataAdapter also allows you to update, delete, and modify the results.

Generally, the query result set is a DataTable, And the foolish update of DataAdapter is not suitable for formal projects, so it is simpler to use.

DataTable dt = new DataTable ();
Dt. Load (reader );

Note: declare DataTable outside using and use query results outside using.

Traverse DataTable

For (int I = 0; I <dt. Rows. Count; I ++)
{
DataRow row = dt. Rows [I];
String name = row. IsNull ("name ")? Null :( string) row ["Name"]; // NULL Processing
Console. WriteLine ("name =" + name );
}

7. encapsulate a database: Analysis

1. Write the connection string of ADO. Net to the configuration file.

2. Every time you operate the database, you have to write a bunch of code, which is too tired. Therefore, encapsulate a library that simplifies ADO. Net operations:
Set connection strings in the configuration file, simplify connection creation, and simplify SQL Execution

3. If an operation needs to execute multiple SQL statements, the efficiency will be very low and there will be a "transaction" problem if a connection is opened, executed, and the connection is closed.
Therefore, you should provide methods such as "Open, execute, and close", and also "methods for executing existing connections after use ".

4. The number of query parameters for parametric query is unknown, and variable length parameters are more convenient.

5. To facilitate small result sets in most cases, execute the query to return the DataTable


VIII. MySqlHelper method Planning

1, public static MySqlConnection CreateConnection ()

2, public static int ExecuteNonQuery (MySqlConnection conn, string SQL, params MySqlParameter [] parameters)

3, public static int ExecuteNonQuery (string SQL, params MySqlParameter [] parameters)

4, public static object ExecuteScalar (MySqlConnection conn, string SQL, params MySqlParameter [] parameters)

5, public static object ExecuteScalar (string SQL, params MySqlParameter [] parameters)

6, public static DataTable ExecuteQuery (MySqlConnection conn, string SQL, params MySqlParameter [] parameters)

7. public static DataTable ExecuteQuery (string SQL, params MySqlParameter [] parameters)

9. Implement MySqlHelper

1. The connection string is generally configured in the <connectionStrings> section of App. config (the website is Web. config ).
Then use the ConfigurationManager class (you need to add a reference to System. Configuration) to read
String connStr = ConfigurationManager. ConnectionStrings ["connStr"]. ConnectionString
　　　　

Note:
Make sure that the name in the Code is the same as that in the configuration file.

2. Implementation of other methods

3. test several methods. You do not need to control the connection by yourself, and then call the Public connection


10. Obtain the value of the automatically increasing field

1. The method for getting the maximum value after insertion cannot be used, and there is a concurrency problem.

2. In the same connection: select LAST_INSERT_ID ()

3. Insert and LAST_INSERT_ID () can be executed separately in the same connection, or LAST_INSERT_ID () can be placed after the insert statement;
Split (use ExecuteScalar to execute)

XI. Transaction Basics

1. There is a demand, similar to a transfer, which transfers 10 yuan from Tom's salary and 10 yuan to Jerry's salary.

Update T_Employees Set Salary = Salary-10 where Name = 'Tom'
Update T_Employees Set Salary = Salary + 10 where Name = 'Jerry'

If an error occurs when you add 10 yuan to the Jerry account (use SQL syntax to write an error simulation), the overall loss of 10 yuan will occur. What if it is a transfer?

2. transactions have four features:
Atomicity, consistency, isolation, and Durability
Atomicity means that several operations either succeed or fail.

12. ADO. Net transactions

1. it must be in a connection (otherwise it will involve distributed transactions)
MySqlTransaction tx = conn. BeginTransaction ();
Execute tx. Commit () to submit the transaction;
If an error occurs, tx. Rollback () is rolled back (all operations of the current transaction are canceled)

Sample Code:
MySqlTransaction tx = conn. BeginTransaction ();
Try
{
MySqlHelper. ExecuteNonQuery (conn, "Update T_Accounts set Amount = Amount-1000 where Number = '123 '");
String s = null;
S. ToLower (); // manufacturing exception
MySqlHelper. ExecuteNonQuery (conn, "Update t_accounts Set Amount = Amount + 1000 where Number = '000000 '");
Tx. Commit ();
}
Catch (Exception ex)
{
Tx. Rollback ();
}

There are also isolation levels and nested transactions in transactions.

13. Use of SQLServer

1. Installation. Version: 2008 contains compatibility issues and bugs. Therefore, it is recommended to install SQLServer 2008 R2.

2. Use of Management Studio

Two Connection Methods of SQLServer:
Windows authentication (in a trusted LAN );
SQL Server Authentication (using SQL Server user name and password verification requires a more complex password ).
Windows authentication has another purpose: Forget the sa password, you can go to the local machine to change it.

3. Create a database and a new table
SQLServer Data Type
(Varchar and nvarchar; nvarchar (n) and nvarchar (MAX );
Long is bigint;
Obtain the first 10 data records: select top 10 * from t_persons ),
Automatic growth (ID) of SQLServer and no special encoding required

4. If the error "Do Not Allow save changes" is returned when saving table design changes ":
Tools → options → Designers → select the "higher than the requirement for re-creating a table for blocking SAVE.
Error message: Read it carefully.

5. Execute the SQL statement (right-click "new query" on the database, and do not click "debug ])

14. Connecting to SQLServer using ADO. Net
1. How does ADO. Net Connect to SQLServer:
SQLServer driver built-in

2. connection string:
Server = ip; user id = sa; passWord = passWord; database = db1

3, SQLHelper:
Replace MySql with SQL.

4. Get the value of the auto-increment column:
Insert into t1 (...) output insert. Id values (....)

5. If you program based on the interface, you only need to change CreateConnection. The query parameters are transmitted using Directory <string, object>.
If you use the Provider, you do not need to change the code. Just change the configuration file.