such as Peng Web study notes (vi) ADO Foundation

Ado. NET Foundation

First, ADO. NET Introduction
1, the program to the SQL statement to automate the operation of the database, you must use a class library,
Class Library to provide a similar approach to execute ("insert INTO ...")/executequery ("Select * From ...")

2,ado.net is the standard Access database interface provided in. NET, the underlying approach to accessing different DBMS is not the same, and ADO unifies the method of accessing the database.
The approach to accessing different databases, such as MySQL, Oracle, and SQL Server, is almost the same

3,ado.net is a specification, by different database vendors to provide the implementation of ADO, known as the ADO-driven, each vendor provides a driver can be used to operate their own database

Second, ADO. NET connection MySQL

1, install the MySQL. NET driver Mysql-connector-net-***.msi added to the project's library.
If the installation encounters a problem, download mysqlnetconnection (V4.5). zip directly.

2, new project, add reference--"extension", add mysql.data, if it is a direct decompression version, and then add a 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, Mysqlcommend implements IDisposable interface, so use using for resource reclamation

2 , "Server=localhost;database=study1;uid=root;pwd=root; Charset=utf8 "is called the connection string, the
server is the address of the MySQL server, database is the connected databases, UID, PWD is the user name and password, using UTF8 encoding

3,conn. Open the database connection before executing Mysqlcommand, or you will get an error

4,executenoquery is a non-query statement that performs update, Insert, delete, and the return value is the number of rows affected


3,executescalar
Executes the query. and returns the first column of the first row in the result set returned by the query, ignoring the other rows. Typically used to simply get a free row of values for a column of query results

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 ("Password for admin:" +pwd);
}

4, execute 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:
Reader traversal, read the need to connection remain connected, if the connection is turned off, using error
can also get the column value according to the column ordinal, efficiency Slightly higher, but the program is not easy to read; through Reeder. GetOrdinal ("Age") gets the sequence number corresponding to the column name



Third, SQL injection vulnerability

1, because of the concatenation of SQL statements,
when the program receives input from the user, it is necessary to consider the impact of user input on database operations,

2, the corresponding workaround, parameterized query

cmd. CommandText = "SELECT count (*) from tusers where [email protected] and [email protected]";
Cmd. Parameters.Add (new SqlParameter () {parametername = "@UserName", Value = UserName});
Cmd. Parameters.Add (new SqlParameter () {parametername = "@Password", Value = Password});

Why this avoids "SQL injection vulnerability",
so using parameterized queries, the user input can be judged and processed, essentially parameter assignment

Note:
1, all SQL can use parameterized query delivery; Name, field name, and so on cannot be replaced with parameterization
2, do not use SqlParameter (string parametername,object value) This constructor,
because ("age", 0) will be matched to SqlParameter (String Parametername,sqldbtype DbType) This constructor

Four, the basic data type is empty question

1, modify the name and age column of the t_persons table to "Allow null", insert a name, an age-empty line
After execution found, for empty data reader. The get** method throws an exception sqlnullvalueexception,
The equivalent of asking the database "name of the current row" is what the database tells you "do not know".

How to solve?
Use IsDBNull to get the value of the column with the specified ordinal is null

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

Five, offline result set dataset

DataReader is the manifestation of the server result set cursor, and all the queried data is on the MySQL server.
The advantage is that you avoid using local memory when the query results data is hit.

However, most of the projects will avoid large query results, so the shortcomings are obvious
Must be kept connection when reading, not only trouble to use, but also take up the connection resources of MySQL server for a long time.

The

DataSet is an offline result set container that places the result data in local memory.
Because the query results may contain more than one table, the dataset contains several DataTable (Ds.tables), and the
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];

VI. DataTable
DataSet can hold multiple query result sets to DataTable

DataAdapter can also be used to fool the update, delete, modify the results.　　

We generally query the result set for a datatable,dataadapter update that is not suitable for a formal project, so there is a simpler usage of the

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

Note: Use the query result

to traverse the DataTable

for (int i =0;i<dt) by declaring the DataTable to a using outside of using. rows.count;i++)
{
DataRow row = dt. Rows[i];
String name = row.　　IsNull ("name")? Null: (String) row["name"];//null processing
Console.WriteLine ("Name=" +name);


VII encapsulates a library: parsing

The connection string for the 1,ado.net is written to the configuration file.

2, each operation of the database to write a piece of code, too tired, so encapsulate a simplified ADO operation of the library come out:
Setting the connection string for a configuration file; simplifying the creation of a connection; simplifying the execution of SQL

3, if an operation to execute multiple SQL statements, if each open a connection-execution-Close the connection, the efficiency will be very low, and there will be a "transaction" problem.
Therefore, you should provide methods such as "Open, execute, close," as well as "methods to execute existing connections after use."

4, parametric query of query parameter number is indeterminate, variable length parameter will be more convenient

5, in order to facilitate the small result set 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)

IX. realization of Mysqlhelper

1, the connection string is typically configured in the <connectionStrings> section of the App. Config (Web site 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
　　　　

Attention:
Make sure that the name in the code is consistent with the name in the configuration file

2, the implementation of other methods

3, test several methods, call do not need to control the connection, and then call the public a connected


Ten. Get the value of the autogrow field

1, can not be used after inserting the maximum value of the method, there are concurrency problems

2, to be in the same connection: select LAST_INSERT_ID ()

3, can insert, last_insert_id () in the same connection to execute alone, you can also put last_insert_id () in the INSERT statement after the use;
Split (can be performed with ExecuteScalar)

Xi. the basis of the transaction

1, there is a demand, similar to the transfer, from Tom's wages to transfer 10 yuan, transferred to Jerry's salary increase of 10 yuan

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

If you execute an error when you add $10 from Jerry's account (using SQL syntax to write the error simulation), then there will be a total loss of $10, if it is a transfer?

2, transactions (Transaction) have four characteristics:
Atomicity, consistency, isolation, persistence
Atomicity means that several operations either succeed or fail.

12, ADO. NET transaction

1, to be in a connection (otherwise involving a distributed transaction)
Mysqltransaction TX = conn. BeginTransaction ();
Execute Tx.commit () COMMIT transaction after the operation is completed;
TX If there is an error in execution. Rollback () rollback (the operation of the current transaction is all canceled)

Example code:
Mysqltransaction TX = conn. BeginTransaction ();
Try
{
Mysqlhelper.executenonquery (conn, "Update t_accounts set Amount = Amount-1000 where number = ' 0001 '");
string s = null;
S.tolower ();//Manufacturing anomalies
Mysqlhelper.executenonquery (conn, "Update t_accounts Set amount=amount+1000 where number= ' 0002 '");
Tx.commit ();
}
catch (Exception ex)
{
Tx. Rollback ();
}

Transactions also have isolation levels, nested transactions, and so on

13. Use of SQL Server

1, installation, version: 2008 There is a problem with compatibility, there are bugs, so it is recommended to install SQL Server R2

Use of 2,management Studio

Two ways to connect to SQL Server:
Windows authentication (in a mutually trusted LAN);
SQL Server Authentication (use SQL Server user name password authentication, password is a bit more complicated).
Windows authentication also has a purpose: forget the sa password, you can go to the machine to change.

3, new database, new table
data types for SQL Server
(VarChar and Nvarchar;nvarchar (n) and nvarchar (MAX);
Long is bigint;
Get the first 10 data: Select Top * from T_persons),
Automatic growth of SQL Server (identity), no special specified encoding required

4, when saving the table design changes, if the error "do not allow saving changes":
Tools → Options →designers→ the "block save required to recreate table higher" tick off.
Encountered error message: to read carefully first.

5, Execute SQL statement (right-click "New Query" on the database, do not click "Debug")

14, ADO. NET Connect SQL Server
1,ado.net How to connect to SQL Server:
SQL Server driver built-in

2, connection string:
Server=ip;user id =sa;password = password; database = DB1

3,sqlhelper:
You can replace MySQL with SQL.

4, get the value of the auto-Grow column:
INSERT INTO T1 (...) output insert. Id VALUES (...)

5, if based on the interface programming, as long as the change createconnection, query parameters to directory<string,object> Pass
If you use provider, you do not need to change the code, you can change the configuration file

such as Peng Web study notes (vi) ADO Foundation