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) { 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); } }
//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); } }
private void btExecute_Click(object sender, System.EventArgs e) { const int len = 10; int recCount = 0; PgSqlCommand cursorCmd = new PgSqlCommand("fetch all in \"dept\"", pgSqlCommand.Connection); // need to start transaction, because cursor is accessible only in transaction PgSqlTransaction tr = pgSqlCommand.Connection.BeginTransaction(); try { // executes stored procedure that opens cursor pgSqlCommand.ExecuteScalar(); // fetch data from named cursor PgSqlDataReader dataReader = cursorCmd.ExecuteReader(); if (dataReader.FieldCount > 0) { for (int i = 0; i < dataReader.FieldCount; i++) { tbResult.AppendText(dataReader.GetName(i).PadRight(len).Substring(0, len) + " "); } tbResult.AppendText("\r\n"); for (int i = 0; i < dataReader.FieldCount; i++) { tbResult.AppendText(String.Empty.PadRight(len, '-').Substring(0, len) + " "); } tbResult.AppendText("\r\n"); while (dataReader.Read()) { for (int i = 0; i < dataReader.FieldCount; i++) { tbResult.AppendText(dataReader.GetValue(i).ToString().PadRight(len).Substring(0, len) + " "); } tbResult.AppendText("\r\n"); recCount++; } tbResult.AppendText("\r\n"); tbResult.AppendText(recCount.ToString() + " rows selected.\r\n"); } else { tbResult.AppendText("Statement executed.\r\n"); } tbResult.AppendText("\r\n"); dataReader.Close(); // commit transaction tr.Commit(); } catch (PgSqlException exception) { // rollback transaction on error tr.Rollback(); tbResult.AppendText(exception.Message + "\r\n\r\n"); throw; } }