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