Remove From My Forums

sql results display data in table

Question
0

Sign in to vote

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