C# SqliteConnection.Close方法代码示例

        public void Close_can_be_called_before_open()
        {
            var connection = new SqliteConnection();

            connection.Close();
        }

        public void Close_can_be_called_more_than_once()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();

                connection.Close();
                connection.Close();
            }
        }

            private void MigrateDb0_9(IUnitOfWork uow)
            {
                var db = new SqliteConnection("Data Source=data/nadekobot.sqlite");

                if (!File.Exists("data/nadekobot.sqlite"))
                {
                    _log.Warn("No data from the old database will be migrated.");
                    return;
                }
                db.Open();

                var com = db.CreateCommand();
                com.CommandText = "SELECT * FROM Announcement";

                var reader = com.ExecuteReader();
                var i = 0;
                while (reader.Read())
                {
                    var gid = (ulong)(long)reader["ServerId"];
                    var greet = (long)reader["Greet"] == 1;
                    var greetDM = (long)reader["GreetPM"] == 1;
                    var greetChannel = (ulong)(long)reader["GreetChannelId"];
                    var greetMsg = (string)reader["GreetText"];
                    var bye = (long)reader["Bye"] == 1;
                    var byeDM = (long)reader["ByePM"] == 1;
                    var byeChannel = (ulong)(long)reader["ByeChannelId"];
                    var byeMsg = (string)reader["ByeText"];
                    var grdel = false;
                    var byedel = grdel;
                    var gc = uow.GuildConfigs.For(gid);

                    if (greetDM)
                        gc.SendDmGreetMessage = greet;
                    else
                        gc.SendChannelGreetMessage = greet;
                    gc.GreetMessageChannelId = greetChannel;
                    gc.ChannelGreetMessageText = greetMsg;

                    gc.SendChannelByeMessage = bye;
                    gc.ByeMessageChannelId = byeChannel;
                    gc.ChannelByeMessageText = byeMsg;

                    gc.AutoDeleteGreetMessagesTimer = gc.AutoDeleteByeMessagesTimer = grdel ? 30 : 0;
                    _log.Info(++i);
                }

                var com2 = db.CreateCommand();
                com.CommandText = "SELECT * FROM CurrencyState GROUP BY UserId";

                i = 0;
                var reader2 = com.ExecuteReader();
                while (reader2.Read())
                {
                    _log.Info(++i);
                    var curr = new Currency()
                    {
                        Amount = (long)reader2["Value"],
                        UserId = (ulong)(long)reader2["UserId"]
                    };
                    uow.Currency.Add(curr);
                }
                db.Close();
                try { File.Move("data/nadekobot.sqlite", "data/DELETE_ME_nadekobot.sqlite"); } catch { }
            }

        public void Close_works()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();

                var raised = false;
                StateChangeEventHandler handler = (sender, e) =>
                    {
                        raised = true;

                        Assert.Equal(connection, sender);
                        Assert.Equal(ConnectionState.Open, e.OriginalState);
                        Assert.Equal(ConnectionState.Closed, e.CurrentState);
                    };

                connection.StateChange += handler;
                try
                {
                    connection.Close();

                    Assert.True(raised);
                    Assert.Equal(ConnectionState.Closed, connection.State);
                }
                finally
                {
                    connection.StateChange -= handler;
                }
            }
        }

        public void Commit_throws_when_connection_closed()
        {
            using (var connection = new SqliteConnection("Data Source=:memory:"))
            {
                connection.Open();

                using (var transaction = connection.BeginTransaction())
                {
                    connection.Close();

                    var ex = Assert.Throws<InvalidOperationException>(() => transaction.Commit());

                    Assert.Equal(Strings.TransactionCompleted, ex.Message);
                }
            }
        }

        public bool UpdateTableField(
            string tableName,
            string keyFieldName,
            string keyFieldValue,
            string dataFieldName,
            string dataFieldValue,
            string additionalWhere)
        {
            bool result = false;

            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE " + tableName + " ");
            sqlCommand.Append(" SET " + dataFieldName + " = :fieldValue ");
            sqlCommand.Append(" WHERE " + keyFieldName + " = " + keyFieldValue);
            sqlCommand.Append(" " + additionalWhere + " ");
            sqlCommand.Append(" ; ");

            SqliteParameter[] arParams = new SqliteParameter[1];

            arParams[0] = new SqliteParameter(":fieldValue", DbType.String);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = dataFieldValue;

            SqliteConnection connection = new SqliteConnection(connectionString);
            connection.Open();
            try
            {
                int rowsAffected = AdoHelper.ExecuteNonQuery(connection, sqlCommand.ToString(), arParams);
                result = (rowsAffected > 0);
            }
            finally
            {
                connection.Close();
            }

            return result;

        }

        internal void Init()
        {
            try
            {
                using (SqliteConnection connection = new SqliteConnection(this.connectionString))
                {
                    connection.Open();

                    using (SqliteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT version From Version";

                        try
                        {
                            using (SqliteDataReader reader = command.ExecuteReader())
                            {
                                if (reader.Read())
                                {
                                    connection.Close();
                                    return;
                                }
                            }
                        }
                        catch (SqliteException)
                        {
                        }
                    }

                    // Here we have to create the database
                    using (SqliteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = @"CREATE TABLE Event(
                                            Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                                            Title text NOT NULL,
                                            Description text NULL,
                                            Modification text NOT NULL,
                                            Expiration text NOT NULL)";
                        command.ExecuteNonQuery();
                    }

                    using (SqliteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = @"CREATE TABLE Painting(
                                            Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                                            Title text NOT NULL,
                                            ThemeId integer NOT NULL,
                                            Description text NULL,
                                            Filename text NOT NULL,
                                            OnSlider integer NULL)";
                        command.ExecuteNonQuery();
                    }

                    using (SqliteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = @"CREATE TABLE Theme(
                                            Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                                            ParentId integer NULL,
                                            Title text NOT NULL,
                                            Description text NULL)";
                        command.ExecuteNonQuery();
                    }

                    using (SqliteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = @"CREATE TABLE User(
                                            Id integer NOT NULL PRIMARY KEY AUTOINCREMENT,
                                            Login text NOT NULL,
                                            Password text NOT NULL)";
                        command.ExecuteNonQuery();

                    }

                    using (SqliteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = @"CREATE TABLE Version(
                                            version integer)";
                        command.ExecuteNonQuery();

                    }

                    using (SqliteCommand command = connection.CreateCommand())
                    {
                        command.CommandText = @"INSERT INTO Version(version) VALUES(1)";
                        command.ExecuteNonQuery();

                        connection.Close();
                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
        }

//.........这里部分代码省略.........
            comm14.CommandText = @"SELECT ContactName,MAX(sum1) as Quantity FROM(
                                   SELECT ContactName, Sum(Quantity) AS sum1 From Orders
                                   INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID
                                   Inner JOIN 'Order Details' ON 'Order Details'.OrderID=Orders.OrderID
                                   GROUP BY ContactName);";
            reader = comm14.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("{0},{1}", reader.GetString(0), reader.GetString(1));
            }
            Console.WriteLine("------------------");

            Console.WriteLine("Select only these customers that ordered the absolutely the same products as customer “FAMIA”");
            Console.WriteLine("this returns the customer used as parameter as well, to remove this you can add AND CustomerID<>'FAMIA' at the end of query");
            Console.WriteLine("------------------");
            SqliteCommand comm15 = conn.CreateCommand();
            comm15.CommandText = @"SELECT CustomerID
            FROM Customers c1
            WHERE NOT EXISTS
            (
            SELECT  od1.ProductID
            FROM 'Order Details' od1
            Inner Join Orders ord1 ON od1.OrderID=ord1.OrderID
            WHERE ord1.CustomerID='FAMIA' AND od1.ProductID NOT IN (

            SELECT  ProductID
            FROM 'Order Details' od2
            Inner Join Orders ord2 ON od2.OrderID=ord2.OrderID
            Inner Join Customers c2 ON ord2.CustomerID=c2.CustomerID
            WHERE c2.CustomerID=c1.CustomerID
            )
            )
            AND NOT EXISTS(

            SELECT od3.ProductID
            FROM 'Order Details' od3
            Inner Join Orders ord3 ON od3.OrderID=ord3.OrderID
            Inner Join Customers c3 ON ord3.CustomerID=c3.CustomerID

            WHERE   c3.CustomerID=c1.CustomerID AND od3.ProductID NOT IN (
                            SELECT  od4.ProductID
                            FROM 'Order Details' od4
                            Inner Join Orders ord4 ON od4.OrderID=ord4.OrderID
                            WHERE ord4.CustomerID='FAMIA'
                               )
            )";
            reader = comm15.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine("{0}", reader.GetString(0));
            }
            Console.WriteLine("------------------");

            Console.WriteLine("Outputs from tasks 8 and 9 are very long, enter 8 or 9 to view (q to quit)");
            Console.WriteLine("Task 9 implements outer join (not available in sqlite) by union");
            string str;
            while ((str = Console.ReadLine()) != "q")
            {
                if (str.StartsWith("8"))
                {
                    Console.WriteLine("Select all employees and any orders they might have");
                    Console.WriteLine("------------------");
                    SqliteCommand comm8 = conn.CreateCommand();
                    comm8.CommandText = @"SELECT Employees.EmployeeID,Employees.LastName,Orders.OrderID
                                  FROM Employees
                                  LEFT JOIN Orders
                                  ON Employees.EmployeeID=Orders.EmployeeID
                                  ORDER BY LastName;";
                    reader = comm8.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine("{0},{1},{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                    }
                    Console.WriteLine("------------------");
                }
                else if (str.StartsWith("9"))
                {
                    Console.WriteLine("Selects all employees, and all orders");
                    Console.WriteLine("------------------");
                    SqliteCommand comm9 = conn.CreateCommand();
                    comm9.CommandText = @"SELECT Employees.EmployeeID,Employees.LastName,Orders.OrderID
                                  FROM   Employees
                                  LEFT JOIN Orders
                                  ON Employees.EmployeeID = Orders.EmployeeID
                                  UNION ALL
                                  SELECT Employees.EmployeeID,Employees.LastName,Orders.OrderID
                                  FROM   Orders
                                  LEFT JOIN Employees
                                  ON Employees.EmployeeID = Orders.EmployeeID
                                  WHERE  Employees.EmployeeID IS NULL";
                    reader = comm9.ExecuteReader();
                    while (reader.Read())
                    {
                        Console.WriteLine("{0},{1},{2}", reader.GetString(0), reader.GetString(1), reader.GetString(2));
                    }
                    Console.WriteLine("------------------");
                }
            }
            conn.Close();
        }

//.........这里部分代码省略.........
                comm3.CommandText = @"SELECT MAX(id),Title FROM companies";
                var reader = comm3.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("{0}|{1}", reader.GetInt32(0),
                        reader.GetString(1));
                }
                Console.WriteLine("------------------");

            //
                SqliteCommand comm4 = conn.CreateCommand();
                comm4.CommandText = @"
            UPDATE companies
            SET Country= @country1
            WHERE Country= @country2;
            ";
                var country1Param = new SqliteParameter();
                country1Param.ParameterName = "@country1";
                country1Param.Value = "USA";
                var country2Param = new SqliteParameter();
                country2Param.ParameterName = "@country2";
                country2Param.Value = "Ukraine";
                comm4.Parameters.Add(country1Param);
                comm4.Parameters.Add(country2Param);
                comm4.ExecuteNonQuery();
            //
                SqliteCommand comm5 = conn.CreateCommand();
                comm5.CommandText = @"
            DELETE FROM companies
            WHERE Country<>@country1;
            ";
            comm5.Parameters.Add(country1Param);
            comm5.ExecuteNonQuery();
            //

                SqliteCommand comm6 = conn.CreateCommand();
                comm6.CommandText = @"
            SELECT COUNT(*) FROM companies
            ";
                Console.WriteLine(comm6.ExecuteScalar());
                Console.WriteLine("------------------");

            //
                SqliteCommand comm7 = conn.CreateCommand();
                comm7.CommandText = @"SELECT * FROM companies";
                reader = comm7.ExecuteReader();
                while (reader.Read())
                {
                    Console.WriteLine("{0}|{1}|{2}|{3}", reader.GetInt32(0),
                        reader.GetString(1), reader.GetString(2), reader.GetString(3));
                }
                Console.WriteLine("------------------");

            //
                Console.WriteLine(@"input example:{'Title':'title','Country':'country','AddedDate':'20.10.2015'}");
                Console.WriteLine("------------------");
            List<Company> companiesToAdd = new List<Company>();
            string str;
                while ((str = Console.ReadLine()) != "q")
                {
                    if (str.StartsWith("{"))
                    {
                    Company obj = Newtonsoft.Json.JsonConvert.DeserializeObject<Company>(str);
                    companiesToAdd.Add(obj);
                    }
                }

            if (companiesToAdd.Count > 0)
            {
                SqliteTransaction st = conn.BeginTransaction();
                try
                {

                    SqliteCommand comm8 = conn.CreateCommand();
                    comm8.CommandText = @"INSERT INTO companies(Title,Country,AddedDate) VALUES
                           (@title, @country, @date);";
                    comm8.Parameters.Add(new SqliteParameter("@title", SqliteType.Text));
                    comm8.Parameters.Add(new SqliteParameter("@country", SqliteType.Text));
                    comm8.Parameters.Add(new SqliteParameter("@date", SqliteType.Text));

                    foreach (Company comp in companiesToAdd)
                    {
                        comm8.Parameters[0].Value = comp.Title;
                        comm8.Parameters[1].Value = comp.Country;
                        comm8.Parameters[2].Value = comp.AddedDate;
                        if (comm8.ExecuteNonQuery() != 1)
                        {
                            throw new InvalidProgramException();
                        }
                    }
                    st.Commit();
                }
                catch (Exception ex)
                {
                    st.Rollback();
                }
            }

            conn.Close();
        }