ADO.NET学习之SqlConnection,SqlCommand

ADO.NET架构

组成数据提供程序的类包括：

• Connection使用它来建立和数据库的连接
• Command使用它执行SQL命令和存储过程
• DataReader它提供对查询结果的快速的只读、只进访问方法
• DataAdapter 可以用它来执行2项任务。首先把数据源获取的信息填充到DataSet，其次，依照DataSet的修改更新数据源

ADO.NET有两种类型的对象：基于连接的和基于内容的

• 基于连接的对象。它们是数据提供程序对象，如Connection、Command、DataReader和DataAdapter。它们连接到数据库，执行SQL语句，遍历只读结果集或者填充DataSet。基于连接的对象是针对具体数据源类型的，并且可以在提供程序制定的命令空间中(例如SQL Server提供程序的System.Data.SqlClient)找到
• 基于内容的对象。这些对象其实是数据的”包”，包括DataSet、DataColumn、DataRow、DataRelation等。它们完全和数据源独立，出现在System.Data命令空间里

不同的Data Provider
1.Data Provider for SQL Server - System.Data.SqlClient，连接SQL Server数据库

SqlConnection con = new SqlConnection("data source=.; database=Sample; integrated security=SSPI");SqlCommand cmd = new SqlCommand("Select * from tblProduct", con);con.Open();SqlDataReader rdr = cmd.ExecuteReader();GridView1.DataSource = rdr;GridView1.DataBind();con.Close();

2.Data Provider for Oracle - System.Data.OracleClient，连接Oracle数据库

OracleConnection con = new OracleConnection("Oracle Database Connection String");OracleCommand cmd = new OracleCommand("Select * from tblProduct", con);con.Open();OracleDataReader rdr = cmd.ExecuteReader();GridView1.DataSource = rdr;GridView1.DataBind();con.Close(); 

3.Data Provider for OLEDB - System.Data.OleDb，连接Excel、Access 等
4.Data Provider for ODBC - System.Data.Odbc

---

SqlConnection

SqlConnection有2个构造方法，一个有参，一个无参

public SqlConnection();public SqlConnection(string connectionString);

创建SqlConnection对象的两种形式：
第一种方式：

SqlConnection connection = new SqlConnection("data source=.; database=SampleDB; integrated security=SSPI");

第二种方式：

SqlConnection connection = new SqlConnection();connection.ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";

ConnectionString是由键值对组成的
例如，创建一个windows authentication的连接对象

string ConnectionString = "data source=.; database=SampleDB; integrated security=SSPI";

创建一个SQL Server authentication的连接对象

string ConnectionString = "data source=.; database=SampleDB; user id=MyUserName; password=MyPassword";

data source指的是我们想要连接的SQL Server的名字或者IP地址，如果连接的是本地，直接使用一个点(.)即可

完整使用SqlConnection 的例子，注意要捕获异常，关闭连接

protected void Page_Load(object sender, EventArgs e){    SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI");;    try    {        SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);        connection.Open();        GridView1.DataSource = cmd.ExecuteReader();        GridView1.DataBind();    }    catch (Exception ex)    {   }    finally    {        connection.Close();    }}

也可以使用using，这样就不用显示调用Close() 方法关闭连接，如下，连接将会被自动关闭：

protected void Page_Load(object sender, EventArgs e){    using (SqlConnection connection = new SqlConnection("data source=.; database=Sample_Test_DB; integrated security=SSPI"))    {        SqlCommand cmd = new SqlCommand("Select * from tblProductInventory", connection);        connection.Open();        GridView1.DataSource = cmd.ExecuteReader();        GridView1.DataBind();    }}

using语句声明正在短期使用一个可释放的对象。using语句一旦结束，CLR会立刻通过调用对象的Dispose()方法释放相应的对象。有趣的是，调用Connection对象的Dispose()方法好调用Close()方法等效

总结一下：
1.connection要尽可能晚的打开，要尽可能早的关闭
2.要在finally块中关闭connection，或者使用using statement

面试可能会问的问题
Common Interview Question: What are the 2 uses of an using statement in C#?
1. To import a namespace. Example: using System;
2. To close connections properly as shown in the example above

---

在配置文件中保存connection string
configuration string可保存在web.config文件中，如下：

<connectionStrings>  <add name="DatabaseConnectionString"        connectionString="data source=.; database=Sample_Test_DB; Integrated Security=SSPI"        providerName="System.Data.SqlClient" /></connectionStrings> 

如何获取配置文件中的connection string？
使用ConfigurationManager（位于System.Configuration）的ConnectionStrings属性来获取

    string ConnectionString = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;    using (SqlConnection connection = new SqlConnection(ConnectionString))    {        SqlCommand cmd = new SqlCommand("Select * from tblProduct", connection);        connection.Open();        GridView1.DataSource = cmd.ExecuteReader();        GridView1.DataBind();    }

还有另一种形式是，把连接字符串作为key添加到<appSettings>中

    <appSettings>        <add key="conn" value="Server=192.168.0.1;database=xxxx;Uid=xx;Pwd=xxxx"/>    </appSettings>

通过ConfigurationManager.AppSettings["conn"];形式来获取，参考ConfigurationManager.AppSettings 属性

                var appSettings = ConfigurationManager.AppSettings;                if (appSettings.Count == 0)                {                    Console.WriteLine("AppSettings is empty.");                }                else                {                    foreach (var key in appSettings.AllKeys)                    {                        Console.WriteLine("Key: {0} Value: {1}", key, appSettings[key]);                    }                }

---

SqlCommand

SqlCommand用来对SQL Server执行一个SQL语句或者存储过程
常用方法
1.ExecuteReader-执行select查询，并返回一个封装了只读、只进游标的DataReader对象
2.ExecuteNonQuery-在执行Insert、Update或者Delete操作时使用，返回值显示命令影响的行数
3.ExecuteScalar()-执行select查询，并返回命令生成的记录集的第一行第一列的字段。该方法常用来执行使用COUNT() 、SUM()等函数的聚合select语句，用于计算单个值

可以设置CommandType、CommandText、Connection，或者把它们作为构造函数的参数来传递

创建SqlCommand对象的的两种方式
第一种方式

SqlCommand cmd = new SqlCommand("Select Id,ProductName,QuantityAvailable from tblProductInventory", connection);

第二种方式

    SqlCommand cmd = new SqlCommand();    cmd.CommandText = "Select Id,ProductName,QuantityAvailable from tblProductInventory";    cmd.Connection = connection;

1.调用ExecuteReader()方法，执行T-SQL语句，返回多行数据

    GridView1.DataSource = cmd.ExecuteReader();    GridView1.DataBind();

2.调用ExecuteScalar()方法，返回单个值，如下统计行数：

        SqlCommand cmd = new SqlCommand("Select Count(Id) from tblProductInventory", con);        con.Open();        int TotalRows = (int)cmd.ExecuteScalar();        Response.Write("Total Rows = " + TotalRows.ToString());

3.调用ExecuteNonQuery()方法，执行插入，更新、删除

        //插入，返回值表示插入多少行        SqlCommand cmd = new SqlCommand("insert into tblProductInventory values (104, 'Apple Laptops', 100)", con);        con.Open();        int rowsAffected = cmd.ExecuteNonQuery();        Response.Write("Inserted Rows = " + rowsAffected.ToString() + "<br/>");        //更新        cmd.CommandText = "update tblProductInventory set QuantityAvailable = 101 where Id = 101";        rowsAffected = cmd.ExecuteNonQuery();        Response.Write("Updated Rows = " + rowsAffected.ToString() + "<br/>");        //删除        cmd.CommandText = "Delete from tblProductInventory where Id = 102";        rowsAffected = cmd.ExecuteNonQuery();        Response.Write("Deleted Rows = " + rowsAffected.ToString() + "<br/>");

---

SQL注入

例如，有如下的数据表：

如下的页面，根据ProductName字段来查询，数据组在GridView中显示：

按钮的事件如下：

protected void Button1_Click(object sender, EventArgs e){    string CS = ConfigurationManager.ConnectionStrings["DatabaseConnectionString"].ConnectionString;    using (SqlConnection con = new SqlConnection(CS))    {        string Command = "select * from tblProductInventory where ProductName like '"            + TextBox1.Text + "%'";        SqlCommand cmd = new SqlCommand(Command, con);        con.Open();        GridView1.DataSource =  cmd.ExecuteReader();        GridView1.DataBind();    }}

如上，获取输入框的值，动态的拼接SQL语句，是非常危险的！
假设，在输入框输入的是i'; Delete from tblProductInventory --，那么拼接之后的语句就变为select * from tblProductInventory where ProductName like 'ip'; Delete from tblProductInventory --%'
在SQL Server中 --表示注释，所以上面语句相当于如下：

select * from tblProductInventory where ProductName like 'ip';Delete from tblProductInventory --%'

所以，数据表中的数据就被删除了……

如何防止SQL注入呢？
1.使用TextBox.MaxLength属性防止用户输入过长的字符
2.使用ASP.NET验证控件锁定错误的数据（如文本、空格、数值中的特殊字符）
3.限制错误信息给出的提示，捕获到数据库异常时，只显示一些通用信息（如”数据源错误”）而不是显示Exception.Message属性中的信息，它可能暴露了系统的攻击点
4.一定要小心去除特殊字符，比如，可以将单引号替换为2个单引号
4.可使用参数化的查询或者使用存储过程

---

补充内容

SqlParameter类

参考Adding Parameters to Commands

例如，你想做一个过滤查询：

    // 不要这样做    SqlCommand cmd = new SqlCommand(        "select * from Customers where city = '" + inputCity + "'";

inputCity时从TextBox中获取的，可能被注入。所以要使用parameter ，parameter will be treated as field data, not part of the SQL statement,会更安全。

使用步骤
1.创建一个包含parameter占位符的command

    // 1. declare command object with parameter    SqlCommand cmd = new SqlCommand(        "select * from Customers where city = @City", conn);

2.声明一个SqlParameter 对象

    // 2. define parameters used in command object    SqlParameter param  = new SqlParameter();    param.ParameterName = "@City";    param.Value         = inputCity;

3.把SqlParameter与SqlCommand对象结合起来

    // 3. add new parameter to command object    cmd.Parameters.Add(param);

更简便的方式

SqlCommand command = new SqlCommand(                "SELECT * FROM Dogs1 WHERE Name LIKE @Name", connection)command.Parameters.Add(new SqlParameter("Name", dogName));

或者通过AddWithValue方法

cmd.Parameters.AddWithValue("@CustID", txtID.Text);