Differences

This shows you the differences between two versions of the page.

--- swdev:sqlite:custom_client_in_csharp [2018/06/25 16:16]
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)
 conn.Open();
+//...
+conn.Close();
 </code>
 See more: [[https://www.connectionstrings.com/sqlite/|SQLite connection strings]]
@@ Line 76: / Line 78: @@
 ==== Insert Table Entries ====
 <code csharp>
-command.CommandText = "INSERT INTO MyTable (Key,Value) VALUES ('key one','value one')";
+string sql = "INSERT INTO MyTable (Key,Value) VALUES ('key one','value one')";
+SQLiteCommand command = new SQLiteCommand(sql, conn);
 command.ExecuteNonQuery();
+Console.WriteLine("NewRecID: " + conn.LastInsertRowId);
 </code>
@@ Line 90: / Line 94: @@
 </code>
+==== Get Database Tables ====
+<code csharp>
+///---------------------------------------------------------------------
+/// <summary>
+/// Get table fields.
+/// </summary>
+/// <param name="dbPath"></param>
+/// <returns>List of table fields</returns>
+///---------------------------------------------------------------------
+public static List<string> GetDatabaseTables(string dbPath)
+{
+    List<string> result = new List<string>();
+    if (System.IO.File.Exists(dbPath))
+    {
+        string dbConnectionString = $"Data Source={dbPath};Version=3;";
+        SQLiteConnection conn = new SQLiteConnection(dbConnectionString);
+        conn.Open();
+        DataTable tbl = conn.GetSchema(
+            collectionName: "Tables",
+            restrictionValues: new string[] {
+                "main"       // catalog
+                //tableName  // including wildcards % or _
+            }
+        );
+        // Print table list
+        Console.WriteLine($"GetDatabaseTables(): Available tables:");
+        foreach (DataRow row in tbl.Rows)
+        {
+            Console.WriteLine($"- {row[2]}");  // table name
+            result.Add(row[2].ToString());
+            //Console.WriteLine($"- {row[6]}");  // table schema
+        }
+        conn.Close();
+    }
+    return result;
+}
+</code>
+==== Get Table Schema ====
+<code csharp>
+///---------------------------------------------------------------------
+/// <summary>
+/// Generate SQL CREATE TABLE statement.
+/// </summary>
+/// <param name="dbPath"></param>
+/// <param name="tableName"></param>
+/// <returns>string with schema</returns>
+///---------------------------------------------------------------------
+public static string GetTableSchema(string dbPath, string tableName)
+{
+    string result = "";
+    if (System.IO.File.Exists(dbPath))
+    {
+        string dbConnectionString = $"Data Source={dbPath};Version=3;";
+        SQLiteConnection conn = new SQLiteConnection(dbConnectionString);
+        conn.Open();
+        DataTable tbl = conn.GetSchema(
+            collectionName: "Tables",
+            restrictionValues: new string[] {
+                "main"       // catalog
+                //tableName  // including wildcards % or _
+            }
+        );
+        // Print table list
+        Console.WriteLine($"GetDatabaseTables(): Available tables:");
+        foreach (DataRow row in tbl.Rows)
+        {
+            if (tableName == $"{row[2]}")       // table name match
+            {
+                result = row[6].ToString();     // table schema
+            }
+        }
+        conn.Close();
+    }
+    return result;
+}
+///---------------------------------------------------------------------
+/// <summary>
+/// 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://www.devart.com/dotconnect/sqlite/docs/MetaData.html
+/// </summary>
+/// <param name="conn"></param>
+/// <param name="tableName"></param>
+///---------------------------------------------------------------------
+public static void GetCreateTable(SQLiteConnection conn, string tableName)
+{
+    // Open the connection
+    conn.Open();
+    // Fill DataTable with columns information
+    DataTable tbl = conn.GetSchema(
+        "Columns", new string[] { "main", tableName }
+    );
+    string sql = $"CREATE TABLE {tableName} (\n";
+    string fieldLine;
+    DataRow row;
+    // For every row in the table
+    for (int i = 0; i < tbl.Rows.Count; i++)
+    {
+        // Get column name and type
+        row = tbl.Rows[i];
+        fieldLine = row["Name"] + " " + row["TypeName"];
+        // Add comma or closing bracket
+        if (i < tbl.Rows.Count - 1)
+        {
+            fieldLine = fieldLine + ",\n";
+        }
+        else
+        {
+            fieldLine = fieldLine + ")";
+        }
+        // Add new column to script
+        sql += fieldLine;
+    }
+    Console.WriteLine(sql);
+    // Close the connection
+    conn.Close();
+}
+</code>
+==== Get Table Fields ====
+<code csharp>
+///---------------------------------------------------------------------
+/// <summary>
+/// Get table fields.
+/// </summary>
+/// <param name="dbPath"></param>
+/// <param name="tableName"></param>
+/// <param name="getAllSchemaData"></param>
+/// <returns>List of table fields</returns>
+///---------------------------------------------------------------------
+public static List<string> GetTableFields(string dbPath, string tableName, bool getAllSchemaData=false)
+{
+    List<string> result = new List<string>();
+    if (System.IO.File.Exists(dbPath))
+    {
+        string dbConnectionString = $"Data Source={dbPath};Version=3;";
+        SQLiteConnection conn = new SQLiteConnection(dbConnectionString);
+        conn.Open();
+        DataTable tbl = conn.GetSchema(
+            collectionName: "Columns",
+            restrictionValues: new string[] { "main", tableName }  // main & tableName
+        );
+        if (getAllSchemaData)
+        {
+            // Print schema column captions:
+            //   TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,COLUMN_GUID,COLUMN_PROPID,
+            //   ORDINAL_POSITION,COLUMN_HASDEFAULT,COLUMN_DEFAULT,COLUMN_FLAGS,IS_NULLABLE,DATA_TYPE,
+            //   TYPE_GUID,CHARACTER_MAXIMUM_LENGTH,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,
+            //   NUMERIC_SCALE,DATETIME_PRECISION,CHARACTER_SET_CATALOG,CHARACTER_SET_SCHEMA,
+            //   CHARACTER_SET_NAME,COLLATION_CATALOG,COLLATION_SCHEMA,COLLATION_NAME,DOMAIN_CATALOG,
+            //   DOMAIN_NAME,DESCRIPTION,PRIMARY_KEY,EDM_TYPE,AUTOINCREMENT,UNIQUE
+            for (int i = 0; i < tbl.Columns.Count; i++)
+            {
+                //Console.Write($"{tbl.Columns[i].ColumnName} ({tbl.Columns[i].DataType}),");
+                Console.Write(tbl.Columns[i].Caption + ",");
+            }
+            Console.WriteLine();
+            // Print table schema data. Eg: In table 'entry', field 'order_number':
+            // main,sqlite_default_schema,entry,order_number,,,1,True,0,,True,integer,,8,,19,0,,,,,,,BINARY,,,,False,integer,False,False
+            foreach (DataRow row in tbl.Rows)
+            {
+                // Print only field's column name and type
+                if (tableName == (string)row["TABLE_NAME"])
+                {
+                    // Print all field's metadata
+                    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's column name and type
+                if (tableName == (string)row["TABLE_NAME"])
+                {
+                    string fieldLine = $"### {row["COLUMN_NAME"]} ({row["DATA_TYPE"]})";
+                    Console.WriteLine(fieldLine);
+                    result.Add($"{row["COLUMN_NAME"]}");
+                }
+            }
+        }
+        conn.Close();
+    }
+    return result;
+}
+///---------------------------------------------------------------------
+/// <summary>
+/// Get table fields.
+/// </summary>
+/// <param name="dbPath"></param>
+/// <param name="tableName"></param>
+/// <param name="getAllSchemaData"></param>
+/// <returns>List of table fields</returns>
+///---------------------------------------------------------------------
+public static string GetTableFieldsAsCSV(string dbPath, string tableName, bool getAllSchemaData=false)
+{
+    List<string> lstFields = GetTableFields(dbPath, tableName, getAllSchemaData);
+    string fields = "";
+    foreach (string field in lstFields)
+    {
+        fields += $"{field},";
+    }
+    fields = fields.TrimEnd(',');
+    return fields;
+}
+///---------------------------------------------------------------------
+/// <summary>
+/// Retrieve columns information from a table and render it to console.
+/// Source: https://www.devart.com/dotconnect/sqlite/docs/MetaData.html
+/// </summary>
+/// <param name="conn"></param>
+/// <param name="tableName"></param>
+/// <returns>result with table fields</returns>
+///---------------------------------------------------------------------
+public static List<string> GetTableInfo(SQLiteConnection conn, string tableName)
+{
+    List<string> result = new List<string>();
+    conn.Open();
+    DataTable tbl = conn.GetSchema(
+        collectionName: "Columns",
+        restrictionValues: new string[] { "main", tableName }
+    );
+    for (int i = 0; i < tbl.Columns.Count; i++)
+    {
+        Console.Write(tbl.Columns[i].Caption + "\t");
+        result.Add(tbl.Columns[i].Caption);
+    }
+    Console.WriteLine();
+    foreach (DataRow row in tbl.Rows)
+    {
+        if (tableName == (string)row["TABLE_NAME"])
+        {
+            foreach (DataColumn col in tbl.Columns)
+            {
+                    Console.Write(row[col] + "\t");
+                    result.Add($"{row[col]}");
+            }
+            Console.WriteLine();
+        }
+    }
+    conn.Close();
+    return result;
+}
+</code>
 === Full Example ===
 <code csharp>
@@ Line 386: / Line 676: @@
 	                `brand`	            TEXT DEFAULT 'Audina',
 	                `product_code`	    TEXT,
-	                `product_data_hash`	TEXT,
+	                `product_data_hash` TEXT,
 	                `product_data`	    TEXT,
 	                `workstation`	    TEXT,
-	                `program_version`	TEXT,
+	                `program_version`   TEXT,
 	                `created_at`	    TEXT,
 	                `updated_at`	    TEXT
@@ Line 408: / Line 698: @@
                 //   "program_version"
-                string product_data = "";
+                string guid = Guid.NewGuid().ToString();
-                string fields = "id, order_number, serial_number, brand, product_code, product_data_hash, product_data, workstation, program_version, created_at, updated_at";
+                //string ProductDataEscaped = System.Security.SecurityElement.Escape(ProductData);
-                //string values = $"1, 11111, '18060466', 'Acme', 'prod_Vehicle', '3a4969c65f6dec10d56a9d6b63affe54', '{product_data}', 'QCPC2', '4.2.6670.18099', '2018-06-22 10:30:17', ''";
+                //string ProductDataEscaped = ProductData.Replace("&", "&amp;")
-                string values = $"null, 11112, '18208865', 'Acme', 'prod_Intuition2ER', '9ef7fb550acb27cc5033e37ef4f939bf', '{product_data}', 'QCPC2', '4.2.6670.18099', datetime('now', 'localtime'), ''";
+                //    .Replace("<", "&lt;").Replace(">", "&gt;").Replace("\"", "&quot;").Replace("'", "&apos;");
-                command.CommandText = $"INSERT INTO 'entry' ({fields}) VALUES ({values})";
+                //string ProductDataEscaped = ProductData.Replace("&", "&amp;")
+                //    .Replace("\"", "&quot;").Replace("'", "&apos;");
+                string ProductDataEscaped = ProductData.Replace("&", "&amp;");
+                string fields = "id, guid, order_number, serial_number, brand, product_code,
+                    product_data_hash, product_data, workstation, program_version,
+                    created_at, updated_at, is_submitted, is_deleted";
+                //string values = $"1, '2a202e53-fabf-43aa-a93c-bbfbcc65c572', 11111, '18060466', 'Acme',
+                //    'prod_Vehicle', '3a4969c65f6dec10d56a9d6b63affe54', '{ProductDataEscaped}', 'QCPC2',
+                //    'MyApp 4.2.6670.18099', '2018-06-22 10:30:17', ''";
+                string values = $"null, '{guid}', null, '{SerialNumber}', '{Brand}',
+                    '{ProductCode}', null, '{ProductDataEscaped}', '{Workstation}',
+                    'MyApp {TAppVersionBuild.VERSIONPUBLIC}.{TAppVersionBuild.VERSIONBUILD}',
+                    datetime('now', 'localtime'), '', 0, 0";
+                string sql    = $"INSERT INTO 'entry' ({fields}) VALUES ({values}) ";
+                SQLiteCommand command = new SQLiteCommand(sql, conn);
                 command.ExecuteNonQuery();
+                //sql = $"UPDATE 'entry' SET product_data = '{ProductDataEscaped}' WHERE guid = '{guid}'";
+                //command.CommandText = sql;
+                //command.ExecuteNonQuery();
                 conn.Close();
@@ Line 453: / Line 762: @@
         }
+        /// <summary>
+        /// SelectionChanged event handler for DataGrid.
+        /// </summary>
+        /// <param name="sender"></param>
+        /// <param name="e"></param>
         private void gridData_SelectionChanged(object sender, SelectionChangedEventArgs e)
         {