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