ADO.NET命令执行方式比较
使用完整的SQL语句
sql 语句编写困难,容易出错
网络传输不安全,很容易发生“注入式攻击”
执行效率低
使用带参数的SQL语句
sql 语句编写叫容易
网络传输安全
执行效率一般
调用存储过程
不需要编写SQL语句
网络传输非常安全
执行效率非常高
带参数SQL语句与一般SQL语句写法比较
public int AddStudent(Student objStudent)
{
//[1]编写SQL语句 -- 使用占位符的SQL语句
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("insert into Students(StudentName,Gender,Birthday,");
sqlBuilder.Append("StudentIdNo,PhoneNumber,StudentAddress,ClassId)");
sqlBuilder.Append(" values('{0}',{'1}','{2}','{3}','{4}','{5}',{6});select @@identtity");
//[2]解析对象
string sql = string.Format(sqlBuilder.ToString(),objStudent.StudentName,
objStudent.Gender,objStudent.Birthday,
objStudent.StudentIdNo,objStudent.PhoneNumber,
objStudent.StudentAddress,objStudent.ClassId);
try
{
//[3]执行sql语句,返回结果
return Convert.ToInt32(SQLHelper.GetSingleResult(sql));
}
catch(SqlException ex)
{
throw new Exception("数据库操作出现异常!具体信息:"+ex.Message);
}
catch(Exception ex)
{
throw ex;
}
}
带参数SQL语句与一般SQL语句写法比较
public int AddStudent(Student objStudent)
{
StringBuilder sqlBuilder = new StringBuilder();//使用参数的SQL语句
sqlBuilder.Append("insert into Students(StudentName,Gender,Birthday,");
sqlBuilder.Append("StudentIdNo,PhoneNumber,StudentAddress,ClassId)");
sqlBuilder.Append(" values(@StudentName,@Gender,@Birthday,");
sqlBuilder.Append("@StudentIdNo,@PhoneNumber,@StudentAddress,@ClassId)");
SqlParameter[] parameters = new SqlParameter[]{//创建参数数组
new SqlParameter("@StudentNumber",objStudent,StudentName),
new SqlParameter("@Gender",objStudent,Gender),
new SqlParameter("@Birthday",objStudent,Birthday),
new SqlParameter("@StudentIdNo",objStudent,StudentIdNo);
new SqlParameter("@PhoneNumber",objStudent,PhoneNumber);
new SqlParameter("@StudentAddress",objStudent,StudentAddress);
new SqlParameter("@ClassId",objStudent,ClassId);
};
try
{//调用带参数的方法
return Convert.ToInt32(SQLHelper.Update(sqlBuilder.ToString(),parameters));
}
catch(SqlException ex)
{
throw new Exception("数据库操作出现异常!具体信息:"+ex.Message);
}
catch(Exception ex)
{
throw ex;
}
}
编写带参数的通用Update方法
public static int Update(string sql,SqlParameter[] parameters)
{//重载Update方法
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql,conn);
try
{
conn.Open();
cmd.Parameters.AddRange(parameters);//添加参数
int result = cmd.ExecuteNonQuery();
return result;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
编写数据库端存储过程
use StudentManager
go
if exists(select * from sysobjects where name='usp_AddStudent')
drop procedure usp_AddStudent
go
create procedure usp_AddStudent
@StudentName varchar(20),
@Gender char(2),
@Birthday smalldatetime,
@StudentIdNo numeric(18,0),
@PhoneNumber varchar(50),
@StudentAddress varchar(500),
@ClassId int
as
insert into Students(StudentName,Gender,Birthday,
StudentIdNo,PhoneNumber,StudentAddress,ClassId)
values(@studentName,@Gender,@Birthday,
@StudentIdNo,@PhoneNumber,@StudentAddress,ClassId)
go
编写调用存储过程的通用数据访问方法
public static int UpdateByProcedure(string proceureName,SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoredProcedure;//声明当前调用的是存储过程
cmd.CommandText = procedureName;//存储过程名称
cmd.Parameters.AddRange(parameters);//添加输入参数
int result = cmd.ExecuteNonQuery();
return result;
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
}
}
编写调用存储过程的DAO方法
public int AddStudent(Student objStudent)
{
SqlParameter[] parameters = new SqlParameter[]{
new SqlParameter("@studentName",objStudent.StudentName),
new SqlParameter("@Gender",objStudent.Gender),
new SqlParameter("@Birthday",objStudent.Birthday),
new SqlParameter("@StudetnIdNo",objStudent.StudetnIdNo),
new SqlParameter("@PhoneNumber",objStudent.PhoneNumber),
new SqlParameter("@StudentAddress",objStudent.StudStudentAddressntName),
new SqlParameter("@ClassId",objStudent.ClassId)
};
try
{
return Convert.ToInt32(SQLHelper.UpdateByProcedure("usp_AddStudent",parameters);
}
catch(SqlException ex)
{
throw new Execption("数据库操作出现异常!具体信息:\r\n"+ex.Message);
}
catch(Exception ex)
{
throw ex;
}
}
存储过程编写
create procedure usp_ScoreQuery
@className varchar(20),
@stuCount int output,
@avgCSharp int output,
@avgDB int output,
@absectCount int output
as
if(len(@className)=0)--查询全部
begin
--查询全校考试成绩列表
select Students.StudentId,StudentName,ClassName,CSharp,SQLServerDB from Students
inner join StudentClass on StudentClass.ClassId = Students.ClassId
inner join ScoreList on ScoreList.StudentId = Students.StudentId
--查询没有参加考试的学生名单
select StudentName frorm Studnts where StudentId not in(select StudentId from ScoreList)
--输出考试统计信息(学生总数,C#平均分,数据库平均分,缺考总数)
select @stuCount=count(*),@avgCSharp=avg(CSharp),@avgDB=avg(SQLServerDB) from ScoreList
select @absectCount=count(*) form Students where StudentId not in(select StudentId from ScoreList)
end
else --根据班级查询
begin
--根据班级名称获取编辑ID
declare @Classid int
select @ClassId=ClassId from StudnetClass where ClassName=@ClassName
--按照班级产讯考试成绩列表
select Studnets.StudnetId,StudentName,ClassName,CSharp,SQLServerDB from Studnets
inner join StudentClass on StudnetClass = Studnets.ClassId
inner join ScoreList on ScoreList.StudentId = Students.ClassId
where StudentClass.CLass=@ClassId
--查询没有参加考试的学生名单
select StudentName from Students
where StudentId not in(select StudentId from ScoreList) and ClassId=@ClassID
--输出考试统计信息(学生总数,C#平均分,数据库平均分,缺考总数)
select @stuCount=count(*),@avgCSharp=avg(CSharp),@avgDB=avg(SQLServerDB) from ScoreList
inner join Students on Students.StudentId = ScoreList.StudentId
inner join StudentClass on StudentClass.ClassId = Students.Classid
where StudentClass.ClassId = @ClassId
select @absectCount=count(*) from Students
where StudentId not in(select StudentId from ScoreList) and ClassId = @ClassId
end
go
--测试存储过程
declare @stuCOunt int,@absentCountint,@avgCSharpint,@avgDB int
exec usp_QueryScore '计算机4班',@stuCountoutput,@absentCountoutput,@avgCSharpoutput,@avgDB output
print '参考总人数:' + convert(varchar(20),@stuCount)
print '缺考总人数:' + convert(varchar(20),@absentCount)
print 'C#平均分:' + convert(varchar(20),@avgCSharp)
print 'DB平均分:' + convert(varchar(20),@avgDB)
select * from StudentClass
编写通用数据查询方法
//存储过程名称 , 参数数组
public static SqlDataReader GetReaderByProcedure(string procedureName,SqlParameter[] parameters)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandType = CommandType.StoreProcedure;
cmd.CommandText = procedureName;
cmd.Parameters.AddRange(parameters);
SqlDataReader objReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return objReader;
}
catch(Exception ex)
{
conn.Close();
throw.ex;
}
}
Dao类中查询方法编写
public List<StudentExt> GetScoreInfo(string className,out Dictionary<string,string> dicParam, out List<string> absectList)
{
//1.定义参数
SqlParameter intputClassName = new SqlParameter("@className",className);
intputClassName.Direction = ParameterDirection.Input;//设置参数为"输入"类型
SqlParameter outputStuCount = new SqlParameter("@stuCount",SqlDbType.Int);
outputStuCount.Direction = ParameterDirection.Output;
//设置参数为"输出"类型
SqlPartmeter outputAvgCSharp = new SqlParameter("@avgCSharp",SqlDbType.Int);
outputAvgCSharp .Direction = ParameterDirection.Output;
SqlPartmeter outputAvgDB = new SqlParameter("@avgDB",SqlDbType.Int);
outputAvgDB .Direction = ParameterDirection.Output;
SqlPartmeter outputAbsectCount = new SqlParameter("@absectCount",SqlDbType.Int);
outputAvgDB .Direction = ParameterDirection.Output;
//2.执行查询
SqlParameter[] parameter = new SqlParameter[]{
intputClassName,outputStuCount,outputAvgCSharp,outputAvgDB,outputAbsectCount
};
SqlDataReader objReader = SQLHelper.GetReaderByProcedure("usp_ScoreQuery",parameter);
//3.读取考试成绩列表
List<StudentExt> list = new List<StudentExt>();
while(objReader.Read())
{
list.Add(new StudentExe()
{
StudentId = Convert.ToInt32(objReader["StudentId"]),
StudentName = objReader["StudentName"].ToString(),
Classname = objReader["ClassName"].ToString(),
CSharp = objReader["CSharp"].ToString(),
SQLServerDb = objReader["SQLServerDB"].ToString()
});
}
//4.读取缺考人员列表
List<string> absentName = new List<string>();
if(objReader.NextResult())
{
while(objReader.Read())
{
absentName.Add(objReader["StudentName"].ToString());
}
}
//5.关闭读取器(必须在读取输出参数之前关闭)
objReader.Close();
//6.获取输出参数的值
Dictionary<string,string> outDicParam = new Dictionary<string,string>();
outDicParam["stuCount"] = outputStuCount.Value.ToString();
outDicParam["avgCSharp"] = outputAvgCSharp.Value.ToString();
outDicParam["avgDB"] = outputAvgDB.Value.ToString();
outDicParam["absentCount"] = outputAbsectCount.Value.ToString();
//7.返回输出参数和结果
dicParam = outDicParam;
absectList = absentName;
return list;
}
编写UI中的查询事件
private void Query(string className)
{
//定义输出参数(相对于后台调用来说)
Dictionary<string,string> dicParam = null;
List<string> absectList = null;
//执行查询(注意:输出参数在C#中如何定义)
this.dgvScoreList.DataSource = objScoreDao.GetScoreInfo(className,out dicParam,out absectList)
//同步显示班级考试信息
this.gbStat.Text = "[" + this.cboClass.Text.Trim()+"]考试成绩统计";
this.lblAttendCount.Text = dicParam["stuCount"];
this.lblCSharpAvg.Text = dicParam["avgCSharp"];
this.lblDBAvg.Text = dicParm["avgDB"];
this.lblCouont.Text = dicParam["absentCount"];
//显示缺考人员姓名
this.lblList.Items.Clear();
if(absectList.Count == 0)
{
this.lblList.Item.Add("没有缺考");
}
else
{
lblList.Items.AddRange(absectList.ToArray());
}
}
//根据班级或学号查询
private void cboClass_SelectIndexChanged(object sender,EventArgs e)
{
if(this.cboClass.SelectIndex == -1)
{
MessageBox.Show("请首先选择要查询的班级","查询提示");
return;
}
this.dgbScoreList.AutoGenerateColumns = false;
Query(this.cboClass.Text.Trim());
}
//统计全校考试成绩
private void btnStat_Click(object sender,EventArgs e)
{
Query("");
this.gbStat.Text = "全校考试成绩统计";
}
使用数据库连接池提高访问效率
每当用户请求一次,都回重新建立物理连接
不同用户的操作,使得物理链接不断的被建立和关闭
数据库物理链接的建立非常耗费实践
数据库允许同时建立的链接个数是有限的
减少物理链接可以有效减少数据响应实践
web应用程序此问题更加突出
理解连接池的概念
连接池是Data Provider 提供的一个机制,使得应用程序使用的链接保存在连接池里,而避免每次都要完成建立、关闭物理链接的完整过程
应用程序打开和关闭链接时,一般只需要和链接池“打交道”
如何创建连接池?
<add name="connString" connectionString = "Server=.;DataBase=SS;Uid=sa;Pwd=sa;
Pooling=true;Max pool size=10;Min pool size=5"/>
连接池中的代码解释
Pooling = true;表示启用连接池
Max Pool Size = 10;表示连接池里面最大允许的连接数(可以自定义)
Min Pool Size = 5;表示连接池里面最小的连接数,当第一次访问数据库的时候会马上创建5个连接,以后根据并发的需要自动增加连接数,但最多不超过规定的10个连接,如果超过10个并发请求的时候,则在连接池之外创建连接对象,在连接池以外创建的连接关闭后,会释放连接资源。
连接池的使用
注意的问题
在WinForm程序中,要求连接字符串必须完全一样,这样不同应用程序可以共性数据库中的连接池的连接对象,如果应用程序的连接字符串不同,则数据库为给每个程序创建一个连接池,一定要避免这样做
在Web应用程序中,因为连接字符串在服务器端只有一个,所以不会出现上面的情况
连接池连接的使用情况
public static int Update(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql,conn);
try
{
conn.Open();
1.当使用连接池以后,执行Open()方法的时候,系统会从连接池中提取一个现有的连接对象过来,这时候打开的是一个逻辑连接
2.如果连接池中的连接对象都被占用了,则会创建一个新连接对象
return cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
throw ex;
}
finally
{
conn.Close();
1.当使用Close()方法不安比连接的时候,系统会把连接对象放回到连接池,这时候关闭的是一个逻辑连接
2.如果是独立创建的对象,则会被GC释放掉
}
}
须知后事如何,请听下回分解........