SQL Filteration

There are 2 method to form a filteration SQL Statment with valueable or nullable parameter.The [Country] is must filter object and [City] is an optional filter object:


METHOD 1 

1. All the Paramater is always added [Con]

const string SQL_STATEMENT =

                "SELECT COUNT(1) FROM [dbo].[Customer] " +

                "Where [Country]= @Country{0}";

string filter = (city != “”) ? " AND [City]=@City" : String.Empty;

          

Database db = DatabaseFactory.CreateDatabase(BULKSETTINGS_CONNECTIONNAME);

              

using (DbCommand cmd = db.GetSqlStringCommand(String.Format(SQL_STATEMENT, filter)))

{

db.AddInParameter(cmd, "@City ", DbType.String, city);

db.AddInParameter(cmd, "@Country ", DbType.String, country);

    result = Convert.ToInt32(db.ExecuteScalar(cmd));

}

---

METHOD 2 

1. Only Filter onject have to add param
2. need to form SQL Statment 1st and change it by cmd.CommandText

const string SQL_STATEMENT =

                "SELECT COUNT(1) FROM [dbo].[Customer] " +

                "{0}";

Database db = DatabaseFactory.CreateDatabase(CONNECTION_NAME);

using (DbCommand cmd = db.GetSqlStringCommand(SQL_STATEMENT))

{

   string filter = string.Empty;

   db.AddInParameter(cmd, "@Country", DbType.AnsiString, country);

   filter += "[Country]=@Country ";

   if (!string.IsNullOrWhiteSpace(prefix))

   {

      db.AddInParameter(cmd, "@City ", DbType.AnsiString, city);

      filter += “AND [City]=@City ";

   }

   if (!string.IsNullOrWhiteSpace(filter))

        filter = "WHERE " + base.FormatFilterStatement(filter);

cmd.CommandText = string.Format(SQL_STATEMENT, filter);

---

SUPPORT METHOD

To Remve AND OR word in 1st word of  filteration sql statment

protected string FormatFilterStatement(string filter)

{

   return Regex.Replace(filter, "^(AND|OR)", string.Empty);

}