|
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace JS.Data
{
public sealed class DataProvider
{
#region Fields
private static volatile DataProvider instance;
private static object syncRoot = new Object();
private string _connectionString = string.Empty;
private SqlConnection _dbConnection = null;
private SqlTransaction _dbTransaction = null;
#endregion
#region Properties
public static DataProvider Instance
{
get
{
if (instance == null)
{
lock (syncRoot)
{
if (instance == null)
instance = new DataProvider();
}
}
return instance;
}
}
public string ConnectionString
{
get { return _connectionString; }
set { _connectionString = value; }
}
#endregion
#region Constructor
private DataProvider()
{
_connectionString = ConfigurationManager.ConnectionStrings["Js_Connection"].ConnectionString;
}
#endregion
#region Methods
public void StartTransaction()
{
try
{
this.OpenConnection();
if (_dbConnection != null)
_dbTransaction = _dbConnection.BeginTransaction(IsolationLevel.ReadCommitted, "dbTransaction");
}
catch
{
_dbTransaction = null;
this.CloseConnection();
}
}
public void CommitTransaction()
{
try
{
if (_dbTransaction != null)
_dbTransaction.Commit();
}
catch
{
throw;
}
finally
{
_dbTransaction = null;
this.CloseConnection();
}
}
public void RollbackTransaction()
{
try
{
if (_dbTransaction != null)
_dbTransaction.Rollback();
}
catch
{
throw;
}
finally
{
_dbTransaction = null;
this.CloseConnection();
}
}
public void ExecuteQuery(string commandText)
{
SqlCommand dbCommand = GetDataCommand(commandText);
try
{
if (dbCommand != null)
dbCommand.ExecuteNonQuery();
}
catch
{
throw;
}
finally
{
if (dbCommand != null)
{
if (dbCommand.Connection != null)
{
if (dbCommand.Connection.State == ConnectionState.Open)
{
dbCommand.Connection.Close();
dbCommand.Connection.Dispose();
}
}
dbCommand.Dispose();
}
}
}
public object ExecuteQuery(string commandText, Hashtable @Parameters)
{
object result = null;
SqlCommand dbCommand = GetDataCommand(commandText, @Parameters);
try
{
if (dbCommand != null)
{
if (this._dbTransaction == null)
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
result = dbCommand.ExecuteNonQuery();
if (dbCommand.Parameters != null && dbCommand.Parameters.Contains("@Output"))
result = dbCommand.Parameters["@Output"].Value;
}
}
catch
{
throw;
}
finally
{
if (dbCommand != null)
{
if (dbCommand.Transaction == null)
{
if (dbCommand.Connection != null)
{
if (dbCommand.Connection.State == ConnectionState.Open)
{
dbCommand.Connection.Close();
dbCommand.Connection.Dispose();
}
}
}
dbCommand.Dispose();
}
}
return result;
}
public object ExecuteNonQuery(string commandText, Hashtable @parameters)
{
object result = null;
string strResult = string.Empty;
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
if (dbCommand != null)
{
if (this._dbTransaction == null)
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (_dbTransaction != null)
dbCommand.Transaction = this._dbTransaction;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
{
result = dbCommand.ExecuteNonQuery();
if (dbCommand.Parameters != null && dbCommand.Parameters.Contains("@Output"))
result = dbCommand.Parameters["@Output"].Value;
dbCommand.Dispose();
}
}
}
catch
{
result = -1;
}
finally
{
if (_dbTransaction == null)
this.CloseConnection();
}
return result;
}
public int ExecuteNonQuery(string commandText)
{
int result = -1;
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText);
if (dbCommand != null)
{
if (this._dbTransaction == null)
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (_dbTransaction != null)
dbCommand.Transaction = this._dbTransaction;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
result = dbCommand.ExecuteNonQuery();
dbCommand.Dispose();
}
}
catch
{
result = -1;
}
finally
{
if (_dbTransaction == null)
this.CloseConnection();
}
return result;
}
public int ExecuteScaler(string commandText, Hashtable @parameters)
{
object result = string.Empty;
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
if (dbCommand != null)
{
if (this._dbTransaction == null)
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (_dbTransaction != null)
dbCommand.Transaction = this._dbTransaction;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
{
result = dbCommand.ExecuteScalar();
if (@parameters.Contains("@output"))
{
int output = 0;
int.TryParse(@parameters["@output"].ToString(), out output);
if (output > 0)
result = output;
}
dbCommand.Dispose();
}
}
}
catch
{
result = -1;
}
finally
{
if (_dbTransaction == null)
this.CloseConnection();
}
return Convert.ToInt32(result);
}
public object ExecuteScalarQuery(string commandText)
{
object result = "";
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText);
if (dbCommand != null)
{
if (this._dbTransaction == null)
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (_dbTransaction != null)
dbCommand.Transaction = this._dbTransaction;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
result = dbCommand.ExecuteScalar();
dbCommand.Dispose();
}
}
catch
{
result = -1;
}
finally
{
if (_dbTransaction == null)
this.CloseConnection();
}
return result;
}
public DataSet GetDataSet(string commandText, Hashtable @parameters)
{
DataSet result = new DataSet();
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
if (dbCommand != null)
{
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
{
using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
{
dbAdapter.Fill(result);
}
}
dbCommand.Dispose();
}
}
catch
{
result = null;
}
finally
{
this.CloseConnection();
}
return result;
}
public DataSet GetDataSet(string commandText)
{
DataSet result = new DataSet();
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText);
if (dbCommand != null)
{
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
{
using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
{
dbAdapter.Fill(result);
}
}
dbCommand.Dispose();
}
}
catch
{
result = null;
}
finally
{
this.CloseConnection();
}
return result;
}
public DataTable GetDataTable(string commandText, Hashtable @parameters)
{
DataTable result = new DataTable();
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText, parameters);
if (dbCommand != null)
{
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
{
using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
{
dbAdapter.Fill(result);
}
}
dbCommand.Dispose();
}
}
catch
{
result = null;
}
finally
{
this.CloseConnection();
}
return result;
}
public DataTable GetDataTable(string commandText)
{
DataTable result = new DataTable();
try
{
SqlCommand dbCommand = this.GetDataCommand(commandText);
dbCommand.CommandTimeout = 60;
if (dbCommand != null)
{
this.OpenConnection();
dbCommand.Connection = this._dbConnection;
if (dbCommand.Connection != null && dbCommand.Connection.State == ConnectionState.Open)
{
using (SqlDataAdapter dbAdapter = new SqlDataAdapter(dbCommand))
{
dbAdapter.Fill(result);
}
}
dbCommand.Dispose();
}
}
catch
{
result = null;
}
finally
{
this.CloseConnection();
}
return result;
}
#endregion
#region Private Methods
private void OpenConnection()
{
try
{
if (_dbConnection == null)
_dbConnection = new SqlConnection(ConnectionString);
_dbConnection.Open();
}
catch
{
_dbConnection = null;
}
}
private void CloseConnection()
{
try
{
if (_dbConnection != null && _dbConnection.State == ConnectionState.Open)
_dbConnection.Close();
_dbConnection = null;
}
catch
{
_dbConnection = null;
}
}
private SqlCommand GetDataCommand(string commandText, Hashtable @parameters)
{
SqlCommand dbCommand = null;
try
{
dbCommand = new SqlCommand();
dbCommand.CommandText = commandText;
dbCommand.CommandType = CommandType.StoredProcedure;
if (parameters != null && parameters.Count > 0)
{
foreach (string parameterKey in parameters.Keys)
{
SqlParameter dbParameter = new SqlParameter(parameterKey, parameters[parameterKey]);
if (parameterKey == "@Output")
{
dbParameter.Direction = ParameterDirection.Output;
dbParameter.SqlDbType = System.Data.SqlDbType.NVarChar;
dbParameter.Size = -1;
}
dbCommand.Parameters.Add(dbParameter);
}
}
}
catch
{
dbCommand = null;
}
return dbCommand;
}
//private SqlCommand GetDataCommand(string commandText, Hashtable @Parameters)
//{
// SqlCommand dbCommand = null;
// try
// {
// if (this.dbTransaction == null)
// OpenConnection();
// if (this.dbConnection != null && this.dbConnection.State == ConnectionState.Open)
// {
// dbCommand = new SqlCommand();
// dbCommand.Connection = this.dbConnection;
// if (this.dbTransaction != null)
// dbCommand.Transaction = this.dbTransaction;
// dbCommand.CommandText = commandText;
// dbCommand.CommandType = CommandType.StoredProcedure;
// if (@Parameters != null)
// {
// foreach (string parameter in @Parameters.Keys)
// {
// SqlParameter dbParameter = new SqlParameter(parameter, @Parameters[parameter]);
// if (parameter == "@Output")
// dbParameter.Direction = ParameterDirection.Output;
// dbCommand.Parameters.Add(dbParameter);
// }
// }
// }
// }
// catch (SqlException ex)
// {
// throw ex;
// //logger.Error("SQL Error: ", ex);
// }
// catch (Exception ex)
// {
// throw ex;
// //logger.Error("Data Error: ", ex);
// }
// return dbCommand;
//}
private SqlCommand GetDataCommand(string commandText)
{
SqlCommand dbCommand = null;
try
{
dbCommand = new SqlCommand();
dbCommand.CommandText = commandText;
dbCommand.CommandType = CommandType.Text;
}
catch
{
dbCommand = null;
}
return dbCommand;
}
#endregion
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.