static void SimpleTransaction()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();

                    SqlDatabaseTransaction trans = cnn.BeginTransaction();
                    cmd.Transaction = trans;

                    try
                    {
                        for (int i = 0; i < 1000; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'A Value" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                        trans.Commit(); //Commit since all inserts are completed.

                        //Test if transaction is open
                        if (trans.IsOpen)
                        {
                            trans.Commit();
                        }
                    }
                    catch (SqlDatabaseException sqlex)
                    {
                        //Rollback as their was an error.
                        if (trans.IsOpen)
                        {
                            trans.Rollback();
                        }
                        Console.WriteLine(sqlex.Message);
                    }

                    cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
                    Console.WriteLine("Table Record Count using COUNT(*) : {0}", cmd.ExecuteScalar());

                    // Pure SQL Way of starting and committing transaction.
                    cmd.CommandText = "BEGIN";
                    cmd.ExecuteNonQuery();
                    // Your SQL Statements and commands can reside here to run with-in a transaction.
                    // INSERT UPDATE and DELETE
                    cmd.CommandText = "COMMIT"; //ROLLBACK
                    cmd.ExecuteNonQuery();
                }
            }
        }
示例#2
0
        private void SimpleTransaction_Click(object sender, EventArgs e)
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();

                    SqlDatabaseTransaction trans = cnn.BeginTransaction();
                    cmd.Transaction = trans;

                    try
                    {
                        for (int i = 0; i < 1000; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                    } catch (SqlDatabaseException sqlex)
                    {
                        trans.Rollback();
                        Debug.WriteLine(sqlex.Message);
                    }
                    finally
                    {
                        trans.Commit();
                    }

                    cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
                    Debug.WriteLine(cmd.ExecuteScalar());
                }
            }
        }
        static void MultipleCommandTransaction()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();

                // Create a table
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "Create Table If not exists temptable2(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();
                }

                // Start a transaction on this connection
                SqlDatabaseTransaction trans = cnn.BeginTransaction();

                try
                {
                    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                    {
                        cmd.Transaction = trans; // attach this Command object to transaction.

                        for (int i = 0; i < 10; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                    }

                    // Other processes can run here.
                    // Transaction stays active even after command object is closed and can be attached to other objects.

                    //Create another command object and insert in temptable2 using same transaction.
                    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                    {
                        cmd.Transaction = trans; // attach this Command object to transaction.

                        for (int i = 0; i < 10; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable2 VALUES (" + i + ", 'AValue" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                    }

                    trans.Commit();
                }
                catch (SqlDatabaseException sqlex)
                {
                    trans.Rollback();
                    Console.WriteLine(sqlex.Message);
                }

                // Let's check the record count.
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
                    Console.WriteLine("Record Count temptable : {0}", cmd.ExecuteScalar());
                    cmd.CommandText = "SELECT COUNT(*) FROM temptable2;";
                    Console.WriteLine("Record Count temptable2 : {0}", cmd.ExecuteScalar());
                }
            }
        }
        static void SavePoint()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();
                SqlDatabaseTransaction trans = cnn.BeginTransaction();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = "CREATE TABLE SavePointExample (id Integer); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO SavePointExample VALUES (1); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SAVEPOINT a; ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO SavePointExample VALUES (2); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SAVEPOINT b; ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO SavePointExample VALUES (3); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SAVEPOINT c; ";
                    cmd.ExecuteNonQuery();

                    //should return 1, 2, 3 since no rollback or released has occured.
                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    SqlDatabaseDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine("");
                    }

                    //rollback save point to b without committing transaction. The value 3 and savepoint c will be gone.
                    cmd.CommandText = "ROLLBACK TO b"; //b
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine(""); // line break.
                    }

                    //if we uncomment and release c it wil produce logical error as savepoint c does not exists due to rollback to b.
                    //cmd.CommandText = "RELEASE c"; //c
                    //cmd.ExecuteNonQuery();

                    cmd.CommandText = "RELEASE b;"; //release b means commit the deffered transaction.
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine(""); // line break.
                    }


                    //We can still rollback entire transaction
                    //trans.Rollback();

                    //commit an entire transaction
                    trans.Commit();

                    //If we rollback transaction above regardless of release savepoint (i.e. saving)
                    //following will produce an error that SavePointExample table not found.
                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine(""); // line break.
                    }
                }
            }
        }
示例#5
0
        private void ParallelInsertFile_Click(object sender, EventArgs e)
        {
            DialogResult dlgresult = MessageBox.Show("This example will copy file names from MyDocuments folder, continue ?", "Important Question", MessageBoxButtons.YesNo);

            if (dlgresult.ToString() == "No")
            {
                return;
            }

            string[] files = System.IO.Directory.GetFiles(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "*.*");

            string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "files.db");

            try
            {
                if (File.Exists(dbfilepath))
                {
                    File.Delete(dbfilepath);
                }
            } catch (IOException ioe)
            {
                MessageBox.Show(ioe.Message);
                return;
            }


            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + dbfilepath + ";"))
            {
                cnn.Open();

                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "CREATE TABLE FileNames (Id Integer primary key autoincrement, InsertDateTime Text, ThreadId Integer, FileName Text); ";
                    cmd.ExecuteNonQuery();
                }
                SqlDatabaseTransaction trans = cnn.BeginTransaction();

                try
                {
                    Parallel.ForEach(files, (currentFile) =>
                    {
                        using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                        {
                            cmd.Transaction = trans;
                            cmd.CommandText = "INSERT INTO FileNames VALUES (null, GetDate(), @ThreadId, @FileName); ";
                            cmd.Parameters.Add("@ThreadId", Thread.CurrentThread.ManagedThreadId);
                            cmd.Parameters.Add("@FileName", currentFile);
                            cmd.ExecuteNonQuery();
                        }
                    });
                }
                catch
                {
                    trans.Rollback();
                }

                trans.Commit();
                //Now try reading first 100 rows by using LIMIT 100..
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT * FROM FileNames LIMIT 100; ";
                    SqlDatabaseDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Debug.Write(dr.GetValue(c) + "\t");
                        }
                        Debug.WriteLine("");
                    }
                }
            }

            //Delete the database file since we don't need it.
            try
            {
                if (File.Exists(dbfilepath))
                {
                    File.Delete(dbfilepath);
                }
            }
            catch (IOException ioe)
            {
                MessageBox.Show(ioe.Message);
                return;
            }
        }