Databases

.Databases
Data Access Technologies | SQL Server Editions | Connection Strings | CRUD Operations | DataSet - Disconnected Storage | SQL Injection | Transactions | Entity Framework - Object Relational Mapping | Local SQL Code Example

"Microsoft has developed several data access components over the last twenty years. The trend in this development has been toward greater levels of abstraction."

.NET Data Access



.NET Data Access

Today the .NET framework provides developers with many choices for data access. Connected data access in ADO.NET offers the most direct and low level method available to developers for working with relational data. With disconnected data access in ADO.NET you work offline with DataSets that represent the underlying relational database in memory, and then later sync the DataSets with a database when you go online.

Also, .NET contains a query capability which is integrated into the .NET languages (LINQ). LINQ provides a consistent interface across a variety of data sources (files, memory, databases) and makes a query variable a first class citizen. At a higher level of data abstraction .NET introduced Object Relational Mapping technology (Entity Framework) to assist in mapping objects to relational data. Using LINQ to Entity Framework provides mapping between the data's conceptual model and the physical database in a way that is integrated with the .NET languages.




Microsoft Data Access Technologies

In 1989 Microsoft introduced SQL Server 1.0 which contained a call-level API called DB-Library. At this point in time Database Management Systems (DBMS) each has their own, and different, call-level APIs for data access. In 1992 the Open Database Connectivity (ODBC) specification was released. ODBC builds a data abstraction on top of proprietary APIs giving applications a single way to access a wide variety of databases and database-like stores. (ODBC is still in widespread use.)

As object technologies moved into the mainstream, new object-oriented access layers were created:

  1. Data Access Object (DAO) - 1992, now deprecated

  2. Remote Data Objects (RDO) - 1995, now deprecated

  3. Object Linking and Embedding Database (OLE DB) - 1996, an object-oriented access layer alongside ODBC. (OLE DB is still in widespread use).

ActiveX Data Object

As the Internet began its rapid expansion is became necessary for web applications to access a wide variety of data stores. OLE DB was oriented around pointer-capable languages (e.g. C, C++), which excluded languages which do not support pointers (JavaScript, VBScript). So also in 1996, Microsoft developed ActiveX Data Object (ADO) which was an abstraction on top of OLE DB supporting languages which could not support pointers, as well as those languages which could support pointers.



ADO.NET

The release of the .NET framework in 2002 included a sibling to ADO called ADO.NET, which also included some additional capabilities such as XML access. ADO.NET is still a foundational component of the .NET framework and offers the most direct and low level method available to developers for working with relational data.



Entity Framework
.Entity Framework Model

The Entity Framework was introduced in .NET 3.5 (2008) and was greatly improved in .NET 4.0 (2010). Entity Framework is a set of technologies in ADO.NET which allows developers to work with data at a higher level of abstraction. Developers query entities and relationships in a conceptual model. Entity Framework also contains an Object-Relational Mapping (ORM) which helps with the "object-relational impedance mismatch" problem of mapping object-oriented classes to relational tables and columns of the database. There are a number of third-party ORMs for .NET (DataObjects.Net, NHibernate, OpenAccess, SubSonic) which can been used to create a "virtual object database", however Entity Framework is integrated into multiple Microsoft products. Using the Entity Framework, developers issue queries using LINQ, then retrieve and manipulate data as strongly typed objects. Entity Framework also helps access data which spans multiple storage systems, each with its own protocols.



Language Integrated Query

In .NET 3.5, Microsoft released Language Integrated Query (LINQ) which is an integrated query capability directly into .NET. LINQ provides a consistent way of accessing various data sources (e.g. in-memory collections, databases, XML documents, flat files, etc). LINQ makes the query a first-class language construct with the ability to be executed immediately or assigned to a query variable for deferred execution. For more information on LINQ, see the page LINQ.



Windows Communication Foundation

The next trend in technology was to build abstractions of the data sources themselves. Formats for containing the data, such as XML and JSON, were a natural integration with the Web and Data Services where created to support the protocols (SOAP, REST) for transmitting this data across the Web. These service-oriented, communication programs used to transmit this data (abstracted from its data source by the XML and JSON formatting) became known as Web Services. Microsoft developed Windows Communication Foundation (WCF) to implement the industry standards for Web service interoperability. WCF was first released in .NET 3.0 (2006) and then updated in .NET 3.5 and .NET 4.0. More recent enhancements have been the development of WCF data services to support cloud storage of data. Details of WCF are outside the scope of this article

Top




Microsoft SQL Editions

Microsoft, Sybase and Ashton-Tate created and marketed SQL Server 1.0 in 1989. In 1993 Sybase and Microsoft dissolved their relationship and Microsoft acquired exclusive rights to all version of SQL Server written for Microsoft operating systems. In 2000 extensions and modifications were made to the Sybase base code. In SQL Server 2005 the legacy Sybase code was completely rewritten. Additional SQL Server releases include 2008 and 2012, with the 2014 version currently in development.

Microsoft SQL Server is available in different editions targeting different needs. For example the top of the line 2012 Enterprise edition can manage databases as large as 524 petabytes, address 2 terabytes of memory and supports 160 physical processors. The Enterprise edition contains the full feature set with a full complement of tools and utilities. At the other end of the size spectrum is the Compact Edition which is a free, embedded database that software developers can use for building ASP.NET websites and Windows desktop applications. The Compact Edition has a small size (1 megabyte DLL) and a considerably reduced feature set which support a subset of the standard data types. The Compact Edition does not support stored procedures, views, and ODBC connectivity, among other things. The Compact Edition is limited to a 4 gigabyte database size maximum and must be hosted by the application which is using it. The Compact Edition is the default database version provided with Visual Studio.

Additional editions of Microsoft SQL Server include:

  1. Standard - core data management and business intelligence capabilities for non-critical workloads with minimal IT resources.
  2. Business Intelligence - SQL Server Business Intelligence delivers a comprehensive platform empowering organizations to build and deploy secure, scalable and manageable BI solutions.
  3. Express- free database engine in a version tailored for redistribution and embedding.
  4. Web- designed to support internet-facing workloads and enables organizations to rapidly deploy web pages, applications, websites and services.

Note: As of November 2012, the features of the Datacenter edition are included in the Enterprise addition. The Web edition will only be offered to hosters via Service Providers License Agreement (SPLA). The Workgroup and Small Business editions have been discontinued.

Microsoft uses the terminology "service-based database" and "local database" to distinguish between SQL Server and SQL Server Compact Databases. That is, a service-based database is a database that is only accessed through a server. It uses an MDF data file, which is SQL Server format. To be able to connect to a SQL Server database the SQL Server service must be running. A local database is one that is local to your application only. It uses an SDF data file, which is SQL Server CE (Compact Edition) format. There is no need to install a server to access an SDF database. You simply distribute the DLLs that constitute SSCE along with your app and access the data file directly."

Coding Differences between Service-Based Databases and Local Databases

Microsoft SQL Server Compact edition is a free SQL Server embedded database (local database) ideal for building standalone and occasionally connected applications for mobile devices, desktops, and Web clients. Unlike the other editions of SQL Server (service-based databases), the SQL CE engine was based on SQL Mobile (initially designed for use with hand-held devices) and does not share the same binaries. A different set of classes are used with the service-based databases than are used with local databases. Local database classes are are located in the System.Data.SqlServerCe namespace (in System.Data.SqlServerCe.dll) and can be identified with the embedded "ce" in the class names.

  1. System.Data.SqlClient - Service-Based Database (SQL Server)
    • SQLConnection
    • SQLCommand
    • SQLDataReader
  2. System.Data.SqlServerCe - Local Database (SQL Server Compact Edition)
    • SQLCeConnection
    • SQLCeCommand
    • SQLCeData Reader

The file extensions also vary for different types of Microsoft databases:

  • .sdf - SQL Server Compact Edition database file
  • .mdb - Access database
  • .mdf - SQL Server database file (main data file)
  • .ndf - SQL Server database file (when multiple files are used)
  • .ldf - SQL Server database log file
Top




Connection Strings

A connection to a database is established by using a connection string which provides the .NET framework with the information required to find the database (server name, database path,database instance). Additionally the connection string provides the information needed to authenticate the user to the database (user name, password). Connection classes are derived from the DbConnection base class. Databases are unmanaged resources, so DbConnection implements IDiposable. Putting the creation of the connection instance inside a "using" statement will automatically close and properly dispose of the database connection.

The website ConnectionStrings.com can help determine how to create the connection string for a particular type of database. Below is a simple example of creating a connection string for an SQL Server Compact database.

Connection String Hard Coded in Application
 // Build Connection String for Microsoft SQL Server Compact 3.5 
string connectString = @"Data Source = |DataDirectory|\Database1.sdf"; 

using (SqlCeConnection ceConnection = new SqlCeConnection(connectString))
{
}

During the development cycle of an application the connection string frequently needs to change to point to different databases. This can more easily be accomplished if the connection string is not hard coded in the application. Instead the connection string can be stored in the app.config (e.g. ConsoleApplication5.exe.config) or web.config files, which allow the connection strings to be changed without recompiling the program. To reference the connection string stored in the configuration file, the ConfigurationManager.ConnectionStrings property (from System.Configuration.dll) can be used in the program.

Connection String Stored in app.config File
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <configSections>
    </configSections>
    <connectionStrings>
        <add name="Database1ConnectionString"
            connectionString="Data Source=|DataDirectory|\Database1.sdf"
            providerName="Microsoft.SqlServerCe.Client.3.5" />
    </connectionStrings>
</configuration>


Reference Connection String Stored in app.config File
// Reference Connection String in app.config File
string connectString = ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ConnectionString;

If a database connection is open too long it can prevent other users from accessing the database. However opening and closing database connections consume a considerable amount of time as several steps are required (handshake, authentication, parsing, etc.). For this reason .NET uses connection pooling by default. Each application maintains a number of connections to a database. .NET checks for an available connection from the pool when a new connection is requested. Only connections with the same configuration can be in a particular pool. .NET maintains several pools at the same time, one for each configuration. Connection string modifiers are used to control connection pooling behavior.

Top




CRUD Operations

C# supports the Create, Read, Update, and Delete (CRUD) operations in both synchronous and asynchronous modes with the SqlCommand and corresponding SqlCeCommand classes . The command object has a ExecuteReader method for creating a forward read-only cursor (known as an SqlDataReader). The command object has an ExecuteNonQuery method for executing Update, Insert, and Delete statements.

SqlDataReader - Forward Read-Only Cursor

Invoking the Command object's ExecuteReader method creates an SqlDataReader which is a forward read-only cursor (a.k.a. firehose cursor). The columns in the result set can be accessed by name or by index. The SqlDataReader has several Getxxx methods which allow the mapping of column value to a .NET type. (e.g. GetInt32, GetDouble, etc).

It is possible to batch multiple SQL statements together in an SqlDataReader to return multiple result sets. The NextResult method allows you to advance to the next result set. Note: Multiple results sets are not supported in the SQL Server Compact edition.

The following example uses an SqlCeReader to display all the rows from a table:

SqlDataReader Code Example

using System;
using System.Data.SqlServerCe;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Collections.Generic;

namespace DataReaderExample
{
    class Program
    {
        static void Main()
        {
            // Reference Connection String in ConsoleApplication5.exe.config File
            string connectString = ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ConnectionString;

            using (SqlCeConnection ceConnection = new SqlCeConnection(connectString))
            {

                // Query String
                string queryString = @"Select T1.ID, T1.Name, T2.ID, T2.Cost from Customers T1
                                       inner join Orders T2
                                       on T1.ID = T2.Customer_ID";

                SqlCeCommand queryCommand = new SqlCeCommand(queryString, ceConnection);

                try
                {
                    // Open Database
                    ceConnection.Open();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }

                // Read Contents of Result Set
                Console.WriteLine("\n----------- Contents of Orders Table -----------");
                SqlCeDataReader reader = queryCommand.ExecuteReader();
                try
                {
                    while (reader.Read())
                    {
                        Console.WriteLine(String.Format("Customer ID: {0} Customer Name: {1} Order ID: {2}  Cost: {3:C}",
                                            reader[0], reader[1], reader[2], reader[3]));
                    }
                }
                finally
                {
                    reader.Close();
                }
            }
        }
    }
}

ExecuteNonQuery - Update, Insert, and Delete

The ExecuteNonQuery method of the command class can be used to execute Update, Insert, and Delete SQL statements against a database. Additionally it can be used to perform catalog operations such as create tables or query the structure of a database. The return value for the ExecuteNonQuery class is the number of rows affected for the Update, Insert, and Delete commands, -1 for all other data management commands, and 0 for data definition commands (e.g. Create Table, Alter Table, etc).

The following program uses ExecuteNonQuery to create a table and then insert 9 rows of data into the table.

ExecuteNonQuery Code Example

using System;
using System.Data.SqlServerCe;
using System.Configuration;

namespace ExecuteNonQueryExample
{
    class Program
    {
        static void Main()
        {
            // Reference Connection String in app.config File
            string connectString = ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ConnectionString;

            using (SqlCeConnection ceConnection = new SqlCeConnection(connectString))
            {
                // Create Table String
                string createString = "Create Table Orders(ID int, Customer_ID int, Cost money);";
                SqlCeCommand createCommand = new SqlCeCommand(createString, ceConnection);

                try
                {
                    // Open Database
                    ceConnection.Open();

                    // Create Order Table
                    createCommand.ExecuteNonQuery();

                    // Insert String
                    string insertString = @"Insert Into Orders ([ID], [Customer_ID], [Cost])
                                        values (@id, @customerID, @cost);";
                    SqlCeCommand insertCommand = new SqlCeCommand(insertString, ceConnection);

                    // Multiple Inserts
                    for (int i = 1; i < 10; i++)
                    {
                        insertCommand.Parameters.Clear();
                        insertCommand.Parameters.AddWithValue("@id", i);
                        insertCommand.Parameters.AddWithValue("@customerID", 1);
                        insertCommand.Parameters.AddWithValue("@cost", i * 10);
                        insertCommand.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
        }
    }
}

Top




DataSet - Disconnected Storage

The DataSet class is a component of the ADO.NET architecture which can be viewed as a miniature database management system inside memory. Microsoft defines a DataSet as a memory-resident representation of data that provides a consistent relational programming model independent of the data source. The DataSet represents a complete set of data that includes tables, constraints, and relationships among the tables. DataSet allows you to work with the contents of a database while you are disconnected from the database. DataSet contains DataTables which represent one table in memory, and DataRow which represents one table row in memory. A DataAdapter object is used to populate DataSets from existing data sources.

The following program creates a DataSet with one DataTable which is loaded from an existing database using a DataAdapter. The DataRows within the DataTable are then dumped in XML format (DataSet.GetXml method) and are also displayed in text.

.DataSet Created from Database



Create DataSet from Database and Dump in XML and Text Formats

using System;
using System.Data.SqlServerCe;
using System.Data;
using System.Configuration;

namespace DataSetExample1
{
    class Program
    {
        static void Main(string[] args)
        {
            // Reference Connection String in config File
            string connectString = ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ConnectionString;

            using (SqlCeConnection ceConnection = new SqlCeConnection(connectString))
            {
                try
                {
                    // Open Database
                    ceConnection.Open();

                    //Use DataAdapter to Load Dataset from Existing Database.
                    SqlCeDataAdapter da = new SqlCeDataAdapter();
                    DataSet ds = new DataSet();
                    DataTable dt = new DataTable();

                    da.SelectCommand = new SqlCeCommand("SELECT * FROM Customers", ceConnection);
                    da.Fill(ds, "Customers");
                    dt = ds.Tables["Customers"];

                    // Dump the Table in XML
                    Console.WriteLine("---------- Customer Table in XML ----------");
                    Console.WriteLine(ds.GetXml());

                    // Display the Table Contents in Text Format
                    Console.WriteLine("\n---------- Customer Table in Text ---------");

                    foreach (DataRow dr in dt.Rows)
                    {
                        Console.WriteLine("ID: {0}, Name: {1}", dr["ID"].ToString(), dr["Name"].ToString());
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
                Console.WriteLine();
            }
        }
    }
}


Top




SQL Injection

SQL injection is a technique where SQL code is inserted in user input fields to alter the SQL executed on a database. A typical example is to enter ' OR '1'='1 in a field, instead a value such as KEVIN. This could alter the SQL from this statement:

SELECT * FROM users WHERE name = 'KEVIN'




to this statement:

SELECT * FROM users WHERE name = '' OR '1'='1'



which would result in all the users being selected from the database. If this code was used in an authentication procedure, it could be used to force the selection of a valid username.

A basic rule for preventing SQL injection is to never directly use the user's input in SQL strings. Many languages, including C#, support Parameterized Queries which bind the user input to a variable before putting the user's input into an SQL string. Binding the user's input to a variable, and then using the variable as a parameter, will frequently defeat the SQL injection. What happens is the embedded SQL fragment will be treated as an invalid variable or parameter value and the SQL string will not be created. Additional ways to guard against SQL injection include properly limiting database permissions and pattern checking user input (e.g. use regular expressions to validate data patterns)

Another method of SQL injection is to cause an SQL statement to be appended to an existing SQL statement. For example, if the user input was '; DROP TABLE users; the following SQL statements could be created:

SELECT * FROM users WHERE name = ''; DROP table users;



Many database management systems allow multiple SQL statement to be executed within one call. However some do not, or can be configured to not allow this for security reasons. Also some API's contain calls which will limit the number of SQL statements per call to only one.

Parameterized Queries

In addition to being more secure, parameterized queries offer better performance. They are interpreted by the database as more of a generic query which makes it easier for the database to find a precompiled execution plan. The SqlCommand and SqlCeCommand classes contain a parameter property for storing a collection of parameters which are used with the SQL command. The parameters in the collection must match the requirements of the query being executed, otherwise an error will occur.

The following example uses three parameters with an SQL insert statement. The parameter names are prefixed with the "@"symbol. The Clear() method removes all the parameters from the collection. The example uses a loop to insert 9 rows into the database.

Using Parameterized Query to Insert Data

// Insert String
string insertString = @"Insert Into Orders ([ID], [Customer_ID], [Cost])
                    values (@id, @customerID, @cost);";
SqlCeCommand insertCommand = new SqlCeCommand(insertString, ceConnection);

// Multiple Inserts
for (int i = 1; i < 10; i++)
{
    insertCommand.Parameters.Clear();
    insertCommand.Parameters.AddWithValue("@id", i);
    insertCommand.Parameters.AddWithValue("@customerID", 1);
    insertCommand.Parameters.AddWithValue("@cost", i * 10);
    insertCommand.ExecuteNonQuery();
}

Top




Transactions

A database transaction is comprised of one or more operations grouped together into a unit of work. If one of the operations in the group fails, then all the operations in the group also fail. This "all-or-nothing" characteristic of a transaction helps the data maintain its integrity within the database. Each transaction results in the data maintaining a consistent state. A committed transaction ensures the entire unit of work is permanently updated in the database, even if the database crashes.

The System.Transactions namespace contains classes for creating transactions. The namespace provides an explicit programming model based on the Transaction class, as well as an implicit programming model using the TransactionScope class. The implicit TransactionScope creates transactions which are automatically managed by the infrastructure. Microsoft recommends the use of the easier implicit model for development.

The TransactionScope class makes a code block transactional. If nested connections, multiple databases, or multiple resources are involved, the transaction is automatically promoted to a distributed transaction. Note: Distributed transactions are not supported by SQL Server Compact. When the application completes all work it wants to perform in a transaction, the Complete method should be called (only once) to inform that transaction manager that it is acceptable to commit the transaction. If the Complete method is not called, the entire transaction is aborted. The TransactionScope should be coded inside a using statement to ensure the associated resources are properly disposed.

Inside the TransactionScope constructor are:

  1. TransactionScopeOption Enumeration
    • Required - used an ambient transaction if one already exists, else it creates a new transaction.
    • RequiresNew - a new transaction is always created.
    • Suppress - does not take part in any transaction.
  2. TimeSpan - represents a time interval after which the transaction aborts.

The following program incorporates two insert commands into one transaction. Both or neither of the insert commands will be successful.

Implicit Transaction using TransactionScope

using System;
using System.Data.SqlServerCe;
using System.Configuration;
using System.Transactions;

namespace TransactionExample
{
    class Program
    {
        static void Main()
        {
            // Reference Connection String in ConsoleApplication5.exe.config File
            string connectString = ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ConnectionString;

            using (TransactionScope transactionScope = new TransactionScope())
            {
                using (SqlCeConnection ceConnection = new SqlCeConnection(connectString))
                {
                    // First Insert String
                    string insertString1 = "Insert into Customers ([ID], [Name]) values (3, 'Shadow')";
                    SqlCeCommand insertCommand1 = new SqlCeCommand(insertString1, ceConnection);

                    // Second Insert String
                    string insertString2 = "Insert into Orders ([ID], [Customer_ID], [Cost]) values (1, 3, 9.99)";
                    SqlCeCommand insertCommand2 = new SqlCeCommand(insertString2, ceConnection);

                    try
                    {
                        // Open Database
                        ceConnection.Open();

                        // Execute the Insert Commands
                        int numRows1 = insertCommand1.ExecuteNonQuery();
                        int numRows2 = insertCommand2.ExecuteNonQuery();
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e.Message);
                    }
                }
                // Commit the Transaction
                transactionScope.Complete();
            }
        }
    }
}

Top




Entity Framework - Object Relational Mapping

The phrase object-relational impedance mismatch is used to describe a set of conceptual and technical difficulties that are often encountered when a relational database is being used by a program written in an Object-Oriented Programming language (OOP). The essential OOP concepts of encapsulation, inheritance, and polymorphism are not supported by relational databases. There are also significant difference between the two models in the way data is structured, manipulated, and accessed. Some relational database advocates argue that traditional "procedural" languages should be used with relational databases, while OOP advocates argue for the development of object-oriented database management systems. Another alternative to bridge the fundamental differences between relational and object-oriented data is to provide an abstraction layer that maps the "Conceptual Model" (Business Model) to the "Physical Model" (Database Model). Microsoft's Entity Framework provides this object-relational mapping (ORM).

The additional layer of abstraction provided by an ORM decreases the performance of a system, but can improve development speed. ORM resolves the object code and relational database mismatch with three approaches: bottom up, top-down and meet in the middle. Each approach has its share of benefits and drawbacks. Sometimes a hybrid approach is used, with an ORM approached used initially, then stored procedures are developed for those areas where performance is a problem. The Entity Framework is an open-source ORM project hosted on Microsoft's CodePlex site. The Microsoft Data Development Center provides documentation and tutorials for Entity Framework.

Other ORM frameworks for .NET predate Entity Framework. NHibernate is a leading ORM framework with version 1.2.1 released in 2007 and the 3.3.3 version released in 2013. NHibernate is also open-source and is distributed under the GNU license. The NHibernate homepage describes the framework as mature, fully featured, and actively developed. ORM frameworks have been developed for various OOP languages (Java, Ruby, PHP). SQLAlchemy is an ORM for Python which is used by a number of large organizations.

ORM's have created disagreement among experts. Key concerns are performance, complexity, scalability, and poorly-designed databases. Martin Fowler concludes in this ORM Hate article:

"So ORMs help us deal with a very real problem for most enterprise applications. It's true they are often misused, and sometimes the underlying problem can be avoided. They aren't pretty tools, but then the problem they tackle isn't exactly cuddly either. I think they deserve a little more respect and a lot more understanding."

Top




Local SQL Code Example

.Local SQL Code Example
Local SQL Code Example

The following program connects to a local Microsoft SQL Compact database and displays the path to the database, the database version, and the database connection state. It then creates a new table called Orders and populates the table with 9 rows of data using the ExecuteNonQuery method. Next it creates a DataReader and reads the contents of the new table and displays the data on the screen.

using System;
using System.Data.SqlServerCe;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Collections.Generic;

namespace LocalDatabaseExample
{
    class Program
    {
        static void Main()
        {
            // Reference Connection String in app.config File
            string connectString = ConfigurationManager.ConnectionStrings["Database1ConnectionString"].ConnectionString;

            using (SqlCeConnection ceConnection = new SqlCeConnection(connectString))
            {
                // Create Table String
                string createString = "Create Table Orders(ID int, Customer_ID int, Cost money);";
                SqlCeCommand createCommand = new SqlCeCommand(createString, ceConnection);

                try
                {
                    // Open Database
                    ceConnection.Open();

                    // Display Database Information
                    Console.WriteLine("\n------------- Database Information -------------");
                    Console.WriteLine("Database  : {0}", ceConnection.Database);
                    Console.WriteLine("Version   : {0}", ceConnection.ServerVersion);
                    Console.WriteLine("Connection: {0}\n", ceConnection.State);

                    // Create Order Table
                    createCommand.ExecuteNonQuery();

                    // Insert String
                    string insertString = @"Insert Into Orders ([ID], [Customer_ID], [Cost])
                                        values (@id, @customerID, @cost);";
                    SqlCeCommand insertCommand = new SqlCeCommand(insertString, ceConnection);

                    // Multiple Inserts
                    for (int i = 1; i < 10; i++)
                    {
                        insertCommand.Parameters.Clear();
                        insertCommand.Parameters.AddWithValue("@id", i);
                        insertCommand.Parameters.AddWithValue("@customerID", 1);
                        insertCommand.Parameters.AddWithValue("@cost", i * 10);
                        insertCommand.ExecuteNonQuery();
                    }

                    // Query String
                    string queryString = @"Select T1.ID, T1.Name, T2.ID, T2.Cost from Customers T1
                                       inner join Orders T2
                                       on T1.ID = T2.Customer_ID";
                    SqlCeCommand queryCommand = new SqlCeCommand(queryString, ceConnection);

                    // Read Contents of Result Set
                    Console.WriteLine("\n----------- Contents of Orders Table -----------");
                    SqlCeDataReader reader = queryCommand.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine(String.Format("Customer ID: {0} Customer Name: {1} Order ID: {2}  Cost: {3:C}",
                                                reader[0], reader[1], reader[2], reader[3]));
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }
        }
    }
}

Top



Reference Articles

Top