static void EXECUDFExample()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + ExampleDatabaseFile + "; "))
            {
                cnn.Open();
                // Available from version 2.7
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
                {
                    cmd.Connection = cnn;

                    // SELECT - ExecScalarUDF
                    // ExecScalarUDF is called once for each row for each call.
                    // Following will call the event handler twice as ExecScalarUDF is called twice.
                    DataTable dt = new DataTable();
                    cmd.CommandText = "SELECT *, ExecScalarUDF('Exec1', Phone) AS ANumber, ExecScalarUDF('Exec2', 1, 2, @Param1, FirstName ) AS ExampleText FROM Customers LIMIT 10;";
                    cmd.Parameters.AddWithValue("@Param1", 3);
                    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("");
                    }
                    ;
                }
            }
        }
        public Dictionary <string, object> GetAll(string CollectionName)
        {
            Dictionary <string, object> KeyValuePairs = new Dictionary <string, object>();

            if (!TableExistsOrCreated)
            {
                OpenConnectionAndCreateTableIfNotExists();
            }

            if (string.IsNullOrWhiteSpace(CollectionName))
            {
                throw new Exception("CollectionName is required.");
            }

            using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
            {
                cmd.CommandText = "SELECT Key, Value FROM KeyValueStore WHERE CollectionName = @CollectionName;";
                cmd.Parameters.AddWithValue("@CollectionName", CollectionName);
                SqlDatabaseDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    byte[] b = (byte[])dr["Value"];
                    if (b != null && b.Length > 1)
                    {
                        KeyValuePairs[dr["Key"].ToString()] = ObjectFromByteArray(b);
                    }
                    else
                    {
                        KeyValuePairs[dr["Key"].ToString()] = null;
                    }
                }
            }

            return(KeyValuePairs);
        }
Exemplo n.º 3
0
        static void AddImage()
        {
            // Following example code inserts and retrive files/images from database
            Console.WriteLine("Example: How to add images or files in database.");
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("SchemaName=db;Uri=@memory")) //We will strore in memory for the example
            {
                cnn.Open();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "CREATE TABLE Images (Id Integer Primary Key, FileName Text, ActualImage BLOB);";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO Images VALUES(@Id, @FileName, @Image);";
                    cmd.Parameters.Add("@Id");
                    cmd.Parameters.Add("@FileName");
                    cmd.Parameters.Add("@Image");

                    //Read the file
                    if (File.Exists("ImageFile.png"))
                    {
                        byte[] byteArray = File.ReadAllBytes("ImageFile.png");
                        // Assign values to parameters.
                        cmd.Parameters["@Id"].Value       = 1;
                        cmd.Parameters["@FileName"].Value = "ImageFile.png";
                        cmd.Parameters["@Image"].Value    = byteArray;
                        cmd.ExecuteNonQuery(); // Execute insert query

                        Console.WriteLine("Image / File example read..");
                        cmd.CommandText = "SELECT FileName , ActualImage FROM Images WHERE Id = 1 LIMIT 1;";
                        SqlDatabaseDataReader dr = cmd.ExecuteReader();
                        while (dr.HasRows)
                        {
                            while (dr.Read())
                            {
                                // Create Random file name so we won't overwrite the original actual file.
                                string NewFileName = Path.GetRandomFileName() + dr["FileName"].ToString();
                                byte[] FileBytes   = (byte[])dr.GetValue(dr.GetOrdinal("ActualImage"));
                                File.WriteAllBytes(NewFileName, FileBytes);
                                if (File.Exists(NewFileName))
                                {
                                    Console.WriteLine("File {0} created successfully.", NewFileName);
                                }
                                else
                                {
                                    Console.WriteLine("Unable to create file {0}.", NewFileName);
                                }
                            }
                        }
                    }
                    else
                    {
                        Console.WriteLine("File ImageFile.png not found.");
                    }
                }
            }
        }
 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"]);
             }
         }
     }
 }
Exemplo n.º 5
0
        private void MultiRead_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrWhiteSpace(ExampleDatabaseFile))
            {
                return;
            }

            //build connection string
            cb.Clear(); //clear any existing settings.
            cb.Uri        = ExampleDatabaseFile;
            cb.SchemaName = "db";


            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(cb.ConnectionString))
            {
                cnn.Open();

                Parallel.For(0, Environment.ProcessorCount, new ParallelOptions {
                    MaxDegreeOfParallelism = Environment.ProcessorCount
                }, i => {
                    try
                    {
                        using (SqlDatabaseCommand command = new SqlDatabaseCommand())
                        {
                            command.Connection       = cnn;
                            command.CommandText      = "SELECT ProductId, ProductName FROM Products ORDER BY ProductId LIMIT 10 OFFSET " + (10 * i) + ";";
                            SqlDatabaseDataReader rd = command.ExecuteReader();
                            while (rd.Read())
                            {
                                Debug.Write(Thread.CurrentThread.ManagedThreadId + "\t");
                                for (int c = 0; c < rd.VisibleFieldCount; c++)
                                {
                                    Debug.Write(rd.GetValue(c) + "\t");
                                }
                                Debug.WriteLine("");
                            }
                        }
                    }
                    catch (Exception ex)
                    {
                        Debug.WriteLine(ex.Message);
                    }
                });
            }
        }
Exemplo n.º 6
0
        public int ExportTable(string FilePathAndName, bool AppendToFile = false)
        {
            int _row_count = 0;

            using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
            {
                if (SQLDatabaseTransaction != null)
                {
                    cmd.Transaction = SQLDatabaseTransaction;
                }

                cmd.CommandText = string.Format("SELECT * FROM [{0}].[{1}]", SchemaName, TableName);
                using (CsvWriter = new CsvFileWriter(FilePathAndName, AppendToFile, Encoding.UTF8))
                {
                    SqlDatabaseDataReader dataReader  = cmd.ExecuteReader();
                    List <string>         ColumnNames = new List <string>();
                    // Write header i.e. column names
                    for (int i = 0; i < dataReader.VisibleFieldCount; i++)
                    {
                        if (dataReader.GetFieldType(i) != Type.GetType("byte[]")) // BLOB will not be written
                        {
                            ColumnNames.Add(dataReader.GetName(i));               //maintain columns in the same order as the header line.
                            CsvWriter.AddField(dataReader.GetName(i));
                        }
                    }
                    CsvWriter.SaveAndCommitLine();
                    // Write data i.e. rows.
                    while (dataReader.Read())
                    {
                        foreach (string ColumnName in ColumnNames)
                        {
                            CsvWriter.AddField(dataReader.GetString(dataReader.GetOrdinal(ColumnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
                        }
                        CsvWriter.SaveAndCommitLine();
                        _row_count++; //Increase row count to track number of rows written.
                    }
                }
            }

            return(_row_count);
        }
Exemplo n.º 7
0
        public long ExportTable(string filePathAndName, bool appendToFile = false)
        {
            SQLDatabaseConnection.Open();
            long rowCount = 0;

            using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(SQLDatabaseConnection))
            {
                if (SQLDatabaseTransaction != null)
                {
                    cmd.Transaction = SQLDatabaseTransaction;
                }

                cmd.CommandText = $@"SELECT * FROM [{SchemaName}].[{TableName}]";
                using (DelimitedWriter = new DelimitedFileWriter(filePathAndName, appendToFile, Encoding.UTF8))
                {
                    // set variables
                    DelimitedWriter.Delimiter = Delimiter;
                    DelimitedWriter.QuoteWrap = ReplicationFormData.QuoteWrap;
                    DelimitedWriter.NullValue = ReplicationFormData.NullValue;

                    // write custom header to file if not empty
                    if (!string.IsNullOrWhiteSpace(ReplicationFormData.CustomHeader))
                    {
                        DelimitedWriter.WriteLineToFile(ReplicationFormData.CustomHeader);
                    }

                    SqlDatabaseDataReader dataReader  = cmd.ExecuteReader();
                    List <string>         columnNames = new List <string>();
                    // Write header i.e. column names
                    for (int i = 0; i < dataReader.VisibleFieldCount; i++)
                    {
                        var name = dataReader.GetName(i);
                        if (dataReader.GetFieldType(i) != Type.GetType("byte[]") &&
                            name != Constants.ReplicationRecordId &&
                            name != Constants.ReplicationVersionIds &&
                            name != Constants.ReplicationVersionRecordId) // BLOB will not be written
                        {
                            columnNames.Add(name);                        //maintain columns in the same order as the header line.
                            DelimitedWriter.AddField(name);
                        }
                    }

                    DelimitedWriter.SaveAndCommitLine();
                    // Write data i.e. rows.
                    while (dataReader.Read())
                    {
                        foreach (string columnName in columnNames)
                        {
                            DelimitedWriter.AddField(
                                dataReader.GetString(
                                    dataReader.GetOrdinal(
                                        columnName))); //dataReader.GetOrdinal(ColumnName) provides the position.
                        }

                        DelimitedWriter.SaveAndCommitLine();
                        rowCount++; //Increase row count to track number of rows written.
                    }
                }
            }

            return(rowCount);
        }
Exemplo n.º 8
0
        static void LoadDepartments()
        {
            Console.WriteLine("Loading Departments example..");
            Console.WriteLine();

            //Initalize ORM client with Departments object
            SqlDatabaseOrmClient <Departments> depts = new SqlDatabaseOrmClient <Departments>(InMemoryConnection);

            depts.CreateTable();//Create table departments if it does not exists.

            // Add department one by one
            // We do not need to provide departmentid as it is autogenerated.
            Departments dept = new Departments(); //Create new instance of Departments class

            dept.DepartmentName = "Administration";
            depts.Add(dept);

            dept = new Departments
            {
                DepartmentName = "Sales & Marketing"
            };
            depts.Add(dept);

            //Following will produce error due to DepartmentName may not be null.
            try
            {
                dept = new Departments
                {
                    DepartmentName = null
                };
                depts.Add(dept);
            }
            catch (Exception e)
            {
                Console.WriteLine("Error due to null: {0}", e.Message);
                Console.WriteLine();
            }
            // End of Add department one by one

            // Adding multiple departments using list
            List <Departments> departmentslist = new List <Departments>();

            departmentslist.Add(new Departments()
            {
                DepartmentName = "HR"
            });
            departmentslist.Add(new Departments()
            {
                DepartmentName = "Information Technology"
            });
            depts.AddRange(departmentslist);

            // Get all records.
            Console.WriteLine("Example to get all records for departments..");
            foreach (Departments d in depts.GetAll())
            {
                Console.Write(string.Format("Id: {0} \t Name: {1}", d.DepartmentId, d.DepartmentName));
                Console.WriteLine();
            }
            Console.WriteLine(); //Empty line

            // Optional for testing, get records using sql
            Console.WriteLine("Example to fetch records using SQL statement....");
            using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(InMemoryConnection))
            {
                cmd.CommandText = "SELECT * FROM Departments; ";
                SqlDatabaseDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    Console.Write(string.Format("Id: {0} \t Name: {1}", dr["DepartmentId"], dr["DepartmentName"]));
                    Console.WriteLine();
                }
            }
            Console.WriteLine(); //Empty line


            //Updating existing records
            Departments DeptToUpdate = new Departments
            {
                DepartmentId   = 4,
                DepartmentName = "IT"
            };

            depts.Update(DeptToUpdate);

            //Filtering records
            SqlDatabaseOrmClient <Departments> .Filter <Departments> DeptFilter = new SqlDatabaseOrmClient <Departments> .Filter <Departments>();

            DeptFilter.WhereWithOR(item => item.DepartmentName == "HR");
            DeptFilter.WhereWithOR(item => item.DepartmentId == 4);

            DeptFilter.LimitAndOffSet(2, 0);
            DeptFilter.OrderByDescending(item => item.DepartmentId);


            // Get records based on filter.
            Console.WriteLine("Filtered Records example....");
            foreach (Departments d in depts.Find(DeptFilter))
            {
                Console.Write(string.Format("Id: {0} \t Name: {1}", d.DepartmentId, d.DepartmentName));
                Console.WriteLine();
            }

            //Drop table
            //depts.DropTable(new Departments());
        }
        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");
            }
        }
Exemplo n.º 10
0
        static void MultiThreading()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;")) // In Memory database.
            {
                try
                {
                    cnn.Open();
                    if (cnn.State != ConnectionState.Open)
                    {
                        Console.WriteLine("Unable to open connection.");
                        return;
                    }
                }
                catch (SqlDatabaseException e)
                {
                    Console.WriteLine("Error: {0}", e.Message);
                    return;
                }

                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
                {
                    cmd.Connection  = cnn;
                    cmd.CommandText = "CREATE TABLE IF NOT EXISTS TestTable (ThreadId Integer, Id Integer, RandomText Text, ByteArray Blob);";
                    cmd.ExecuteNonQuery();
                }

                Random rnd = new Random();
                Parallel.For(0, Environment.ProcessorCount,
                             new ParallelOptions
                {
                    MaxDegreeOfParallelism = Environment.ProcessorCount
                },
                             i => {
                    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
                    {
                        cmd.Connection = cnn;

                        string RandomPathForText = System.IO.Path.GetRandomFileName();

                        cmd.CommandText = "INSERT INTO TestTable VALUES (@ThreadId, @Id, @RandomText, @ByteArray);";
                        if (!cmd.Parameters.Contains("@ThreadId"))
                        {
                            cmd.Parameters.AddWithValue("@ThreadId", System.Threading.Thread.CurrentThread.ManagedThreadId);
                        }
                        else
                        {
                            cmd.Parameters["@ThreadId"].Value = System.Threading.Thread.CurrentThread.ManagedThreadId;
                        }

                        if (!cmd.Parameters.Contains("@Id"))
                        {
                            cmd.Parameters.AddWithValue("@Id", rnd.Next(1, 100));
                        }
                        else
                        {
                            cmd.Parameters["@Id"].Value = rnd.Next(1, 100);
                        }

                        if (!cmd.Parameters.Contains("@RandomText"))
                        {
                            cmd.Parameters.AddWithValue("@RandomText", RandomPathForText);
                        }
                        else
                        {
                            cmd.Parameters["@RandomText"].Value = RandomPathForText;
                        }

                        if (!cmd.Parameters.Contains("@ByteArray"))
                        {
                            cmd.Parameters.AddWithValue("@ByteArray", Encoding.UTF8.GetBytes(RandomPathForText));
                        }
                        else
                        {
                            cmd.Parameters["@ByteArray"].Value = Encoding.UTF8.GetBytes(RandomPathForText);
                        }

                        cmd.ExecuteNonQuery();
                    }
                });


                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT * FROM TestTable; ";
                    cmd.ExecuteNonQuery();
                    SqlDatabaseDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            //Console.Write(Encoding.UTF8.GetString(dr.GetFieldValue<byte[]>(c)) + "\t");
                            //byte[] byteArray = (byte[])dr.GetValue(c);
                            if (dr.GetName(c).Equals("ByteArray"))
                            {
                                Console.Write(Encoding.UTF8.GetString(dr.GetFieldValue <byte[]>(c)) + "\t");
                            }
                            else
                            {
                                Console.Write(dr.GetValue(c) + "\t");
                            }
                        }
                        Console.WriteLine("");
                    }
                }
            }
        }
Exemplo n.º 11
0
        static void MixedLanguagesUTF8()
        {
            //Uncomment if your console window does not show all utf8 characters.
            //Console.OutputEncoding = Encoding.UTF8;

            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();
                SqlDatabaseTransaction trans = cnn.BeginTransaction();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "CREATE TABLE Languages (Id Integer Primary Key AutoIncrement, LanguageName Text, LangText Text);";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "INSERT INTO Languages VALUES (null, @Language, @LangText);";
                    cmd.Parameters.Add(new SqlDatabaseParameter {
                        ParameterName = "@Language"
                    });
                    cmd.Parameters.Add(new SqlDatabaseParameter {
                        ParameterName = "@LangText"
                    });

                    cmd.Parameters["@Language"].Value = "English";
                    cmd.Parameters["@LangText"].Value = "Hello World";
                    cmd.ExecuteNonQuery();

                    //Languages written right to left must use parameters intead of string concatenation of sql text.
                    cmd.Parameters["@Language"].Value = "Urdu";
                    cmd.Parameters["@LangText"].Value = "ہیلو ورلڈ";
                    cmd.ExecuteNonQuery();

                    cmd.Parameters["@Language"].Value = "Arabic";
                    cmd.Parameters["@LangText"].Value = "مرحبا بالعالم";
                    cmd.ExecuteNonQuery();

                    cmd.Parameters["@Language"].Value = "Chinese Traditional";
                    cmd.Parameters["@LangText"].Value = "你好,世界";
                    cmd.ExecuteNonQuery();

                    cmd.Parameters["@Language"].Value = "Japanese";
                    cmd.Parameters["@LangText"].Value = "こんにちは世界";
                    cmd.ExecuteNonQuery();

                    cmd.Parameters["@Language"].Value = "Russian";
                    cmd.Parameters["@LangText"].Value = "Привет мир";
                    cmd.ExecuteNonQuery();

                    cmd.Parameters["@Language"].Value = "Hindi";
                    cmd.Parameters["@LangText"].Value = "नमस्ते दुनिया";
                    cmd.ExecuteNonQuery();


                    cmd.CommandText = "SELECT * FROM Languages; ";
                    SqlDatabaseDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine("");
                    }

                    Console.WriteLine("---- Search -----");
                    //Urdu and Arabic should return when searching like ر which is R character in english.
                    cmd.CommandText = "SELECT * FROM Languages WHERE LangText LIKE @LikeSearch;"; //note no single quotes around @LikeSearch parameter LIKE '%w%'
                    cmd.Parameters.Add(new SqlDatabaseParameter {
                        ParameterName = "@LikeSearch", Value = "%ر%"
                    });
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine("");
                    }

                    Console.WriteLine("---- Search With OR operator -----");

                    //Now it should return English, Urdu, Arabic and Russian due to OR operator
                    cmd.CommandText = "SELECT * FROM Languages WHERE (LangText LIKE '%W%') OR (LangText LIKE @LikeSearch) OR (LangText = @LangText);"; //note no single quotes around @LikeSearch parameter LIKE '%w%'

                    //Parameters can be cleared using : cmd.Parameters.Clear(); //however we are reusing existing parameter names.
                    cmd.Parameters["@LikeSearch"].Value = "%ر%";        //since parameter @LikeSearch already exist assign new value.
                    cmd.Parameters["@LangText"].Value   = "Привет мир"; //parameter @LangText already exist in this Command object.
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine("");
                    }
                }
            }
        }
Exemplo n.º 12
0
        static void SavePoint()
        {
            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://@memory;"))
            {
                cnn.Open();
                SqlDatabaseTransaction trans = cnn.BeginTransaction();
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.Transaction = trans;
                    cmd.CommandText = "CREATE TABLE SavePointExample (id Integer); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO SavePointExample VALUES (1); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SAVEPOINT a; ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO SavePointExample VALUES (2); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SAVEPOINT b; ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "INSERT INTO SavePointExample VALUES (3); ";
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SAVEPOINT c; ";
                    cmd.ExecuteNonQuery();

                    //should return 1, 2, 3 since no rollback or released has occured.
                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    SqlDatabaseDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine("");
                    }

                    //rollback save point to b without committing transaction. The value 3 and savepoint c will be gone.
                    cmd.CommandText = "ROLLBACK TO b"; //b
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine(""); // line break.
                    }

                    //if we uncomment and release c it wil produce logical error as savepoint c does not exists due to rollback to b.
                    //cmd.CommandText = "RELEASE c"; //c
                    //cmd.ExecuteNonQuery();

                    cmd.CommandText = "RELEASE b;"; //release b means commit the deffered transaction.
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine(""); // line break.
                    }


                    //We can still rollback entire transaction
                    //trans.Rollback();

                    //commit an entire transaction
                    trans.Commit();

                    //If we rollback transaction above regardless of release savepoint (i.e. saving)
                    //following will produce an error that SavePointExample table not found.
                    cmd.CommandText = "SELECT * FROM SavePointExample; ";
                    dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Console.Write(dr.GetValue(c) + "\t");
                        }
                        Console.WriteLine(""); // line break.
                    }
                }
            }
        }
Exemplo n.º 13
0
        private void CreateDropTable_Click(object sender, EventArgs e)
        {
            //Create file name
            string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "tempdb.db");

            //Connection string
            string strCon = "schemaname=db;uri=file://" + dbfilepath + ";";

            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection(strCon))
            {
                //Either open the existing database file or create new.
                //Other option is DatabaseFileMode.OpenIfExists in which new file is not created.
                cnn.DatabaseFileMode = DatabaseFileMode.OpenOrCreate;

                //Since we are creating new table, database must be opened in ReadWrite mode.
                cnn.DatabaseMode = DatabaseMode.ReadWrite;

                try
                {
                    cnn.Open();
                } catch (SqlDatabaseException dbe)
                {
                    Debug.WriteLine(dbe.Message);
                    return;
                }

                // Check if database connection is open before we create command object to query.
                if (cnn.State == ConnectionState.Open)
                {
                    using (SqlDatabaseCommand cmd = new SqlDatabaseCommand())
                    {
                        // Assign the connection to this command object.
                        cmd.Connection = cnn;

                        cmd.CommandText = "DROP TABLE IF EXISTS ATableName; ";
                        cmd.ExecuteNonQuery();

                        StringBuilder sb = new StringBuilder();
                        sb.AppendLine("CREATE TABLE IF NOT EXISTS ATableName ");
                        sb.AppendLine(" ( ");
                        sb.AppendLine(" Id INTEGER PRIMARY KEY AUTOINCREMENT "); // Column with integer data types
                        sb.AppendLine(" , ProductName TEXT ");                   // Column with Text data type with no max number of characters same as varchar or string
                        sb.AppendLine(" , Price REAL ");                         // double, float or decimal datatype used for money.
                        sb.AppendLine(" , Picture BLOB ");                       // BLOB data type for bytes.
                        sb.AppendLine(" , MoreInfo NONE ");                      // Not sure and no preference
                        sb.AppendLine(" ) ");
                        cmd.CommandText = sb.ToString();
                        cmd.ExecuteNonQuery();

                        //SYS_OBJECTS Stores the table , you can verify that table exists or get the original sql from sqltext column.
                        sb.Clear();
                        sb.AppendLine("SELECT type [Object Type], crdatetime AS [DateTime Created], tablename [Table Name] FROM SYS_OBJECTS WHERE type = 'table' AND Name = 'ATableName';");
                        cmd.CommandText = sb.ToString();
                        SqlDatabaseDataReader dr = cmd.ExecuteReader();
                        while (dr.Read())
                        {
                            // Column Names using GetName function
                            for (int c = 0; c < dr.VisibleFieldCount; c++)
                            {
                                Debug.Write(dr.GetName(c).ToString() + "\t");
                            }
                            Debug.WriteLine(Environment.NewLine + "-------------------------------------------");

                            // Row values
                            for (int c = 0; c < dr.VisibleFieldCount; c++)
                            {
                                Debug.Write(dr.GetValue(c).ToString() + "\t");
                            }
                        }

                        // Drop the table
                        sb.Clear();
                        sb.Append("DROP TABLE IF EXISTS ATableName ; ");
                        cmd.CommandText = sb.ToString();
                        cmd.ExecuteNonQuery();
                    }
                }
            }

            try
            {
                if (File.Exists(dbfilepath))
                {
                    File.Delete(dbfilepath);
                }
            } catch (IOException ex)
            {
                throw ex;
            }
        }
Exemplo n.º 14
0
        private void ParallelInsertFile_Click(object sender, EventArgs e)
        {
            DialogResult dlgresult = MessageBox.Show("This example will copy file names from MyDocuments folder, continue ?", "Important Question", MessageBoxButtons.YesNo);

            if (dlgresult.ToString() == "No")
            {
                return;
            }

            string[] files = System.IO.Directory.GetFiles(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments), "*.*");

            string dbfilepath = Path.Combine(Path.GetDirectoryName(Application.ExecutablePath), "files.db");

            try
            {
                if (File.Exists(dbfilepath))
                {
                    File.Delete(dbfilepath);
                }
            } catch (IOException ioe)
            {
                MessageBox.Show(ioe.Message);
                return;
            }


            using (SqlDatabaseConnection cnn = new SqlDatabaseConnection("schemaname=db;uri=file://" + dbfilepath + ";"))
            {
                cnn.Open();

                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "CREATE TABLE FileNames (Id Integer primary key autoincrement, InsertDateTime Text, ThreadId Integer, FileName Text); ";
                    cmd.ExecuteNonQuery();
                }
                SqlDatabaseTransaction trans = cnn.BeginTransaction();

                try
                {
                    Parallel.ForEach(files, (currentFile) =>
                    {
                        using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                        {
                            cmd.Transaction = trans;
                            cmd.CommandText = "INSERT INTO FileNames VALUES (null, GetDate(), @ThreadId, @FileName); ";
                            cmd.Parameters.Add("@ThreadId", Thread.CurrentThread.ManagedThreadId);
                            cmd.Parameters.Add("@FileName", currentFile);
                            cmd.ExecuteNonQuery();
                        }
                    });
                }
                catch
                {
                    trans.Rollback();
                }

                trans.Commit();
                //Now try reading first 100 rows by using LIMIT 100..
                using (SqlDatabaseCommand cmd = new SqlDatabaseCommand(cnn))
                {
                    cmd.CommandText = "SELECT * FROM FileNames LIMIT 100; ";
                    SqlDatabaseDataReader dr = cmd.ExecuteReader();
                    while (dr.Read())
                    {
                        for (int c = 0; c < dr.VisibleFieldCount; c++)
                        {
                            Debug.Write(dr.GetValue(c) + "\t");
                        }
                        Debug.WriteLine("");
                    }
                }
            }

            //Delete the database file since we don't need it.
            try
            {
                if (File.Exists(dbfilepath))
                {
                    File.Delete(dbfilepath);
                }
            }
            catch (IOException ioe)
            {
                MessageBox.Show(ioe.Message);
                return;
            }
        }