Differences

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

--- swdev:sqlite:custom_client_in_csharp [2018/07/12 10:46]
smayr [Get Table Fields]
+++ 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 179: / Line 181: @@
     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>
@@ Line 286: / Line 336: @@
     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>