SQLite metadata

最新推荐文章于 2023-01-05 16:17:03 发布
最新推荐文章于 2023-01-05 16:17:03 发布
阅读量251 收藏
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/geovindu/p/4362420.html
版权
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();
            //    }
            //}
        }

　　数据库类型

smallintSystem.Int1610
intSystem.Int3211
realSystem.Double8
singleSystem.Single15
floatSystem.Double8
doubleSystem.Double8
moneySystem.Decimal7
currencySystem.Decimal7
decimalSystem.Decimal7
numericSystem.Decimal7
bitSystem.Boolean3
yesnoSystem.Boolean3
logicalSystem.Boolean3
boolSystem.Boolean3
booleanSystem.Boolean3
tinyintSystem.Byte2
integerSystem.Int6412
counterSystem.Int6412
autoincrementSystem.Int6412
identitySystem.Int6412
longSystem.Int6412
bigintSystem.Int6412
binarySystem.Byte[]1
varbinarySystem.Byte[]1
blobSystem.Byte[]1
imageSystem.Byte[]1
generalSystem.Byte[]1
oleobjectSystem.Byte[]1
varcharSystem.String16
nvarcharSystem.String16
memoSystem.String16
longtextSystem.String16
noteSystem.String16
textSystem.String16
ntextSystem.String16
stringSystem.String16
charSystem.String16
ncharSystem.String16
datetimeSystem.DateTime6
smalldateSystem.DateTime6
timestampSystem.DateTime6
dateSystem.DateTime6
timeSystem.DateTime6
uniqueidentifierSystem.Guid4
guidSystem.Guid4

 

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:\Project\OrgCertificate\OrgCertificate\bin\Debug\OrgCertificateDB.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();
            }
        }
    }
}

　　

 

转载于:https://www.cnblogs.com/geovindu/p/4362420.html

weixin_30417487
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
metadata.db
09-18
calibre 数据库，放在挂载的目录下面； 解决问题：DB location is not valid, please enter correct path
Getting SQLite metadata with Csharp
weixin_34367845的博客
03-24 80
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Wind...
请问安卓sqlite中自动生成的Android_metedata是什么表啊？
qq_40154660的博客
02-20 152
SQLite 使用
Adimecoin的博客
08-31 1329
SQLite
SQLite Metadata Reader:将SQLiteConnection.GetSchema（）的结果包装为类-开源
04-25
c＃程序集返回由ADO.NET适配器System.Data.SQLite作为类返回的sqlite数据库的元数据。 唯一的要求是可从http://system.data.sqlite.org获得的System.Data.SQLite.dll。 它简单易用。 下载提供了代码示例。
javasqlite
08-01
5. **DatabaseMetaData**：提供了关于数据库的信息，如表、列、索引等。 在使用JavaSQLite时，首先需要在项目中引入相应的JDBC驱动，例如，压缩包中的`j2sdk1.4.2_03`可能包含了Java SDK的一个旧版本，其中可能不...
sqlite3.zip
05-17
首先，描述中提到的“SQLITE_ENABLE_COLUMN_METADATA”和“SQLITE_ENABLE_RTREE”是SQLite3编译时的预处理器定义，它们是用来启用特定功能的。`SQLITE_ENABLE_COLUMN_METADATA`是一个标志，当设置此标志时，SQLite会...
VC++ sqlite_3.12.0 实现了加密功能
04-12
VC++项目， 3.12.0版本的sqlite，使用wxWidgets中的加密代码。 编译时会有LNK4006警告，无影响。 使用时请在项目预处理器定义中添加 SQLITE_HAS_CODEC=1 CODEC_TYPE=CODEC_TYPE_AES128...SQLITE_ENABLE_COLUMN_METADATA
SQLite做为本地缓存应注意的几大方面
codepython的专栏
02-26 1083
 SQLite做为本地缓存应注意的几大方面 2011-02-18 09:34 Tecky Li 博客园 字号：T | T 今天我们要介绍的就是如何利用SQLite作为本地缓存的方法。其拥有适应于本地数据缓存和应用程序等诸多优点。 AD：WOT2015 互联网运维与开发者大会 热销抢票 今天看到了园友陆敏计的一篇文章>,
【原创】Ubuntu安装 Superset
u011700186的博客
09-17 2130
安装准备 1、安装简介 Superset 是 airbnb 开源的一款轻量级的 BI 可视化工具，现已托管在 apache 基金会旗下。本文将在 Ubuntu 18.04 上安装 Superset 并简单体验。 2、安装环境 Ubuntu 18.04.5 Python 3.6.9 Superset 0.37.1 安装步骤 1、创建一个 Python 虚拟环境并激活 $ py...
Android学习笔记---SQLite介绍,以及使用Sqlite,进行数据库的创建,完成数据添删改查的理解
添柴程序猿的专栏
03-10 1万+
17_创建数据库与完成数据添删改查 -------------------------------------- 1.SQLite介绍:最大特点是,无数据类型; 除了可以使用文件或SharedPreferences存储数据，还可以选择使用SQLite数据库存储数据。 在Android平台上，集成了一个嵌入式关系型数据库—SQLite，SQLite3支持 NULL、INTEGER
sqlite元数据
10-28 445
sqlite数据库每个文件都是一个database，因此同一个文件内部不再划分database。sqlite没有提供像mysql那样的show tables;和desc 表名类似的语句。许多数据库的元数据也是存储在一个表里面，mysql是这样，sqlite也是这样。在sqlite中，这个表的名字叫sql_master。 查看表的内容 select * from sqlite_master...
解决cannot import name ‘soft_unicode‘ from ‘markupsafe‘和‘EntryPoints‘ object has no attriibute ‘get‘
Redamancy06的博客
11-17 2597
如果将markupsafe改为2.0.1之后还是会报如下错误。将markupsafe依赖的版本回退到 2.0.1。ImportError: cannot import name 'soft_unicode' from 'markupsafe' (/opt/module/miniconda3/envs/superset/lib/python3.7/site-packages/markupsafe/__init__.py)AttributeError: 'EntryPoints' object has no a
SQLite Database Introduction
GOLOJO的博客
01-05 531
Briefly introduce some content related to SQLite3.
VS2013 生成sqlite3动态连接库及sqlite3.dll的调用
热门推荐
谢小军的专栏
03-05 1万+
一，生成sqlite3动态连接库 1，去sqlite官网上下载最近的sqlite源码包，解压后得到四个文件：shell.c,sqlite3.c,sqlite3.h,sqlite3ext.h 此处还需要sqlite3.def文件，它在sqlite官方生成的dll包中，下载下来，解压即可。 其中，shell.c文件是做来生成exe可执行文件用的。 2,打开vs2008 新建sqlite3的非mfc的D...
sqlite3命令详解（下）
mmdev
12-10 227
sqlite3提供了多个命令来查看数据库的schema ".tables"命令可以查看当前数据库所有的表 比如，示例14： sqlite&gt;.tables tbl1 tbl2 sqlite&gt; ".tables"和在list模式下执行下面的语句相似: SELECT name FROM sqlite_master WHERE type IN ('table','...
使用 GetSchema 方法
vevi的专栏
12-12 9826
使用 GetSchema 方法 每个由 .NET Framework 管理的提供程序中的 Connection 类实现 GetSchema 方法，该方法用于检索与当前连接的数据库有关的架构信息，从 GetSchema 方法返回的架构信息以 DataTable 的形式传入。GetSchema 方法属于重载方法，提供可选的参数来指定要返回的架构集合以及限制返回的信息量。指定架构集合
Sqlite 查询数据库的schema
weixin_34241036的博客
08-29 674
用于查询数据库的schema的Pragma PRAGMA database_list; 对每个打开的数据库，使用该数据库的信息调用一次回叫函数。使用包括附加的数据库名和索引名在内的参数。第一行用于主数据库，第二行用于存放临时表的临时数据库。 PRAGMA foreign_key_list(table-name); 对于参数表中每个涉及到字段的外键，使用该外...
#define SQLITE_ENABLE_COLUMN_METADATA 1
最新发布
07-02
`#define SQLITE_ENABLE_COLUMN_METADATA 1` 是一个预处理器宏定义，它通常用于SQLite数据库引擎的配置。当这个宏被设置为1时，SQLite会启用列元数据（column metadata）功能。列元数据包括列的名称、数据类型、长度...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值