ADO.NET学习之SqlConnection,SqlCommand

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