C# DataContext.Execute方法代码示例

        public void DataContext_Should_Execute_And_Commit_Some_SQL_Commands_Without_Transaction()
        {
            using (var context = new DataContext(Transaction.No))
            {
                context.Execute("DELETE FROM Airplane");

                // After the first Execute, simulates the connection state to "open".
                connectionMock.Setup(c => c.State).Returns(ConnectionState.Open);

                context.Execute("DELETE FROM Tools");
            }

            connectionMock.Verify(c => c.CreateCommand(), Times.Exactly(2));
            connectionMock.Verify(c => c.Open(), Times.Once());
            connectionMock.Verify(c => c.BeginTransaction(), Times.Never());
            connectionMock.Verify(c => c.Close(), Times.Once());
            commandMock.Verify(c => c.ExecuteNonQuery(), Times.Exactly(2));
            commandMock.VerifySet(c => c.CommandText = "DELETE FROM Airplane");
            commandMock.VerifySet(c => c.CommandText = "DELETE FROM Tools");
            commandMock.VerifySet(c => c.Connection = connectionMock.Object);
            commandMock.VerifySet(c => c.Transaction = transactionMock.Object, Times.Never());
            commandMock.Verify(c => c.CreateParameter(), Times.Never());
            transactionMock.Verify(t => t.Commit(), Times.Never());
            providerMock.Object.DbTransaction.Should().BeNull();
        }

        private void CreateTables()
        {
            var createManufacturerTable = new StringBuilder("CREATE TABLE Le_Manufacturer (")
                .Append("[TheId] NUMERIC(8) NOT NULL IDENTITY PRIMARY KEY,")
                .Append("[Name] VARCHAR(32) NOT NULL,")
                .Append("[BuildYear] NUMERIC(4) NOT NULL)")
                .ToString();

            var createCarTable = new StringBuilder("CREATE TABLE Car (")
                .Append("[Id] NUMERIC(8) NOT NULL IDENTITY PRIMARY KEY,")
                .Append("[Name] VARCHAR(32) NOT NULL,")
                .Append("[ModelYear] NUMERIC(4) NOT NULL,")
                .Append("[Date] DATETIME NOT NULL,")
                .Append("[Chassis] VARCHAR(32) NOT NULL,")
                .Append("[Mileage] FLOAT,")
                .Append("[ManufacturerId] NUMERIC(8) REFERENCES Le_Manufacturer(TheId))")
                .ToString();

            using (var data = new DataContext())
            {
                data.Execute(createManufacturerTable);
                data.Execute(createCarTable);
                data.Commit();
            }
        }

 private void CreateDatabase()
 {
     using (var data = new DataContext("Master", Transaction.No))
     {
         data.Execute("CREATE DATABASE ThunderTest");
     }
 }

 private void DropDatabase()
 {
     using (var data = new DataContext("Master", Transaction.No))
     {
         data.Execute("DROP DATABASE ThunderTest");
     }
 }

        public void DataObjectQuery_Should_Select_All_Items_From_Database_With_Parameter_Binding_And_Context_Transaction()
        {
            commandMock.Setup(c => c.CommandText).Returns("SELECT [Id], [Name], [FirstFlight] FROM Airplane WHERE Name = @Name");

            using (var context = new DataContext())
            {
                // Execute a command to open transaction.
                context.Execute("DELETE FROM Airplane");

                // Simulate connection open state later the first execute.
                connectionMock.Setup(c => c.State).Returns(ConnectionState.Open);

                select.With(context).All("WHERE Name = @Name", new { Name = "Omega" });

                context.Commit();
            }

            connectionMock.Verify(c => c.CreateCommand(), Times.Exactly(2));
            connectionMock.Verify(c => c.Open(), Times.Once());
            connectionMock.Verify(c => c.BeginTransaction(), Times.Once());
            connectionMock.Verify(c => c.Close(), Times.Once());
            commandMock.Verify(c => c.ExecuteReader(), Times.Once());
            commandMock.VerifySet(c => c.CommandText = "SELECT [Id], [Name], [FirstFlight] FROM Airplane WHERE Name = @Name");
            commandMock.VerifySet(c => c.Connection = connectionMock.Object);
            commandMock.VerifySet(c => c.Transaction = transactionMock.Object);
            commandMock.Verify(c => c.CreateParameter(), Times.Once());
            parameterMock.VerifySet(p => p.ParameterName = "@Name");
            parameterMock.VerifySet(p => p.Value = "Omega");
            parameterCollectionMock.Verify(p => p.Add(parameterMock.Object), Times.Once());
            transactionMock.Verify(t => t.Commit(), Times.Once());
        }

        public void B_DataContext_Transitional_Should_Execute_A_Insert_SQL_Command_With_Array_Parameters_And_Read_Created_Data_In_Same_And_Count_Data_In_Another_Context()
        {
            using (var context = new DataContext())
            {
                context.Execute("INSERT INTO Le_Manufacturer VALUES (@0, @1)", "McLaren", 1963);
                var manufacturer = context.First<Manufacturer>("SELECT TOP 1 * FROM Le_Manufacturer WHERE Name = @0", "McLaren");
                context.Commit();

                manufacturer.TheId.Should().BeGreaterThan(0);
                manufacturer.Name.Should().Be("McLaren");
                manufacturer.BuildYear.Should().Be(1963);
            }

            using (var context = new DataContext())
            {
                var manufacturerCount = context.GetValue<int>("SELECT COUNT(TheId) FROM Le_Manufacturer");

                manufacturerCount.Should().Be(2);
            }
        }

        public void DataContext_Should_Execute_And_Commit_A_SQL_Command_With_Transaction()
        {
            using (var context = new DataContext())
            {
                context.Execute("DELETE FROM Airplane");
                context.Commit();
            }

            connectionMock.Verify(c => c.CreateCommand(), Times.Once());
            connectionMock.Verify(c => c.Open(), Times.Once());
            connectionMock.Verify(c => c.BeginTransaction(), Times.Once());
            connectionMock.Verify(c => c.Close(), Times.Once());
            commandMock.Verify(c => c.ExecuteNonQuery(), Times.Once());
            commandMock.VerifySet(c => c.CommandText = "DELETE FROM Airplane");
            commandMock.VerifySet(c => c.Connection = connectionMock.Object);
            commandMock.VerifySet(c => c.Transaction = transactionMock.Object);
            commandMock.Verify(c => c.CreateParameter(), Times.Never());
            transactionMock.Verify(t => t.Commit(), Times.Once());
            providerMock.Object.DbTransaction.Should().NotBeNull();
        }

        public void L_DataContext_Should_Throws_An_Exeption_When_A_Constraint_Was_Broken()
        {
            using (var context = new DataContext())
            {
                var insertParameters = new Car
                {
                    Name = "AGL 1113",
                    ModelYear = 1964,
                    Mileage = 1567922,
                    ManufacturerId = 98765, // Invalid ManufacturerId
                    Category = CarCategory.Transport
                };

                context.Execute("INSERT INTO Car VALUES (@Name, @ModelYear, @CreatedAt, @Chassis, @Mileage, @Category, @ManufacturerId)", insertParameters);
            }
        }

        public void DataContext_Should_Execute_Command_With_Length_One_Array_Parameters()
        {
            commandMock.Setup(c => c.CommandText).Returns("INSERT INTO Airplane VALUES (@0)");

            using (var context = new DataContext())
            {
                context.Execute("INSERT INTO Airplane VALUES (@0)", "Fokker Dr.I");
            }

            commandMock.Verify(c => c.ExecuteNonQuery(), Times.Once());
            commandMock.Verify(c => c.CreateParameter(), Times.Once());
            parameterMock.VerifySet(p => p.ParameterName = "0");
            parameterMock.VerifySet(p => p.Value = "Fokker Dr.I");
            parameterCollectionMock.Verify(p => p.Add(parameterMock.Object), Times.Once());
        }

        public void H_DataContext_Reader_Should_Translate_DBNull_To_Null_Value()
        {
            using (var context = new DataContext())
            {
                context.Execute("UPDATE Car SET Name = NULL WHERE Name = 'Esprit Turbo'");
                var car = context.First<Car>("SELECT * FROM Car WHERE Name IS NULL");

                car.Name.Should().BeNull();
            }
        }

        public void F_DataContext_Should_Read_Data_With_One_Field_And_Insert_A_Data_With_Some_Parameters_Types()
        {
            using (var context = new DataContext())
            {
                var selectParameters = new { Name = "Lotus" };
                var manufacturer = context.First<Manufacturer>("SELECT TheId FROM Le_Manufacturer WHERE Name = @Name", selectParameters);

                var insertParameters = new Car
                {
                    Name = "Esprit Turbo",
                    ModelYear = 1981,
                    Mileage = 318.19850801,
                    ManufacturerId = manufacturer.TheId,
                    Category = CarCategory.Sport
                };

                context.Execute("INSERT INTO Car VALUES (@Name, @ModelYear, @CreatedAt, @Chassis, @Mileage, @Category, @ManufacturerId)", insertParameters);
                var car = context.First<Car>("SELECT TOP 1 * FROM Car");
                context.Commit();

                car.Id.Should().BeGreaterThan(0);
                car.Name.Should().Be("Esprit Turbo");
                car.ModelYear.Should().Be(1981);
                car.Mileage.Should().Be(318.19850801);
                car.ManufacturerId.Should().Be(manufacturer.TheId);
                car.CreatedAt.Date.Should().Be(DateTime.Today.Date);
                car.Category.Should().Be(CarCategory.Sport);
            }
        }

        public void E_DataContext_Transitional_Should_Execute_A_SQL_And_Share_Transaction_With_A_Data_Reader()
        {
            using (var context = new DataContext())
            {
                var insertParameters = new { Name = "BMW", Year = 1916 };
                context.Execute("INSERT INTO Le_Manufacturer VALUES (@Name, @Year)", insertParameters);
                var selectParameters = new { Name = "BMW" };
                var manufacturer = context.First<Manufacturer>("SELECT * FROM Le_Manufacturer WHERE Name = @Name", selectParameters);

                manufacturer.Should().NotBeNull();
            }

            using (var context = new DataContext())
            {
                var selectParameters = new { Name = "BMW" };
                var manufacturer = context.First<Manufacturer>("SELECT * FROM Le_Manufacturer WHERE Name = @Name", selectParameters);

                manufacturer.Should().BeNull();
            }
        }

        public void D_DataContext_Transitional_Should_Not_Commit_Data_If_Not_Explicit_Commit_Command_Call()
        {
            using (var context = new DataContext())
            {
                var insertParameters = new { Name = "Bentley", Year = 1919 };
                context.Execute("INSERT INTO Le_Manufacturer VALUES (@Name, @Year)", insertParameters);
            }

            using (var context = new DataContext())
            {
                var manufacturers = context.All<Manufacturer>("SELECT * FROM Le_Manufacturer");

                manufacturers.Count().Should().Be(3);
                manufacturers.Should().Contain(m => m.Name == "Lotus");
                manufacturers.Should().Contain(m => m.Name == "General Motors");
            }
        }

 public void I_DataContext_Should_Throws_An_Exception_When_Execute_An_Incorrect_Command()
 {
     using (var context = new DataContext())
     {
         context.Execute("DELETE FROM Blargh");
     }
 }

        public void DataContext_Should_Execute_Command_With_Anonymous_Object_Parameters()
        {
            using (var context = new DataContext())
            {
                var airplane = new { Name = "Fokker Dr.I", FirstFlight = (int?) null };
                var command = "INSERT INTO Airplane VALUES (@Name, @FirstFlight)";
                context.Execute(command, airplane);
            }

            commandMock.Verify(c => c.ExecuteNonQuery(), Times.Once());
            commandMock.Verify(c => c.CreateParameter(), Times.Exactly(2));
            parameterMock.VerifySet(p => p.ParameterName = "Name");
            parameterMock.VerifySet(p => p.ParameterName = "FirstFlight");
            parameterMock.VerifySet(p => p.Value = "Fokker Dr.I");
            parameterMock.VerifySet(p => p.Value = DBNull.Value);
            parameterCollectionMock.Verify(p => p.Add(parameterMock.Object), Times.Exactly(2));
        }