How does asp.net SqlParameter add parameters according to conditions?

SqlParameter adds, deletes, modifies, and queries with parameters to prevent injection. Sometimes, when you write an SQL statement, the where condition parameter in the SQL statement is determined based on the parameters passed in by the method.

General Method

DAL Layer Method
Copy codeThe Code is as follows:
Public UserInfo GetAll (UserInfo)
{
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;
}

Now you want to add the SqlParameter parameter based on the attributes in the Set UserInfo.

The method is as follows:

DAL Layer Method
Copy codeThe Code is as follows:
Public UserInfo GetALL (UserInfo)
{
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 (); // The ToArray () method copies the elements of List <T> to 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 codeThe Code is 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); // The AddRange () parameter group cannot be empty.
// Params indicates that the parameter group can be null.
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;
}
}