<add name=”connection” connectionString=”Data Source=***.***.*.*;Initial Catalog=DataBaseName;Persist Security Info=True;User ID=SA**Uaser;Password=******” providerName=”System.Data.SqlClient”/>

Advertisements

DbHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace TestNameSpace
{
internal class DbHelper : IDisposable
{
public DbHelper()
{
OpenConnection();
}

#region PRIVATE MEMBERS

private SqlConnection _connection;

///

/// Gets Connection String from web configuration file
///

private static string ConnectionString
{
get { return ConfigurationManager.ConnectionStrings[“connection”].ConnectionString; }
}

#endregion

#region METHODS

///

/// Establishes connection with sql server
///

private void OpenConnection()
{
try
{
// create a new instance of sqlconnection class.
_connection = new SqlConnection(ConnectionString);

// if connection is open the close it and again open it.This approach is followed
// only for safety or ignoring the status any existing connection.
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
_connection.Open();
}
catch
{
throw new Exception(“An error occured while communicating to sql server database.”);
}
}

///

/// Closes existing sql server connection from database
///

private void CloseConnection()
{
try
{
if (_connection.State == ConnectionState.Open)
{
_connection.Close();
}
}
finally
{
_connection.Dispose();
}
}

///

/// Executes any Create , Update or Delete query and in lieu returns the total
/// number of rows affected by the query
///

/// sql statement as string whether it is sp or inline query.
/// command type
///
public int ExecuteNonQuery(string commandText, CommandType commandType)
{
int result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
result = command.ExecuteNonQuery();
}
return result;
}

///

/// Executes any Create , Update or Delete query and in lieu returns the total
/// number of rows affected by the query
///

/// sql statement as string
/// type of command whether it is sp or inline query
/// sql parameter
///
public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter parameter)
{
int result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
result = command.ExecuteNonQuery();
command.Parameters.Clear();

}
return result;
}

///

/// Executes any Create , Update or Delete query and in lieu returns the total
/// number of rows affected by the query
///

/// sql statement as string
/// type of command whether it is sp or inline query
/// array of sql parameter
///
public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] parameters)
{
int result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
result = command.ExecuteNonQuery();
command.Parameters.Clear();

}
return result;
}

///

/// Executes any Create , Update, Delete or even Select query and in lieu returns the value
/// of first row and first column
///

/// sql statement as string whether it is sp or inline query.
/// command type
///
public object ExecuteScalar(string commandText, CommandType commandType)
{
object result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
result = command.ExecuteScalar();

}
return result;
}

///

/// Executes any Create , Update, Delete or even Select query and in lieu returns the value
/// of first row and first column
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// sql parameter
///
public object ExecuteScalar(string commandText, CommandType commandType, SqlParameter parameter)
{
object result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
result = command.ExecuteScalar();
command.Parameters.Clear();

}
return result;
}

///

/// Executes any Create , Update, Delete or even Select query and in lieu returns the value
/// of first row and first column
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// array of sql parameter
///
public object ExecuteScalar(string commandText, CommandType commandType, SqlParameter[] parameters)
{
object result;
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
result = command.ExecuteScalar();
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the table of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// Datatable
public DataTable ExecuteSelect(string commandText, CommandType commandType)
{
var result = new DataTable();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
using (SqlDataReader reader = command.ExecuteReader())
{
result.Load(reader);
}
}
return result;
}

///

/// Executes Select query and in lieu returns the table of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// sql parameter
/// datatable
public DataTable ExecuteSelect(string commandText, CommandType commandType, SqlParameter parameter)
{
var result = new DataTable();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
using (SqlDataReader reader = command.ExecuteReader())
{
result.Load(reader);
}
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the table of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// array of sql parameter
/// Datatable
public DataTable ExecuteSelect(string commandText, CommandType commandType, SqlParameter[] parameters)
{
var result = new DataTable();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
using (SqlDataReader reader = command.ExecuteReader())
{
result.Load(reader);
}
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the set of tables of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// Datatset
public DataSet ExecuteSelectDataSet(string commandText, CommandType commandType)
{
var result = new DataSet();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
}
return result;
}

///

/// Executes Select query and in lieu returns the set of tables of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// sql parameter
/// dataset
public DataSet ExecuteSelectDataSet(string commandText, CommandType commandType, SqlParameter parameter)
{
var result = new DataSet();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.Add(parameter);
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
command.Parameters.Clear();

}
return result;
}

///

/// Executes Select query and in lieu returns the set of tables of the resultant query
///

/// sql statement as string whether it is sp or inline query.
/// command type
/// array of sql parameter
/// Dataset
public DataSet ExecuteSelectDataSet(string commandText, CommandType commandType, SqlParameter[] parameters)
{
var result = new DataSet();
using (var command = new SqlCommand(commandText, _connection))
{
command.CommandType = commandType;
command.Parameters.AddRange(parameters);
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(result);
}
command.Parameters.Clear();

}
return result;
}

#endregion

// Implement IDisposable.
// Do not make this method virtual.
// A derived class should not be able to override this method.

#region IDisposable Members

public void Dispose()
{
CloseConnection();

// This object will be cleaned up by the Dispose method.
// Therefore, you should call GC.SupressFinalize to
// take this object off the finalization queue
// and prevent finalization code for this object
// from executing a second time.
GC.SuppressFinalize(this);
}

#endregion
}
}