/// <summary>
/// Execute Non Query Operation
/// </summary>
/// <returns>Execution Code</returns>
public static int ExecuteNonQuery(string storedProcedure, SqlParameter[] arParms)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
try
{
try
{
connection = new SQLSettings().GetConnection();
}
catch (Exception ex)
{
throw ex;
}
int returnCode = SqlHelper.ExecuteNonQuery(connection, CommandType.StoredProcedure, storedProcedure, arParms);
return(returnCode);
}
catch (Exception ex)
{
string errMessage = "";
for (Exception tempException = ex; tempException != null; tempException = tempException.InnerException)
{
errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
}
throw new Exception(errMessage);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <method>
/// Get Data Table
/// </method>
public static DataTable GetDataTable(string storedProcedure, SqlParameter[] arParms)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
DataTable dt = new DataTable();
try
{
//CodeTrace.Log("Method Start:", MethodBase.GetCurrentMethod());
try
{
connection = new SQLSettings().GetConnection();
}
catch (Exception ex)
{
throw ex;
}
// DataSet that will hold the returned results
DataSet ds;
// Call ExecuteDataset static method of SqlHelper class that returns a Dataset
ds = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, storedProcedure, arParms);
//CodeTrace.Log("Method End:", MethodBase.GetCurrentMethod());
if (ds != null && ds.Tables.Count > 0)
{
dt = ds.Tables[0];
}
return(dt);
}
catch (Exception ex)
{
string errMessage = "";
for (Exception tempException = ex; tempException != null; tempException = tempException.InnerException)
{
errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
}
throw new Exception(errMessage);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
public static string ExecStringCommand(string cmd)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
DataTable dt = new DataTable();
string res;
try
{
try
{
connection = new SQLSettings().GetConnection();
}
catch (Exception ex)
{
throw ex;
}
// Call ExecuteDataset static method of SqlHelper class that returns a Dataset
//ds = SqlHelper.ExecuteDataset(connection, CommandType.StoredProcedure, storedProcedure);
res = SqlHelper.ExecuteScalar(connection, CommandType.Text, cmd).ToString();
//CodeTrace.Log("Method End:", MethodBase.GetCurrentMethod());
return(res);
}
catch (Exception ex)
{
string errMessage = "";
for (Exception tempException = ex; tempException != null; tempException = tempException.InnerException)
{
errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
}
throw new Exception(errMessage);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
/// <summary>
/// Get the scalar object
/// </summary>
/// <returns>Object</returns>
public static object GetScalar(string storedProcedure, SqlParameter[] arParms)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
try
{
//CodeTrace.Log("Method Start:", MethodBase.GetCurrentMethod());
try
{
connection = new SQLSettings().GetConnection();
}
catch (Exception ex)
{
//Error.ReportErrorBrief("ERR0001", "SQL Connection Failure", "Application Error");
throw ex;
}
object obj = SqlHelper.ExecuteScalar(connection, CommandType.StoredProcedure, storedProcedure, arParms);
//CodeTrace.Log("Method End:", MethodBase.GetCurrentMethod());
return(obj);
}
catch (Exception ex)
{
string errMessage = "";
for (Exception tempException = ex; tempException != null; tempException = tempException.InnerException)
{
errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
}
throw new Exception(errMessage);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}
/// <summary>
/// <summary>
/// Execute SQL Commands
/// </summary>
public static void ExecuteSQLCommands(DataSet ds, string tableName, string addProcedure, string updateProcedure, string deleteProcedure, string[] addSourceColumns, object[] addValues, string[] updateSourceColumns, object[] updateValues, string[] deleteSourceColumns, object[] deleteValues)
{
// SqlConnection that will be used to execute the sql commands
SqlConnection connection = null;
try
{
try
{
connection = new SQLSettings().GetConnection();
}
catch (Exception ex)
{
throw ex;
}
DataTable dt = ds.Tables[tableName];
// Add a new row to existing Data Table
DataRow addedRow = dt.Rows.Add(addValues);
// Create the command that will be used for insert operation
SqlCommand insertCommand = SqlHelper.CreateCommand(connection, addProcedure, addSourceColumns);
// Modify a row
foreach (object obj in updateValues)
{
//dt.Rows[0]["ProductName"] = "Modified product";
}
// Create the command that will be used for update operations
// The stored procedure also performs a SELECT to allow updating the DataSet with other changes ***
SqlCommand updateCommand = SqlHelper.CreateCommand(connection, updateProcedure, updateSourceColumns);
// Create the command that will be used for delete operations
SqlCommand deleteCommand = SqlHelper.CreateCommand(connection, deleteProcedure, deleteSourceColumns);
try
{
// Update the data source with the DataSet changes
SqlHelper.UpdateDataset(insertCommand, deleteCommand, updateCommand, ds, tableName);
}
catch (DBConcurrencyException)
{
string errMessage = "A concurrency error has ocurred while trying to update the data source; The row wasn´t updated: ";
throw new Exception(errMessage);
}
}
catch (Exception ex)
{
string errMessage = "";
for (Exception tempException = ex; tempException != null; tempException = tempException.InnerException)
{
errMessage += tempException.Message + Environment.NewLine + Environment.NewLine;
}
throw new Exception(errMessage);
}
finally
{
if (connection != null)
{
connection.Dispose();
}
}
}