如鹏网学习笔记(六)ADO.Net基础_.NET_编程开发_程序员俱乐部

中国优秀的程序员网站程序员频道CXYCLUB技术地图
热搜:
更多>>
 
您所在的位置: 程序员俱乐部 > 编程开发 > .NET > 如鹏网学习笔记(六)ADO.Net基础

如鹏网学习笔记(六)ADO.Net基础

 2017/8/19 21:31:25  半路和尚  程序员俱乐部  我要评论(0)
  • 摘要:ADO.Net基础一、ADO.Net简介1,程序要通过SQL语句自动化的操作数据库,必须要用一个类库,类库要提供execute("insertinto...")/executeQuery("select*from...")类似的方法2,ADO.Net是.Net中提供的标准访问数据库的接口,访问不同的DBMS的底层方法是不一样的,ADO.Net把访问数据库的方法进行了统一,访问MYSql、Oracle、SqlServer等不同数据库的方法几乎是一样的3,ADO.Net是规范
  • 标签:笔记 .net 学习 net 学习笔记

ADO.Net基础

一、ADO.Net简介
  1,程序要通过SQL语句自动化的操作数据库,必须要用一个类库,
    类库要提供execute("insert into ...")/executeQuery("select * from ...")类似的方法

  2,ADO.Net是.Net中提供的标准访问数据库的接口,访问不同的DBMS的底层方法是不一样的,ADO.Net把访问数据库的方法进行了统一,
    访问MYSql、Oracle、SqlServer等不同数据库的方法几乎是一样的

  3,ADO.Net是规范,被不同的数据库厂商提供ADO.Net的实现,称之为ADO.Net驱动,每个厂商提供的驱动可以用来操作自己的数据库

二、ADO.Net连接MYSQL

  1,安装MySql的.Net驱动mysql-connector-net-***.msi添加到项目的库中。
    如果安装遇到问题,则直接下载mysqlnetconnection(V4.5).zip。

  2,新建项目,添加引用——“扩展”,添加Mysql.Data;如果是直接解压版,然后直接添加对MySql.Data.dll的文件的引用
    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+"受到影响");
        Console.ReadKey();
      }
    }

    代码解释:
    1,MySQLConnection、MySQLCommend实现了IDisposable接口,因此使用using进行资源回收

    2,"Server=localhost;Database=study1;uid=root;pwd=root;Charset=utf8"叫连接字符串,
      Server是Mysql服务器的地址,Database是连接的数据库,uid、pwd是用户名和密码,采用utf8编码

    3,conn.Open();在执行MySqlCommand之前一定要先打开数据库连接,否则会报错

    4,ExecuteNoQuery是执行Update、Insert、Delete等非查询语句,返回值为受影响的行数


  3,ExecuteScalar
    执行查询,并返回查询所返回的结果集中第一行的第一列,忽略其他行列。一般用来简单的获得自由一行一列的查询结果的值

    案例1
    cmd.CommandText = "Select count(*) from T_Users";
    long count = (long)cmd.ExecuteScalar();

    案例2
    cmd.CommandText = "Select Password from T_Users where UserName = 'admin'";
    string pwd = (string)cmd.ExecuteScalar();
    if(string.isNullOrEmpty(pwd))
    {
      Console.WriteLine("找不到admin");
    }
    else
    {
      Console.WriteLine("admin的密码:"+pwd);
    }

  4,执行查询 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);
      }
    }

    注意:
      Reader的遍历、读取时需要Connection保持连接,如果关闭了Connection,使用会出错
      也可以根据列序号获取列的值,效率略高,不过程序不容易读;通过reeder.GetOrdinal("Age")获取列名对应的序列号



三、SQL注入漏洞

  1,是由于Sql语句的拼接造成的,
    在程序接收用户的输入时,需要考虑用户输入的内容对数据库操作产生的影响,

  2,对应的解决方法,参数化查询

    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 });

    为什么这样可以避免“SQL注入漏洞”,
    因此使用参数化查询,就可以对用户输入的内容进行判断和处理了,本质上是参数赋值

    注意:
    1,所有SQL中都可以使用参数化查询传递;表名、字段名等不能使用参数化进行替换
    2,不要用SqlParameter(string parameterName,object value)这个构造函数
    因为("Age",0)会被匹配成Sqlparameter(string parameterName,SqlDbType dbType)这个构造函数

四、基本数据类型为空的问题

  1,把T_Persons表的Name、Age列修改为“允许为空”,插入一条Name、Age为空的行
    执行后发现,对于空数据reader.Get**方法会抛异常SqlNullValueException,
    相当于问数据库“当前行的Name”是什么,数据库告诉你“不知道”。

    怎么解决?
    使用ISDBNull获取指定序号的列的值是否为null

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

五、离线结果集DataSet

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

六、DataTable
  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,ADO.Net的连接字符串写到配置文件中。

  2,每次操作数据库都要写一坨代码,太累,因此封装一个简化ADO.Net操作的库出来:
    配置文件的设置连接字符串;简化连接的创建;简化SQL的执行

  3,如果一个操作要执行多条SQL语句,如果每条都打开一个连接——执行——关闭连接的话,效率会非常低,而且会有“事务”的问题。
    因此应该提供“打开、执行、关闭”这样的方法,也要提供“使用后现有连接执行的方法”。

  4,参数化查询的查询参数个数不确定,可变长度参数会更方便

  5,为了方便大部分情况下的小结果集,执行查询返回DataTable


八、MySqlHelper方法规划

  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)


九、实现MySqlHelper

  1,连接字符串一般配置到App.config(网站是Web.config)中的<connectionStrings>段中
    然后使用ConfigurationManager类(需要添加对System.Configuration的引用)读取
    string connStr = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString
    

    注意:
    一定要保证代码中的名字和配置文件中的名字是一致的

  2,其他方法的实现

  3,测试几个方法,调用不需要自己控制连接的,再调用公用一个连接的


十、获得自动增长字段的值

  1,不能用插入后获取最大值的方法,有并发问题

  2,要在同一个连接中:select LAST_INSERT_ID()

  3,可以Insert、LAST_INSERT_ID()在同一个连接中单独执行,也可以把LAST_INSERT_ID()放到insert语句后面用;
    分割(使用ExecuteScalar执行即可)

十一、事务基础

  1,有一个需求,类似于转账,从Tom的工资上转走10元,转到Jerry的工资上增加10元

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

    如果执行从Jerry账上加10元的时候执行出错(使用SQL语法写错误模拟),那么就会出现总体丢失10元的问题,如果是转账呢?

  2,事务(Transaction)有四大特征:
    原子性、一致性、隔离性、持久性
    原子性指的是:几个操作要么都成功,要么都失败

十二、ADO.Net事务

  1,要在一个连接中(否则要涉及到分布式事务)
    MySqlTransaction tx = conn.BeginTransaction();
    操作结束后执行tx.Commit()提交事务;
    如果执行出错,则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();
    }

    事务还有隔离级别、嵌套事务等问题


十三、SQLServer的使用

  1,安装,版本:2008有兼容性的问题,有bug、因此推荐安装SQLServer 2008 R2

  2,Management Studio的使用

    SQLServer的两种连接方式:
    Windows 身份验证(互相信任的局域网中);
    SQLServer身份验证(使用SQLServer用户名密码验证,密码要复杂一点)。
    Windows 身份验证还有一个用途:忘了sa密码,可以本机进去改。

  3,新建数据库、新建表
    SQLServer的数据类型
    (varchar和nvarchar;nvarchar(n)和nvarchar(MAX);
    long是bigint;
    获取前10条数据: select top 10 * from t_persons )、
    SQLServer的自动增长(是标识)、不需要特殊指定编码

  4,保存表设计修改的时候,如果报错“不允许保存更改”:
    工具→选项→Designers→把“阻止保存要求重新创建表的更高”勾选掉。
    遇到报错信息:要先仔细阅读。

  5,执行SQL语句(数据库上点右键“新建查询”,不要点【调试】)


十四、ADO.Net连接SQLServer
  1,ADO.Net如何连接SQLServer:
    SQLServer驱动内置

  2,连接字符串:
    server=ip;user id =sa;passWord = 密码;database = db1

  3,SQLHelper:
    把MySql替换成Sql就可以了

  4,获得自动增长列的值:
    Insert into t1(...) output insert.Id values(....)

  5,如果基于接口编程,只要改动CreateConnection就可以了,查询参数以Directory<string,object>传递
    如果使用Provider,连代码都不用改,改配置文件即可

 

 

 

 

 


发表评论
用户名: 匿名