Visual CSharp 2005 Recipes (2006) [eng]

308 C H A P T E R 9 ■ D ATA B A S E A C C E S S

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.MinPoolSize); Console.WriteLine(" Lifetime = " + sb1.LoadBalanceTimeout); Console.WriteLine(" Connection Reset = " + sb1.ConnectionReset);

// 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.MinPoolSize = 15; sb2.LoadBalanceTimeout = 600; sb2.ConnectionReset = true;

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();

}

}

}

9-4. Store a Database Connection String Securely

Problem

You need to store a database connection string securely.

Solution

Store the connection string in an encrypted section of the application’s configuration file.

■Note Protected configuration—the .NET Framework feature that lets you encrypt configuration information— relies on the key storage facilities of the Data Protection API (DPAPI) to store the secret key used to encrypt the configuration file. This solves the very difficult problem of code-based secret key management.

C H A P T E R 9 ■ D ATA B A S E A C C E S S

309

How It Works

Database connection strings often contain secret information, or at the very least information that would be valuable to someone trying to attack your system. As such, you should not store connection strings in plaintext, nor is it sufficient to hard-code them into the application code. Strings embedded in an assembly can easily be retrieved using a disassembler. Version 2.0 of the .NET Framework adds a number of classes and capabilities that make storing and retrieving encrypted connection strings in your application’s configuration trivial.

Unencrypted connection strings are stored in the machine or application configuration file in the <connectionStrings> section in the format shown here:

<configuration>

<connectionStrings>

<add name="ConnectionString1" connectionString="Data Source=.\sqlexpress ;Database=Northwind;Integrated Security=SSPI;Min Pool Size=5;Max Pool Size=15;Co nnection Reset=True;Connection Lifetime=600;"

providerName="System.Data.SqlClient" /> </connectionStrings>

</configuration>

The easiest way to read this connection string is to use the indexed ConnectionStrings property of the System.Configuration.ConfigurationManager class. Specifying the name of the connection string you want as the property index will return a System.Configuration.ConnectionStringSettings object. The ConnectionStringSettings.ConnectionString property gets the connection string, and the ConnectionStringSettings.ProviderName property gets the provider name that you can use to create a data provider factory (see recipe 9-10). This process will work regardless of whether the connection string has been encrypted or written in plaintext.

To write a connection string to the application’s configuration file, you must first obtain

a System.Configuration.Configuration object, which represents the application’s configuration file. The easiest way to do this is by calling the System.Configuration.ConfigurationManager. OpenExeConfiguration method. You should then create and configure a new System.Configuration. ConnectionStringSettings object to represent the stored connection string. You should provide a name, connection string, and data provider name for storage. Add the ConnectionStringSettings object to the Configuration’s ConnectionStringsSection collection available through the Configuration. ConnectionStrings property. Finally, save the updated file by calling the Configuration.Save method.

To encrypt the connection strings section of the configuration file, before saving the file, you must configure the ConnectionStringsSection collection. To do this, call the ConnectionStringsSection.

SectionInformation.ProtectSection method and pass it a string containing the name of the protected configuration provider to use: either RsaProtectedConfigurationProvider or

DPAPIProtectedConfigurationProvider. To disable encryption, call the SectionInformation. Unprotect method.

■Note To use the classes from the System.Configuration namespace discussed in this recipe, you must add a reference to the System.Configuration.dll assembly when you build your application.

The Code

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;

310C H A P T E R 9 ■ D ATA B A S E A C C E S S

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";

C H A P T E R 9 ■ D ATA B A S E A C C E S S

311

//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();

}

}

}

9-5. Execute a SQL Command or Stored Procedure

Problem

You need to execute a SQL command or stored procedure on a database.

Solution

Create a command object appropriate to the type of database you intend to use. All command objects implement the System.Data.IDbCommand interface. Configure the command object by setting its CommandType and CommandText properties. Execute the command using the ExecuteNonQuery, ExecuteReader, or ExecuteScalar method, depending on the type of command and its expected results.

How It Works

The IDbCommand interface represents a database command, and each data provider includes a unique implementation. Here is the list of IDbCommand implementations for the five standard data providers:

•System.Data.Odbc.OdbcCommand

•System.Data.OleDb.OleDbCommand

•System.Data.OracleClient.OracleCommand

•System.Data.SqlServerCe.SqlCeCommand

•System.Data.SqlClient.SqlCommand

To execute a command against a database, you must have an open connection (discussed in recipe 9-1) and a properly configured command object appropriate to the type of database you are

312C H A P T E R 9 ■ D ATA B A S E A C C E S S

accessing. You can create command objects directly using a constructor, but a simpler approach is to use the CreateCommand factory method of a connection object. The CreateCommand method returns a command object of the correct type for the data provider and configures it with basic information obtained from the connection you used to create the command. Before executing the command, you must configure the properties described in Table 9-3, which are common to all command implementations.

Table 9-3. Common Command Object Properties

Property	Description
CommandText	A string containing the text of the SQL command to execute or the name of
	a stored procedure. The content of the CommandText property must be
	compatible with the value you specify in the CommandType property.
CommandTimeout	An int that specifies the number of seconds to wait for the command to
	return before timing out and raising an exception. Defaults to 30 seconds.
CommandType	A value of the System.Data.CommandType enumeration that specifies the type of
	command represented by the command object. For most data providers, valid
	values are StoredProcedure, when you want to execute a stored procedure,
	and Text, when you want to execute a SQL text command. If you are using the
	OLE DB data provider, you can specify TableDirect when you want to return
	the entire contents of one or more tables; refer to the .NET Framework SDK
	documentation for more details. Defaults to Text.
Connection	An IDbConnection instance that provides the connection to the database on
	which you will execute the command. If you create the command using the
	IDbConnection.CreateCommand method, this property will be automatically set
	to the IDbConnection instance from which you created the command.
Parameters	A System.Data.IDataParameterCollection instance containing the set of
	parameters to substitute into the command. (See recipe 9-6 for details on how
	to use parameters.)
Transaction	A System.Data.IDbTransaction instance representing the transaction into
	which to enlist the command. (See the .NET Framework SDK documentation
	for details about transactions.)

Once you have configured your command object, you can execute it in a number of ways, depending on the nature of the command, the type of data returned by the command, and the format in which you want to process the data.

•To execute a command that does not return database data (such as INSERT, DELETE, or

CREATE TABLE), call ExecuteNonQuery. For the UPDATE, INSERT, and DELETE commands, the

ExecuteNonQuery method returns an int that specifies the number of rows affected by the command. For other commands, such as CREATE TABLE, ExecuteNonQuery returns the value –1.

•To execute a command that returns a result set, such as a SELECT statement or stored procedure, use the ExecuteReader method. ExecuteReader returns an IDataReader instance (discussed in recipe 9-7) through which you have access to the result data. Most data providers also allow you to execute multiple SQL commands in a single call to the ExecuteReader method, as demonstrated in the example in this recipe, which also shows how to access each result set.

•If you want to execute a query but only need the value from the first column of the first row of result data, use the ExecuteScalar method. The value is returned as an object reference that you must cast to the correct type.

C H A P T E R 9 ■ D ATA B A S E A C C E S S

313

■Note The IDbCommand implementations included in the Oracle and SQL data providers implement additional command execution methods. Recipe 9-8 describes how to use the ExecuteXmlReader method provided by the SqlCommand class. Refer to the .NET Framework’s SDK documentation for details on the additional

ExecuteOracleNonQuery and ExecuteOracleScalar methods provided by the OracleCommand class.

The Code

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}",

314 C H A P T E R 9 ■ D ATA B A S E A C C E S S

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();

}

}

}

C H A P T E R 9 ■ D ATA B A S E A C C E S S

315

9-6. Use Parameters in a SQL Command or Stored Procedure

Problem

You need to set the arguments of a stored procedure or use parameters in a SQL command to improve flexibility.

Solution

Create parameter objects appropriate to the type of command object you intend to execute. All parameter objects implement the System.Data.IDataParameter interface. Configure the parameter objects’ data types, values, and directions and add them to the command object’s parameter collection using the IDbCommand.Parameters.Add method.

How It Works

All command objects support the use of parameters, so you can do the following:

•Set the arguments of stored procedures.

•Receive stored procedure return values.

•Substitute values into text commands at runtime.

The IDataParameter interface represents a parameter, and each data provider includes a unique implementation. Here is the list of IDataParameter implementations for the five standard data providers:

•System.Data.Odbc.OdbcParameter

•System.Data.OleDb.OleDbParameter

•System.Data.OracleClient.OracleParameter

•System.Data.SqlServerCe.SqlCeParameter

•System.Data.SqlClient.SqlParameter

To use parameters with a text command, you must identify where to substitute the parameter’s value within the command. The ODBC, OLE DB, and SQL Server CE data providers support positional parameters; the location of each argument is identified by a question mark (?). For example, the following command identifies two locations to be substituted with parameter values.

UPDATE Employees SET Title = ? WHERE EmployeeId = ?

The SQL Server and Oracle data providers support named parameters, which allow you to identify each parameter location using a name preceded by the at symbol (@). Here is the equivalent command using named parameters:

UPDATE Employees SET Title = @title WHERE EmployeeId = @id

To specify the parameter values to substitute into a command, you must create parameter objects of the correct type and add them to the command object’s parameter collection accessible through the Parameters property. You can add named parameters in any order, but you must add positional parameters in the same order they appear in the text command. When you execute your command,

316C H A P T E R 9 ■ D ATA B A S E A C C E S S

the value of each parameter is substituted into the command string before the command is executed against the data source. You can create parameter objects in the following ways:

•Use the IDbCommand.CreateParameter method.

•Use the IDbCommand.Parameters.Add method.

•Use System.Data.Common.DbProviderFactory.

•Directly create parameter objects using constructors and configure them using constructor arguments or through setting their properties. (This approach ties you to a specific database provider.)

A parameter object’s properties describe everything about a parameter that the command object needs to use the parameter object when executing a command against a data source. Table 9-4 describes the properties that you will use most frequently when configuring parameters.

Table 9-4. Commonly Used Parameter Properties

Property	Description
DbType	A value of the System.Data.DbType enumeration that specifies the type of data
	contained in the parameter. Commonly used values include String, Int32,
	DateTime, and Currency.
Direction	A value from the System.Data.ParameterDirection enumeration that indicates
	the direction in which the parameter is used to pass data. Valid values are
	Input, InputOutput, Output, and ReturnValue.
IsNullable	A bool that indicates whether the parameter accepts null values.
ParameterName	A string containing the name of the parameter.
Value	An object containing the value of the parameter.

When using parameters to execute stored procedures, you must provide parameter objects to satisfy each argument required by the stored procedure, including both input and output arguments. You must set the Direction property of each parameter as described in Table 9-4; parameters are Input by default. If a stored procedure has a return value, the parameter to hold the return value (with a Direction property equal to ReturnValue) must be the first parameter added to the parameter collection.

The Code

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

{

C H A P T E R 9 ■ D ATA B A S E A C C E S S

317

public static void ParameterizedCommandExample(SqlConnection con, string employeeID, string title)

{

// Create and configure a new command containing 2 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);