static void IdentityColumnValues()
 {
     using (SqlDatabaseConnection sqlcnn = new SqlDatabaseConnection("schemaName=db;uri=@memory"))
     {
         sqlcnn.Open();
         using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(sqlcnn))
         {
             cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable(Id Integer Primary Key AutoIncrement, Name Text);";
             cmd.ExecuteNonQuery();
             // Id should be one (1) after first insert
             cmd.CommandText = "INSERT INTO TestTable VALUES(null, 'Hello');";
             cmd.ExecuteNonQuery();
             //LastSequenceNumber requires table name
             Console.WriteLine(string.Format("via cmd.LastSequenceNumber: {0}", cmd.LastSequenceNumber("TestTable")));
             //LastInsertRowId is tracked on connection.
             Console.WriteLine(string.Format("via sqlcnn.LastInsertRowId: {0}", sqlcnn.LastInsertRowId));
             //last_insert_rowid() is tracked on connection and returns Int64
             cmd.CommandText = "SELECT last_insert_rowid()";
             Int64 LastID = (Int64)cmd.ExecuteScalar();
             Console.WriteLine(string.Format("via SQL: {0}", LastID));
             // Id should be two (2) after following insert.
             cmd.CommandText = "INSERT INTO TestTable(Name) VALUES('World');";
             cmd.ExecuteNonQuery();
             //LastSequenceNumber requires table name
             Console.WriteLine(string.Format("via cmd.LastSequenceNumber: {0}", cmd.LastSequenceNumber("TestTable")));
             //LastInsertRowId is tracked on connection.
             Console.WriteLine(string.Format("via sqlcnn.LastInsertRowId: {0}", sqlcnn.LastInsertRowId));
             //last_insert_rowid is tracked on connection SQL Statement
             cmd.CommandText = "SELECT last_insert_rowid()";
             LastID          = (Int64)cmd.ExecuteScalar();
             Console.WriteLine(string.Format("via SQL: {0}", LastID));
             //Attach another database file to same connection
             cmd.CommandText = "ATTACH DATABASE '@memory' AS 'db1'";
             cmd.ExecuteNonQuery();
             // Create table on schema db1
             cmd.CommandText = "CREATE TABLE IF NOT EXISTS db1.TestTable(Id Integer Primary Key AutoIncrement, Name Text);";
             cmd.ExecuteNonQuery();
             // Id should be one (1)
             cmd.CommandText = "INSERT INTO db1.TestTable VALUES(null, 'Hello');";
             cmd.ExecuteNonQuery();
             cmd.CommandText = "SELECT last_insert_rowid()";
             LastID          = (Int64)cmd.ExecuteScalar();
             Console.WriteLine(string.Format("via SQL from db1: {0}", LastID));
             cmd.CommandText = "SELECT * FROM db1.TestTable WHERE Id = last_insert_rowid()";
             SqlDatabaseDataReader dr = cmd.ExecuteReader();
             while (dr.Read())
             {
                 Console.WriteLine(dr["Name"]);
             }
         }
     }
 }
        public object Get(string CollectionName, string Key)
        {
            if (!TableExistsOrCreated)
            {
                OpenConnectionAndCreateTableIfNotExists();
            }

            if ((string.IsNullOrWhiteSpace(CollectionName)) || (string.IsNullOrWhiteSpace(Key)))
            {
                throw new Exception("CollectionName and Key are required.");
            }

            using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
            {
                cmd.CommandText = "SELECT Value FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1;";
                cmd.Parameters.AddWithValue("@CollectionName", CollectionName);
                cmd.Parameters.AddWithValue("@Key", Key);
                byte[] b = (byte[])cmd.ExecuteScalar();
                if (b != null)
                {
                    return(ObjectFromByteArray(b));
                }
                else
                {
                    return(b);
                }
            }
        }
예제 #3
0
        private void SimpleTransaction_Click(object sender, EventArgs e)
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();

                    SqlDatabaseTransaction trans = cnn.BeginTransaction();
                    cmd.Transaction = trans;

                    try
                    {
                        for (int i = 0; i < 1000; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                    } catch (SqlDatabaseException sqlex)
                    {
                        trans.Rollback();
                        Debug.WriteLine(sqlex.Message);
                    }
                    finally
                    {
                        trans.Commit();
                    }

                    cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
                    Debug.WriteLine(cmd.ExecuteScalar());
                }
            }
        }
        public int AddOrUpdate(string CollectionName, string Key, object Value)
        {
            int RowsAffected = -1;

            if (!TableExistsOrCreated)
            {
                OpenConnectionAndCreateTableIfNotExists();
            }

            if ((string.IsNullOrWhiteSpace(CollectionName)) || (string.IsNullOrWhiteSpace(Key)))
            {
                throw new Exception("CollectionName and Key are required.");
            }


            byte[] b = new byte[0];

            if (Value != null)
            {
                if (!(Value is byte[]))
                {
                    b = ObjectToByteArray(Value);
                }
                else
                {
                    b = (byte[])Value;
                }
            }

            using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
            {
                cmd.CommandText = "SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1;";
                cmd.Parameters.AddWithValue("@CollectionName", CollectionName);
                cmd.Parameters.AddWithValue("@Key", Key);
                cmd.Parameters.AddWithValue("@Value", b);
                object ObjRowId = cmd.ExecuteScalar();

                lock (LockObject)
                {
                    if (ObjRowId != null)
                    {
                        int Id = int.Parse(ObjRowId.ToString());

                        cmd.CommandText = "UPDATE KeyValueStore SET Value = @Value WHERE RowId = (SELECT RowId FROM KeyValueStore WHERE CollectionName = @CollectionName AND Key = @Key LIMIT 1);";
                        RowsAffected    = cmd.ExecuteNonQuery();
                    }
                    else
                    {
                        cmd.CommandText = "INSERT INTO KeyValueStore(CollectionName, Key, Value) VALUES(@CollectionName, @Key, @Value);";
                        RowsAffected    = cmd.ExecuteNonQuery();
                    }
                }
            }

            return(RowsAffected);
        }
예제 #5
0
        private void ToInMemoryDatabase_Click(object sender, EventArgs e)
        {
            //Connection to physical database file
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://" + ExampleDatabaseFile))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT COUNT(*) FROM db.SYS_OBJECTS;"; // db.SYS_OBJECTS is SchemaName.ObjectName
                    Debug.WriteLine("Object Count in db: " + cmd.ExecuteScalar());


                    cmd.CommandText = "ATTACH Database '@memory' AS 'memdb1' ; ";  //Attach new database schema with name memdb1
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "BACKUP Database 'db' AS 'memdb1' ;";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT COUNT(*) FROM memdb1.SYS_OBJECTS;";
                    Debug.WriteLine("Object Count in memdb1: " + cmd.ExecuteScalar());

                    // To Save In memory database to file take backup to disk.
                    string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "ExampleBackup.db");
                    if (File.Exists(dbfilepath))
                    {
                        File.Delete(dbfilepath);
                    }

                    cmd.CommandText = "BACKUP Database 'memdb1' AS '" + dbfilepath + "' ;";
                    cmd.ExecuteNonQuery();

                    if (File.Exists(dbfilepath))
                    {
                        Debug.WriteLine(string.Format("Backup file created at {0}", dbfilepath));
                    }
                    else
                    {
                        Debug.WriteLine(cmd.GetLastError());
                    }
                }
            }
        }
        static void SIUDOperations()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;")) // In Memory database.
            {
                cnn.Open();

                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
                {
                    cmd.Connection  = cnn;
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable (Username TEXT PRIMARY KEY, FirstName TEXT, LastName TEXT);";
                    cmd.ExecuteNonQuery();

                    // INSERT
                    cmd.CommandText = "INSERT INTO TestTable VALUES ('jdoe', 'John' , 'DOE');";
                    cmd.ExecuteNonQuery();

                    // SELECT - Load DataTable
                    DataTable dt = new DataTable();
                    cmd.CommandText = "SELECT Username, FirstName, LastName FROM TestTable;";
                    using (SqlDatabaseDataAdapter da = new SqlDatabaseDataAdapter())
                    {
                        da.SelectCommand = cmd;
                        da.Fill(dt);
                    }
                    if (dt.Rows.Count > 0)
                    {
                        Console.WriteLine(string.Format("Total Rows {0}", dt.Rows.Count));
                    }

                    // UPDATE
                    cmd.CommandText = "UPDATE TestTable SET LastName = 'Doe' WHERE Username = '******'; ";
                    cmd.ExecuteNonQuery();

                    // DELETE
                    cmd.CommandText = "DELETE FROM TestTable WHERE Username = '******'; ";
                    cmd.ExecuteNonQuery();


                    // TRUNCATE - Library does not support truncate but it can be achived by recreating the table
                    cmd.CommandText = "SELECT sqltext FROM SYS_OBJECTS Where type = 'table' AND tablename = 'TestTable' LIMIT 1;";
                    object TableSQLText = cmd.ExecuteScalar();
                    if (!string.IsNullOrWhiteSpace(TableSQLText.ToString()))
                    {
                        cmd.CommandText = "DROP TABLE IF EXISTS TestTable;";
                        cmd.ExecuteNonQuery();
                        // Now recreate the table....
                        cmd.CommandText = TableSQLText.ToString();
                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }
        static void SimpleTransaction()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();

                    SqlDatabaseTransaction trans = cnn.BeginTransaction();
                    cmd.Transaction = trans;

                    try
                    {
                        for (int i = 0; i < 1000; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'A Value" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                        trans.Commit(); //Commit since all inserts are completed.

                        //Test if transaction is open
                        if (trans.IsOpen)
                        {
                            trans.Commit();
                        }
                    }
                    catch (SqlDatabaseException sqlex)
                    {
                        //Rollback as their was an error.
                        if (trans.IsOpen)
                        {
                            trans.Rollback();
                        }
                        Console.WriteLine(sqlex.Message);
                    }

                    cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
                    Console.WriteLine("Table Record Count using COUNT(*) : {0}", cmd.ExecuteScalar());

                    // Pure SQL Way of starting and committing transaction.
                    cmd.CommandText = "BEGIN";
                    cmd.ExecuteNonQuery();
                    // Your SQL Statements and commands can reside here to run with-in a transaction.
                    // INSERT UPDATE and DELETE
                    cmd.CommandText = "COMMIT"; //ROLLBACK
                    cmd.ExecuteNonQuery();
                }
            }
        }
예제 #8
0
        /// <summary>
        /// <see cref="IDataService.GetPodcastID(string)"/>
        /// </summary>
        /// <param name="shortUrl"></param>
        /// <returns></returns>
        public int?GetPodcastID(string shortUrl)
        {
            Podcast podcast = _podcasts?.FirstOrDefault(i => i.PodcastKey == shortUrl);

            if (podcast != null)
            {
                return(podcast.PodcastID);
            }
            else
            {
                using (SqlDatabaseCommand cmd = this.GetDatabaseCommand())
                {
                    cmd.CommandText.AppendLine(" SELECT PodcastID FROM Podcast WHERE PodcastKey = @Key ");
                    cmd.Parameters.AddWithValue("@Key", shortUrl);
                    return(cmd.ExecuteScalar <int?>());
                }
            }
        }
예제 #9
0
        private void IndexAndVacuum_Click(object sender, EventArgs e)
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + ExampleDatabaseFile))
            {
                cnn.Open();
                //CREATE INDEX IndexName ON TableName (Columns...)

                // Also see online documentation
                // http://www.sqldatabase.net/docs/create-index.aspx

                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "ReIndex ; "; // Rebuild all indexes on all tables.
                    cmd.ExecuteNonQuery();
                }

                // After large delete or dropping of large table Vacuum will rearrange space.
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "VACUUM ; "; // Rearrange database pages
                    cmd.ExecuteNonQuery();
                }

                // Integrity Check in case something else write to file or any other issues.
                // If integrity check is not equals to SQLDATABASE_OK then it can be fixed by rebuilding indexes.
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SYSCMD Integrity_Check ; ";
                    if (!cmd.ExecuteScalar().Equals("SQLDATABASE_OK"))
                    {
                        cmd.CommandText = "ReIndex ; VACUUM ;";
                        cmd.ExecuteNonQuery();
                    }
                }


                // Not required since dispose also closes the connection
                if (cnn.State != ConnectionState.Closed)
                {
                    cnn.Close();
                }
            }
        }
예제 #10
0
        public static string GetCreateSql(ISqlDataObject templateTable)
        {
            var createSql = "DROP TABLE IF EXISTS " + templateTable.Name + ";\n" +
                            "CREATE TABLE " + templateTable.Name + "\n(";

            if (templateTable.GetType() == typeof(SqlDataView))
            {
                createSql = "DROP VIEW IF EXISTS " + templateTable.Name + ";\r\n";
                using (var con = new SqlDatabaseConnection(templateTable.ConnectionString))
                {
                    con.Open();
                    try
                    {
                        var sql = string.Format("SELECT sqltext FROM SYS_OBJECTS WHERE\n" +
                                                "type = 'view' \n" +
                                                "AND name = '{0}'", templateTable.Name);
                        using (var cmd = new SqlDatabaseCommand(sql, con))
                        {
                            createSql += Convert.ToString(cmd.ExecuteScalar());
                        }
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        con.Close();
                    }
                }

                return(createSql);
            }

            var fields = new List <string>();

            foreach (var col in templateTable.Columns.Where(o => !string.IsNullOrEmpty(o.Name)))
            {
                var s = "\n" + col.Name + " " + col.Type;
                if (!col.Nullable || col.IsPKey)
                {
                    s += " NOT NULL";
                }
                if (col.IsPKey)
                {
                    s += " PRIMARY KEY";
                }
                if (col.IsPKey && col.AutoInc)
                {
                    s += " AUTOINCREMENT ";
                }
                var sDefault = Convert.ToString(col.DefaultValue);
                if (sDefault == "''")
                {
                    sDefault = string.Empty;
                }

                if (!col.AutoInc && col.DefaultValue != null && col.DefaultValue != DBNull.Value && !string.IsNullOrEmpty(sDefault))
                {
                    s += " DEFAULT ";
                    if (col.Type.ToUpper() == "TEXT" && !sDefault.StartsWith("'"))
                    {
                        sDefault = "'" + sDefault + "'";
                    }

                    s += sDefault;
                }

                fields.Add(s);
            }

            createSql += string.Join(",", fields);
            createSql += "\n)";

            return(createSql.Replace("\n", "\r\n"));
        }
예제 #11
0
        static void EncryptionDecryption()
        {
            Console.WriteLine("*************** Encrypted File Example *******************");
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://Encrypted.db;"))
            {
                cnn.Open();

                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    //Entire Database File will be encrypted using AES 256
                    cmd.CommandText = "SYSCMD Key='SecretPassword';";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "Create table if not exists Users(id integer primary key autoincrement, Username Text, Password Text); ";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO Users values(NULL, @username, @password);";
                    cmd.Parameters.AddWithValue("@username", "sysdba");
                    cmd.Parameters.AddWithValue("@password", "SecretPassword");
                    cmd.ExecuteNonQuery();
                }
            }


            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://Encrypted.db;"))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    //Entire Database File will be encrypted using AES 256
                    cmd.CommandText = "SYSCMD Key = 'SecretPassword'; ";  //If incorrect password library will not respond.
                    cmd.ExecuteNonQuery();

                    // COLLATE BINARY performs case sensitive search for password
                    // see http://www.sqldatabase.net/docs/syscmd.aspx for available collation sequences.

                    cmd.CommandText = "SELECT Id FROM Users WHERE Username = @username AND Password = @password COLLATE BINARY;";
                    cmd.Parameters.AddWithValue("@username", "sysdba");
                    cmd.Parameters.AddWithValue("@password", "SecretPassword");
                    Console.WriteLine("User Found {0}", cmd.ExecuteScalar() == null ? "No" : "Yes");
                }
            }

            Console.Write(string.Empty);
            Console.WriteLine("*************** Encrypted Column Example *******************");

            string RandomUserName = "******" + System.IO.Path.GetRandomFileName();

            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://EncryptedColumn.db;"))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS UsersCreditCards(Name Text Primary Key, CreditCardNumber Text); ";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO UsersCreditCards values(@Name, EncryptText(@CreditCardNumber , 'SecretPassword'));";
                    cmd.Parameters.AddWithValue("@Name", RandomUserName);
                    cmd.Parameters.AddWithValue("@CreditCardNumber", "1234-5678");
                    cmd.ExecuteNonQuery();
                }
            }

            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;uri=file://EncryptedColumn.db;"))
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT DecryptText(CreditCardNumber , 'SecretPassword') AS [CreditCardNumber] FROM UsersCreditCards WHERE Name = @Name LIMIT 1;";
                    cmd.Parameters.AddWithValue("@Name", RandomUserName);
                    Console.WriteLine("User {0} Credit Card Number is : {1}", RandomUserName, cmd.ExecuteScalar());

                    Console.WriteLine("*************** All Users *******************");
                    cmd.CommandText = "SELECT Name, DecryptText(CreditCardNumber , 'SecretPassword') AS CreditCardNumber FROM UsersCreditCards;";
                    SqlDatabaseDataReader dr = cmd.ExecuteReader();
                    for (int c = 0; c < dr.VisibleFieldCount; c++)
                    {
                        Console.Write(dr.GetName(c) + "\t");
                    }
                    Console.WriteLine(Environment.NewLine + "----------------------");
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine("");
                    }
                    ;
                }
            }

            if (File.Exists("Encrypted.db"))
            {
                File.Delete("Encrypted.db");
            }

            if (File.Exists("EncryptedColumn.db"))
            {
                File.Delete("EncryptedColumn.db");
            }
        }
예제 #12
0
        static void MultipleCommandTransaction()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();

                // Create a table
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "Create Table If not exists temptable(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "Create Table If not exists temptable2(Id Integer, TextValue Text) ; ";
                    cmd.ExecuteNonQuery();
                }

                // Start a transaction on this connection
                SqlDatabaseTransaction trans = cnn.BeginTransaction();

                try
                {
                    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                    {
                        cmd.Transaction = trans; // attach this Command object to transaction.

                        for (int i = 0; i < 10; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable VALUES (" + i + ", 'AValue" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                    }

                    // Other processes can run here.
                    // Transaction stays active even after command object is closed and can be attached to other objects.

                    //Create another command object and insert in temptable2 using same transaction.
                    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                    {
                        cmd.Transaction = trans; // attach this Command object to transaction.

                        for (int i = 0; i < 10; i++)
                        {
                            cmd.CommandText = "INSERT INTO temptable2 VALUES (" + i + ", 'AValue" + i + "');";
                            cmd.ExecuteNonQuery();
                        }
                    }

                    trans.Commit();
                }
                catch (SqlDatabaseException sqlex)
                {
                    trans.Rollback();
                    Console.WriteLine(sqlex.Message);
                }

                // Let's check the record count.
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT COUNT(*) FROM temptable;";
                    Console.WriteLine("Record Count temptable : {0}", cmd.ExecuteScalar());
                    cmd.CommandText = "SELECT COUNT(*) FROM temptable2;";
                    Console.WriteLine("Record Count temptable2 : {0}", cmd.ExecuteScalar());
                }
            }
        }
예제 #13
0
        private void ParallelInsert_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
            {
                return;
            }

            string[] Files = Directory.GetFiles(Path.Combine(Directory.GetParent(Path.GetDirectoryName(Application.ExecutablePath)).Parent.FullName, "csv"), "*.csv");

            // Example use @memory but parallel insert can be against any database schema files
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();

                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText  = "CREATE TABLE Transactions (StreetAddress Text, City Text, Zip Text, State Text, Beds Integer, Baths Integer";
                    cmd.CommandText += ", SQFT Text, PropertyType Text, SaleDate Text, Price Real, Latitude Text, Longitude Text);";
                    cmd.ExecuteNonQuery();
                }


                Parallel.For(0, Files.Length, i =>
                {
                    string[] FileLines = File.ReadAllLines(Files[i]);
                    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
                    {
                        cmd.Connection  = cnn;
                        cmd.CommandText = "INSERT INTO Transactions VALUES (@StreetAddress, @City, @Zip, @State, @Beds, @Baths, @SQFT, @PropertyType, @SaleDate, @Price, @Latitude, @Longitude); ";

                        //Add Query Parameters if they don't exists.
                        if (!cmd.Parameters.Contains("@StreetAddress"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@StreetAddress"
                            });
                        }
                        if (!cmd.Parameters.Contains("@City"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@City"
                            });
                        }
                        if (!cmd.Parameters.Contains("@Zip"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@Zip"
                            });
                        }
                        if (!cmd.Parameters.Contains("@State"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@State"
                            });
                        }
                        if (!cmd.Parameters.Contains("@Beds"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@Beds"
                            });
                        }
                        if (!cmd.Parameters.Contains("@Baths"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@Baths"
                            });
                        }
                        if (!cmd.Parameters.Contains("@SQFT"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@SQFT"
                            });
                        }
                        if (!cmd.Parameters.Contains("@PropertyType"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@PropertyType"
                            });
                        }
                        if (!cmd.Parameters.Contains("@SaleDate"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@SaleDate"
                            });
                        }
                        if (!cmd.Parameters.Contains("@Price"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@Price"
                            });
                        }
                        if (!cmd.Parameters.Contains("@Latitude"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@Latitude"
                            });
                        }
                        if (!cmd.Parameters.Contains("@Longitude"))
                        {
                            cmd.Parameters.Add(new SqlDatabaseParameter {
                                ParameterName = "@Longitude"
                            });
                        }


                        for (int l = 0; l < FileLines.Length; l++)
                        {
                            string[] Values = FileLines[l].Split(','); //split line based on comma

                            //// Method 1 starts here
                            cmd.Parameters["@StreetAddress"].Value = Values[0];
                            cmd.Parameters["@City"].Value          = Values[1];
                            cmd.Parameters["@Zip"].Value           = Values[2];
                            cmd.Parameters["@State"].Value         = Values[3];
                            cmd.Parameters["@Beds"].Value          = Values[4];
                            cmd.Parameters["@Baths"].Value         = Values[5];
                            cmd.Parameters["@SQFT"].Value          = Values[6];
                            cmd.Parameters["@PropertyType"].Value  = Values[7];
                            cmd.Parameters["@SaleDate"].Value      = Values[8];
                            cmd.Parameters["@Price"].Value         = Values[9];
                            cmd.Parameters["@Latitude"].Value      = Values[10];
                            cmd.Parameters["@Longitude"].Value     = Values[11];

                            cmd.ExecuteNonQuery();
                            ////Method 1 ends here

                            ////Method 2 starts here
                            ////Parameters can be acccessed via index to shorten the code
                            ////Parameters must be created in correct order and their count must match with values from file.
                            ////Comment the above code if you want to test following code.
                            //for (int v = 0; v < Values.Length; v++)
                            //{
                            //    cmd.Parameters[v].Value = Values[v];
                            //}
                            //cmd.ExecuteNonQuery();
                            ////Method 2 ends here
                        }
                    }

                    Debug.WriteLine("Finished Thread {0} with total lines {1} from file {2}", Thread.CurrentThread.ManagedThreadId, FileLines.Length, Files[i]);
                });
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT Count(*) as [TotalTransactions] FROM Transactions;";
                    Debug.WriteLine("Total Transactions {0}", cmd.ExecuteScalar());
                }
                Debug.WriteLine("Finished All Files");
            }
        }