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