SQLServer与ADO.Net（四）
---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------
1、ADO.NET
l 为什么要学ADO.NET
• 之前我们所学只能在查询分析器里查看数据，操作数据，我们不能让普通用户去学sql，所以我们搭建一个界面(Web Winform)让用户方便的操作数据库中的数据
l 什么是ADO.NET
• ADO.NET就是一组类库，这组类库可以让我们通过程序的方式访问数据库，就像System.IO下的类用类操作文件一样， System.Data.这组类是用来操作数据库(不光是MSSql Server)，它提供了统一的编程接口让操作其它数据库(Access、Oracle等)的方式和操作MSSql Server一致
2、ADO.NET组成（*）
l 数据提供程序
• ***Connection，用来连接数据库
• ***Command，用来执行SQL语句
• DataReader只读只进的结果集，一条一条读取数据
• DataAdapter，一个封装了上面3个对象的对象。
l 数据集(DataSet)，临时数据库。
• 断开时连接的数据库操作
3、ADO.NET与数据库
l Ado.net访问数据库的步骤。
• 1
• 1.连接数据用Connection
• 2.执行SQL语句Command
• 3.执行完毕之后将结果一条一条返回。DataReader
• 2
• 使用DataAdapter+DataSet,这种方法本质还是通过Connection、Command、DataReader将数据全部取出来然后放到了DataSet中。
4、如何连接数据库
l 在管理工具里面
• 连接数据库
• 新建查询
• 写SQL语句
• 执行SQL语句
l 在ADO.Net中
• 创建连接通道（SqlConnection）
• 创建SQL命令对象（SqlCommand）
• 开始查询
5、连接SQLServer
l SqlConnection
• 构造方法提供连接字符串
• 属性提供连接字符串
l 连接字符串
• data source=实例名;initial catalog=数据库名;integrated security=true/user id=…;password=…;
• server=实例名;database=数据库名;integrated security=true/uid=…;pwd=…;
l 打开连接open()
l 连接都需要Dispose()
l State属性可以查看连接状态
附：随机练习代码
static void Main(string[] args)
{
/*1、要连接数据库，就要创建连接对象
* 数据库实例名 （Local）or T1417SMIS
* 数据库 UsersInfo
* 用户名和密码 （Windows身份验证） sa 123456
*/
string strConn =@"server=(local);database=UsersInfo;uid=sa;pwd=123456";
//连接字符串，需要确定连接哪个数据库管理软件，即连接哪个实例名，用Server表示
//需要了解连接哪一个数据库文件，用Database表示
//身份验证
// Windows身份验证：integratedsecurity=true
// SqlServer验证：uid pwd
// Data Source=T1417SMIS;Initial Catalog=UsersInfo;IntegratedSecurity=True
// Data Source=t1417smis;Initial Catalog=UsersInfo;UserID=sa;Password=123456;
#region try-catch-finally手动释放资源
////创建连接通道
//SqlConnection conn = new SqlConnection(strConn);
//// 需要连接
//try
//{
// //开始就执行这里，如果出现异常，在异常处停下，并跳至Catch中代码
// conn.Open();
// Console.WriteLine("我成功连接啦！");
//}
//catch(Exception ex)
//{
// //平时不执行，当try出现异常时，才执行其中代码
// Console.WriteLine(ex.Message);
//}
//finally
//{
// //无论何时均执行,一般常用作释放资源
// conn.Close(); //关闭后可重新开启
// conn.Dispose(); //销毁后需重新连接才能开启
// Console.WriteLine("我成功关闭啦！");
//}
#endregion
#region 使用using自动释放资源，推荐使用
using (SqlConnection conn = new SqlConnection(strConn))
{
// 需要连接
try
{
//开始就执行这里，如果出现异常，在异常处停下，并跳至Catch中代码
if (conn.State==System.Data.ConnectionState.Closed)
{
//? State属性可以查看连接状态，数据库重复打开会报异常
conn.Open();
}
Console.WriteLine("我成功连接啦！");
}
catch (Exception ex)
{
//平时不执行，当try出现异常时，才执行其中代码
Console.WriteLine(ex.Message);
}
}
Console.WriteLine("我成功关闭啦！");
Console.ReadKey();
#endregion
}
6、准备执行SQL语句
l 执行SQL语句使用SqlCommand对象
l 提供SQL语句
• 构造方法提供
• 属性提供
l -> 执行SQL语句需要知道在哪儿执行
• SqlCommand需要提供SQL语句和连接通道
l -> 常用三个方法
• ExecuteNonQuery() 处理增、删、改，返回受影响行数
• ExecuteScalar() 处理查询，返回首行首列
• ExecuteReader() 处理查询，返回DataReader对象
7、执行简单的增删改语句
l SqlCommand的ExecuteNonQuery执行增删改操作
l ExecuteNonQuery返回值是执行的影响行数
• //执行数据库操作3步
• //打开连接
• conn.Open();
• //执行操作
• result = cmd.ExecuteNonQuery();
• //关闭连接
• conn.Close();
l 使用using语句即使释放连接资源，不用再考虑是否关闭连接，推荐使用
8、异常处理
l 可以使用try…catch…finally来捕获异常
l 使用异常处理可以保证一个功能出错不影响另一个功能，比如添加操作失败，不影响查询的操作
9、ExecuteScalar
l 使用ExecuteScalar()执行SQL语句，返回第一行第一列，object类型
• -> 转换使用Convert，不要使用强转
l 使用ExecuteScalar()一般执行聚合查询
l 使用ExecuteScalar()亦可查询selectgetdate()
l 在添加数据的时候，得到自动增长id
• -> 在value前使用output inserted.主键字段
l 使用详注：
1）、SqlCommand的ExecuteScalar方法用于执行查询，并返回查询所返回的结果集中第一行的第一列，因为不能确定返回值的类型，所以返回值是object类型。
cmd.CommandText = "selectcount(*) from student";int i = Convert.ToInt32(cmd.ExecuteScalar())
cmd.CommandText = "selectgetdate()"; DateTime dt = Convert.ToDateTime(cmd.ExecuteScalar());
2）、得到自动增长字段的主键值，在values关键词前加上output inserted.Id，其中Id为主键字段名。执行结果就试插入的主键值，用ExecuteScalar执行最方便。
cmd.CommandText =“insert intoclass(cName,cDescription) output inserted.Id values(‘高三一班’,‘描述’)”;
int i =Convert.ToInt32(cmd.ExecuteScalar());
10、ExecuteReader
l 查询多行数据，返回一个DataReader对象
• HasRows属性表示查询结果
• 提供连接通道用来持续得到数据
• 并未将数据全部倒入到内存
l -> 如何处理DataReader
• 使用while循环，调用Read方法
• 调一次，查一次
• GetString()、GetInt32()、GetOrdinal()方法
• 使用索引，索引器有重载
l DataReader必须保证SqlConnection处于连接状态
附：随机练习代码
//1、ExecuteNonQuery()
static void Main(string[] args)
{
//ADO.NET能执行什么，你能写什么SQL语句，它就能给你执行什么
SqlConnection conn = new SqlConnection(@"DataSource=T1417SMIS;Initial Catalog=UsersInfo;Integrated Security=True");
string sql = "create table Tbl(id int,name nvarchar(10));insertinto Tbl values(1,'张洋洋');";
using (conn)
{
using (SqlCommand cmd = newSqlCommand(sql, conn))
{
if (conn.State==System.Data.ConnectionState.Closed)
{
conn.Open();
}
cmd.ExecuteNonQuery();
//ExecuteNonQuery()对于Update、Insert和Delete语句，返回值为该命令所唱响的行数。对于其他所有类型的语句，返回值为-1;
}
}
Console.WriteLine("OK");
Console.ReadKey();
}
#region 2、ExecuteScalar()
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"DataSource=T1417SMIS;Initial Catalog=UsersInfo;Integrated Security=True");
string sql = @"select [name] from Tbl;";
using (conn)
{
using (SqlCommand cmd = newSqlCommand(sql, conn))
{
if (conn.State ==System.Data.ConnectionState.Closed)
{
conn.Open();
}
//ExecuteScalar() 返回结果集中第一行的第一列
object o =cmd.ExecuteScalar();
//int res =Convert.ToInt32(o);
Console.WriteLine(o.ToString());
//Console.WriteLine(res);
}
}
Console.ReadKey();
}
#endregion
#region 3、ExecuteReader
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection(@"Data Source=T1417SMIS;InitialCatalog=UsersInfo;Integrated Security=True");
string sql = @"select * from Student;";
using (conn)
{
using (SqlCommand cmd = newSqlCommand(sql, conn))
{
if (conn.State ==System.Data.ConnectionState.Closed)
{
conn.Open();
}
//在这里读取
SqlDataReader reader =cmd.ExecuteReader();
using (reader)
{
// 判断是否有数据
if (reader.HasRows)
{
//循环读取
while(reader.Read())
{
Console.Write(reader.IsDBNull(0) ? "0" : reader.GetInt32(0) +"\t");
Console.Write(reader.IsDBNull(1)? "0" : reader.GetString(1));
Console.WriteLine();
}
}
}
}
}
Console.ReadKey();
}
#endregion
11、SQL注入漏洞攻击
l 对于用户登录的实现，提供SQL语句
• select * from 表名 where uid=… and pwd=…
• 使用字符串拼接
l 提供密码为：’or’1’=’1
l 防范注入漏洞攻击的方法就是使用参数查询
12、查询参数
l 提供SqlParameter对象处理参数比较
l SqlParameter提供两个参数
• 参数别名
• 参数值
l SqlCommand提供一个Parameter属性
• 该属性表示SqlParameter的集合
• 使用Add方法添加（记得new）
• AddWithValue方法
• 不使用要清空
l SQL语句中直接使用参数别名
• - 参数在SQLServer内部不是简单的字符串替换
• SQLServer直接用添加的值进行数据比较
l 使用详注：
1）对于查询，可以使用SQL Server Profiler查看
2）SQL语句使用@UserName表示“此处用参数代替”，向SqlCommand的Parameters中添加参数
cmd.CommandText ="select * from [user] where uUserName=@UserName and uPwd=@Password";
cmd.Parameters.Add(newSqlParameter(“@ UserName ","admin"));
cmd.Parameters.Add(newSqlParameter(“@ Password ",password));
3）参数在SQLServer内部不是简单的字符串替换，SQLServer直接用添加的值进行数据比较，因此不会有注入漏洞攻击。
13、DataSet(ado.net断开式数据访问)
l DataSet是数据集，是一个临时数据库
• 多层数据中常用
• 现在多用List<T>强类型
l 建数据集 -> 建表 -> 建列结构 -> 附加
• 设置列属性：自增、是否允许为空
l 添加数据（创建行对象）
• 创建行 -> 加行到DataSet
l 遍历数据集（循环与集合与索引结构）
l 显示在DataGrideView（DataSource属性）
附：随机练习代码：
//手动创建一个DataSet
DataSet ds = new DataSet("MyDataSet");
//数据集中有表的集合，所有手动创建一张表
DataTable dt = new DataTable("MyTable");
//对表中的列进行初始化
DataColumn dcId = new DataColumn("id", typeof(int));
DataColumn dcName = new DataColumn("name",typeof(string));
DataColumn dcAge = new DataColumn("age",typeof(int));
DataColumn dcGender = new DataColumn("gender",typeof(string));
//将初始化的列加到表中
dt.Columns.Add(dcId);
dt.Columns.Add(dcName);
dt.Columns.Add(dcAge);
dt.Columns.Add(dcGender);
//手动创建一个行
DataRow dr = dt.NewRow();
DataRow dr1 = dt.NewRow();
//往行里面添加数据，或读取数据
dr["id"] = 1;
dr["name"] = "张三";
dr["age"] = 18;
dr["gender"] = "男";
//该行数据加在dt这张表中
dt.Rows.Add(dr);
//将创建的表加到DataSet中
ds.Tables.Add(dt);
14、SqlDataAdapter
l 将数据库中的数据一次性提出放至DataSet
• 创建一个DataSet
• 创建一个SqlDataAdapter
• Fill方法取数据
l 保存修改数据
• SqlCommandBuilder对象（以主键定位）
• Update方法
附：随机练习代码：
//创建一个SqlDataAdapter对象
SqlDataAdapter sa = new SqlDataAdapter(@"selectcityID,cityName,proID from city",@"server=(local);database=DBPromary;uid=sa;pwd=123456");
//创建一个DataSet
DataSet ds = new DataSet();
//使用using以便及时释放资源
using (sa)
{
sa.Fill(ds);
}
//将数据保存
ds.Tables[0].WriteXml("456.xml");
-----------------------------------------------------------------------------------------------------------
//通过dataGridView控件更新数据
//根据操作生成动态的SQL语句等
SqlCommandBuilder scb = new SqlCommandBuilder(sa);
//执行刚刚生成的SQL语句
sa.Update(ds.Tables["myTable"]);
//Update时填充ds.Tables["myTable"]的SqlDataAdapter资源需保持，不能Dispose掉
---------------------- ASP.Net+Android+IOS开发、.Net培训、期待与您交流！ ----------------------