public static Exception ReadBinary(string query, string conn_string, out byte[] result, int timeoutMilliseconds = 100) { try { using (var sourceConnection = new NpgsqlConnection(conn_string)) { sourceConnection.Open(); using (var memoryStream = new MemoryStream()) { using (var inStream = sourceConnection.BeginRawBinaryCopy($"COPY ({query}) TO STDOUT (FORMAT BINARY)")) { var buffer = new byte[BUFFER_SIZE_BYTES]; var tryNumber = 0; while (tryNumber <= MAX_READ_TRY_COUNT) { tryNumber++; int readLength; while ((readLength = inStream.Read(buffer, 0, buffer.Length)) > 0) { memoryStream.Write(buffer, 0, readLength); tryNumber = 0; } Thread.Sleep(timeoutMilliseconds); } } sourceConnection.Close(); result = memoryStream.ToArray(); return(null); } } } catch (Exception ex) { result = null; QueryHandler.OnErrorExecute(ex); return(ex); } }
public static Exception Copy(string sourceQuery, string destinationTableName, string sourceConnectionString, string destinationConnectionString, int timeoutMilliseconds = 100) { try { using (var sourceConnection = new NpgsqlConnection(sourceConnectionString)) using (var destinationConnection = new NpgsqlConnection(destinationConnectionString)) { sourceConnection.Open(); destinationConnection.Open(); using (var inStream = sourceConnection.BeginRawBinaryCopy($"COPY ({sourceQuery}) TO STDOUT (FORMAT BINARY)")) using (var outStream = destinationConnection.BeginRawBinaryCopy($"COPY {destinationTableName} FROM STDIN (FORMAT BINARY)")) { var buffer = new byte[BUFFER_SIZE_BYTES]; var tryNumber = 0; while (tryNumber <= MAX_READ_TRY_COUNT) { tryNumber++; int readLength; while ((readLength = inStream.Read(buffer, 0, buffer.Length)) > 0) { outStream.Write(buffer, 0, readLength); tryNumber = 0; } Thread.Sleep(timeoutMilliseconds); } } sourceConnection.Close(); destinationConnection.Close(); } return(null); } catch (Exception exception) { QueryHandler.OnErrorExecute(exception); return(exception); } }
public static Exception Copy(string destinationConnectionString, IList <Dictionary <string, object> > datas, IList <string> fieldNames, string destinationTableName) { try { if (datas == null || !datas.Any()) { return(null); } if (fieldNames == null || !fieldNames.Any()) { return(null); } using (var destinationConnection = new NpgsqlConnection(destinationConnectionString)) { destinationConnection.Open(); var destinationQuery = $"{destinationTableName}({string.Join(",", fieldNames)})"; using (var writer = destinationConnection.BeginTextImport($"COPY {destinationQuery} FROM STDIN")) { foreach (var item in datas) { var rowString = string.Join("\t", fieldNames.Select(name => $"{item[name].GenerateFormatText()}")); writer.Write($"{rowString}\n"); } writer.Write("\\.\n"); } destinationConnection.Close(); return(null); } } catch (Exception exception) { QueryHandler.OnErrorExecute(exception); return(exception); } }
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_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); }