public string Field2String(NpgsqlDataReader dr, int i)
{
try{
switch (dr.GetFieldType(i).ToString())
{
default:
return((string)dr.GetValue(i));
}
}
catch {
return(dr.GetFieldType(i).ToString());
}
}
/// <summary>
/// Fill
/// </summary>
/// <param name="dt"></param>
public void Fill(DataTable dt)
{
if (dt == null)
{
dt = new DataTable();
}
var columns = dt.Columns;
var rows = dt.Rows;
using (NpgsqlDataReader dr = command.ExecuteReader())
{
for (int i = 0; i < dr.FieldCount; i++)
{
string name = dr.GetName(i).Trim();
if (!columns.ContainsKey(name))
{
columns.Add(new DataColumn(name, dr.GetFieldType(i)));
}
}
while (dr.Read())
{
DataRow daRow = new DataRow();
for (int i = 0; i < columns.Count; i++)
{
if (!daRow.ContainsKey(columns[i].ColumnName))
{
daRow.Add(columns[i].ColumnName, dr.GetValue(i));
}
}
dt.Rows.Add(daRow);
}
}
}
private async Task IterateReader(NpgsqlDataReader reader, string schema, string tableName, CancellationToken cancellationToken = default(CancellationToken))
{
var selector = new List <string>();
var sb = new StringBuilder();
while (await reader.ReadAsync(cancellationToken).ConfigureAwait(false))
{
sb.Append("(");
for (int i = 0; i < reader.FieldCount; i++)
{
var val = FormatValueFromDataType(reader.GetFieldType(i), reader[i]);
sb.Append($"{val},");
if (!selector.Count().Equals(reader.FieldCount))
{
selector.Add(reader.GetName(i));
}
}
sb.Length--;
sb.Append("),");
}
sb.Length--;
var str = $"INSERT INTO {schema}.{tableName} ({string.Join(",", selector)}) VALUES";
File.WriteAllText($"{FileDirectory}{schema}-{tableName}.sql", $"{str} {sb.ToString()};");
}
private unRetourRequete copieDonnees(ref NpgsqlDataReader source)
{
unRetourRequete retour = new unRetourRequete();
try
{
retour.Tables.Add("Resultat1");
for (int i = 0; i <= source.FieldCount - 1; i++)
{
retour.Tables[0].Columns.Add(source.GetName(i), source.GetFieldType(i));
}
while (source.Read())
{
retour.Tables[0].Rows.Add();
for (int numColonne = 0; numColonne <= source.FieldCount - 1; numColonne++)
{
retour.Tables[0].Rows[retour.Tables[0].Rows.Count - 1][numColonne] = source[numColonne];
}
}
return(retour);
}
catch (Exception ex)
{
_lastError = ex.Message;
}
finally
{
try
{
source.Close();
}
catch {}
}
return(null);
}
/// <summary>
/// Fill
/// </summary>
/// <param name="ds"></param>
public void Fill(DataSet ds)
{
if (ds == null)
{
ds = new DataSet();
}
using (NpgsqlDataReader dr = command.ExecuteReader())
{
do
{
var dt = new DataTable();
var columns = dt.Columns;
var rows = dt.Rows;
for (int i = 0; i < dr.FieldCount; i++)
{
string name = dr.GetName(i).Trim();
if (!columns.Contains(name))
{
columns.Add(new DataColumn(name, dr.GetFieldType(i)));
}
}
while (dr.Read())
{
DataRow daRow = dt.NewRow();
for (int i = 0; i < columns.Count; i++)
{
daRow[columns[i].ColumnName] = dr.GetValue(i);
}
dt.Rows.Add(daRow);
}
ds.Tables.Add(dt);
} while (dr.NextResult());
}
}
public void GetInt32ArrayFieldType()
{
NpgsqlCommand command = new NpgsqlCommand("select cast(null as integer[])", TheConnection);
using (NpgsqlDataReader dr = command.ExecuteReader())
{
Assert.AreEqual(typeof(int[]), dr.GetFieldType(0));
}
}
/// <summary>
/// スキーマ取得
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
private DataTable GetShcema(NpgsqlDataReader reader)
{
var result = new DataTable();
for (var i = 0; i < reader.FieldCount; i++)
{
var dataType = reader.GetFieldType(i);
result.Columns.Add(new DataColumn(reader.GetName(i).ToUpper(), dataType));
}
return(result);
}
public static void WriteToFile(NpgsqlDataReader reader, string filename)
{
using (var tempFile = File.Create(filename))
{
using (var writer = new StreamWriter(tempFile))
{
var columns = reader.GetColumnSchema();
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
writer.Write(columns[i].ColumnName);
}
else
{
writer.Write("\t{0}", columns[i].ColumnName);
}
}
writer.WriteLine();
while (reader.Read())
{
for (int i = 0; i < columns.Count; i++)
{
Type type = reader.GetFieldType(i);
var method = reader.GetType().GetMethod("GetFieldValue", new Type[] { typeof(int) });
var genericMethod = method.MakeGenericMethod(type);
var value = genericMethod.Invoke(reader, new object[] { i });
string valueHolder = "NULL";
if (value != null)
{
valueHolder = value.ToString();
}
if (i == 0)
{
writer.Write(valueHolder);
}
else
{
writer.Write("\t{0}", valueHolder);
}
}
writer.WriteLine();
}
writer.Flush();
writer.Close();
}
}
}
private void PrepareDataTable(NpgsqlDataReader reader, EbDataTable dt)
{
int _fieldCount = reader.FieldCount;
while (reader.Read())
{
EbDataRow dr = dt.NewDataRow();
for (int i = 0; i < _fieldCount; i++)
{
var _typ = reader.GetFieldType(i);
if (_typ == typeof(DateTime))
{
dr[i] = reader.IsDBNull(i) ? DateTime.Now : reader.GetDateTime(i);
continue;
}
else if (_typ == typeof(string))
{
dr[i] = reader.IsDBNull(i) ? string.Empty : reader.GetString(i);
continue;
}
else if (_typ == typeof(bool))
{
dr[i] = reader.IsDBNull(i) ? false : reader.GetBoolean(i);
continue;
}
else if (_typ == typeof(decimal))
{
dr[i] = reader.IsDBNull(i) ? 0 : reader.GetDecimal(i);
continue;
}
else if (_typ == typeof(int) || _typ == typeof(Int32))
{
dr[i] = reader.IsDBNull(i) ? 0 : reader.GetInt32(i);
continue;
}
else if (_typ == typeof(Int64))
{
dr[i] = reader.IsDBNull(i) ? 0 : reader.GetInt64(i);
continue;
}
else
{
dr[i] = reader.GetValue(i);
continue;
}
}
dt.Rows.Add(dr);
}
}
private static string[] GetColumnValuesAsList(NpgsqlDataReader reader, int columnId)
{
string[] res;
if (reader.GetFieldType(columnId).Name == "String")
{
var attribute = reader.GetString(columnId);
res = new string[1] {
attribute
};
}
else
{
res = reader.GetFieldValue <string[]>(columnId);
}
return(res);
}
/// <summary>
/// 查询字段属性
/// </summary>
/// <param name="TableName">表名</param>
/// <param name="Field">字段名</param>
/// <param name="FieldType">字段类型</param>
/// <returns>true 字段存在，false 字段不存在</returns>
public bool CheckField(string TableName, string Field, out System.Type FieldType, out bool CanBeNull, out bool IsPrimaryKey)
{
FieldType = typeof(object);
CanBeNull = false;
IsPrimaryKey = false;
if (string.IsNullOrEmpty(TableName))
{
throw new NullReferenceException("TableName Is Null Or Empty");
}
else if (string.IsNullOrEmpty(Field))
{
throw new NullReferenceException("Field Is Null Or Empty");
}
try
{
NpgsqlConnection conn = (NpgsqlConnection)GetConnection();
bool res = false;
DataTable dt = conn.GetSchema("Columns", new string[] { null, null, TableName });
int m = dt.Columns.IndexOf("COLUMN_NAME");
int n = dt.Columns.IndexOf("ISNULLABLE");
int o = dt.Columns.IndexOf("COLUMN_KEY");
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
if (dr.ItemArray.GetValue(m).ToString().ToUpper() == Field.ToUpper())
{
CanBeNull = dr.ItemArray.GetValue(n).ToString().ToUpper().Contains("YES");
IsPrimaryKey = dr.ItemArray.GetValue(o).ToString().ToUpper().Contains("PRI");
res = true;
break;
}
}
if (res)
{
NpgsqlCommand cmd = new NpgsqlCommand("select " + Field + " from " + TableName + "", conn);
cmd.CommandTimeout = this.Timeout * 1000;
NpgsqlDataReader da = cmd.ExecuteReader();
FieldType = da.GetFieldType(0);
}
CloseConnection(conn);
return(res);
}
catch
{
return(false);
}
}
public static void Main(String[] args)
{
NpgsqlConnection conn = new
NpgsqlConnection("Server=192.168.0.3;Port=5432;User Id=rick;Password=password;Database=bpfinal;");
try {
conn.Open();
NpgsqlCommand cmd = new NpgsqlCommand("SELECT * FROM customer", conn);
NpgsqlDataReader datard = cmd.ExecuteReader();
datard.Read();
Console.Write("There are {0} columns\n", datard.FieldCount);
for (int i = 0; i < datard.FieldCount; i++)
{
Console.Write("Name: {0}, NpgsqlType: {1}", datard.GetName(i), datard.GetFieldType(i));
Console.WriteLine();
}
Console.Write("First row by named column: {0}, {1}", datard["fname"], datard["lname"]);
}
finally {
conn.Close();
}
}
private static FeatureType GetFeatureType(NpgsqlDataReader r, out DataTable table,
out Func <NpgsqlDataReader, int, IBasicGeometry> gc, out int gIndex, out ColumMapper mapper)
{
table = new DataTable();
mapper = new ColumMapper();
gIndex = -1;
gc = null;
var res = FeatureType.Unspecified;
for (var i = 0; i < r.FieldCount; i++)
{
var t = r.GetFieldType(i);
if (t == null)
{
throw new InvalidOperationException("Could not get column type");
}
if (t == typeof(byte[]))
{
gc = PostGisServerReaderUtility.ReadGeometry;
gIndex = i;
res = gc(r, gIndex).FeatureType;
}
else
{
table.Columns.Add(r.GetName(i), t);
mapper.AddMap(i, table.Columns.Count - 1);
}
}
if (gIndex == -1)
{
throw new InvalidOperationException("No geometry column found");
}
return(res);
}
private void cmdReader_Click(object sender, System.EventArgs e)
{
log("Executing Reader...");
log("Query: " + txtReader.Text);
// Check the connection state
if (cnDB == null)
{
log("Error: The connection has not been opened.");
log("Finished executing Reader!\r\n");
return;
}
else
{
if (cnDB.State != ConnectionState.Open)
{
log("Error: The connection has not been opened.");
log("Finished executing Reader!\r\n");
return;
}
}
// Attempt to execute the query
NpgsqlDataReader objRdr = null;
try
{
NpgsqlCommand cmdNQ = new NpgsqlCommand(txtReader.Text, cnDB);
objRdr = cmdNQ.ExecuteReader();
}
catch (Exception ex)
{
log("Error: " + ex.Message + "\r\n" + "StackTrace: \r\n" + ex.StackTrace);
log("Finished executing Reader!\r\n");
return;
}
// Output some basic info
log("Columns: " + objRdr.FieldCount + "\r\n");
// Iterate through the rows, adding them to the output
int y = 0;
try
{
while (objRdr.Read())
{
y++;
log("Record: " + y);
for (int x = 0; x < objRdr.FieldCount; x++)
{
log(objRdr.GetName(x) + " (" + objRdr.GetFieldType(x) + ") = \"" + objRdr.GetValue(x) + "\"");
}
log("");
}
}
catch (Exception ex)
{
log("Error: " + ex.Message + "\r\n" + "StackTrace: \r\n" + ex.StackTrace);
log("Finished executing Reader!\r\n");
return;
}
finally
{
objRdr.Close();
}
log("Finished executing Reader!\r\n");
}
/// <summary>
/// Gets the System.Type of a column, given a zero based ordinal.
/// </summary>
/// <param name="i"></param>
/// <returns></returns>
public Type GetFieldType(int i)
{
return(_rdr.GetFieldType(i));
}
public override Type GetFieldType(int ordinal)
{
return(source.GetFieldType(ordinal));
}
/// <summary> /// Gets the System.Type of a column, given a zero based ordinal. /// </summary> public Type GetFieldType(int i) => _rdr.GetFieldType(i);
public async static Task StreamToGCS()
{
//Task.Run(async () => { await test(); }).GetAwaiter().GetResult();
string connectionString = "Host=localhost;Port=5701;Database=hiring;Username=tri;Password=1hEWZ4GeN24c";
Connection = new NpgsqlConnection(connectionString);
/**
* Needs Cleanup
* */
NpgsqlCommand cmd = new NpgsqlCommand();
cmd.CommandText = "SELECT * FROM public.language;";
cmd.Connection = Connection;
cmd.Connection.Open();
MemoryStream ms = new MemoryStream();
/**
* Needs Cleanup
* */
NpgsqlDataReader reader = cmd.ExecuteReader();
new Thread(() => {
var columns = reader.GetColumnSchema();
while (reader.Read())
{
for (int i = 0; i < columns.Count; i++)
{
Type type = reader.GetFieldType(i);
var method = reader.GetType().GetMethod("GetFieldValue", new Type[] { typeof(int) });
var genericMethod = method.MakeGenericMethod(type);
var value = genericMethod.Invoke(reader, new object[] { i });
string valueHolder = "NULL";
if (value != null)
{
valueHolder = value.ToString();
}
if (i == 0)
{
Console.Write(valueHolder);
WriteStringToStream(ms, valueHolder);
}
else
{
Console.Write("\t{0}", valueHolder);
WriteStringToStream(ms, string.Format("\t{0}", valueHolder));
}
}
Console.WriteLine();
WriteStringToStream(ms, "\n");
//Thread.Sleep(200);
}
}).Start();
var credentialsPath = "auth\\gd-hiring.json";
var credentialsJson = File.ReadAllText(credentialsPath);
var googleCredential = GoogleCredential.FromJson(credentialsJson);
var storageClient = StorageClient.Create(googleCredential);
storageClient.Service.HttpClient.Timeout = new TimeSpan(1, 0, 0);
var bucketName = "gd-hiring-tri";
await storageClient.UploadObjectAsync(
bucketName,
"public.language_2018-05-11.tsv",
"text/html",
ms
);
Thread.Sleep(5000);
using (var outputFile = File.OpenWrite("temp-output.tsv"))
{
storageClient.DownloadObject(bucketName, "public.language_2018-05-11.tsv", outputFile);
}
Console.WriteLine("Done??");
}
public async static Task StreamSqlToGCS(NpgsqlDataReader reader, StorageClient storageClient, string filename, string bucketName)
{
using (MemoryStream ms = new MemoryStream())
{
new Thread(() =>
{
Console.WriteLine("Start reading DB.");
var columns = reader.GetColumnSchema();
for (int i = 0; i < columns.Count; i++)
{
if (i == 0)
{
//Console.Write(columns[i].ColumnName);
WriteStringToStream(ms, columns[i].ColumnName);
}
else
{
//Console.Write("\t{0}", columns[i].ColumnName);
WriteStringToStream(ms, string.Format("\t{0}", columns[i].ColumnName));
}
}
//Console.WriteLine();
WriteStringToStream(ms, "\n");
while (reader.Read())
{
for (int i = 0; i < columns.Count; i++)
{
Type type = reader.GetFieldType(i);
var method = reader.GetType().GetMethod("GetFieldValue", new Type[] { typeof(int) });
var genericMethod = method.MakeGenericMethod(type);
var value = genericMethod.Invoke(reader, new object[] { i });
string valueHolder = "NULL";
if (value != null)
{
valueHolder = value.ToString();
}
if (i == 0)
{
//Console.Write(valueHolder);
WriteStringToStream(ms, valueHolder);
}
else
{
//Console.Write("\t{0}", valueHolder);
WriteStringToStream(ms, string.Format("\t{0}", valueHolder));
}
}
//Console.WriteLine();
WriteStringToStream(ms, "\n");
//Thread.Sleep(200);
}
Console.WriteLine("Finished reading DB.");
}).Start();
Thread.Sleep(5000);
await storageClient.UploadObjectAsync(
bucketName,
filename,
"text/html",
ms
);
//Thread.Sleep(5000);
using (var outputFile = File.OpenWrite("out-" + filename))
{
await storageClient.DownloadObjectAsync(bucketName, filename, outputFile);
}
}
Console.WriteLine("File uploaded.");
}
