SQL Compact Edition tips & tricks (ADO.NET)

Support for SQL Compact Edition is ... well sparse. It's one of those unwanted child's of Microsoft that he tries quickly to forget.
That means you'l have pain if you choose that way.
Just few years ago making local database in VS 2008 was child's game. Everything was there, project Data template, designer.
Now in VS 2013 it's hell.
At the moment if you need some kind of local database you can use "Service-based database" VS template.
It uses MDF & LDF files architecture from SQL server. I'm not clear how can you distribute this database to application users. There is some talk about special licence concerning use of this type of database.
Another approach is to dive into SQL CE fog.

1. Using Extensions & Updates, find and install third-party SQL Compact Toolbox.
Careful people say it's full of bugs and not well maintained.

2. Install NuGet package "Microsoft SQL Server Compact Edition"

3. Using SQL Compact Toolbox create new local SDF file.

4. Create connection string:

<add name="Application" connectionString="Data Source=|DataDirectory|\SQLCEv2.sdf;" providerName="System.Data.SqlServerCe.4.0"/>

For more info about DataDirectory folder substition read:

Folder substitution in DataSource property of Connection string:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/0712dd47-8437-4c84-b27e-2b12fddf2153/what-is-the-datadirectory-in-data-source-datadirectorynorthwindsdf?forum=sqlce


5. ADO.Net should work now:

string conStr = ConfigurationManager.ConnectionStrings["Application"].ToString();
            SqlCeConnection con = new SqlCeConnection(conStr);
            con.Open();
            SqlCeCommand cmd = new SqlCeCommand("insert into [Product] (Name) VALUES('Sinisa')", con);
            cmd.ExecuteNonQuery();
            con.Close();

Instead of ADO.NET you can use NPoco with no problem.

C# - throwing exceptions in catch block ?

Tricky question from job interview.

Will in given code finally be executed ?

string t = "x";
            try
            {
                try
                {
                    int i = int.Parse(t);
                    Console.WriteLine(i);
                }
                catch (FormatException e)
                {
                    Console.WriteLine(e.Message);
                    throw new ApplicationException("new exception...");
                }
                finally
                {
                    Console.WriteLine("finally");
                }
            }
            catch (Exception)
            {
                Console.WriteLine("handled exception...");
            }
            Console.ReadLine();

Yes it will but ONLY if new exception from catch block is handled in outer try block.

Result:

FormatException
Finally
Handled exception


In this case when outer try block is missing finally will not execute:

string t = "x";

                try
                {
                    int i = int.Parse(t);
                    Console.WriteLine(i);
                }
                catch (FormatException e)
                {
                    Console.WriteLine(e.Message);
                    throw new ApplicationException("new exception...");
                }
                finally
                {
                    Console.WriteLine("finally");
                }
            Console.ReadLine();


To conclude be very careful with code you write in your catch block.
It should not employ any kind of complexity that mighty throw exception!