Nice C# class for ADO.net databinding

Nice class with a couple of methods to handle execution of a stored procedure with an array of parameters or a regular query and return a dataset object, all you need do is pass in the connection string when creating the connection.

[codesyntax lang=”csharp”]

using System;
using System.Data.SqlClient;
using System.Data;

namespace Connection
{
  public class Connect
  {
    private SqlConnection sqlConn;

    // Constructor
    public Connect(string ConnectionString)
    {
      sqlConn = new SqlConnection(ConnectionString);
    }

    // Open the connection 
    public void OpenCon()
    {
      sqlConn.Open();
    }

    // Close the connection 
    public void CloseCon()
    {
      sqlConn.Close();
    }

    public DataSet ExecuteProc(string spName, SqlParameter[] spParams)
    {
      try
      {
        SqlCommand cmd = new SqlCommand(spName, sqlConn);
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Clear();
        if (spParams != null)
        {
          cmd.Parameters.AddRange(spParams);
        }
        DataSet ds = new DataSet();
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          OpenCon();
          da.Fill(ds);
          cmd.Dispose();
          da.Dispose();
        }
        return ds;
      }
      catch (Exception ex)
      {
        throw new Exception("Error while executing the stored proc : " + ex.Message);
      }
      finally
      {
        CloseCon();
      }
    }

    public DataSet ExecuteQuery(string queryString)
    {
      try
      {
        SqlCommand cmd = new SqlCommand(queryString, sqlConn);
        cmd.CommandTimeout = 0;
        cmd.CommandType = CommandType.Text;

        DataSet ds = new DataSet();
        using (SqlDataAdapter da = new SqlDataAdapter(cmd))
        {
          OpenCon();
          da.Fill(ds);
          cmd.Dispose();
          da.Dispose();
        }
        return ds;
      }
      catch (Exception ex)
      {
        throw new Exception("Error while executing the query : " + ex.Message);
      }
      finally
      {
        CloseCon();
      }
    }

  }
}

[/codesyntax]

 

Usage in your code behind, place these using directives

[codesyntax lang=”csharp”]

using Connection;
using System.Data.SqlClient;

[/codesyntax]

Call a stored procedure in an event within the page such as follows

[codesyntax lang=”csharp”]

Connect Conn = new Connect(WebConfigurationManager.ConnectionStrings["NAMEOFCONNECTIONSTRING"].ConnectionString);
      Conn.ExecuteProc("AddRecord", new SqlParameter[] {
      new SqlParameter("@Name", txtName.Text),
      new SqlParameter("@Address", txtAddress.Text),
      new SqlParameter("@Telephone", txtTelephone.Text),
      new SqlParameter("@Comments", txtComments.Text),
      new SqlParameter("@CodeField", ddlCodeField.SelectedValue),
    });

[/codesyntax]