How to retrieve data from sql server database and manipulate it in c#?

Question

I am new to asp.net. I am trying to retrieve data from SQL Server in an asp.net website. This is the result of my table in SQL,

Day_Of_Week    Product_Count
-----------------------------
Sunday              8
Monday            150
Tuesday            80
Wednesday          95
Thursday          345
Friday            229
Saturday           48

This is my code in c#,

    DataTable dt = new DataTable();
    SqlConnection con = new SqlConnection(Settings.DatabaseConnectionString);
    SqlCommand com = new SqlCommand("Select * from tblProducts");
    com.CommandType = CommandType.Text;
    com.Connection = con;
    con.Open();
    SqlDataAdapter da = new SqlDataAdapter(com);
    da.Fill(dt); 

   int monday = Convert.ToInt32(dt.Rows[1]["Product_Count"]);
   int tuesday = monday + Convert.ToInt32(dt.Rows[2]["Product_Count"]);
   int wednesday = tuesday + Convert.ToInt32(dt.Rows[3]["Product_Count"]);
   int thursday = wednesday + Convert.ToInt32(dt.Rows[4]["Product_Count"]) ;
   int friday = thursday + Convert.ToInt32(dt.Rows[5]["Product_Count"]);

Now, if there are no records on Sunday, it does not display Sunday row and I have to change the c# code. Instead, I want to put a switch case or something so that if the day is Monday, then I can write dt.Rows[0], if the day is Tuesday, then dt.Rows[0], etc.

Let me know what would be the best option to do this. Any help will be appreciated.

Thanks.

Can you consider reading the recent documentation? The usage of DataTables like that is like worst practice for - hm - 1o years or so (not that it was better before). Since then MS has really adjusted to the rest of the world and I wonder where people "new to whatever .NET" get their education from to follow that old antipatterns. — TomTom
– TomTom, Commented Nov 10, 2014 at 15:38
@TomTom Can you point the OP (and the rest of us) to the recent documentation so that we may learn something? What's wrong with using DataTables? — dtsg
– dtsg, Commented Nov 10, 2014 at 16:02
You msut be joking? What about Entity Fraemwork (part of the .NET framework? ADO.NET documentaiton? YOu seriously ask me to point you to msdn.microsoft.com and google.com? — TomTom
– TomTom, Commented Nov 10, 2014 at 16:04
@TomTom I am not joking. It's genuine question and I'm willing to learn. What's wrong with DataTables? — dtsg
– dtsg, Commented Nov 10, 2014 at 16:11

DGibbs · Accepted Answer · 2014-11-10 16:04:05Z

You need to reconsider your approach. Since all you want to do is fetch the product count by day I'd do something like this instead of relying on the row number.

public static int FetchProductCountByDayOfWeek(DayOfWeek dayOfWeek)
{
    using(SqlConnection con = new SqlConnection(Settings.DatabaseConnectionString))
    {
         con.Open();

         SqlCommand com = new SqlCommand("Select * from tblProducts where Day_Of_Week = @day_of_week");
         com.CommandType = CommandType.Text;
         com.Connection = con;

         com.Parameters.AddWithValue("@day_of_week", dayOfWeek.ToString());

         using (SqlDataAdapter da = new SqlDataAdapter(com))
         {
               DataTable dt = new DataTable();
               da.Fill(dt);

               return Convert.ToInt32(dt.Rows[0]["Product_Count"]);
         }
    }
}

You would then call this like so:

int monday = ProductHelper.FetchProductCountByDayOfWeek(DayOfWeek.Monday);

Though this isn't as efficient as reading the rows in one go as it's doing multiple DB calls.

Mark Fitzpatrick · Accepted Answer · 2014-11-10 15:34:12Z

1

You can use a foreach to iterate through the rows and a switch to set the data. Pre-initialize your variables first.

int monday = 0;
int tuesday = 0;
// and repeat
foreach(DataRow row in dt.Rows)
{
    switch(row["Days_Of_Week"].ToString().ToLower())
    {
        case "monday":
             monday = (int)row["Product_Count"];
        break;
        case "tuesday":
             tuesday = (int)row["Product_Count"] + monday;
        break;
        // repeat for each
    }
}

answered Nov 10, 2014 at 15:34

Mark Fitzpatrick

1,6341 gold badge11 silver badges8 bronze badges

2 Comments

BLoB Over a year ago

Beware the difference between Convert and Cast... nulls will freakout your cast, Convert will return 0.

Mark Fitzpatrick Over a year ago

Good point Paul. In this case though he isn't returning records for nulls. An extra if could be added though to exclude rows where the Product_Count is null to avoid interaction with them entirely.

Semih Yagcioglu · Accepted Answer · 2014-11-10 15:42:17Z

0

It might be better to retrieve the required data using a query so you won't have to manipulate it in C#.

You might use a T-SQL statement to detect when there is no row for a given day. Not the exact query you want but basically you might use ISNULL to detect the cases where there is no row, for instance days with no counts.

SELECT ISNULL(
(SELECT 1 FROM tblProducts T
WHERE T.Day_Of_Week = @DayName and T. Product_Count > 0), 0)

answered Nov 10, 2014 at 15:42

Semih Yagcioglu

4,1111 gold badge29 silver badges43 bronze badges

Comments

Steve · Accepted Answer · 2014-11-10 16:16:48Z

Suppose to change your query to use the PIVOT SQL Statement

string query = @"
   SELECT 'Day Production' As DayProduction, Sunday, Monday, Tuesday, Wednesday, 
                                             Thursday, Friday, Saturday
FROM (SELECT Day_Of_Week, ProductCount FROM tblProducts) as tbp
PIVOT
(
    SUM(ProductCount)
    FOR Day_Of_Week IN ( Sunday, Monday, Tuesday,Wednesday,Thursday,Friday,Saturday )
) AS Totals";

Now your code could be rewritten as

using(SqlConnection con = new SqlConnection(Settings.DatabaseConnectionString))
using(SqlCommand cmd = new SqlCommand(query, con))
{
     con.Open();

     // The query will return just one row with eight columns
     using(SqlDataReader reader = cmd.ExecuteReader())
     {
       if(reader.Read())
       {  
         // The column at ordinal 0 is the DayProduction one....

         // Check if column at ordinal 1 (Sunday) contains a DBNull.Value or not...
         int sunday = reader.IsDBNull(1) ? 0 : Convert.ToInt32(reader[1]);

         int monday = reader.IsDBNull(2) ? 0 : Convert.ToInt32(reader[2]);
         int tuesday = reader.IsDBNull(3) ? 0 : Convert.ToInt32(reader[3]);
         int wednesday = reader.IsDBNull(4) ? 0 : Convert.ToInt32(reader[4]);
         int thursday = reader.IsDBNull(5) ? 0 : Convert.ToInt32(reader[5]);
         int friday = reader.IsDBNull(6) ? 0 : Convert.ToInt32(reader[6]);
         int saturday = reader.IsDBNull(7) ? 0 : Convert.ToInt32(reader[7]);
       }
     }
}

The rows of your previous query are rotated as columns and the Sunday (or any other day) if not present is always returned as a DBNull.Value.

Collectives™ on Stack Overflow

How to retrieve data from sql server database and manipulate it in c#?

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related