.

Displaying a datatable

To display a Data Table:

@model System.Data.DataTable
@using System.Data;

<h2>View Table</h2>

<table>
    <thead>
    <tr>
    @foreach (DataColumn col in Model.Columns)    
    {         
        <th>@col.ColumnName</th>
    }    
    </tr>
    </thead>        
    <tbody>
    @foreach (DataRow row in Model.Rows)    
    {        
        <tr>
        @foreach (DataColumn col in Model.Columns)        
        {             
            <td>@row[col.ColumnName]</td>
        }        
        </tr>
    }    
    </tbody>
</table>

You can get data as follows:

public DataTable GetDataForSql(string sql, string connectionString)
{
    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        using(SqlCommand command = new SqlCommand())
        {
            command.CommandType = CommandType.Text;
            command.Connection = connection;
            command.CommandText = sql;
            connection.Open();          
            using(SqlDataReader reader = command.ExecuteReader())
            {
                DataTable data = new DataTable();
                data.Load(reader);
                return data;
            }

        }

    }

}

If you just wanted a DataTable then the following method is short and reduces complexity:

<code>public DataTable GetDataForSql(string sql, string connectionString)
{
    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        using(SqlCommand command = new SqlCommand())
        {
            command.CommandType = CommandType.Text;
            command.Connection = connection;
            command.CommandText = sql;
            connection.Open();          
            using(SqlDataReader reader = command.ExecuteReader())
            {
                DataTable data = new DataTable();
                data.Load(reader);
                return data;
            }

        }

    }

}</code>

usage:

<code>try{
    DataTable results = GetDataForSql("SELECT * FROM Table;", ApplicationSettings["ConnectionString"]);
}
catch(Exception e)
{
    //Logging
    //Alert to user that command failed.
}</code>

What's your thoughts on this?

*

Protected by WP Anti Spam