C# SQLiteClient.SqliteConnection类代码示例

 public OurMediaItem(Community.CsharpSqlite.SQLiteClient.SqliteConnection conn, int id)
 {
     // TODO: Complete member initialization
     this.conn = conn;
     this.id = id;
     checked_to_patron_id = -1;
 }

        public void save(SqliteConnection conn)
        {
            string command;
            if (id == -1)
                command = "insert into mediaitem (name, type, checked_to_patron_id, " +
                          " checkout_date, due_date) values (@name, @type, @patron_id, @co_date, @due_date);";
            else
                command = "update mediaitem set [email protected], [email protected], [email protected]_id, " +
                          " [email protected]_date, [email protected]_date where [email protected]";
            SqliteCommand cmd = new SqliteCommand(command, conn);
            cmd.Parameters.Add("@id", id);
            cmd.Parameters.Add("@name", name);
            cmd.Parameters.Add("@type", type);
            if (checked_to_patron_id > -1)
                cmd.Parameters.Add("@patron_id", checked_to_patron_id);
            else
                cmd.Parameters.Add("@patron_id", null);
            if (checkout_date != null)
                cmd.Parameters.Add("@co_date", checkout_date.Value.ToString());
            else
                cmd.Parameters.Add("@co_date", null);
            if (due_date != null)
                cmd.Parameters.Add("@due_date", due_date);
            else
                cmd.Parameters.Add("@due_date", null);

            cmd.ExecuteNonQuery();
            //id = cmd.LastInsertRowID();
        }

 public void Update(SqliteConnection connection, int currentVersion)
 {
     foreach (var script in _scripts.Skip(currentVersion))
     {
         script.Apply(connection);
     }
 }

        /// <summary>
        /// Execute command on the database, commands are serialized
        /// </summary>
        /// <param name="database"></param>
        /// <param name="commandText"></param>
        /// <returns></returns>
        public static IEnumerable<SqliteDataReader> ExecuteCommand(string database, string commandText)
        {
            SqliteConnection conn;
            if (!_Connection.TryGetValue(database, out conn))
            {
                conn = new SqliteConnection(SqliteConnectionManager.GetConnectionString(database));
                conn.Open();
                _Connection.Add(database, conn);
            }

            lock (conn)
            {
                var command = conn.CreateCommand();
                command.CommandText = commandText;

                using (var result = command.ExecuteReader())
                {
                    while (result.Read())
                    {
                        yield return result;
                    }
                }

                command.Dispose();
            }
        }

        public CodeProjectDatabase()
        {
            dbPath = "items.db3";

            IsolatedStorageFile isf = IsolatedStorageFile.GetUserStoreForApplication();
            bool exists = isf.FileExists(dbPath);

            var connection = new SqliteConnection("data source=" + dbPath);
            connection.Open();
                
            if (!exists)
            {
                var commands = new[]{
					"CREATE TABLE [Member] (Key integer, Name ntext, ArticleCnt integer, BlogCnt integer, Reputation ntext, IsMe integer);"
				};
                foreach (var command in commands)
                {
                    using (var c = connection.CreateCommand())
                    {
                        c.CommandText = command;
                        c.ExecuteNonQuery();
                    }
                }
            }

        }

        protected override ICSDbConnection CreateConnection()
        {
            SqliteConnection conn = new SqliteConnection(ConnectionString);

            conn.Open();
            
            return new CSSqliteConnection(conn);
        }

 /// <summary>
 /// Create schema of sync source table
 /// supports transaction
 /// </summary>
 /// <param name="con"></param>
 public static void CreateSchema(SqliteConnection con)
 {
     using (SqliteCommand cmd = con.CreateCommand())
     {
         cmd.CommandText = "CREATE TABLE IF NOT EXISTS " + Configuration.TBL_DATASOURCE_INFO +
                          " ( " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " TEXT, " +
                          Configuration.COL_SOURCE_ID + " TEXT PRIMARY KEY)";
         cmd.ExecuteNonQuery();
     }
 }

        public static int ExecuteNonQuery(string sql)
        {
            using (SqliteConnection connection = new SqliteConnection())
            {
                connection.ConnectionString = ConnectionString;
                System.Data.IDbCommand cmd = connection.CreateCommand();
                cmd.CommandText = sql;

                connection.Open();
                return cmd.ExecuteNonQuery();
            }
        }

 /// <summary>
 /// Update details of sync source
 /// Pass SQLiteConnection object to make atomic action
 /// </summary>
 /// <param name="source"></param>
 /// <param name="con"></param>
 /// <returns></returns>
 public static bool Update(SyncSource source, SqliteConnection con )
 {
     using (SqliteCommand cmd = con.CreateCommand())
     {
         cmd.CommandText = "UPDATE " + Configuration.TBL_DATASOURCE_INFO +
                 " SET " + Configuration.COL_SOURCE_ABSOLUTE_PATH + " = @path WHERE "
                 + Configuration.COL_SOURCE_ID + " = @id";
         cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = source.ID });
         cmd.Parameters.Add(new SqliteParameter("@path", DbType.String) { Value = source.Path });
         cmd.ExecuteNonQuery();
         return true;
     }
 }

 /// <summary>
 /// This method takes in SQLiteConnection object as a parameter
 /// </summary>
 /// <param name="s"></param>
 /// <param name="con"></param>
 /// <returns></returns>
 public static bool Add(SyncSource s, SqliteConnection con)
 {
     using (SqliteCommand cmd = con.CreateCommand())
      {
          cmd.CommandText = "INSERT INTO " + Configuration.TBL_DATASOURCE_INFO +
                          "(" + Configuration.COL_SOURCE_ID + "," + Configuration.COL_SOURCE_ABSOLUTE_PATH +
                          ") VALUES (@id, @path)";
          cmd.Parameters.Add(new SqliteParameter("@id", DbType.String) { Value = s.ID });
          cmd.Parameters.Add(new SqliteParameter("@path", DbType.String) { Value = s.Path });
          cmd.ExecuteNonQuery();
      }
      return true;
 }

        public override bool Initialize(Framework.Interfaces.ICore core)
        {
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADING));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGDATA));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGGEOCACHES));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGLOGS));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGLOGIMAGES));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_LOADINGWAYPOINTS));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVING));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGDATA));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGGEOCACHES));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGLOGS));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGLOGIMAGES));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGLOGIMAGES));
            core.LanguageItems.Add(new Framework.Data.LanguageItem(STR_SAVINGWAYPOINTS));

            if (Properties.Settings.Default.UpgradeNeeded)
            {
                Properties.Settings.Default.Upgrade();
                Properties.Settings.Default.UpgradeNeeded = false;
                Properties.Settings.Default.Save();
            }
            if (string.IsNullOrEmpty(Properties.Settings.Default.ActiveDataFile))
            {
                Properties.Settings.Default.ActiveDataFile = System.IO.Path.Combine(core.PluginDataPath, "InternalStorage.db3" );
                Properties.Settings.Default.Save();
            }
            try
            {
                string fld = System.IO.Path.GetDirectoryName(Properties.Settings.Default.ActiveDataFile);
                if (!System.IO.Directory.Exists(fld))
                {
                    System.IO.Directory.CreateDirectory(fld);
                }
                _dbcon = new SqliteConnection(string.Format("data source=file:{0}", Properties.Settings.Default.ActiveDataFile));
                InitDatabase(_dbcon);
            }
            catch
            {
                _dbcon = null;
            }

            SetDataSourceName(Properties.Settings.Default.ActiveDataFile);
            core.Logs.LoadFullData += new Framework.EventArguments.LogEventHandler(Logs_LoadFullData);
            core.Geocaches.LoadFullData += new Framework.EventArguments.GeocacheEventHandler(Geocaches_LoadFullData);

            bool result = base.Initialize(core);
            return result;
        }

 public bool Add(SyncAction action, SqliteConnection con)
 {
     switch (action.ChangeType)
     {
         case ChangeType.DELETED:
             return InsertDeleteAction((DeleteAction)action);
         case ChangeType.NEWLY_CREATED:
             return InsertCreateAction((CreateAction)action, con);
         case ChangeType.RENAMED:
             return InsertRenameAction((RenameAction)action);
         default:
             // Log error? Throw ex?
             return false;
     }
 }

 /// <summary>
 /// Open a connection to the database.
 /// </summary>
 private void Open()
 {
     if (db == null)
     {
         try
         {
             db = new SqliteConnection(Constants.Database.connectionString);
             db.Open();
         }
         catch (Exception e)
         {
             System.Windows.MessageBox.Show("Error while connecting to Database: \n" + e.Message + "\n" + e.StackTrace);
         }
     }
 }

 public static bool OpenDatabase(string dbFileName)
 {
     try
     {
         Log.log("Opening sqlite database connection: "+dbFileName);
         string cs = string.Format("Version=3,uri=file:{0}", dbFileName);
         connection = new SqliteConnection(cs);
         connection.Open();
         trans =  connection.BeginTransaction();
         return true;
     }
     catch (Exception e)
     {
         return false;
     }
 }

        internal static List<Patron> getAll(SqliteConnection conn)
        {
            List<Patron> plist = new List<Patron>();

            SqliteCommand cmd = new SqliteCommand("select id, name, type from patron order by name", conn);
            SqliteDataReader rdr = cmd.ExecuteReader();
            while (rdr.NextResult())
            {
                OurPatron p = new OurPatron(conn, rdr.GetInt32(0));
                p.name = rdr.GetString(1);
                p.type = (PatronType)rdr.GetInt32(2);
                plist.Add(p);
            }

            return plist;
        }