C# SQLiteDataReader.GetSchemaTable方法代码示例

        private DataTable convertToDataTable(SQLiteDataReader reader)
        {
            DataTable table = new DataTable();
            DataTable dtb = new DataTable();
            table = reader.GetSchemaTable();

            for (int x = 0; x < table.Rows.Count; x++)
            {
                string colNome = table.Rows[x]["ColumnName"].ToString();
                dtb.Columns.Add(colNome, typeof(string));
            }

            dtb.AcceptChanges();

            while (reader.Read())
            {
                DataRow dr = dtb.NewRow();
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    string colNome = table.Rows[i]["ColumnName"].ToString();
                    string colType = table.Rows[i]["DataTypeName"].ToString();
                    string valor = string.Empty;
                    if (colType.ToUpper().Equals("NUMBER") || colType.ToUpper().Equals("INT") || colType.ToUpper().Equals("INTERGER"))
                    {
                        try
                        {
                            long Lvalor = reader.GetInt64(i);
                            if (Lvalor != null)
                                valor = Lvalor.ToString();
                        }
                        catch (InvalidCastException inv)
                        {
                            valor = string.Empty;
                        }
                    }
                    else if (colType.ToUpper().Equals("VARCHAR2") || colType.ToUpper().Equals("VARCHAR") || colType.ToUpper().Equals("TEXT"))
                        valor = reader.GetString(i).ToString();
                    dr[colNome] = valor;
                }
                dtb.Rows.Add(dr);

            }
            dtb.AcceptChanges();
            return dtb;
        }

 private string[] PopulateTableSchema(IFeatureSet fs, string geomColumnName, SQLiteDataReader rdr)
 {
     DataTable schemaTable = rdr.GetSchemaTable();
     List<string> columnNameList = new List<string>();
     foreach (DataRow r in schemaTable.Rows)
     {
         if (r["ColumnName"].ToString() != geomColumnName)
         {
             string colName = Convert.ToString(r["ColumnName"]);
             string colDataType = Convert.ToString(r["DataType"]);
             fs.DataTable.Columns.Add(colName, Type.GetType(colDataType));
             columnNameList.Add(colName);
         }
     }
     return columnNameList.ToArray();
 }

    /// <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"], CultureInfo.InvariantCulture));
        }
      }

      // 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"], CultureInfo.InvariantCulture);
                  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++)
//.........这里部分代码省略.........

        public DataSet DataReader2DataSet ( SQLiteDataReader reader )
        {
            DataSet dataSet = new DataSet ( );
            do
            {
                // Create new data table

                DataTable schemaTable = reader.GetSchemaTable ( );
                DataTable dataTable = new DataTable ( );

                if ( schemaTable != null )
                {
                    // A query returning records was executed

                    for ( int i = 0 ; i < schemaTable.Rows.Count ; i++ )
                    {
                        DataRow dataRow = schemaTable.Rows [ i ];
                        // Create a column name that is unique in the data table
                        string columnName = ( string ) dataRow [ "ColumnName" ];
                        // Add the column definition to the data table
                        DataColumn column = new DataColumn ( columnName , ( Type ) dataRow [ "DataType" ] );
                        dataTable.Columns.Add ( column );
                    }

                    dataSet.Tables.Add ( dataTable );

                    // Fill the data table we just created

                    while ( reader.Read ( ) )
                    {
                        DataRow dataRow = dataTable.NewRow ( );

                        for ( int i = 0 ; i < reader.FieldCount ; i++ )
                            dataRow [ i ] = reader.GetValue ( i );

                        dataTable.Rows.Add ( dataRow );
                    }
                }
                else
                {
                    // No records were returned

                    DataColumn column = new DataColumn ( "RowsAffected" );
                    dataTable.Columns.Add ( column );
                    dataSet.Tables.Add ( dataTable );
                    DataRow dataRow = dataTable.NewRow ( );
                    dataRow [ 0 ] = reader.RecordsAffected;
                    dataTable.Rows.Add ( dataRow );
                }
            }
            while ( reader.NextResult ( ) );
            return dataSet;
        }