一、参数化查询的两种书写方式：
SqlParameter[] patameters = { new SqlParameter("@StudentID", SqlDbType.VarChar, 10) };
patameters[0].Value = studentID;
SqlParameter[] pram = new SqlParameter[1];
pram[0] = new SqlParameter("@Student", studentID);
注：个人觉得下面的用来相对简单，因为不需要知道参数的类型和长度
调用方法: 将参数数组和执行的字符串带入其中即可 SelectSql(sSql,pram);
public static DataSet SelectSql(string sSql,SqlParameter[] paramList)
{
SqlConnection mConn = new SqlConnection();
SqlCommand mComm = new SqlCommand();
mConn.ConnectionString = ConnStr;
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
mComm.Connection = mConn;
}
DataSet ds = new DataSet();
mComm.CommandType = CommandType.Text;
mComm.CommandText = sSql;
mComm.Parameters.AddRange(paramList);
try
{
SqlDataAdapter mAdapter = new SqlDataAdapter(mComm);
mAdapter.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
mConn.Close();
return ds;
}
-------------
参数化登录程序：
string sSql = string.Format("select * from " + tblName + " where adminNum=@adminNum and password=@password");
SqlParameter[] pram = new SqlParameter[2];
pram[0] = new SqlParameter("@adminNum", userName);
pram[1] = new SqlParameter("@password", userPwd);
DataSet ds = DBAccess.SelectSql(sSql, pram);
if (ds.Tables[0].Rows.Count > 0)
{
return 1;
}
else
{
return 0;
}
存储过程登录：
public static DataSet SelectByProc(string sProcName, SqlParameter[] paramList)
{
SqlConnection mConn = new SqlConnection();
SqlCommand mComm = new SqlCommand();
mConn.ConnectionString = ConnStr;
if (mConn.State == ConnectionState.Closed)
{
mConn.Open();
mComm.Connection = mConn;
}
DataSet ds = new DataSet();
mComm.CommandType = CommandType.StoredProcedure;
mComm.CommandText = sProcName;
mComm.Parameters.AddRange(paramList);
try
{
SqlDataAdapter mAdapter = new SqlDataAdapter(mComm);
mAdapter.Fill(ds);
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
mConn.Close();
return ds;
}
----调用代码：
SqlParameter[] pram = new SqlParameter[2];
pram[0] = new SqlParameter("@adminNum", userName);//参数与存储过程中的参数相同
pram[1] = new SqlParameter("@password", userPwd);
DataSet ds = DBAccess.SelectByProc("pro_admin", pram);
if (ds.Tables[0].Rows.Count > 0)
{
return 1;
}
else
{
return 0;
}
存储过程：pro_admin="create procedure pro_admin
@adminNum varchar (50),
@password varchar (32)
as
begin
select * from tb_administrator
where adminNum = @adminNum and password=@password
end ":