Differences

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

Link to this comparison view

Both sides previous revision Previous revision
Next revision 		Previous revision
swdev:sqlite:custom_client_in_csharp [2018/06/25 14:19]
smayr 		swdev:sqlite:custom_client_in_csharp [2018/07/12 10:52] (current)
smayr [Insert Table Entries]
Line 1: Line 1:
-= Custom client in C# =+= Custom SQLite Client in C# =
  
 == Setup == == Setup ==
Line 54: Line 54:
 SQLiteConnection conn = new SQLiteConnection(DatabaseSource) SQLiteConnection conn = new SQLiteConnection(DatabaseSource)
 conn.Open(); conn.Open();
 +//...
 +conn.Close();
 </code> </code>
 See more: [[https://www.connectionstrings.com/sqlite/|SQLite connection strings]] See more: [[https://www.connectionstrings.com/sqlite/|SQLite connection strings]]
Line 69: Line 71:
  
 // Method 2 // Method 2
 +SQLiteCommand command = new SQLiteCommand(conn);
 command.CommandText = sql; command.CommandText = sql;
 command.ExecuteNonQuery(); command.ExecuteNonQuery();
Line 75: Line 78:
 ==== Insert Table Entries ==== ==== Insert Table Entries ====
 <code csharp> <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(); command.ExecuteNonQuery();
 +Console.WriteLine("NewRecID: " + conn.LastInsertRowId);
 </code> </code>
  
Line 89: Line 94:
 </code> </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 === === Full Example ===
 <code csharp> <code csharp>
Line 325: Line 616:
     public partial class MainWindow : Window     public partial class MainWindow : Window
     {     {
-        private string dbConnectionString = @"Data Source=C:\TEMP\data.db;Version=3;";+        private string dbFile = @"C:\TEMP\data.db"; 
 +        private string dbConnectionString = "Version=3;";
  
 +        /// <summary>
 +        /// Main routine.
 +        /// </summary>
         public MainWindow()         public MainWindow()
         {         {
             InitializeComponent();             InitializeComponent();
 +
 +            this.dbConnectionString = $"Data Source={dbFile};Version=3;";
 +        }
 +
 +        /// <summary>
 +        /// Click event handler for btnCreateDB.
 +        /// </summary>
 +        /// <param name="sender"></param>
 +        /// <param name="e"></param>
 +        private void btnCreateDB_Click(object sender, RoutedEventArgs e)
 +        {
 +            ResetDatabase(this.dbConnectionString);
         }         }
  
 +        /// <summary>
 +        /// Click event handler for btnConnect.
 +        /// </summary>
 +        /// <param name="sender"></param>
 +        /// <param name="e"></param>
         private void btnConnect_Click(object sender, RoutedEventArgs e)         private void btnConnect_Click(object sender, RoutedEventArgs e)
         {         {
Line 343: Line 655:
         }         }
  
 +        /// <summary>
 +        /// Reset database to original state.
 +        /// </summary>
 +        /// <param name="connectionString"></param>
 +        private void ResetDatabase(string connectionString)
 +        {
 +            // Create database
 +            try
 +            {
 +                SQLiteConnection.CreateFile(this.dbFile);
 +
 +                SQLiteConnection conn = new SQLiteConnection(connectionString);
 +                conn.Open();
 +
 +                // Create table 'entry'
 +                string sql = @"CREATE TABLE IF NOT EXISTS `entry` (
 +                 `id`             INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
 +                 `order_number`     INTEGER DEFAULT 0,
 +                 `serial_number`     TEXT DEFAULT 0,
 +                 `brand`             TEXT DEFAULT 'Audina',
 +                 `product_code`     TEXT,
 +                 `product_data_hash` TEXT,
 +                 `product_data`     TEXT,
 +                 `workstation`     TEXT,
 +                 `program_version`   TEXT,
 +                 `created_at`     TEXT,
 +                 `updated_at`     TEXT
 +                );";
 +
 +                SQLiteCommand command = new SQLiteCommand(sql, conn);
 +                command.ExecuteNonQuery();
 +
 +                // Insert sample table entries
 +                //
 +                // Required fields:
 +                //   "brand"
 +                //   "order_number"
 +                //   "serial_number"
 +                //   "product_code"
 +                //   "product_data"
 +                //   "workstation"
 +                //   "program_version"
 +
 +                string guid = Guid.NewGuid().ToString();
 +                //string ProductDataEscaped = System.Security.SecurityElement.Escape(ProductData);
 +                //string ProductDataEscaped = ProductData.Replace("&", "&amp;")
 +                //    .Replace("<", "&lt;").Replace(">", "&gt;").Replace("\"", "&quot;").Replace("'", "&apos;");
 +                //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();
 +
 +                MessageBox.Show($"Created database {dbFile}.", 
 +                    $"Database {dbFile}", 
 +                    MessageBoxButton.OK, MessageBoxImage.Information
 +                );
 +            }
 +            catch (Exception exc)
 +            {
 +                MessageBox.Show($"Attempting to create a database {dbFile}.\r\n\r\nException: {exc.Message}", 
 +                    "Exception", MessageBoxButton.OK, MessageBoxImage.Error
 +                );
 +            }
 +        }
 +
 +        /// <summary>
 +        /// Update DataGrid to show existing table data.
 +        /// </summary>
 +        /// <param name="conn"></param>
 +        /// <param name="sql"></param>
         private void UpdateDataGrid(SQLiteConnection conn, string sql)         private void UpdateDataGrid(SQLiteConnection conn, string sql)
         {         {
Line 359: Line 760:
  
             dataAdapter.Update(tbl);             dataAdapter.Update(tbl);
 +        }
 +
 +        /// <summary>
 +        /// SelectionChanged event handler for DataGrid.
 +        /// </summary>
 +        /// <param name="sender"></param>
 +        /// <param name="e"></param>
 +        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      = row.Row["id"].ToString();
 +            string order_number  = row.Row["order_number"].ToString();
 +            string serial_number = row.Row["serial_number"].ToString();
 +            string brand         = row.Row["brand"].ToString();
 +
 +            MessageBox.Show($"entry_id={entry_id}\r\norder_number={order_number}\r\nserial_number={serial_number}\r\nbrand={brand}\r\n", "Record Details", MessageBoxButton.OK, MessageBoxImage.Information);
         }         }
     }     }
swdev/sqlite/custom_client_in_csharp.1529950774.txt.gz · Last modified: 2018/06/25 14:19 by smayr
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Share Alike 4.0 International


  • Show page
  • Old revisions
  • Backlinks
  • Recent Changes
  • Media Manager
  • Sitemap