locked
sql results display data in table RRS feed

  • Question

  • I would like to setup a table to display data from the sql results. Could this code be changed to do that.

    Any help would be appreciated

    OracleCommand myOracleCommand3 = myOracleConnection.CreateCommand();
                     
                        myOracleCommand3.CommandText = "SELECT field1, field2, fiedl3, FROM table WHERE field1 = '111111'";
                        OracleDataReader myOracleDataReader3 = myOracleCommand3.ExecuteReader();

    // Total number of rows
     int rowCnt;
    // Current row count
    int rowCtr;
    // Total number of cells per row (columns)
    int cellCtr;
    // Current cell counter
     int cellCnt;

    rowCnt = 3;
    cellCnt = 5;

    for (rowCtr = 1; rowCtr <= rowCnt; rowCtr++)
    {
    // Create new row and add it to the table.
    DataTable table = new DataTable();
    TableRow tRow = new TableRow();

    Table1.Rows.Add(tRow);

    for (cellCtr = 1; cellCtr <= cellCnt; cellCtr++)
    {
    // Create a new cell and add it to the row.
    TableCell tCell = new TableCell();


    tCell.Text = "Row " + rowCtr + ", Cell " + cellCtr;
     tRow.Cells.Add(tCell);
    }
    }

                                                     
    Thursday, July 12, 2012 1:46 AM

Answers

  • sorry yaar i thought you asked the conversion from sql table to datatable.

    to convert sql table to ordinary table can be done like this

    If you are using normal datatable.

    using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace HTML_Table { public partial class Form1 : Form { DataTable table = new DataTable(); public Form1() { InitializeComponent(); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Columns.Add("Date", typeof(DateTime)); // // Here we add five DataRows. // table.Rows.Add(25, "Indocin", "David", DateTime.Now); table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); webBrowser1.DocumentText=DataTableToHTMLTable(table); }

    public string DataTableToHTMLTable(DataTable inTable) { StringBuilder dString = new StringBuilder(); dString.Append("<html><head><body><table border=&quot1&quot>"); dString.Append(GetHeader(inTable)); dString.Append(GetBody(inTable)); dString.Append("</table></body></head></html>"); return dString.ToString(); } private string GetHeader(DataTable dTable) { StringBuilder dString = new StringBuilder(); dString.Append("<thead><tr>"); foreach (DataColumn dColumn in dTable.Columns) { dString.AppendFormat("<th>{0}</th>", dColumn.ColumnName); } dString.Append("</tr></thead>"); return dString.ToString(); }

    private string GetBody(DataTable dTable) { StringBuilder dString = new StringBuilder(); dString.Append("<tbody>"); foreach (DataRow dRow in dTable.Rows) { dString.Append("<tr>"); for (int dCount = 0; dCount <= dTable.Columns.Count - 1; dCount++) { dString.AppendFormat("<td>{0}</td>", dRow[dCount]); } dString.Append("</tr>"); } dString.Append("</tbody>"); return dString.ToString(); } } }


    Output

    If using Sql. export sql table to datatable and then populate the table

    using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace HTML_Table { public partial class Form1 : Form { public Form1() { InitializeComponent();

    DataTable table = GetData();

    webBrowser1.DocumentText=DataTableToHTMLTable(table);

    }

    public DataTable GetData()

    { DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE"); try { connection.Open(); SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Table", connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlDa.Fill(dt); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Fetch Error:"; msg += ex.Message; throw new Exception(msg); } finally { connection.Close(); } return dt; }

            public string DataTableToHTMLTable(DataTable inTable)
            {
                StringBuilder dString = new StringBuilder();
                dString.Append("<html><head><body><table border=&quot1&quot>");
                dString.Append(GetHeader(inTable));
                dString.Append(GetBody(inTable));
                dString.Append("</table></body></head></html>");
                return dString.ToString();
            }
    
            private string GetHeader(DataTable dTable)
            {
                StringBuilder dString = new StringBuilder();
    
                dString.Append("<thead><tr>");
                foreach (DataColumn dColumn in dTable.Columns)
                {
                    dString.AppendFormat("<th>{0}</th>", dColumn.ColumnName);
                }
                dString.Append("</tr></thead>");
    
                return dString.ToString();
            }
    
    
            private string GetBody(DataTable dTable)
            {
                StringBuilder dString = new StringBuilder();
    
                dString.Append("<tbody>");
    
                foreach (DataRow dRow in dTable.Rows)
                {
                    dString.Append("<tr>");
                    for (int dCount = 0; dCount <= dTable.Columns.Count - 1; dCount++)
                    {
                        dString.AppendFormat("<td>{0}</td>", dRow[dCount]);
                    }
                    dString.Append("</tr>");
                }
                dString.Append("</tbody>");
    
                return dString.ToString();
            }
        }
    }


    • Edited by Srithar Wednesday, July 18, 2012 1:05 PM
    • Proposed as answer by Srithar Wednesday, July 18, 2012 1:05 PM
    • Marked as answer by Lisa Zhu Monday, July 23, 2012 9:19 AM
    Wednesday, July 18, 2012 12:53 PM

All replies

  • This is a code in c# to get the datatable from a Sql Server Table

    public DataTable GetData()
    {
            DataTable dt = new DataTable();
            SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE");
            try
            {
            connection.Open();
            SqlCommand sqlCmd = new SqlCommand("SELECT TOP(1000) FieldName FROM Table", connection);
            SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);
    
    
            sqlDa.Fill(dt);
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                    string msg = "Fetch Error:";
                    msg += ex.Message;
                    throw new Exception(msg);
    
    
            }
            finally
            {
                connection.Close();
            }
    
    
            return dt;
    }

    Hope this one Will help you.You can try changing Sql into Oracle in the Following Code
    Thursday, July 12, 2012 5:01 AM
  • sorry yaar i thought you asked the conversion from sql table to datatable.

    to convert sql table to ordinary table can be done like this

    If you are using normal datatable.

    using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace HTML_Table { public partial class Form1 : Form { DataTable table = new DataTable(); public Form1() { InitializeComponent(); table.Columns.Add("Dosage", typeof(int)); table.Columns.Add("Drug", typeof(string)); table.Columns.Add("Patient", typeof(string)); table.Columns.Add("Date", typeof(DateTime)); // // Here we add five DataRows. // table.Rows.Add(25, "Indocin", "David", DateTime.Now); table.Rows.Add(50, "Enebrel", "Sam", DateTime.Now); table.Rows.Add(10, "Hydralazine", "Christoff", DateTime.Now); table.Rows.Add(21, "Combivent", "Janet", DateTime.Now); table.Rows.Add(100, "Dilantin", "Melanie", DateTime.Now); webBrowser1.DocumentText=DataTableToHTMLTable(table); }

    public string DataTableToHTMLTable(DataTable inTable) { StringBuilder dString = new StringBuilder(); dString.Append("<html><head><body><table border=&quot1&quot>"); dString.Append(GetHeader(inTable)); dString.Append(GetBody(inTable)); dString.Append("</table></body></head></html>"); return dString.ToString(); } private string GetHeader(DataTable dTable) { StringBuilder dString = new StringBuilder(); dString.Append("<thead><tr>"); foreach (DataColumn dColumn in dTable.Columns) { dString.AppendFormat("<th>{0}</th>", dColumn.ColumnName); } dString.Append("</tr></thead>"); return dString.ToString(); }

    private string GetBody(DataTable dTable) { StringBuilder dString = new StringBuilder(); dString.Append("<tbody>"); foreach (DataRow dRow in dTable.Rows) { dString.Append("<tr>"); for (int dCount = 0; dCount <= dTable.Columns.Count - 1; dCount++) { dString.AppendFormat("<td>{0}</td>", dRow[dCount]); } dString.Append("</tr>"); } dString.Append("</tbody>"); return dString.ToString(); } } }


    Output

    If using Sql. export sql table to datatable and then populate the table

    using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; namespace HTML_Table { public partial class Form1 : Form { public Form1() { InitializeComponent();

    DataTable table = GetData();

    webBrowser1.DocumentText=DataTableToHTMLTable(table);

    }

    public DataTable GetData()

    { DataTable dt = new DataTable(); SqlConnection connection = new SqlConnection("YOUR CONNECTION STRING HERE"); try { connection.Open(); SqlCommand sqlCmd = new SqlCommand("SELECT * FROM Table", connection); SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd); sqlDa.Fill(dt); } catch (System.Data.SqlClient.SqlException ex) { string msg = "Fetch Error:"; msg += ex.Message; throw new Exception(msg); } finally { connection.Close(); } return dt; }

            public string DataTableToHTMLTable(DataTable inTable)
            {
                StringBuilder dString = new StringBuilder();
                dString.Append("<html><head><body><table border=&quot1&quot>");
                dString.Append(GetHeader(inTable));
                dString.Append(GetBody(inTable));
                dString.Append("</table></body></head></html>");
                return dString.ToString();
            }
    
            private string GetHeader(DataTable dTable)
            {
                StringBuilder dString = new StringBuilder();
    
                dString.Append("<thead><tr>");
                foreach (DataColumn dColumn in dTable.Columns)
                {
                    dString.AppendFormat("<th>{0}</th>", dColumn.ColumnName);
                }
                dString.Append("</tr></thead>");
    
                return dString.ToString();
            }
    
    
            private string GetBody(DataTable dTable)
            {
                StringBuilder dString = new StringBuilder();
    
                dString.Append("<tbody>");
    
                foreach (DataRow dRow in dTable.Rows)
                {
                    dString.Append("<tr>");
                    for (int dCount = 0; dCount <= dTable.Columns.Count - 1; dCount++)
                    {
                        dString.AppendFormat("<td>{0}</td>", dRow[dCount]);
                    }
                    dString.Append("</tr>");
                }
                dString.Append("</tbody>");
    
                return dString.ToString();
            }
        }
    }


    • Edited by Srithar Wednesday, July 18, 2012 1:05 PM
    • Proposed as answer by Srithar Wednesday, July 18, 2012 1:05 PM
    • Marked as answer by Lisa Zhu Monday, July 23, 2012 9:19 AM
    Wednesday, July 18, 2012 12:53 PM