databaseutils.cs

Creating your SQL Server Compact Edition database and schema in code

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Diagnostics;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace CreatingDatabaseSchemaInCode
{
	public class DatabaseUtils
	{
		public void InitializeDatabase(string connectionString)
		{
			// we'll use the SqlServerCe connection object to get the database file path
			using (SqlCeConnection localConnection = new SqlCeConnection(connectionString))
			{
				// The SqlCeConnection.Database contains the file parth portion
				// of the database from the full connectionstring
				if (! File.Exists(localConnection.Database))
				{
					// No file, no database
					using (SqlCeEngine sqlCeEngine = new SqlCeEngine(connectionString))
					{
						sqlCeEngine.CreateDatabase();
						CreateInitialDatabaseObjects(connectionString);
					}
				}
			}
		}
		
		/// <summary>
		/// When the database is created, we need to initialize it with new tables, relations and possibly data
		/// </summary>
		private void CreateInitialDatabaseObjects(string connectionString)
		{
			// Grab a reference to the connection on the client provider
			using (SqlCeConnection connection = new SqlCeConnection(connectionString))
			{
				// Using the SQL Management Studio convention of using GO
				// to identify individual commands
				// Create a list of commands to execute
				string[] commands;
				
				// To simplify editing and testing TSQL statements,
				// the commands are placed in a managed resource of the dll.
				commands = Properties.Resources.CreateSchema.Split(new string[] {"GO" + "\r\n"}, StringSplitOptions.RemoveEmptyEntries);
				
				SqlCeCommand cmd = new SqlCeCommand();
				// make sure we put the connection back to its previous state
				cmd.Connection = connection;
				
				connection.Open();
				foreach (string commandText in commands)
				{
					if (commandText.Trim().Length > 0)
					{
						cmd.CommandText = commandText;
						cmd.ExecuteNonQuery();
					}
				}
			}
		}

		private void GetConnected()
		{
			SqlCeConnection conn = new SqlCeConnection();
			conn.ConnectionString = "Data Source=|DataDirecotry|\\Northwind.sdf;password=foo;Max Database Size=256";
			try
			{
				conn.Open();
			}
			catch (SqlCeException sqlCeEx)
			{
				MessageBox.Show(sqlCeEx.Message);
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}

		public List<KeyValuePair<string, string>> GetDbInfo(string connStr)
		{
			SqlCeConnection conn = new SqlCeConnection(connStr);
			conn.Open();
			try
			{
				return conn.GetDatabaseInfo();
			}
			finally
			{
				conn.Close();
			}
		}
	}
}