C# SqliteConnection.GetSchema方法代码示例

 public bool VerifyDb()
 {
     using (var conn = new SqliteConnection(ConnectionString))
     {
         conn.Open();
         var schema = conn.GetSchema("Tables");
         Console.WriteLine("Tables = " + schema.Rows.Count);
         conn.Close();
         return schema.Rows.Count == 2;
     }
 }

    /// <summary>
    /// Clones the settings and connection string from an existing connection.  If the existing connection is already open, this
    /// function will open its own connection, enumerate any attached databases of the original connection, and automatically
    /// attach to them.
    /// </summary>
    /// <param name="connection"></param>
    public SqliteConnection(SqliteConnection connection)
      : this(connection.ConnectionString)
    {
      string str;

      if (connection.State == ConnectionState.Open)
      {
        Open();

        // Reattach all attached databases from the existing connection
        using (DataTable tbl = connection.GetSchema("Catalogs"))
        {
          foreach (DataRow row in tbl.Rows)
          {
            str = row[0].ToString();
            if (String.Compare(str, "main", true, CultureInfo.InvariantCulture) != 0
              && String.Compare(str, "temp", true, CultureInfo.InvariantCulture) != 0)
            {
              using (SqliteCommand cmd = CreateCommand())
              {
                cmd.CommandText = String.Format(CultureInfo.InvariantCulture, "ATTACH DATABASE '{0}' AS [{1}]", row[1], row[0]);
                cmd.ExecuteNonQuery();
              }
            }
          }
        }
      }
    }

        public static bool DatabaseHelperTableExists(string tableName)
        {
            SqliteConnection connection = new SqliteConnection(GetConnectionString());
            string[] restrictions = new string[4];
            restrictions[2] = tableName;
            connection.Open();
            DataTable table = connection.GetSchema("Tables", restrictions);
            connection.Close();
            if (table != null)
            {
                return (table.Rows.Count > 0);
            }

            return false;
        }

 /// <summary>
 /// 获取所有数据类型信息
 /// </summary>
 /// <returns></returns>
 public DataTable GetSchema()
 {
     using (SqliteConnection connection = new SqliteConnection(dbConnectionString))
     {
         connection.Open();
         DataTable dt = connection.GetSchema("TABLES");
         return dt;
     }
 }

 public Dictionary<string, Table> Tables()
 {
     Dictionary<string, Table> tables = new Dictionary<string, Table>();
     createDatabase(AppSettings.Default.ConnectionString);
     using (SqliteConnection conn = new SqliteConnection(AppSettings.Default.ConnectionString)) {
         conn.Open();
         DataTable tabs = conn.GetSchema("Tables");
         DataTable cols = conn.GetSchema("Columns");
         DataTable fkeyCols = conn.GetSchema("ForeignKeys");
         DataTable indexes = conn.GetSchema("Indexes");
         DataTable indexCols = conn.GetSchema("IndexColumns");
         DataTable views = conn.GetSchema("Views");
         DataTable viewCols = conn.GetSchema("ViewColumns");
         foreach(DataRow table in tabs.Rows) {
             string name = table["TABLE_NAME"].ToString();
             string filter = "TABLE_NAME = " + Quote(name);
             Field[] fields = cols.Select(filter, "ORDINAL_POSITION")
                 .Select(c => new Field(c["COLUMN_NAME"].ToString(), typeFor(c["DATA_TYPE"].ToString()),
                     lengthFromColumn(c), c["IS_NULLABLE"].ToString() == "True", c["AUTOINCREMENT"].ToString() == "True",
                     defaultFromColumn(c))).ToArray();
             List<Index> tableIndexes = new List<Index>();
             foreach (DataRow ind in indexes.Select(filter + " AND PRIMARY_KEY = 'True'")) {
                 string indexName = ind["INDEX_NAME"].ToString();
                 tableIndexes.Add(new Index("PRIMARY",
                     indexCols.Select(filter + " AND INDEX_NAME = " + Quote(indexName), "ORDINAL_POSITION")
                     .Select(r => fields.First(f => f.Name == r["COLUMN_NAME"].ToString())).ToArray()));
             }
             foreach (DataRow ind in indexes.Select(filter + " AND PRIMARY_KEY = 'False' AND UNIQUE = 'True'")) {
                 string indexName = ind["INDEX_NAME"].ToString();
                 tableIndexes.Add(new Index(indexName,
                     indexCols.Select(filter + " AND INDEX_NAME = " + Quote(indexName), "ORDINAL_POSITION")
                     .Select(r => fields.First(f => f.Name == r["COLUMN_NAME"].ToString())).ToArray()));
             }
             tables[name] = new Table(name, fields, tableIndexes.ToArray());
         }
         foreach (DataRow fk in fkeyCols.Rows) {
             Table detail = tables[fk["TABLE_NAME"].ToString()];
             Table master = tables[fk["FKEY_TO_TABLE"].ToString()];
             Field masterField = master.FieldFor(fk["FKEY_TO_COLUMN"].ToString());
             detail.FieldFor(fk["FKEY_FROM_COLUMN"].ToString()).ForeignKey = new ForeignKey(master, masterField);
         }
         foreach (DataRow table in views.Select()) {
             string name = table["TABLE_NAME"].ToString();
             string filter = "VIEW_NAME = " + Quote(name);
             Field[] fields = viewCols.Select(filter, "ORDINAL_POSITION")
                 .Select(c => new Field(c["VIEW_COLUMN_NAME"].ToString(), typeFor(c["DATA_TYPE"].ToString()),
                     lengthFromColumn(c), c["IS_NULLABLE"].ToString() == "True", false,
                     defaultFromColumn(c))).ToArray();
             Table updateTable = null;
             tables.TryGetValue(Regex.Replace(name, "^.*_", ""), out updateTable);
             tables[name] = new View(name, fields, new Index[] { new Index("PRIMARY", fields[0]) },
                 table["VIEW_DEFINITION"].ToString(), updateTable);
         }
     }
     return tables;
 }

        /// <summary>
        /// 获取所有数据类型信息
        /// </summary>
        /// <returns></returns>
		public static DataTable GetSchema(string connectionString)
        {
			using (var connection = new SqliteConnection(connectionString))
            {
                connection.Open();
                DataTable dt = connection.GetSchema("TABLES");
                return dt;
            }
        }

    /// <summary>
    /// This function does all the nasty work at determining what keys need to be returned for
    /// a given statement.
    /// </summary>
    /// <param name="cnn"></param>
    /// <param name="reader"></param>
    /// <param name="stmt"></param>
    internal SqliteKeyReader(SqliteConnection cnn, SqliteDataReader reader, SqliteStatement stmt)
    {
      Dictionary<string, int> catalogs = new Dictionary<string, int>();
      Dictionary<string, List<string>> tables = new Dictionary<string, List<string>>();
      List<string> list;
      List<KeyInfo> keys = new List<KeyInfo>();

      // Record the statement so we can use it later for sync'ing
      _stmt = stmt;

      // Fetch all the attached databases on this connection
      using (DataTable tbl = cnn.GetSchema("Catalogs"))
      {
        foreach (DataRow row in tbl.Rows)
        {
          catalogs.Add((string)row["CATALOG_NAME"], Convert.ToInt32(row["ID"]));
        }
      }

      // Fetch all the unique tables and catalogs used by the current statement
      using (DataTable schema = reader.GetSchemaTable(false, false))
      {
        foreach (DataRow row in schema.Rows)
        {
          // Check if column is backed to a table
          if (row[SchemaTableOptionalColumn.BaseCatalogName] == DBNull.Value)
            continue;

          // Record the unique table so we can look up its keys
          string catalog = (string)row[SchemaTableOptionalColumn.BaseCatalogName];
          string table = (string)row[SchemaTableColumn.BaseTableName];

          if (tables.ContainsKey(catalog) == false)
          {
            list = new List<string>();
            tables.Add(catalog, list);
          }
          else
            list = tables[catalog];

          if (list.Contains(table) == false)
            list.Add(table);
        }

        // For each catalog and each table, query the indexes for the table.
        // Find a primary key index if there is one.  If not, find a unique index instead
        foreach (KeyValuePair<string, List<string>> pair in tables)
        {
          for (int i = 0; i < pair.Value.Count; i++)
          {
            string table = pair.Value[i];
            DataRow preferredRow = null;
            using (DataTable tbl = cnn.GetSchema("Indexes", new string[] { pair.Key, null, table }))
            {
              // Loop twice.  The first time looking for a primary key index, 
              // the second time looking for a unique index
              for (int n = 0; n < 2 && preferredRow == null; n++)
              {
                foreach (DataRow row in tbl.Rows)
                {
                  if (n == 0 && (bool)row["PRIMARY_KEY"] == true)
                  {
                    preferredRow = row;
                    break;
                  }
                  else if (n == 1 && (bool)row["UNIQUE"] == true)
                  {
                    preferredRow = row;
                    break;
                  }
                }
              }
              if (preferredRow == null) // Unable to find any suitable index for this table so remove it
              {
                pair.Value.RemoveAt(i);
                i--;
              }
              else // We found a usable index, so fetch the necessary table details
              {
                using (DataTable tblTables = cnn.GetSchema("Tables", new string[] { pair.Key, null, table }))
                {
                  // Find the root page of the table in the current statement and get the cursor that's iterating it
                  int database = catalogs[pair.Key];
                  int rootPage = Convert.ToInt32(tblTables.Rows[0]["TABLE_ROOTPAGE"]);
                  int cursor = stmt._sql.GetCursorForTable(stmt, database, rootPage);

                  // Now enumerate the members of the index we're going to use
                  using (DataTable indexColumns = cnn.GetSchema("IndexColumns", new string[] { pair.Key, null, table, (string)preferredRow["INDEX_NAME"] }))
                  {
                    KeyQuery query = null;

                    List<string> cols = new List<string>();
                    for (int x = 0; x < indexColumns.Rows.Count; x++)
//.........这里部分代码省略.........