All In One Class For ADO.NET

CRUD Operations using ADO.NET with SQL Server 2008 are mostly used in the .NET.

In the beginning, I was little confused about how and where to make connection, how many connections to open and how to manage all connection with SQL Server database.

And one more question was that which method of ADO.NET, I need to access to get optimum result from the SQL Server. So I can retrieve optimum result and manipulate it as per the need.

So I collected all the possible methods and got the differences and implemented the common class which provide support for all basic operation from open SQL connection, execution of query, receiving the result and close SQL connection.

Each method has its own purpose. like

Method Description
public override Object ExecuteScalar() Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.
public override int ExecuteNonQuery() Executes a Transact-SQL statement against the connection and returns the number of rows affected.
public virtual int Fill(DataSet dataSet) The Fill method retrieves rows from the data source using the SELECT statement specified by an associated Select Command property.

I have implemented one common class. So we can directly call the right method just passing the required parameters.

//------------DBFunctions.cs file----------------------------
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Data.SqlClient;

public class DBFunctions
{
public static string CONNSTR =
ConfigurationManager.ConnectionStrings["DBCNSTR"].ConnectionString;

public DBFunctions()
{
}

public static object
GetScalarParameterizedDB(string sql, SqlParameter[] pArr)
{
   SqlConnection conn = new SqlConnection(CONNSTR);
   object obj = null;
   try
   {
       conn.Open();
       SqlCommand cmd = new SqlCommand(sql, conn);
       for (int i = 0; i < pArr.Length; i++)
       cmd.Parameters.Add(pArr[i]);
       obj = cmd.ExecuteScalar();
       conn.Close();
   }
   catch (Exception ex)
   {
       throw ex;
   }
   finally
   {
       conn.Close(); // very important with databases
   }
return obj;
}

public static object GetScalarDB(string sql)
{
   SqlConnection conn = new SqlConnection(CONNSTR);
   object obj = null;
   try
   {
      conn.Open();
      SqlCommand cmd = new SqlCommand(sql, conn);
      obj = cmd.ExecuteScalar();
      conn.Close();

   }
   catch (Exception ex)
   {
      throw ex;
   }
   finally
   {
      conn.Close(); // very important with databases
   }
return obj;

}

public static int GetNonQueryDB(string sql)
{
   SqlConnection conn = new SqlConnection(CONNSTR);
   int rows = 0;
   try
   {
      conn.Open();
      SqlCommand cmd = new SqlCommand(sql, conn);
      rows = cmd.ExecuteNonQuery();
      conn.Close();
   }
   catch (Exception ex)
   {
      throw ex;
   }
   finally
   {
      conn.Close(); // very important with databases
   }
return rows;
}

public static DataSet GetDataSetDB(string sql)
 {
   SqlConnection conn = new SqlConnection(CONNSTR);
   DataSet ds = null;
   try
   {
      conn.Open();
      SqlDataAdapter da = new SqlDataAdapter(sql, conn);
      ds = new DataSet();
      da.Fill(ds);
      conn.Close();
   }
   catch (Exception ex)
   {
   throw ex;
   }
   finally
   {
      conn.Close(); // very important with databases
   }
   return ds;
 }
}

Thanks...
Advertisements