C# SQLiteCommand.GetString方法代码示例

        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)
            {
            }
        }

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

        internal Action GetAction(string actionName)
        {
            var cn = new SQLiteConnection("Data Source=" + DbName + ";Version=3;");
             cn.Open();

             var reader = new SQLiteCommand("select id, command, arg1, arg2 from actions where id = '" + actionName + "'", cn).ExecuteReader();

             Action action = null;
             try
             {
            if (reader.Read())
            {
               action = new Action();
               action.Id = reader.GetString(reader.GetOrdinal("id"));
               action.Arg1 = reader.GetString(reader.GetOrdinal("arg1"));
               var arg2 = reader["arg2"];
               action.Arg2 = arg2.ToString();
               //action.Arg3 = reader.GetString(reader.GetOrdinal("arg3"));

               if (reader.GetString(reader.GetOrdinal("command")) == "pgdb-backup")
               {
                  action.Command = new PostgreSQLBackupCommand(action.Arg1, action.Arg2, action.Arg3, action.Arg4);
               }
               if (reader.GetString(reader.GetOrdinal("command")) == "execute-bat")
               {
                  action.Command = new ExecuteBatCommand(action.Arg1);
               }
               if (reader.GetString(reader.GetOrdinal("command")) == "sync-actions")
               {
                  action.Command = new SyncActionsCommand(action.Arg1);
               }
            }
             }
             finally
             {
            cn.Close();
             }
             return action;
        }

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

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



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

                        return;
                    }

                    #region update
                    {
                        var sql = "update ";
                        sql += Context.Name;
                        sql += " set ValueString = ";
                        sql += "'";
                        sql += value;
                        sql += "'";
                        sql += " where Key = ";
                        sql += "'";
                        sql += Key;
                        sql += "'";

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

                }

                get
                {
                    Context.Create();


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

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

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

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

                    return value;
                }
            }

            public Point this[string id]
            {
                set
                {
                    Context.Create();

                    if (Context.Connection.SQLiteCountByColumnName(Context.Name, "id", id) == 0)
                    {
                        var sql = "insert into ";
                        sql += Context.Name;
                        sql += " (id, x, y) values (";
                        sql += "'";
                        sql += id;
                        sql += "'";
                        sql += ", ";
                        sql += "'";
                        sql += value.x;
                        sql += "'";

                        sql += ", ";
                        sql += "'";
                        sql += value.y;
                        sql += "'";

                        sql += ")";



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

                        return;
                    }

                    #region update
                    {
                        var sql = "update ";
                        sql += Context.Name;

                        sql += " set x = ";
                        sql += "'";
                        sql += value.x;
                        sql += "'";

                        sql += " set y = ";
                        sql += "'";
                        sql += value.y;
                        sql += "'";


                        sql += " where id = ";
                        sql += "'";
                        sql += id;
                        sql += "'";

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

                }

                get
                {
                    Context.Create();


                    var sql = "select x, y from ";
                    sql += Context.Name;
                    sql += " where id = ";
                    sql += "'";
                    sql += id;
                    sql += "'";

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

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

                    if (reader.Read())
                    {
                        value = new Point { x = reader.GetString(0), y = reader.GetString(1) };
                    }
                    reader.Close();

                    return value;
                }
            }

        public List<string> GetKeys()
        {
            var sql = "select id from ";
            sql += Name;

            var value = new List<string>();

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

            while (reader.Read())
            {
                value.Add(reader.GetString(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;
        }

        /// <summary>
        /// Get the non-spatial columns
        /// </summary>
        private void GetNonSpatialColumns()
        {
            if (!string.IsNullOrEmpty(_columns))
                return;

            if (string.IsNullOrEmpty(ConnectionID))
                return;

            using (var cn = GetConnection(ConnectionString))
            {
                using (var dr = new SQLiteCommand(string.Format("PRAGMA table_info('{0}');", Table), cn).ExecuteReader())
                {
                    if (!dr.HasRows)
                        throw new InvalidOperationException("Provider configuration incomplete or wrong!");

                    var columns = new List<string>
                        {
                            string.Equals(ObjectIdColumn, "rowid", StringComparison.OrdinalIgnoreCase)
                                ? "\"ROWID\" AS \"ROWID\""
                                : string.Format("\"{0}\"", ObjectIdColumn)
                        };

                    while (dr.Read())
                    {
                        var column = dr.GetString(1);
                        if (string.Equals(column, ObjectIdColumn)) continue;
                        if (string.Equals(column, GeometryColumn)) continue;
                        columns.Add(string.Format("\"{0}\"", column));
                    }

                    _columns = string.Join(", ", columns);
                }
            }
        }

        private void ApplicationControl_Load(object sender, EventArgs e)
        {
            #region read
            var csb = new SQLiteConnectionStringBuilder
          {
              DataSource = DataSource,
              Version = 3
          };

            using (var c = new SQLiteConnection(csb.ConnectionString))
            {
                c.Open();

                {
                    var sql = "create table if not exists Table1 (ContentKey INTEGER PRIMARY KEY AUTOINCREMENT, ContentValue text not null, ContentBytes blob)";
                    using (var reader = new SQLiteCommand(sql, c).ExecuteReader())
                    {
                    }
                }

                {
                    var sql = "select ContentValue, ContentBytes from Table1";
                    var cmd = new SQLiteCommand(sql, c);

                    using (var reader = cmd.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            var ContentValue = reader.GetString(reader.GetOrdinal("ContentValue"));

                            var chunkSize = 4096;

                            // Get size of image data–pass null as the byte array parameter
                            long bytesize = reader.GetBytes(reader.GetOrdinal("ContentBytes"), 0, null, 0, 0);
                            // Allocate byte array to hold image data
                            byte[] imageData = new byte[bytesize];
                            long bytesread = 0;
                            int curpos = 0;
                            while (bytesread < bytesize)
                            {
                                // chunkSize is an arbitrary application defined value 
                                bytesread += reader.GetBytes(reader.GetOrdinal("ContentBytes"), curpos, imageData, curpos, chunkSize);
                                curpos += chunkSize;
                            }

                            dataGridView1.Rows.Add(
                                "",
                                ContentValue,
                                "" + bytesize
                            );
                        }
                    }
                }
            }
            #endregion

        }