C# SqlCeConnection.Execute方法代码示例

        public void MultiRSSqlCE()
        {
            if (File.Exists("Test.sdf"))
                File.Delete("Test.sdf");

            var cnnStr = "Data Source = Test.sdf;";
            var engine = new SqlCeEngine(cnnStr);
            engine.CreateDatabase();

            using (var cnn = new SqlCeConnection(cnnStr))
            {
                cnn.Open();

                cnn.Execute("create table Posts (ID int, Title nvarchar(50), Body nvarchar(50), AuthorID int)");
                cnn.Execute("create table Authors (ID int, Name nvarchar(50))");

                cnn.Execute("insert Posts values (1,'title','body',1)");
                cnn.Execute("insert Posts values(2,'title2','body2',null)");
                cnn.Execute("insert Authors values(1,'sam')");

                var data = cnn.Query<PostCE, AuthorCE, PostCE>(@"select * from Posts p left join Authors a on a.ID = p.AuthorID", (post, author) => { post.Author = author; return post; }).ToList();
                var firstPost = data.First();
                firstPost.Title.IsEqualTo("title");
                firstPost.Author.Name.IsEqualTo("sam");
                data[1].Author.IsNull();
                cnn.Close();
            }
        }

        public void Append(IEnumerable<LineCoverage> coverage)
        {
            string[] testMethods = coverage.Select(x => x.TestPath).Distinct().ToArray();

            if (testMethods.Length == 0)
                return;

            using (var connection = new SqlCeConnection(GetConnectionString()))
            {
                connection.Open();

                string delete;

                if (testMethods.Length == 1)
                {
                    delete = "DELETE FROM Coverage where TestPath = @testMethod";
                    connection.Execute(delete, new { testMethod = testMethods[0] });
                }
                else
                {
                    delete = "DELETE FROM Coverage where TestPath in @testMethods";
                    connection.Execute(delete, new { testMethods });
                }

                InsertLineCoverage(connection, coverage.ToArray());
            }
        }

        public HttpResponseMessage Put(int matchId, int winningracerid)
        {
            using (var connection = new SqlCeConnection(Settings.Cnn))
            {
                connection.Open();

                if (winningracerid < 1)
                {
                    connection.Execute("update match set winningracerid=null, modified=null where [email protected]", new { matchId });
                    return new HttpResponseMessage(HttpStatusCode.OK);
                }

                connection.Execute("update match set [email protected], modified=GETDATE() where [email protected]", new { matchId, winningracerid });

                Match match = connection.Query<Match>("select * from match where [email protected]", new {matchId}).First();

                var sql = match.NextWinningMatchSlot == 1
                    ? "update match set [email protected] where [email protected]"
                    : "update match set [email protected] where [email protected]";

                connection.Execute(sql, new { match.NextWinningMatchId, winningracerid });

                return new HttpResponseMessage(HttpStatusCode.OK);
            }
        }

        public bool AssignBracketsByStartSlot()
        {
            using (var connection = new SqlCeConnection(Settings.Cnn))
            {
                connection.Open();

                connection.Execute("update match set racer1id=null, racer2id=null, winningracerid=null");

                var racers = connection.Query<Racer>("select * from racer order by startslot").ToList();

                var matches1 = connection.Query<Match>("select * from match where roundid=1 order by matchid").ToList();

                var racerIndex = 0;

                foreach (var match in matches1)
                {
                    if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 1))
                    {
                        var racer1Id = racers[racerIndex].RacerId;
                        match.Racer1Id = racer1Id;
                        connection.Execute("update match set [email protected] where [email protected]", new { racer1Id, match.MatchId });
                        racerIndex++;
                    }

                    if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 2))
                    {
                        var racer2Id = racers[racerIndex].RacerId;
                        match.Racer2Id = racer2Id;
                        connection.Execute("update match set [email protected] where [email protected]", new { racer2Id, match.MatchId });
                        racerIndex++;
                    }
                }

                var matches2 = connection.Query<Match>("select * from match where roundid=2 order by matchid").ToList();

                foreach (var match in matches2)
                {
                    if (!matches1.Any(m => m.NextWinningMatchId == match.MatchId && m.NextWinningMatchSlot == 1))
                    {
                        var racer1Id = racers[racerIndex].RacerId;
                        match.Racer1Id = racer1Id;
                        connection.Execute("update match set [email protected] where [email protected]", new { racer1Id, match.MatchId });
                        racerIndex++;
                    }
                }
            }

            return true;
        }

        public bool AssignRandomStartSlots()
        {
            using (var connection = new SqlCeConnection(Settings.Cnn))
            {
                connection.Open();

                connection.Execute("update racer set startslot=-1");

                var numbers = Enumerable.Range(1, 100).ToList();

                var racers = connection.Query<Racer>("select * from racer order by racerid").ToList();

                var rnd = new Random();

                foreach (var racer in racers)
                {
                    var x = rnd.Next(0, numbers.Count);
                    racer.StartSlot = numbers[x];
                    numbers.RemoveAt(x);
                    connection.Execute("update racer set [email protected] where [email protected]", new { racer.StartSlot, racer.RacerId });
                }
            }

            return true;
        }

        private static void Setup()
        {
            var projLoc = Assembly.GetAssembly(typeof(Program)).Location;
            var projFolder = Path.GetDirectoryName(projLoc);

            if (File.Exists(projFolder + "\\Test.sdf"))
                File.Delete(projFolder + "\\Test.sdf");
            var connectionString = "Data Source = " + projFolder + "\\Test.sdf;";
            var engine = new SqlCeEngine(connectionString);
            engine.CreateDatabase();
            using (var connection = new SqlCeConnection(connectionString))
            {
                connection.Open();
                connection.Execute(@" create table Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null) ");
                connection.Execute(@" create table Automobiles (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) ");
            }
            Console.WriteLine("Created database");
        }

        static void Main(string[] args)
        {
            var oldFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoSh.sdf");
            var newFile = System.IO.Path.Combine(Environment.CurrentDirectory, "PoShNew.sdf");
            using(var oldConn = new SqlCeConnection("Data Source=" + oldFile))
            using(var newConn = new SqlCeConnection("Data Source=" + newFile))
            {
                oldConn.Open();
                newConn.Open();

                newConn.Execute("delete from timecard");
                newConn.Execute("delete from bartender");

                var countBefore = newConn.Query<int>("select count(id) from bartender").Single();

                var bts = oldConn.Query("select * from bartender");
                newConn.Execute("insert into bartender(id, name, hire, fire, pin) values(@Id,@Name,@Hire,@Fire,@Pin)", bts);

                var countAfter = newConn.Query<int>("select count(id) from bartender").Single();

                bool worked = countAfter > countBefore;

                var last = newConn.Query<DateTime>("select max(ClockOut) from timecard").Single();

                var newTimecards = oldConn.Query<Timecard>("Select * from timecard where ClockOut > @LastClockOut", new
                    {
                        LastClockOut = last
                    }
                );

                newConn.Execute("insert into timecard(Id, BartenderId, ClockIn, ClockOut) values (@Id, @BartenderId, @ClockIn, @ClockOut)", newTimecards);

                var result = newConn.Query("Select * from timecard where ClockOut > @LastClockOut", new
                {
                    LastClockOut = last
                }
                );
                foreach(var item in result)
                    Console.WriteLine("{0} {1} {2}", item.BartenderId, item.ClockIn, item.ClockOut);
            }
            Console.Read();
        }

        public void AppendByDocumentPath(string documentPath, IEnumerable<LineCoverage> coverage)
        {
            using (var connection = new SqlCeConnection(GetConnectionString()))
            {
                connection.Open();

                const string delete =
                "DELETE FROM Coverage where [email protected] or [email protected]";

                connection.Execute(delete, new { documentPath });
                InsertLineCoverage(connection, coverage.ToArray());
            }
        }

        public void RunBeforeAnyTests()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["__DefaultSqlCe"].ConnectionString;
            string[] connectionParts = connectionString.Split(';');
            string file = connectionParts
                .ToDictionary(k => k.Split('=')[0], v => v.Split('=')[1])
                .Where(d => d.Key.Equals("Data Source", StringComparison.OrdinalIgnoreCase))
                .Select(k => k.Value).Single();

            if (File.Exists(file))
                File.Delete(file);

            using (SqlCeEngine ce = new SqlCeEngine(connectionString))
            {
                ce.CreateDatabase();
            }

            Container = new Castle.Windsor.WindsorContainer();

            DapperConfiguration
                .Use()
                .UseClassMapper(typeof(AutoClassMapper<>))
                .UseContainer<ContainerForWindsor>(cfg => cfg.UseExisting(Container))
                .UseSqlDialect(new SqlCeDialect())
                .WithDefaultConnectionStringNamed("__DefaultSqlCe")
                .FromAssembly("Dapper.Extensions.Linq.Test.Entities")
                .FromAssembly("Dapper.Extensions.Linq.Test.Maps")
                .Build();

            _connection = new SqlCeConnection(connectionString);
            var files = new List<string>
            {
                ReadScriptFile("CreateAnimalTable"),
                ReadScriptFile("CreateFooTable"),
                ReadScriptFile("CreateMultikeyTable"),
                ReadScriptFile("CreatePersonTable"),
                ReadScriptFile("CreateCarTable"),
                ReadScriptFile("CreatePhoneTable")
            };

            foreach (var setupFile in files)
            {
                _connection.Execute(setupFile);
            }
        }

        public void BeforeDatabaseIntegration()
        {
            if (File.Exists("TestDB.sdf"))
                File.Delete("TestDB.sdf");

            var engine = new SqlCeEngine(TestDBConnectionString);
            engine.CreateDatabase();

            using (var connection = new SqlCeConnection(TestDBConnectionString))
            {
                connection.Execute(@"
                   create table Posts (
                      Id INT Primary Key Identity(1,1),
                      CreateDate DATETIME NOT NULL,
                      Author nvarchar(100),
                      Body nvarchar(4000)
                    )");
            }
        }

        public virtual void Setup()
        {
            string connectionString = string.Format("Data Source=.\\dapperTest_{0}.sdf", Guid.NewGuid());
            string[] connectionParts = connectionString.Split(';');
            string file = connectionParts
                .ToDictionary(k => k.Split('=')[0], v => v.Split('=')[1])
                .Where(d => d.Key.Equals("Data Source", StringComparison.OrdinalIgnoreCase))
                .Select(k => k.Value).Single();

            if (File.Exists(file))
            {
                File.Delete(file);
            }

            using (SqlCeEngine ce = new SqlCeEngine(connectionString))
            {
                ce.CreateDatabase();
            }

            SqlCeConnection connection = new SqlCeConnection(connectionString);
            var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqlCeDialect());
            var sqlGenerator = new SqlGeneratorImpl(config);
            Db = new Database(connection, sqlGenerator);

            var files = new List<string>
                                {
                                    ReadScriptFile("CreateAnimalTable"),
                                    ReadScriptFile("CreateFooTable"),
                                    ReadScriptFile("CreateMultikeyTable"),
                                    ReadScriptFile("CreatePersonTable"),
                                    ReadScriptFile("CreateCarTable")
                                };

            foreach (var setupFile in files)
            {
                connection.Execute(setupFile);
            }
        }

 static SqlCETestSuite()
 {
     if (File.Exists(FileName))
     {
         File.Delete(FileName);
     }
     var engine = new SqlCeEngine(ConnectionString);
     engine.CreateDatabase();
     using (var connection = new SqlCeConnection(ConnectionString))
     {
         connection.Open();
         connection.Execute(@"CREATE TABLE Stuff (TheId int IDENTITY(1,1) not null, Name nvarchar(100) not null, Created DateTime null) ");
         connection.Execute(@"CREATE TABLE People (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) ");
         connection.Execute(@"CREATE TABLE Users (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, Age int not null) ");
         connection.Execute(@"CREATE TABLE Automobiles (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null) ");
         connection.Execute(@"CREATE TABLE Results (Id int IDENTITY(1,1) not null, Name nvarchar(100) not null, [Order] int not null) ");
         connection.Execute(@"CREATE TABLE ObjectX (ObjectXId nvarchar(100) not null, Name nvarchar(100) not null) ");
         connection.Execute(@"CREATE TABLE ObjectY (ObjectYId int not null, Name nvarchar(100) not null) ");
         connection.Execute(@"CREATE TABLE ObjectZ (Id int not null, Name nvarchar(100) not null) ");
     }
     Console.WriteLine("Created database");
 }

        public async Task<HttpResponseMessage> Post(JObject value)
        {
            dynamic data = value;
            IEnumerable<int> result;
            using (var connection = new SqlCeConnection(_connString))
            {
                await connection.OpenAsync();

                connection.Execute(
                    "insert into Tasks (Title, Description, CreatedDate) values (@title, @description, @createdDate);",
                    new
                    {
                        title = (string)data.title,
                        description = (string)data.description,
                        createdDate = DateTime.Parse((string)data.createdDate)
                    }
                );

                result = await connection.QueryAsync<int>("select max(Id) as id from Tasks;");
            }

            int id = result.First();
            data.id = id;
            var response = Request.CreateResponse(HttpStatusCode.Created, (JObject)data);
            response.Headers.Location = new Uri(Url.Link("DefaultApi", new { controller = "Tasks", id = id }));
            return response;
        }

        public bool AssignSequentialStartSlots()
        {
            using (var connection = new SqlCeConnection(Settings.Cnn))
            {
                connection.Open();
                connection.Execute("update racer set startslot=racerId");
            }

            return true;
        }

        public void Create(string databasename)
        {
            _log.Debug(string.Format("Creating database: {0}", databasename));

            if (_fileSystemHelper.FileExists(databasename))
            {
                _log.Debug("Deleting current database");
                _fileSystemHelper.DeleteFile(databasename);
            }

            var connectionString = string.Format("Data Source = {0};", databasename);

            using (var engine = new SqlCeEngine(connectionString))
            {
                _log.Debug(string.Format("Creating new database with connectionstring: {0}", connectionString));

                engine.CreateDatabase();

                _log.Debug("Database was created");
            }

            using (IDbConnection connection = new SqlCeConnection(connectionString))
            {
                connection.Open();

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaErrorLogsTable);
                _log.Debug(string.Format("Created table: {0}", "ErrorLogs"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerVariablesTable);
                _log.Debug(string.Format("Created table: {0}", "ServerVariables"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaCookieValuesTable);
                _log.Debug(string.Format("Created table: {0}", "CookieValues"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaFormValuesTable);
                _log.Debug(string.Format("Created table: {0}", "FormValues"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaQuerystringValuesTable);
                _log.Debug(string.Format("Created table: {0}", "QuerystringValues"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaClientInformationTable);
                _log.Debug(string.Format("Created table: {0}", "ClientInformation"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerInformationTable);
                _log.Debug(string.Format("Created table: {0}", "ServerInformation"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerVariablesForeignKeys);
                _log.Debug(string.Format("Created foreign keys for: {0}", "ServerVariables"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaFormValuesForeignKeys);
                _log.Debug(string.Format("Created foreign keys for: {0}", "FormValues"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaCookieValuesForeignKeys);
                _log.Debug(string.Format("Created foreign keys for: {0}", "CookieValues"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaQuerystringValuesForeignKeys);
                _log.Debug(string.Format("Created foreign keys for: {0}", "QuerystringValues"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaClientInformationForeignKeys);
                _log.Debug(string.Format("Created foreign keys for: {0}", "ClientInformation"));

                connection.Execute(DatabaseScripts.SqlCeDatabaseSchemaServerInformationForeignKeys);
                _log.Debug(string.Format("Created foreign keys for: {0}", "ServerInformation"));
            }
        }