SqlParameter帶參數(shù)的增刪改查語句,可以防止注入.有時候寫sql語句的時候會根據方法傳進來的參數(shù)來判斷sql語句中where條件的參數(shù).

一般方法

DAL層方法

代碼如下:

public UserInfo GetAll(UserInfo a)

{

string strSql = "select id,name,code,password from [tb].[dbo].[User] where 1=1";

strSql += " and [id]=@id";

strSql += " and [name]=@name";

strSql += " and [code]=@code";

strSql += " and [password]=@password";

SqlParameter[] parameters = {

new SqlParameter("@id", a.id)

new SqlParameter("@name", a.name)

new SqlParameter("@code", a.code),

new SqlParameter("@password", a.password)

};

SqlDataReader reader = SqlHelper.ExecuteReader(strSql, parameters);

UserInfo hc = new UserInfo();

while(reader.Read())

{

hc.id = reader.GetInt32(reader.GetOrdinal("id"));

hc.name = reader.GetString(reader.GetOrdinal("name"));

hc.code = reader.GetString(reader.GetOrdinal("code"));

hc.password = reader.GetString(reader.GetOrdinal("password"));

}

reader.Close();

return hc;

}

現(xiàn)在想根據集合UserInfo內屬性來添加SqlParameter參數(shù)

方法如下

DAL層方法

代碼如下:

public UserInfo GetALL(UserInfo a)

{

string strSql = "select id,name,code,password from [tb].[dbo].[User] where 1=1";

if (a.id>0) strSql += " and [id]=@id";

if (!string.IsNullOrEmpty(a.name)) strSql += " and [name]=@name";

if (!string.IsNullOrEmpty(a.code)) strSql += " and [code]=@code";

if (!string.IsNullOrEmpty(a.password)) strSql += " and [password]=@password";

List<SqlParameter> parametertemp = new List<SqlParameter>();

if (a.id > 0) parametertemp.Add(new SqlParameter("@id", a.id));

if (!string.IsNullOrEmpty(a.name)) parametertemp.Add(new SqlParameter("@name", a.name));

if (!string.IsNullOrEmpty(a.code)) parametertemp.Add(new SqlParameter("@code", a.code));

if (!string.IsNullOrEmpty(a.password)) parametertemp.Add(new SqlParameter("@password", a.password));

SqlParameter[] parameters = parametertemp.ToArray();//ToArray()方法將 List<T> 的元素復制到新數(shù)組中。

SqlDataReader reader = SqlHelper.ExecuteReader(strSql, parameters);

UserInfo hc = new UserInfo();

while (reader.Read())

{

hc.id = reader.GetInt32(reader.GetOrdinal("id"));

hc.name = reader.GetString(reader.GetOrdinal("name"));

hc.code = reader.GetString(reader.GetOrdinal("code"));

hc.password = reader.GetString(reader.GetOrdinal("password"));

}

reader.Close();

return hc;

}

DBUtility層SqlHelper

代碼如下:

public SqlDataReader ExecuteReader(string query, params SqlParameter[] parameters)

{

SqlConnString = GetConnect2();

SqlConnString.Open();

SqlCommand SqlCmd = new SqlCommand();

SqlCmd.Connection = SqlConnString;

SqlCmd.CommandText = query;

//SqlCmd.Parameters.AddRange(parameters);//AddRange()不能傳空參數(shù)組

//params 的意思就是允許傳空參數(shù)組

foreach (SqlParameter item in parameters)

{

SqlCmd.Parameters.Add(item);

}

SqlDataReader dr;

try

{

dr = SqlCmd.ExecuteReader(CommandBehavior.CloseConnection);

return dr;

}

catch (Exception ee)

{

SqlConnString.Close();

throw ee;

}

}

更多信息請查看IT技術專欄