How to select data from SQL Server Compact CE using ADO.NET SqlCeCommand and SqlCeDataReader Objects

In this post, I will show you how to use some of the classes in System.Data.SqlServerCe Namespace to access a SQL Server CE database from a ASP.NET 4.0 web forms application.

The DLL that contains System.Data.SqlServerCe Namespace and all classes in that namespace is System.Data.SqlServerCe.dll. The System.Data.SqlServerCe namespace is the managed data provider for SQLServer CE. This namespace is a collection of classes that provide access to  SQL Server CE database. By using System.Data.SqlServerCe, you can create, manage, and synchronize  databases from a smart device or a computer. The classes include all the ADO.NET classes such as SqlCeConnection, SqlCeDataAdapter, SqlCeCommand, SqlCeDataReader.

I am assuming you already know the following from my previous post Using SQL Server CE in Visual Studio 2010 Express SP1:

  • How to create a new ASP.NET application using Visual Web Developer Express
  • How to create a Database1.sdf SQL Server CE Data file under App_Data folder
  • Create a Students Table with three columns
  • Populate some data into this table

In this post I will show you some C# code to actually access the data file and contents of the “Students” table and show you how to insert, delete and update records using ADO.NET objects.

ADO.NET

ADO.NET provides a bunch of classes to access any database. All the classes needed to access a certain type of database (example Oracle) witll go into one single namespace. In this case, classes in the System.Data.SqlServerCe Namespace will be used in the C# code.

First thing I need to do after creating the databse is to create my connection string pointing to that database. I can do it thru the IDE or simply by opening and editing my Web.config file. I need to insert the following snippet inside the configuration section.

<configuration>
    <connectionStrings>    
    <add name="ConnectionString1" connectionString="Data Source=|DataDirectory|\Database1.sdf" providerName="System.Data.SqlServerCe.4.0"/>  
    </connectionStrings>

Look at |DataDirectory| element. It is a placeholder to point to my App_Data folder which happens to be

C:\Documents and Settings\raja\My Documents\Visual Studio 2010\Projects\DataAccess\DataAccessProject\App_Data\. So using this will simplify some typing for you. Once the connectionstring is created in the Web.config file, you can programmatically access that from your ASPX file using  <%$ ConnectionStrings:ConnectionString1 %>

For example inside your aspx where you define a SqlDataSource you can use <%$ ConnectionStrings:ConnectionString1 %> to actually point to the connection string for the web.config file as shown below:

<asp:SqlDataSource ID="SqlDataSource1" runat="server"  
ConnectionString="<%$ ConnectionStrings:ConnectionString1 %>"             
ProviderName="<%$ ConnectionStrings:ConnectionString1.ProviderName %>"             
SelectCommand="SELECT [Name], [Class] FROM [Students]">
</asp:SqlDataSource>

But in this example I am not going to use that. I will only be using my C# file to access the connection string.

I created a ADOExample.aspx file from a Master Page. The VWD 2010 IDE automatically creates ADOExample.aspx.cs and ADOExample.aspx.designer.cs files for me. In the Page_Load( ) function of the ADOExample.aspx.cs, I assigned the public variable connstr with System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString which actually returns the ConnectionString1 that I created inside thw web.config file.

protected void Page_Load(object sender, EventArgs e)        {            
    connstr = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ConnectionString;        
}

Selecting Data for Read-only using SqlCeDataReader

I will use the  SqlCeDataReader Object is to select data from a database, if you are only interested in reading the data. You may not be able to edit the data with this. If you want to edit/update the data you will need to use DataSet and DataAdapter objects. I create a Button called Button1 and a Label caled Label1 in my aspx form. When you click on the Button1, I’ll fetch the data and display it in the Label.

protected void Button1_Click(object sender, EventArgs e) {            
    Label1.Text = "Select Command Output<br/>";            
    // Create and open the connection in a using block. This            
    // ensures that all resources will be closed and disposed            
    // when the code exits.            
    using (SqlCeConnection conn = new SqlCeConnection(connstr)) {                
        string queryString = "SELECT * FROM Students where Class >= @classval";                
        int classval = 1;                
        SqlCeCommand command = new SqlCeCommand(queryString, conn);                
        command.Parameters.AddWithValue("@classval", classval);                
        try {                    
            conn.Open();                    
            SqlCeDataReader reader = command.ExecuteReader();                    
            while (reader.Read()) {                        
                Label1.Text += reader[0] + " " + reader[1] + " " + reader[2] + "<br/>";                    
            }                    
            reader.Close();                
        }                
        catch (Exception ex)  {                    
            Label1.Text = ex.Message;                
        }            
    }        
}

here is a flow chart to show you the usage of various objects to read the data.You can also download the flow chart in MS Word Format.

Flow Chart to show how the AD.NET SqlCeDataReader works

  1. You always start with a ConnectionString. You can either hard code it or obtain it from Web.Config
  2. then you create SqlConnection object with the above connection String
  3. create query string containing the SELECT statement. Any dynamic variables can be represented by @variable-name and can be passed in using Parameter.AddWithValue
  4. Create SqlCommand object by passing in the querystring from #3 and Connection object from #2
  5. Pass any dynamic parameters using command.Parameters.AddWithValue (paramname, paramvalue) method
  6. now you are ready to execute the command. Simply call command.ExecuteReader() which will return a DataReader Object
  7. Call reader.Read() method once. Now the cursor is set to the first row of the results.
    1. You can access the contents of the first row using reader[i] where i is the column index
    2. or by using reader["ColumnName"]
  8. Once you fetch the values you can go to the next row by called reader.Read() again. Do this in a loop until Read() returns false indicating there are no more rows available in the resultset.

In my next blog Using SqlDataAdapater and SqlDataSet to modify data in SQL Server CE Tables we will talk in detail how to use the SqlDataAdapter and SqlDataSet Objects to modify the table data and save it back to SQL Server CE.

This entry was posted in ASP.NET, Data Access, SQL Server Compact and tagged , , , , . Bookmark the permalink. Post a comment or leave a trackback: Trackback URL.

One Trackback

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>