Sqldataread doesn't read all column and values in join query.

Question

1.00/5 (1 vote)

See more:

SqlDataReader doesn't read all columns and their values in C#.
My query in Sql Managemnt Studio works perfectly. Where am I wrong there?
Any help will be much appreciated. Thank you so much in advanced.

C#

sql = 
"SELECT Invoices.* FROM Invoices inner JOIN Customers " +
"ON Invoices.custID = Customers.custID " +
"where Invoices.custID like N'" + custID.Text + "' and Invoices.Series like N'ΑΛΕ'";
SqlCommand cmd = new SqlCommand(sql, con);
if (con.State == ConnectionState.Closed) con.Open();
Rdr = cmd.ExecuteReader();
                
if (Rdr.HasRows)
{
    while (Rdr.Read())
    {
        if (Rdr.GetName(0).ToString() == "InvoiceID")
             InvoiceID_ = Rdr.GetValue(0).ToString();
        else if (Rdr.GetName(0).ToString() == "FromOrderID")
             FromOrderID_ = Rdr.GetValue(0).ToString();
        else if (Rdr.GetName(0).ToString() == "FinalPriceWithTax")
        {
             InvFinalPriceWithTax_ = decimal.Parse(Rdr.GetValue(0).ToString());
        }
   }
}
Rdr.Close();

What I have tried:

sql = 
"SELECT Invoices.* FROM Invoices inner JOIN Customers " +
"ON Invoices.custID = Customers.custID " +
"where Invoices.custID like N'" + custID.Text + "' and Invoices.Series like N'ΑΛΕ'";
SqlCommand cmd = new SqlCommand(sql, con);
if (con.State == ConnectionState.Closed) con.Open();
Rdr = cmd.ExecuteReader();
                
if (Rdr.HasRows)
{
    while (Rdr.Read())
    {
        if (Rdr.GetName(0).ToString() == "InvoiceID")
             InvoiceID_ = Rdr.GetValue(0).ToString();
        else if (Rdr.GetName(0).ToString() == "FromOrderID")
             FromOrderID_ = Rdr.GetValue(0).ToString();
        else if (Rdr.GetName(0).ToString() == "FinalPriceWithTax")
        {
             InvFinalPriceWithTax_ = decimal.Parse(Rdr.GetValue(0).ToString());
        }
   }
}
Rdr.Close();

Posted 8-Apr-24 3:58am

Aggeliki Asimakopoulou

Add a Solution

Comments

CHill60 8-Apr-24 10:42am

It is a good idea to share your table design, some sample data and your expected results. For example - which columns are not being returned?
Also you need to do something about the SQL injection risk - never use string concatentation to create sql queries

PIEBALDconsult 8-Apr-24 11:32am

And don't use string concatenation to form SQL statements.

Aggeliki Asimakopoulou 9-Apr-24 11:16am

Which would be the better approach instead of using string concatenation to sql statements?

Richard Deeming 8-Apr-24 11:42am

Your code is vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]

Aggeliki Asimakopoulou 9-Apr-24 11:17am

I have no idea how to use a parameterized query. May you give me an example please?

Richard Deeming 10-Apr-24 3:19am

const string sql = @"SELECT Invoices.* 
FROM Invoices 
INNER JOIN Customers ON Invoices.custID = Customers.custID 
WHERE Invoices.custID Like @custID 
And Invoices.Series = N'ΑΛΕ'";

using (SqlConnection con = new SqlConnection(...))
using (SqlCommand cmd = new SqlCommand(sql, con))
{
    cmd.Parameters.AddWithValue("@custID", custID.Text);
    
    con.Open();
    using (SqlDataReader rdr = cmd.ExecuteReader())
    {
        ...
    }
}

George Swan 8-Apr-24 14:22pm

If your query executes in Sql Server Management Studio, I would suggest that you save it as a stored procedure and simply run that. It is a secure and fast way of executing queries

Aggeliki Asimakopoulou 10-Apr-24 1:55am

No, my queries run through source code application in C#. Sql is embedded to C# project. It's a back - end client application.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Dave Kreskowiak · Accepted Answer · 2024-04-08T05:06:00

Solution 1

You told the SELECT statement to return only the columns from the Invoices table with the "Invoices.*":

SELECT Invoices.* FROM Invoices inner JOIN Customers
ON Invoices.custID = Customers.custID
where Invoices.custID ...

By the way, NEVER use SELECT *. ALWAYS spell out every column name you want returned.

Also, the following code is utter garbage. You should NEVER have to figure out which column is the first column:

C#

if (Rdr.GetName(0).ToString() == "InvoiceID")
     InvoiceID_ = Rdr.GetValue(0).ToString();
else if (Rdr.GetName(0).ToString() == "FromOrderID")
     FromOrderID_ = Rdr.GetValue(0).ToString();
else if (Rdr.GetName(0).ToString() == "FinalPriceWithTax")
{
     InvFinalPriceWithTax_ = decimal.Parse(Rdr.GetValue(0).ToString());
}

You should just convert the values you need directly by column name, not index:

C#

InvoiceID_ = (string)Rdr["InvoiceID"];
FromOrderID_ = (string)Rdr["FromOrderID"];
InvFinalPriceWithTax_ = (decimal)Rdr["FinalPricesWithTax"];

Posted 8-Apr-24 5:06am

Dave Kreskowiak

Updated 9-Apr-24 4:06am

v3

Comments

Aggeliki Asimakopoulou 9-Apr-24 8:12am

Thank you so much. I have some question please!!!
First question : You told me NEVER use SELECT .*, however with your code I realized that it works perfectly and not only with SqlDataReader but with DataSet too. So, is there any reason for not doing this? I would appreciate your explation.

Secondly as a determination : InvoiceID_ = (string)Rdr["InvoiceID"].Value;
FromOrderID_ = (string)Rdr["FromOrderID"].Value;
InvFinalPriceWithTax_ = (decimal)Rdr["FinalPricesWithTax"].Value;
.Value doesn't need because it doesn't exist such a property.
However I didn't know that at all, I was always finding as an explanation with Rdr.GetName(0) or Rdr.GetValue(0) we always get the current name of the field or value respectively. So, I want to thank you so much for that, because I learned something new.

Dave Kreskowiak 9-Apr-24 10:09am

SELECT * returns every column in a table(s), even the ones you may not need. This increases the load on the SQL server and needlessly increases the amount of data coming back from the database.

Next, the columns returned may not be in the order you expect, so code that uses index numbers to identify columns is easily broken when you start adding/removing columns from tables.

You're right about .Value. It's not needed. I've been spending a lot of time doing my work in ORMs and WMI lately so I forgot the database Reader syntax. Whoops!

Aggeliki Asimakopoulou 9-Apr-24 11:15am

You are welcome, thank you so much. I realised that I forgot Rdr.NextResult(); statement under the while (Rdr.Read()) ends. However it runs, both ways. It's very effective using the column name to get the appropriate value. Thank you.