I have this example code:
string query = "select * from xy where id == @id and name == @name";
SqlCommand cmd = new SqlCommand(query);
if(txtUsername.Text.Length > 0)
{
cmd.Parameters.Add["@name", SqlDbType.VarChar);
cmd.Parameters["@name"].Value = txtUsername.Text;
}
Now if the txtUsername <=0 I have to cut the query string dropping the AND and the name == @name. How can I achieve this result?
Thanks in advance.
bool useName = !String.IsNullOrEmpty(txtUsername.Text);
StringBuilder query = new StringBuilder("select * from xy where id=@id");
if(useName)
query.Append(" AND name=@name");
SqlCommand cmd = new SqlCommand(query.ToString());
// add ID param
if(useName) {
// add name param
}
string query2 = cmd.CommandText;You could change your query to
"SELECT * FROM xy WHERE id = @id and (@name = '' OR name = @name");
Saves messing about with your query when the parameter has no value.
where block has a tautology in case @name is empty. Otherwise this query would have the overhead of two string comparisons. As always don't forget to check for NULL valuesSqlCommand cmd = new SqlCommand();
if(txtUsername.Text != string.Empty)
{
cmd.CommandText = "select * from xy where id = @id and name = @name";
cmd.Parameters.Add["@name", SqlDbType.VarChar);
cmd.Parameters["@name"].Value = txtUsername.Text;
}
else
{
cmd.CommandText = "select * from xy where id = @id";
}
Have a look at stringtemplate.org. ST allows for all kinds of text construction and is very flexible.
If your example is the only query that needs runtime construction then ST is overkill. But I am currently working on a project with many queries and ST made me happy. The fact that all your queries are stored in a single file and not spread out inside many C# classes is enough to make the transition to ST worthwhile.
.Length?