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