CODEWITHSUNDEEP
c#sqllist
J.S.Orris
J.S.Orris

Reputation: 4821

Populate c# list with results from table from stored procedure that returns multiple tables

I have a stored procedure that return 3 tables . All I care about populating the List with is the result set from the last table the stored procedure returns; which has 3 columns. I have the following code so far:

string connStr = null;
SqlConnection scnn;
SqlCommand sCmd;
string sql = null;

connStr = "Data Source=server;Initial Catalog=dbName;Integrated Security=SSPI";
sql = @"DECLARE @udt1 userDefTblType1;" +
       "INSERT INTO @one (uid) VALUES (0), (1), (2), (3);" +
       "DECLARE @udt2 userDefTblType2;" +
       "INSERT INTO @two (uid) VALUES (0);" +
       "DECLARE @udt3 userDefTblType3;" +
       "INSERT INTO @three (uid) VALUES (0),(1);" +
       "EXEC [dbo].[storedProcedure] @one, @two, @three;";

sqlCnn = new SqlConnection(connStr);

try
{
    sCnn.Open();
    sCmd = new SqlCommand(sql, sCnn);
    SqlDataReader sReader = sCmd.ExecuteReader();

    sReader.Read();
    sReader.NextResult(); //move to next table

    sReader.Read();
    sReader.NextResult(); //move to next table

    sReader.Read();  //table of interest
    List<decimal> results = new List<decimal>();
    while (sReader.Read())
    {
        results.Items.Add(sqlReader["column1"].ToString());  //my problem is here
        results.Items.Add(sqlReader["column2"].ToString());  //my problem is here
        results.Items.Add(sqlReader["column2"].ToString());  //my problem is here                
    };

    sqlReader.Close();
    sqlCmd.Dispose();
    sqlCnn.Close();
}

catch (Exception ex)
{
    MessageBox.Show("Can not open connection ! ");
}

I need to fill the list like column1, column2, column3 respectively for as many rows there are so that I can populate an HTML table.

May you advise on what I am doing wrong; am I taking the right approach?

Thanks (I am starting to get a little more advance in C#, I do more db dev)

EDIT:

The following is an example of the 3rd table the stored proc returns:

Column1 | Column2 | Column3
---------------------------
5.6     | 5.1     | 7.4    |
5.7     | 5.4     | 7.7    |
5.8     | 5.6     | 7.9    |
5.9     | 5.8     | 7.0    |
5.1     | 5.6     | 7.7    |

I have code already dynamically written for a html table. I just need to store these results some where to enumerate through them so can add the values to the relative html tabs in my code.

EDIT:

In the end I want my code to look something like this:

html.WriteLine("<tr>");
while (colCount <= numCol)
{
html.WriteLine("<td>" POSITION IN <LIST> + "</td>");
cFinalColCount++;
}
html.WriteLine("</tr>");
rowCount++;

Upvotes: 0

Views: 817

Answers (2)

Gregg Duncan
Gregg Duncan

Reputation: 2725

Use a gridview for tabling data in your html. It's much easier than building your own tables.

C# code:

DataTable dt = new DataTable();
try
{
    sCnn.Open();
    sCmd = new SqlCommand(sql, sCnn);
    SqlDataReader sReader = sCmd.ExecuteReader();

    sReader.Read();
    sReader.NextResult(); //move to next table

    sReader.Read();
    sReader.NextResult(); //move to next table

    dt.Load(sReader); // Convert your data reader to a DataTable

    sqlReader.Close();
    sqlCmd.Dispose();
    sqlCnn.Close();


    // UNTESTED CODE - but it should be close. 

    GridView gv = new GridView(); // create gridview
    gv.DataSource = dt; // Set the DataTable as the DataSource for the GridView
    gv.DataBind(); // Bind the Data to the GridView
    StringWriter sWriter = new StringWriter(); //stringwriter needed for html writer
    HtmlTextWriter htWriter = new HtmlTextWriter(sWriter); // create HthmWriter
    gv.RenderControl(htWriter); //render the gridview in the htmlwriter
    htWriter.Write(true); // write the html writer to the output stream

}

catch (Exception ex)
{
    MessageBox.Show("Can not open connection ! ");
}

Upvotes: 2

Adam V
Adam V

Reputation: 6356

As JonSkeet suggested, what makes the most sense is to declare a class with three decimals as properties (preferably with sensible names, such as below):

public class Volume
{
  public decimal Length { get; set; }
  public decimal Width { get; set; }
  public decimal Height { get; set; }
}

Then you'll create a List of Volume objects:

List<Volume> results = new List<Volume>();
while (sReader.Read())
{
  // Error checking elided
  var length = decimal.Parse(sqlReader["column1"]);
  var width = decimal.Parse(sqlReader["column2"]);
  var height = decimal.Parse(sqlReader["column3"]);
  results.Add(new Volume { Length = length, Width = width, Height = height });
};

Upvotes: 0

Related Questions