/// <summary>
/// Executes a SQL Query that returns no results
/// </summary>
/// <param name="SQLQuery">The Query to execute</param>
/// <param name="PID">The Program ID that matches the configured connection string name</param>
/// <returns></returns>
public static void ExecuteNonQuery(string SQLQuery, string PID, DalapiTransaction Transaction)
{
if (Transaction == null)
{
ExecuteNonQuery(SQLQuery, PID);
}
using (SqlCommand cmd = new SqlCommand(SQLQuery, Transaction.Connection))
{
cmd.Transaction = Transaction.Transaction;
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Truncates a table within a transaction
/// </summary>
/// <param name="PID"></param>
/// <param name="TableName"></param>
public static void TruncateTable(string PID, string TableName, DalapiTransaction Transaction)
{
if (Transaction == null)
{
TruncateTable(PID, TableName);
}
string sql = string.Format("TRUNCATE TABLE [{0}].[{1}]", PID, TableName);
using (SqlCommand cmd = new SqlCommand(sql, Transaction.Connection))
{
cmd.Transaction = Transaction.Transaction;
cmd.ExecuteNonQuery();
}
}
/// <summary>
/// Uses a transaction to execute a SQL Query and returns the result
/// </summary>
/// <param name="SQLQuery">The Query to execute</param>
/// <param name="PID">The Program ID that matches the configured connection string name</param>
/// <returns></returns>
public static int ExecuteScalar(string SQLQuery, string PID, DalapiTransaction Transaction)
{
if (Transaction == null)
{
return(ExecuteScalar(SQLQuery, PID));
}
using (SqlCommand cmd = new SqlCommand(SQLQuery, Transaction.Connection))
{
cmd.Transaction = Transaction.Transaction;
cmd.CommandType = CommandType.Text;
int id = Convert.ToInt32(cmd.ExecuteScalar());
return(id);
}
}
/// <summary>
/// Uses a transaction to call a stored procedure with an array of parameters and returns a single value - useful
/// when the stored procedure returns a newly created identity
/// </summary>
/// <param name="StoredProcName"></param>
/// <param name="Parameters"></param>
/// <param name="PID"></param>
/// <returns></returns>
public static int ExecuteScalar(string StoredProcName, SqlParameter[] Parameters, string PID, DalapiTransaction Transaction)
{
if (Transaction == null)
{
return(ExecuteScalar(StoredProcName, Parameters, PID));
}
using (SqlCommand cmd = new SqlCommand(StoredProcName, Transaction.Connection))
{
cmd.Transaction = Transaction.Transaction;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parameters);
int id = Convert.ToInt32(cmd.ExecuteScalar());
return(id);
}
}
/// <summary>
/// Calls a stored procedure with an array of parameters
/// </summary>
/// <param name="StoredProcName"></param>
/// <param name="Parameters"></param>
public static void ExecuteNonQuery(string StoredProcName, SqlParameter[] Parameters, string PID, DalapiTransaction Transaction)
{
if (Transaction == null)
{
ExecuteNonQuery(StoredProcName, Parameters, PID);
}
using (SqlCommand cmd = new SqlCommand(StoredProcName, Transaction.Connection))
{
cmd.Transaction = Transaction.Transaction;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(Parameters);
cmd.ExecuteNonQuery();
}
}