涂聚文--信息智能化学习者,豆腐渣工程投诉者

Getting SQLite metadata with csharp

2015-3-24 21:31:37 人评论 次浏览

: nothing at of , which is

分类: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();
            //    }
            //}
        }