你的位置:首页 > 数据库

[数据库]SQLite metadata


http://www.devart.com/dotconnect/sqlite/docs/MetaData.html
https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.1
In this overload first parameter is name of a collection, and second parameter is the array of restrictions to be applied when querying information. These restrictions specify which subset of the collection will be returned. The restrictions can include, for example, the database name (in this case, only collection elements belonging to this database will be returned) or the mask for the name of collection elements (only the elements satisfying this mask will be returned). The quantity and description of restrictions allowed for each metadata collection are represented in the table here. Their number can also be obtained from the return of the GetSchema() method. If the second parameter is null/Nothing, it is ignored.
Instead of specifying the metadata collection name as a string constant, you may use members of System.Data.DbMetaDataCollectionNames and Devart.Data.SQLite.SQLiteMetadataCollectionNames as the first GetSchema argument values. The members of these classes are the string fields, each field stores the corresponding metadata collection name. It is recommended to use these fields rather than manually input the collection names manually as the string constants because in case of using these fields, you will find misspellings at compile-time, and intellisense will show you all the available metadata collection names.
GetSchema Method Reference
The following table provides detailed information on metadata collections that can be retrieved using the GetSchema method, and restrictions that can be applied for them. When calling the GetSchema method, you can pass all or few arguments. In the latter case, some default values are assumed, if they were not specified explicitly. The default value of catalog restriction is "main" - the currently opened database.

 

Collection Name
Number of restrictionsRemarks
Catalogs1
Lists all connected catalogs.
When restricted by name mask, returns all catalogs that match the mask.
Columns3
Returns list of columns, their type and some extra information.
  • The first restriction is name of a catalog that GetSchema method should search in.
  • The second restriction is name of a table that GetSchema method should search in.
  • At last, you can set column name pattern as described in "Tables" collection.

     

DatasourceInformation0
Returns information about the data source.
DataTypes0
Returns information about data types supported by the data source.
ForeignKeyColumns3
Returns list of columns used by foreign keys in the catalog.
Restrict it with catalog name, table name, and the foreign key name pattern.
ForeignKeys3
Returns list of columns that participate in foreign keys.
  • The first restriction for this collection is name of a catalog.
  • The second restriction is table name mask.
  • The third restriction is the key pattern.

     

IndexColumns4
Returns list of indexed columns in the catalog, their type and some extra information.
Restrict it with catalog name, table name, index name pattern, and column name pattern.
Indexes3
Returns list of indexes and their details.
  • The first restriction is catalog name
  • The second restriction is table name.
  • The last restriction is the index name pattern.

     

MetaDataCollections0
Returns this list. Same as using GetSchema() method without parameters.
PrimaryKeys2
Returns list of columns that participate in primary keys.
  • The first restriction for this collection is name of a catalog.
  • The second restriction is table name.

     

ReservedWords0
Lists all reserved words used in the server.
Restrictions0
Returns list of possible restrictions and their default values for the metadata collections.
Tables2
GetSchema("Tables") returns list of tables in "main" catalog.
  • The first restriction for this collection is name of a catalog. If specified, the method returns all tables within the catalog.
  • The second restriction is table name mask. You can use wildcards '%' (any number of characters) and '_' (one character) to retrieve names of tables that match the mask.

     

UniqueKeys2
  • Returns list of columns that participate in unique keys.
  • The first restriction for this collection is name of a catalog.
  • The second restriction is table name.

     

ViewColumns3
Returns list of columns used by views in the catalog.
Restrict it with catalog name, table name and column name.
Views2
GetSchema("Views") returns list of views in "main" catalog.
  • The first restriction for this collection is name of a catalog.
  • The second restriction is view name mask.

     

csharp代码示例:

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using System.Data.SQLite;using System.Data.SQLite.Linq;/* * 参考资源 塗聚文 Geovin Du Access,DB2,Informix,MySql,Oracle,PostgreSQL,SqlCe,SQLite,SqlServer,Sybase,Firebird http://zetcode.com/db/sqlitecsharp/meta/ * http://sourceforge.net/projects/sqlitemetadata/files/?source=navbar * http://www.codeproject.com/Tips/810596/Csharp-VB-NET-Cplusplus-CLI-Create-read-and-write * https://dbschemareader.codeplex.com/ * https://github.com/sqlitebrowser/sqlitebrowser/releases/tag/v3.5.1 * http://stackoverflow.com/questions/15207045/determining-primary-key-columns-via-getschema * http://www.devart.com/dotconnect/sqlite/docs/MetaData.html * http://www.devart.com/dotconnect/mysql/docs/MetaData.html * http://www.devart.com/dotconnect/sqlserver/docs/MetaData.html * http://www.devart.com/dotconnect/db2/docs/MetaData.html * http://www.devart.com/dotconnect/sqlite/docs/MetaData.html * http://www.devart.com/dotconnect/oracle/articles/metadata.html * http://www.devart.com/dotconnect/postgresql/docs/MetaData.html * SQL Server Schema Collections * https://msdn.microsoft.com/en-us/library/ms254969.aspx * https://msdn.microsoft.com/en-us/library/kcax58fh.aspx * https://support.microsoft.com/en-us/kb/318452 Excel MetaData * CollectionName	#	NameColumns	1	TABLE_CATALOGColumns	2	TABLE_SCHEMAColumns	3	TABLE_NAMEColumns	4	COLUMN_NAMEIndexes	1	TABLE_CATALOGIndexes	2	TABLE_SCHEMAIndexes	3	INDEX_NAMEIndexes	4	TYPEIndexes	5	TABLE_NAMEProcedures	1	PROCEDURE_CATALOGProcedures	2	PROCEDURE_SCHEMAProcedures	3	PROCEDURE_NAMEProcedures	4	PROCEDURE_TYPETables	1	TABLE_CATALOGTables	2	TABLE_SCHEMATables	3	TABLE_NAMETables	4	TABLE_TYPEViews	1	TABLE_CATALOGViews	2	TABLE_SCHEMAViews	3	TABLE_NAME * * * MetaDataCollections集合下有:DataSourceInformationDataTypesReservedWordsCatalogsColumnsIndexesIndexColumnsTablesViewsViewColumnsForeignKeysTriggers * * */namespace PayrollPrint{  /// <summary>  /// SELECT name, sql FROM sqlite_master WHERE type="table" AND name = "Dom"  /// SELECT count(*) FROM sqlite_master WHERE type = "view" AND name = "myView"  /// 涂聚文 20150324 21:19  /// </summary>  public partial class SQLiteFileForm : Form  {    public string connectionString = "Data Source=geovindu;Version=3;Pooling=true;FailIfMissing=false;UTF8Encoding=True;";    /// <summary>    /// 3.8.5版本查詢    /// </summary>    /// <returns></returns>    private DataTable setTables()    {      DataTable dt = new DataTable();      dt.Columns.Add("id", typeof(int));      dt.Columns.Add("name", typeof(string));      dt.Rows.Add(1, "TABLES");//所有表包括系统表      dt.Rows.Add(2, "Indexes");//所有有主键的表      dt.Rows.Add(3, "IndexColumns");//有键的表及主键      dt.Rows.Add(4, "Views");//所有视图      dt.Rows.Add(5, "DataTypes");//字段类型      dt.Rows.Add(6, "Columns");//表的字段      dt.Rows.Add(7, "Catalogs");//数据库文件地址      dt.Rows.Add(8, "DatasourceInformation");//数据库文件版本等信息      dt.Rows.Add(9, "ForeignKeyColumns");//无效      dt.Rows.Add(10, "ForeignKeys");//外键      dt.Rows.Add(11, "MetaDataCollections");//MetaData集合      dt.Rows.Add(12, "PrimaryKeys");      dt.Rows.Add(13, "ReservedWords");      dt.Rows.Add(14, "Restrictions");      dt.Rows.Add(15, "UniqueKeys");      dt.Rows.Add(16, "ViewColumns");//视图的字段      dt.Rows.Add(17, "Triggers");      return dt;    }    /// <summary>    ///     /// </summary>    public SQLiteFileForm()    {      InitializeComponent();    }    /// <summary>    /// select * from sqlite_master where type = 'table' and name ='myTable';    /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void SQLiteFileForm_Load(object sender, EventArgs e)    {      this.comboBox1.DataSource = setTables();      this.comboBox1.DisplayMember = "name";      this.comboBox1.ValueMember = "id";    }    /// <summary>    ///     /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void button1_Click(object sender, EventArgs e)    {      openFileDialog1.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);      //JPEG Files (*.jpeg)|*.jpeg|PNG Files (*.png)|*.png|JPG Files (*.jpg)|*.jpg|GIF Files (*.gif)|*.gif      openFileDialog1.FileName = "";      openFileDialog1.Filter = "files(*.db)|*.db|files (*.*)|*.*";//|(*.xlsx)|*.xlsx Image Files(*.BMP;*.JPG;*.GIF)|*.BMP;*.JPG;*.GIF|All files (*.*)|*.* txt files (*.txt)|*.txt|All files (*.*)|*.*"       openFileDialog1.FilterIndex = 2;      openFileDialog1.RestoreDirectory = true;      if (openFileDialog1.ShowDialog() == DialogResult.OK)      {        if (!openFileDialog1.FileName.Equals(String.Empty))        {          connectionString = @"Data Source=" + openFileDialog1.FileName + ";Version=3;Pooling=true;FailIfMissing=false;UTF8Encoding=True;";          this.textBox1.Text = openFileDialog1.FileName;        }      }    }    /// <summary>    /// TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ID,TABLE_ROOTPAGE,TABLE_DEFINITION    /// TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,INDEX_CATALOG,INDEX_SCHEMA,INDEX_NAME,PRIMARY_KEY,UNIQUE,CLUSTERED,TYPE,FILL_FACTOR,INITIAL_SIZE,NULLS,SORT_BOOKMARKS,AUTO_UPDATE,NULL_COLLATION,ORDINAL_POSITION,COLUMN_NAME,COLUMN_GUID,COLUMN_PROPID,COLLATION,CARDINALITY,PAGES,FILTER_CONDITION,INTEGRATED,INDEX_DEFINITION    ///IndexColumns: CONSTRAINT_CATALOG,CONSTRAINT_SCHEMA,CONSTRAINT_NAME,TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,INDEX_NAME,COLLATION_NAME,SORT_MODE,CONFLICT_OPTION    /// </summary>    /// <param name="sender"></param>    /// <param name="e"></param>    private void button2_Click(object sender, EventArgs e)    {      DataTable dt = new DataTable();      using (SQLiteConnection conn = new SQLiteConnection(connectionString))      {        conn.Open();        //http://www.devart.com/dotconnect/sqlite/docs/MetaData.html        //Catalogs,Columns,DatasourceInformation,DataTypes,ForeignKeyColumns,ForeignKeys,IndexColumns,Indexes,MetaDataCollections,PrimaryKeys,ReservedWords,Restrictions,Tables,UniqueKeys,ViewColumns,Views        DataTable schemaTable = conn.GetSchema(this.comboBox1.Text);//(有键的表及主键)// conn.GetSchema("Indexes");(所有有主键的表)// conn.GetSchema("TABLES");(所有表包括系统表)//Views        dataGridView1.DataSource = schemaTable;        this.textBox2.Text = GetColumnNames(schemaTable);      }    }    /// <summary>    ///     /// </summary>    /// <param name="table"></param>    /// <returns></returns>    public static string GetColumnNames(System.Data.DataTable table)    {      if (table != null)      {        List<string> lstColumn = new List<string>();        foreach (System.Data.DataColumn col in table.Columns)        {          lstColumn.Add(col.ColumnName);        }        return String.Join(",", lstColumn.ToArray());      }      return string.Empty;      //foreach (DataRow row in table.Rows)      //{      //  foreach (DataColumn column in table.Columns)      //  {      //    ColumnName = column.ColumnName;      //    ColumnData = row[column].ToString();      //  }      //}    }