internal DbDataAdapter GetDataAdapter(string sqlCommand, IDbConnection connection, DBParameter param, CommandType commandType)
{
DbDataAdapter adapter = null;
IDbCommand command = _commandBuilder.GetCommand(sqlCommand, connection, param , commandType);
adapter = GetDataAdapter(command);
return adapter;
}
internal DbParameter GetParameter(DBParameter parameter)
{
DbParameter dbParam = GetParameter();
dbParam.ParameterName = parameter.Name;
dbParam.Value = parameter.Value;
dbParam.Direction = parameter.ParamDirection;
dbParam.DbType = parameter.Type;
return dbParam;
}
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);
}
/// <summary>
/// Executes the Sql Command and returns result.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="param">Parameter 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, DBParameter param)
{
return ExecuteScalar(commandText, param, (IDbTransaction)null);
}
/// <summary>
/// Removes parameter from the Parameter Collection
/// </summary>
/// <param name="parameter">Parameter to be removed</param>
public void Remove(DBParameter parameter)
{
_parameterCollection.Remove(parameter);
}
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());
}
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);
}
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.
/// 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</param>
/// <param name="parameter">Database parameter</param>
/// <returns>Command ready for execute</returns>
public IDbCommand GetCommand(string commandText, DBParameter parameter)
{
return GetCommand(commandText, parameter, 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 and returns the IDataReader. Do remember to Commit or Rollback the transaction
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="param">Database Parameter</param>
/// <param name="transaction">Database Transaction (Use DBHelper.Transaction property for getting the transaction.)</param>
/// <returns>Data Reader</returns>
public IDataReader ExecuteDataReader(string commandText, DBParameter param, IDbTransaction transaction)
{
return ExecuteDataReader(commandText, param, transaction, CommandType.Text);
}
/// <summary>
/// Executes the Sql Command and returns the DataReader.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="con">Database Connection object (DBHelper.GetConnObject() may be used)</param>
/// <param name="param">Parameter to be associated with the Sql Command.</param>
/// <returns>DataReader</returns>
public IDataReader ExecuteDataReader(string commandText, IDbConnection connection, DBParameter param)
{
return ExecuteDataReader(commandText, connection, param, CommandType.Text);
}
/// <summary>
/// Executes the Sql Command or Stored Procedure and returns the DataReader.
/// </summary>
/// <param name="commandText">Sql Command or Stored Procedure Name</param>
/// <param name="con">Database Connection object (DBHelper.GetConnObject() may be used)</param>
/// <param name="param">Parameter to be associated with the Sql Command or Stored Procedure.</param>
/// <param name="commandType">Type of command (i.e. Sql Command/ Stored Procedure name/ Table Direct)</param>
/// <returns>DataReader</returns>
public IDataReader ExecuteDataReader(string commandText, IDbConnection connection, DBParameter param, CommandType commandType)
{
DBParameterCollection paramCollection = new DBParameterCollection();
paramCollection.Add(param);
return ExecuteDataReader(commandText, connection, paramCollection, commandType);
}
internal DbDataAdapter GetDataAdapter(string sqlCommand, IDbConnection connection, DBParameter param, CommandType commandType)
{
DbDataAdapter adapter = null;
IDbCommand command = _commandBuilder.GetCommand(sqlCommand, connection, param, commandType);
adapter = GetDataAdapter(command);
return(adapter);
}
/// <summary>
/// Executes the Sql Command and returns result.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="param">Parameter to be associated</param>
/// <param name="transaction">Database Transacion</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)
{
return ExecuteScalar(commandText, param, transaction, CommandType.Text);
}
/// <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="parameter">Database parameter</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, DBParameter parameter, CommandType commandType)
{
IDbConnection connection = _connectionManager.GetConnection();
IDbCommand command = _commandBuilder.GetCommand(commandText, connection, parameter, commandType);
return command;
}
/// <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);
}
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 and return resultset in the form of DataTable.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="param">Parameter to be associated with the Command.</param>
/// <returns>Result in the form of DataTable</returns>
public DataTable ExecuteDataTable(string commandText, DBParameter param)
{
return ExecuteDataTable(commandText, string.Empty, param, CommandType.Text);
}
/// <summary>
/// Executes Sql Command and returns number of rows effected.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="param">Parameter to be associated with the command</param>
/// <returns>Number of rows effected.</returns>
public int ExecuteNonQuery(string commandText, DBParameter param)
{
return ExecuteNonQuery(commandText, param, (IDbTransaction)null);
}
/// <summary>
/// Executes Sql Command or Stored procedure and returns number of rows affected.
/// </summary>
/// <param name="commandText">Sql Command</param>
/// <param name="param">Parameter 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, DBParameter param, IDbTransaction transaction)
{
return ExecuteNonQuery(commandText, param, transaction, CommandType.Text);
}
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());
}
/// <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">Parameter 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, DBParameter param, CommandType commandType)
{
return ExecuteScalar(commandText, param, null, commandType);
}
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");
}
/// <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 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);
}
/// <summary>
/// Adds a DBParameter to the ParameterCollection
/// </summary>
/// <param name="parameter">Parameter to be added</param>
public void Add(DBParameter parameter)
{
_parameterCollection.Add(parameter);
}
