SQL Query not working

[Suzi167]

Hello Evryone,

I have an application where I am using a dataset and store the results of an SQL query.

I need somehow to determine when the query returns no affected rows.
For example if I say:

Select * FROM table_name WHERE UserID = 1

but there is not UserIS with value 1
How can I know that from my C# application

Thanks in advance

Suzi

[Eli Gassert]

DataSet ds = new DataSet();
DataAdapter da = /* whatever */;

da.Fill(ds);

if(ds.Tables[0].Rows.Count == 0)
// no results
else
// has results


When a single query is executed and stored into a DataSet, Table[0] will contain the result set. The Rows property is a collection of all the rows, so if Rows.Count is set to 0 then obviously there are no rows!

[Suzi167]

What would I do without this site??? :)

Thanks!

[hedge_fund]

Suzi,
although the example provided does work, I would suggest looking into a datareader in this case. Since you are only returning a few values (if i understand correctly, only 1 record since ids are unique), it will be MUCH more efficient than building, populating, and returning a dataset.

[mmetzger]

Suzi,
although the example provided does work, I would suggest looking into a datareader in this case. Since you are only returning a few values (if i understand correctly, only 1 record since ids are unique), it will be MUCH more efficient than building, populating, and returning a dataset.

There's no mention of what that table actually is - it could be downloads, logins, sales, etc so there could be any number of rows that match that query. While using a DataReader works for certain behaviors it also doesn't allow you to easily modify / update / delete data in the same way that a dataset does. Not to mention it is not nearly as easy to bind to a DataGrid / GridView. You also have no method of sorting / filtering after you've obtained the data (user level sorting / filtering) without requerying the data.

In the end you may be right (in which case the DataReader.HasRows property will usually tell you) but considering they were designed for different things there may be very good reason to use a DataSet in this case...

[Suzi167]

- Actually iy is not just one row that I am returning which I why I opted for
the dataset.
- The user can select the criteria by which to run the query - let's say the
user says he needs to see all users with first name JAnet - but there
might be more than one person with that first name.
- Another time the user may select to see all users which report to
Manager 2 (He can select the criteria from a drop down menu)
- My Quesry sting looks like this:


string SelectSQL = "SELECT FirstName, LastName, Title FROM Employees ";
SelectSQL += "WHERE " + LstString + " = " + Complete_txt_String;


- And Yes I am using Datagrid to display the results which is very easy to
link to a Dataset.
- However since we are at that topic what is the advantage of using
Datareader and When should I use it since it does not offer the
functionality of a dataset and I can not go back , I can only read forward.
- I am personally fan of datasets or just sending the queries directly to the
SQL server.
- What disadvantages do datasets have ?

Thanks ro everyone who posted their suggestions.

Suzi

[mmetzger]

- My Quesry sting looks like this:


string SelectSQL = "SELECT FirstName, LastName, Title FROM Employees ";
SelectSQL += "WHERE " + LstString + " = " + Complete_txt_String;



- A side note here - Depending on how much you trust the user this leaves you open to some SQL injection attacks. It is much better to use a parameterized statement.


- And Yes I am using Datagrid to display the results which is very easy to
link to a Dataset.
- However since we are at that topic what is the advantage of using
Datareader and When should I use it since it does not offer the
functionality of a dataset and I can not go back , I can only read forward.
- I am personally fan of datasets or just sending the queries directly to the
SQL server.
- What disadvantages do datasets have ?

Thanks ro everyone who posted their suggestions.

Suzi

DataReaders rock for fast forward reading of a data file. Here are some assorted situations you can use them:
- Perhaps you're performing some analysis of data that isn't easy to do in SQL that requires reading every row to tabulate options.
- Build a CSV of the results in a single row (FormsAuthentication and roles come to mind)
- You're running on a low speed machine with low RAM and can't hold the entire dataset in memory at one time (this applies more to WinForms than WebForms)
- You need to encrypt data before sending it / writing it (yes you can do this with a DataSet too, but remember you're not holding all of it in memory / ViewState)
- You're worried about network utilization via the app - a dataset fill can take up considerably more bandwidth (at a point in time) than a DataReader can.

DataSets can take up a lot of memory depending on the situation. This is especially true with ViewState on a page w/ a large data grid.

There are a ton of other pros / cons, but In the end, it's really just using the best tool for the job.

[Eli Gassert]

Suzi,

My guess is you are an ASP programmer coming over to the .NET world. I make that assumption by seeing how you build your query.

Regardless, your query is dangerous. you should consider using a SqlCommand and using @parameters instead.


string sql = "SELECT * FROM tbl WHERE Field1 = @Field1 AND Field2 = @Field2";
SqlCommand cmd = new SqlCommand(sql);

cmd.Parameters.AddWithValue("Field1", "This is my 'string' value, no need to escape single quotes (')");
cmd.Parameters.AddWithValue("Field2", 123);


Note that I didn't have to do anything different for a string field (Field1) or an int field (Field2). AddWithValue takes an object and does what it needs to do when it creates the sql command. It also protects you from SQL injection attacks.

Cheers
-eli

[Suzi167]

Thanks for the suggestion.
I will look at it closer tonight and let you know if I have further questions.

Suzi

[Suzi167]

Hello Eli,

- I want to thank you very much for the advise you gave me.
- I looked into SQL Injection attacks and now I understand what you mean.
- I change my query as you siggested.
- However there is still one question I have - I could not find an
explanation to that.
- When I write the code below for example:

string SelectSQL = "SELECT FirstName, LastName, Title FROM Employees ";
SelectSQL += "WHERE " + @LstString + " = " + @Complete_txt_String;


SqlConnection con1 = new SqlConnection(Connection);
SqlCommand cmd = new SqlCommand(SelectSQL, con1);
cmd.Parameters.AddWithValue("@LstString", LstString);
cmd.Parameters.AddWithValue("@Complete_txt_string",Complete_txt_String);



The AddwithValue Method - except for assigning the input from the text box to the @variable - does it do anything else?
It must be doing some checking of the data I assume becasue otherwise it is not really useful,right?
You are right - in the past I have developed applications with Windows Forms and I am relatively new to Web Base Data driven applications but I like it a lot

Thanks Again

Suzi

[Eli Gassert]

Your code is still not quite right. Let me show you what it should look like:

string SelectSQL = "SELECT FirstName, LastName, Title FROM Employees ";
SelectSQL += "WHERE " + LstString + " = @Complete_txt_String";


SqlConnection con1 = new SqlConnection(Connection);
SqlCommand cmd = new SqlCommand(SelectSQL, con1);
cmd.Parameters.AddWithValue("@LstString", LstString);
cmd.Parameters.AddWithValue("@Complete_txt_string",Complete_txt_String);


Note that I got rid of the parameter @LstString. Here is why. Check out this example:

LstString = "LastName";
string Complete_txt_String = "Gassert";

string SelectSQL = @"SELECT FirstName, LastName, Title FROM Employees
WHERE " + LstString + @" = @Complete_txt_String";
// note the use of the @ Operator for strings spanning multiple lines. Check out MSDN for the @ String literal operator

SqlConnection con1 = new SqlConnection(Connection);
SqlCommand cmd = new SqlCommand(SelectSQL, con1);
cmd.Parameters.AddWithValue("@Complete_txt_String",Complete_txt_String);


The query that will be executed is:
SELECT FirstName, LastName, Title FROM Employees
WHERE LastName = @Complete_txt_String

When you run the query, the SqlCommand examines it's parameters and replaces the @Complete_txt_String parameter with the value specified with cmd.Parameters.AddWithValue.

Now, why can't you do that for @LstString? Here's why:

If you use @LstStringand use AddWithValue("@LstString", "LastName"); then your query would look like:
SELECT FirstName, LastName, Title FROM Employees
WHERE @LstString = @Complete_txt_String

which, at runtime, with the parameters replaced, will look like:
SELECT FirstName,LastName, Title FROM Employees
WHERE 'LastName' = 'Gassert'

See how the string literals got replaced? It didn't say WHERE (the field) LastName = (the string) 'Gassert'; doing such things is DSQL, not parameterized queries.



Oh, I talk and talk. I kind of said the same thing like 5 times, so I hope at least one of them made sense :)

[Suzi167]

Hi Eli,

I did as you suggested and it works well.
That however raises another question. Why are we afraid from Injection attacks in the second string and we are not concerned in the first string .
Is it because after the WHERE there is not much that can be inserted that can damage the database since it has to be valid column name from the table for the quesry to execute?

Also the other amazing thing is that somehow Addwithvalue knows when to put ' ' around the string and when not to do it - see I get both my string inputs from text boxes - first one is the search criteria (such as firstName, ManagerID,etc.) and the second one is the value for that criteria that I am searching for (for ex: 'Susan',1,etc)

Becasue of that however in my second text box the user sometimes enters a string such as 'Susan' and sometimes enters an int such as 5.
Addwithvalue somehow knows when to insert 'Susan' and when not to insert the ''
Thus my final quesry is as you suggested(Except I did not read yet about the @ operator and I don't want to use something I don't understand.


string SelectSQL = "SELECT FirstName, LastName, Title FROM Employees ";
SelectSQL += "WHERE " + LstString + " = @txt_String";
SqlConnection con1 = new SqlConnection(Connection);
SqlCommand cmd = new SqlCommand(SelectSQL, con1);

cmd.Parameters.AddWithValue("@txt_String",txtString);

Amazing!!!

Thanks Again for the thorough explanation!

Suzi

[wildfrog]

That however raises another question. Why are we afraid from Injection attacks in the second string and we are not concerned in the first string .
Is it because after the WHERE there is not much that can be inserted that can damage the database since it has to be valid column name from the table for the quesry to execute?

IF the LstString parameter is controlled by the user (an evil one) he will sooner or later try to enter something like this:

FirstName = '0'; DELETE Employees; --

and the resulting CommandText will look something like this:
SELECT FirstName, LastName, Title FROM Employees WHERE FirstName = '0'; DELETE Employees; -- @txt_String;And you don't want that... so, you should control and check everything that the user may freely enter (or don't let the user enter sql at all).

- petter

[Suzi167]

But how can you do that , other than with AddwithValue method which in the case of the first string clearly can not be used.
When you say

or don't let the user enter sql at all).




What exactly do you mean.
Should I check for sql function words? Is there a special command that does that?

Thanks in advance!

[wildfrog]

I don't have any experience with what you're trying to do, but to me it seems rather unsafe to let the user 'freely' enter sql statements (like column names etc.).

One solution is to let the user choose column names from a static list - that way you avoid bugs and fatal sql injections.

- petter

[Eli Gassert]

Suzi. Surely you don't want the user to have full control over the LstString string. It's probably a set number of columns in the database that you want them to match against your @txt_String parameter, i.e. LastName, FirstName, etc.

Instead, give them the option, perhaps in a drop down list or a radio button list, to chose which field to filter on. And then have a switch statement to say "if the option for 'LastName' was selected, then SelectQuery += "WHERE LastName = @txt_String"; if the option for 'FirstName' was selected, then SelectQuery += "WHERE FirstName = @txt_String"..."

Do you see how I got rid of the use of the LstSTring variable ? Yes, this will mean more code for you to write (because you have to have a SelectQuery += statement for each of the available LstString values) but it will be a lot safer than your code.

This isn't your only option, but it's probably one of the easiest ones.

Cheers
-eli

[Suzi167]

Thanks !

Actually that is exactly what I am doing - it is a list box and they can choose one thing from there for the field lstString.
I just thought that in general there might be other ways of verifying user input that I still don't know about and I always want to learn :)

thanks!

Suzi