static void Count(PgSqlConnection connection)
{
DataTable datatable = new DataTable();
PgSqlCommand command = connection.CreateCommand();
command.CommandText = @"SELECT COUNT
(_uid)
FROM public._gps_log";
Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
Console.Write(Environment.NewLine);
while (myReader.Read())
{
DataRow dr = datatable.NewRow();
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetString(i) + "\t");
dr[i] = myReader.GetString(i);
}
datatable.Rows.Add(dr);
Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
}
foreach (DataRow row in datatable.Rows) // Loop over the rows.
{
Console.WriteLine("--- Row ---"); // Print separator.
foreach (var item in row.ItemArray) // Loop over the items.
{
Console.Write("Item: "); // Print label.
Console.WriteLine(item); // Invokes ToString abstract method.
}
}
Console.WriteLine("############");
Console.WriteLine(datatable.Rows[0].ItemArray[0].ToString());
}
static void PrintDept(PgSqlConnection connection)
{
PgSqlCommand command = connection.CreateCommand();
command.CommandText = "select * from test";
//async
Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetName(i).ToString() + "\t");
}
Console.Write(Environment.NewLine);
while (myReader.Read())
{
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetString(i) + "\t");
}
Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
}
/*
* // Call the Close method when you are finished using the PgSqlDataReader
* // to use the associated PgSqlConnection for any other purpose.
* // Or put the reader in the using block to call Close implicitly.
* //sync
* Console.WriteLine("Starting synchronous retrieval of data...");
* using (PgSqlDataReader reader = command.ExecuteReader())
* {
* // printing the column names
* for (int i = 0; i < reader.FieldCount; i++)
* Console.Write(reader.GetName(i).ToString() + "\t");
* Console.Write(Environment.NewLine);
* // Always call Read before accesing data
* while (reader.Read())
* {
* // printing the table content
* for (int i = 0; i < reader.FieldCount; i++)
* Console.Write(reader.GetValue(i).ToString() + "\t");
* Console.Write(Environment.NewLine);
* }
* }
* */
}
//For SELECT statements
public DataTable get_DataTable(string cmd)
{
try
{
if (pgSqlConnection != null && IsConnected)
{
DataTable datatable = new DataTable();
PgSqlCommand command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
Console.Write(Environment.NewLine);
while (myReader.Read())
{
DataRow dr = datatable.NewRow();
for (int i = 0; i < myReader.FieldCount; i++)
{
Console.Write(myReader.GetString(i) + "\t");
dr[i] = myReader.GetString(i);
}
datatable.Rows.Add(dr);
Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
}
foreach (DataRow row in datatable.Rows) // Loop over the rows.
{
Console.WriteLine("--- Row ---"); // Print separator.
foreach (var item in row.ItemArray) // Loop over the items.
{
Console.Write("Item: "); // Print label.
Console.WriteLine(item); // Invokes ToString abstract method.
}
}
return(datatable);
}
else
{
return(null);
}
}
catch (PgSqlException ex)
{
Console.WriteLine("Exception occurs: {0}", ex.Error);
return(null);
}
}
//For SELECT statements
public DataTable get_DataTable(string cmd)
{
Stopwatch stopWatch = new Stopwatch();
PgSqlCommand command = null;
stopWatch.Start();
try
{
if (pgSqlConnection != null && IsConnected)
{
DataTable datatable = new DataTable();
command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//Console.WriteLine("Starting asynchronous retrieval of data...");
IAsyncResult cres = command.BeginExecuteReader();
//Console.Write("In progress...");
//while (!cres.IsCompleted)
{
//Console.Write(".");
//Perform here any operation you need
}
//if (cres.IsCompleted)
//Console.WriteLine("Completed.");
//else
//Console.WriteLine("Have to wait for operation to complete...");
PgSqlDataReader myReader = command.EndExecuteReader(cres);
try
{
// printing the column names
for (int i = 0; i < myReader.FieldCount; i++)
{
//Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
//Console.Write(Environment.NewLine);
while (myReader.Read())
{
DataRow dr = datatable.NewRow();
for (int i = 0; i < myReader.FieldCount; i++)
{
//Console.Write(myReader.GetString(i) + "\t");
dr[i] = myReader.GetString(i);
}
datatable.Rows.Add(dr);
//Console.Write(Environment.NewLine);
//Console.WriteLine(myReader.GetInt32(0) + "\t" + myReader.GetString(1) + "\t");
}
}
finally
{
myReader.Close();
stopWatch.Stop();
// Get the elapsed time as a TimeSpan value.
TimeSpan ts = stopWatch.Elapsed;
// Format and display the TimeSpan value.
string elapsedTime = String.Format("{0:00}:{1:00}:{2:00}.{3:00}",
ts.Hours, ts.Minutes, ts.Seconds,
ts.Milliseconds / 10);
SiAuto.Main.AddCheckpoint(Level.Debug, "sql query take time:" + elapsedTime, cmd);
}
/*
* foreach (DataRow row in datatable.Rows) // Loop over the rows.
* {
* Console.WriteLine("--- Row ---"); // Print separator.
* foreach (var item in row.ItemArray) // Loop over the items.
* {
* Console.Write("Item: "); // Print label.
* Console.WriteLine(item); // Invokes ToString abstract method.
* }
* }
*/
if (command != null)
{
command.Dispose();
}
command = null;
return(datatable);
}
else
{
return(null);
}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("GetDataTable exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
log.Error("GetDataTable exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
Console.ResetColor();
if (command != null)
{
command.Dispose();
}
command = null;
return(null);
}
}
