internal DataTable GetDataTable(string sqlCommand, DBParameterCollection paramCollection, IDbConnection connection, string tableName, CommandType commandType)
{
DataTable table = null;
if(tableName != string.Empty)
table = new DataTable(tableName);
else
table = new DataTable();
IDbCommand command = null;
if (paramCollection != null)
{
if(paramCollection.Parameters.Count > 0)
command = _commandBuilder.GetCommand(sqlCommand, connection, paramCollection, commandType);
else
command = _commandBuilder.GetCommand(sqlCommand, connection, commandType);
}
else
command = _commandBuilder.GetCommand(sqlCommand, connection, commandType);
DbDataAdapter adapter = GetDataAdapter(command);
ConstructorInfo constructor = adapter.GetType().GetConstructor(new Type[] { command.GetType() });
adapter = (DbDataAdapter)constructor.Invoke(new object[] { command });
MethodInfo method = adapter.GetType().GetMethod("Fill", new Type[] { typeof(DataTable) });
try
{
method.Invoke(adapter, new object[] { table });
}
catch (Exception err)
{
throw err;
}
return table;
}
internal DbDataAdapter GetDataAdapter(string sqlCommand, IDbConnection connection, DBParameterCollection paramCollection, CommandType commandType)
{
DbDataAdapter adapter = null;
IDbCommand command = _commandBuilder.GetCommand(sqlCommand, connection, paramCollection, commandType);
adapter = GetDataAdapter(command);
return adapter;
}
internal List<DbParameter> GetParameterCollection(DBParameterCollection parameterCollection)
{
List<DbParameter> dbParamCollection = new List<DbParameter>();
DbParameter dbParam = null;
foreach(DBParameter param in parameterCollection.Parameters)
{
dbParam = GetParameter(param);
dbParamCollection.Add(dbParam);
}
return dbParamCollection;
}
private void btnExecNonQuery2_Click(object sender, EventArgs e)
{
DBParameter param1 = new DBParameter("@FIRSTNAME", "Yash");
DBParameter param2 = new DBParameter("@LASTNAME", "Tripathi");
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param1);
paramCollection.Add(param2);
string insertCommand = "INSERT INTO USERDETAILS (FirstName, LastName, Email) VALUES (@FIRSTNAME, @LASTNAME, '*****@*****.**')";
string message = _dbHelper.ExecuteNonQuery(insertCommand, paramCollection) > 0 ? "Record inserted successfully." : "Error in inserting record.";
MessageBox.Show(message);
}
private void btnMisc1_Click(object sender, EventArgs e)
{
DBParameter param1 = new DBParameter("@FIRSTNAME", "Yash");
DBParameter param2 = new DBParameter("@LASTNAME", "Tripathi");
DBParameter param3 = new DBParameter("@EMAIL", "*****@*****.**");
DBParameter outParam = new DBParameter("@USERID", 0, DbType.Int32, ParameterDirection.Output);
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param1);
paramCollection.Add(param2);
paramCollection.Add(param3);
paramCollection.Add(outParam);
IDbTransaction transaction = _dbHelper.BeginTransaction();
IDbCommand command = null;
object retValue = null;
try
{
command = _dbHelper.GetCommand("PROC_DALC4NET_RETRIEVE_OUTPUT_VALUE", paramCollection, transaction, CommandType.StoredProcedure);
command.Transaction = transaction;
command.ExecuteNonQuery();
retValue = _dbHelper.GetParameterValue(3, command);
_dbHelper.CommitTransaction(transaction);
}
catch (Exception err)
{
_dbHelper.RollbackTransaction(transaction);
}
finally
{
_dbHelper.DisposeCommand(command);
}
MessageBox.Show(retValue != null ? "Returened Value is: " + retValue.ToString() : "null");
}
private void btnExecScalar3_Click(object sender, EventArgs e)
{
string sqlCommand = "SELECT Count(1) FROM USERDETAILS WHERE FIRSTNAME = @FIRSTNAME AND LASTNAME = @LASTNAME";
DBParameter param1 = new DBParameter("@FIRSTNAME", "ashish");
DBParameter param2 = new DBParameter("@LASTNAME", "tripathi");
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param1);
paramCollection.Add(param2);
object objCont = _dbHelper.ExecuteScalar(sqlCommand, paramCollection);
MessageBox.Show(objCont.ToString());
}
internal DataTable GetDataTable(string sqlCommand, DBParameter param, IDbConnection connection, string tableName, CommandType commandType)
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param);
return GetDataTable(sqlCommand, paramCollection , connection, tableName, commandType);
}
/// <summary>
/// Prepares command for the passed SQL Command or Stored Procedure.
/// Command is prepared for SQL Command only not for the stored procedures.
/// Use DisposeCommand method after use of the command.
/// </summary>
/// <param name="commandText">SQL Command or Stored Procedure name</param>
/// <param name="parameterCollection">Database parameter collection</param>
/// <param name="commandType">Type of Command i.e. Text or Stored Procedure</param>
/// <returns>Command ready for execute</returns>
public IDbCommand GetCommand(string commandText, DBParameterCollection parameterCollection, CommandType commandType)
{
IDbConnection connection = _connectionManager.GetConnection();
IDbCommand command = _commandBuilder.GetCommand(commandText, connection, parameterCollection, commandType);
return command;
}
/// <summary>
/// Executes the Sql Command and returns result.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="paramCollection">Database Parameter Collection</param>
/// <param name="transaction">Database Transacion (Use DBHelper.Transaction property.)</param>
/// <returns></returns>
public object ExecuteScalar(string commandText, DBParameterCollection paramCollection, IDbTransaction transaction)
{
return ExecuteScalar(commandText, paramCollection, transaction, CommandType.Text);
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and return resultset in the form of DataTable.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure name</param>
/// <param name="tableName">Table name</param>
/// <param name="paramCollection">Parameter collection to be associated with the Command or Stored Procedure.</param>
/// <param name="commandType">Type of command (i.e. Sql Command/ Stored Procedure name/ Table Direct)</param>
/// <returns>Result in the form of DataTable</returns>
public DataTable ExecuteDataTable(string commandText, string tableName, DBParameterCollection paramCollection, CommandType commandType)
{
DataTable dtReturn = new DataTable();
IDbConnection connection = null;
try
{
connection = _connectionManager.GetConnection();
dtReturn = _dbAdapterManager.GetDataTable(commandText, paramCollection, connection, tableName, commandType);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return dtReturn;
}
/// <summary>
/// Executes the Sql Command and return resultset in the form of DataSet.
/// </summary>
/// <param name="commandText">Sql Command </param>
/// <param name="paramCollection">Parameter collection to be associated with the command</param>
/// <returns>Result in the form of DataSet</returns>
public DataSet ExecuteDataSet(string commandText, DBParameterCollection paramCollection)
{
return ExecuteDataSet(commandText, paramCollection, CommandType.Text);
}
/// <summary>
/// Executes the Sql Command and return resultset in the form of DataSet.
/// </summary>
/// <param name="commandText">Sql Command </param>
/// <param name="param">Parameter to be associated with the command</param>
/// <returns>Result in the form of DataSet</returns>
public DataSet ExecuteDataSet(string commandText, DBParameter param)
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param);
return ExecuteDataSet(commandText, paramCollection);
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and return resultset in the form of DataSet.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure name</param>
/// <param name="paramCollection">Parameter collection to be associated with the command</param>
/// <param name="commandType">Type of command (i.e. Sql Command/ Stored Procedure name/ Table Direct)</param>
/// <returns>Result in the form of DataSet</returns>
public DataSet ExecuteDataSet(string commandText, DBParameterCollection paramCollection, CommandType commandType)
{
DataSet dataSet = new DataSet();
IDbConnection connection = _connectionManager.GetConnection();
IDataAdapter adapter = _dbAdapterManager.GetDataAdapter(commandText, connection, paramCollection, commandType);
try
{
adapter.Fill(dataSet);
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
return dataSet;
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and returns the IDataReader. Do remember to Commit or Rollback the transaction
/// </summary>
/// <param name="commandText">Sql Command or Stored Proc name</param>
/// <param name="paramCollection">Database Parameter Collection</param>
/// <param name="transaction">Database Transaction (Use DBHelper.Transaction property for getting the transaction.)</param>
/// <param name="commandType">Text/ Stored Procedure</param>
/// <returns>IDataReader</returns>
public IDataReader ExecuteDataReader(string commandText, DBParameterCollection paramCollection, IDbTransaction transaction, CommandType commandType)
{
IDataReader dataReader = null;
IDbConnection connection = transaction.Connection;
IDbCommand command = _commandBuilder.GetCommand(commandText, connection, paramCollection, commandType);
command.Transaction = transaction;
dataReader = command.ExecuteReader();
command.Dispose();
return dataReader;
}
/// <summary>
/// Executes the Sql Command and returns the IDataReader. Do remember to Commit or Rollback the transaction
/// </summary>
/// <param name="commandText">Sql Command </param>
/// <param name="paramCollection">Database Parameter Collection</param>
/// <param name="transaction">Database Transaction (Use DBHelper.Transaction property for getting the transaction.)</param>
/// <returns>IDataReader</returns>
public IDataReader ExecuteDataReader(string commandText, DBParameterCollection paramCollection, IDbTransaction transaction)
{
return ExecuteDataReader(commandText, paramCollection, transaction, CommandType.Text);
}
/// <summary>
/// Executes the Sql Command and returns result.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="paramCollection">Parameter collection to be associated.</param>
/// <returns>A single value. (First row's first cell value, if more than one row and column is returned.)</returns>
public object ExecuteScalar(string commandText, DBParameterCollection paramCollection)
{
return ExecuteScalar(commandText, paramCollection, null);
}
/// <summary>
/// Executes the Sql Command and return resultset in the form of DataTable.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="paramCollection">Parameter collection to be associated with the Command.</param>
/// <returns>Result in the form of DataTable</returns>
public DataTable ExecuteDataTable(string commandText, DBParameterCollection paramCollection)
{
return ExecuteDataTable(commandText, string.Empty, paramCollection, CommandType.Text);
}
public IDbCommand GetCommand(string commandText, DBParameter parameter, IDbTransaction transaction)
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(parameter);
return GetCommand(commandText, paramCollection, transaction, CommandType.Text);
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and return resultset in the form of DataTable.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure Name</param>
/// <param name="tableName">Table name</param>
/// <param name="param">Parameter to be associated with the Command.</param>
/// <param name="commandType">Type of command (i.e. Sql Command/ Stored Procedure name/ Table Direct)</param>
/// <returns>Result in the form of DataTable</returns>
public DataTable ExecuteDataTable(string commandText, string tableName, DBParameter param, CommandType commandType)
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param);
return ExecuteDataTable(commandText, tableName, paramCollection, commandType);
}
/// <summary>
/// Prepares command for the passed SQL Command or Stored Procedure.
/// Use DisposeCommand method after use of the command.
/// </summary>
/// <param name="commandText"></param>
/// <param name="parameterCollection"></param>
/// <param name="transaction"></param>
/// <param name="commandType"></param>
/// <returns></returns>
public IDbCommand GetCommand(string commandText, DBParameterCollection parameterCollection, IDbTransaction transaction, CommandType commandType)
{
IDbConnection connection = transaction != null ? transaction.Connection : _connectionManager.GetConnection();
IDbCommand command = _commandBuilder.GetCommand(commandText, connection, parameterCollection, commandType);
return command;
}
/// <summary>
/// Executes Sql Command or Stored procedure and returns number of rows affected.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure Name</param>
/// <param name="paramCollection">Parameter Collection to be associated with the comman</param>
/// <param name="transaction">Current Database Transaction (Use Helper.Transaction to get transaction)</param>
/// <param name="commandType">Type of command (i.e. Sql Command/ Stored Procedure name/ Table Direct)</param>
/// <returns>Number of rows affected.</returns>
public int ExecuteNonQuery(string commandText, DBParameterCollection paramCollection, IDbTransaction transaction, CommandType commandType)
{
int rowsAffected = 0;
IDbConnection connection = transaction != null ? transaction.Connection : _connectionManager.GetConnection();
IDbCommand command = _commandBuilder.GetCommand(commandText, connection, paramCollection, commandType);
command.Transaction = transaction;
try
{
rowsAffected = command.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (transaction == null)
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
if (command != null)
command.Dispose();
}
return rowsAffected;
}
/// <summary>
/// Executes Sql Command and returns number of rows effected.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="paramCollection">Parameter Collection to be associated with the command</param>
/// <returns>Number of rows effected.</returns>
public int ExecuteNonQuery(string commandText, DBParameterCollection paramCollection)
{
return ExecuteNonQuery(commandText, paramCollection, (IDbTransaction)null);
}
/// <summary>
/// Executes Sql Command and returns number of rows affected.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="paramCollection">Parameter Collection to be associated with the command</param>
/// <param name="transaction">Current Database Transaction (Use Helper.Transaction to get transaction)</param>
/// <returns>Number of rows affected.</returns>
public int ExecuteNonQuery(string commandText, DBParameterCollection paramCollection, IDbTransaction transaction)
{
return ExecuteNonQuery(commandText, paramCollection, transaction, CommandType.Text);
}
private void btnExecNonQuery5_Click(object sender, EventArgs e)
{
string message = "";
int rowsAffected = 0;
DBParameter param1 = new DBParameter("@FIRSTNAME", "Yash");
DBParameter param2 = new DBParameter("@LASTNAME", "Tripathi");
DBParameter param3 = new DBParameter("@EMAIL", "*****@*****.**");
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param1);
paramCollection.Add(param2);
paramCollection.Add(param3);
IDbTransaction transaction = _dbHelper.BeginTransaction();
try
{
rowsAffected = _dbHelper.ExecuteNonQuery("PROC_DALC4NET_EXECUTE_NON_QUERY_STORED_PROC_MULTIPLE_PARAM", paramCollection, transaction, CommandType.StoredProcedure);
message = rowsAffected > 0 ? "Record inserted successfully." : "Error in inserting record.";
_dbHelper.CommitTransaction(transaction);
}
catch (Exception err)
{
_dbHelper.RollbackTransaction(transaction);
}
MessageBox.Show(message);
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and returns result.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure name</param>
/// <param name="param">Database parameter</param>
/// <param name="transaction">Current Database Transaction (Use Helper.Transaction to get transaction)</param>
/// <param name="commandType">Text or Stored Procedure</param>
/// <returns>A single value. (First row's first cell value, if more than one row and column is returned.)</returns>
public object ExecuteScalar(string commandText, DBParameter param, IDbTransaction transaction, CommandType commandType)
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param);
return ExecuteScalar(commandText, paramCollection, transaction, commandType);
}
private void btnExecScalar6_Click(object sender, EventArgs e)
{
DBParameter param1 = new DBParameter("@FIRSTNAME", "ashish");
DBParameter param2 = new DBParameter("@LASTNAME", "tripathi");
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param1);
paramCollection.Add(param2);
object objCont = _dbHelper.ExecuteScalar("PROC_DALC4NET_EXECUTE_SCALAR_MULTIPLE_PARAM", paramCollection, CommandType.StoredProcedure);
MessageBox.Show(objCont.ToString());
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and returns result.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure name</param>
/// <param name="paramCollection">Parameter collection to be associated</param>
/// <param name="commandType">Type of command (i.e. Sql Command/ Stored Procedure name/ Table Direct)</param>
/// <returns>A single value. (First row's first cell value, if more than one row and column is returned.)</returns>
public object ExecuteScalar(string commandText, DBParameterCollection paramCollection, CommandType commandType)
{
return ExecuteScalar(commandText, paramCollection, (IDbTransaction)null, commandType);
}
private object InsertSQL()
{
object retValue = null;
string sqlCommand = "INSERT INTO UserTypes VALUES(@ID, @VALUE)";
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(new DBParameter("@ID", 13));
paramCollection.Add(new DBParameter("@VALUE", "Eleven"));
IDbTransaction transaction = _dbHelper.BeginTransaction();
try
{
IDataReader objScalar = _dbHelper.ExecuteDataReader(sqlCommand, paramCollection, transaction, CommandType.Text);
if (objScalar != null)
{
objScalar.Close();
objScalar.Dispose();
}
_dbHelper.CommitTransaction(transaction);
}
catch (Exception err)
{
_dbHelper.RollbackTransaction(transaction);
MessageBox.Show(err.Message);
}
return retValue;
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and returns result.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure name</param>
/// <param name="paramCollection">Database parameter Collection</param>
/// <param name="transaction">Current Database Transaction (Use Helper.Transaction to get transaction)</param>
/// <param name="commandType">Text or Stored Procedure</param>
/// <returns>A single value. (First row's first cell value, if more than one row and column is returned.)</returns>
public object ExecuteScalar(string commandText, DBParameterCollection paramCollection, IDbTransaction transaction, CommandType commandType)
{
object objScalar = null;
IDbConnection connection = transaction != null ? transaction.Connection : _connectionManager.GetConnection();
IDbCommand command = _commandBuilder.GetCommand(commandText, connection, paramCollection, commandType);
command.Transaction = transaction;
try
{
objScalar = command.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (transaction == null)
{
if (connection != null)
{
connection.Close();
connection.Dispose();
}
}
if (command != null)
command.Dispose();
}
return objScalar;
}
private void btnExecNonQuery3_Click(object sender, EventArgs e)
{
DBParameter param1 = new DBParameter("@FIRSTNAME", "Yash");
DBParameter param2 = new DBParameter("@LASTNAME", "Tripathi");
DBParameter param3 = new DBParameter("@EMAIL", "*****@*****.**");
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param1);
paramCollection.Add(param2);
paramCollection.Add(param3);
string message = _dbHelper.ExecuteNonQuery("PROC_DALC4NET_EXECUTE_NON_QUERY_STORED_PROC_MULTIPLE_PARAM", paramCollection, CommandType.StoredProcedure) > 0 ? "Record inserted successfully." : "Error in inserting record.";
MessageBox.Show(message);
}
internal DbDataAdapter GetDataAdapter(string sqlCommand, IDbConnection connection, DBParameterCollection paramCollection, CommandType commandType)
{
DbDataAdapter adapter = null;
IDbCommand command = _commandBuilder.GetCommand(sqlCommand, connection, paramCollection, commandType);
adapter = GetDataAdapter(command);
return(adapter);
}