数据库——存储过程（C#实现）

创建一个存储过程
 CREATE PROC QueryEmployee(@name NVARCHAR(50),@countEmp INT OUTPUT)
 AS
 BEGIN
 DECLARE @returnValue INT
       BEGIN TRAN M
 SELECT @countEmp=COUNT(*) FROM Customer
 SELECT * FROM Customer WHERE CustomerName LIKE '%'+@name+'%'
 SELECT @returnValue=@@ROWCOUNT--@@RowCount返回上次处理数据库的行数
 RETURN @returnValue
 END
执行一个存储过程
 /*第一种方式*/
 DECLARE @COUNT INT ,@RETURNV INT
 EXEC @RETURNV= QueryEmployee '4',@COUNT OUT
 SELECT @RETURNV
 SELECT @COUNT
 /*第二种方式*/
 DECLARE @COUNT INT ,@RETURNV INT
 EXEC @RETURNV= QueryEmployee @NAME= '4',@countEmp=@COUNT OUT
 SELECT @RETURNV
 SELECT @COUNT
 在VS中执行存储过程的时候:
        public List<EmployeeInfo> TestProc(string empName, out int empCount, out int findEmpCount)
        {

            string cmdText = "QueryEmployee";
            using (SqlConnection conn = new SqlConnection(DBSetting.SqlConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(cmdText, conn);
                cmd.CommandType = CommandType.StoredProcedure;//设置执行存储过程
                //普通参数
                cmd.Parameters.Add(new SqlParameter("@name", SqlDbType.NVarChar, 50));
                cmd.Parameters["name"].Value = empName;
                //输出参数
                cmd.Parameters.Add(new SqlParameter("@countEmp", SqlDbType.Int));
                cmd.Parameters["@countEmp"].Direction = ParameterDirection.Output;//设置输出参数
                //返回值参数
                cmd.Parameters.Add(new SqlParameter("@returnValue", SqlDbType.Int));
                cmd.Parameters["@returnValue"].Direction = ParameterDirection.ReturnValue;//设置返回值

                List<EmployeeInfo> list = new List<EmployeeInfo>();
                using (SqlDataReader rdr = cmd.ExecuteReader())
                {
                    while (rdr.Read())
                    {
                        EmployeeInfo info=new  Employee();
                        info.ID=new Guid(rdr["EmployeeID"].ToString());//将Guid强制类型转换
                        info.name=rdr["Name"].Tostring();
                        list.Add(info);
                    }
                    empCount = (int)cmd.Parameters["@countEmp"].Value;
                    findEmpCount = (int)cmd.Parameters["@returnValue"].Value;
                }
            }
        }