Programmatically creating the SQL Server CE database

Let's take a look at the CreateSalesForceDatabase method. This method is expected to create a database named salesForce in SQL Server CE and to generate all the sales force application tables automatically. On implementing this function, we will employ the use of the following technologies:

• We will use the sqlCeEngine class to generate the new database.

• We will use the sqlCeConnection class to connect to the database.

• We will store all the SQL statements (that define the table schemas) in a Visual Studio generated resource file and retrieve them during realtime.

• We will make use of .NET reflection to obtain the current folder path.

In the CreateSalesForceDatabase implementation, we first need to declare a few function-scope variables:

SqlCeEngine _engine; SqlCeConnection _connection; SqlCeCommand _command; resourcemanager _ResourceManager; string _dbcreationstring; string _SDFPath;

bool _dbcreationsuccess = false;

Next, we will use .NET reflection to retrieve the current path. We will use that to build your full connection string:

_SDFPath = System.IO.Path.GetDirectoryName(Assembly. GetExecutingAssembly().GetName().CodeBase) + "\\salesforce.sdf";

_dbcreationstring = "Data Source='" + _SDFpath + "';LCID=1033;Password ='admin123';Encrypt=FALSE;"

We then use the SqlCeEngine class to create the database using the connection string you've built earlier. We put this whole block of code under a Try...Catch block so that if this part of the code fails, we will simply exit the function:

_engine = new SqlCeEngine(_dbcreationstring);

_engine.CreateDatabase(); _dbcreationsuccess = true;

catch (Exception ex) {

MessageBox.Show(ex.Message, "Create database",

MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Buttonl);

finally {

_engine.Dispose();

if (_dbcreationsuccess == false) return false;

Now that the database .sdf file has been created, we will attempt to connect to it and execute the SQL statements stored in the TableSchema.resx resource file to build the entire schema required for the sales force application. We start by first opening a connection to your newly created database:

_connectionString = "Data source='" + _SDFPath + "';Password=admin123;";

_connection = new SqlCeConnection(_connectionString);

_connection.Open();

catch (Exception ex) {

MessageBox.Show(ex.Message, "Connecting to database", MessageBoxButtons.OK, MessageBoxIcon.Exclamation, MessageBoxDefaultButton.Buttonl); return false;

We latch .NET's ResourceManager class on to your TableSchema.resx resource file. You will need to provide the full base name for your resource file, which is CRMLive. TableSchema:

_ResourceManager = new ResourceManager("CRMLive.TableSchema", Assembly.GetExecutingAssembly());

Lastly, we create the SqlCeCommand object to run each of the SQL statements retrieved from the resource file. This will create all the tables we need for the application:

_command = _connection.CreateCommand();

_command.CommandText = _ResourceManager.GetString("Accounts_SQL"); _command.ExecuteNonQuery();

_command.CommandText = _ResourceManager.GetString("AccountTasks_SQL"); _command.ExecuteNonQuery();

_command.CommandText = _ResourceManager.GetString("AccountHistories_ SQL");

_command.ExecuteNonQuery();

Also don't forget to close and dispose of the database connection at the end:

_connection.Close(); _connection.Dispose(); _connection = null; return true;

Was this article helpful?

0 0

Post a comment