public class DataHelper
{
/// <summary>
/// This method is created for the insert update table using Stored procedure.
/// </summary>
/// <param name="strProcedureName"></param>
/// <param name="objDbTransaction"></param>
/// <param name="objDbParameterCollection"></param>
public static void ExecuteNonQuery(string strProcedureName, DbTransaction objDbTransaction, DbParameter[] objDbParameter)
{
DbProviderFactory objDbProviderFactory = DbProviderFactories.GetFactory(Utility.ProviderName);
DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
objDbCommand.CommandType = CommandType.StoredProcedure;
objDbCommand.CommandText = strProcedureName;
objDbCommand.Connection = objDbTransaction.Connection;
objDbCommand.Transaction = objDbTransaction;
foreach (DbParameter objDbParameterNew in objDbParameter)
{
objDbCommand.Parameters.Add(objDbParameterNew);
}
objDbCommand.ExecuteNonQuery();
}
/// <summary>
/// This method is created for executing reader using query(Select query/(View)).
/// </summary>
/// <param name="strQuery"></param>
/// <returns></returns>
public static DbDataReader ExecuteReader(string strQuery, DbParameter[] objDbParameter)
{
DbConnection objDbConnection = Utility.GetConnection;
try
{
DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;
objDbConnection.Open();
DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
objDbCommand.CommandType = CommandType.Text;
objDbCommand.Connection = objDbConnection;
objDbCommand.CommandText = strQuery;
if (objDbParameter != null)
{
foreach (DbParameter objDbParameterNew in objDbParameter)
{
objDbCommand.Parameters.Add(objDbParameterNew);
}
}
return objDbCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
finally
{
objDbConnection.Close();
}
}
/// <summary>
/// This method is created for select the single value
/// </summary>
/// <param name="strQuery"></param>
/// <returns></returns>
public static object ExecuteScalar(string strQuery, DbParameter[] objDbParameter)
{
DbConnection objDbConnection = Utility.GetConnection;
try
{
DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;
objDbConnection.Open();
DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
objDbCommand.CommandType = CommandType.Text;
objDbCommand.CommandText = BuildQuery(strQuery);
objDbCommand.Connection = objDbConnection;
if (objDbParameter != null)
{
foreach (DbParameter objDbParameterNew in objDbParameter)
{
objDbCommand.Parameters.Add(objDbParameterNew);
}
}
return objDbCommand.ExecuteScalar();
}
finally
{
objDbConnection.Close();
}
}
/// <summary>
/// This method returns datatable.
/// </summary>
/// <param name="strQuery"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string strQuery, DbParameter[] objDbParameter)
{
DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;
DbConnection objDbConnection = Utility.GetConnection;
objDbConnection.Open();
DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
objDbCommand.CommandType = CommandType.Text;
objDbCommand.CommandText = BuildQuery(strQuery);
objDbCommand.Connection = objDbConnection;
if (objDbParameter != null)
{
foreach (DbParameter objDbParameterNew in objDbParameter)
{
objDbCommand.Parameters.Add(objDbParameterNew);
}
}
DbDataAdapter objDbDataAdapter = objDbProviderFactory.CreateDataAdapter();
objDbDataAdapter.SelectCommand = objDbCommand;
DataTable objDataTable = new DataTable();
objDbDataAdapter.Fill(objDataTable);
objDbConnection.Close();
return objDataTable;
}
/// <summary>
/// This method returns dataset.
/// </summary>
/// <param name="strQuery"></param>
/// <returns></returns>
public static DataSet ExecuteDataSet(string strQuery)
{
DbProviderFactory objDbProviderFactory = Utility.GetProviderFactory;
DbConnection objDbConnection = Utility.GetConnection;
objDbConnection.Open();
DbCommand objDbCommand = objDbProviderFactory.CreateCommand();
objDbCommand.CommandType = CommandType.Text;
objDbCommand.CommandText = BuildQuery(strQuery);
objDbCommand.Connection = objDbConnection;
DbDataAdapter objDbDataAdapter = objDbProviderFactory.CreateDataAdapter();
objDbDataAdapter.SelectCommand = objDbCommand;
DataSet objDataSet = new DataSet();
objDbDataAdapter.Fill(objDataSet);
objDbConnection.Close();
return objDataSet;
}
private static string BuildQuery(string strQuery)
{
if (Utility.GetProviderFactory.GetType().ToString() == "System.Data.SqlClient.SqlClientFactory")
{
strQuery = strQuery.Replace("@", "@");
}
else if (Utility.GetProviderFactory.GetType().ToString() == "System.Data.OracleClient.OracleClientFactory")
{
strQuery = strQuery.Replace("@", ":");
}
else
{
strQuery = strQuery.Replace("@", "?");
}
return strQuery;
}
}