【下载文档:  asp.net SqlParameter如何根据条件有选择的添加参数.txt 】

---

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 parametertemp = new List(); 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 的元素复制到新数组中。 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; } }