asp.net SqlParameter 根据条件 有选择的添加参数

SqlParameter带参数的增删改查语句,可以防止注入.有时候写sql语句的时候会根据方法传进来的参数来判断sql语句中where条件的参数.

一般方法

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

现在想根据集合UserInfo内属性来添加SqlParameter参数

方法如下

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> 的元素复制到新数组中。                        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()不能传空参数组//params 的意思就是允许传空参数组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; } }