07ADO.Net

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 = "123" });
        //执行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)0));//可以强制转换这样做避免这个陷阱

 

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[0];

 

 

DataSet 可以盛放多个查询结果集(DataTable),DataAdapter还可以对结果进行傻瓜化更新、删除、修改。我们一般查询集就一个DataTable,DataAdapter的傻瓜化更新不适合于正式项目,因此有更简单的用法:
DataTable dt = new DataTable();
dt.Load(reader);

 

 
把DataTable声明到 using 外,using 外在使用查询结果
遍历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处理
    Console.WriteLine("name"+name);
}

 

  1.  
 
 
案例代码:
(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 = 0; 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 = 0; 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

 

  1 class MySqlHelper
  2 {
  3     private static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
  4         
  5     /// <summary>
  6     /// 创建一个链接并打开
  7     /// </summary>
  8     /// <returns>MySql连接对象</returns>
  9     public static MySqlConnection CreatConnection()
 10     {
 11         MySqlConnection conn = new MySqlConnection(connstr);
 12         conn.Open();
 13         return conn;
 14     }
 15     /// <summary>
 16     /// 用已创建的连接执行一个MySQL语句
 17     /// </summary>
 18     /// <param name="conn">已创建的连接对象</param>
 19     /// <param name="sql">要执行的MySql语句</param>
 20     /// <param name="parameters">参数化查询的参数值</param>
 21     /// <returns>返回受影响的行数</returns>
 22     public static int ExecuteNonQuery(MySqlConnection conn, string sql, params MySqlParameter[] parameters)
 23     {
 24         using (MySqlCommand cmd = conn.CreateCommand())
 25         {
 26             cmd.CommandText = sql;
 27             //foreach (MySqlParameter p in parameters)
 28             //{
 29             //    cmd.Parameters.Add(p);
 30             //}
 31             cmd.Parameters.AddRange(parameters);
 32             int i=cmd.ExecuteNonQuery();
 33             return i;
 34         }
 35     }
 36     /// <summary>
 37     /// 自己创建连接执行MySql语句
 38     /// </summary>
 39     /// <param name="sql">要执行的语句</param>
 40     /// <param name="parameters">参数化查询的替换值</param>
 41     /// <returns>受影响的行数</returns>
 42     public static int ExecuteNonQuery(string sql, params MySqlParameter[] parameters)
 43     {
 44         using (MySqlConnection conn = CreatConnection())
 45         {
 46             return ExecuteNonQuery(conn, sql, parameters);
 47         }
 48     }
 49     /// <summary>
 50     /// 用已创建的连接查询第一行第一列
 51     /// </summary>
 52     /// <param name="conn">已创建的连接对象</param>
 53     /// <param name="sql">要执行的MySql语句</param>
 54     /// <param name="parameters">参数化查询的参数传递值</param>
 55     /// <returns></returns>
 56     public static object ExecuteScalar(MySqlConnection conn, string sql, params MySqlParameter[] parameters)
 57     {
 58         using (MySqlCommand cmd = conn.CreateCommand())
 59         {
 60             cmd.CommandText = sql;
 61             cmd.Parameters.AddRange(parameters);
 62             return cmd.ExecuteScalar();
 63         }
 64     }
 65     /// <summary>
 66     /// 自己创建连接查询第一行第一列
 67     /// </summary>
 68     /// <param name="sql"></param>
 69     /// <param name="parameters"></param>
 70     /// <returns></returns>
 71     public static object ExecuteScalar(string sql, params MySqlParameter[] parameters)
 72     {
 73         using (MySqlConnection conn = CreatConnection())
 74         {
 75             return ExecuteScalar(conn, sql, parameters);
 76         }
 77     }
 78     /// <summary>
 79     /// 获得查询的表数据
 80     /// </summary>
 81     /// <param name="conn">已创建的连接对象</param>
 82     /// <param name="sql">要执行的语句</param>
 83     /// <param name="parameters">参数化查询的参数值</param>
 84     /// <returns>返回DataTable本地结果集对象</returns>
 85     public static DataTable ExecuteDataTable(MySqlConnection conn, string sql,params MySqlParameter[] parameters)
 86     {
 87         DataTable table = new DataTable();
 88         using (MySqlCommand cmd = conn.CreateCommand())
 89         {
 90             cmd.CommandText = sql;
 91             cmd.Parameters.AddRange(parameters);
 92             using (MySqlDataReader reader = cmd.ExecuteReader())
 93             {
 94                 table.Load(reader);
 95             }
 96         }
 97         return table;
 98     }
 99     /// <summary>
100     /// 获得查询的表数据
101     /// </summary>
102     /// <param name="sql">要执行的语句</param>
103     /// <param name="parameters">参数化查询的参数值</param>
104     /// <returns>返回DataTable本地结果集对象</returns>
105     public static DataTable ExecuteDataTable(string sql,params MySqlParameter[] parameters)
106     {
107         using (MySqlConnection conn = CreatConnection())
108         {
109             return ExecuteDataTable(conn, sql, parameters);
110         }
111     }
112 }
View Code

 

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 属性”。

 

 

 

posted @ 2015-06-03 19:02  Kenward  阅读(192)  评论(0编辑  收藏  举报