private bool RunSqlCommandNonTransaction(string sql)
{
SQLiteConnection myConnection = new SQLiteConnection(ConnectionString);
myConnection.Open();
SQLiteCommand myCommand = new SQLiteCommand();
myCommand.Connection = myConnection;
bool rval = false;
try
{
myCommand.CommandText = sql;
if (myCommand.ExecuteNonQuery() > 0)
{
rval = true;
}
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
}
catch (Exception e)
{
Console.WriteLine(e.ToString());
Console.WriteLine("Error running " + sql);
this.lastMessage = e.ToString();
rval = false;
}
finally
{
myConnection.Close();
}
return(rval);
}
public override int SaveTable(DataTable dataTable, string sql)
{
Console.WriteLine("Saving " + dataTable.TableName);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
OracleConnection myAccessConn = new OracleConnection(ConnectionString);
OracleCommand myAccessCommand = new OracleCommand(sql, myAccessConn);
OracleDataAdapter myDataAdapter = new OracleDataAdapter(myAccessCommand);
OracleCommandBuilder cb = new OracleCommandBuilder(myDataAdapter);
// myDataAdapter.InsertCommand = cb.GetInsertCommand();
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
myAccessConn.Open();
int recordCount = 0;
try
{ // call Fill method only to make things work. (we ignore myDataSet)
myDataAdapter.Fill(myDataSet, dataTable.TableName);
recordCount = myDataAdapter.Update(dataTable);
}
finally
{
myAccessConn.Close();
}
return(recordCount);
}
public override void FillTable(DataTable dataTable, string sql)
{
base.SqlCommands.Add("Fill(" + dataTable.TableName + ")");
var myAccessConn = new SQLiteConnection(ConnectionString);
var myAccessCommand = new SQLiteCommand(sql, myAccessConn);
var myDataAdapter = new SQLiteDataAdapter(myAccessCommand);
//Console.WriteLine(sql);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
try
{
myAccessConn.Open();
myDataAdapter.Fill(dataTable);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close();
myAccessConn.Dispose();
}
// DataTable tbl = myDataSet.Tables[tableName];
// return tbl;
}
public override void FillTable(DataTable dataTable, string sql)
{
if (dataTable.TableName == "")
{
dataTable.TableName = "table1";
}
base.SqlCommands.Add("Fill(" + dataTable.TableName + ")");
string strAccessSelect = sql;
var myAccessConn = new NpgsqlConnection(ConnectionString);
var myAccessCommand = new NpgsqlCommand(strAccessSelect, myAccessConn);
var myDataAdapter = new NpgsqlDataAdapter(myAccessCommand);
//Console.WriteLine(sql);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
try
{
myAccessConn.Open();
myDataAdapter.Fill(dataTable);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close(); //
}
}
public DataTable Fill(string tableName, string sql, DataSet myDataSet)
{
string strAccessSelect = sql;
SQLiteConnection myAccessConn = new SQLiteConnection(ConnectionString);
SQLiteCommand myAccessCommand = new SQLiteCommand(strAccessSelect, myAccessConn);
SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(myAccessCommand);
//Console.WriteLine(sql);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
try
{
myAccessConn.Open();
myDataAdapter.Fill(myDataSet, tableName);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close();
}
DataTable tbl = myDataSet.Tables[tableName];
return(tbl);
}
public DataTable Table(string tableName, string sql, bool AcceptChangesDuringFill)
{
string strAccessSelect = sql;
MySqlConnection c = new MySqlConnection(ConnectionString);
var cmd = new MySqlCommand(strAccessSelect, c);
var myDataAdapter = new MySqlDataAdapter(cmd);
myDataAdapter.AcceptChangesDuringFill = AcceptChangesDuringFill;
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
DataSet myDataSet = new DataSet();
try
{
c.Open();
myDataAdapter.Fill(myDataSet, tableName);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
c.Close();
}
DataTable tbl = myDataSet.Tables[tableName];
myDataSet.Tables.Remove(tbl);
return(tbl);
}
public override int SaveTable(DataTable dataTable, string sql)
{
Performance perf = new Performance();
Logger.WriteLine("Saving " + dataTable.TableName);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
int recordCount = 0;
using (var conn = GetConnection(ConnectionString))
using (var myAccessCommand = GetCommand(sql, conn))
using (var myDataAdapter = GetAdapter(myAccessCommand))
using (var karlCB = GetBuilder(myDataAdapter))
{
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
//baseline Saved 1000 records in 6.938seconds
// transaction Saved 1000 records in 0.052seconds
//baseline Saved 50000 records in 1.659seconds
//(use Insert only) Saved 50000 records in 1.194seconds
try
{
conn.Open();
var dbTrans = conn.BeginTransaction();
myDataAdapter.Fill(myDataSet, dataTable.TableName);
recordCount = myDataAdapter.Update(dataTable);
dbTrans.Commit();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
}
string msg = "[" + dataTable.TableName + "] " + recordCount;
Logger.WriteLine(msg, "ui");
Console.WriteLine(msg);
return(recordCount);
}
/// <summary>
/// runs sql command.
/// returns number of rows affected.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
int RunSqlCommand(string sql, string SqlConnString, bool useTransaction = true)
{
int rval = 0;
//this.lastMessage = "";
var myConnection = new NpgsqlConnection(SqlConnString);
myConnection.Open();
var myCommand = new NpgsqlCommand();
NpgsqlTransaction myTrans = null;
myCommand.Connection = myConnection;
if (useTransaction)
{
// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
myCommand.Transaction = myTrans;
}
try
{
myCommand.CommandText = sql;
rval = myCommand.ExecuteNonQuery();
if (useTransaction)
{
myTrans.Commit();
}
//Logger.WriteLine(rval + " rows affected");
this.lastSqlCeCommand = sql;
SqlCommands.Add(sql);
}
catch (Exception e)
{
if (useTransaction)
{
myTrans.Rollback();
}
Logger.WriteLine(e.ToString());
Logger.WriteLine("Error running " + sql);
//this.lastMessage = e.ToString();
throw e;
}
finally
{
myConnection.Close();
}
return(rval);
}
public virtual TContext CreateContext(
Action <JetDbContextOptionsBuilder> jetOptions = null,
Action <IServiceProvider, DbContextOptionsBuilder> options = null,
Action <ModelBuilder> model = null)
{
var context = new TContext();
context.Initialize(
TestStore.Name,
command => SqlCommands.Add(command.CommandText),
model: model,
options: options,
jetOptions: jetOptions);
TestStore.Clean(context);
return(context);
}
public PostgreSQL(string cs)
{
//SearchPath=wtr..
//searchpath=schemanamehere..
//NpgsqlConnection conn = new NpgsqlConnection(cs);
//"Server=127.0.0.1;Database=eeeeee;User id=npgsql_tests;password=npgsql_tests;");
ConnectionString = cs;
Name = ConnectionStringUtility.GetToken(cs, "Database", "");
var pw = ConnectionStringUtility.GetToken(cs, "password", "");
var logSafeConnectionString = cs;
if (pw.Trim() != "")
{
logSafeConnectionString = logSafeConnectionString.ToLower().Replace(pw, "*******");
}
SqlCommands.Add(logSafeConnectionString);
}
//public override int InsertTable(DataTable dataTable)
//{
// //if (DateTime.Now.Month == 2) ;
// //return SaveTable(dataTable);
// //http://sqlite.phxsoftware.com/forums/t/134.aspx
// Performance perf = new Performance();
// Logger.WriteLine("InsertTable " + dataTable.TableName);
// string sql = "select * from " + dataTable.TableName + " where 2=1";
// DataSet myDataSet = new DataSet();
// myDataSet.Tables.Add(dataTable.TableName);
// SQLiteConnection conn = new SQLiteConnection(ConnectionString);
// SQLiteCommand cmd = new SQLiteCommand(sql, conn);
// int recordCount = 0;
// try
// {
// conn.Open();
// SQLiteTransaction tran = conn.BeginTransaction();
// SQLiteDataAdapter da = new SQLiteDataAdapter(cmd);
// SQLiteCommandBuilder bld = new SQLiteCommandBuilder(da);
// this.lastSqlCommand = sql;
// SqlCommands.Add(sql);
// da.InsertCommand = (SQLiteCommand)((ICloneable)bld.GetInsertCommand()).Clone();
// bld.DataAdapter = null; // prevent callbacks
// da.Fill(myDataSet, dataTable.TableName);
// recordCount = da.Update(dataTable);
// tran.Commit();
// }finally
// {
// if( conn != null)
// conn.Close();
// }
// Logger.WriteLine("Saved " + recordCount + " records in " + perf.ElapsedSeconds + "seconds");
// return recordCount;
//}
public override int SaveTable(DataTable dataTable, string sql)
{
Performance perf = new Performance();
Logger.WriteLine("Saving " + dataTable.TableName);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
SQLiteConnection conn = new SQLiteConnection(ConnectionString);
SQLiteCommand myAccessCommand = new SQLiteCommand(sql, conn);
SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(myAccessCommand);
SQLiteCommandBuilder karlCB = new SQLiteCommandBuilder(myDataAdapter);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
int recordCount = 0;
//baseline Saved 1000 records in 6.938seconds
// transaction Saved 1000 records in 0.052seconds
//baseline Saved 50000 records in 1.659seconds
//(use Insert only) Saved 50000 records in 1.194seconds
try
{
conn.Open();
var dbTrans = conn.BeginTransaction();
myDataAdapter.Fill(myDataSet, dataTable.TableName);
recordCount = myDataAdapter.Update(dataTable);
dbTrans.Commit();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
Logger.WriteLine("Saved " + recordCount + " records in " + perf.ElapsedSeconds + "seconds");
return(recordCount);
}
/// <summary>
/// runs sql command.
/// returns number of rows affected.
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public int RunSqlCommand(string sql, string SqlConnString)
{
int rval = 0;
this.lastMessage = "";
using (SQLiteConnection myConnection = new SQLiteConnection(SqlConnString))
{
myConnection.Open();
using (SQLiteCommand myCommand = new SQLiteCommand())
{
SQLiteTransaction myTrans;
// Start a local transaction
myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted);
// Assign transaction object for a pending local transaction
myCommand.Connection = myConnection;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = sql;
rval = myCommand.ExecuteNonQuery();
myTrans.Commit();
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
}
catch (Exception e)
{
myTrans.Rollback();
Console.WriteLine(e.ToString());
Console.WriteLine("Error running " + sql);
this.lastMessage = e.ToString();
throw e;
}
finally
{
myConnection.Close();
}
return(rval);
}
}
}
public override int SaveTable(DataTable dataTable, string sql)
{
Performance perf = new Performance();
Logger.WriteLine("Saving " + dataTable.TableName);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
MySqlConnection conn = new MySqlConnection(ConnectionString);
MySqlCommand myAccessCommand = new MySqlCommand(sql, conn);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(myAccessCommand);
MySqlCommandBuilder karlCB = new MySqlCommandBuilder(myDataAdapter);
this.lastSqlCommand = sql;
if (MapToLowerCase)
{
MapToLower(dataTable, myDataAdapter);
}
SqlCommands.Add(sql);
int recordCount = 0;
try
{
conn.Open();
var dbTrans = conn.BeginTransaction();
myDataAdapter.Fill(myDataSet, dataTable.TableName);
recordCount = myDataAdapter.Update(dataTable);
dbTrans.Commit();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
Logger.WriteLine("Saved " + recordCount + " records in " + perf.ElapsedSeconds + "seconds");
return(recordCount);
}
public override int SaveTable(DataTable dataTable, string sql)
{
Console.WriteLine("Saving " + dataTable.TableName);
Performance perf = new Performance();
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
using (SqlConnection conn = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
conn.Open();
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlCommandBuilder karlCB = new SqlCommandBuilder(da);
SqlTransaction tran = conn.BeginTransaction();
cmd.Transaction = tran;
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
da.UpdateBatchSize = 0;
int recordCount = 0;
try
{ // call Fill method only to make things work. (we ignore myDataSet)
da.Fill(myDataSet, dataTable.TableName);
recordCount = da.Update(dataTable);
tran.Commit();
}
finally
{
conn.Close();
}
Logger.WriteLine("Saved " + recordCount + " records in " + perf.ElapsedSeconds + "seconds");
return(recordCount);
}
}
}
/// <summary>
/// returns table using sql
/// </summary>
/// <param name="tableName"></param>
/// <param name="sql"></param>
/// <param name="AcceptChangesDuringFill"></param>
/// <returns></returns>
public DataTable Table(string tableName, string sql, bool AcceptChangesDuringFill)
{
string strAccessSelect = sql;
using (SQLiteConnection myAccessConn = new SQLiteConnection(ConnectionString))
{
//myAccessConn.ConnectionTimeout = 30;
using (SQLiteCommand myAccessCommand = new SQLiteCommand(strAccessSelect, myAccessConn))
{
myAccessCommand.CommandTimeout = myAccessConn.ConnectionTimeout;
SQLiteDataAdapter myDataAdapter = new SQLiteDataAdapter(myAccessCommand);
myDataAdapter.AcceptChangesDuringFill = AcceptChangesDuringFill;
//Console.WriteLine(sql);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
DataSet myDataSet = new DataSet();
try
{
myAccessConn.Open();
myDataAdapter.Fill(myDataSet, tableName);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.ToString();
Console.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close();
}
DataTable tbl = myDataSet.Tables[tableName];
myDataSet.Tables.Remove(tbl);
return(tbl);
}
}
}
/// <summary>
/// returns table using sql
/// </summary>
/// <param name="tableName"></param>
/// <param name="sql"></param>
/// <param name="AcceptChangesDuringFill"></param>
/// <returns></returns>
public DataTable Table(string tableName, string sql, bool AcceptChangesDuringFill)
{
base.SqlCommands.Add("Table(" + sql + ")\n AcceptChangesDuringFill " + AcceptChangesDuringFill);
string strAccessSelect = sql;
var myAccessConn = new NpgsqlConnection(ConnectionString);
var myAccessCommand = new NpgsqlCommand(strAccessSelect, myAccessConn);
var myDataAdapter = new NpgsqlDataAdapter(myAccessCommand);
myDataAdapter.AcceptChangesDuringFill = AcceptChangesDuringFill;
//Console.WriteLine(sql);
this.lastSqlCommand = sql;
SqlCommands.Add(sql);
DataSet myDataSet = new DataSet();
try
{
myAccessConn.Open();
myDataAdapter.Fill(myDataSet, tableName);
}
catch (Exception e)
{
string msg = "Error reading from database " + sql + " Exception " + e.Message;
Logger.WriteLine(msg);
throw e;
}
finally
{
myAccessConn.Close();
}
DataTable tbl = myDataSet.Tables[tableName];
myDataSet.Tables.Remove(tbl);
return(tbl);
}
//public override int SaveTable(DataTable dataTable, string sql)
//{
// return SaveTable(dataTable, sql, false);
//}
/// <summary>
/// SaveTable1 is SLOW -- not using a transaction.
/// </summary>
/// <param name="dataTable"></param>
/// <param name="sql"></param>
/// <param name="insert"></param>
/// <returns></returns>
private int SaveTable1(DataTable dataTable, string sql, bool insert = false)
{
base.SqlCommands.Add(sql);
//Logger.WriteLine("Saving " + dataTable.TableName + "\n ");
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
NpgsqlConnection myAccessConn = new NpgsqlConnection(ConnectionString);
NpgsqlCommand myAccessCommand = new NpgsqlCommand(sql, myAccessConn);
var da = new NpgsqlDataAdapter(myAccessCommand);
// myDataAdapter.TableMappings.Add(dataTable.TableName.ToLower(), dataTable.TableName);
var cb = new NpgsqlCommandBuilder(da);
cb.ConflictOption = ConflictOption.OverwriteChanges;
da.InsertCommand = (NpgsqlCommand)cb.GetInsertCommand();
//da.RowUpdated += da_RowUpdated;
Logger.WriteLine(da.InsertCommand.CommandText);
if (!insert)
{
try
{
da.UpdateCommand = (NpgsqlCommand)cb.GetUpdateCommand();
}
catch (InvalidOperationException ioe)
{
Console.WriteLine(ioe.Message);
}
}
if (!insert)
{
try
{
da.DeleteCommand = (NpgsqlCommand)cb.GetDeleteCommand();
}
catch (InvalidOperationException ioe)
{
Console.WriteLine(ioe.Message);
}
}
this.lastSqlCeCommand = sql;
SqlCommands.Add(sql);
myAccessConn.Open();
int recordCount = 0;
try
{ // call Fill method only to make things work. (we ignore myDataSet)
da.Fill(myDataSet, dataTable.TableName);
recordCount = da.Update(dataTable);
}
catch (DBConcurrencyException e)
{
throw e;
}
finally
{
myAccessConn.Close();
}
return(recordCount);
}
public override int SaveTable(DataTable dataTable, string sql)
{
//Logger.WriteLine("Save Table with transaction");
Performance perf = new Performance();
Logger.WriteLine("Saving " + dataTable.TableName);
//Logger.WriteLine(sql);
DataSet myDataSet = new DataSet();
myDataSet.Tables.Add(dataTable.TableName);
NpgsqlConnection conn = new NpgsqlConnection(ConnectionString);
NpgsqlCommand myAccessCommand = new NpgsqlCommand(sql, conn);
NpgsqlDataAdapter da = new NpgsqlDataAdapter(myAccessCommand);
NpgsqlCommandBuilder cb = new NpgsqlCommandBuilder(da);
da.UpdateCommand = cb.GetUpdateCommand();
da.InsertCommand = cb.GetInsertCommand();
da.DeleteCommand = cb.GetDeleteCommand();
cb.ConflictOption = ConflictOption.OverwriteChanges; // this fixes System.InvalidCastException : Specified cast is not valid.
// when reserved word (group) was a column name
if (MapToLowerCase)
{
var map = da.TableMappings.Add(dataTable.TableName.ToLower(), dataTable.TableName);
for (int i = 0; i < dataTable.Columns.Count; i++)
{
var cn = dataTable.Columns[i].ColumnName;
map.ColumnMappings.Add(cn.ToLower(), cn);
}
//PrintMapping(da);
}
SqlCommands.Add(sql);
int recordCount = 0;
//da.RowUpdating += myDataAdapter_RowUpdating;
try
{
conn.Open();
var dbTrans = conn.BeginTransaction();
da.Fill(myDataSet, dataTable.TableName);
recordCount = da.Update(dataTable);
dbTrans.Commit();
}
finally
{
if (conn != null)
{
conn.Close();
}
}
Logger.WriteLine("Saved " + recordCount + " records in " + perf.ElapsedSeconds + "seconds");
if (SqlCommands.Count > 5000)
{
SqlCommands.Clear();
}
return(recordCount);
}