C# SQLiteClient.SqliteCommand类代码示例

        public static void createTable(Type ja)
        {
            try
            {
                SqliteCommand sql = new SqliteCommand("DROP TABLE " + ja.Name, SqliteManager.connection);
                sql.ExecuteNonQuery();
            }
            catch (Exception e)
            {
            }
            string tmp = "CREATE TABLE " + ja.Name + "(";
            System.Reflection.FieldInfo[] fieldInfo = ja.GetFields();

            bool was = false;
            string primary = "";
            foreach (System.Reflection.FieldInfo info in fieldInfo)
            {
                Type temp = info.FieldType;
                if (was) tmp += ", ";
                tmp += info.Name + " ";
                was = true;
                if (temp == typeof(long))
                {
                    tmp += " INT";
                }
                else
                    if (temp == typeof(string))
                    {
                        tmp += " TEXT";
                    }
                    else
                        if (temp == typeof(double))
                        {
                            tmp += " REAL";
                        }
                        else
                            if (temp == typeof(bool))
                            {
                                tmp += " INT";
                            }
                            else
                            {
                                tmp += " TEXT";
                            }
                if (info.IsDefined(typeof(PrimaryKey), true))
                {
                    if (primary.Length != 0) primary += ", ";
                    primary += info.Name;
                }
            }
            if (primary.Length > 0)
            {
                tmp += ", PRIMARY KEY(" + primary + ") ";
            }
            tmp += ");";
            SqliteCommand sql2 = new SqliteCommand(tmp, SqliteManager.connection);
            sql2.ExecuteNonQuery();
        }

		internal SqliteDataReader (SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
		{
			command = cmd;
            rows = new List<object[]>();
            column_names_sens = new Dictionary<String, Object>();
            column_names_insens = new Dictionary<String, Object>( StringComparer.InvariantCultureIgnoreCase );
			closed = false;
			current_row = -1;
			reading = true;
			ReadpVm (pVm, version, cmd);
			ReadingDone ();
		}

 internal SqliteDataReader(SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
 {
     command = cmd;
     rows = new ArrayList ();
     column_names_sens = new Hashtable ();
     column_names_insens = new Hashtable (StringComparer.InvariantCultureIgnoreCase);
     closed = false;
     current_row = -1;
     reading = true;
     ReadpVm (pVm, version, cmd);
     ReadingDone ();
 }

 private void CreateHistoryTable()
 {
     string sql = @"
     CREATE TABLE history (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     client_id varchar(32) NOT NULL,
     from_jid varchar(3071),
     to_jid varchar(3071),
     timestamp INTEGER NOT NULL,
     message TEXT
     )";
     var command = new SqliteCommand(sql, _connection);
     command.ExecuteNonQuery();
 }

 internal static Patron get(int patron_id, Community.CsharpSqlite.SQLiteClient.SqliteConnection conn)
 {
     SqliteCommand cmd = new SqliteCommand(
         string.Format("select id, name, type from patron where id = {0}", patron_id), 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);
         return p;
     }
     return null;
 }

        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;
        }

		internal SqliteDataReader (SqliteCommand cmd, Sqlite3.Vdbe pVm, int version)
		{
			command = cmd;
			rows = new ArrayList ();
			column_names_sens = new Hashtable ();
#if NET_2_0
			column_names_insens = new Hashtable (StringComparer.InvariantCultureIgnoreCase);
#else
			column_names_insens = new Hashtable (CaseInsensitiveHashCodeProvider.DefaultInvariant,
							     CaseInsensitiveComparer.DefaultInvariant);
#endif
			closed = false;
			current_row = -1;
			reading = true;
			ReadpVm (pVm, version, cmd);
			ReadingDone ();
		}

 private void CreateVcard()
 {
     string sql = @"
     CREATE TABLE contact_details (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     jid varchar(2048) NOT NULL,
     hash varchar(2048) NOT NULL,
     fullname varchar(2048) NULL,
     url varchar(2048) NULL,
     birthday INTEGER NULL,
     details TEXT NULL,
     photoType varchar(2048) NULL,
     photoBin BLOB NULL
     );
     CREATE INDEX IX_contact_details ON contact_details(jid, hash)
     ";
     var command = new SqliteCommand(sql, _connection);
     command.ExecuteNonQuery();
 }

 private void CreateDiscoCache()
 {
     string sql = @"
     CREATE TABLE caps_info (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     node varchar(2048) NULL,
     version varchar(2048) NULL,
     hash varchar(2048) NULL
     );
     CREATE TABLE feature (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     uri varchar(2048) UNIQUE NOT NULL
     );
     CREATE TABLE caps_to_feature (
     id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
     caps_info_id int NOT NULL,
     feature_id int NOT NULL,
     FOREIGN KEY (caps_info_id) REFERENCES caps_info(id),
     FOREIGN KEY (feature_id) REFERENCES feature(id)
     )";
     var command = new SqliteCommand(sql, _connection);
     command.ExecuteNonQuery();
 }

 /// <summary>
 /// Select a single result using the passed in value to formulate a query.
 /// </summary>
 /// <param name="statement"></param>
 /// <returns></returns>
 public Model.Item Select(String statement)
 {
     Open();
     Model.Item result = null;
     try
     {
         SqliteCommand cmd = new SqliteCommand(statement, db);
         SqliteDataReader reader = cmd.ExecuteReader();  //all rows exist in this object only right now
         
         while (reader.Read())
         {
             //value 0 = id
             //value 1 = name
             //value 2 = html
             result = new Model.Item((int)reader.GetValue(0), (string)reader.GetValue(1), (string)reader.GetValue(2));
         }
     }
     catch (SqliteExecutionException ee)
     {
         System.Windows.MessageBox.Show("Error while querying Database: \n" + ee.Message + "\n" + ee.StackTrace);
     }
     catch (SqliteSyntaxException se)
     {
         System.Windows.MessageBox.Show("Error while querying Database: \n" + se.Message + "\n" + se.StackTrace);
     }
     catch (ArgumentException ae)
     {
         System.Windows.MessageBox.Show("Error while adding value to list: \n" + ae.Message + "\n" + ae.StackTrace);
     }
     finally
     {
         Close();
     }
     
     return result;
 }

        public override void CreateTable(string table, ColumnDefinition[] columns, IndexDefinition[] indexDefinitions)
        {
            if (TableExists(table))
            {
                MainConsole.Instance.WarnFormat("[SQLite]: Trying to create a table '{0}' that already exists.", table);
                return;
            }

            IndexDefinition primary = null;
            foreach (IndexDefinition index in indexDefinitions)
            {
                if (index.Type == IndexType.Primary)
                {
                    primary = index;
                    break;
                }
            }

            List<string> columnDefinition = new List<string>();

            bool has_auto_increment = false;
            foreach (ColumnDefinition column in columns)
            {
                has_auto_increment |= column.Type.auto_increment;
                columnDefinition.Add(column.Name + " " + GetColumnTypeStringSymbol(column.Type));
            }
            if (!has_auto_increment && primary != null && primary.Fields.Length > 0)
            {
                columnDefinition.Add("PRIMARY KEY (" + string.Join(", ", primary.Fields) + ")");
            }

            var cmd = new SqliteCommand 
                            {
                                CommandText = 
                                    string.Format ("create table " + table + " ({0})",
                                    string.Join (", ", columnDefinition.ToArray ()))
                            };
            try {
                ExecuteNonQuery (cmd);
            } catch (SqliteException e) {
                MainConsole.Instance.Error ("[Sqlite]: Create table (" + table + "), " + e);
            }
            CloseReaderCommand(cmd);

            if (indexDefinitions.Length >= 1 && (primary == null || indexDefinitions.Length >= 2))
            {
                columnDefinition = new List<string>(primary != null ? indexDefinitions.Length : indexDefinitions.Length - 1);
                    // reusing existing variable for laziness
                uint i = 0;
                foreach (IndexDefinition index in indexDefinitions)
                {
                    if (index.Type == IndexType.Primary || index.Fields.Length < 1)
                    {
                        continue;
                    }

                    i++;
                    columnDefinition.Add("CREATE " + (index.Type == IndexType.Unique ? "UNIQUE " : string.Empty) +
                                         "INDEX idx_" + table + "_" + i + " ON " + table + "(" +
                                         string.Join(", ", index.Fields) + ")");
                }
                foreach (string query in columnDefinition)
                {
                    cmd = new SqliteCommand
                              {
                                  CommandText = query
                              };
                    try {
                    ExecuteNonQuery(cmd);
                    } catch (SqliteException e) {
                        MainConsole.Instance.Error ("[Sqlite]: Create index (" + query + "), " + e);
                    }

                    CloseReaderCommand(cmd);
                }
            }
        }

        public override bool Delete(string table, QueryFilter queryFilter)
        {
            Dictionary<string, object> ps = new Dictionary<string, object>();
            string query = "DELETE FROM " + table +
                           (queryFilter != null ? (" WHERE " + queryFilter.ToSQL(':', out ps)) : "");

            SqliteCommand cmd = new SqliteCommand(query);
            AddParams(ref cmd, ps);
            try
            {
                ExecuteNonQuery(cmd);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[Sqlite]: Delete(" + query + "), " + e);
                return false;
            }
            CloseReaderCommand(cmd);
            return true;
        }

        public override bool Insert(string table, object[] values, string updateKey, object updateValue)
        {
            var cmd = new SqliteCommand();
            Dictionary<string, object> ps = new Dictionary<string, object>();

            string query;
            query = string.Format("insert into {0} values (", table);
            int i = 0;
            foreach (object value in values)
            {
                ps[":" + Util.ConvertDecString(i)] = value;
                query = string.Format(query + ":{0},", Util.ConvertDecString(i++));
            }
            query = query.Remove(query.Length - 1);
            query += ")";
            cmd.CommandText = query;
            AddParams(ref cmd, ps);
            try
            {
                ExecuteNonQuery(cmd);
                CloseReaderCommand(cmd);
            }
                //Execute the update then...
            catch (Exception)
            {
                cmd = new SqliteCommand();
                query = string.Format("UPDATE {0} SET {1} = '{2}'", table, updateKey, updateValue);
                cmd.CommandText = query;
                ExecuteNonQuery(cmd);
                CloseReaderCommand(cmd);
            }
            return true;
        }

        bool InsertOrReplace(string table, Dictionary<string, object> row, bool insert)
        {
            SqliteCommand cmd = new SqliteCommand();
            string query = (insert ? "INSERT" : "REPLACE") + " INTO " + table + " (" +
                           string.Join(", ", row.Keys.ToArray ()) + ")";
            List<string> ps = new List<string>();
            foreach (KeyValuePair<string, object> field in row)
            {
                string key = ":" + field.Key.Replace("`", "");
                ps.Add(key);
                AddParam(ref cmd, key, field.Value);
            }
            query += " VALUES( " + string.Join(", ", ps.ToArray<string>()) + " )";

            cmd.CommandText = query;
            try
            {
                ExecuteNonQuery(cmd);
            }
            catch (Exception e)
            {
                MainConsole.Instance.Error("[Sqlite]: " + (insert ? "Insert" : "Replace") + "(" + query + "), " + e);
            }
            CloseReaderCommand(cmd);
            return true;
        }

		private void CreateNewCommand (ref SqliteCommand command)
                {
                        SqliteCommand sourceCommand = SourceCommand;
                        if (command == null) {
                                command = sourceCommand.Connection.CreateCommand () as SqliteCommand;
                                command.CommandTimeout = sourceCommand.CommandTimeout;
                                command.Transaction = sourceCommand.Transaction;
                        }
                        command.CommandType = CommandType.Text;
                        command.UpdatedRowSource = UpdateRowSource.None;
                        command.Parameters.Clear ();
                }