How to connect SQL Server Compact Edition database

Question

I'm trying to connect my SQL Server Compact Edition database to Crystal Report. I've been searching all day long and I've found many question related to it so far on other websites, but none had a working answer.

I also found this question in this site which has asked for VB.Net solution. And this link which is all over the internet and has a confusing way to do it by XML, which I couldn't figure it out.

There are other links with solutions in SQL Server and Access which don't help.

• SQL Server solution
• Access Solution

I'm wondering is there a simple way to connect a SQL Server CE database to Crystal Report which actually works?

Answer 1

So I found my solution thanks to this helpful CodeProject sample

I will demonstrate an easier sample to make it easier to figure it out.

1. Create a Winform and add a button and a CrystalReportViewer control to it.

2. Add a DataSet (*.xsd file) to your project using add -> New Items in solution explorer. After that, add a DataTable to the DataSet.

3. Add columns to DataTable. It's better to name them the same as the columns you are going to display on your report. The number of columns depends on how many columns should be displayed in the Crystal report.

4. Add a Crystal Report into the project using add -> New Items and using the Report Wizard, choose ADO.NET DataSets of the Project data source as the data source of the Crystal Report and select the data table you just created in your DataSet, as the selected table of the Crystal Report.

5. Click finish and your columns will automatically be added in CrystalReport.

6. Go to the button click event and write these codes in it.

   private void btnGo_Click(object sender, EventArgs e)
   {
       CrReport2 objRpt = new CrReport2();
       string query = "Select Name,Number from tblInfo";  //Your sql query
       SqlCeConnection conn =
           new SqlCeConnection(
              @"Data Source=|DataDirectory|\myDB.sdf;Persist Security Info=False"); //Your connection
   
       SqlCeDataAdapter adepter = new SqlCeDataAdapter(query, conn);
       DsReport Ds = new DsReport(); //DsReport is my dataset
   
       adepter.Fill(Ds, "customer"); //customer is my datatable in dataset
   
       objRpt.SetDataSource(Ds);
       crystalReportViewer1.ReportSource = objRpt;
   }
   

7. Enjoy your report :)