Displaying SQL Server data from c# to a html page

Question

I need to make a website that pulls data from a SQL Server and displays it on the site, it is set up on a asp.net empty web application, and the pages are web forms so this code is in the .cs page.

How would I display this on data on the .aspx page ??

private List<Course> GetCourses()
{
    var dataTable = new DataTable();

    using (var sqlConnection = new SqlConnection("Data Source=localhost;Initial Catalog=tafe_work;Integrated Security=True"))
    {
        sqlConnection.Open();

        using (var sqlCommand = new SqlCommand("select * from Course", sqlConnection))
        {
            using (var sqlReader = sqlCommand.ExecuteReader())
            {
                dataTable.Load(sqlReader);
            }
        }
    }

    var courses = new List<Course>();

    foreach (DataRow dataRow in dataTable.Rows)
    {
        var course = new Course() {
            ID = (int)dataRow["Course_ID"],
            Name = (string)dataRow["Name"]
        };
        courses.Add(course);
    }

    return courses;
}

public class Course
{
    public int ID { get; set; }
    public string Name { get; set; }
}

Answer 1

If you don't have to, don't double handle the data. Asp.net provides many data controls. All of these controls can be directly bound to the data table so there is no need to iterate through the table to create a list of objects. You can bind to a list of objects as well if you really have to.

You can find the data controls in the VisualStudio toolbox and drag them directly to your .aspx page.

You will need to determine which control suites your need best. GridView & DataList use HTML Table where as Repeater uses a structure that you define.

Gridview would potentialy be the quickes way to do this. Drag one to your .aspx page and give it an ID, lets say grdCourses

In your .cs page set up something like

private void bindCourses()
{
    var dataTable = new DataTable();

   using (var sqlConnection = new SqlConnection("Data Source=localhost;Initial Catalog=tafe_work;Integrated Security=True"))
{
    sqlConnection.Open();

    using (var sqlCommand = new SqlCommand("select * from Course", sqlConnection))
    {
        using (var sqlReader = sqlCommand.ExecuteReader())
        {
            dataTable.Load(sqlReader);
            grdCourses.DataSource = dataTable;
            grdCourses.DataBind();
        }
    }
  }
}


protected void Page_Load(object sender, EventArgs e)
{
   if(!isPostBack)
   {
       bindCourses();
   }
}

I mentioned that GridView is the quickest, and that is fine for this demonstration, but evaulate what you really need, do you really need a table, or would a list be better?

Also the built in data controls are very powerful and have a lot of depth to what they can do. If you google: "databind asp.net controls" you should be able to find pleanty of tutorials to help you.

Answer 2

there are many solution to your problem if you want to show your data in grid so the Rjv answer is best but if you want customization in your html or designing then you have to work with the asp.net Repeater Control. for Repeater Control follow this link Repeater its is also data binding control

aspx code will be

<asp:Repeater ID="RptCourse" runat="server">
        <HeaderTemplate>
            <table>
                <tr>
                    <th>ID </th>
                    <th>Name</th>
                </tr>
        </HeaderTemplate>

        <ItemTemplate>
            <tr>
                <td>
                    <asp:Label runat="server" ID="Label1"
                        Text='<%# Eval("ID") %>' />
                </td>
                <td >
                    <asp:Label runat="server" ID="Label2"
                        Text='<%# Eval("Name") %>' />
                </td>
            </tr>
        </ItemTemplate>

        <AlternatingItemTemplate>
            <tr>
                <td>
                    <asp:Label runat="server" ID="Label1"
                        Text='<%# Eval("ID") %>' />
                </td>
                <td >
                    <asp:Label runat="server" ID="Label2"
                        Text='<%# Eval("Name") %>' />
                </td>
            </tr>
        </AlternatingItemTemplate>
        <FooterTemplate>
            </table>
        </FooterTemplate>
    </asp:Repeater>

and your aspx.cs code will be

        protected void Page_Load(object sender, EventArgs e)
    {
        RptCourse.DataSource = GetCourses();
        RptCourse.DataBind();
    }

    private List<Course> GetCourses()
    {
        var dataTable = new DataTable();

        using (var sqlConnection = new SqlConnection("Data Source=localhost;Initial Catalog=tafe_work;Integrated Security=True"))
        {
            sqlConnection.Open();

            using (var sqlCommand = new SqlCommand("select * from Course", sqlConnection))
            {
                using (var sqlReader = sqlCommand.ExecuteReader())
                {
                    dataTable.Load(sqlReader);
                }
            }
        }

        var courses = new List<Course>();

        foreach (DataRow dataRow in dataTable.Rows)
        {
            var course = new Course()
            {
                ID = (int)dataRow["Course_ID"],
                Name = (string)dataRow["Name"]
            };
            courses.Add(course);
        }

        return courses;
    }

}
public class Course
{
    public int ID { get; set; }
    public string Name { get; set; }

}

Answer 3

Add a GridView control to your aspx page. Bind your collection to the GridView as follows

List<Course> coll = GetCourses();
courseGridView.DataSource = coll;
courseGridView.DataBind()

In the aspx file, add gridview as

<asp:GridView id="courseGridView" runat="server" />

Answer 4

You need to bring a GridView control. That is the best control.

http://msdn.microsoft.com/en-us/library/aa479342.aspx

You can set data source just like this:

GridView1.DataSource = list
GridView1.DataBind()

Other available controls are: DataList, Repeater, DataList and so on