The following example demonstrates how to use both the SqlConnection and OleDbConnection classes to open a connection to a Microsoft SQL Server Express database running on the local machine that uses integrated Windows security:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_01
    {
        public static void SqlConnectionExample()
        {
            // Create an empty SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString =
                    @"Data Source=.sqlexpress;" + // local SQL Server instance
                    "Database=Northwind;" +        // the sample Northwind DB
                    "Integrated Security=SSPI";    // integrated Windows security

                // Open the database connection.
                con.Open();

// Display information about the connection.
                if (con.State == ConnectionState.Open)
                {
                    Console.WriteLine("SqlConnection Information:");
                    Console.WriteLine("  Connection State = " + con.State);
                    Console.WriteLine("  Connection String = " +
                        con.ConnectionString);
                    Console.WriteLine("  Database Source = " + con.DataSource);
                    Console.WriteLine("  Database = " + con.Database);
                    Console.WriteLine("  Server Version = " + con.ServerVersion);
                    Console.WriteLine("  Workstation Id = " + con.WorkstationId);
                    Console.WriteLine("  Timeout = " + con.ConnectionTimeout);
                    Console.WriteLine("  Packet Size = " + con.PacketSize);
                }
                else
                {
                    Console.WriteLine("SqlConnection failed to open.");
                    Console.WriteLine("  Connection State = " + con.State);
                }
                // At the end of the using block Dispose() calls Close().
            }
        }

        public static void OleDbConnectionExample()
        {

            // Create an empty OleDbConnection object.
            using (OleDbConnection con = new OleDbConnection())
            {
                // Configure the OleDbConnection object's connection string.
                con.ConnectionString =
                    "Provider=SQLOLEDB;" +         // OLE DB Provider for SQL Server
                    @"Data Source=.sqlexpress;" + // local SQL Server instance
                    "Initial Catalog=Northwind;" + // the sample Northwind DB
                    "Integrated Security=SSPI";      // integrated Windows security

                // Open the database connection.
                con.Open();

                // Display information about the connection.
                if (con.State == ConnectionState.Open)
                {
                    Console.WriteLine("OleDbConnection Information:");
                    Console.WriteLine("  Connection State = " + con.State);
                    Console.WriteLine("  Connection String = " +
                        con.ConnectionString);
                    Console.WriteLine("  Database Source = " + con.DataSource);
                    Console.WriteLine("  Database = " + con.Database);
                    Console.WriteLine("  Server Version = " + con.ServerVersion);
                    Console.WriteLine("  Timeout = " + con.ConnectionTimeout);
                }

else
                {
                    Console.WriteLine("OleDbConnection failed to open.");
                    Console.WriteLine("  Connection State = " + con.State);
                }
                // At the end of the using block Dispose() calls Close().
            }
        }

        public static void Main()
        {
            // Open connection using SqlConnection.
            SqlConnectionExample();
            Console.WriteLine(Environment.NewLine);

            // Open connection using OleDbConnection.
            OleDbConnectionExample();

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the configuration of a connection pool that contains a minimum of 5 and a maximum of 15 connections. Connections expire after 10 minutes (600 seconds) and are reset each time a connection is obtained from the pool. The example also demonstrates how to use the Pooling setting to obtain a connection object that is not from a pool. This is useful if your application uses a single long-lived connection to a database.

using System;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_02
    {
        public static void Main()
        {
            // Obtain a pooled connection.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString =
                    @"Data Source = .sqlexpress;" +// local SQL Server instance
                    "Database = Northwind;" +       // the sample Northwind DB
                    "Integrated Security = SSPI;" + // integrated Windows security
                    "Min Pool Size = 5;" +          // configure minimum pool size
                    "Max Pool Size = 15;" +         // configure maximum pool size
                    "Connection Reset = True;" +    // reset connections each use
                    "Connection Lifetime = 600";    // set max connection lifetime

                // Open the database connection.
                con.Open();

                // Access the database . . .

                // At the end of the using block, the Dispose calls Close, which
                // returns the connection to the pool for reuse.
            }

            // Obtain a nonpooled connection.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString =
                    @"Data Source = .sqlexpress;" +//local SQL Server instance
                    "Database = Northwind;" +       //the sample Northwind DB
                    "Integrated Security = SSPI;" + //integrated Windows security
                    "Pooling = False";              //specify nonpooled connection

                // Open the database connection.
                con.Open();

                // Access the database . . .

                // At the end of the using block, the Dispose calls Close, which
                // closes the nonpooled connection.
            }

// Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the use of the SqlConnectionStringBuilder class to parse and construct SQL Server connection strings:

using System;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_03
    {
        public static void Main(string[] args)
        {
            string conString = @"Data Source=.sqlexpress;" +
                "Database=Northwind;Integrated Security=SSPI;" +
                "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" +
                "Connection Lifetime=600;";

            // Parse the SQL Server connection string and display the component
            // configuration parameters.
            SqlConnectionStringBuilder sb1 =
                new SqlConnectionStringBuilder(conString);

            Console.WriteLine("Parsed SQL Connection String Parameters:");
            Console.WriteLine("  Database Source = " + sb1.DataSource);
            Console.WriteLine("  Database = " + sb1.InitialCatalog);
            Console.WriteLine("  Use Integrated Security = "
                + sb1.IntegratedSecurity);
            Console.WriteLine("  Min Pool Size = " + sb1.MinPoolSize);
            Console.WriteLine("  Max Pool Size = " + sb1.MaxPoolSize);
            Console.WriteLine("  Lifetime = " + sb1.LoadBalanceTimeout);

// Build a connection string from component parameters and display it.
            SqlConnectionStringBuilder sb2 =
                new SqlConnectionStringBuilder(conString);

            sb2.DataSource = @".sqlexpress";
            sb2.InitialCatalog = "Northwind";
            sb2.IntegratedSecurity = true;
            sb2.MinPoolSize = 5;
            sb2.MaxPoolSize = 15;
            sb2.LoadBalanceTimeout = 600;

            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Constructed connection string:");
            Console.WriteLine("  " + sb2.ConnectionString);

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the writing of an encrypted connection string to the application's configuration file and the subsequent reading and use of that connection string.

using System;
using System.Configuration;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_04
    {
        private static void WriteEncryptedConnectionStringSection(
            string name, string constring, string provider)
        {
            // Get the configuration file for the current application. Specify
            // the ConfigurationUserLevel.None argument so that we get the
            // configuration settings that apply to all users.
            Configuration config = ConfigurationManager.OpenExeConfiguration(
                ConfigurationUserLevel.None);

            // Get the connectionStrings section from the configuration file.
            ConnectionStringsSection section = config.ConnectionStrings;

            // If the connectionString section does not exist, create it.
            if (section == null)
            {
                section = new ConnectionStringsSection();
                config.Sections.Add("connectionSettings", section);
            }

            // If it is not already encrypted, configure the connectionStrings
            // section to be encrypted using the standard RSA Proected
            // Configuration Provider.
            if (!section.SectionInformation.IsProtected)
            {
                // Remove this statement to write the connection string in clear
                // text for the purpose of testing.
                section.SectionInformation.ProtectSection(
                    "RsaProtectedConfigurationProvider");
            }

            // Create a new connection string element and add it to the
            // connection string configuration section.
            ConnectionStringSettings cs =
                new ConnectionStringSettings(name, constring, provider);
            section.ConnectionStrings.Add(cs);

// Force the connection string section to be saved.
            section.SectionInformation.ForceSave = true;

            // Save the updated configuration file.
            config.Save(ConfigurationSaveMode.Full);
        }

        public static void Main(string[] args)
        {
            // The connection string information to be written to the
            // configuration file.
            string conName = "ConnectionString1";
            string conString = @"Data Source=.sqlexpress;" +
                "Database=Northwind;Integrated Security=SSPI;" +
                "Min Pool Size=5;Max Pool Size=15;Connection Reset=True;" +
                "Connection Lifetime=600;";
            string providerName = "System.Data.SqlClient";

            // Write the new connection string to the application's
            // configuration file.
            WriteEncryptedConnectionStringSection(conName, conString, providerName);

            // Read the encrypted connection string settings from the
            // application's configuration file.
            ConnectionStringSettings cs2 =
                ConfigurationManager.ConnectionStrings["ConnectionString1"];

            // Use the connection string to create a new SQL Server connection.
            using (SqlConnection con = new SqlConnection(cs2.ConnectionString))
            {
                // Issue database commands/queries . . .

            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the use of command objects to update a database record, run a stored procedure, and obtain a scalar value:

using System;
using System.Data;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_05
    {
        public static void ExecuteNonQueryExample(IDbConnection con)
        {
            // Create and configure a new command.
            IDbCommand com = con.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = "UPDATE Employees SET Title = 'Sales Director'" +
                " WHERE EmployeeId = '5'";

            // Execute the command and process the result.
            int result = com.ExecuteNonQuery();

            if (result == 1)
            {
                Console.WriteLine("Employee title updated.");
            }
            else
            {
                Console.WriteLine("Employee title not updated.");
            }
        }

        public static void ExecuteReaderExample(IDbConnection con)
        {
            // Create and configure a new command.
            IDbCommand com = con.CreateCommand();
            com.CommandType = CommandType.StoredProcedure;
            com.CommandText = "Ten Most Expensive Products";

            // Execute the command and process the results.
            using (IDataReader reader = com.ExecuteReader())
            {
                Console.WriteLine("Price of the Ten Most Expensive Products.");

                while (reader.Read())
                {
                    // Display the product details.
                    Console.WriteLine("  {0} = {1}",
                        reader["TenMostExpensiveProducts"],
                        reader["UnitPrice"]);
                }
            }
        }

public static void ExecuteScalarExample(IDbConnection con)
        {
            // Create and configure a new command.
            IDbCommand com = con.CreateCommand();
            com.CommandType = CommandType.Text;
            com.CommandText = "SELECT COUNT(*) FROM Employees";

            // Execute the command and cast the result.
            int result = (int)com.ExecuteScalar();

            Console.WriteLine("Employee count = " + result);
        }

        public static void Main()
        {
            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Open the database connection and execute the example
                // commands through the connection.
                con.Open();

                ExecuteNonQueryExample(con);
                Console.WriteLine(Environment.NewLine);

                ExecuteReaderExample(con);
                Console.WriteLine(Environment.NewLine);

                ExecuteScalarExample(con);
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the use of parameters in SQL commands. The ParameterizedCommandExample method demonstrates the use of parameters in a SQL Server UPDATE statement. The ParameterizedCommandExample method's arguments include an open SqlConnection and two strings. The values of the two strings are substituted into the UPDATE command using parameters. The StoredProcedureExample method demonstrates the use of parameters to call a stored procedure.

using System;
using System.Data;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_06
    {
        public static void ParameterizedCommandExample(SqlConnection con,
            string employeeID, string title)
        {
            // Create and configure a new command containing two named parameters.
            using (SqlCommand com = con.CreateCommand())
            {
                com.CommandType = CommandType.Text;
                com.CommandText = "UPDATE Employees SET Title = @title" +
                    " WHERE EmployeeId = @id";

                // Create a SqlParameter object for the title parameter.
                SqlParameter p1 = com.CreateParameter();
                p1.ParameterName = "@title";
                p1.SqlDbType = SqlDbType.VarChar;
                p1.Value = title;
                com.Parameters.Add(p1);

                // Use a shorthand syntax to add the id parameter.
                com.Parameters.Add("@id", SqlDbType.Int).Value = employeeID;

                // Execute the command and process the result.
                int result = com.ExecuteNonQuery();

                if (result == 1)
                {
                    Console.WriteLine("Employee {0} title updated to {1}.",
                        employeeID, title);
                }
                else
                {
                    Console.WriteLine("Employee {0} title not updated.",
                        employeeID);
                }
            }
        }

        public static void StoredProcedureExample(SqlConnection con,
            string category, string year)
        {

// Create and configure a new command.
            using (SqlCommand com = con.CreateCommand())
            {
                com.CommandType = CommandType.StoredProcedure;
                com.CommandText = "SalesByCategory";

                // Create a SqlParameter object for the category parameter.
                com.Parameters.Add("@CategoryName", SqlDbType.NVarChar).Value =
                    category;

                // Create a SqlParameter object for the year parameter.
                com.Parameters.Add("@OrdYear", SqlDbType.NVarChar).Value = year;

                // Execute the command and process the results.
                using (IDataReader reader = com.ExecuteReader())
                {
                    Console.WriteLine("Sales By Category ({0}).", year);

                    while (reader.Read())
                    {
                        // Display the product details.
                        Console.WriteLine("  {0} = {1}",
                            reader["ProductName"],
                            reader["TotalPurchase"]);
                    }
                }
            }
        }

        public static void Main()
        {
            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Open the database connection and execute the example
                // commands through the connection.
                con.Open();

                ParameterizedCommandExample(con, "5", "Cleaner");
                Console.WriteLine(Environment.NewLine);

                StoredProcedureExample(con, "Seafood", "1999");
                Console.WriteLine(Environment.NewLine);
            }

// Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the use of a data reader to process the contents of two result sets returned by executing a batch query containing two SELECT queries. The first result set is enumerated and displayed to the console. The second result set is inspected for metadata information, which is then displayed.

using System;
using System.Data;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_07
    {
        public static void Main()
        {
            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Create and configure a new command.
                using (SqlCommand com = con.CreateCommand())
                {
                    com.CommandType = CommandType.Text;
                    com.CommandText = "SELECT BirthDate,FirstName,LastName FROM "+
                        "Employees ORDER BY BirthDate;SELECT * FROM Employees";

                    // Open the database connection and execute the example.
                    // commands through the connection.
                    con.Open();

                    // Execute the command and obtain a SqlReader.
                    using (SqlDataReader reader = com.ExecuteReader())
                    {
                        // Process the first set of results and display the
                        // content of the result set.
                        Console.WriteLine("Employee Birthdays (By Age).");

while (reader.Read())
                        {
                            Console.WriteLine("  {0,18:D} - {1} {2}",
                                reader.GetDateTime(0),  // Retrieve typed data
                                reader["FirstName"],    // Use string index
                                reader[2]);             // Use ordinal index
                        }
                        Console.WriteLine(Environment.NewLine);

                        // Process the second set of results and display details
                        // about the columns and data types in the result set.
                        reader.NextResult();
                        Console.WriteLine("Employee Table Metadata.");
                        for (int field = 0; field < reader.FieldCount; field++)
                        {
                            Console.WriteLine("  Column Name:{0}  Type:{1}",
                                reader.GetName(field),
                                reader.GetDataTypeName(field));
                        }
                    }
                }
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates how to retrieve results as XML using the FOR XML clause and the ExecuteXmlReader method:

using System;
using System.Xml;
using System.Data;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_08
    {
        public static void ConnectedExample()
        {
            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Create and configure a new command that includes the
                // FOR XML AUTO clause.
                using (SqlCommand com = con.CreateCommand())
                {
                    com.CommandType = CommandType.Text;
                    com.CommandText = "SELECT CustomerID, CompanyName" +
                        " FROM Customers FOR XML AUTO";

                    // Open the database connection.
                    con.Open();

                    // Execute the command and retrieve an XmlReader to access
                    // the results.
                    using (XmlReader reader = com.ExecuteXmlReader())
                    {
                        while (reader.Read())
                        {
                            Console.Write("Element: " + reader.Name);
                            if (reader.HasAttributes)
                            {
                                for (int i = 0; i < reader.AttributeCount; i++)
                                {
                                    reader.MoveToAttribute(i);
                                    Console.Write("  {0}: {1}",
                                        reader.Name, reader.Value);
                                }

// Move the XmlReader back to the element node.
                                reader.MoveToElement();
                                Console.WriteLine(Environment.NewLine);
                            }
                        }
                    }
                }
            }
        }

        public static void DisconnectedExample()
        {
            XmlDocument doc = new XmlDocument();

            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Create and configure a new command that includes the
                // FOR XML AUTO clause.
                SqlCommand com = con.CreateCommand();
                com.CommandType = CommandType.Text;
                com.CommandText =
                    "SELECT CustomerID, CompanyName FROM Customers FOR XML AUTO";

                // Open the database connection.
                con.Open();

                // Load the XML data into the XmlDocument. Must first create a
                // root element into which to place each result row element.
                XmlReader reader = com.ExecuteXmlReader();
                doc.LoadXml("<results></results>");

                // Create an XmlNode from the next XML element read from the
                // reader.
                XmlNode newNode = doc.ReadNode(reader);

                while (newNode != null)
                {
                    doc.DocumentElement.AppendChild(newNode);
                    newNode = doc.ReadNode(reader);
                }
            }

            // Process the disconnected XmlDocument.
            Console.WriteLine(doc.OuterXml);
        }

public static void Main(string[] args)
        {
            ConnectedExample();
            Console.WriteLine(Environment.NewLine);

            DisconnectedExample();
            Console.WriteLine(Environment.NewLine);

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

Recipe 4-2 provides examples of all of the completion techniques summarized in the preceding list. The following example demonstrates the use of an asynchronous call to execute a stored procedure on a SQL Server database. The code uses a callback to process the returned result set.

using System;
using System.Data;
using System.Threading;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_09
    {
        // A method to handle asynchronous completion using callbacks.
        public static void CallbackHandler(IAsyncResult result)
        {
            // Obtain a reference to the SqlCommand used to initiate the
            // asynchronous operation.
            using (SqlCommand cmd = result.AsyncState as SqlCommand)
            {
                // Obtain the result of the stored procedure.
                using (SqlDataReader reader = cmd.EndExecuteReader(result))
                {

// Display the results of the stored procedure to the console.
                    lock (Console.Out)
                    {
                        Console.WriteLine(
                            "Price of the Ten Most Expensive Products:");

                        while (reader.Read())
                        {
                            // Display the product details.
                            Console.WriteLine("  {0} = {1}",
                                reader["TenMostExpensiveProducts"],
                                reader["UnitPrice"]);
                        }
                    }
                }
            }
        }

        public static void Main()
        {
            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                // You must specify Asynchronous Processing=true to support
                // asynchronous operations over the connection.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI;" +
                    "Asynchronous Processing=true";

                // Create and configure a new command to run a stored procedure.
                // Do not wrap it in a using statement because the asynchronous
                // completion handler will dispose of the SqlCommand object.
                SqlCommand cmd = con.CreateCommand();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "Ten Most Expensive Products";

                // Open the database connection and execute the command
                // asynchronously. Pass the reference to the SqlCommand
                // used to initiate the asynchronous operation.
                con.Open();
                cmd.BeginExecuteReader(CallbackHandler, cmd);

// Continue with other processing.
                for (int count = 0; count < 10; count++)
                {
                    lock (Console.Out)
                    {
                        Console.WriteLine("{0} : Continue processing...",
                            DateTime.Now.ToString("HH:mm:ss.ffff"));
                    }
                    Thread.Sleep(500);
                }
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the enumeration of all data providers configured for the local machine and application. It then uses the DbProviderFactories class to instantiate a DbProviderFactory object (actually a SqlClientFactory) from which it creates the appropriate IDbConnection. It then uses the factory methods of the data provider interfaces to create other required objects, resulting in code that is completely generic.

using System;
using System.Data;
using System.Data.Common;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_10
    {
        public static void Main(string[] args)
        {
            // Obtain the list of ADO.NET data providers registered in the
            // machine and application configuration files.
            using (DataTable providers = DbProviderFactories.GetFactoryClasses())
            {
                // Enumerate the set of data providers and display details.
                Console.WriteLine("Available ADO.NET Data Providers:");
                foreach (DataRow prov in providers.Rows)
                {
                    Console.WriteLine(" Name:{0}", prov["Name"]);
                    Console.WriteLine("   Description:{0}",
                        prov["Description"]);
                    Console.WriteLine("   Invariant Name:{0}",
                        prov["InvariantName"]);
                }
            }

            // Obtain the DbProviderFactory for SQL Server. The provider to use
            // could be selected by the user or read from a configuration file.
            // In this case, we simply pass the invariant name.
            DbProviderFactory factory =
                DbProviderFactories.GetFactory("System.Data.SqlClient");

            // Use the DbProviderFactory to create the initial IDbConnection, and
            // then the data provider interface factory methods for other objects.
            using (IDbConnection con = factory.CreateConnection())
            {
                // Normally, read the connection string from secure storage.
                // See recipe 9-3. In this case, use a default value.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

// Create and configure a new command.
                using (IDbCommand com = con.CreateCommand())
                {
                    com.CommandType = CommandType.StoredProcedure;
                    com.CommandText = "Ten Most Expensive Products";

                    // Open the connection.
                    con.Open();

                    // Execute the command and process the results.
                    using (IDataReader reader = com.ExecuteReader())
                    {
                        Console.WriteLine(Environment.NewLine);
                        Console.WriteLine("Price of the Ten Most" +
                            " Expensive Products.");

                        while (reader.Read())
                        {
                            // Display the product details.
                            Console.WriteLine("  {0} = {1}",
                                reader["TenMostExpensiveProducts"],
                                reader["UnitPrice"]);
                        }
                    }
                }
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example demonstrates the use of the SqlDataSourceEnumerator class to discover and display details of all SQL Server instances accessible (and visible) on the network. The IsClustered and Version columns may be blank for some versions of SQL Server.

using System;
using System.Data;
using System.Data.Sql;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_11
    {
        public static void Main(string[] args)
        {
            // Obtain the DataTable of SQL Server instances.
            using (DataTable SqlSources =
                SqlDataSourceEnumerator.Instance.GetDataSources())
            {
                // Enumerate the set of SQL Servers and display details.
                Console.WriteLine("Discover SQL Server Instances:");
                foreach (DataRow source in SqlSources.Rows)
                {
                    Console.WriteLine(" Server Name:{0}", source["ServerName"]);
                    Console.WriteLine("   Instance Name:{0}",
                        source["InstanceName"]);
                    Console.WriteLine("   Is Clustered:{0}",
                        source["IsClustered"]);
                    Console.WriteLine("   Version:{0}", source["Version"]);
                }
            }

// Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following example creates a DataSet and fills it with the contents of the Region table of the Northwind sample database. The DataSet contains one DataTable, whose schema and contents are printed out. A new record is added and an existing one modified before the changes are written back to the database.

using System;
using System.Data;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_12
    {
        static void Main(string[] args)
        {

            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Open the database connection.
                con.Open();

                // Create the query string.
                string query = "SELECT * from Region";

                // Create the data set.
                DataSet dataset = new DataSet();

                // Create the SQL data adapter.
                SqlDataAdapter adapter = new SqlDataAdapter(query, con);
                // Create the command builder so we can do modifications.
                SqlCommandBuilder commbuilder = new SqlCommandBuilder(adapter);

                // Populate the data set from the database.
                adapter.Fill(dataset);

// Print details of the schema.
                Console.WriteLine("
Schema for table");
                DataTable table = dataset.Tables[0];
                foreach (DataColumn col in table.Columns)
                {
                    Console.WriteLine("Column: {0} Type: {1}",
                       col.ColumnName, col.DataType);
                }

                // Enumerate the data we have received.
                Console.WriteLine("
Data in table");
                foreach (DataRow row in table.Rows)
                {
                    Console.WriteLine("Data {0} {1}", row[0], row[1]);
                }

                // Create a new row.
                DataRow newrow = table.NewRow();
                newrow["RegionID"] = 5;
                newrow["RegionDescription"] = "Central";
                table.Rows.Add(newrow);

                // Modify an existing row.
                table.Rows[0]["RegionDescription"] = "North Eastern";

                // Enumerate the cached data again.
                // Enumerate the data we have received.
                Console.WriteLine("
Data in (modified) table");
                foreach (DataRow row in table.Rows)
                {
                    Console.WriteLine("Data {0} {1}", row[0], row[1]);
                }

                // Write the data back to the database.
                adapter.Update(dataset);
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

Running the example produces the following results:

Schema for table

Column: RegionID Type: System.Int32

Column: RegionDescription Type: System.String



Data in table

Data 1 Eastern

Data 2 Western

Data 3 Northern

Data 4 Southern



Data in (modified) table

Data 1 North Eastern

Data 2 Western

Data 3 Northern

Data 4 Southern

Data 5 Central





Main method complete. Press Enter.

The following code creates a DataSet that contains a single DataTable and populates it with instances of DataRow. Once populated, the same queries, modifications, and additions are performed upon it as in the previous recipe.

using System;
sing System.Data;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_13
    {
        static void Main(string[] args)
        {
            // Create the data set.
            DataSet dataset = new DataSet();

            // Create the table and add it to the data set.
            DataTable table = new DataTable("Regions");
            dataset.Tables.Add(table);

            // Create the colums for the table.
            table.Columns.Add("RegionID", typeof(int));
            table.Columns.Add("RegionDescription", typeof(string));

// Populate the table.
            string[] regions = { "Eastern", "Western", "Northern", "Southern" };
            for (int i = 0; i < regions.Length; i++)
            {
                DataRow row = table.NewRow();
                row["RegionID"] = i + 1;
                row["RegionDescription"] = regions[i];
                table.Rows.Add(row);
            }

            // Print details of the schema.
            Console.WriteLine("
Schema for table");
            foreach (DataColumn col in table.Columns)
            {
                Console.WriteLine("Column: {0} Type: {1}",
                    col.ColumnName, col.DataType);
            }

            // Enumerate the data we have received.
            Console.WriteLine("
Data in table");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine("Data {0} {1}", row[0], row[1]);
            }

            // Create a new row.
            DataRow newrow = table.NewRow();
            newrow["RegionID"] = 5;
            newrow["RegionDescription"] = "Central";
            table.Rows.Add(newrow);

            // Modify an existing row.
            table.Rows[0]["RegionDescription"] = "North Eastern";

            // Enumerate the cached data again.
            // Enumerate the data we have received.
            Console.WriteLine("
Data in (modified) table");
            foreach (DataRow row in table.Rows)
            {
                Console.WriteLine("Data {0} {1}", row[0], row[1]);
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The program produces the following output:

Schema for table

Column: RegionID Type: System.Int32

Column: RegionDescription Type: System.String



Data in table

Data 1 Eastern

Data 2 Western

Data 3 Northern

Data 4 Southern



Data in (modified) table

Data 1 North Eastern

Data 2 Western

Data 3 Northern

Data 4 Southern

Data 5 Central





Main method complete. Press Enter.

The following example creates a DataSet that contains a DataTable with all of the rows of the Northwind Region table, and then performs a LINQ query using the DataTable as the data source:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_14
    {
        static void Main(string[] args)
        {

            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Open the database connection.
                con.Open();

                // Create the query string.
                string query = "SELECT * from Region";

                // Create the data set.
                DataSet dataset = new DataSet();

// Create the SQL data adapter.
                SqlDataAdapter adapter = new SqlDataAdapter(query, con);
                // Create the command builder so we can do modifications.
                SqlCommandBuilder commbuilder = new SqlCommandBuilder(adapter);

                // Populate the data set from the database.
                adapter.Fill(dataset);

                // Obtain the data table.
                DataTable table = dataset.Tables[0];

                // Perform the LINQ query.
                IEnumerable<string> result = from e in table.AsEnumerable()
                                             where e.Field<int>(0) < 3
                                             select e.Field<string>(1);

                // Enumerate the results of the LINQ query.
                foreach (string str in result)
                {
                    Console.WriteLine("Result: {0}", str);
                }
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

Running the program gives the following results:

Result: Eastern

Result: Western





Main method complete. Press Enter.

The following example defines the type Region to represent rows in the Northwind Region table. A DataContext is created to access the data, and the Region table is used as the basis for a LINQ query, returning an IEnumeration<Region> as the result.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    [Table(Name = "Region")]
    public partial class Region
    {
        [Column]
        public int RegionID;
        [Column]
        public string RegionDescription;
    }
    class Recipe09_15
    {
        static void Main(string[] args)
        {

// Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {
                // Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Open the database connection.
                con.Open();

                // Create the data context.
                DataContext context = new DataContext(con);

                // Get the table we are interested in.
                Table<Region> regionstable = context.GetTable<Region>();

                IEnumerable<Region> result = from e in regionstable
                                             where e.RegionID < 3
                                             select e;
                foreach (Region res in result)
                {
                    Console.WriteLine("RegionID {0} Descr: {1}",
res.RegionID, res.RegionDescription);
                }
            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

The following program performs two queries against the same table and then uses the Union, Intersect, and Except methods to compare the results:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace Apress.VisualCSharpRecipes.Chapter09
{
    class Recipe09_16
    {
        static void Main(string[] args)
        {

            // Create a new SqlConnection object.
            using (SqlConnection con = new SqlConnection())
            {

// Configure the SqlConnection object's connection string.
                con.ConnectionString = @"Data Source = .sqlexpress;" +
                    "Database = Northwind; Integrated Security=SSPI";

                // Open the database connection.
                con.Open();

                // Create the query string.
                string query = "SELECT * from Region";

                // Create the data set.
                DataSet dataset = new DataSet();

                // Create the SQL data adapter.
                SqlDataAdapter adapter = new SqlDataAdapter(query, con);
                // Create the command builder so we can do modifications.
                SqlCommandBuilder commbuilder = new SqlCommandBuilder(adapter);

                // Populate the data set from the database.
                adapter.Fill(dataset);

                // Obtain the data table.
                DataTable table = dataset.Tables[0];

                // Perform the first LINQ query.
                IEnumerable<DataRow> result1 = from e in table.AsEnumerable()
                                              where e.Field<int>(0) < 3
                                              select e;

                // Enumerate the results of the first LINQ query.
                Console.WriteLine("Results from first LINQ query");
                foreach (DataRow row in result1)
                {
                    Console.WriteLine("ID: {0} Name: {1}",
                        row.Field<int>(0), row.Field<string>(1));
                }

                // Perform the first LINQ query.
                IEnumerable<DataRow> result2 = from e in table.AsEnumerable()
                                              let name = e.Field<string>(1)
                                              where name.StartsWith("North")
                                                || name.StartsWith("East")
                                              select e;

// Enumerate the results of the first LINQ query.
                Console.WriteLine("
Results from second LINQ query");
                foreach (DataRow row in result2)
                {
                    Console.WriteLine("ID: {0} Name: {1}",
                        row.Field<int>(0), row.Field<string>(1));
                }
                IEnumerable<DataRow> union = result1.Union(result2);
                // Enumerate the results.
                Console.WriteLine("
Results from union");
                foreach (DataRow row in union)
                {
                    Console.WriteLine("ID: {0} Name: {1}",
                        row.Field<int>(0), row.Field<string>(1));
                }


                IEnumerable<DataRow> intersect = result1.Intersect(result2);
                // Enumerate the results.
                Console.WriteLine("
Results from intersect");
                foreach (DataRow row in intersect)
                {
                    Console.WriteLine("ID: {0} Name: {1}",
                        row.Field<int>(0), row.Field<string>(1));
                }

                IEnumerable<DataRow> except = result1.Except(result2);
                // Enumerate the results.
                Console.WriteLine("
Results from except");
                foreach (DataRow row in except)
                {
                    Console.WriteLine("ID: {0} Name: {1}",
                        row.Field<int>(0), row.Field<string>(1));
                }

            }

            // Wait to continue.
            Console.WriteLine(Environment.NewLine);
            Console.WriteLine("Main method complete. Press Enter.");
            Console.ReadLine();
        }
    }
}

Running the sample program gives the following results:

Results from first LINQ query

ID: 1 Name: Eastern

ID: 2 Name: Western



Results from second LINQ query

ID: 1 Name: Eastern

ID: 3 Name: Northern



Results from union

ID: 1 Name: Eastern

ID: 2 Name: Western

ID: 3 Name: Northern



Results from intersect

ID: 1 Name: Eastern



Results from except

ID: 2 Name: Western




Main method complete. Press Enter.