How do asp. net SqlParameter add parameters selectively based on conditions
- 2020-12-19 20:58:16
- OfStack
SqlParameter add, delete, change, check statement with parameter to prevent injection. Sometimes, when writing sql statements, the parameters of the where condition in sql statements are determined by the parameters passed in by the method.
1 a method
DAL layer method
Now you want to add the SqlParameter parameter based on the property within the collection UserInfo
Methods the following
DAL layer method
DBUtility layer SqlHelper
1 a method
DAL layer method
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;
}
Now you want to add the SqlParameter parameter based on the property within the collection UserInfo
Methods the following
DAL layer method
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() Method will be List<T> Copy the elements of the.
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
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 null parameter groups
//params It is allowed to pass null parameter groups
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;
}
}