SqlDataSource Question

[mrkan]

Using control SqlDataSource in .NET 2.0, how do I search for parameter when it is provided, and return all results when parameter is not provided.

For example:
On my page I have TextBoxSearch. If user type something in, and then click search button, I would like to perform something like: SELECT * FROM Customers WHERE LastName = @SearchParam in my datasource. If TextBoxSearch is empty I want all results from select statement returned: exp. SELECT * FROM Customers

How do I do this?

I've seen one example where ConvertEmptyStringToNull for parameter is used, then statement is constructed something like this: SELECT * FROM Customers WHERE LastName = IsNull(@SearchParam, LastName). Is this proper way?

 

[jbenson001]

I would suggest not using the DataSource objects as they have limitations. When writing your own code, debugging is easier and you have more control. You should be using stored procedures with parameters.

 

[jmeckley]

all data source objects are evil! (obviously a personal opinion ) like jbenson001 stated; create the query in code it allows for code reuse, easier debugging and automated unit testing.

There is a debate between dynamic sql vs stored procs older than time. with the advent of parameterized queries i go with dynamic sql. I can unit test the creation of sql statements and take advantage of ORM tools.

example

MyTextBox.Text = GetData();

private string GetData()
{
   using(IDbConnection cnn = new SqlConnection())
   {
      IDbCommand cmd = cmm.CreateCommand();
      cmd.text = "select [field] from [table] where id=@id";
      cmd.CreateParameter("@id").Value = 1;
      return (string)cmd.ExecuteScalar();
   }
}

i use a scalar value here because your returning a single value, (one row, one column). if i was returning multiple rows or columns I would use ExecuteReader().

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mrkan]

Thanks jmeckley,

Thanks for advice. We are now getting to my question. I like idea of using parameters versus constructing of SQL statement old way (or my way) exp. ... "select ... from where id =" + id;

But once I add parameter, how I remove it if I don't need it. Let's say in your example @id is provided and you are returning single value. But what if @id is not provided and you want to return all values, or you just want to ignore @id . What do you do then?

 

[jmeckley]

then construct a different command object. in it's crudest form

MyTextBox.Text = GetData();

private string GetData()
{
   int id = GetIdFromSomewhere();
   using(IDbConnection cnn = new SqlConnection())
   {
      IDbCommand cmd = cmm.CreateCommand();
      if(id > 0)
      {
         cmd.Text = "select top 1 [field] from [table] where id=@id";
         cmd.CreateParameter("@id").Value = 1;
      }
      else
      {
         cmd.Text = select top 1 [field] from [table]
      }
      return (string)cmd.ExecuteScalar();
   }
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

 

[mrkan]

Thanks, for some reason I thought there is some mechanism provided to do this.