Differences
This shows you the differences between two versions of the page.
Both sides previous revision Previous revision Next revision | Previous revision | ||
swdev:sqlite:custom_client_in_csharp [2018/07/12 10:42] smayr [Get Table Schema] |
swdev:sqlite:custom_client_in_csharp [2018/07/12 10:52] (current) smayr [Insert Table Entries] |
||
---|---|---|---|
Line 78: | Line 78: | ||
==== Insert Table Entries ==== | ==== Insert Table Entries ==== | ||
<code csharp> | <code csharp> | ||
- | command.CommandText | + | string sql = " |
+ | SQLiteCommand command = new SQLiteCommand(sql, | ||
command.ExecuteNonQuery(); | command.ExecuteNonQuery(); | ||
+ | Console.WriteLine(" | ||
</ | </ | ||
Line 179: | Line 181: | ||
return result; | return result; | ||
+ | } | ||
+ | |||
+ | /// | ||
+ | /// < | ||
+ | /// Generate SQL CREATE TABLE statement basing on metadata retrieved with GetSchema method. | ||
+ | /// The generated script will work with all catalog management systems that support | ||
+ | /// ANSI standard. Only column name and type are included in the script. | ||
+ | /// Source: https:// | ||
+ | /// </ | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | /// | ||
+ | public static void GetCreateTable(SQLiteConnection conn, string tableName) | ||
+ | { | ||
+ | // Open the connection | ||
+ | conn.Open(); | ||
+ | |||
+ | // Fill DataTable with columns information | ||
+ | DataTable tbl = conn.GetSchema( | ||
+ | " | ||
+ | ); | ||
+ | string sql = $" | ||
+ | string fieldLine; | ||
+ | DataRow row; | ||
+ | |||
+ | // For every row in the table | ||
+ | for (int i = 0; i < tbl.Rows.Count; | ||
+ | { | ||
+ | // Get column name and type | ||
+ | row = tbl.Rows[i]; | ||
+ | fieldLine = row[" | ||
+ | |||
+ | // Add comma or closing bracket | ||
+ | if (i < tbl.Rows.Count - 1) | ||
+ | { | ||
+ | fieldLine = fieldLine + ", | ||
+ | } | ||
+ | else | ||
+ | { | ||
+ | fieldLine = fieldLine + " | ||
+ | } | ||
+ | // Add new column to script | ||
+ | sql += fieldLine; | ||
+ | } | ||
+ | Console.WriteLine(sql); | ||
+ | |||
+ | // Close the connection | ||
+ | conn.Close(); | ||
} | } | ||
</ | </ | ||
Line 212: | Line 262: | ||
{ | { | ||
// Print schema column captions: | // Print schema column captions: | ||
- | // TABLE_CATALOG, | + | // |
+ | // ORDINAL_POSITION, | ||
+ | // TYPE_GUID, | ||
+ | // NUMERIC_SCALE, | ||
+ | // CHARACTER_SET_NAME, | ||
+ | // DOMAIN_NAME, | ||
for (int i = 0; i < tbl.Columns.Count; | for (int i = 0; i < tbl.Columns.Count; | ||
{ | { | ||
Line 257: | Line 312: | ||
conn.Close(); | conn.Close(); | ||
} | } | ||
+ | |||
+ | return result; | ||
+ | } | ||
+ | |||
+ | /// | ||
+ | /// < | ||
+ | /// Get table fields. | ||
+ | /// </ | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | /// < | ||
+ | /// | ||
+ | public static string GetTableFieldsAsCSV(string dbPath, string tableName, bool getAllSchemaData=false) | ||
+ | { | ||
+ | List< | ||
+ | string fields = ""; | ||
+ | foreach (string field in lstFields) | ||
+ | { | ||
+ | fields += $" | ||
+ | } | ||
+ | fields = fields.TrimEnd(',' | ||
+ | |||
+ | return fields; | ||
+ | } | ||
+ | |||
+ | /// | ||
+ | /// < | ||
+ | /// Retrieve columns information from a table and render it to console. | ||
+ | /// Source: https:// | ||
+ | /// </ | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | /// < | ||
+ | /// | ||
+ | public static List< | ||
+ | { | ||
+ | List< | ||
+ | |||
+ | conn.Open(); | ||
+ | DataTable tbl = conn.GetSchema( | ||
+ | collectionName: | ||
+ | restrictionValues: | ||
+ | ); | ||
+ | |||
+ | for (int i = 0; i < tbl.Columns.Count; | ||
+ | { | ||
+ | Console.Write(tbl.Columns[i].Caption + " | ||
+ | result.Add(tbl.Columns[i].Caption); | ||
+ | } | ||
+ | Console.WriteLine(); | ||
+ | foreach (DataRow row in tbl.Rows) | ||
+ | { | ||
+ | if (tableName == (string)row[" | ||
+ | { | ||
+ | foreach (DataColumn col in tbl.Columns) | ||
+ | { | ||
+ | Console.Write(row[col] + " | ||
+ | result.Add($" | ||
+ | } | ||
+ | Console.WriteLine(); | ||
+ | } | ||
+ | } | ||
+ | conn.Close(); | ||
return result; | return result; |