SqlHelper.cs

using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.Web.UI.WebControls;

public class SQLHelper
{
    public static SqlConnection CreateConnection()
    {
        string strCon = WebConfigurationManager.ConnectionStrings["MessageBoardDOM"].ToString();
        return new SqlConnection(strCon);
    }

    public static int ExecuteSql(string strSql)
    {
        SqlConnection connection = CreateConnection();
        var command = new SqlCommand(strSql, connection);
        connection.Open();
        int result = command.ExecuteNonQuery();
        connection.Close();
        return result;
    }

    public static void BindDropDownList(DropDownList dropDownList, string strSql, string dataTextField)
    {
        SqlConnection connection = CreateConnection();
        var adapter = new SqlDataAdapter(strSql, connection);
        var dataSet = new DataSet();
        connection.Open();
        adapter.Fill(dataSet);
        connection.Close();
        dropDownList.DataSource = dataSet;
        dropDownList.DataTextField = dataTextField;
        dropDownList.DataBind();
    }

    public static void BindGridView(GridView gridView, string strSql)
    {
        SqlConnection connection = CreateConnection();
        var adapter = new SqlDataAdapter(strSql, connection);
        var dataSet = new DataSet();
        connection.Open();
        adapter.Fill(dataSet);
        connection.Close();
        gridView.DataSource = dataSet;
        gridView.DataBind();
    }

    public static DataSet GetDataSet(string strSql)
    {
        SqlConnection connection = CreateConnection();
        var adapter = new SqlDataAdapter(strSql, connection);
        var dataSet = new DataSet();
        connection.Open();
        adapter.Fill(dataSet);
        connection.Close();
        return dataSet;
    }

    public static void BindDataList(DataList dataList, string strSql, string dataKeyField)
    {
        SqlConnection connection = CreateConnection();
        var adapter = new SqlDataAdapter(strSql, connection);
        var dataSet = new DataSet();
        connection.Open();
        adapter.Fill(dataSet);
        connection.Close();
        dataList.DataSource = dataSet;
        dataList.DataKeyField = dataKeyField;
        dataList.DataBind();
    }

    public static void BindDataList(DataList dataList, string strSql)
    {
        SqlConnection connection = CreateConnection();
        var adapter = new SqlDataAdapter(strSql, connection);
        var dataSet = new DataSet();
        connection.Open();
        adapter.Fill(dataSet);
        connection.Close();
        dataList.DataSource = dataSet;
        dataList.DataBind();
    }

    public static int ExecuteScalar(string strSql)
    {
        SqlConnection connection = CreateConnection();
        var command = new SqlCommand(strSql, connection);
        connection.Open();
        var result = (int) command.ExecuteScalar();
        connection.Close();
        return result;
    }

    public static string SubString(string strOriginal, int length)
    {
        if (strOriginal.Length < length)
        {
            return strOriginal;
        }
        else
        {
            string strDealed = strOriginal.Substring(0, length - 1);
            strDealed += "...";
            return strDealed;
        }
    }

    public static SqlDataReader GetDataReader(string strSql)
    {
        SqlConnection connection = CreateConnection();
        var command = new SqlCommand(strSql, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        return reader;
    }
}