Getting SQLite metadata with Csharp

* 参考资源 塗聚文 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
/// 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(); // } }

我为什么用 SQLite 和 FMDB 而不用 Core Data 凭良心讲，我不能告诉你不去使用Core Data。它不错，而且也在变好，并且它被很多其他Cocoa开发者所理解，当有新人加入你的组或者需要别人接手你的项目的时候，这点很重要。 更重要的是，不值得花时间和精力去写自己的系统去代替它。真的，使用Core Data吧。
uwp - 解决使用EntityFramework时报错“unable to load dll sqlite3 :the specified module could not be found” 原文:uwp - 解决使用EntityFramework时报错 unable to load dll sqlite3 :the specified module could not be found 在使用uwp的ef过程中碰到一万个问题快折腾死我了，好在最后终于解决掉所有问题，但愿如此，因为在这之前先后发生不同的报错，不知道后面还会碰到新的问题不。
uwp - 解决使用EntityFramework时报错 unable to load dll sqlite3 :the specified module could not be found 原文:uwp - 解决使用EntityFramework时报错 unable to load dll sqlite3 :the specified module could not be found 在使用uwp的ef过程中碰到一万个问题快折腾死我了，好在最后终于解决掉所有问题，但愿如此，因为在这之前先后发生不同的报错，不知道后面还会碰到新的问题不。
geovindu 读者是,读之者,者之读.一沙一世界! to be is to do举世皆清我独浊,众人皆醒我独醉.俺是农民工,程序员.