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 class from being created. /// 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)); } } } }

No comments:

Post a Comment