locked
Display DataTable Data using C# RRS feed

  • Question

  • User529229055 posted

    I'm trying to build an order confirmation email with data living in several SQL tables. The end result is to get the data to write out as follows:

    08009330126
    0800 Medium (Landline)
    Auto Attendant
    Time Of Day Routing

    08451520125
    0845 Lite (Landline)
    Call Whisper

    I've created a SQL command that pulls all the data needed so I can at least access the information:

    SELECT SC_NewView.PkgName, SC_NewView.Number, SC_Features.FeatureName, SC_Features.FeatureSetUp, SC_Features.FeatureMonthly 
    FROM SC_NewView 
    INNER JOIN SC_FeatureCart 
    ON SC_FeatureCart.RecordID = SC_NewView.RecordID 
    INNER JOIN SC_Features 
    ON SC_FeatureCart.FeatureID = SC_Features.FeatureID 
    WHERE SC_NewView.CpyID = @CpyID"
    

    This command retrieves the data like so:

    PkgName Number FeatureName FeatureSetUp FeatureMonthly
    0800 Medium (Landline) 08009330126 Auto Attendant 15.0000 4.0000
    0800 Medium (Landline) 08009330126 Time Of Day Routing 10.0000 3.0000
    0845 Lite (Landline) 08451520125 Call Whisper 2.0000 1.0000

    My C# code to build the order details is below but it's not producing the result I want illustrated above.

    SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["XXXXXXXXXXXXXXXXX"].ConnectionString);
    string sql = "SQL COMMAND AS DETAILED ABOVE";
    SqlCommand cmd = new SqlCommand(sql, conn);
    cmd.Parameters.AddWithValue("@CpyID", CpyID);
    conn.Open();
    DataTable table = new DataTable();
    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
    adapter.Fill(table);
    cmd.ExecuteNonQuery();
    
    foreach (DataRow row in table.Rows)
    {
      string number = row["Number"].ToString();
      string package = row["PkgName"].ToString();
      string feature = row["FeatureName"].ToString();
      string featureSetup = row["FeatureSetUp"].ToString(); 
                                                  
      bodyText += "<tr>";
      bodyText += "<td>" + number + "</td>";
      bodyText += "<td>" + package + "</td>";
      bodyText += "</tr>";
    
          foreach (var item in row.ItemArray)
          {
             bodyText += "<tr>";
             bodyText += "<td>" + feature + "</td>";
             bodyText += "<td>" + featureSetup + "</td>";
             bodyText += "</tr>";
          }
    }

    Please can anyone shed some light on what I can do to end up with the example given at the top?

    Many thanks in advance!

    Chris


    Friday, September 20, 2013 6:13 PM

Answers

  • User1143442848 posted

    Hi Oned_gk,

    Thanks for taking the time to respond but I'm not too sure I understand how to do this? 

    Do you have an example? The problem I have is rendering the data being retrieved fromt the sql query.

    Thanks,

    Chris

    Hi,

    According to your description, I write a Demo for you to refer to and it works well in my lab machine.

    And please try to adapt the code to your requirement.

    In the .aspx file, you can put a button control.

    When you click the button, it will output the result you expected to the web page.

    Here is the code in .cs:

    string bodyText = "";
            protected void Page_Load(object sender, EventArgs e)
            {
               
            }
    
            private DataTable BindGridData()
            {
                DataTable dt = new DataTable();
                DataColumn c_id = new DataColumn("PkgName", typeof(string));
                dt.Columns.Add(c_id);
                DataColumn c_name = new DataColumn("Number", typeof(string));
                dt.Columns.Add(c_name);
                DataColumn c_address = new DataColumn("FeatureName", typeof(string));
                dt.Columns.Add(c_address);
                DataColumn c_setup = new DataColumn("FeatureSetUp", typeof(double));
                dt.Columns.Add(c_setup);
    
                DataRow row = dt.NewRow();
                row[c_id] = "0800 Medium (Landline)";
                row[c_name] = "08009330126";
                row[c_address] = "Auto Attendant";
                row[c_setup] = "15.0000";
                dt.Rows.Add(row);
    
                row = dt.NewRow();
                row[c_id] = "0800 Medium (Landline)";
                row[c_name] = "08009330126";
                row[c_address] = "Time Of Day Routing";
                 row[c_setup] = "10.0000";
                dt.Rows.Add(row);
    
                row = dt.NewRow();
                row[c_id] = "0845 Lite (Landline)";
                row[c_name] = "08451520125";
                row[c_address] = "Call Whisper";
                 row[c_setup] = "2.0000";
                dt.Rows.Add(row);           
    
                return dt;
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                DataTable table = BindGridData();
                string number = "";
                string package = "";
                string feature = "";           
                int count = table.Rows.Count;
    
                if (count > 0)
                {
                    for (int i = 0; i < count; i++)
                    {
                        number = table.Rows[i]["Number"].ToString();
                        package = table.Rows[i]["PkgName"].ToString();
                        feature = table.Rows[i]["FeatureName"].ToString();
                        bodyText += "<table>";
    
                        if (i + 1 <= count)          
                        {
                            
                            bodyText += "<tr>";
                            bodyText += "<td>" + number + "</td>";
                            bodyText += "</tr>";
                            bodyText += "<tr>";
                            bodyText += "<td>" + package + "</td>";
                            bodyText += "</tr>";
                            bodyText += "<tr>";
                            bodyText += "<td>" + feature + "</td>";
                            bodyText += "</tr>";
    
                            for (int j = i+1; j < count; j++)
                            {                       
    
                                string s = table.Rows[j]["Number"].ToString();
                                string ss = table.Rows[i]["Number"].ToString();
    
                                if (table.Rows[j]["Number"].ToString() == table.Rows[i]["Number"].ToString())
                                {
                                    bodyText += "<tr>";
                                    bodyText += "<td>" + table.Rows[j]["FeatureName"].ToString() + "</td>";
                                    bodyText += "</tr>";
                                    i = i+1;
                                 }
                            }
    
                            bodyText += "<br>";
                        }
                    }
    
                    bodyText += "</table>";
                }
    
                Response.Write(bodyText);
            }
    

    Please have a try.

    Best Regards,

    Lisa Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 23, 2013 1:34 AM

All replies

  • User-1716253493 posted
    Design it with table and labels. Then code it like : LblNumber.Text = number;
    Friday, September 20, 2013 7:14 PM
  • User529229055 posted

    Hi Oned_gk,

    Thanks for taking the time to respond but I'm not too sure I understand how to do this? 

    Do you have an example? The problem I have is rendering the data being retrieved fromt the sql query.

    Thanks,

    Chris

    Saturday, September 21, 2013 2:27 PM
  • User1143442848 posted

    Hi Oned_gk,

    Thanks for taking the time to respond but I'm not too sure I understand how to do this? 

    Do you have an example? The problem I have is rendering the data being retrieved fromt the sql query.

    Thanks,

    Chris

    Hi,

    According to your description, I write a Demo for you to refer to and it works well in my lab machine.

    And please try to adapt the code to your requirement.

    In the .aspx file, you can put a button control.

    When you click the button, it will output the result you expected to the web page.

    Here is the code in .cs:

    string bodyText = "";
            protected void Page_Load(object sender, EventArgs e)
            {
               
            }
    
            private DataTable BindGridData()
            {
                DataTable dt = new DataTable();
                DataColumn c_id = new DataColumn("PkgName", typeof(string));
                dt.Columns.Add(c_id);
                DataColumn c_name = new DataColumn("Number", typeof(string));
                dt.Columns.Add(c_name);
                DataColumn c_address = new DataColumn("FeatureName", typeof(string));
                dt.Columns.Add(c_address);
                DataColumn c_setup = new DataColumn("FeatureSetUp", typeof(double));
                dt.Columns.Add(c_setup);
    
                DataRow row = dt.NewRow();
                row[c_id] = "0800 Medium (Landline)";
                row[c_name] = "08009330126";
                row[c_address] = "Auto Attendant";
                row[c_setup] = "15.0000";
                dt.Rows.Add(row);
    
                row = dt.NewRow();
                row[c_id] = "0800 Medium (Landline)";
                row[c_name] = "08009330126";
                row[c_address] = "Time Of Day Routing";
                 row[c_setup] = "10.0000";
                dt.Rows.Add(row);
    
                row = dt.NewRow();
                row[c_id] = "0845 Lite (Landline)";
                row[c_name] = "08451520125";
                row[c_address] = "Call Whisper";
                 row[c_setup] = "2.0000";
                dt.Rows.Add(row);           
    
                return dt;
            }
    
            protected void Button1_Click(object sender, EventArgs e)
            {
                DataTable table = BindGridData();
                string number = "";
                string package = "";
                string feature = "";           
                int count = table.Rows.Count;
    
                if (count > 0)
                {
                    for (int i = 0; i < count; i++)
                    {
                        number = table.Rows[i]["Number"].ToString();
                        package = table.Rows[i]["PkgName"].ToString();
                        feature = table.Rows[i]["FeatureName"].ToString();
                        bodyText += "<table>";
    
                        if (i + 1 <= count)          
                        {
                            
                            bodyText += "<tr>";
                            bodyText += "<td>" + number + "</td>";
                            bodyText += "</tr>";
                            bodyText += "<tr>";
                            bodyText += "<td>" + package + "</td>";
                            bodyText += "</tr>";
                            bodyText += "<tr>";
                            bodyText += "<td>" + feature + "</td>";
                            bodyText += "</tr>";
    
                            for (int j = i+1; j < count; j++)
                            {                       
    
                                string s = table.Rows[j]["Number"].ToString();
                                string ss = table.Rows[i]["Number"].ToString();
    
                                if (table.Rows[j]["Number"].ToString() == table.Rows[i]["Number"].ToString())
                                {
                                    bodyText += "<tr>";
                                    bodyText += "<td>" + table.Rows[j]["FeatureName"].ToString() + "</td>";
                                    bodyText += "</tr>";
                                    i = i+1;
                                 }
                            }
    
                            bodyText += "<br>";
                        }
                    }
    
                    bodyText += "</table>";
                }
    
                Response.Write(bodyText);
            }
    

    Please have a try.

    Best Regards,

    Lisa Zhang

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 23, 2013 1:34 AM
  • User529229055 posted

    Thank you so much for this very clear example. You really helped me a lot!  Smile

    Monday, September 23, 2013 2:39 PM