Ejemplo n.º 1
0
        /// <summary>
        /// Create a SqlCeCommand instance using the global SQL CE Conection instance and associate this with a transaction
        /// </summary>
        /// <param name="transaction">SqlCeTransaction to be used for the SqlCeCommand</param>
        public static System.Data.SqlServerCe.SqlCeCommand CreateCommand(System.Data.SqlServerCe.SqlCeTransaction transaction)
        {
            var command = Connection.CreateCommand();

            command.Transaction = transaction;
            return(command);
        }
 /// <summary>
 /// Starts a SqlCeTransaction using the global SQL CE Conection instance
 /// </summary>
 public System.Data.SqlServerCe.SqlCeTransaction BeginTransaction()
 {
     if (transaction != null)
         throw new System.InvalidOperationException("A transaction has already been started. Only one transaction is allowed");
     transaction = EntityBase.Connection.BeginTransaction();
     EnglishSentences.Transaction = transaction;
     return transaction;
 }
Ejemplo n.º 3
0
 /// <summary>
 /// Starts a SqlCeTransaction using the global SQL CE Conection instance
 /// </summary>
 public System.Data.SqlServerCe.SqlCeTransaction BeginTransaction()
 {
     if (transaction != null)
     {
         throw new System.InvalidOperationException("A transaction has already been started. Only one transaction is allowed");
     }
     transaction = EntityBase.Connection.BeginTransaction();
     EnglishSentences.Transaction = transaction;
     return(transaction);
 }
Ejemplo n.º 4
0
 static public void SafeTransRollback(System.Data.SqlServerCe.SqlCeTransaction trans)
 {
     try
     {
         trans.Rollback();
     }
     catch (System.InvalidOperationException)
     {
     }
 }
Ejemplo n.º 5
0
 /// <summary>
 /// Starts a SqlCeTransaction using the global SQL CE Conection instance
 /// </summary>
 public System.Data.SqlServerCe.SqlCeTransaction BeginTransaction()
 {
     if (transaction != null)
     {
         throw new System.InvalidOperationException("A transaction has already been started. Only one transaction is allowed");
     }
     transaction          = EntityBase.Connection.BeginTransaction();
     Contact.Transaction  = transaction;
     Customer.Transaction = transaction;
     Product.Transaction  = transaction;
     Stock.Transaction    = transaction;
     return(transaction);
 }
Ejemplo n.º 6
0
 protected void Dispose(bool disposing)
 {
     if (disposed)
     {
         return;
     }
     if (disposing)
     {
         if (transaction != null)
         {
             transaction.Dispose();
             transaction = null;
         }
     }
     disposed = true;
 }
Ejemplo n.º 7
0
        static public int GetLastInsertId(System.Data.SqlServerCe.SqlCeTransaction trans)
        {
            System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery();
            sqlQuery.CommandText = "SELECT @@IDENTITY";
            if (trans != null)
            {
                sqlQuery.Transaction = trans;
            }
            object o = ExecuteScalar(sqlQuery);

            if (o == null || o == DBNull.Value)
            {
                return(-1);
            }
            else
            {
                return((int)(decimal)o);
            }
        }
 protected void Dispose(bool disposing)
 {
     if (disposed) return;
     if (disposing)
     {
         if (transaction != null)
         {
             transaction.Dispose();
             transaction = null;
         }
     }
     disposed = true;
 }
Ejemplo n.º 9
0
        bool CloneGame(int gameId)
        {
            System.Data.SqlServerCe.SqlCeTransaction trans      = null;
            System.Data.SqlServerCe.SqlCeCommand     sqlQuery   = null;
            System.Data.SqlServerCe.SqlCeDataReader  sqlReader  = null;
            System.Data.SqlServerCe.SqlCeDataReader  sqlReader2 = null;

            try
            {
                int comm_maxlen = DB.DB_GetMaxLength("Games", "Comment");

                // Склонировать (используя транзакцию)
                sqlQuery             = DB.CreateQuery();
                trans                = DB.sqlConnection.BeginTransaction();
                sqlQuery.Transaction = trans;
                sqlQuery.CommandText = "INSERT INTO Games(Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, Comment, StartDate) (SELECT Type, GameOptions, DealsInMatch, FirstDealer, ZoneSwims, fk_Folder_id, fk_N, fk_S, fk_E, fk_W, Place, (CASE WHEN Comment is NULL THEN '{клон}' ELSE (SUBSTRING(Comment, 1, (" + comm_maxlen + " - LEN(' {клон}'))) + ' {клон}')  END) as Comment_clon, StartDate FROM Games WHERE id=" + gameId + ")";
                if (sqlQuery.ExecuteNonQuery() == 0)
                {
                    trans.Rollback();

                    MessageBox.Show("Игра #" + gameId + " не была склонирована!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                    return(false);
                }

                int new_gameId = DB.GetLastInsertId(trans);


                sqlQuery.CommandText = "SELECT id FROM Matches WHERE fk_Game_id=" + gameId + " ORDER BY id";
                sqlReader            = sqlQuery.ExecuteReader();

                while (sqlReader.Read())
                {
                    int cur_matchId = sqlReader.GetInt32(0);

                    sqlQuery.CommandText = "INSERT INTO Matches(fk_Game_id, SCORE_NS, SCORE_EW) (SELECT " + new_gameId + " as fk_Game_id__new, SCORE_NS, SCORE_EW FROM Matches WHERE id=" + cur_matchId + ")";
                    if (sqlQuery.ExecuteNonQuery() == 0)
                    {
                        sqlReader.Close();
                        trans.Rollback();

                        MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                        return(false);
                    }

                    int new_cur_matchId = DB.GetLastInsertId(trans);


                    /****int added = 0;
                     * sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + ")";
                     * added = sqlQuery.ExecuteNonQuery();
                     *
                     * sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + ")";
                     * added = sqlQuery.ExecuteNonQuery();
                     *
                     * sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + ")";
                     * added = sqlQuery.ExecuteNonQuery();*****/

                    int cur_dealId = -1;

                    sqlQuery.CommandText = "SELECT id FROM Deals_Rob WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
                    sqlReader2           = sqlQuery.ExecuteReader();
                    while (sqlReader2.Read())
                    {
                        cur_dealId           = (int)sqlReader2.GetSqlInt32(0);
                        sqlQuery.CommandText = "INSERT INTO Deals_Rob(fk_Match_id, Pair, Contract, Oners, Result, CardsDistribution) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Oners, Result, CardsDistribution FROM Deals_Rob WHERE id=" + cur_dealId + ")";
                        if (sqlQuery.ExecuteNonQuery() == 0)
                        {
                            sqlReader2.Close();
                            sqlReader.Close();
                            trans.Rollback();

                            MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                            return(false);
                        }
                    }
                    sqlReader2.Close();

                    sqlQuery.CommandText = "SELECT id FROM Deals_Sport WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
                    sqlReader2           = sqlQuery.ExecuteReader();
                    while (sqlReader2.Read())
                    {
                        cur_dealId           = (int)sqlReader2.GetSqlInt32(0);
                        sqlQuery.CommandText = "INSERT INTO Deals_Sport(fk_Match_id, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair) (SELECT " + new_cur_matchId + " as fk_Match_id__new, Pair, Contract, Result, CardsDistribution, Figures, Fits, StrongestPair FROM Deals_Sport WHERE id=" + cur_dealId + ")";
                        if (sqlQuery.ExecuteNonQuery() == 0)
                        {
                            sqlReader2.Close();
                            sqlReader.Close();
                            trans.Rollback();

                            MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                            return(false);
                        }
                    }
                    sqlReader2.Close();

                    sqlQuery.CommandText = "SELECT id FROM Deals_Double WHERE fk_Match_id=" + cur_matchId + " ORDER BY id";
                    sqlReader2           = sqlQuery.ExecuteReader();
                    while (sqlReader2.Read())
                    {
                        cur_dealId           = (int)sqlReader2.GetSqlInt32(0);
                        sqlQuery.CommandText = "INSERT INTO Deals_Double(fk_Match_id, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted) (SELECT " + new_cur_matchId + " as fk_Match_id__new, CardsDistribution, Pair1, Contract1, Result1, Pair2, Contract2, Result2, IsSecondStarted FROM Deals_Double WHERE id=" + cur_dealId + ")";
                        if (sqlQuery.ExecuteNonQuery() == 0)
                        {
                            sqlReader2.Close();
                            sqlReader.Close();
                            trans.Rollback();

                            MessageBox.Show("Игра #" + gameId + " не была склонирована!\nОшибка клонирования сдачи #" + cur_dealId + " матча #" + cur_matchId + "!", "db error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                            return(false);
                        }
                    }
                    sqlReader2.Close();
                }
                sqlReader.Close();


                // Добавить в список
                sqlQuery.CommandText = "SELECT g.id, g.StartDate, g.Place, g.Comment, g.Type, g.DealsInMatch, g.GameOptions, n.Name as N, s.Name as S, e.Name as E, w.Name as W FROM Games g LEFT JOIN Players n ON g.fk_N = n.id  LEFT JOIN Players s ON g.fk_S = s.id  LEFT JOIN Players e ON g.fk_E = e.id  LEFT JOIN Players w ON g.fk_W = w.id WHERE g.id=" + new_gameId;
                sqlReader            = sqlQuery.ExecuteReader();
                if (sqlReader.Read())
                {
                    Load1Game(null, sqlReader);

                    sqlReader.Close();
                    trans.Commit();

                    LoadFoldersToCombo(true); // перезагрузить список папок

                    MessageBox.Show("Игра #" + gameId + " успешно склонирована в #" + new_gameId, "Клонирование успешно", MessageBoxButtons.OK, MessageBoxIcon.Asterisk, MessageBoxDefaultButton.Button1);
                    return(true);
                }
                else
                {
                    sqlReader.Close();
                    trans.Rollback();

                    MessageBox.Show("Игра #" + gameId + " не была склонирована в #" + new_gameId, "Клонирование неудачно", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                    return(false);
                }
            }
            catch (System.Data.SqlServerCe.SqlCeException ex)
            {
                if (trans != null)
                {
                    DB.SafeTransRollback(trans);
                }
                if (sqlReader != null && sqlReader.IsClosed == false)
                {
                    sqlReader.Close();
                }
                if (sqlReader2 != null && sqlReader2.IsClosed == false)
                {
                    sqlReader2.Close();
                }


                if (ex.NativeError == 0)
                {
                    return(false);
                }
                else
                {
                    throw;
                }
            }
        }
Ejemplo n.º 10
0
        //-------------------------------------------------------- create/modify db 'bridge' ---------------------------------

        /*static public bool CreateBridgeDB()
         * {
         *  System.Data.SqlServerCe.SqlCeCommand sqlQuery = CreateQuery();
         *
         *  sqlQuery.CommandText = "CREATE DATABASE Bridge";
         *  try
         *  {
         *      sqlQuery.ExecuteNonQuery();
         *      return true;
         *  }
         *  catch (System.Data.SqlServerCe.SqlCeException e)
         *  {
         *      // error #25114 - База уже есть
         *      MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
         *      return false;
         *  }
         * }*/



        static public bool CreateTables(bool use_trans)
        {
            System.Data.SqlServerCe.SqlCeCommand     sqlQuery = CreateQuery();
            System.Data.SqlServerCe.SqlCeTransaction trans    = null;

            if (use_trans)
            {
                trans = sqlConnection.BeginTransaction();
                sqlQuery.Transaction = trans;
            }

            sqlQuery.CommandText = "CREATE TABLE Folders (id INT IDENTITY PRIMARY KEY, Name NVARCHAR(30))";
            try
            {
                sqlQuery.ExecuteNonQuery();
            }
            catch (System.Data.SqlServerCe.SqlCeException e)
            {
                MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }


            /*sqlQuery.CommandText = "alter table folders alter column Name nvarchar(30)";
             * sqlQuery.ExecuteNonQuery();
             * sqlQuery.CommandText = "alter table players alter column Name nvarchar(50)";
             * sqlQuery.ExecuteNonQuery();*/


            sqlQuery.CommandText = "CREATE TABLE Players (id INT IDENTITY PRIMARY KEY, Name NVARCHAR(50))";
            try
            {
                sqlQuery.ExecuteNonQuery();
            }
            catch (System.Data.SqlServerCe.SqlCeException e)
            {
                MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }



            sqlQuery.CommandText = "CREATE TABLE Games (id INT IDENTITY PRIMARY KEY, Type TINYINT NOT NULL, GameOptions TINYINT, DealsInMatch TINYINT, FirstDealer TINYINT, ZoneSwims BIT, fk_Folder_id INT, CONSTRAINT FK__Games__no1__Folders__id FOREIGN KEY(fk_Folder_id) REFERENCES Folders(id), fk_N INT, CONSTRAINT FK__Games__N__Players__id FOREIGN KEY(fk_N) REFERENCES Players(id), fk_S INT, CONSTRAINT FK__Games__S__Players__id FOREIGN KEY(fk_S) REFERENCES Players(id), fk_E INT, CONSTRAINT FK__Games__E__Players__id FOREIGN KEY(fk_E) REFERENCES Players(id), fk_W INT, CONSTRAINT FK__Games__W__Players__id FOREIGN KEY(fk_W) REFERENCES Players(id), Place NVARCHAR(30), Comment NVARCHAR(60), StartDate DATETIME)";
            try
            {
                sqlQuery.ExecuteNonQuery();
            }
            catch (System.Data.SqlServerCe.SqlCeException e)
            {
                MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }

            sqlQuery.CommandText = "CREATE TABLE Matches (id INT IDENTITY PRIMARY KEY, fk_Game_id INT, CONSTRAINT FK__Matches__no1__Games__id FOREIGN KEY(fk_Game_id) REFERENCES Games(id) ON UPDATE CASCADE ON DELETE CASCADE, SCORE_NS INT, SCORE_EW INT)";
            try
            {
                sqlQuery.ExecuteNonQuery();
            }
            catch (System.Data.SqlServerCe.SqlCeException e)
            {
                MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }


            sqlQuery.CommandText = "CREATE TABLE Deals_Rob (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Rob__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, Pair BIT, Contract TINYINT, Oners TINYINT, Result TINYINT, CardsDistribution BINARY(20))";
            try
            {
                sqlQuery.ExecuteNonQuery();
            }
            catch (System.Data.SqlServerCe.SqlCeException e)
            {
                MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }

            sqlQuery.CommandText = "CREATE TABLE Deals_Sport (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Sport__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, Pair BIT, Contract TINYINT, Result TINYINT, CardsDistribution BINARY(20), Figures TINYINT, Fits TINYINT, StrongestPair BIT)";
            try
            {
                sqlQuery.ExecuteNonQuery();
            }
            catch (System.Data.SqlServerCe.SqlCeException e)
            {
                MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }

            sqlQuery.CommandText = "CREATE TABLE Deals_Double (id INT IDENTITY PRIMARY KEY, fk_Match_id INT, CONSTRAINT FK__Deals_Double__no1__Matches__id FOREIGN KEY(fk_Match_id) REFERENCES Matches(id) ON UPDATE CASCADE ON DELETE CASCADE, CardsDistribution BINARY(20), Pair1 BIT, Contract1 TINYINT, Result1 TINYINT, Pair2 BIT, Contract2 TINYINT, Result2 TINYINT, IsSecondStarted BIT)";
            try
            {
                sqlQuery.ExecuteNonQuery();
            }
            catch (System.Data.SqlServerCe.SqlCeException e)
            {
                MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
            }


            if (use_trans)
            {
                try
                {
                    trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
                    return(true);
                }
                catch (System.Data.SqlServerCe.SqlCeException e)
                {
                    SafeTransRollback(trans);
                    MessageBox.Show(e.Message, "Error #" + e.NativeError, MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Button1);
                    return(false);
                }
            }
            else
            {
                return(true);
            }
        }
Ejemplo n.º 11
0
        static private int ExecuteNonQuery(System.Data.SqlServerCe.SqlCeCommand command, bool useTransaction, out int out__rowid, bool findoutLastInsertId)
        {
            bool retry         = false;
            bool trans_started = false;

            System.Data.SqlServerCe.SqlCeTransaction trans = null;


            try
            {
                if (useTransaction)
                {
                    trans               = command.Connection.BeginTransaction();
                    trans_started       = true;
                    command.Transaction = trans;
                }
                int rows_aff = command.ExecuteNonQuery();
                int row_id   = -1;
                if (findoutLastInsertId)
                {
                    if (useTransaction)
                    {
                        row_id = GetLastInsertId(trans);
                    }
                    else
                    {
                        row_id = GetLastInsertId();
                    }
                }
                if (useTransaction)
                {
                    trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
                    trans_started       = false;
                    command.Transaction = null;
                }

                out__rowid = row_id;
                return(rows_aff);
            }
            catch (System.Data.SqlServerCe.SqlCeException ex)
            {
                ////////MessageBox.Show(ex.Message + "\n" + "HRES = " + ex.HResult + "\n" + "ERRNO = " + ex.NativeError);

                if (useTransaction && trans_started)
                {
                    SafeTransRollback(trans);
                    trans.Dispose();
                    trans = null;
                    command.Transaction = null;
                    trans_started       = false;
                }

                if (ex.NativeError == 0)
                {
                    retry = true;
                }
                else
                {
                    throw;
                }
            }



            if (retry)
            {
                command.Connection.Close();
                command.Connection.Open();

                if (useTransaction)
                {
                    trans               = command.Connection.BeginTransaction();
                    trans_started       = true;
                    command.Transaction = trans;
                }
                int rows_aff = command.ExecuteNonQuery();
                int row_id   = -1;
                if (findoutLastInsertId)
                {
                    if (useTransaction)
                    {
                        row_id = GetLastInsertId(trans);
                    }
                    else
                    {
                        row_id = GetLastInsertId();
                    }
                }
                if (useTransaction)
                {
                    trans.Commit(System.Data.SqlServerCe.CommitMode.Immediate);
                    trans_started       = false;
                    command.Transaction = null;
                }

                out__rowid = row_id;
                return(rows_aff);
            }
            else
            {
                out__rowid = -1;
                return(0);
            }
        }