ADO.NET--升阶--使用带参数SQL&存储过程&连接池
用一个学员管理系统的添加学员功能来做一下知识点使用。
第一步:编写存储过程
里面用了事务--Transaction,它单条SQL语句本来就是默认事务,这里只是想显示如何结合使用。
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 --数据库端存储过程编写 2 if exists(select * from sysobjects where name='usp_AddStudent') 3 drop procedure usp_AddStudent 4 go 5 create procedure usp_AddStudent 6 7 @StudentName nvarchar(20),@Gender char(2),@BirthDate datetime,@StudentIdNo char(18), 8 @Age int,@CardNo nvarchar(20),@PhoneNumber nvarchar(50),@Photo text,@HomeAddress nvarchar(20), 9 @ClassId int,@StudentId int output --使用输出参数 10 as 11 declare @errorNum int 12 begin transaction 13 begin 14 insert into Students 15 (StudentName,Gender,Birthday,StudentIdNo,Age,CardNo,PhoneNumber,Photo,HomeAddress,ClassId) 16 values (@StudentName,@Gender,@BirthDate,@StudentIdNo,@Age,@CardNo,@PhoneNumber,@Photo,@HomeAddress,@ClassId) 17 set @errorNum=@errorNum+@@error 18 if(@errorNum>0) 19 rollback transaction 20 else 21 begin 22 commit transaction 23 set @StudentId=@@Identity --获取自增列的值,即学号 24 end 25 end 26 go
第二步:编写SQLHelper
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public static int UpdateBySP(string procedure,SqlParameter[] parameter) 2 { 3 SqlConnection conn = new SqlConnection(connString); 4 SqlCommand cmd = new SqlCommand(); 5 try 6 { 7 conn.Open(); 8 cmd.Connection = conn; 9 cmd.CommandType = CommandType.StoredProcedure;//声明需要调用存储过程 10 cmd.CommandText = procedure; //指明所使用的存储过程 11 cmd.Parameters.AddRange(parameter); 12 int result = cmd.ExecuteNonQuery(); 13 return result; 14 } 15 catch (SqlException ex) 16 { 17 throw new Exception("数据库异常:" + ex); 18 } 19 catch (Exception ex) 20 { 21 throw ex; 22 } 23 finally 24 { 25 conn.Close(); 26 } 27 } 28 }
第三步:根据数据库Student表设计Models-Student类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public class Student 2 { 3 public int StudentId { get; set; } 4 public string StudentName { get; set; } 5 public string Gender { get; set; } 6 public DateTime Birthday { get; set; } 7 public string StudentIdNo { get; set; } 8 public int Age { get; set; } 9 public string CardNo { get; set; } 10 public string PhoneNumber { get; set; } 11 public string Photo { get; set; } 12 public string HomeAddress { get; set; } 13 public int ClassId { get; set; } 14 15 public string ClassName { get; set; } 16 17 }
第四步:编写DAL-StudentsService
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 public int AddStudent(Student objStu) 2 { 3 SqlParameter stuIdParameter = new SqlParameter("StudentId", objStu.StudentId); 4 stuIdParameter.Direction = ParameterDirection.Output;//指定为输出参数 5 SqlParameter[] parameter = new SqlParameter[] 6 { 7 new SqlParameter ("@StudentName",objStu.StudentName), 8 new SqlParameter ("@Gender",objStu.Gender), 9 new SqlParameter ("@BirthDate",objStu.Birthday), 10 new SqlParameter ("@StudentIdNo",objStu.StudentIdNo), 11 new SqlParameter ("@Age",objStu.Age), 12 new SqlParameter ("@CardNo",objStu.CardNo), 13 new SqlParameter ("@PhoneNumber",objStu.PhoneNumber), 14 new SqlParameter ("@Photo",objStu.Photo), 15 new SqlParameter ("@HomeAddress",objStu.HomeAddress), 16 new SqlParameter ("@ClassId",objStu.ClassId), 17 stuIdParameter 18 }; 19 try 20 { 21 int res = SQLHepler.UpdateBySP("usp_AddStudent", parameter); 22 if (res > 0) 23 return Convert.ToInt32(stuIdParameter.Value); 24 else 25 return 0; 26 } 27 catch (Exception ex) 28 { 29 30 throw ex; 31 } 32 } 33 }
第五步:编写UI-添加事件
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 static void Main(string[] args) 2 { 3 Console.WriteLine("键入Y开始添加学员:"); 4 string key = Console.ReadLine(); 5 if (key.Equals("Y")) 6 { 7 Student objStu = new Student() 8 { 9 StudentName = "阿于", 10 Gender="女", 11 Birthday=Convert.ToDateTime("1994-04-13"), 12 StudentIdNo="321284199404131234", 13 Age =24, 14 PhoneNumber="12345678910", 15 Photo="123", 16 HomeAddress="未知", 17 CardNo="12345678", 18 ClassId=1 19 }; 20 int res = new StudentService().AddStudent(objStu); 21 if(res>0) 22 Console.WriteLine("添加成功,学员学号:"+res); 23 else 24 Console.WriteLine("添加失败!"); 25 } 26 Console.ReadLine(); 27 }
看结果:
这里有个问题:
Student里一个属性:Photo为null的时候,去插入会报错:没有Photo参数传入。
这里说明:调用带参数的存储过程,不能传递一个为null的参数给存储过程,其实我们在实际开发的时候也要尽量避免null值的传递。
如果是想要允许null值给存储过程,可以在存储过程里给指定参数赋一个默认值,比如这里:@Photo text=‘123’,经测试这样是不会再报错的。
记录一下连接池的概念与使用
所谓数据库连接池,其实就是我们提前在数据库端建立好几个特定的连接,等待用户的使用;
程序访问时我们直接把已建好的“连接”拿过来直接使用;
等执行完数据议问后,需要把这个连接在放回到连接池中;
等待用户再访问时,可以不断反复使用,这样可以有效的提高数据议问效率。
数据库连接池的创建非常简单,只要学会连接池的字符串编写方法就可以了。
连接池字符串编写:
代码解释:
Pooling=true;表示启用连接池。
Max Pool Size=10;表示连接池里面最大允许的连接数(可以自定)。
Min Pool Size=5;表示连接池里面最小的连接数。
在第一次议问数据库的时候会马上创建 5 个连接,以后根据并发的需要自动增加连接数,但最多不超过规定的10 个连接,如果超过 10 个并发请求的时候,则在连接池之外创建连接对象,在连接池以外创建的连接对象关闭后,会释放连接资源。