ADO.NET+存储过程
实现思路
-
先将SQL业务逻辑在SQL中编辑好
-
在CMD中设置CMD的格式类型为存储过程类型
-
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;
}