C# SQLiteCommand.GetInt32方法代码示例

        public void CountItems(string Key, Action<string> y)
        {
            //Console.WriteLine("CountItems enter");
            using (var c = OpenReadOnlyConnection())
            {
                c.Open();

                using (var reader = new SQLiteCommand(
                    "select count(*) from MY_TABLEXX where XKey ='" + Key.Replace("'", "\\'") + "'", c).ExecuteReader()
                    )
                {

                    if (reader.Read())
                    {
                        var Content = (int)reader.GetInt32(0);

                        y("" + Content);

                    }
                }

                c.Close();

            }
            //Console.WriteLine("CountItems exit");
        }

        public void Init()
        {
            try
            {
                string path = Path.Combine(Program.Config.LauncherDir, "cards.cdb");
                m_cards = new Dictionary<int, CardInfos>();

                if (!File.Exists(path)) return;

                SQLiteConnection connection = new SQLiteConnection("Data Source=" + path);
                connection.Open();
                SQLiteDataReader reader = new SQLiteCommand("SELECT id, alias, type, level, race, attribute, atk, def FROM datas", connection).ExecuteReader();

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

                    CardInfos infos = new CardInfos(id)
                    {
                        AliasId = reader.GetInt32(1),
                        Type = reader.GetInt32(2),
                        Level = reader.GetInt32(3),
                        Race = reader.GetInt32(4),
                        Attribute = reader.GetInt32(5),
                        Atk = reader.GetInt32(6),
                        Def = reader.GetInt32(7)
                    };
                    m_cards.Add(id, infos);
                }

                reader.Close();
                reader = new SQLiteCommand("SELECT id, name, desc FROM texts", connection).ExecuteReader();

                while (reader.Read())
                {
                    int key = reader.GetInt32(0);
                    if (m_cards.ContainsKey(key))
                    {
                        m_cards[key].Name = reader.GetString(1);
                        m_cards[key].CleanedName = m_cards[key].Name.Trim().ToLower().Replace("-", " ").Replace("'", " ").Replace("   ", " ").Replace("  ", " ");
                        m_cards[key].Description = reader.GetString(2);
                    }
                }
                connection.Close();
            }
            catch (Exception)
            {
            }
        }

        /// <summary>
        /// This Method is a javascript callable method.
        /// </summary>
        /// <param name="e">A parameter from javascript.</param>
        /// <param name="y">A callback to javascript.</param>
        public void AddItem(string Key, string Content, Action<string> y)
        {
#if !DEBUG
            // should jsc do this implictly?
            try
            {
                //DriverManager.registerDriver(new AppEngineDriver());
            }
            catch
            {
                throw;
            }
#endif

            using (var c = new SQLiteConnection(

                  new SQLiteConnectionStringBuilder
                  {
                      DataSource = "MY_DATABASE.sqlite",
                      Version = 3
                  }.ConnectionString

            ))
            {
                c.Open();

                using (var cmd = new SQLiteCommand("create table if not exists MY_TABLEXX (XKey text not null, Content text not null)", c))
                {
                    cmd.ExecuteNonQuery();
                }

                new SQLiteCommand("insert into MY_TABLEXX (XKey, Content) values ('" + Key.Replace("'", "\\'") + "', '" + Content.Replace("'", "\\'") + "')", c).ExecuteNonQuery();

                using (var reader = new SQLiteCommand("select count(*) from MY_TABLEXX", c).ExecuteReader())
                {

                    if (reader.Read())
                    {
                        var count = (int)reader.GetInt32(0);

                        y("" + count);

                    }
                }


                c.Close();
            }

        }

 public void Init()
 {
     try
     {
         m_cards = new Dictionary<int, CardInfos>();
         string str = Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location) ?? "";
         string str2 = Path.Combine(str, @"cards.cdb");
         if (!File.Exists(str2)) return;
         SQLiteConnection connection = new SQLiteConnection("Data Source=" + str2);
         connection.Open();
         SQLiteDataReader reader = new SQLiteCommand("SELECT id, alias, type, level, race, attribute, atk, def FROM datas", connection).ExecuteReader();
         while (reader.Read())
         {
             int id = reader.GetInt32(0);
             CardInfos infos = new CardInfos(id)
             {
                 AliasId = reader.GetInt32(1),
                 Type = reader.GetInt32(2),
                 Level = reader.GetInt32(3),
                 Race = reader.GetInt32(4),
                 Attribute = reader.GetInt32(5),
                 Atk = reader.GetInt32(6),
                 Def = reader.GetInt32(7)
             };
             m_cards.Add(id, infos);
         }
         reader.Close();
         reader = new SQLiteCommand("SELECT id, name, desc FROM texts", connection).ExecuteReader();
         while (reader.Read())
         {
             int key = reader.GetInt32(0);
             if (m_cards.ContainsKey(key))
             {
                 m_cards[key].Name = reader.GetString(1);
                 m_cards[key].CleanedName = m_cards[key].Name.Trim().ToLower().Replace("-", " ").Replace("'", " ").Replace("   ", " ").Replace("  ", " ");
                 m_cards[key].Description = reader.GetString(2);
             }
         }
         connection.Close();
         Loaded = true;
     }
     catch (Exception)
     {
         MessageBox.Show("Error loading cards.cdb");
     }
 }

            public int this[string Key]
            {
                set
                {
                    Context.Create();

                    if (Context.Connection.SQLiteCountByColumnName(Context.Name, "Key", Key) == 0)
                    {
                        var sql = "insert into ";
                        sql += Context.Name;
                        sql += " (Key, ValueInt32) values (";
                        sql += "'";
                        sql += Key;
                        sql += "'";
                        sql += ", ";
                        sql += ((object)value).ToString();
                        sql += ")";



                        new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();

                        return;
                    }

                    #region update
                    {
                        var sql = "update ";
                        sql += Context.Name;
                        sql += " set ValueInt32 = ";
                        sql += ((object)value).ToString();
                        sql += " where Key = ";
                        sql += "'";
                        sql += Key;
                        sql += "'";

                        new SQLiteCommand(sql, Context.Connection).ExecuteNonQuery();
                    }
                    #endregion

                }

                get
                {
                    Context.Create();


                    var sql = "select ValueInt32 from ";
                    sql += Context.Name;
                    sql += " where Key = ";
                    sql += "'";
                    sql += Key;
                    sql += "'";

                    //new SQLiteCommand(sql, Connection).ExecuteScalar();

                    var value = 0;
                    var reader = new SQLiteCommand(sql, Context.Connection).ExecuteReader();

                    if (reader.Read())
                    {
                        value = reader.GetInt32(0);
                    }
                    reader.Close();

                    return value;
                }
            }

        public static int SQLiteCountByColumnName(this SQLiteConnection Connection, string Name, string ByColumnName, string ValueString)
        {
            var sql = "select count(*) from ";
            sql += Name;
            sql += " where ";
            sql += ByColumnName;
            sql += " = ";
            sql += "'";
            sql += ValueString;
            sql += "'";

            var value = 0;
            var reader = new SQLiteCommand(sql, Connection).ExecuteReader();

            if (reader.Read())
            {
                value = reader.GetInt32(0);
            }

            reader.Close();

            return value;
        }

        internal FeatureDataTable GetBaseTable()
        {
            const string sqlPragmaTableInfo ="PRAGMA table_info('{0}');";

            const string sqlSelectHasColumnData =
                "SELECT COUNT(*) FROM \"sqlite_master\" WHERE \"type\"='table' AND \"name\"='gpkg_column_data';";
            const string sqlSelectColumnData =
                "SELECT * FROM \"gpkg_column_data\" WHERE \"table_name\"=? AND \"column_name\"=?;";

            const string sqlSelectColumnConstraint =
                "SELECT * FROM \"gpkg_column_constraint\" WHERE \"constraint_name\"=?;";

            var fdt = new FeatureDataTable();
            
            // Get the geometry column definition if not previously done
            if (string.IsNullOrEmpty(GeometryColumn))
                GetGeometryColumnDefinition();

            using (var cnCI = new SQLiteConnection(_connectionString).OpenAndReturn())
            using (var cnCD = new SQLiteConnection(_connectionString).OpenAndReturn())
            using (var cnCC = new SQLiteConnection(_connectionString).OpenAndReturn())
            {
                var rdrCI = new SQLiteCommand(string.Format(sqlPragmaTableInfo, TableName), cnCI).ExecuteReader();
                if (!rdrCI.HasRows)
                    throw new GeoPackageException("The table '{0}' does not exist in database!");

                // has additional column data?
                var cmdCD = new SQLiteCommand(sqlSelectHasColumnData, cnCD);
                var hasCD = Convert.ToInt32(cmdCD.ExecuteScalar()) == 1;

                // additional column data
                cmdCD = new SQLiteCommand(sqlSelectColumnData, cnCD);
                var parCD0 = cmdCD.Parameters.Add("table_name", DbType.String);
                parCD0.Value = TableName;
                var parCD1 = cmdCD.Parameters.Add("column_name", DbType.String);

                // additional column constaint(s)
                var cmdCC = new SQLiteCommand(sqlSelectColumnConstraint, cnCC);
                var parCC0 = cmdCC.Parameters.Add("pcc", DbType.String);

                while (rdrCI.Read())
                {
                    // Get the column name
                    var columnName = rdrCI.GetString(1);
                    
                    // We don't want the geometry to appear as an attribute in the feature data table;
                    if (columnName == GeometryColumn) continue;
                    
                    // Set up the column
                    // column name and type
                    var dc = new DataColumn(rdrCI.GetString(1), GpkgUtility.GetTypeForDataTypeString(rdrCI.GetString(2)));

                    // Allow DBNull?
                    if (rdrCI.GetInt32(3) == 0) dc.AllowDBNull = true;

                    // Assign default value
                    if (!rdrCI.IsDBNull(4)) dc.DefaultValue = rdrCI.GetValue(4);

                    // Add the column
                    fdt.Columns.Add(dc);

                    // Get additional information
                    if (hasCD)
                    {
                        parCD1.Value = columnName;
                        var rdrCD = cmdCD.ExecuteReader(CommandBehavior.SingleRow);
                        if (rdrCD.HasRows)
                        {
                            rdrCD.Read();
                            if (!rdrCD.IsDBNull(2)) dc.Caption = rdrCD.GetString(2);

                            if (!rdrCD.IsDBNull(3))
                                dc.ExtendedProperties.Add("Title", rdrCD.GetString(3));
                            if (!rdrCD.IsDBNull(4))
                                dc.ExtendedProperties.Add("Description", rdrCD.GetString(4));
                            if (!rdrCD.IsDBNull(5))
                                dc.ExtendedProperties.Add("MimeType", rdrCD.GetString(5));

                            if (!rdrCD.IsDBNull(rdrCD.GetOrdinal("constraint_name")))
                            {
                                parCC0.Value = rdrCD.GetString(rdrCD.GetOrdinal("constraint_name"));
                                var rdrCC = cmdCC.ExecuteReader();
                                while (rdrCC.Read())
                                {
                                }
                            }
                        }
                    }

                    if (rdrCI.GetInt32(5) == 1)
                    {
                        fdt.PrimaryKey = new[] {dc};
                        OidColumn = dc.ColumnName;
                    }
                }
            }
            return fdt;
        }