Basic start with Visual Studio C# and SQL Compact (connect, select, insert)?

Question

I'm trying to learn about C# with SQL CE so that my program can remember stuff.

I have created a database and can connect to it:

SqlCeConnection conn = 
         new SqlCeConnection(@"Data Source=|DataDirectory|\dbJournal.sdf");
conn.Open();

And it connects right, I guess cause if I rename the dbJournal.sdf to something wrong it doesn't debug right.

Let's say I want to make a simple SELECT query.

(SELECT * FROM tblJournal)

How is that done?

What about a simple insert?

(INSERT TO tblJournal (column1, column2, column2) VALUES 
                                        (value1, value2, value3))

I'm used to PHP and MySQL (as you properly can see :o))

Answer 1

@Chuck mentions EntityFramework which simplifies things and does all the work of writing the sql for you.

But there is a basic ADO.NET approach here which I will describe below.

The classes follow a standard pattern so to insert/read from sql server or other databases there are exact replica classes like SqlConnection or OleDbConnection and OleDbCommand etc

This is the most barebones ado.net approach:

using( SqlCeConnection conn =
          new SqlCeConnection(@"Data Source=|DataDirectory|\dbJournal.sdf") )
using( SqlCeCommand cmd = conn.CreateCommand() )
{
  conn.Open();
  //commands represent a query or a stored procedure       
  cmd.CommandText = "SELECT * FROM tblJournal";
  using( SqlCeDataReader rd = cmd.ExecuteReader() )
  {
     //...read
  }
  conn.Close();
}

Then to read data :

while (rd.Read())
{//loop through the records one by one
     //0 gets the first columns data for this record
     //as an INT
     rd.GetInt32(0);
     //gets the second column as a string
     rd.GetString(1);
}

A nice and quicker way to read data is like this:

using( SqlCeDataAdapter adap = 
          new SqlCeDataAdapter("SELECT * FROM tblJournal", "your connection") )
{
  //the adapter will open and close the connection for you.
  DataTable dat = new DataTable();
  adap.Fill(dat);
}

This gets the entire data in one shot into a DataTable class.

To insert data :

SqlCeCommand cmdInsert = conn.CreateCommand();
cmdInsert.CommandText = "INSERT TO tblJournal (column1, column2, column2) 
                           VALUES (value1, value2, value3)";
cmdInsert.ExecuteNonQuery();