Thursday, 12 September 2013
DataLayer
namespace DataAccessLayer
{
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using System.Text;
///
/// Represent the Description of the DataAccess.
///
public sealed class DataAccessProvider
{
#region Variable declaration.
///
/// Connection string.
///
private static string connectionString;
///
/// Connectionn Object.
///
private static SqlConnection conn;
///
/// Command Object.
///
private static SqlCommand command;
///
/// Adapte Object.
///
private static SqlDataAdapter adapter;
///
/// Transaction Object.
///
private static SqlTransaction transaction;
///
/// Simple Object.
///
private static object result;
#endregion
///
/// Prevents a default instance of the
private DataAccessProvider()
{
}
///
/// Gets or sets connection string.
///
/// Connection string as string
public static string ConnectionString
{
get
{
if (String.IsNullOrEmpty(connectionString))
{
try
{
connectionString = System.Configuration.ConfigurationManager.AppSettings["connectionString"].ToString();
}
catch (NullReferenceException)
{
connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ToString();
}
}
else if (String.IsNullOrEmpty(connectionString))
{
AppSettingsReader objConfig = new AppSettingsReader();
connectionString = objConfig.GetValue("connectionString", typeof(string)).ToString();
}
return connectionString;
}
set
{
connectionString = value;
}
}
///
/// Return the Collection of the record against Query.
///
/// The object of the Query.
/// The dataset of the Result set.
public static DataSet GetDataSet(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
adapter = new SqlDataAdapter(command);
if (query.DataSet == null)
{
query.DataSet = new DataSet();
query.DataSet.Locale = CultureInfo.InvariantCulture;
}
conn.Open();
if (!String.IsNullOrEmpty(query.DataSetTableName))
{
if ((query.DataSet.Tables.Count == 0) || (query.DataSet.Tables[query.DataSetTableName] == null))
{
query.DataSet.Tables.Add(query.DataSetTableName);
}
adapter.Fill(query.DataSet, query.DataSetTableName);
}
else
{
adapter.Fill(query.DataSet);
}
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return query.DataSet;
}
///
/// Return the Collecton of records against Query.
///
/// The object the query.
/// The datatable of recordset.
public static DataTable GetDataTable(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
adapter = new SqlDataAdapter(command);
if (query.DataTable == null)
{
query.DataTable = new DataTable();
query.DataTable.Locale = CultureInfo.InvariantCulture;
}
conn.Open();
adapter.Fill(query.DataTable);
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return query.DataTable;
}
///
/// Gets the value of specified colum in its native format.
///
/// The object of the Query.
/// The object of the column in its native format.
public static object GetValues(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
conn.Open();
SqlDataReader read = command.ExecuteReader();
while (read.Read())
{
query.ReturnValue = read.GetValue(0);
}
read.Close();
read = null;
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (adapter != null)
{
adapter.Dispose();
}
if (command != null)
{
command.Dispose();
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return query.ReturnValue;
}
///
/// Excute the inset and update Query.
///
/// The object of the Query.
/// The first column of first rows in the result set.
public static object ExecuteScalar(Query query)
{
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
conn.Open();
transaction = conn.BeginTransaction();
command.Transaction = transaction;
result = command.ExecuteScalar();
PopulateOutPutParamValues(query.Parameters, command);
transaction.Commit();
return result;
}
catch (MyException)
{
throw;
}
catch (Exception)
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
}
///
/// Excute the inset and update Query.
///
/// The object of the Query.
/// The Number of rows affected.
public static int ExecuteNonQuery(Query query)
{
int result = 0;
try
{
PopulatCommandObject(ref conn, ref command, query.Name, query.Parameters);
conn.Open();
transaction = conn.BeginTransaction();
command.Transaction = transaction;
result = command.ExecuteNonQuery();
PopulateOutPutParamValues(query.Parameters, command);
transaction.Commit();
return result;
}
catch (MyException)
{
throw;
}
catch (Exception)
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
}
///
/// Excute the set of query string againt connection.
///
/// set of query string as arraylist
/// The Number of row affected.
public static int ExecuteNonQuery(ArrayList arrayQuery)
{
int result = 0;
SqlConnection conn = null;
SqlCommand command = null;
Query query;
// start the Ienum collection.
if (conn == null)
{
conn = new SqlConnection(ConnectionString);
}
conn.Open();
if (command == null)
{
command = new SqlCommand();
transaction = conn.BeginTransaction();
command.Transaction = transaction;
}
try
{
IEnumerator objenum = arrayQuery.GetEnumerator();
objenum.MoveNext();
do
{
query = (Query)objenum.Current;
PopulatCommandObject(conn, command, query.Name, query.Parameters);
result = command.ExecuteNonQuery();
PopulateOutPutParamValues(command, query);
command.Parameters.Clear();
}
while (objenum.MoveNext());
transaction.Commit();
}
catch (MyException)
{
throw;
}
catch (Exception)
{
if (transaction != null)
{
transaction.Rollback();
}
throw;
}
finally
{
if (conn != null)
{
conn.Close();
if (command != null)
{
command.Dispose();
}
if (transaction != null)
{
transaction.Dispose();
transaction = null;
}
conn.Dispose();
conn = null;
connectionString = string.Empty;
}
}
return result;
}
///
/// Build the command object.
///
/// The connection object reference.
/// The command object Reference.
/// The Command Text.
/// The parameter name as parameter list.
private static void PopulatCommandObject(ref SqlConnection conn, ref SqlCommand command, string queryName, List parameters)
{
try
{
if (conn == null)
{
conn = new SqlConnection(ConnectionString);
}
command = new SqlCommand(queryName, conn);
command.CommandType = CommandType.StoredProcedure;
foreach (Parameter parameter in parameters)
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + parameter.ParameterName;
param.DbType = parameter.DbType;
param.Value = parameter.Value;
param.Direction = parameter.Direction;
command.Parameters.Add(param);
// command.Parameters.AddWithValue("@" + parameter.ParameterName, parameter.DbType.ToString()).Value = parameter.Value;
}
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
}
///
/// Build the command object.
///
/// The connection object Reference.
/// The command object Reference.
/// The command text.
/// The parameter name as parameter list.
private static void PopulatCommandObject(SqlConnection conn, SqlCommand command, string queryName, List parameters)
{
try
{
command.Connection = conn;
command.CommandText = queryName;
command.CommandType = CommandType.StoredProcedure;
foreach (Parameter parameter in parameters)
{
SqlParameter param = new SqlParameter();
param.ParameterName = "@" + parameter.ParameterName;
param.DbType = parameter.DbType;
param.Value = parameter.Value;
param.Direction = parameter.Direction;
command.Parameters.Add(param);
}
}
catch (MyException)
{
throw;
}
catch (Exception)
{
throw;
}
}
///
/// Build output parameter values.
///
/// the object of IDbCommand interface.
/// the object of the query.
private static void PopulateOutPutParamValues(IDbCommand command, Query query)
{
query.Parameters.Clear(); // only return output and return type param from Storedprocedure.
foreach (IDataParameter param in command.Parameters)
{
query.Parameters.Add(new Parameter(param.ParameterName, param.DbType, param.Value, param.Direction));
}
}
///
/// Build ouput parameter.
///
/// the parameter of the parameter list.
/// the object of the IDbcommand interface.
private static void PopulateOutPutParamValues(List parameters, IDbCommand command)
{
parameters.Clear(); // only return output and return type param from Storedprocedure.
foreach (IDataParameter param in command.Parameters)
{
parameters.Add(new Parameter(param.ParameterName, param.DbType, (command.Parameters[param.ParameterName] as IDataParameter).Value, param.Direction));
}
}
}
}
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment