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代码示例:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
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

 

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
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();
            }
        }
    }
}

　　

 

posted @   ®Geovin Du Dream Park™  阅读(591)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5
点击右上角即可分享
微信分享提示