Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        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);
        }
Ejemplo n.º 3
0
        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;
        }
Ejemplo n.º 4
0
        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(); //
            }
        }
Ejemplo n.º 5
0
        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);
        }
Ejemplo n.º 6
0
        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);
        }
Ejemplo n.º 7
0
        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);
        }
Ejemplo n.º 8
0
        /// <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);
        }
Ejemplo n.º 9
0
        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);
        }
Ejemplo n.º 10
0
        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);
        }
Ejemplo n.º 11
0
        //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);
        }
Ejemplo n.º 12
0
        /// <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);
                }
            }
        }
Ejemplo n.º 13
0
        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);
        }
Ejemplo n.º 14
0
        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);
                }
            }
        }
Ejemplo n.º 15
0
        /// <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);
                }
            }
        }
Ejemplo n.º 16
0
        /// <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);
        }
Ejemplo n.º 17
0
        //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);
        }
Ejemplo n.º 18
0
        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);
        }