ADO.NET--升阶--使用带参数SQL&存储过程&连接池

用一个学员管理系统的添加学员功能来做一下知识点使用。

第一步：编写存储过程

里面用了事务--Transaction，它单条SQL语句本来就是默认事务，这里只是想显示如何结合使用。

--数据库端存储过程编写
if exists(select * from sysobjects where name='usp_AddStudent')
        drop procedure usp_AddStudent
go
create procedure usp_AddStudent
    
        @StudentName nvarchar(20),@Gender char(2),@BirthDate datetime,@StudentIdNo char(18),
        @Age int,@CardNo nvarchar(20),@PhoneNumber nvarchar(50),@Photo text,@HomeAddress nvarchar(20),
        @ClassId int,@StudentId int output --使用输出参数
as
        declare @errorNum int
        begin transaction
            begin
            insert into Students
            (StudentName,Gender,Birthday,StudentIdNo,Age,CardNo,PhoneNumber,Photo,HomeAddress,ClassId)
            values            (@StudentName,@Gender,@BirthDate,@StudentIdNo,@Age,@CardNo,@PhoneNumber,@Photo,@HomeAddress,@ClassId)
             set @errorNum=@errorNum+@@error
             if(@errorNum>0)
                rollback transaction
            else 
                begin
                    commit transaction
                    set @StudentId=@@Identity --获取自增列的值，即学号
                end
         end
go

 第二步：编写SQLHelper

public static int UpdateBySP(string procedure,SqlParameter[] parameter)
        {
            SqlConnection conn = new SqlConnection(connString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.StoredProcedure;//声明需要调用存储过程
                cmd.CommandText = procedure;                  //指明所使用的存储过程
                cmd.Parameters.AddRange(parameter);
                int result = cmd.ExecuteNonQuery();
                return result;
            }
            catch (SqlException ex)
            {
                throw new Exception("数据库异常：" + ex);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
    }

第三步：根据数据库Student表设计Models-Student类

public class Student
    {
        public int StudentId { get; set; }
        public string StudentName { get; set; }
        public string Gender { get; set; }
        public DateTime Birthday { get; set; }
        public string StudentIdNo { get; set; }
        public int Age { get; set; }
        public string CardNo { get; set; }
        public string PhoneNumber { get; set; }
        public string Photo { get; set; }
        public string HomeAddress { get; set; }
        public int ClassId { get; set; }

        public string ClassName { get; set; }

    }

第四步：编写DAL-StudentsService

 public int AddStudent(Student objStu)
        {
            SqlParameter stuIdParameter = new SqlParameter("StudentId", objStu.StudentId);
            stuIdParameter.Direction = ParameterDirection.Output;//指定为输出参数
            SqlParameter[] parameter = new SqlParameter[]
                {
                    new SqlParameter ("@StudentName",objStu.StudentName),
                    new SqlParameter ("@Gender",objStu.Gender),
                    new SqlParameter ("@BirthDate",objStu.Birthday),
                    new SqlParameter ("@StudentIdNo",objStu.StudentIdNo),
                    new SqlParameter ("@Age",objStu.Age),
                    new SqlParameter ("@CardNo",objStu.CardNo),
                    new SqlParameter ("@PhoneNumber",objStu.PhoneNumber),
                    new SqlParameter ("@Photo",objStu.Photo),
                    new SqlParameter ("@HomeAddress",objStu.HomeAddress),
                    new SqlParameter ("@ClassId",objStu.ClassId),
                    stuIdParameter
                };
            try
            {
                int res = SQLHepler.UpdateBySP("usp_AddStudent", parameter);
                if (res > 0)
                    return Convert.ToInt32(stuIdParameter.Value);
                else
                    return 0;
            }
            catch (Exception ex)
            {

                throw ex;
            }        
        }
    }

第五步：编写UI-添加事件

  static void Main(string[] args)
        {
            Console.WriteLine("键入Y开始添加学员：");
            string key = Console.ReadLine();
            if (key.Equals("Y"))
            {
                Student objStu = new Student()
                {
                    StudentName = "阿于",
                    Gender="女",
                    Birthday=Convert.ToDateTime("1994-04-13"),
                    StudentIdNo="321284199404131234",
                    Age =24,
                    PhoneNumber="12345678910",
                    Photo="123",
                    HomeAddress="未知",
                    CardNo="12345678",
                    ClassId=1
                };
                int res = new StudentService().AddStudent(objStu);
                if(res>0)
                    Console.WriteLine("添加成功，学员学号："+res);
                else
                    Console.WriteLine("添加失败！");
            }
            Console.ReadLine();
        }      

看结果：

这里有个问题：

Student里一个属性：Photo为null的时候，去插入会报错：没有Photo参数传入。

这里说明：调用带参数的存储过程，不能传递一个为null的参数给存储过程，其实我们在实际开发的时候也要尽量避免null值的传递。

如果是想要允许null值给存储过程，可以在存储过程里给指定参数赋一个默认值，比如这里：@Photo text=‘123’，经测试这样是不会再报错的。

记录一下连接池的概念与使用

所谓数据库连接池，其实就是我们提前在数据库端建立好几个特定的连接，等待用户的使用；

程序访问时我们直接把已建好的“连接”拿过来直接使用；

等执行完数据议问后，需要把这个连接在放回到连接池中；

等待用户再访问时，可以不断反复使用，这样可以有效的提高数据议问效率。

数据库连接池的创建非常简单，只要学会连接池的字符串编写方法就可以了。 

连接池字符串编写：

代码解释：

Pooling=true；表示启用连接池。

Max Pool Size=10；表示连接池里面最大允许的连接数（可以自定）。

Min Pool Size=5；表示连接池里面最小的连接数。

在第一次议问数据库的时候会马上创建 5 个连接，以后根据并发的需要自动增加连接数，但最多不超过规定的10 个连接，如果超过 10 个并发请求的时候，则在连接池之外创建连接对象，在连接池以外创建的连接对象关闭后，会释放连接资源。