Differences

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

--- swdev:sqlite:custom_client_in_csharp [2018/07/12 10:40]
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 ====
 <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 144: / Line 146: @@
 /// <param name="dbPath"></param>
 /// <param name="tableName"></param>
-/// <returns>List of table fields</returns>
+/// <returns>string with schema</returns>
 ///---------------------------------------------------------------------
 public static string GetTableSchema(string dbPath, string tableName)
@@ Line 177: / Line 179: @@
         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;