/ ASP.NET

SQL CE db creation with C#

I hope this would be useful for porting a MDF Sql db to CE Sql db.
we all very much familiar with ADO.NET the namespaces available for SQL server data provider are System.Data and System.Data.SqlClient similarly in ADOCE.NET for SQL CE data provider are System.Data and System.Data.SqlserverCE. The process to develop an application to use SQL Server CE is
  • Check for database available or not if not create the database through SqlCeEngine object as below

    if     (!File.Exists(\My Documents\EmpDB.sdf))
    {
    SqlCeEngine empEngine = new     SqlCeEngine("Data Source = \My Documents\EmpDB.sdf");
    empEngine.CreateDatabase();}

    Note : the database file name must have .sdf extension

  • After create the database is created next step is to create the required tables

    SqlCeConnection empCon;
    SqlCeCommand empCom;
    empCon =     new SqlCeConnection("Data Source = \My Documents\EmpDB.sdf");
    empCon.Open();
    empCom = empCon.CreateCommand();
    string     strQuery = "CREATE TABLE Employee (EmpID int IDENTITY(0,1) PRIMARY KEY,EmpName nvarchar(10),Salary int)";
    empCom.CommandText=strQuery;
    empCom.ExecuteNonQuery();

    This code snippet create a table with three columns ( empid,emp name & salary )

  • Once you setup the database then further operations are store or fetch data , ii can be done through SqlCeDataReader as below

    string strQuery = "SELECT * FROM Employee ORDER By EmpName ASC";
    empCom.CommandText = strQuery;
    SqlCeDataReader empReader = empCom.ExecuteReader();

you can use the code and fill the datas from the existing MDF to CE.