C# SqliteCommand.Prepare方法代码示例

        public SyncItem CreateSyncItem(SyncItem newItem)
        {
            using(SqliteConnection con = _provider.GetConnection())
            {
                con.Open();

                using (SqliteCommand cmd = new SqliteCommand(con))
                {
                    cmd.CommandText = string.Format(
                        @"INSERT INTO {0}(
                        Id,
                        FileKey,
                        CloudAssetId,
                        CloudLastUpdatedDate,
                        CloudCreatedDate,
                        IsDeleted,
                        Size)
                        VALUES(
                        @Id,
                        @FileKey,
                        @CloudAssetId,
                        @CloudLastUpdatedDate,
                        @CloudCreatedDate,
                        @IsDeleted,
                        @Size)", SyncItem.TableName);
                    cmd.Prepare();

                    cmd.Parameters.AddWithValue("@Id", newItem.Id);
                    cmd.Parameters.AddWithValue("@FileKey", newItem.FileKey);
                    cmd.Parameters.AddWithValue("@CloudAssetId", newItem.CloudAssetId);
                    cmd.Parameters.AddWithValue("@CloudLastUpdatedDate", newItem.CloudLastUpdated.Ticks);
                    cmd.Parameters.AddWithValue("@CloudCreatedDate", newItem.CloudCreated.Ticks);
                    cmd.Parameters.AddWithValue("@IsDeleted", newItem.IsDeleted);
                    cmd.Parameters.AddWithValue("@Size", newItem.Size);

                    cmd.ExecuteNonQuery();
                }

                con.Close();
            }
            return newItem;
        }

        //public static Dictionary<string, SpeciesData> spNameUpperList = new Dictionary<string, SpeciesData>();
        public static void Initialize()
        {
            SqliteConnection con = new SqliteConnection("URI=file:" + Application.dataPath + "/Database/WoB_DB.db");
            con.Open();

            SqliteCommand cmd = new SqliteCommand(con);
            cmd.CommandText = "" +
            "SELECT *" +
            " FROM `species`";

            cmd.Prepare();
            cmd.ExecuteNonQuery();

            SqliteDataReader reader = cmd.ExecuteReader();

            while (reader.Read()) {
            int species_id = reader.GetInt32(0);

            SpeciesData species = new SpeciesData(species_id);
            species.name = reader.GetString(1);
            species.description = reader.GetString(2);
            species.biomass = reader.GetInt32(3);
            species.diet_type = reader.GetInt16(4);
            species.trophic_level = reader.GetFloat(5);
            species.level = reader.GetInt16(6);

            SqliteCommand subcmd = new SqliteCommand(con);
            subcmd.CommandText = "" +
                "SELECT `predator_id`" +
                " FROM `pp_relations`" +
                " WHERE `prey_id` = @species_id";
            subcmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));

            subcmd.Prepare();
            subcmd.ExecuteNonQuery();
            SqliteDataReader subreader = subcmd.ExecuteReader();

            while (subreader.Read()) {
                int predator_id = subreader.GetInt32(0);

                if (!species.predatorList.ContainsKey(predator_id)) {
                    species.predatorList.Add(predator_id, "");
                }
            }

            subcmd = new SqliteCommand(con);
            subcmd.CommandText = "" +
                "SELECT `prey_id`" +
                " FROM `pp_relations`" +
                " WHERE `predator_id` = @species_id";
            subcmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));

            subcmd.Prepare();
            subcmd.ExecuteNonQuery();
            subreader = subcmd.ExecuteReader();

            while (subreader.Read()) {
                int prey_id = subreader.GetInt32(0);

                if (!species.preyList.ContainsKey(prey_id)) {
                    species.preyList.Add(subreader.GetInt32(0), "");
                }
            }

            species.name = Functions.NormalizeSpeciesName (species.name);
            speciesList.Add(species.species_id, species);
            spNameList.Add(species.name, species);
            //spNameUpperList.Add(species.name.ToUpper (), species);
            }

            foreach (SpeciesData species in speciesList.Values) {
            foreach (int predator_id in new List<int>(species.predatorList.Keys)) {
                if (SpeciesTable.speciesList.ContainsKey(predator_id)) {
                    species.predatorList[predator_id] = SpeciesTable.speciesList[predator_id].name;
                } else {
                    species.predatorList.Remove(predator_id);
                }
            }

            foreach (int prey_id in new List<int>(species.preyList.Keys)) {
                if (SpeciesTable.speciesList.ContainsKey(prey_id)) {
                    species.preyList[prey_id] = SpeciesTable.speciesList[prey_id].name;
                } else {
                    species.preyList.Remove(prey_id);
                }
            }
            }

            reader.Close();
            con.Close();
        }

        public static void Update(Dictionary<int, SpeciesData> updateList)
        {
            SqliteConnection con = new SqliteConnection("URI=file:" + Application.dataPath + "/Database/WoB_DB.db");
            con.Open();

            SqliteCommand cmd = new SqliteCommand(con);
            cmd.CommandText = "" +
            "DELETE FROM `pp_relations`" +
            " WHERE `predator_id` > 0 OR `prey_id` > 0";

            cmd.Prepare();
            cmd.ExecuteNonQuery();

            foreach (KeyValuePair<int, SpeciesData> entry in updateList) {
            int species_id = entry.Key;
            SpeciesData species = entry.Value;

            if (speciesList.ContainsKey(species_id)) { // If Exists, Delete Record
                cmd.CommandText = "" +
                    "DELETE FROM `species`" +
                    " WHERE `species_id` = @species_id";
                cmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));

                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }

            cmd.CommandText = "" +
                "INSERT INTO `species` (`species_id`, `name`, `description`, `biomass`, `diet_type`, `trophic_level`, 'level')" +
                " VALUES (@species_id, @name, @description, @biomass, @diet_type, @trophic_level, @level)";
            cmd.Parameters.Add(new SqliteParameter("@species_id", species.species_id));
            cmd.Parameters.Add(new SqliteParameter("@name", species.name));
            cmd.Parameters.Add(new SqliteParameter("@description", species.description));
            cmd.Parameters.Add(new SqliteParameter("@biomass", species.biomass));
            cmd.Parameters.Add(new SqliteParameter("@diet_type", species.diet_type));
            cmd.Parameters.Add(new SqliteParameter("@trophic_level", species.trophic_level));
            cmd.Parameters.Add(new SqliteParameter("@level", species.level));

            cmd.Prepare();
            cmd.ExecuteNonQuery();

            foreach (int predator_id in species.predatorList.Keys) {
                cmd.CommandText = "" +
                    "INSERT INTO `pp_relations` (`predator_id`, `prey_id`)" +
                    " VALUES (@predator_id, @prey_id)";
                cmd.Parameters.Add(new SqliteParameter("@predator_id", predator_id));
                cmd.Parameters.Add(new SqliteParameter("@prey_id", species.species_id));

                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }

            foreach (int prey_id in species.preyList.Keys) {
                cmd.CommandText = "" +
                    "INSERT INTO `pp_relations` (`predator_id`, `prey_id`)" +
                    " VALUES (@predator_id, @prey_id)";
                cmd.Parameters.Add(new SqliteParameter("@predator_id", species.species_id));
                cmd.Parameters.Add(new SqliteParameter("@prey_id", prey_id));

                cmd.Prepare();
                cmd.ExecuteNonQuery();
            }
            }

            con.Close();
        }

        public IEnumerable<IEntity> Select(EntityType type, bool useView, String where, params object[] arguments)
        {
            string[] columns = GetColumns(type);
            int n;
            String cmdKey = type.Name + where + useView;
            SqliteCommand cmd;
            if (!_commands.TryGetValue(cmdKey, out cmd))
            {
                String tableName = type.TableName;
                String columnNames = "";
                foreach (string column in columns)
                    columnNames = columnNames + String.Format("{0}{1}", String.IsNullOrEmpty(columnNames) ? "" : ",", column);
                if (!useView)
                    columnNames = columnNames + ",IsTombstone";
                columnNames = columnNames + ",IsDirty";

                cmd = new SqliteCommand
                {
                    Connection = ActiveConnection,
                    CommandText =
                        String.Format("SELECT {0} FROM [{1}]{2}", columnNames, useView ? tableName : "_" + tableName,
                            String.IsNullOrEmpty(@where) ? "" : " " + @where)
                };
                n = 1;
                foreach (object p in arguments)
                {
                    cmd.Parameters.AddWithValue(String.Format("@p{0}", n), p);
                    n++;
                }
                cmd.Prepare();
                _commands.Add(cmdKey, cmd);
            }
            else
            {
                for (int i = 0; i < arguments.Length; i++)
                {
                    cmd.Parameters[i].Value = arguments[i];
                }
            }

            var list = new List<IEntity>();
            using (SqliteDataReader r = cmd.ExecuteReader())
            {
                if (r.HasRows)
                {
                    while (r.Read())
                    {
                        IEntity entity = EntityFactory.CreateInstance(type);
                        n = 0;
                        foreach (string column in columns)
                        {
                            Type t = type.GetPropertyType(column);
                            if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>))
                                t = Nullable.GetUnderlyingType(t);

                            object value = r[n];
                            if (value.GetType().Equals(typeof(DBNull)))
                                value = null;
                            else
                            {
                                if (t.Equals(typeof(IDbRef)))
                                    value = DbRef.FromString(value.ToString());
                                else
                                {
                                    if (t == typeof(Guid))
                                    {
                                        Guid g;
                                        if (!Guid.TryParse(value.ToString(), out g))
                                        {
                                            if (t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>))
                                                value = null;
                                            else
                                                throw new ArgumentException(String.Format("Can't convert value '{0}' to System.Guid", value));
                                        }
                                        else
                                            value = g;
                                    }
                                    else
                                        value = Convert.ChangeType(value, t);
                                }
                            }
                            entity.SetValue(column, value);
                            n++;
                        }

                        bool isTombstone = !useView && (long)r["IsTombstone"] != 0;
                        (entity as ISqliteEntity).IsTombstone = isTombstone;
                        (entity as ISqliteEntity).Load((long)r["IsDirty"] != 0);

                        list.Add(entity);
                    }
                }
            }
            return list;
        }

 protected void PutParameters(SqliteCommand cmd, object[] args)
 {
     for(int i = 0; i < args.Length; i++)
     {
         string p_name = "@p" + i;
         SqliteParameter p = new SqliteParameter(p_name, args[i]);
         cmd.Parameters.Add(p);
         cmd.Prepare();
     }
 }

        public SyncItem UpdateSyncItem(SyncItem updatedItem)
        {
            using(SqliteConnection con = _provider.GetConnection())
            {
                con.Open();

                using (SqliteCommand cmd = new SqliteCommand(con))
                {
                    cmd.CommandText = string.Format(@"UPDATE {0} SET
                        FileKey = @FileKey,
                        CloudAssetId = @CloudAssetId,
                        CloudLastUpdatedDate = @CloudLastUpdatedDate,
                        CloudCreatedDate = @CloudCreatedDate,
                        IsDeleted = @IsDeleted,
                        Size = @Size WHERE
                        Id = {1}", SyncItem.TableName, updatedItem.Id);
                    cmd.Prepare();
                    cmd.Parameters.AddWithValue("@FileKey", updatedItem.FileKey);
                    cmd.Parameters.AddWithValue("@CloudAssetId", updatedItem.CloudAssetId);
                    cmd.Parameters.AddWithValue("@CloudLastUpdatedDate", updatedItem.CloudLastUpdated.Ticks);
                    cmd.Parameters.AddWithValue("@CloudCreatedDate", updatedItem.CloudCreated.Ticks);
                    cmd.Parameters.AddWithValue("@IsDeleted", updatedItem.IsDeleted);
                    cmd.Parameters.AddWithValue("@Size", updatedItem.Size);
                    cmd.ExecuteNonQuery();
                }

                con.Close();
            }
            return updatedItem;
        }

        private void InsertDummyData(SyncItem dummyData, SqliteCommand cmd)
        {
            cmd.CommandText = string.Format(
                @"INSERT INTO {0}(
                        FileKey,
                        CloudAssetId,
                        CloudLastUpdatedDate,
                        CloudCreatedDate,
                        IsDeleted,
                        Size)
                        VALUES(
                        @FileKey,
                        @CloudAssetId,
                        @CloudLastUpdated,
                        @CloudCreated,
                        @IsDeleted,
                        @Size)", SyncItem.TableName);
            cmd.Prepare();

            cmd.Parameters.AddWithValue("@FileKey", dummyData.FileKey);
            cmd.Parameters.AddWithValue("@CloudAssetId", dummyData.CloudAssetId);
            cmd.Parameters.AddWithValue("@CloudLastUpdated", dummyData.CloudLastUpdated.Ticks);
            cmd.Parameters.AddWithValue("@CloudCreated", dummyData.CloudCreated.Ticks);
            cmd.Parameters.AddWithValue("@IsDeleted", dummyData.IsDeleted);
            cmd.Parameters.AddWithValue("@Size", dummyData.Size);

            cmd.ExecuteNonQuery();
        }