asp.net sqlparameter How to add parameters based on conditional selection _ Practical Tips

SqlParameter with the parameters of Add and remove to check the statement, you can prevent injection. Sometimes the SQL statement is written to determine the parameters of the Where condition in the SQL statement based on the parameters passed in by the method.

General method

Dal Layer Method

Copy Code code as follows:

Public UserInfo GetAll (UserInfo a)
{
String strSQL = "Select Id,na Me,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 Sq Lparameter ("@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;
}

Now you want to add SqlParameter parameters based on the UserInfo attribute in the collection

Methods are as follows

Dal Layer Method

Copy Code code as follows:

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. The ToArray ()//toarray () method copies the elements of the list<t> into the new array.

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 Layer SqlHelper

Copy Code code as follows:

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 () cannot pass empty parameter groups
//params means to allow the null parameter group
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;
}
}