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/06/25 16:00] smayr [Example using DataGrid (WPF)] |
swdev:sqlite:custom_client_in_csharp [2018/07/12 10:52] (current) smayr [Insert Table Entries] |
||
---|---|---|---|
Line 54: | Line 54: | ||
SQLiteConnection conn = new SQLiteConnection(DatabaseSource) | SQLiteConnection conn = new SQLiteConnection(DatabaseSource) | ||
conn.Open(); | conn.Open(); | ||
+ | //... | ||
+ | conn.Close(); | ||
</ | </ | ||
See more: [[https:// | See more: [[https:// | ||
Line 76: | 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 90: | Line 94: | ||
</ | </ | ||
+ | ==== Get Database Tables ==== | ||
+ | <code csharp> | ||
+ | /// | ||
+ | /// < | ||
+ | /// Get table fields. | ||
+ | /// </ | ||
+ | /// <param name=" | ||
+ | /// < | ||
+ | /// | ||
+ | public static List< | ||
+ | { | ||
+ | List< | ||
+ | |||
+ | if (System.IO.File.Exists(dbPath)) | ||
+ | { | ||
+ | string dbConnectionString = $"Data Source={dbPath}; | ||
+ | |||
+ | SQLiteConnection conn = new SQLiteConnection(dbConnectionString); | ||
+ | conn.Open(); | ||
+ | |||
+ | DataTable tbl = conn.GetSchema( | ||
+ | collectionName: | ||
+ | restrictionValues: | ||
+ | " | ||
+ | // | ||
+ | } | ||
+ | ); | ||
+ | |||
+ | // Print table list | ||
+ | Console.WriteLine($" | ||
+ | foreach (DataRow row in tbl.Rows) | ||
+ | { | ||
+ | Console.WriteLine($" | ||
+ | result.Add(row[2].ToString()); | ||
+ | // | ||
+ | } | ||
+ | |||
+ | conn.Close(); | ||
+ | } | ||
+ | |||
+ | return result; | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | ==== Get Table Schema ==== | ||
+ | <code csharp> | ||
+ | /// | ||
+ | /// < | ||
+ | /// Generate SQL CREATE TABLE statement. | ||
+ | /// </ | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | /// < | ||
+ | /// | ||
+ | public static string GetTableSchema(string dbPath, string tableName) | ||
+ | { | ||
+ | string result = ""; | ||
+ | |||
+ | if (System.IO.File.Exists(dbPath)) | ||
+ | { | ||
+ | string dbConnectionString = $"Data Source={dbPath}; | ||
+ | |||
+ | SQLiteConnection conn = new SQLiteConnection(dbConnectionString); | ||
+ | conn.Open(); | ||
+ | |||
+ | DataTable tbl = conn.GetSchema( | ||
+ | collectionName: | ||
+ | restrictionValues: | ||
+ | " | ||
+ | // | ||
+ | } | ||
+ | ); | ||
+ | |||
+ | // Print table list | ||
+ | Console.WriteLine($" | ||
+ | foreach (DataRow row in tbl.Rows) | ||
+ | { | ||
+ | if (tableName == $" | ||
+ | { | ||
+ | result = row[6].ToString(); | ||
+ | } | ||
+ | } | ||
+ | |||
+ | conn.Close(); | ||
+ | } | ||
+ | |||
+ | 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(); | ||
+ | } | ||
+ | </ | ||
+ | |||
+ | ==== Get Table Fields ==== | ||
+ | <code csharp> | ||
+ | /// | ||
+ | /// < | ||
+ | /// Get table fields. | ||
+ | /// </ | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | /// < | ||
+ | /// | ||
+ | public static List< | ||
+ | { | ||
+ | List< | ||
+ | |||
+ | if (System.IO.File.Exists(dbPath)) | ||
+ | { | ||
+ | string dbConnectionString = $"Data Source={dbPath}; | ||
+ | |||
+ | SQLiteConnection conn = new SQLiteConnection(dbConnectionString); | ||
+ | conn.Open(); | ||
+ | |||
+ | DataTable tbl = conn.GetSchema( | ||
+ | collectionName: | ||
+ | restrictionValues: | ||
+ | ); | ||
+ | |||
+ | if (getAllSchemaData) | ||
+ | { | ||
+ | // Print schema column captions: | ||
+ | // | ||
+ | // | ||
+ | // | ||
+ | // | ||
+ | // | ||
+ | // | ||
+ | for (int i = 0; i < tbl.Columns.Count; | ||
+ | { | ||
+ | // | ||
+ | Console.Write(tbl.Columns[i].Caption + "," | ||
+ | } | ||
+ | Console.WriteLine(); | ||
+ | |||
+ | // Print table schema data. Eg: In table ' | ||
+ | // main, | ||
+ | foreach (DataRow row in tbl.Rows) | ||
+ | { | ||
+ | // Print only field' | ||
+ | if (tableName == (string)row[" | ||
+ | { | ||
+ | // Print all field' | ||
+ | string str = ""; | ||
+ | foreach (DataColumn col in tbl.Columns) | ||
+ | { | ||
+ | string cellContent = row[col] + ","; | ||
+ | Console.Write(cellContent); | ||
+ | str += cellContent; | ||
+ | } | ||
+ | result.Add(str.TrimEnd(',' | ||
+ | Console.WriteLine(); | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | else | ||
+ | { | ||
+ | // Print table fields | ||
+ | foreach (DataRow row in tbl.Rows) | ||
+ | { | ||
+ | // Print only field' | ||
+ | if (tableName == (string)row[" | ||
+ | { | ||
+ | string fieldLine = $"### {row[" | ||
+ | Console.WriteLine(fieldLine); | ||
+ | result.Add($" | ||
+ | } | ||
+ | } | ||
+ | } | ||
+ | |||
+ | 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; | ||
+ | } | ||
+ | </ | ||
=== Full Example === | === Full Example === | ||
<code csharp> | <code csharp> | ||
Line 386: | Line 676: | ||
`brand` | `brand` | ||
`product_code` | `product_code` | ||
- | `product_data_hash` TEXT, | + | `product_data_hash` TEXT, |
`product_data` | `product_data` | ||
`workstation` | `workstation` | ||
- | `program_version` TEXT, | + | `program_version` |
`created_at` | `created_at` | ||
`updated_at` | `updated_at` | ||
Line 408: | Line 698: | ||
// " | // " | ||
- | string | + | string |
- | string fields = "id, order_number, | + | //string ProductDataEscaped = System.Security.SecurityElement.Escape(ProductData); |
- | //string values = $"1, 11111, ' | + | //string ProductDataEscaped = ProductData.Replace("&", "& |
- | string values = $"null, 11112, '18208865', 'Acme', 'prod_Intuition2ER', '9ef7fb550acb27cc5033e37ef4f939bf', '{product_data}', 'QCPC2', '4.2.6670.18099', datetime(' | + | // .Replace("<", |
- | | + | //string ProductDataEscaped = ProductData.Replace("&", |
+ | // .Replace(" | ||
+ | string ProductDataEscaped = ProductData.Replace("&", | ||
+ | |||
+ | string fields = "id, guid, order_number, | ||
+ | | ||
+ | | ||
+ | //string values = $"1, ' | ||
+ | // | ||
+ | // | ||
+ | string values = $" | ||
+ | | ||
+ | | ||
+ | | ||
+ | | ||
+ | SQLiteCommand command = new SQLiteCommand(sql, | ||
command.ExecuteNonQuery(); | command.ExecuteNonQuery(); | ||
+ | |||
+ | //sql = $" | ||
+ | // | ||
+ | // | ||
conn.Close(); | conn.Close(); | ||
Line 453: | Line 762: | ||
} | } | ||
+ | /// < | ||
+ | /// SelectionChanged event handler for DataGrid. | ||
+ | /// </ | ||
+ | /// <param name=" | ||
+ | /// <param name=" | ||
+ | private void gridData_SelectionChanged(object sender, SelectionChangedEventArgs e) | ||
+ | { | ||
+ | // Get selected Row | ||
+ | DataRowView row = (DataRowView)gridData.SelectedItem; | ||
+ | |||
+ | // Get selected Row Cell base on which the datagrid will be changed | ||
+ | string entry_id | ||
+ | string order_number | ||
+ | string serial_number = row.Row[" | ||
+ | string brand = row.Row[" | ||
+ | |||
+ | MessageBox.Show($" | ||
+ | } | ||
} | } | ||
} | } |