public static bool Execute(this string query, string csName, string connectionString, out DataResult result
, int timeout = 10, int error1205LevelHanle = 10)
{
if (csName == null)
{
return(query.Execute(out result, connectionString, timeout, error1205LevelHanle));
}
else
{
return(query.Execute(csName, out result, timeout, error1205LevelHanle));
}
}
public static bool Execute_Bulk(this string table_name, string csName, string connectionString, out DataResult result, List <Tuple <Type, string> > columns, List <Dictionary <string, object> > data
, int timeout = 3600, int error1205LevelHanle = 10)
{
if (csName == null)
{
return(table_name.Execute_Bulk(out result, connectionString, columns, data, timeout, error1205LevelHanle));
}
else
{
return(table_name.Execute_Bulk(csName, out result, columns, data, timeout, error1205LevelHanle));
}
}
public static bool Execute_Bulk(this string table_name, out DataResult result, string connectionString, List <Tuple <Type, string> > columns, List <Dictionary <string, object> > data
, int timeout = 3600, int error1205LevelHanle = 10)
{
bool res = false;
result = new DataResult();
DataTable dt = new DataTable(table_name);
{
for (int i = 0; i < columns.Count; i++)
{
DataColumn dc = new DataColumn();
dc.ColumnName = columns[i].Item2;
dc.DataType = columns[i].Item1;
dt.Columns.Add(dc);
}
for (int j = 0; j < data.Count; j++)
{
DataRow row = dt.NewRow();
for (int i = 0; i < columns.Count; i++)
{
row[columns[i].Item2] = data[j].GetElement(columns[i].Item2) ?? DBNull.Value;
}
dt.Rows.Add(row);
}
}
bool error1205 = false;
do
{
error1205 = false;
try
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
{
bulkCopy.DestinationTableName =
table_name;
bulkCopy.WriteToServer(dt);
}
}
return(true);
}
catch (SqlException se)
{
if (se.Number == 1205)
{
error1205 = true;
error1205LevelHanle--;
}
else
{
result.e = se;
result.se = se;
OnErrorExecute(se);
}
}
catch (Exception ex)
{
result.e = ex;
result.se = new Exception("fail_to_call");
OnErrorExecute(ex);
}
} while (error1205 && error1205LevelHanle >= 0);
return(res);
}
public static bool Execute_Step(this string query, out DataResult result, string connectionString, int batch_limit
, Func <List <Tuple <Type, string> >, List <Dictionary <string, object> >, Tuple <bool, Exception> > func
, int timeout = 3600, int error1205LevelHanle = 10)
{
bool res = false;
result = new DataResult();
bool error1205 = false;
do
{
error1205 = false;
try
{
using (var conn = new SqlConnection(connectionString))
{
conn.Open();
using (var cmd = new SqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = query;
cmd.CommandTimeout = timeout;
using (var reader = cmd.ExecuteReader())
{
while (reader.FieldCount > 0)
{
List <Tuple <Type, string> > ft = new List <Tuple <Type, string> >();
Dictionary <string, Type> b_name = new Dictionary <string, Type>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type t = reader.GetFieldType(i);
string field_name = reader.GetName(i);
if (b_name.ContainsKey(field_name))
{
field_name = field_name + i.ToString();
}
ft.Add(new Tuple <Type, string>(t == typeof(DBNull) ? null : t, field_name));
b_name.Add(field_name, t);
}
List <Dictionary <string, object> > lr = new List <Dictionary <string, object> >();
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < ft.Count; i++)
{
object v = ft[i].Item1 == null ? null : reader.GetValue(i);
if (v is DBNull)
{
v = null;
}
row.Add(ft[i].Item2, v);
}
lr.Add(row);
if (lr.Count >= batch_limit)
{
var f_wr = func(ft, lr);
if (!f_wr.Item1)
{
Exception ex = new Exception("function faild in Execute_Step\r\n" + f_wr.Item2.Message, f_wr.Item2);
result.e = ex;
OnErrorExecute(ex);
return(false);
}
lr = new List <Dictionary <string, object> >();
}
}
if (lr.Count > 0)
{
var f_wr = func(ft, lr);
if (!f_wr.Item1)
{
Exception ex = new Exception("function faild in Execute_Step\r\n" + f_wr.Item2.Message, f_wr.Item2);
result.e = ex;
OnErrorExecute(ex);
return(false);
}
}
result.res.Add(lr);
reader.NextResult();
}
}
}
}
return(true);
}
catch (SqlException se)
{
if (se.Number == 1205)
{
error1205 = true;
error1205LevelHanle--;
}
else
{
result.e = se;
result.se = se;
OnErrorExecute(se);
}
}
catch (Exception ex)
{
result.e = ex;
result.se = new Exception("fail_to_call");
OnErrorExecute(ex);
}
} while (error1205 && error1205LevelHanle >= 0);
return(res);
}
public static bool Execute_Step(this string query, string csName, string connectionString, out DataResult result, int batch_limit
, Func <List <Tuple <Type, string> >, List <Dictionary <string, object> >, Tuple <bool, Exception> > func
, int timeout = 3600, int error1205LevelHanle = 10)
{
if (csName == null)
{
return(query.Execute_Step(out result, connectionString, batch_limit, func, timeout, error1205LevelHanle));
}
else
{
return(query.Execute_Step(csName, out result, batch_limit, func, timeout, error1205LevelHanle));
}
}
public static bool ExecutePg(this string query, out DataResult result, string connectionString, int timeout = 30)
{
result = new DataResult();
try
{
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SET statement_timeout = " + timeout.ToString() + "000;";
cmd.ExecuteNonQuery();
}
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = query;
cmd.CommandTimeout = timeout;
using (var reader = cmd.ExecuteReader())
{
while (reader.FieldCount > 0)
{
List <Tuple <Type, string> > ft = new List <Tuple <Type, string> >();
Dictionary <string, bool> b_name = new Dictionary <string, bool>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type t = reader.GetFieldType(i);
string field_name = reader.GetName(i);
if (b_name.ContainsKey(field_name))
{
field_name = field_name + i.ToString();
}
ft.Add(new Tuple <Type, string>(t == typeof(DBNull) ? null : t, field_name));
b_name.Add(field_name, true);
}
result.res_types.Add(ft);
List <Dictionary <string, object> > lr = new List <Dictionary <string, object> >();
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < ft.Count; i++)
{
object v = ft[i].Item1 == null ? null : reader.GetValue(i);
if (v is DBNull)
{
v = null;
}
row.Add(ft[i].Item2, v);
}
lr.Add(row);
}
result.res.Add(lr);
reader.NextResult();
}
}
}
}
return(true);
}
catch (PostgresException se)
{
result.e = se;
result.se = se;
QueryHandler.OnErrorExecute(se);
}
catch (Exception ex)
{
result.e = ex;
QueryHandler.OnErrorExecute(ex);
}
return(false);
}
public static bool ExecutePg(this string query, string csName, string connectionString, out DataResult result
, int timeout = 10)
{
if (csName == null)
{
return(query.ExecutePg(out result, connectionString, timeout));
}
else
{
return(query.ExecutePg(csName, out result, timeout));
}
}
public static bool ExecutePg_Step(this string query, out DataResult result, string connectionString, int batch_limit
, Func <List <Tuple <Type, string> >, List <Dictionary <string, object> >, Tuple <bool, Exception> > func, int timeout = 3600)
{
result = new DataResult();
try
{
using (var conn = new Npgsql.NpgsqlConnection(connectionString))
{
conn.Open();
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = "SET statement_timeout = " + timeout.ToString() + "000;";
cmd.ExecuteNonQuery();
}
using (var cmd = new Npgsql.NpgsqlCommand())
{
cmd.Connection = conn;
cmd.CommandText = query;
using (var reader = cmd.ExecuteReader())
{
while (reader.FieldCount > 0)
{
List <Tuple <Type, string> > ft = new List <Tuple <Type, string> >();
Dictionary <string, bool> b_name = new Dictionary <string, bool>();
for (int i = 0; i < reader.FieldCount; i++)
{
Type t = reader.GetFieldType(i);
string field_name = reader.GetName(i);
if (b_name.ContainsKey(field_name))
{
field_name = field_name + i.ToString();
}
ft.Add(new Tuple <Type, string>(t == typeof(DBNull) ? null : t, field_name));
b_name.Add(field_name, true);
}
List <Dictionary <string, object> > lr = new List <Dictionary <string, object> >();
while (reader.Read())
{
Dictionary <string, object> row = new Dictionary <string, object>();
for (int i = 0; i < ft.Count; i++)
{
object v = ft[i].Item1 == null ? null : reader.GetValue(i);
if (v is DBNull)
{
v = null;
}
row.Add(ft[i].Item2, v);
}
lr.Add(row);
if (lr.Count > batch_limit)
{
var f_wr = func(ft, lr);
if (!f_wr.Item1)
{
Exception ex = new Exception("function faild in ExecutePg_Step\r\n" + f_wr.Item2.Message, f_wr.Item2);
result.e = ex;
QueryHandler.OnErrorExecute(ex);
return(false);
}
lr = new List <Dictionary <string, object> >();
}
}
if (lr.Count > 0)
{
var f_wr = func(ft, lr);
if (!f_wr.Item1)
{
Exception ex = new Exception("function faild in ExecutePg_Step\r\n" + f_wr.Item2.Message, f_wr.Item2);
result.e = ex;
QueryHandler.OnErrorExecute(ex);
return(false);
}
}
reader.NextResult();
}
}
}
}
return(true);
}
catch (NpgsqlException se)
{
result.e = se;
result.se = se;
QueryHandler.OnErrorExecute(se);
}
catch (Exception ex)
{
result.e = ex;
result.se = new Exception("fail_to_call");
QueryHandler.OnErrorExecute(ex);
}
return(false);
}
