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 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);
}
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 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;
}
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());
}
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());
}
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);
}
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);
}
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 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);
}
/// <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>
/// 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 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);
}