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

Question

I am trying to learn about C # with SQL CE so that my program can remember the material.

I created a database and can connect to it:

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

And it connects correctly, I think the reason is, if I rename dbJournal.sdf to something wrong, it does not debug correctly.

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

 (SELECT * FROM tblJournal) 

How it's done?

How about a simple insert?

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

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

Answer 1

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

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

Classes follow standard templates, so there are exact replica classes for inserting / reading from SQL server or other databases, such as SqlConnection or OleDbConnection and OleDbCommand , etc.

This is the easiest approach to ado.net:

 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 the 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 good and fast way to read data is as follows:

 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 allows you to get all the data in one shot into the DataTable class.

Insert data:

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

Answer 2

If you are just starting to learn, I suggest you use LINQ to complete these queries.

Here is an MSDN article showing LINQ features.

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

Using LINQ, it will be easy to complete each query. For example, you can write your selection request as follows

 from journal in TblJournal select journal 

or simply

 context.TblJournal 

Answer 3

also to improve performance, you better keep in touch open all the time working with SQL CE (unlike other standard SQL databases)