Extract data from database of Microsoft sql server compact in C#

This topic explains, How to extract data from database of Microsoft sql server compact database or from sdf file in C# programming.

How to insert data into database of Microsoft SQL Server Compact

Design the frame as shown in the below figure.

How to extract data from database of Microsoft sql server compact - Design

How to extract data from database of Microsoft sql server compact in C# – Design

Microsoft SQL Server – How to extract data from database of Microsoft sql server compact in C#

Microsoft SQL Server is a database management and analysis system for e-commerce, line-of-business, and data warehousing solutions.
SQL Server 2012, the latest version, adds new high availability and disaster recovery solutions through AlwaysOn clusters and availability groups, xVelocity in-memory storage for extremely fast query performance
Courtesy: Microsoft

System.Data.SqlServerCe NameSpace – How to extract data from database of Microsoft sql server compact in C#

The System.Data.SqlServerCe namespace provides programmatic access to Microsoft SQL Server 2005 Compact Edition (SQL Server Compact Edition) databases from a managed application.
System.Data.SqlServerCe provides a set of classes that make available the functionality of SQL Server Compact Edition.

sqlCeCommand Class – How to extract data from database of Microsoft sql server compact in C#

Represent an SQL statement to execute against a data source.
Syntax

// DeclarationPublicNotInheritableClass SqlCeCommand _
InheritsDbCommand;
ImplementsICloneable;
// UsageDim instance As SqlCeCommand

// DeclarationPublicNotInheritableClass SqlCeCommand _

InheritsDbCommand;

ImplementsICloneable;

// UsageDim instance As SqlCeCommand

sqlCeConnection Class – How to extract data from database of Microsoft sql server compact in C#

Represent an open connection to a SQL Server Compact data source.
Syntax

// DeclarationPublicNotInheritableClass SqlCeConnection _
InheritsDbConnection;
// UsageDim instance As SqlCeConnection

// DeclarationPublicNotInheritableClass SqlCeConnection _

InheritsDbConnection;

// UsageDim instance As SqlCeConnection

sqlCeDataAdapter Class – How to extract data from database of Microsoft sql server compact in C#

Represents a set of data commands and a database connection that are used to fill the DataSet and update the data source.
Syntax

// DeclarationPublicNotInheritableClass SqlCeDataAdapter _
InheritsDbDataAdapter;
ImplementsICloneable;
// UsageDim instance As SqlCeDataAdapter

// DeclarationPublicNotInheritableClass SqlCeDataAdapter _

InheritsDbDataAdapter;

ImplementsICloneable;

// UsageDim instance As SqlCeDataAdapter

How to extract data from database of Microsoft sql server compact in C# – Complete Code

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 WindowsFormsApplication1
{
    public partial class Form1 : Form
    {

        SqlCeConnection sqlceCon = new SqlCeConnection();
        DataTable dt = new DataTable();

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // Create you own database
            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);
            // here it fills the selected data to the datatable
            adptrOdbc.Fill(dt);
            // checking datatable has more than one rows
            if ((dt.Rows.Count != 0))
            {
                txtcusID.Text = dt.Rows[0]["customer id"].ToString();
                txtComName.Text = dt.Rows[0]["Company Name"].ToString();
                txtContName.Text = dt.Rows[0]["Contact Name"].ToString();
                txtContTitle.Text = dt.Rows[0]["Contact Title"].ToString();
            }
            sqlceCon.Close();
        }

    }
}

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 WindowsFormsApplication1

{

public partial class Form1 : Form

{

SqlCeConnection sqlceCon = new SqlCeConnection();

DataTable dt = new DataTable();

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

// Create you own database

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

// here it fills the selected data to the datatable

adptrOdbc.Fill(dt);

// checking datatable has more than one rows

if ((dt.Rows.Count != 0))

{

txtcusID.Text = dt.Rows[0]["customer id"].ToString();

txtComName.Text = dt.Rows[0]["Company Name"].ToString();

txtContName.Text = dt.Rows[0]["Contact Name"].ToString();

txtContTitle.Text = dt.Rows[0]["Contact Title"].ToString();

}

sqlceCon.Close();

}