数据库开发(四)ADO.NET操作数据库

作者 Zhendong Ho 日期 2019-05-13
C#
数据库开发(四)ADO.NET操作数据库

ADO.NET介绍

ADO.NET就是一组类库。这组类库可以让我们通过程序的方式访问数据库,就像System.IO下的类操作文件一样,System.Data这组类是用来操作数据库的。它提供了统一的编程接口让操作其他数据库(Access、Oracle等)的方式和操作MsSqlServer一致。

ADO.NET的组成

数据提供程序(常用类)

  • Connection:用来连接数据库。
  • Command:用来执行SQL语句。
  • DataReader:只读、只进的结果集,逐条读取数据(与StreamReader、XmlReader微软的类库类似)。
  • DataAdapter:封装了Connection、Command和DataReader对象。

数据集(DataSet),临时数据库

  • 断开式数据操作。

通过ADO.NET连接数据库

连接数据库的步骤

  1. 创建连接字符串
  2. 创建连接对象
  3. 打开连接(如果打开数据连接没有问题,表示连接成功)
  4. 关闭连接,释放资源

创建连接字符串

大致分为三大类:Data Source、Initial Catalog、Integrated Security。

Data Source:要连接的数据库实例。

Initial Catalog:初始化数据库。

Integrated Security:集成验证方式,为true代表windows身份验证,可以不写数据库用户名密码。

// 采用集成验证方式,不用写数据库用户名和密码
string constr = "Data Source=ZHENDONG-PC;Initial Catalog=MyDatabaseOne;Integrated Security=true";
// 采用身份验证方式,需要写用数据库户名和密码
string constr2 = "Data Source=ZHENDONG-PC;Initial Catalog=MyDatabaseOne;User ID=sa;Password=123";
// 过去的连接字符串(不推荐)
string constr3 = "server=.;database=ZHENDONG-PC;uid=sa;pwd=123";

创建连接对象

// 1.创建连接字符串
string constr = "Data Source=ZHENDONG-PC;Initial Catalog=MyDatabaseOne;Integrated Security=true";
// 2.创建连接对象
using (SqlConnection con = new SqlConnection(constr))
{
// 测试,打开连接
// 3.打开连接
con.Open();
Console.WriteLine("连接打开成功~");
// 4.关闭连接,释放资源
//con.Close(); // SqlConnection类中的Dispose函数会自动调用Close函数
//con.Dispose(); // 这里不需要写Dispose,因为using会自动调用Dispose释放资源
}

向表中插入一条数据

ExecuteNonQuery:执行insert、delete、update的时候使用。这个方法有一个返回值是int类型,表示执行insert、delete、update语句后,所影响的行数。特别注意,ExecuteNonQuery()只有执行insert、delete、update语句的时候会返回所影响的行数,执行其他sql语句永远返回-1。

ExecuteScalar:当执行返回单个结果的时候使用。

ExecuteReader:当查询出多行,多列结果的时候使用。

// 1.连接字符串
string constr = "Data Source=ZHENDONG-PC;Initial Catalog=MyDatabaseOne;Integrated Security=True";
// 2.创建连接对象
using (SqlConnection con = new SqlConnection(constr))
{
// 4.编写sql语句
string sql = "insert into TblPerson values('FFF',24,175,1)";
// 5.创建一个执行sql语句的对象(命令对象)SqlCommand
using (SqlCommand cmd = new SqlCommand(sql, con))
{
// 3.打开连接(连接对象最晚打开,最早关闭。节省资源)
con.Open();
// 6.开始执行sql语句
int r = cmd.ExecuteNonQuery();
Console.WriteLine("成功插入了{0}行数据。", r);
}
}
Console.ReadKey();

删除一条数据

// 1.连接字符串
string constr = "Data Source=zhendong-pc;Initial Catalog=MyDatabaseOne;Integrated Security=True";
// 2.连接对象
using (SqlConnection con = new SqlConnection(constr))
{
// 3.sql语句
string sql = "delete from TblPerson where id = '37'";
// 4.创建SqlCommand对象
using (SqlCommand cmd = new SqlCommand(sql, con))
{
// 5.打开连接
con.Open();
// 6.执行
int r = cmd.ExecuteNonQuery();
Console.WriteLine("成功删除了{0}条数据", r);
}
}
Console.ReadKey();

更新一条数据

string constr = "Data Source=.;Initial Catalog=MyDatabaseOne;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "update Tblperson set age = 25, address = 'address2' where id = '2'";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
int r = cmd.ExecuteNonQuery();
Console.WriteLine("成功更新{0}条数据", r);
}
}
Console.ReadKey();

查询表中的记录条数

string constr = "Data Source=.;Initial Catalog=MyDatabaseOne;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "select count(*) from TblPerson";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
// 聚合函数不会返回null,所以不用判断是否为null
object count = (int)cmd.ExecuteScalar();
Console.WriteLine("TblPerson表中共有{0}条数据", count);
}
}
Console.ReadKey();

获取多行多列数据

通过调用ExecuteReader方法,将给定的sql语句在服务器端执行。

执行完毕后,服务器端就已经查询出了数据。但是数据是保存在数据库服务器的内存当中。并没有返回给应用程序。只是返回给应用程序一个reader对象,这个对象就是用来获取数据的对象。

string constr = "Data Source=.;Initial Catalog=MyDatabaseOne;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "select * from TblPerson";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
// 通过reader对象一条一条获取数据
// 1.在获取数据之前,先判断一下本次执行查询后,是否查询到了数据
if (reader.HasRows)// 如果有数据,则为true,否则为false
{
// 2.一条一条获取数据
// 每次获取数据之前,都要先调用reader.Read()方法,向后移动一条数据,如果成功移动到了某条数据上,则返回true,否则返回false
while (reader.Read())
{
// 获取当前reader指向的数据
// reader.FieldCount,可以获取当前查询语句查询出的列的个数
for (int i = 0; i < reader.FieldCount; i++)
{
// 通过reader[]索引器,可以使用列名来获取列的值
Console.WriteLine(reader[i] + " | ");
Console.WriteLine(reader["autoId"]);

// 根据列名称获取列的索引
int r = reader.GetOrdinal("autoId");// 0

// GetValue()只能通过列索引来获取列的值
Console.WriteLine(reader.GetValue(i) + " | ");

// 通过下面这种方式读取到的数据直接就是对应的类型,不是object类型,使用起来更方便。
//reader.GetXxxxx();
}
Console.WriteLine();
}
}
else
{
Console.WriteLine("没有查询到任何数据");
}
}
}
}

DataReader的特点

  • 只读、只进。只能通过reader读取数据,不能修改。reader只能一条一条向前移动,不能向后,也不能跳跃。
  • 使用reader时必须保证连接是打开状态。当reader使用完毕后,必须把reader关闭,释放。同时关闭释放连接对象。
  • 默认情况下DataReader要求独占一个连接对象。
  • reader有两种方式获取数据。一种是通过GetValue()获取,一种是通过reader[i]索引器获取。索引器可以通过列名获取,而GetValue()只能通过列索引来获取列的值。
  • 可以使用强类型读取列中的数据。如GetXxxxx()。

注意:当遇到数据库中的NULL值的时候,通过reader.GetValue()或者reader[]索引器来获取列的值,拿到的是DBNull.Value,不是C#中的null。而DBNull.Value的ToString()方法返回的是空字符串。

使用强类型读取列种的数据

string constr = "Data Source=.;Initial Catalog=MyDatabaseOne;Integrated Security=true";
using (SqlConnection con = new SqlConnection(constr))
{
string sql = "select * from TblPerson";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
con.Open();
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
// 注意:通过reader.GetXxxxx()方式来获取表中的数据,如果数据为null就会出现异常。此时需要手动写代码来判断数据是否为null
while (reader.Read())
{
// autoId, uName, age, height, gender
Console.Write(reader.GetInt32(0) + "\t\t");
Console.Write(reader.GetString(1) + "\t\t");
Console.Write(reader.GetInt32(2) + "\t\t");

Console.Write(reader.IsDBNull(3) ? "NULL" : reader.GetInt32(3) + "\t\t");
Console.Write(reader.IsDBNull(4) ? "NULL" : reader.GetBoolean(4) + "\t\t");
}
}
else
{
Console.WriteLine("没有查询到任何数据");
}
}
}
}

插入返回自动编号

基本语法:insert into 表 output inserted.要返回的列名 values('列1', '列2', '列3')

insert into TblPerson output inserted.id values('zhdaa', 25, 175, 1) --返回自动编号列id

带参数的SQL语句

SQL注入攻击

所谓SQL注入,就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串,最终达到欺骗服务器执行恶意的SQL命令。

使用SQL拼接的方式(不安全,有SQL注入攻击的问题)

--用户登录,恶意SQL语句
select count(*) from users where loginId = 'abc' or 1 = 1 -- and loginPwd = 'dsafdasg'

如何避免SQL注入攻击:使用带参数的SQL语句或者是存储过程。

使用带参数的SQL语句

string constr = "Data Source=.;Initial Catalog=MyDatabaseOne;Integrated Security=True";
string loginId = "abc";
string password = "pwd";
using (SqlConnection con = new SqlConnection)
{
string sql = "select count(*) from users where loginId=@loginId and loginPwd=@password";
using (SqlCommand cmd = new SqlCommand(sql, con))
{
//1.当使用带参数的sql语句的时候
//1>sql语句会出现参数
//2>如果sql语句中有参数,那么必须在command对象中提供对应的参数和值

//创建两个参数对象(方法一)
/*SqlParameter paramLoginId = new SqlParameter("@loginId", SqlDbType.Varchar, 50) { Value = loginId };
SqlParameter paramPassword = new SqlParameter("@password", SqlDbType.Varchar, 50) { Value = password };
cmd.Parameters.Add(paramLoginId);
cmd.Parameters.Add(paramPassword);*/

//创建参数数组(方法二) 推荐
SqlParameter[] pms = new SqlParameter[] {
new SqlParameter("@loginId", SqlDbType.Varchar, 50) { Value = loginid },
new SqlParameter("@password", SqlDbType.Varchar, 50) { Value = password }
};
//向command对象中增加参数
cmd.Parameters.AddRange(pms);

//方法三
/*cmd.Parameters.AddWithValue("@loginId", loginId);
cmd.Parameters.AddWithValue("@password", password);*/

con.Open();
int r = (int)cmd.ExecuteScalar();
if (r > 0)
{
Console.WriteLine("登录成功!");
}
else
{
Console.WriteLine("登录失败!");
}
}
}

SQL Server探查器

所有对SQL Server数据库引擎进行的操作都可以通过SQL Server探查器监查到。

位置:工具,SQL Server Profiler