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

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

Solution 2

If you just start learning that i will suggest you to use LINQ to make that queries.

Here is MSDN article showing features of LINQ.

http://msdn.microsoft.com/en-us/library/bb425822.aspx

Using LINQ it will be simple to do every query. For example, you can write your select query like this

from journal in TblJournal select journal 

or just

context.TblJournal

Solution 3

also in order to improve performence , you better keep the conncection open all the time when working with SQL CE (as opposed to other standard sql databases)