ADO.NET数据访问技术入门
时间：2011-04-29 来源：韩锁
ADO.NET访问SQL Server示例
先在Microsoft SQL Server Management Studio中测试一下该T-SQL语句。后面的示例中将以此T-SQL语句来讲解。
select * from employees where employeeid between 2 and 6
那么在利用C#构建应用程序时，如何在代码中完成数据访问功能呢。请看示例代码：
示例代码A：
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlServerProvider
{
class Program
{
static void Main(string[] args)
{
// 设置连接字符串
string connString = @"
Server =.;
Integrated Security=True;
Initial Catalog = Northwind
";
// 设置查询字符串,硬编码方式
string sqlqry = @"select * from employees where employeeid between 2 and 6";
// 声明数据连接和数据读取器对象变量
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
conn = new SqlConnection(connString);
SqlCommand cmdqry = conn.CreateCommand();
cmdqry.CommandType = CommandType.Text;
cmdqry.CommandText = sqlqry;
conn.Open();
reader = cmdqry.ExecuteReader();
Console.WriteLine(
"This program demonstrates the use of "
+ "the SQL Server Data Provider."
);
Console.WriteLine(
"Querying database {0} with query \n{1}\n"
, conn.Database
, cmdqry.CommandText
);
Console.WriteLine("First Name\tLast Name\n");
while (reader.Read())
{
Console.WriteLine(
"{0} | {1}"
, reader["FirstName"].ToString().PadLeft(10)
, reader[1].ToString().PadLeft(10)
);
}
Console.ReadKey();
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.ReadKey();
}
finally
{
// 关闭连接
reader.Close();
conn.Close();
}
}
}
}
程序运行结果：
示例很简单，就是先设置好查询语句，在稍微复杂的程序中也可以动态拼接字符串来构建T-SQL语句，接着设置SqlCommand实例对象的CommandType的属性为SQL文本命令，设置其CommandText属性为构建好的T-SQL语句，然后调用执行命令的方法即可。
这种硬编码的方式是完全有效的，动态拼接T-SQL语句的方式特点是简单易学，但存在一个较大弊端。由于数据库中表的字段类型多种多样，在程序中动态的构建一个复杂的T-SQL语句，包括查询、更新、删除等，是一件不容易控制的事，往往拼接出来的T-SQL语句是不符合T-SQL语法要求的。实际上这种方式执行起来相对低效，并且会因此承担SQL注入攻击的风险。
相对较好的方法是使用命令参数。命令参数在命令文本中起到一个占位符的作用，给将要被替代的值做一个标记。在SQL Server中使用命名参数（named parameters）,这些参数以@符号做前缀，后面是参数名。下面请看使用命令参数的示例：
示例代码B:
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlServerProvider
{
class Program
{
static void Main(string[] args)
{
int iMinValue = 2;
int iMaxValue = 6;
// 设置连接字符串
string connString = @"
Server =.;
Integrated Security=True;
Initial Catalog = Northwind
";
// 设置查询字符串，命令参数方式
string sqlqry = @"select * from employees where employeeid between @MinValue and @MaxValue";
// 声明数据连接和数据读取器对象变量
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
conn = new SqlConnection(connString);
SqlCommand cmdqry = conn.CreateCommand();
cmdqry.Prepare();
cmdqry.Parameters.Add("@MinValue", SqlDbType.Int);
cmdqry.Parameters.Add("@MaxValue", SqlDbType.Int);
cmdqry.CommandType = CommandType.Text;
cmdqry.CommandText = sqlqry;
cmdqry.Parameters["@MinValue"].Value = iMinValue;
cmdqry.Parameters["@MaxValue"].Value = iMaxValue;
conn.Open();
reader = cmdqry.ExecuteReader();
Console.WriteLine(
"This program demonstrates the use of "
+ "the SQL Server Data Provider."
);
Console.WriteLine(
"Querying database {0} with query \n{1}\n"
, conn.Database
, cmdqry.CommandText
);
Console.WriteLine("First Name\tLast Name\n");
while (reader.Read())
{
Console.WriteLine(
"{0} | {1}"
, reader["FirstName"].ToString().PadLeft(10)
, reader[1].ToString().PadLeft(10)
);
}
Console.ReadKey();
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.ReadKey();
}
finally
{
// 关闭连接
reader.Close();
conn.Close();
}
}
}
}
程序运行结果：
可以发现，示例代码A与示例代码B所获取的数据结果是一致的。与示例代码A不同的是，在示例代码B中，首先创建了iMinValue和iMaxValue两个样例数据，然后向SqlCommand对象实例的Parameters集合属性添加@MinValue和@MaxValue这两个参数，最后在执行命令前设置参数的值。其余的都与示例代码A相同。
SqlCommand对象的Parameters集合是CommandText中SQL字符串的参数值来源，SQL不必用到其中的全部参数，甚至可以完全不用，但不能使用Parameters集合外的任何参数。SQL语句中的参数值不是在给Parameters集合中的成员赋值时就被替换，二是在提交至数据库服务器时才会发生替换。
命令参数具备以下优点：
变量及其在SQL中的使用位置的映射关系十分清晰，便于代码阅读和理解。
通过参数可以使用Prepare方法，使得代码运行更快。
上面两种方式是足以应对一般的中小型系统了，但如果从性能上仔细斟酌的话，使用存储过程又显得更胜一筹。存储过程是允许重复执行某个任务的SQL语句，只需要在数据库中创建某个存储过程一次，便可在程序中多次使用。可提高程序的可维护性，允许程序以统一、优化的方式访问数据库。可以创建不带任何参数的存储过程，也可以创建带有输入或输出的参数。默认返回一个整数值0，也可返回一个或多个结果集。
先在Microsoft SQL Server Management Studio中测试一下该T-SQL语句创建存储过程。
CREATE procedure pro_Test
@MinValue int,
@MaxValue int
as
select * from employees where employeeid between @MinValue and @MaxValue
然后利用下面的命令和参数值执行存储过程，结果与本文最开始的查询示例结果相同。
Execute @return = pro_Test
@MinValue=2,
@MaxValue=6
创建好的存储过程可以在程序中被调用，也可以被其它的存储过程所调用。下面请看在程序中调用存储过程的示例。
示例代码C:
using System;
using System.Data;
using System.Data.SqlClient;
namespace SqlServerProvider
{
class Program
{
static void Main(string[] args)
{
int iMinValue = 2;
int iMaxValue = 6;
// 设置连接字符串
string connString = @"
Server =.;
Integrated Security=True;
Initial Catalog = Northwind
";
// 声明数据连接和数据读取器对象变量
SqlConnection conn = null;
SqlDataReader reader = null;
try
{
conn = new SqlConnection(connString);
SqlCommand cmdqry = conn.CreateCommand();
cmdqry.Prepare();
cmdqry.Parameters.Add("@MinValue", SqlDbType.Int);
cmdqry.Parameters.Add("@MaxValue", SqlDbType.Int);
cmdqry.CommandType = CommandType.StoredProcedure;
cmdqry.CommandText = "pro_Test";
cmdqry.Parameters["@MinValue"].Value = iMinValue;
cmdqry.Parameters["@MaxValue"].Value = iMaxValue;
conn.Open();
reader = cmdqry.ExecuteReader();
Console.WriteLine(
"This program demonstrates the use of "
+ "the SQL Server Data Provider."
);
Console.WriteLine(
"Querying database {0} with query \n{1}\n"
, conn.Database
, cmdqry.CommandText
);
Console.WriteLine("First Name\tLast Name\n");
while (reader.Read())
{
Console.WriteLine(
"{0} | {1}"
, reader["FirstName"].ToString().PadLeft(10)
, reader[1].ToString().PadLeft(10)
);
}
Console.ReadKey();
}
catch (Exception e)
{
Console.WriteLine("Error: " + e);
Console.ReadKey();
}
finally
{
// 关闭连接
reader.Close();
conn.Close();
}
}
}
}
程序运行结果：
所获取的数据结果与前两个示例是一致的。示例C只是在示例B的基础上做了很少的改动。因为是调用存储过程，所以不需要设置查询字符串，更改一下SqlCommand实例对象的CommandType属性为存储过程，指定其CommandText属性为存储过程的名称，其余与示例B完全相同。不带参数的存储过程比这更显简单，就不再单独介绍了。
欢迎迈进ADO.NET的大门，里面的世界更精彩!
相关阅读 更多 +
