none
Using Compact Edition instead of non-Compact RRS feed

  • Question

  • Please see ADO.NET Code Examples in the ADO.NET Overview in the VS documentation.

     

    For the sample that uses System.Data.SqlClient, is it necessary to modify the source code in some way to use the Compact Edition or would the modification be external to the source code? If the source code needs to be modified then is it just the connection string or is there more that needs to be modified?

     

    If you know where the documentation is that explains this, then I am happy to read the documentation but this is the type of thing that is usually a simple answer but the answer is not easy to find. I have tried to find the answer in the documentation.

     

    I think that one thing that is confusing is that I read "CE" in the documentation and I assume it is talking about Windows CE. I have had to tolerate nuisances of Windows CE documentation for many years so I am accustomed to ignoring everything that says CE. So if CE in the documentation also means Compact Edition then I might have missed something important.

    Sunday, October 19, 2008 3:40 AM

Answers

  • Here is the modified sample, remember to add a reference to System.Data.SqlServerCe.dll.

    Changed code indicated with bold.

     

    Code Snippet

    using System;

    using System.Data;

    using System.Data.SqlServerCe;

    class Program

    {

    static void Main()

    {

    string connectionString =

    @"Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf";

    // Provide the query string with a parameter placeholder.

    string queryString =

    "SELECT [Product ID], [Unit Price], [Product Name] from Products "

    + "WHERE [Unit Price] > @pricePoint "

    + "ORDER BY [Unit Price] DESC;";

    // Specify the parameter value.

    int paramValue = 5;

    // 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 connection =

    new SqlCeConnection(connectionString))

    {

    // Create the Command and Parameter objects.

    SqlCeCommand command = new SqlCeCommand(queryString, connection);

    command.Parameters.AddWithValue("@pricePoint", paramValue);

    // Open the connection in a try/catch block.

    // Create and execute the DataReader, writing the result

    // set to the console window.

    try

    {

    connection.Open();

    SqlCeDataReader reader = command.ExecuteReader();

    while (reader.Read())

    {

    Console.WriteLine("\t{0}\t{1}\t{2}",

    reader[0], reader[1], reader[2]);

    }

    reader.Close();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }

    Console.ReadLine();

    }

    }

    }

     

     

    Sunday, October 19, 2008 7:33 PM
    Moderator

All replies

  • Here is the modified sample, remember to add a reference to System.Data.SqlServerCe.dll.

    Changed code indicated with bold.

     

    Code Snippet

    using System;

    using System.Data;

    using System.Data.SqlServerCe;

    class Program

    {

    static void Main()

    {

    string connectionString =

    @"Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf";

    // Provide the query string with a parameter placeholder.

    string queryString =

    "SELECT [Product ID], [Unit Price], [Product Name] from Products "

    + "WHERE [Unit Price] > @pricePoint "

    + "ORDER BY [Unit Price] DESC;";

    // Specify the parameter value.

    int paramValue = 5;

    // 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 connection =

    new SqlCeConnection(connectionString))

    {

    // Create the Command and Parameter objects.

    SqlCeCommand command = new SqlCeCommand(queryString, connection);

    command.Parameters.AddWithValue("@pricePoint", paramValue);

    // Open the connection in a try/catch block.

    // Create and execute the DataReader, writing the result

    // set to the console window.

    try

    {

    connection.Open();

    SqlCeDataReader reader = command.ExecuteReader();

    while (reader.Read())

    {

    Console.WriteLine("\t{0}\t{1}\t{2}",

    reader[0], reader[1], reader[2]);

    }

    reader.Close();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }

    Console.ReadLine();

    }

    }

    }

     

     

    Sunday, October 19, 2008 7:33 PM
    Moderator
  • Yes, that works. I hope this is useful for others in the future. For the benefit of others, when I first executed my version, I did receive an error message saying that I could not access the file. I investigated and eventually modified the security for the Northwind.sdf file and that worked. So if your user account (Windows security) only allows read and read and execute permission for Northwind.sdf, then you probably need to provide more access than that.

     

    The following is a copy of my source code showing before and after, but this is essentially the same as above.

    Code Snippet

    using System;

    using System.Data;

    //using System.Data.SqlClient;

    using System.Data.SqlServerCe;

    namespace OverviewSqlClient

    {

    class Program

    {

    static void Main()

    {

    string connectionString =

    //"Data Source=(local);Initial Catalog=Northwind;"

    //+ "Integrated Security=true";

    @"Data Source=C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Samples\Northwind.sdf";

    // Provide the query string with a parameter placeholder.

    string queryString =

    //"SELECT ProductID, UnitPrice, ProductName from dbo.products "

    // + "WHERE UnitPrice > @pricePoint "

    // + "ORDER BY UnitPrice DESC;";

    "SELECT [Product ID], [Unit Price], [Product Name] from Products "

    + "WHERE [Unit Price] > @pricePoint "

    + "ORDER BY [Unit Price] DESC;";

    // Specify the parameter value.

    int paramValue = 5;

    // Create and open the connection in a using block. This

    // ensures that all resources will be closed and disposed

    // when the code exits.

    //using (SqlConnection connection =

    // new SqlConnection(connectionString))

    using (SqlCeConnection connection =

    new SqlCeConnection(connectionString))

    {

    // Create the Command and Parameter objects.

    //SqlCommand command = new SqlCommand(queryString, connection);

    SqlCeCommand command = new SqlCeCommand(queryString, connection);

    command.Parameters.AddWithValue("@pricePoint", paramValue);

    // Open the connection in a try/catch block.

    // Create and execute the DataReader, writing the result

    // set to the console window.

    try

    {

    connection.Open();

    //SqlDataReader reader = command.ExecuteReader();

    SqlCeDataReader reader = command.ExecuteReader();

    while (reader.Read())

    {

    Console.WriteLine("\t{0}\t{1}\t{2}",

    reader[0], reader[1], reader[2]);

    }

    reader.Close();

    }

    catch (Exception ex)

    {

    Console.WriteLine(ex.Message);

    }

    Console.ReadLine();

    }

    }

    }

    }

    Monday, October 20, 2008 3:55 AM