1、ADO.Net简介
代码示例：
using (MySqlConnection conn = new MySqlConnection("Server=localhost;Database=study1;uid=root;pwd=root;Charset=utf8"))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();//一定要在执行前Open数据库连接
cmd.CommandText = "Insert into T_Users(UserName,Password) values('中国人','123')";
int rowCount = cmd.ExecuteNonQuery();
Console.WriteLine("受影响的行数"+rowCount);
}
解释一下代码：
MySqlConnection、MySqlCommand实现了IDisposable接口，因此使用using进行资源释放；
"Server=localhost;Database=study1;uid=root;pwd=root;Charset=utf8"叫连接字符串，Server是Mysql服务器的地址，Database是连接的数据库，uid、pwd是用户名和密码，采用utf8编码。
conn.Open()：在执行MySqlCommand之前一定要先打开数据库连接，否则会报错。
ExecuteNonQuery是执行Update、Insert、Delete等查询语句，返回值为受影响的行数。
2、ExecuteScalar
ExecuteScalar：执行查询，并返回查询所返回的结果集中的第一列，忽略其它列。一般用来简单的获得只有一行一列的查询结果的值。
案例1：
cmd.CommandText = "Select count(*) from T_Users";//查询命令：查询T_Users表的总数据条数
long count = (long)cmd.ExecuteScalar();//执行查询命令后获得的结果是一行一列的
案例2：
using(MySqlConnection conn = new MySqlConnection("Server=localhost;Database=study1;uid=root;pwd=root;Charset=utf8"))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();
//查询命令，查询用户名是amdin的密码
cmd.CommandText = "select password from T_Users where UserName='admin'";
//执行查询命令并接收返回的查询命令查询结果
string pwd = (string)cmd.ExecuteScalar();
//判断查询结果是否为空，空则没有查询到
if (string.IsNullOrEmpty(pwd))
{
Console.WriteLine("没有找到用户");
}
else
{
Console.WriteLine("密码是："+pwd);
}
}
Console.ReadKey();
3、ExecuteReader
//创建连接对象
using(MySqlConnection conn = new MySqlConnection("Server=127.0.0.1;Database=study1;uid=root;pwd=root;Charset=utf8"))
using (MySqlCommand cmd = conn.CreateCommand())//创建命令对象
{
conn.Open();
cmd.CommandText = "select * from T_Users";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
conn.Close();//Reader读取的时候要保持Connection连接
while (reader.Read())//Reader的好处是：即使查询数据量大，客户端内存也不会明显增加
{
//获得数据库里各个字段的值
int id = reader.GetInt32("Id");
string username = reader.GetString("UserName");
string password = reader.GetString("PassWord");
Console.WriteLine("Id="+id+";UserName="+username+";PassWord="+password);
}
}
}
Console.ReadKey();
注意：Reader的遍历、读取时需要Connection保持连接，如果关闭了Connection，使用Reader会出错
也可以根据列序号获取列的值，效率略高，不过程序不易读；通过reader.GetOrdinal("Age")获得列名对应的列序号
4、参数化查询
static void Main(string[] args)
{
using (MySqlConnection conn = new MySqlConnection("Server=127.0.0.1;Database=study1;uid=root;pwd=root;Charset=utf8"))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();
//把要插入的值用参数传递 @un @pwd
cmd.CommandText = "insert into T_Users(UserName,PassWord) Values(@un,@pwd)";
//填充这个参数
cmd.Parameters.Add(new MySqlParameter { ParameterName = "@un", Value = "黎庆煌" });
cmd.Parameters.Add(new MySqlParameter { ParameterName = "@pwd", Value = "" });
//执行MySql语句
int count = cmd.ExecuteNonQuery();
Console.WriteLine("影响行数：" + count);
}
Console.ReadKey();
}
(1)参数化查询有点：安全；高效(SQL预编译)
(2)所有SQL中都可以使用参数化查询传递；表名、字段名等不能用参数化查询进行替换
(3)陷阱：不要用 MySqlParameter(string parameterName,object value) 的这个构造函数，因为 ("@Age",0) 会被匹配成 MySqlParameter(string parameterName, MySqlDbType dbType) 这个构造函数
//int i = 0;
//cmd.Parameters.Add(new MySqlParameter("@Age", i));
//cmd.Parameters.Add(new MySqlParameter("@Age", 0));
cmd.Parameters.Add(new MySqlParameter("@Age",(object)));//可以强制转换这样做避免这个陷阱
5、读取数据库中的null值
数据库中的null值在C#中不能用GetString( )、GetInt32( )等读取，需要提前用 IsDBNull( ) 来判断是否数据库的null值，
using(MySqlConnection conn = new MySqlConnection("Server=127.0.0.1;Database=study1;uid=root;pwd=root;Charset=utf8"))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "select * from T_Users";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string username = reader.GetString("UserName");
//string password = reader.GetString("PassWord");
string password;
if (reader.IsDBNull(reader.GetOrdinal("PassWord")))
{
password = null;
}
else
{
//GetString/GetInt32无法读取数据库中的null值
//需要提前用IsDBNull判断
password = reader.GetString("PassWord");
}
int? age;
if (reader.IsDBNull(reader.GetOrdinal("Age")))
{
age = null;
}
else
{
age = reader.GetInt32("Age");
}
//int age = reader.GetInt32("Age");
Console.WriteLine("用户名："+username+"；密码："+password+"；年龄："+age);
}
}
}
Console.ReadKey();
6、离线数据集入门
DataReader是服务器结果集游标的体现，所有 查询出来的数据都在MySQL服务器上。好处是：当查询结果数据量大的时候避免占用本地内存。不过大部分项目中都会避免大查询结果，因此缺点就明显了：读取的时候必须保持Connection，不仅用起来麻烦，而且会较长时间占用MySQL服务器的连接资源。
DataSet是一个离线数据结果集容器，它把结果数据放到本地内存中。因为查询结果可能包含多个表，因此DataSet包含若干个DataTable(ds.Tables)、DataTable包含若干个DataRow(dt.Rows)。
用法1：
DataSet ds = new DataSet();
MySqlDataAdapter adapter = new MySqlDataAdapter(cmd);
adapter.Fill(ds);
DataTable table = ds.Tables[];
DataSet 可以盛放多个查询结果集（DataTable），DataAdapter还可以对结果进行傻瓜化更新、删除、修改。我们一般查询集就一个DataTable，DataAdapter的傻瓜化更新不适合于正式项目，因此有更简单的用法：
DataTable dt = new DataTable();
dt.Load(reader);
把DataTable声明到 using 外，using 外在使用查询结果
遍历DataTable：
for (int i = ; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
string name = row.IsNull("Name")?null:(string)row["Name"];//NULL处理
Console.WriteLine("name"+name);
}
案例代码：
（1）这样的方法是等所有操作才关闭连接，一直占用连接 影响别人访问数据库
static void Main(string[] args)
{
using(MySqlConnection conn = new MySqlConnection("Server=127.0.0.1;Database=study1;uid=root;pwd=root;Charset=utf8"))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();//打开链接
//查询T_Users表所有数据的SQL语句
cmd.CommandText = "select * from T_Users";
//声明DataReader对象
using (MySqlDataReader reader = cmd.ExecuteReader())
{
DataTable dt = new DataTable();
//通过DataTable对象的Load方法填充
dt.Load(reader);
//循环获得DataTable的每行的数据
for (int i = ; i < dt.Rows.Count; i++)
{
DataRow row = dt.Rows[i];
int id = (int)row["Id"];//下标方式获得的是object类型
string username = (string)row["UserName"];
//输出
Console.WriteLine("Id="+id+"；UserName="+username);
}
}
}
Console.ReadKey();
}
（2）这样做是获取数据填充到DataTable，填充完之后断开连接 不影响别人访问，DataTable也可以继续使用操作
DataTable table = new DataTable();//把DataTable声明到using外面
using(MySqlConnection conn = new MySqlConnection("Server=localhost;Database=study1;uid=root;pwd=root;Charset=utf8"))
using (MySqlCommand cmd = conn.CreateCommand())
{
conn.Open();
cmd.CommandText = "select * from T_Users";
using (MySqlDataReader reader = cmd.ExecuteReader())
{
table.Load(reader);//加载数据到table中
}
}
//此时DataTable已经填充完数据 即使连接断开也能操作
for (int i = ; i < table.Rows.Count; i++)
{
DataRow row = table.Rows[i];
int id = (int)row["Id"];
string username = (string)row["UserName"];
Console.WriteLine(id+"："+username);
}
7、获得自动增长字段的值
不要用插入后获取最大值得方法，有并发问题，很多人访问时几乎同时插入信息，获取最大值已经不是刚才插入的值
在同一个连接中使用Last_Insert_Id( ) ；可以Insert、Last_Insert_Id( ) 单独执行，也可以把Last_Insert_Id( )放到 Insert语句后面用 ; 分割（使用ExecuteScalar执行即可）
8、事务的原子性
事务基础
事务（Transaction）有四大特征（*）：原子性、一致性、隔离性、持久性。
原子性指的是“几个操作要么都成功，要么都失败”！
ADO.Net事务的几个关键环节
1)要在一个连接当中
2)启动事务： MySqlTransaction tx = conn.BeginTransaction();
3)操作结束后 tx.Commit() 提交事务
4)如果执行出错，则 tx.Rollback() 回滚 （当前事务的操作全部取消）。
MySqlTransaction tx = conn.BeginTransaction();//启动事务
try
{
MySqlHelper.ExecuteNonQuery(conn, "Update t_accounts Set Amount=Amount-1000 where Number='0001'");
string s = null;
s.ToLower();
MySqlHelper.ExecuteNonQuery(conn, "Update t_accounts Set Amount=Amount+1000 where Number='0002'");
tx.Commit();//执行到这，没有错误则提交事务
}
catch (Exception ex)
{
tx.Rollback();//如果过程中出现任何错误则执行回滚（当前事务操作全部取消）
}
9、写一个MySqlHelper
class MySqlHelper
{
private static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; /// <summary>
/// 创建一个链接并打开
/// </summary>
/// <returns>MySql连接对象</returns>
public static MySqlConnection CreatConnection()
{
MySqlConnection conn = new MySqlConnection(connstr);
conn.Open();
return conn;
}
/// <summary>
/// 用已创建的连接执行一个MySQL语句
/// </summary>
/// <param name="conn">已创建的连接对象</param>
/// <param name="sql">要执行的MySql语句</param>
/// <param name="parameters">参数化查询的参数值</param>
/// <returns>返回受影响的行数</returns>
public static int ExecuteNonQuery(MySqlConnection conn, string sql, params MySqlParameter[] parameters)
{
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
//foreach (MySqlParameter p in parameters)
//{
// cmd.Parameters.Add(p);
//}
cmd.Parameters.AddRange(parameters);
int i=cmd.ExecuteNonQuery();
return i;
}
}
/// <summary>
/// 自己创建连接执行MySql语句
/// </summary>
/// <param name="sql">要执行的语句</param>
/// <param name="parameters">参数化查询的替换值</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = CreatConnection())
{
return ExecuteNonQuery(conn, sql, parameters);
}
}
/// <summary>
/// 用已创建的连接查询第一行第一列
/// </summary>
/// <param name="conn">已创建的连接对象</param>
/// <param name="sql">要执行的MySql语句</param>
/// <param name="parameters">参数化查询的参数传递值</param>
/// <returns></returns>
public static object ExecuteScalar(MySqlConnection conn, string sql, params MySqlParameter[] parameters)
{
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
return cmd.ExecuteScalar();
}
}
/// <summary>
/// 自己创建连接查询第一行第一列
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql, params MySqlParameter[] parameters)
{
using (MySqlConnection conn = CreatConnection())
{
return ExecuteScalar(conn, sql, parameters);
}
}
/// <summary>
/// 获得查询的表数据
/// </summary>
/// <param name="conn">已创建的连接对象</param>
/// <param name="sql">要执行的语句</param>
/// <param name="parameters">参数化查询的参数值</param>
/// <returns>返回DataTable本地结果集对象</returns>
public static DataTable ExecuteDataTable(MySqlConnection conn, string sql,params MySqlParameter[] parameters)
{
DataTable table = new DataTable();
using (MySqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.Parameters.AddRange(parameters);
using (MySqlDataReader reader = cmd.ExecuteReader())
{
table.Load(reader);
}
}
return table;
}
/// <summary>
/// 获得查询的表数据
/// </summary>
/// <param name="sql">要执行的语句</param>
/// <param name="parameters">参数化查询的参数值</param>
/// <returns>返回DataTable本地结果集对象</returns>
public static DataTable ExecuteDataTable(string sql,params MySqlParameter[] parameters)
{
using (MySqlConnection conn = CreatConnection())
{
return ExecuteDataTable(conn, sql, parameters);
}
}
}
10、ADO.Net连接SQLServer
1、ADO.Net如何连接SQLServer：SQLServer驱动.Net内置（亲生的）；把MySqlConnection转换成SqlConnection，MySql***换成Sql***；
2、连接字符串： server=ip;user id= sa;password=密码;database=db1
3、SQLHelper：把MySql替换成Sql就可以了
4、获得自动增长列的值：Insert into T1(...) output Insert.Id Values(......)
5、如果基于接口编程，只要改动 CreateConnection 就可以；查询参数以Dictionary<string,object>传递；如果使用Provider，连代码都不用改，改配置文件即可
6、需要特别注意：SqlServer 的事务和MySql事务使用有一点不一样的地方是 “需要把 BeginTransaction 返回的 SqlTransaction 对象赋值给 SqlCommand 的 Transaction 属性”。