//For UPDATE, INSERT, and DELETE statements
public bool modify(string cmd)
{
try
{
if (pgSqlConnection != null && IsConnected)
{
//insert
PgSqlCommand command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
pgSqlConnection.BeginTransaction();
//async
IAsyncResult cres = command.BeginExecuteNonQuery(null, null);
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...");
*/
int RowsAffected = command.EndExecuteNonQuery(cres);
//Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
/*
* //sync
* int aff = cmd.ExecuteNonQuery();
* Console.WriteLine(aff + " rows were affected.");
*
*/
pgSqlConnection.Commit();
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
log.Error("Modify exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
Console.ResetColor();
pgSqlConnection.Rollback();
return(false);
}
}
static void Insert(PgSqlConnection connection)
{
//insert
PgSqlCommand command = connection.CreateCommand();
command.CommandText = "INSERT INTO public._gps_log (_id,_uid,_status,_time,_validity,_lat,_lon,_speed,_course,_distance,_judgement,_or_lon,_or_lat,_satellites,_temperature,_voltage) VALUES (1681185,'_uid',2,'2012-10-01 18:32:50.553+08','a',25.062923,121.522705,0,0,0,0,0,0,3,35,100)";
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
//async
IAsyncResult cres = command.BeginExecuteNonQuery(null, null);
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
int RowsAffected = command.EndExecuteNonQuery(cres);
Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
/*
* //sync
* int aff = cmd.ExecuteNonQuery();
* Console.WriteLine(aff + " rows were affected.");
*/
}
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());
}
//For UPDATE, INSERT, and DELETE statements
public bool modify(string cmd)
{
try
{
if (pgSqlConnection != null && IsConnected)
{
//insert
PgSqlCommand command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
//async
IAsyncResult cres = command.BeginExecuteNonQuery(null, null);
if (cres.IsCompleted)
{
Console.WriteLine("Completed.");
}
else
{
Console.WriteLine("Have to wait for operation to complete...");
}
int RowsAffected = command.EndExecuteNonQuery(cres);
Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
/*
* //sync
* int aff = cmd.ExecuteNonQuery();
* Console.WriteLine(aff + " rows were affected.");
*
*/
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.WriteLine("Exception occurs: {0}", ex.Error);
return(false);
}
}
static void ModifyDept(PgSqlConnection connection)
{
PgSqlCommand command = connection.CreateCommand();
command.CommandText = "UPDATE public.test SET test='test' WHERE id > 1";
// return value of ExecuteNonQuery (i) is the number of rows affected by the command
int i = command.ExecuteNonQuery();
Console.WriteLine(Environment.NewLine + "Rows in DEPT updated: {0}", i + Environment.NewLine);
}
public bool GetInt32(string query, string field, out int value)
{
bool found = false;
value = 0;
using (PgSqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = query;
using (PgSqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read() == true)
{
value = dr.GetInt32(field);
found = true;
}
dr.Close();
}
}
return(found);
}
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);
* }
* }
* */
}
public void modify(string cmd)
{
PgSqlCommand command = null;
PgSqlTransaction myTrans = null;
using (PgSqlConnection pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
try
{
{
//insert
command = pgSqlConnection.CreateCommand();
command.UnpreparedExecute = true;
command.CommandText = cmd;
//command.CommandTimeout = 30;
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
//pgSqlConnection.BeginTransaction();
//async
int RowsAffected;
lock (accessLock)
{
pgSqlConnection.Open();
myTrans = pgSqlConnection.BeginTransaction(IsolationLevel.ReadCommitted);
command.Transaction = myTrans;
//IAsyncResult cres = command.BeginExecuteNonQuery();
//RowsAffected = command.EndExecuteNonQuery(cres);
//lock (accessLock)
RowsAffected = command.ExecuteNonQuery();
myTrans.Commit();
pgSqlConnection.Close();
}
//IAsyncResult cres=command.BeginExecuteNonQuery(null,null);
//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...");
*/
//int RowsAffected = command.EndExecuteNonQuery(cres);
//Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
//sync
//int aff = command.ExecuteNonQuery();
//Console.WriteLine(RowsAffected + " rows were affected.");
//command.Dispose();
command = null;
//pgSqlConnection.Commit();
/*
* ThreadPool.QueueUserWorkItem(callback =>
* {
*
* Console.ForegroundColor = ConsoleColor.Cyan;
* Console.WriteLine(RowsAffected + " rows were affected.");
* Console.WriteLine(
* "S++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
* Console.WriteLine("sql Write:\r\n" + cmd);
* Console.WriteLine(
* "E++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
* Console.ResetColor();
* log.Info("sql Write:\r\n" + cmd);
* });
*/
// Format and display the TimeSpan value.
}
}
catch (PgSqlException ex)
{
if (myTrans != null)
{
myTrans.Rollback();
}
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
SiAuto.Main.LogError(cmd);
Console.ResetColor();
//pgSqlConnection.Rollback();
//command.Dispose();
command = null;
}
finally
{
pgSqlConnection.Close();
}
}
/*
* public void LoadDatatable(DataTable dt)
* {
* using (PgSqlConnection pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
* {
* using (PgSqlLoader loader = new PgSqlLoader())
* {
* try
* {
* loader.Connection = pgSqlConnection;
* loader.TableName = "custom.WhatsUpDeviceStatus";
* pgSqlConnection.Open();
* loader.Open();
* //loader.CreateColumns();
* loader.LoadTable(dt);
* }
* catch (Exception e)
* {
*
* Console.WriteLine("error:" + e.ToString());
* SiAuto.Main.LogException(e);
* }
* finally
* {
* loader.Close();
* pgSqlConnection.Close();
* }
* }
* }
* }
*/
/*
* public void SqlScriptCmd(string script)
* {
* using (PgSqlConnection pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
* {
* try
* {
* PgSqlScript pgscScript = new PgSqlScript(script, pgSqlConnection);
* pgscScript.Progress += pgscScript_Progress;
* pgscScript.Error += pgscScript_Error;
* pgSqlConnection.Open();
* pgscScript.Execute();
* }
* catch (Exception e)
* {
* Console.WriteLine("error:" + e.ToString());
* SiAuto.Main.LogException(e);
* }
* finally
* {
* pgSqlConnection.Close();
* }
* }
* }
*/
/*
* void pgscScript_Error(object sender, Devart.Common.ScriptErrorEventArgs e)
* {
* e.Ignore = true;
* Console.WriteLine(e.Text);
* Console.WriteLine(" Failed.");
* SiAuto.Main.LogError(e.Text);
* }
*
* void pgscScript_Progress(object sender, Devart.Common.ScriptProgressEventArgs e)
* {
* Console.WriteLine(e.Text);
* SiAuto.Main.LogText("SqlScript",e.Text);
* Console.WriteLine(" Successfully executed.");
* }
*/
//For SELECT statements
public DataTable get_DataTable(string cmd)
{
PgSqlCommand command = null;
using (DataTable datatable = new DataTable())
using (var pgSqlConnection = new PgSqlConnection(pgCSB.ConnectionString))
{
try
{
//if (pgSqlConnection != null && IsConnected)
//{
//pgSqlConnection.Open();
//DataTable datatable = new DataTable();
command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//command.CommandTimeout = 30;
//Console.WriteLine("Starting asynchronous retrieval of data...");
PgSqlDataReader myReader;
//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);
//PgSqlDataReader myReader = command.ExecuteReader();
//try
//{
lock (accessLock)
{
Stopwatch stopWatch = new Stopwatch();
stopWatch.Start();
//IAsyncResult cres = command.BeginExecuteReader();
//myReader = command.EndExecuteReader(cres);
//lock (accessLock)
pgSqlConnection.Open();
myReader = command.ExecuteReader();
//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 query1 take time:" + elapsedTime, cmd);
// printing the column names
stopWatch.Reset();
stopWatch.Start();
for (int i = 0; i < myReader.FieldCount; i++)
{
//Console.Write(myReader.GetName(i).ToString() + "\t");
datatable.Columns.Add(myReader.GetName(i).ToString(), typeof(string));
}
//stopWatch.Stop();
ts = stopWatch.Elapsed;
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 query2 take time:" + elapsedTime, cmd);
//Console.Write(Environment.NewLine);
stopWatch.Reset();
stopWatch.Start();
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");
}
myReader.Close();
pgSqlConnection.Close();
stopWatch.Stop();
ts = stopWatch.Elapsed;
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 query3 take time:" + elapsedTime, cmd);
//myReader.Dispose();
}
//}
//finally
//{
//}
/*
* 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.
* }
* }
*/
Stopwatch stopWatch2 = new Stopwatch();
stopWatch2.Start();
//if (command != null)
//command.Dispose();
command = null;
using (DataTable returnTable = datatable.Copy())
{
stopWatch2.Stop();
SiAuto.Main.AddCheckpoint(Level.Debug, "sql query4 take time(ms):" + stopWatch2.ElapsedMilliseconds, cmd);
return(returnTable);
}
//DataTable returnTable = datatable.Copy();
//}
//else
//{
//return null;
//}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("GetDataTable exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
Console.ResetColor();
SiAuto.Main.LogError(cmd);
//if (command != null)
//command.Dispose();
command = null;
return(null);
}
}
}
//For UPDATE, INSERT, and DELETE statements
public bool modify(string cmd)
{
Stopwatch stopWatch = new Stopwatch();
PgSqlCommand command = null;
stopWatch.Start();
try
{
if (pgSqlConnection != null && IsConnected)
{
//insert
command = pgSqlConnection.CreateCommand();
command.CommandText = cmd;
//cmd.CommandText = "INSERT INTO public.test (id) VALUES (1)";
pgSqlConnection.BeginTransaction();
//async
IAsyncResult cres = command.BeginExecuteNonQuery(null, null);
//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...");
*/
int RowsAffected = command.EndExecuteNonQuery(cres);
//Console.WriteLine("Done. Rows affected: " + RowsAffected.ToString());
/*
* //sync
* int aff = cmd.ExecuteNonQuery();
* Console.WriteLine(aff + " rows were affected.");
*
*/
pgSqlConnection.Commit();
ThreadPool.QueueUserWorkItem(callback =>
{
Console.ForegroundColor = ConsoleColor.Cyan;
Console.WriteLine(
"S++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
Console.WriteLine("sql Write:\r\n" + cmd);
Console.WriteLine(
"E++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++");
Console.ResetColor();
});
if (command != null)
{
command.Dispose();
}
command = null;
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 modify take time:" + elapsedTime, cmd);
return(true);
}
else
{
return(false);
}
}
catch (PgSqlException ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine("Modify exception occurs: {0}" + Environment.NewLine + "{1}", ex.Error, cmd);
log.Error("Modify exception occurs: " + Environment.NewLine + ex.Error + Environment.NewLine + cmd);
Console.ResetColor();
pgSqlConnection.Rollback();
if (command != null)
{
command.Dispose();
}
command = null;
return(false);
}
}
