New to Dynamic SQL Statements.

Question

Hi,

Im currently making a very simple WebApp on a Website using ASP.NET Framework using C# in Visual Studio 2010.

The website will connect to my SQL EXPRESS server running on my laptop (Its all locally based)

I have a table defined as below

CREATE TABLE Users(
 userName varchar(50),
 email varchar(50),
 firstName varchar(50),
 lastName varchar(50),
 gender varchar(50),
 birthday date,
 age int,
 location varchar(50),
 gname varchar(50)
 PRIMARY KEY (userName, email))
GO

Now, I have a page on my website called Users. NOTE: gname means Group Name (ie, users join an interest group.)

Here I have a form that looks something like this:

UserName:
Gender:
Age:
GroupName:
Location:

Now, this would mean I have 5 different fields, and hence, I would have to write 25 SQL statements for each different case. Which I find pretty naive.

I tried Googling around for Dynamic SQL Statements, which do not seem to be addressing my problem here (or at least I don't understand how they address my problem).

Can anyone point me in the right direction to learn how to implement SQL statements thats smart enough to generate queries based on which field has been given input by the user?

Apologise to anyone who might think I have not done my research to something that might seem rather simple.

-Ali

Answer 1

Use parameters in your query like this:

string commandText = "UPDATE Sales.Store SET Demographics = @demographics "
    + "WHERE CustomerID = @ID;";

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(commandText, connection);
    command.Parameters.Add("@ID", SqlDbType.Int);
    command.Parameters["@ID"].Value = customerID;

    // Use AddWithValue to assign Demographics.
    // SQL Server will implicitly convert strings into XML.
    command.Parameters.AddWithValue("@demographics", demoXml);

    try
    {
        connection.Open();
        Int32 rowsAffected = command.ExecuteNonQuery();
        Console.WriteLine("RowsAffected: {0}", rowsAffected);
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}

From http://msdn.microsoft.com/en-gb/library/system.data.sqlclient.sqlcommand.parameters.aspx

Answer 2

hi ali, You don't need to use dynamic sql. here is your solution of parameterized query or stored procedure:

-- assuming you have parameters @username,@gname,@pass for searching user

select * from Users
where (username like '%'+@username+'%' or @username is null)
and (gname = @gname or @gname is null)
and (password = @password or @password = null)

you can add as many params as you want and add column names as you want in place of * to get starte with

Answer 3

Many things wrong here.

PRIMARY KEY (userName, email))

Crap. The primary key is unique. The username is unique. What you want is a unique index on email, on top, but not a combined primary key, because it means it would be legal to have 2 users "Joe" as long as they have different email addresses.

That said, maybe not use the email and username as primary key but gi with a synthetic one.

Same here:

gender varchar(50),

What is gender in your case? "SonOfABitch"? In most cases people write that as CHAR(1) NULL, means either "M" or "F" or NULL (user has not entered). Do NOT store decoded strings in a database for stati.

Goes on:

age int,

Why? Age can be calculated in code in one line from BIrthdate and current date. This is a maintenance nightmare. People enter their birthdate. Work with it in your program. Do not store changing data (they get older) without maintaining it.

And:

location varchar(50),

Now, this makes no sense. Really. FIrst, it should be a Table (Locations) with a reference in here. Second, even if you dont beliefveiit, 50 chars are TOO SMALL. Some spaces have longer names. Anyhow, learn on data normalization.

This:

gname varchar(50)

and this:

NOTE: gname means Group Name (ie, users join an interest group.)

ONLY makes any sense if users only ever join one group, and even then it stinks as design - there should be a group table. Easy case: Table Groups, this would be a GroupREF with the primary (synthetic) key of teh group. Otherwise it is nothing, and an interim table attaches users to groups, as a user can be part of many groups.

Can anyone point me in the right direction to learn how to implement SQL statements thats smart enough to generate queries based on which field has been given input by the user?

Yes. It is called learning programming. Seriously.

• There is no SQL Statement that does so really efficiently.
• SQL is text. Strings. Manipulating string is basic programming.

Basically, sit down and put up a SQL Statement string that does what you need ONLY. Before you do that you may want to read a book like SQL for DUmmies to get an idea about table design.

If you want a static statement, you can put in something like * Paramter @surname for surename - always use parameters to pass values in your field.

WHERE (surname LIKE @surname OR surname IS NULL)

and SQL Server will handle it decently efficiency wise (as it can see only one part can either evaluate to null). Just to answer your question.

Answer 4

Consider a combination of @GôTô's code and the below SQL

select * 
from Users u
where   (@username is null or Username like '%'+ @username + '%')
    AND (@Gender is null or Gender = @gender)
    AND (@age is null or Age = @age)
    AND (@gname is null or gname = @gname)
    AND (@location is null or location = @location)