/// <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(); } } }
//********************************************************************************************** public override void Edit() { SQLSettings form = new SQLSettings(this); form.ShowDialog(); }
/// <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(); } } }