快捷導(dǎo)航

asp.net SqlParameter如何根據(jù)條件有選擇的添加參數(shù)

更新時(shí)間：2014年06月06日 10:25:35 作者：

有時(shí)候?qū)憇ql語句的時(shí)候會根據(jù)方法傳進(jìn)來的參數(shù)來判斷sql語句中where條件的參數(shù)，下面有個(gè)示例，大家可以參考下

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

一般方法

DAL層方法

復(fù)制代碼代碼如下:

 
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)在想根據(jù)集合UserInfo內(nèi)屬性來添加SqlParameter參數(shù)

方法如下

DAL層方法

復(fù)制代碼代碼如下:

 
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> 的元素復(fù)制到新數(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

復(fù)制代碼代碼如下:

 
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; 
} 
} 