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; }
}

dear Friend check my answer bellow if you need any more assistance feel free to ask — Developerzzz
– Developerzzz, Commented Sep 18, 2014 at 6:40

Developerzzz · Accepted Answer · 2014-09-18 06:39:07Z

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; }

}

Jon P · Accepted Answer · 2014-09-18 06:58:58Z

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.

masum7 · Accepted Answer · 2014-09-18 06:21:44Z

0

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

answered Sep 18, 2014 at 6:21

masum7

8326 silver badges17 bronze badges

Comments

rjv · Accepted Answer · 2014-09-18 06:22:42Z

0

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" />

answered Sep 18, 2014 at 6:22

rjv

6,9365 gold badges31 silver badges53 bronze badges

Collectives™ on Stack Overflow

Displaying SQL Server data from c# to a html page

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related