public int ExecuteQueryText(string queryText, object parameters = null)
{
TimeSpan span;
DateTime tm = DateTime.Now;
DateTime tm2 = DateTime.Now;
AppQueryLog ql = new AppQueryLog();
int res = 0;
using (con = new NpgsqlConnection(conStr))
{
con.Open();
try
{
res = con.Execute(queryText, parameters);
tm2 = DateTime.Now;
}
catch (Exception ex)
{
ql.ErrorMessage = (ex.Message + ex.StackTrace).Substring(0, 149);
tm2 = DateTime.Now;
span = tm2 - tm;
ql.ExecutionTime = tm;
ql.Duration = Convert.ToInt32(span.TotalMilliseconds);
ql.QueryName = "Custom";
ql.QueryText = queryText;
CreateQueryLog(ql);
throw ex;
}
}
return(res);
}
public void DeleteObject <T>(T obj) where T : class
{
TimeSpan span;
DateTime tm = DateTime.Now;
DateTime tm2 = DateTime.Now;
AppQueryLog ql = new AppQueryLog();
try
{
using (con = new NpgsqlConnection(conStr))
{
con.Open();
con.Delete <T>(obj);
}
}
catch (Exception ex)
{
ql.ErrorMessage = (ex.Message + ex.StackTrace).Substring(0, 149);
tm2 = DateTime.Now;
span = tm2 - tm;
ql.ExecutionTime = tm;
ql.Duration = Convert.ToInt32(span.TotalMilliseconds);
ql.QueryName = typeof(T).FullName;
CreateQueryLog(ql);
throw ex;
}
}
public List <T> GetQueryResultDirect <T>(NpgsqlConnection con, string queryText, List <IDataParameter> cparams = null)
{
List <T> result = new List <T>();
TimeSpan span;
DateTime tm = DateTime.Now;
DateTime tm2 = DateTime.Now;
AppQueryLog ql = new AppQueryLog();
queryText = RemoveUnsetParameters(queryText, cparams);
DynamicParameters parameters = null;
if (cparams != null)
{
if (cparams.Count > 0)
{
parameters = new DynamicParameters();
foreach (var item in cparams)
{
parameters.Add(item.ParameterName, item.Value);
}
}
}
result = con.Query <T>(queryText, parameters).ToList <T>();
tm2 = DateTime.Now;
return(result);
}
private void CreateQueryLog(AppQueryLog log)
{
//if (this.nolog == false)
//{
// using (con = new NpgsqlConnection(conStr))
// {
// con.Open();
// con.InsertPg<AppQueryLog>(log);
// }
//}
}
public DataTable GetQueryResultDataTableDirect(string queryText, List <IDataParameter> parameters)
{
DataTable result = new DataTable();
if (string.IsNullOrEmpty(queryText))
{
throw new Exception("query text must not be empty !");
}
queryText = RemoveUnsetParameters(queryText, parameters);
TimeSpan span;
DateTime tm = DateTime.Now;
DateTime tm2 = DateTime.Now;
AppQueryLog ql = new AppQueryLog();
try
{
using (con = new NpgsqlConnection(conStr))
{
con.Open();
NpgsqlCommand cmd = new NpgsqlCommand();
if (parameters != null)
{
foreach (var item in parameters)
{
cmd.Parameters.AddWithValue(item.ParameterName, item.Value);
}
}
cmd.Connection = con;
cmd.CommandText = queryText;
cmd.CommandTimeout = 200;
NpgsqlDataAdapter adap = new NpgsqlDataAdapter(cmd);
adap.Fill(result);
tm2 = DateTime.Now;
}
}
catch (Exception ex)
{
ql.ErrorMessage = (ex.Message + ex.StackTrace).Substring(0, 149);
tm2 = DateTime.Now;
span = tm2 - tm;
ql.ExecutionTime = tm;
ql.Duration = Convert.ToInt32(span.TotalMilliseconds);
ql.QueryName = "Custom";
ql.QueryText = queryText;
CreateQueryLog(ql);
throw ex;
}
return(result);
}
public List <T> GetQueryResultDirect <T>(string queryText, List <IDataParameter> cparams = null)
{
List <T> result = new List <T>();
TimeSpan span;
DateTime tm = DateTime.Now;
DateTime tm2 = DateTime.Now;
AppQueryLog ql = new AppQueryLog();
queryText = RemoveUnsetParameters(queryText, cparams);
try
{
using (con = new NpgsqlConnection(conStr))
{
con.Open();
DynamicParameters parameters = null;
if (cparams != null)
{
if (cparams.Count > 0)
{
parameters = new DynamicParameters();
foreach (var item in cparams)
{
parameters.Add(item.ParameterName, item.Value);
}
}
}
result = con.Query <T>(queryText, parameters).ToList <T>();
tm2 = DateTime.Now;
}
}
catch (Exception ex)
{
ql.ErrorMessage = (ex.Message + ex.StackTrace).Substring(0, 149);
tm2 = DateTime.Now;
span = tm2 - tm;
ql.ExecutionTime = tm;
ql.Duration = Convert.ToInt32(span.TotalMilliseconds);
ql.QueryName = "Custom";
ql.QueryText = queryText;
CreateQueryLog(ql);
throw ex;
}
return(result);
}
public int ExecuteQueryText2(string queryText, List <CustomParameter> parameters = null)
{
TimeSpan span;
DateTime tm = DateTime.Now;
DateTime tm2 = DateTime.Now;
AppQueryLog ql = new AppQueryLog();
int res = 0;
using (con = new NpgsqlConnection(conStr))
{
con.Open();
try
{
DynamicParameters dynParam = null;
if (parameters != null)
{
dynParam = new DynamicParameters();
foreach (var item in parameters)
{
dynParam.Add(item.ParameterName, item.Value);
}
}
res = con.Execute(queryText, dynParam);
tm2 = DateTime.Now;
}
catch (Exception ex)
{
ql.ErrorMessage = (ex.Message + ex.StackTrace).Substring(0, 149);
tm2 = DateTime.Now;
span = tm2 - tm;
ql.ExecutionTime = tm;
ql.Duration = Convert.ToInt32(span.TotalMilliseconds);
ql.QueryName = "Custom";
ql.QueryText = queryText;
CreateQueryLog(ql);
throw ex;
}
}
return(res);
}