Your Ads Here

Friday, 23 March 2012

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,, 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()

        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");

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

            dataGridView1.DataSource = dt;

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

1 comment: