CODEWITHSUNDEEP
c#asp.netsql-server-2008
Arpita
Arpita

Reputation: 455

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

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.

Upvotes: 2

Views: 33466

Answers (4)

Steve
Steve

Reputation: 216303

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.

Upvotes: 0

DGibbs
DGibbs

Reputation: 14618

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.

Upvotes: 1

Semih Yagcioglu
Semih Yagcioglu

Reputation: 4101

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)

Upvotes: 0

Mark Fitzpatrick
Mark Fitzpatrick

Reputation: 1624

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
    }
}

Upvotes: 1

Related Questions