Reading records from databases C#

[JR]

Hi, i have two tables in a database Orders and OrderDetail. there are two columns Unit price and quantiy that are located in the table orderdetail.
I was wondering how would you multiply the two of them so i can get the total cost and put it into a label control?
thanks

[Euphoracle]

Well, you can read them using the ADO.NET control, from there, it's just working with variables the way would normally:

Loop for each order,
Read out the unit price and number of units,
Multiply the unit price by the amount of units,
Set label's text,
Repeat?

[rdrake]

Try something like the following. Minor modifications are likely needed to make it work and fit your needs. It's completely untested.

C#:

SqlConnection sqlconn = new SqlConnection(connectionStr); SqlCommand sqlcomm = new SqlCommand("SELECT * FROM OrderDetail", sqlconn); SqlDataReader reader = sqlcomm.ExecuteReader(); const int UNITPRICE = 0; const int QUANTITY = 1; while (reader.Read()) {     float unitPrice = reader.getFloat(UNITPRICE);     int quantity = reader.getInt(QUANTITY);     // Do whatever with the values. } reader.Dispose(); sqlcomm.Dispose(); sqlconn.Dispose();

[rdrake]

Oh, you'll likely want to attempt to open the connection before executing commands on it.

[wtd]

Could not "using" be put to good use here, rdrake?

[jbking]

"using" is put to great use here as without it, you'd have to have a try/catch/finally series of statements in case something goes wrong when the connection is open so that the connection is closed in the end.

[rdrake]

wtd, it could be.

jbking @ Sun Dec 07, 2008 11:49 pm wrote:

"using" is put to great use here as without it, you'd have to have a try/catch/finally series of statements in case something goes wrong when the connection is open so that the connection is closed in the end.

Exceptions are still thrown regardless, and using statements do not catch them. It's just one way of avoiding the finally statement.

[wtd]

Presumably instead of the above, you'd have something like:

code:

using (SqlConnection sqlconn = new SqlConnection(connectionStr),        SqlCommand sqlcomm = new SqlCommand("SELECT * FROM OrderDetail", sqlconn),        SqlDataReader reader = sqlcomm.ExecuteReader()) {                   const int UNITPRICE = 0;         const int QUANTITY = 1;         while (reader.Read())         {                 float unitPrice = reader.getFloat(UNITPRICE);                 int quantity = reader.getInt(QUANTITY);                 // Do whatever with the values.         } }

Maybe some untested type inferencing love?

code:

using (var sqlconn = new SqlConnection(connectionStr),        var sqlcomm = new SqlCommand("SELECT * FROM OrderDetail", sqlconn),        var reader = sqlcomm.ExecuteReader()) {                   const int UNITPRICE = 0;         const int QUANTITY = 1;         while (reader.Read())         {                 float unitPrice = reader.getFloat(UNITPRICE);                 int quantity = reader.getInt(QUANTITY);                 // Do whatever with the values.         } }

[JR]

thanks, i get the value but now i'm trying to compare it to a customer and i get an Invalid Column name exception.

this is my query
the CustomerID column is type nchar btw


string query = "SELECT UnitPrice, Quantity, Discount FROM nwOrderDetails"
+ " JOIN nwOrders ON (nwOrderDetails.OrderID = nwOrders.OrderID) "
+ "WHERE nwOrders.CustomerID =" + ddlCust.SelectedValue.Trim();

[rdrake]

Does that query work in Query Analyzer? I personally use stored procedures for everything, you could always just do it that way.

It's about the same as you had before. The only difference is you change the command type to CommandType.StoredProcedure and the command name is the name of your stored procedure.

Everything else remains the same as far as I can recall.

[JR]

i figured out what i was doing wrong. Since CustomerId was a string [] and the ddl value was a string it gave me the error.

had to use CustomerID LIKE" +ddl.... to make it work

btw if you dont mind me asking what does {0} do? is it like _$ in perl?

[rdrake]

Instead of doing this:

C#:

string mystr = "Hello" + name;

You can do:

C#:

string mystr = string.Format("Hello {0}", name);