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