/// <summary> /// Delete from certain table, DML statement will be excuted here /// </summary> /// <param name="sql">This is sql query to delete data from certain table in database</param> /// <returns></returns> public DeleteDataResult <T> DeleteDataFromTable <T>(string SQL, params object[] pars) { DeleteDataResult <T> deleteDataResult = new DeleteDataResult <T>(); object result = null; try { if (cnn.State != ConnectionState.Open) { cnn.Open(); } NpgsqlCommand command = new NpgsqlCommand(SQL, cnn); var paramIndex = 0; foreach (var p in pars) { var paramName = string.Format(":p{0}", paramIndex); command.CommandText = command.CommandText.Replace("{" + paramIndex.ToString() + "}", paramName); var sqlParam = new NpgsqlParameter(); sqlParam.ParameterName = paramName; if (p != null) { sqlParam.Value = p; } else { sqlParam.Value = DBNull.Value; } command.Parameters.Add(sqlParam); paramIndex++; } result = command.ExecuteNonQuery(); //Delete successfully if (result != null) { deleteDataResult.Error = new ErrorAction() { ErrorType = DataActionErrorTypeEnum.None }; } if (cnn.State != ConnectionState.Closed) { cnn.Close(); } } catch (NpgsqlException ex) { Utils utils = new Utils(); deleteDataResult = utils.ProcessException <T>(ex, deleteDataResult); } catch (Exception ex) { throw ex; } return(deleteDataResult); }
public DeleteDataResult <T> DeleteData <T>(string tableName, string strWhere, List <object> inputParams) { DeleteDataResult <T> deleteDataResult = new DeleteDataResult <T>(); var tblName = Globals.Compiler.GetQName(tableName); for (int i = 0; i < inputParams.Count; i++) { strWhere = strWhere.Replace("{" + i + "}", ":p" + i.ToString()); } string sql = string.Format("DELETE FROM {0} WHERE {1}", tblName, strWhere); deleteDataResult = DeleteDataFromTable <T>(sql, inputParams.ToArray()); return(deleteDataResult); }
/// <summary> /// Process exception when excute Delete /// </summary> /// <param name="ex">Exception</param> /// <returns>deleteDataResult<T></returns> public DeleteDataResult <T> ProcessException <T>(NpgsqlException ex, DeleteDataResult <T> deleteDataResult) { //Foreign key exception if (((PostgresException)ex).SqlState == "23503") { string[] fields = new string[1]; string[] tables = new string[1]; string detail = ((PostgresException)ex).Detail; string leftStr = detail.Split('=')[0]; string tableName = Regex.Split(detail, @"""")[1].Replace(@"""", ""); string fieldName = leftStr.Split('(')[1].Split(')')[0]; fields[0] = fieldName; tables[0] = tableName; deleteDataResult.Error = new ErrorAction() { ErrorType = DataActionErrorTypeEnum.ForeignKey, Fields = fields, RefTables = tables }; } return(deleteDataResult); }