• SQLite metadata


    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 restrictions
    Remarks
    Catalogs
    1
    Lists all connected catalogs.
    When restricted by name mask, returns all catalogs that match the mask.
    Columns
    3
    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.
    DatasourceInformation
    0
    Returns information about the data source.
    DataTypes
    0
    Returns information about data types supported by the data source.
    ForeignKeyColumns
    3
    Returns list of columns used by foreign keys in the catalog.
    Restrict it with catalog name, table name, and the foreign key name pattern.
    ForeignKeys
    3
    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.
    IndexColumns
    4
    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.
    Indexes
    3
    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.
    MetaDataCollections
    0
    Returns this list. Same as using GetSchema() method without parameters.
    PrimaryKeys
    2
    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.
    ReservedWords
    0
    Lists all reserved words used in the server.
    Restrictions
    0
    Returns list of possible restrictions and their default values for the metadata collections.
    Tables
    2
    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.
    UniqueKeys
    2
    • 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.
    ViewColumns
    3
    Returns list of columns used by views in the catalog.
    Restrict it with catalog name, table name and column name.
    Views
    2
    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	#	Name
    Columns	1	TABLE_CATALOG
    Columns	2	TABLE_SCHEMA
    Columns	3	TABLE_NAME
    Columns	4	COLUMN_NAME
    Indexes	1	TABLE_CATALOG
    Indexes	2	TABLE_SCHEMA
    Indexes	3	INDEX_NAME
    Indexes	4	TYPE
    Indexes	5	TABLE_NAME
    Procedures	1	PROCEDURE_CATALOG
    Procedures	2	PROCEDURE_SCHEMA
    Procedures	3	PROCEDURE_NAME
    Procedures	4	PROCEDURE_TYPE
    Tables	1	TABLE_CATALOG
    Tables	2	TABLE_SCHEMA
    Tables	3	TABLE_NAME
    Tables	4	TABLE_TYPE
    Views	1	TABLE_CATALOG
    Views	2	TABLE_SCHEMA
    Views	3	TABLE_NAME
     * 
     * 
     * MetaDataCollections集合下有:
    DataSourceInformation
    DataTypes
    ReservedWords
    Catalogs
    Columns
    Indexes
    IndexColumns
    Tables
    Views
    ViewColumns
    ForeignKeys
    Triggers
     * 
     * 
     */
    
    
    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();
                //    }
                //}
            }
    

      数据库类型

    smallint System.Int16 10
    int System.Int32 11
    real System.Double 8
    single System.Single 15
    float System.Double 8
    double System.Double 8
    money System.Decimal 7
    currency System.Decimal 7
    decimal System.Decimal 7
    numeric System.Decimal 7
    bit System.Boolean 3
    yesno System.Boolean 3
    logical System.Boolean 3
    bool System.Boolean 3
    boolean System.Boolean 3
    tinyint System.Byte 2
    integer System.Int64 12
    counter System.Int64 12
    autoincrement System.Int64 12
    identity System.Int64 12
    long System.Int64 12
    bigint System.Int64 12
    binary System.Byte[] 1
    varbinary System.Byte[] 1
    blob System.Byte[] 1
    image System.Byte[] 1
    general System.Byte[] 1
    oleobject System.Byte[] 1
    varchar System.String 16
    nvarchar System.String 16
    memo System.String 16
    longtext System.String 16
    note System.String 16
    text System.String 16
    ntext System.String 16
    string System.String 16
    char System.String 16
    nchar System.String 16
    datetime System.DateTime 6
    smalldate System.DateTime 6
    timestamp System.DateTime 6
    date System.DateTime 6
    time System.DateTime 6
    uniqueidentifier System.Guid 4
    guid System.Guid 4
    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;
    using Aspose.Cells.Tables;
    using Aspose.Cells;
    using System.Data.OleDb;
    
    
    namespace SQLiteGenerator
    {
    
    
        /// <summary>
        /// 涂聚文 塗聚文
        /// geovindu Geovin Du
        /// </summary>
        public partial class ForeignKeysForm : 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()
            {
                /*
     MetaDataCollections
    DataSourceInformation
    DataTypes
    ReservedWords
    Catalogs
    Columns
    Indexes
    IndexColumns
    Tables
    Views
    ViewColumns
    ForeignKeys
    Triggers
     */
                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(9, "ForeignKeys");//外键
                dt.Rows.Add(10, "MetaDataCollections");//MetaData集合
                //dt.Rows.Add(12, "PrimaryKeys");//无效
                dt.Rows.Add(11, "ReservedWords");
                //dt.Rows.Add(14, "Restrictions");//无效
                // dt.Rows.Add(15, "UniqueKeys");//无效
                dt.Rows.Add(12, "ViewColumns");//视图的字段
                dt.Rows.Add(19, "Triggers");
                return dt;
            }
            /// <summary>
            /// 
            /// </summary>
            public ForeignKeysForm()
            {
                InitializeComponent();
            }
            /// <summary>
            /// /
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void ForeignKeysForm_Load(object sender, EventArgs e)
            {
                try
                {
                    //this.comboBox1.DataSource = setTables();
                    //this.comboBox1.DisplayMember = "name";
                    //this.comboBox1.ValueMember = "id";
                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog(typeof(Program), ex.Message.ToString());
                }
            }
            /// <summary>
            /// 
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void button1_Click(object sender, EventArgs e)
            {
    
                try
                {
                    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;
    
                           DataTable dt=getSchema(connectionString);
                           this.comboBox1.DataSource = dt;
                           this.comboBox1.DisplayMember = "CollectionName";
                           this.comboBox1.ValueMember = "id";
    
    
    
                        }
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.WriteLog(typeof(Program), ex.Message.ToString());
                }
            }
            /// <summary>
            /// CollectionName,NumberOfRestrictions,NumberOfIdentifierParts
            /// </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();MetaDataCollections // conn.GetSchema("Indexes");(所有有主键的表)// conn.GetSchema("TABLES");(所有表包括系统表)//Views
                    dataGridView1.DataSource = schemaTable;
                    this.textBox2.Text = GetColumnNames(schemaTable);
                }
            }
            /// <summary>
            /// TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE,TABLE_ID,TABLE_ROOTPAGE,TABLE_DEFINITION
            /// </summary>
            /// <param name="sender"></param>
            /// <param name="e"></param>
            private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
            {
                if (this.comboBox1.Text.ToLower() == "tables")
                {
                    if(this.dataGridView1.Rows.Count>0)
                    {
                        string tablename = this.dataGridView1.Rows[e.RowIndex].Cells["TABLE_NAME"].Value.ToString();
                        DataTable pdt = GetForeignKeyParentInfo(tablename, connectionString);
                        this.dataGridView2.DataSource = pdt;
                        DataTable cdt = GetForeignChildKeyInfo(tablename, connectionString);
                        this.dataGridView3.DataSource = cdt;
    
                        this.dataGridView4.DataSource = GetSQLiteColumnsData(tablename, connectionString);
    
                    }
                }
            }
            /// <summary>
            /// MetaDataCollections
            /// </summary>
            /// <returns></returns>
            DataTable getSchema(string connString)
            {
                DataTable dt = new DataTable();
                dt.Columns.Add("id", typeof(int));
                dt.Columns.Add("CollectionName", typeof(string));
                dt.Columns.Add("NumberOfRestrictions", typeof(string));
                dt.Columns.Add("NumberOfIdentifierParts", typeof(string));           
                using (SQLiteConnection conn = new SQLiteConnection(connString))
                {
                    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("MetaDataCollections");// conn.GetSchema()和conn.GetSchema("MetaDataCollections")效果是一样的
                    int i = 1;
                    foreach (DataRow row in schemaTable.Rows)
                    {
                        dt.Rows.Add(i,row["CollectionName"], row["NumberOfRestrictions"].ToString(), row["NumberOfIdentifierParts"].ToString());
                        i++;
                    }
    
                }
                return dt;
            }
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="tableName"></param>
            /// <returns></returns>
            public DataSet GetSQLiteColumns(string tableName, string connectionString)
            {
    
                DataTable dtKey = GetPrimaryInfo(tableName, connectionString);//获取主键信息
                DataTable result = GetColumnInfo(tableName, connectionString);//获取列信息
                DataTable paredntfkey = GetForeignKeyParentInfo(tableName, connectionString);
                DataTable childfkey = GetForeignChildKeyInfo(tableName, connectionString);
                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("tableName", typeof(string)));
                dt.Columns.Add(new DataColumn("tableDescription", typeof(string)));
                dt.Columns.Add(new DataColumn("colOrder", typeof(string)));
                dt.Columns.Add(new DataColumn("columnName", typeof(string)));
                dt.Columns.Add(new DataColumn("IsIdentity", typeof(string)));
                dt.Columns.Add(new DataColumn("IsPrimaryKey", typeof(bool)));
                dt.Columns.Add(new DataColumn("TypeName", typeof(string)));
                dt.Columns.Add(new DataColumn("Length", typeof(string)));
                dt.Columns.Add(new DataColumn("Precision", typeof(string)));
                dt.Columns.Add(new DataColumn("Scale", typeof(string)));
                dt.Columns.Add(new DataColumn("Nullable", typeof(string)));
                dt.Columns.Add(new DataColumn("DefaultVal", typeof(string)));
                dt.Columns.Add(new DataColumn("Description", typeof(string)));
                dt.Columns.Add(new DataColumn("IsParentForeignKey", typeof(bool)));//是否存在外键的主表
                dt.Columns.Add(new DataColumn("IsChildForeignKey", typeof(bool)));//是否存在外键的从表
                foreach (DataRow row in result.Rows)
                {
                    DataRow r = dt.NewRow();
                    r["tableName"] = row["TABLE_NAME"].ToString();
                    r["tableDescription"] = row["COLLATION_SCHEMA"].ToString();
                    r["colOrder"] = row["ORDINAL_POSITION"].ToString();//
                    r["columnName"] = row["COLUMN_NAME"].ToString();
                    r["IsIdentity"] = false;//还未找到对应项
                    r["IsPrimaryKey"] = dtKey.Select(string.Format("COLUMN_NAME='{0}'", row["COLUMN_NAME"].ToString())).Length > 0 ? true : false;//是否是主键
                    r["TypeName"] = row["DATA_TYPE"].ToString().ToString();// row["DATA_TYPE"].ToString();
                    r["Length"] = row["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    r["Precision"] = row["NUMERIC_PRECISION"].ToString();
                    r["Scale"] = row["NUMERIC_SCALE"].ToString();
                    r["Nullable"] = bool.Parse(row["IS_NULLABLE"].ToString());
                    r["DefaultVal"] = row["COLUMN_DEFAULT"].ToString();
                    r["Description"] = row["DESCRIPTION"].ToString();
                    r["IsParentForeignKey"] = paredntfkey.Select(string.Format("COLUMN_NAME='{0}'", row["COLUMN_NAME"].ToString())).Length > 0 ? true : false;//是否是主键
                    r["IsChildForeignKey"] = childfkey.Select(string.Format("COLUMN_NAME='{0}'", row["COLUMN_NAME"].ToString())).Length > 0 ? true : false;//是否是主键
                    dt.Rows.Add(r);
                }
                DataSet ds = new DataSet();
                ds.Tables.Add(dt);
                return ds;
            }
            /// <summary>
            /// 
            /// </summary>
            /// <param name="tableName"></param>
            /// <param name="connectionString"></param>
            /// <returns></returns>
            public DataTable GetSQLiteColumnsData(string tableName, string connectionString)
            {
    
                DataTable dtKey = GetPrimaryInfo(tableName, connectionString);//获取主键信息
                DataTable result = GetColumnInfo(tableName, connectionString);//获取列信息
                DataTable paredntfkey = GetForeignKeyParentInfo(tableName, connectionString);
                DataTable childfkey = GetForeignChildKeyInfo(tableName, connectionString);
                DataTable dt = new DataTable();
                dt.Columns.Add(new DataColumn("tableName", typeof(string)));
                dt.Columns.Add(new DataColumn("tableDescription", typeof(string)));
                dt.Columns.Add(new DataColumn("colOrder", typeof(string)));
                dt.Columns.Add(new DataColumn("columnName", typeof(string)));
                dt.Columns.Add(new DataColumn("IsIdentity", typeof(string)));
                dt.Columns.Add(new DataColumn("IsPrimaryKey", typeof(bool)));
                dt.Columns.Add(new DataColumn("TypeName", typeof(string)));
                dt.Columns.Add(new DataColumn("Length", typeof(string)));
                dt.Columns.Add(new DataColumn("Precision", typeof(string)));
                dt.Columns.Add(new DataColumn("Scale", typeof(string)));
                dt.Columns.Add(new DataColumn("Nullable", typeof(string)));
                dt.Columns.Add(new DataColumn("DefaultVal", typeof(string)));
                dt.Columns.Add(new DataColumn("Description", typeof(string)));
                dt.Columns.Add(new DataColumn("IsParentForeignKey", typeof(bool)));//是否存在外键的主表
                dt.Columns.Add(new DataColumn("IsChildForeignKey", typeof(bool)));//是否存在外键的从表
                foreach (DataRow row in result.Rows)
                {
                    DataRow r = dt.NewRow();
                    r["tableName"] = row["TABLE_NAME"].ToString();
                    r["tableDescription"] = row["COLLATION_SCHEMA"].ToString();
                    r["colOrder"] = row["ORDINAL_POSITION"].ToString();//
                    r["columnName"] = row["COLUMN_NAME"].ToString();
                    r["IsIdentity"] = false;//还未找到对应项
                    r["IsPrimaryKey"] = dtKey.Select(string.Format("COLUMN_NAME='{0}'", row["COLUMN_NAME"].ToString())).Length > 0 ? true : false;//是否是主键
                    r["TypeName"] = row["DATA_TYPE"].ToString().ToString();// row["DATA_TYPE"].ToString();
                    r["Length"] = row["CHARACTER_MAXIMUM_LENGTH"].ToString();
                    r["Precision"] = row["NUMERIC_PRECISION"].ToString();
                    r["Scale"] = row["NUMERIC_SCALE"].ToString();
                    r["Nullable"] = bool.Parse(row["IS_NULLABLE"].ToString());
                    r["DefaultVal"] = row["COLUMN_DEFAULT"].ToString();
                    r["Description"] = row["DESCRIPTION"].ToString();
                    r["IsParentForeignKey"] = GetForeignKeyParentInfo(tableName, row["COLUMN_NAME"].ToString(),connectionString).Rows.Count > 0 ? true : false;//是否是主表的主键为外键
                    r["IsChildForeignKey"] = GetForeignChildKeyInfo(tableName, row["COLUMN_NAME"].ToString(), connectionString).Rows.Count > 0 ? true : false;//是否是子表的外主键
                    dt.Rows.Add(r);
                }
                return dt;
            }
            /// <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();
                //    }
                //}
            }
    
            /// <summary>
            /// 
            /// </summary>
            /// <param name="tableName"></param>
            /// <param name="connection"></param>
            /// <returns></returns>
            private DataTable GetReaderSchema(string tableName, SQLiteConnection connection)
            {
                DataTable schemaTable = null;
                IDbCommand cmd = new SQLiteCommand();
                cmd.CommandText = string.Format("select * from [{0}]", tableName);
                cmd.Connection = connection;
    
                using (IDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
                {
                    schemaTable = reader.GetSchemaTable();
                }
                return schemaTable;
            }
            /// <summary>
            /// 获取表列信息
            /// KS_Config
            /// </summary>
            /// <returns></returns>
            public DataTable GetColumnInfo(string tableName, string connectionString)
            {
                //connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:ProjectOrgCertificateOrgCertificateinDebugOrgCertificateDB.mdb;User ID=;Password=;";
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    DataTable dt = connection.GetSchema("Columns");
                    DataView view = new DataView();
                    view.Table = dt;
                    view.RowFilter = string.Format("table_name='{0}'", tableName);
                    //conn.Close();
                    return view.ToTable();
                }
            }
            /// <summary>
            /// 获取表主键信息
            /// KS_Config
            /// </summary>
            /// <param name="tableName"></param>
            /// <returns></returns>
            public DataTable GetPrimaryInfo(string tableName, string connectionString)
            {
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    DataTable dt = connection.GetSchema("IndexColumns");
                    DataView view = new DataView();
                    view.Table = dt;
                    view.RowFilter = string.Format("table_name='{0}'", tableName);
                    //conn.Close();
                    return view.ToTable();
                }
            }
            /// <summary>
            /// 获取外键信息
            /// TABLE_NAME,CONSTRAINT_TYPE,FKEY_FROM_COLUMN,FKEY_TO_TABLE,FKEY_TO_COLUMN
            /// 主表
            /// </summary>
            /// <param name="tableName"></param>
            /// <param name="connectionString"></param>
            /// <returns></returns>
            public DataTable GetForeignKeyParentInfo(string tableName, string connectionString)
            {
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    DataTable dt = connection.GetSchema("ForeignKeys");
                    DataView view = new DataView();
                    view.Table = dt;
                    view.RowFilter = string.Format("FKEY_TO_TABLE='{0}'", tableName);
                    //conn.Close();
                    return view.ToTable();
                }
            }
            /// <summary>
            /// 
            /// </summary>
            /// <param name="tableName"></param>
            /// <param name="columnName"></param>
            /// <param name="connectionString"></param>
            /// <returns></returns>
            public DataTable GetForeignKeyParentInfo(string tableName,string columnName,string connectionString)
            {
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    DataTable dt = connection.GetSchema("ForeignKeys");
                    DataView view = new DataView();
                    view.Table = dt;
                    view.RowFilter = string.Format("FKEY_TO_TABLE='{0}' and FKEY_TO_COLUMN='{1}'", tableName,columnName);
                    //conn.Close();
                    return view.ToTable();
                }
            }
            /// <summary>
            /// 从表
            /// </summary>
            /// <param name="tableName"></param>
            /// <param name="connectionString"></param>
            /// <returns></returns>
            public DataTable GetForeignChildKeyInfo(string tableName, string connectionString)
            {
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    DataTable dt = connection.GetSchema("ForeignKeys");
                    DataView view = new DataView();
                    view.Table = dt;
                    view.RowFilter = string.Format("TABLE_NAME='{0}'", tableName);
                    //conn.Close();
                    return view.ToTable();
                }
            }
            /// <summary>
            /// 
            /// </summary>
            /// <param name="tableName"></param>
            /// <param name="columnName"></param>
            /// <param name="connectionString"></param>
            /// <returns></returns>
            public DataTable GetForeignChildKeyInfo(string tableName, string columnName, string connectionString)
            {
                using (SQLiteConnection connection = new SQLiteConnection(connectionString))
                {
                    connection.Open();
                    DataTable dt = connection.GetSchema("ForeignKeys");
                    DataView view = new DataView();
                    view.Table = dt;
                    view.RowFilter = string.Format("TABLE_NAME='{0}' and FKEY_FROM_COLUMN='{1}'", tableName,columnName);
                    //conn.Close();
                    return view.ToTable();
                }
            }
        }
    }
    

      

  • 相关阅读:
    [Vue] Computed property "XXX" was assigned to but it has no setter.
    vue路由传参的三种基本方式
    ECharts大屏数据可视化展板项目 适配rem
    vue-cli3.0结合lib-flexible、px2rem实现适配,完美解决第三方ui库样式变小问题
    element-ui 实现行合并-亲测有效!
    ui自动化用Tesseract类截取和识别验证码【多测师】
    史上最全软件测试工程师常见的面试题总结(一)【多测师】
    Python操作非关系型数据库Redis【多测师】
    Java当中的重载和重写的区别【多测师】
    3道经典的Python练习题【多测师】
  • 原文地址:https://www.cnblogs.com/geovindu/p/4362420.html
Copyright © 2020-2023  润新知