asp.net SqlParameter如何根据条件有选择的添加参数
一般方法
DAL层方法
publicUserInfoGetAll(UserInfoa) { stringstrSql="selectid,name,code,passwordfrom[tb].[dbo].[User]where1=1"; strSql+="and[id]=@id"; strSql+="and[name]=@name"; strSql+="and[code]=@code"; strSql+="and[password]=@password"; SqlParameter[]parameters={ newSqlParameter("@id",a.id) newSqlParameter("@name",a.name) newSqlParameter("@code",a.code), newSqlParameter("@password",a.password) }; SqlDataReaderreader=SqlHelper.ExecuteReader(strSql,parameters); UserInfohc=newUserInfo(); 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(); returnhc; }
现在想根据集合UserInfo内属性来添加SqlParameter参数
方法如下
DAL层方法
publicUserInfoGetALL(UserInfoa) { stringstrSql="selectid,name,code,passwordfrom[tb].[dbo].[User]where1=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=newList<SqlParameter>(); if(a.id>0)parametertemp.Add(newSqlParameter("@id",a.id)); if(!string.IsNullOrEmpty(a.name))parametertemp.Add(newSqlParameter("@name",a.name)); if(!string.IsNullOrEmpty(a.code))parametertemp.Add(newSqlParameter("@code",a.code)); if(!string.IsNullOrEmpty(a.password))parametertemp.Add(newSqlParameter("@password",a.password)); SqlParameter[]parameters=parametertemp.ToArray();//ToArray()方法将List<T>的元素复制到新数组中。 SqlDataReaderreader=SqlHelper.ExecuteReader(strSql,parameters); UserInfohc=newUserInfo(); 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(); returnhc; }
DBUtility层SqlHelper
publicSqlDataReaderExecuteReader(stringquery,paramsSqlParameter[]parameters) { SqlConnString=GetConnect2(); SqlConnString.Open(); SqlCommandSqlCmd=newSqlCommand(); SqlCmd.Connection=SqlConnString; SqlCmd.CommandText=query; //SqlCmd.Parameters.AddRange(parameters);//AddRange()不能传空参数组 //params的意思就是允许传空参数组 foreach(SqlParameteriteminparameters) { SqlCmd.Parameters.Add(item); } SqlDataReaderdr; try { dr=SqlCmd.ExecuteReader(CommandBehavior.CloseConnection); returndr; } catch(Exceptionee) { SqlConnString.Close(); throwee; } }