ADO.NET+存储过程

实现思路

1. 先将SQL业务逻辑在SQL中编辑好

2. 在CMD中设置CMD的格式类型为存储过程类型

3. SQL语句使用存储过程名称，参数和存储过程中参数一致

   CREATE PROC AdminLog
   @Id INT,
   @Pwd VARCHAR(20)
   AS
   SELECT * FROM Admins WHERE LoginId=@Id AND LoginPwd=@Pwd
   EXEC AdminLog 1000,'123456'

   /// <summary>
           /// 查询结果用DataReader读取
           /// </summary>
           /// <param name="procName">存储过程的名称</param>
           /// <param name="parameters">SQL语句中的所有参数</param>
           /// <returns></returns>
           public static SqlDataReader GetReaderByPROC(string procName, SqlParameter[] parameters)
           {
               SqlConnection con = new SqlConnection(constr);
               SqlCommand cmd = new SqlCommand();
               cmd.Connection = con;
               //调用存储过程
               cmd.CommandType = CommandType.StoredProcedure;
               cmd.CommandText = procName;
               if (parameters != null)
               {
                   cmd.Parameters.AddRange(parameters);//将SQL语句中的所有参数对象接收
               }
               try
               {
                   con.Open();
             //不需要手动关闭con，当DataReader关闭时，con自动跟着关闭
                   return cmd.ExecuteReader(CommandBehavior.CloseConnection);
               }
               catch (Exception ex)
               {
                   con.Close();
                   //记入系统日志
                   throw ex;
               }
           }

   public Admins GetAdmins(Admins adm)
           {
               string procName = "AdminLog";
               SqlParameter[] parameters =
               {
                   new SqlParameter("@id", System.Data.SqlDbType.Int),
                   new SqlParameter("@Pwd", System.Data.SqlDbType.VarChar,50)
               };
               parameters[0].Value = adm.LoginId;
               parameters[1].Value = adm.LoginPwd;
               SqlDataReader reader = DBHelper.SQLHelper.GetReaderByPROC(procName,parameters);
               Admins use = null;
               while (reader.Read())
               {
                   use = new Admins()
                   {
                       AdminName = reader["AdminName"].ToString(),
                       LoginId = Convert.ToInt32(reader["LoginId"]),
                       LoginPwd = reader["LoginPwd"].ToString()
                   };
               }
               reader.Close();
               return use;
           }