asp.net SqlParameter如何根据条件有选择的添加参数
程序员文章站
2024-02-22 17:57:52
sqlparameter带参数的增删改查语句,可以防止注入.有时候写sql语句的时候会根据方法传进来的参数来判断sql语句中where条件的参数. 一般方法 dal层方法...
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;
}
}
一般方法
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;
}
}
上一篇: 详解Java语言中一个字符占几个字节?
下一篇: java Gui实现肯德基点餐收银系统