C# SQLiteConnection.Execute方法代码示例

 internal static void CreateSampleSchema(SQLiteConnection connection)
 {
     try { connection.Execute("DROP TABLE table1"); }
     catch { }
     try { connection.Execute("CREATE TABLE table1 (id integer primary key, name text)"); }
     catch { }
 }

 private void btnClear_Click(object sender, EventArgs e)
 {
     using (var conn = new SQLiteConnection(Common.ConnectionString))
     {
         //清理无效远程主机
         conn.Execute(
             "DELETE FROM RemoteHost WHERE FParentId>0 AND FConectId NOT IN (SELECT FId FROM ConnectLib)");
         //清理无效父级节点
         conn.Execute(
             "DELETE FROM RemoteHost WHERE FParentId=0 AND FId NOT IN (SELECT FParentId FROM RemoteHost)");
     }
     LoadHostConfig();
 }

        public void TestDapper()
        {
            var dataSource = @"test.sqlite";
            SQLiteConnection.CreateFile(dataSource);
            var builder = new SQLiteConnectionStringBuilder
            {
                DataSource = dataSource,
                LegacyFormat = false,
                Version = 3,
                SyncMode = SynchronizationModes.Off,
                JournalMode = SQLiteJournalModeEnum.Wal
            };

            using (var tran = new TransactionScope(TransactionScopeOption.Required))
            using (var connection = new SQLiteConnection(builder.ToString()))
            {
                connection.Open();
                CreateSampleSchema(connection);

                try
                {
                    connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 1, Name = "Name-1" });
                    connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 2, Name = "Name-2" });
                    connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 3, Name = "Name-3" });

                    dynamic dynamicObj = connection.Query("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();

                    Console.WriteLine("Id: {0}", dynamicObj.id);
                    Console.WriteLine("Name: {0}", dynamicObj.name);

                    var mappingObj = connection.Query<Table1>("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();

                    Console.WriteLine("Id: {0}", mappingObj.Id);
                    Console.WriteLine("Name: {0}", mappingObj.Name);

                    dynamic results = connection.Query("SELECT id, name FROM table1");
                    foreach (dynamic item in results)
                    {
                        Console.WriteLine(item);
                    }

                    tran.Complete();
                }
                catch
                {
                }
            }
        }

 public void AddProfile(EmployeeProfile commandDto, string imgURL, string cartoonURL)
 {
     string user = System.Web.HttpContext.Current.User.Identity.Name;
     using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
     {
         conn.Open();
         conn.Execute(
             "insert into EmployeeProfile (Department, FirstName, ImageUrl, LastName, Role, Tags, CreatedBy, CreatedOn, IsSuperUser, Extension, CartoonURL, WindowsIdentity, emailaddress, SeatingFloor, seatNo) values " +
             "(@Department, @FirstName, @ImageUrl, @LastName, @Role, @Tags, @CreatedBy, @CreatedOn, @IsSuperUser, @Extension, @CartoonURL, @WindowsIdentity, @emailAddress, @SeatingFloor, @seatNo)",
             new
                 {
                     Department = commandDto.Department,
                     FirstName = commandDto.FirstName.Replace("'","''"),
                     ImageUrl = imgURL,
                     LastName = commandDto.LastName.Replace("'", "''"),
                     Role = commandDto.Role,
                     Tags = !string.IsNullOrEmpty(commandDto.Tags) ? commandDto.Tags.Replace("'", "''"): "",
                     CreatedBy = user.Remove(0, 11),
                     CreatedOn = commandDto.CreatedOn,
                     IsSuperUser = false,
                     Extension = !string.IsNullOrEmpty(commandDto.Extension) ? commandDto.Extension.Replace("'", "''") : "",
                     CartoonURL = cartoonURL,
                     windowsIdentity = user,
                     emailAddress = commandDto.EmailAddress,
                     SeatingFloor = commandDto.SeatingFloor,
                     seatNo = commandDto.SeatNo
                 });
         conn.Close();
     }
 }

 private void ClearLogs()
 {
     using (var connection = new SQLiteConnection(ConnectionString))
     {
         connection.Execute("delete from Log");
     }
 }

        public virtual void Setup()
        {
            string connectionString = string.Format("Data Source=.\\dapperTest_{0}.sqlite", 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);
            }

            SQLiteConnection connection = new SQLiteConnection(connectionString);
            var config = new DapperExtensionsConfiguration(typeof(AutoClassMapper<>), new List<Assembly>(), new SqliteDialect());
            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);
            }
        }

 public ModuleConfigurations()
 {
     db = new SQLiteConnection("Data Source=:memory:;Version=3;New=True");
     //todo: create platy table
     db.Open();
     db.Execute("PRAGMA foreign_keys=ON");
 }

 public bool Execute(string connectionString, string sql)
 {
     using (var cnn = new SQLiteConnection(connectionString))
     {
         cnn.Open();
         return cnn.Execute(sql) > 0;
     }
 }

 public LitePostRepository() 
 {
     using (var conn = new SQLiteConnection(Connectionstring))
     {
         conn.Open();
         conn.Execute(@" create table IF NOT EXISTS Posts (Id INTEGER PRIMARY KEY, Title nvarchar(255), Content nvarchar(1000) not null) ");
     }
 }

 protected override DbConnection CreateConnectionCore()
 {
     var con = new SQLiteConnection(ConnectionStringBuilder.CreateWithFullUri(
            "file::memory:?cache=shared"));
     con.Open();
     con.Execute("PRAGMA case_sensitive_like=1");
     return con;
 }

        private void btnSave_Click(object sender, System.EventArgs e)
        {
            if (InfoIsError()) return;

            using (var conn = new SQLiteConnection(Common.ConnectionString))
            {
                if (IsModify)
                {
                    conn.Execute("UPDATE RemoteHost SET [email protected],[email protected],[email protected],[email protected] WHERE [email protected]",
                         new { name = txtName.Text, connectId = cbIpAddress.SelectedValue, parentId = chIsParent.Checked ? 0 : cbParent.SelectedValue, sort = numSort.Text, id = RemoteHost.FId });
                }
                else
                {
                    conn.Execute("INSERT INTO RemoteHost('FName','FConectId','FParentId','FSort') VALUES(@name,@connectId,@parentId,@sort)",
                        new { name = txtName.Text, connectId = cbIpAddress.SelectedValue, parentId = chIsParent.Checked ? 0 : cbParent.SelectedValue, sort = numSort.Text });
                }
            }
            DialogResult = DialogResult.OK;
        }

        public void CreateDb()
        {
            using (var connection = new SQLiteConnection(ConnectionString))
            {
                var query = @"CREATE TABLE Init (Id INT NOT NULL)";

                connection.Open();
                connection.Execute(query);
            }
        }

        public static void SaveEvent(EventInfo eventInfo)
        {
            using (var conn = new SQLiteConnection(_connectionString))
            {
                var query = @"  insert into EventInfo(WindowTitle, EventType)
                                values (@title, @type);";
                conn.Open();

                conn.Execute(query, new { title = eventInfo.WindowTitle, type = eventInfo.EventType }, null, null, null);
            }
        }

 public ModulesRepository(SQLiteConnection database)
 {
     this.db = database;
     lock(database) {
         if (db.State == System.Data.ConnectionState.Closed)
         {
             db.Open();
         }
     }
     db.Execute("PRAGMA foreign_keys=ON");
 }

        public void Set(IContent content)
        {
            string connString = ConfigurationManager.ConnectionStrings["WilliamsonFamilyConnectionString"].ConnectionString;
                    //var connection = new ProfiledDbConnection(new SQLiteConnection(connString), MiniProfiler.Current);
             using (var conn = new SQLiteConnection(connString))
             {
                 conn.Open();

                 var contentExists = conn.Query<Content>("SELECT * FROM Content WHERE Token = @token", new { @token = content.Token }, null, true, null, null).FirstOrDefault();
                 if (contentExists == null)
                     conn.Execute("INSERT INTO Content (Token, Value) VALUES (@Token, @Value)", new { @token = content.Token, @value = content.Value }, null, null, null);
                 else
                     conn.Execute(@"
            UPDATE Content
            SET Token = @Token,
            Value = @Value
            WHERE ContentID = @ContentID", new { @token = content.Token, @value = content.Value, @ContentID = content.ContentID }, null, null, null);

                 conn.Close();
             }
        }