11 Replies - 8574 Views - Last Post: 27 January 2011 - 12:59 PM Rate Topic: -----

#1 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

Help with SQL query in C#

Posted 27 January 2011 - 09:09 AM

I am trying to create a basic application to run a sql statement in c#

I am using the Northwind database for those of you are familiar with it.

But I need to get the customer and order table info to have the program print the customer name and the number of orders that customer has.

I have the foundation of the program set up but I am new to sql programming and cannot seem to get it to work how I need.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication3
{
    class Program
    {
        static void Main(string[] args)
        {


            string strConn, strSQL;
            strConn = @"Data Source=.\SQLExpress;" +
                       "Initial Catalog=Northwind;Trusted_Connection=Yes;";

            strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders" +
                      "SELECT ContactName, CompanyName, CustomerID FROM Customers";

            SqlConnection cn = new SqlConnection(strConn);
            cn.Open();

            SqlCommand cmd = new SqlCommand(strSQL, cn);
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
                Console.WriteLine("{0} {1} {2:d} {3}", rdr["OrderID"],
                                  rdr["CustomerID"], rdr["OrderDate"], rdr["CompanyName"] );
            rdr.Close();





        }

        
        }

    }




This is what I have. Right now I am just trying to get it to print the results to show fields in 2 different tables. That is where I am having trouble. I can get it to print what is in one table but not both.

And if anybody can point me in the right direction for getting count in a sql that would be great. I have not researched it yet so I may be able to. But it is not my main priority right now.

Thanks

Is This A Good Question/Topic? 0
  • +

Replies To: Help with SQL query in C#

#2 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Help with SQL query in C#

Posted 27 January 2011 - 09:22 AM

Well, I wouldn't personally do it that way, just concatenating SQL statements. First of all, your code here:

strSQL = "SELECT OrderID, CustomerID, OrderDate FROM Orders" +
          "SELECT ContactName, CompanyName, CustomerID FROM Customers";


Will result in this:

SELECT OrderID, CustomerID, OrderDate FROM OrdersSELECT ContactName, CompanyName, CustomerID FROM Customers


Notice that there's not even a space between Orders and SELECT.

Also, I would rather do each select separately. Either make a new command for it, or reuse the same one, but not both at once.

However, there is a way to do it the way you tried too. There's a method called NextResult that'll move the DataReader on to the next returned dataset. Try using that, after you fix your spacing issues.

This post has been edited by insertAlias: 27 January 2011 - 09:22 AM

Was This Post Helpful? 1
  • +
  • -

#3 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1535
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Help with SQL query in C#

Posted 27 January 2011 - 09:24 AM

If you want to get the Customer name and the number of orders for that customer, you would use this query...

SELECT CU.CustomerName, COUNT(O.OrderID)
FROM Customer CU
    INNER JOIN Order O
       ON CU.CustomerID = O.CustomerID
GROUP BY O.CustomerID



I didn't test it, but that should work. I am joining the two tables with an INNER JOIN on the CustomerID field, then I am grouping by that CustomerID and counting them.

This is the easiest way to do what you want as you only return back the data that you need. To do two separate queries is horribly inefficient.
Was This Post Helpful? 1
  • +
  • -

#4 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

Re: Help with SQL query in C#

Posted 27 January 2011 - 11:31 AM

Alright I have edited the sql a bit. And am getting an error on run now.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

using System.Data.SqlClient;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {


            string strConn, strSQL;
            strConn = @"Data Source=.\SQLExpress;" +
                       "Initial Catalog=Northwind;Trusted_Connection=Yes;";

            strSQL = "SELECT Customers.CustomerName, Count(Orders.OrderID) AS CountOfCustomerOrder FROM Customers INNER JOIN CustomerID ON Orders.CustomerID = Orders.OrderID GROUP BY Customers.CustomerName ORDER BY Customers.CategoryName, Count(Orders.OrderID)";


            SqlConnection cn = new SqlConnection(strConn);
            cn.Open();

            SqlCommand cmd = new SqlCommand(strSQL, cn);
            SqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
                Console.WriteLine(rdr );
                                  
            rdr.Close();





        }


    }

}



The line

SqlDataReader rdr = cmd.ExecuteReader();

says SQLEXCEPTION WAS UNHANDLED
Was This Post Helpful? 0
  • +
  • -

#5 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1535
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Help with SQL query in C#

Posted 27 January 2011 - 11:37 AM

The INNER JOIN statement is wrong. You should INNER JOIN on a table, not a column. You used "CustomerID", which is a column. You should have used "Order", which is the table name.

You also have another problem with that join statement.

The INNER JOIN structure should be this...

SELECT {Some Column(s)}
FROM {Table1}
INNER JOIN {Table2} ON {Table1.Column1} = {Table2.Column1}


You are joining CustomerID and OrderID, which are two completely different fields. When doing a join, you must join on a column that is the same between the two tables.

If you still receive an error after making these changes, post the ACTUAL error message.
Was This Post Helpful? 1
  • +
  • -

#6 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

Re: Help with SQL query in C#

Posted 27 January 2011 - 12:10 PM

Alright here is the message I get now

System.Data.SqlClient.SqlException was unhandled
Message=Column 'Customers.CustomerID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


Though I think this can be fixed y changing the GROUP BY Orders to Customers.CustomerID

iS that right?


string strConn, strSQL;
            strConn = @"Data Source=. \SQLExpress; " +
                       "Initial Catalog=Northwind;Integrated Security=True";
            strSQL = "SELECT Customers.CustomerID, COUNT(Orders.OrderID) FROM Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID GROUP BY Orders.CustomerID";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);
            da.Fill(ds);
            //Display the customer information retrieved
            foreach (DataRow row in ds.Tables[0].Rows)
                Console.WriteLine("{0}–{1}", row["CustomerID"]);

This post has been edited by Public Designs: 27 January 2011 - 12:15 PM

Was This Post Helpful? 0
  • +
  • -

#7 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Help with SQL query in C#

Posted 27 January 2011 - 12:12 PM

Look at your select, and look at your group by. Do you notice a difference between the "CustomerID" columns there? They need to match.
Was This Post Helpful? 1
  • +
  • -

#8 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

Re: Help with SQL query in C#

Posted 27 January 2011 - 12:18 PM

Yea that took care of that error. Now my only problem left is to get it to print the number of orders they have next to it



Also something I am noticing is that when it runs through the customers and all the console closes when its done. Is there a way to keep it open so i can copy and paste it?
Was This Post Helpful? 0
  • +
  • -

#9 eclipsed4utoo   User is offline

  • Not Your Ordinary Programmer
  • member icon

Reputation: 1535
  • View blog
  • Posts: 5,972
  • Joined: 21-March 08

Re: Help with SQL query in C#

Posted 27 January 2011 - 12:22 PM

After all of your code, put this line in...

Console.Read();



The console window will wait for you to press any key before closing.
Was This Post Helpful? 1
  • +
  • -

#10 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Help with SQL query in C#

Posted 27 January 2011 - 12:43 PM

I usually use ReadKey, but it's all the same.

Change your select statement to something like this:

SELECT Customers.CustomerID, COUNT(Orders.OrderID) as OrderCount...


And you can reference the column by "OrderCount"

Otherwise, you can use it's index (which should be 1).
Was This Post Helpful? 2
  • +
  • -

#11 Public Designs   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 145
  • Joined: 08-November 08

Re: Help with SQL query in C#

Posted 27 January 2011 - 12:52 PM

string strConn, strSQL;
            strConn = @"Data Source=. \SQLExpress; " +
                       "Initial Catalog=Northwind;Integrated Security=True";
            strSQL = "SELECT Customers.CustomerID, COUNT(Orders.OrderID) AS numOrders FROM Customers INNER JOIN Orders ON Customers.CustomerID  = Orders.CustomerID GROUP BY Customers.CustomerID";
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(strSQL, strConn);
            da.Fill(ds);
            //Display the customer information retrieved
            foreach (DataRow row in ds.Tables[0].Rows)
                Console.WriteLine("{0} {1}", row["CustomerID"], row["numOrders"]);




            Console.Read();



TADAA

It works now.


You guys have been awesome. All +rep from me. Thank you for the help it is much appreciated

View PostinsertAlias, on 27 January 2011 - 12:43 PM, said:

I usually use ReadKey, but it's all the same.

Change your select statement to something like this:

SELECT Customers.CustomerID, COUNT(Orders.OrderID) as OrderCount...


And you can reference the column by "OrderCount"

Otherwise, you can use it's index (which should be 1).



Just saw this post. I pretty much did that I think so thanks again
Was This Post Helpful? 0
  • +
  • -

#12 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Help with SQL query in C#

Posted 27 January 2011 - 12:59 PM

Yep, that's pretty much exactly what I was suggesting. Glad you got it figured out.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1