How to connect Microsoft SQL Server Compact or .sdf format database in C#.NET

It shows how to connect Microsoft SQL Server Compact or .sdf format database file into our project using C#.NET

Keywords: database, sql, C#.net, connection.

Steps to add .sdf file database into our project.

Step 1: download Microsoft SQL Server Compact setup from here

Step 2: After installation, you can see two setup file (SSCERuntime_x64-ENU and SSCERuntime_x86-ENU), install SSCERuntime_x86-ENU if you are using WINXP.

Step 3: After installation, open your project and create a new database connection, through selecting “Connect to Database”

Step 4: Then change data source into “Microsoft SQL Server Compact 3.5” and press OK

Step 5: Now click browse in connection properties to choose the .sdf file

Step 6: Then test your connection through clicking ‘Test Connection’ and click OK to exit

 

Step 7: Imp: you have to add a reference to your project named: “System.Data.SqlServerCe.dll”(located in “C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop”)

 

Step 8: To check this add a datagridview into your project and type the below code into your project.

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.SqlServerCe;

namespace testc

{

    public partial class Form1 : Form

    {

        SqlCeConnection sqlceCon = new SqlCeConnection();

        SqlCeCommand sqlCeCom = new SqlCeCommand();

        DataTable dt = new DataTable();

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

            sqlceCon = new SqlCeConnection("Data Source=C:\\Program Files\\Microsoft SQL Server Compact Edition\\v3.5\\Samples\\Northwind.sdf");

            sqlceCon.Open();

            SqlCeDataAdapter adptrOdbc = new SqlCeDataAdapter("select * from customers", sqlceCon);

            adptrOdbc.Fill(dt);

            dataGridView1.DataSource = dt;

        }      

    }

}

Note: you can create new database at Connection adding time, through selecting Create option in Connection Properties