Home > Articles > Programming > C#

  • Print
  • + Share This
Like this article? We recommend Accessing the Database with C# Code

Accessing the Database with C# Code

Before running the C# code, you first need to disconnect from the pubs database. To do this in Visual C# 2008 Express Edition, right-click the pubs database and select Detach Database from the context menu. If you don't do this, when you run the C# code you may get the error message shown in Figure 10, which indicates why there can be no ancillary connections to the database.

Figure 10 A common problem—too many connections.

Now that we've removed all connections to the pubs database, running the downloaded C# code for this article results in the output shown in Figure 11.

Figure 11 Successful data retrieval.

Listing 1 shows the database access code in its entirety. It looks very complicated, but it really isn't. In a nutshell, some parameters are read in from a file called App.config and then a connection is made to the database. After this, the required data is retrieved from the database and displayed one row at a time.

Listing 1—The database access code.

// Get metadata from App.config file
string dbProvider =
  ConfigurationManager.AppSettings["dbProvider"];
string connectionString =
  ConfigurationManager.ConnectionStrings["SqlConnStringPubs"].ConnectionString;

// Create a factory provider
DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(dbProvider);

// Create a connection object
DbConnection dbConnection = dbProviderFactory.CreateConnection();
Console.WriteLine("Connection object: {0}", dbConnection.GetType().FullName);

dbConnection.ConnectionString = connectionString;
dbConnection.Open();
Console.WriteLine("Connection string: {0}", dbConnection.ToString());

// Create a command object.
DbCommand cmd = dbProviderFactory.CreateCommand();
Console.WriteLine("Command object: {0}", cmd.GetType().FullName);
cmd.Connection = dbConnection;
cmd.CommandText = "Select * From Authors";

// Create a data reader.
DbDataReader dbDataReader =
  cmd.ExecuteReader(CommandBehavior.CloseConnection);
Console.WriteLine("Data reader object: {0}", dbDataReader.GetType().FullName);

Console.WriteLine("Command " + cmd.CommandText);

while (dbDataReader.Read())
    Console.WriteLine("Row data: {0}, {1}", dbDataReader["au_lname"], dbDataReader["au_fname"]);
dbDataReader.Close();

Notice that at the end of Listing 1 the code makes explicit references to database columns ["au_lname"] and ["au_fname"]. To understand why this works, look at the authors table columns listed in the left pane of Figure 12. The C# code is displaying just a small subset of the data from this table.

Figure 12 Columns in the authors table.

Listing 2 shows the contents of the configuration file App.config.

Listing 2—The external metadata file.

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
    <appSettings>
        <add key="dbProvider" value="System.Data.SqlClient" />
    </appSettings>
    <connectionStrings>
        <add name ="SqlConnStringPubs" 
         connectionString ="Server=.\SQLExpress;AttachDbFilename=C:\Program Files\Microsoft SQL 
Server\MSSQL.1\MSSQL\Data\pubs.mdf;Database=dname; Trusted_Connection=Yes;"/>
    </connectionStrings>
</configuration>

The settings in Listing 2 detail the database provider and the connection information required to access the SQLEXPRESS instance. Notice the sections with the word add. These data items are directly referenced in the C# code at runtime, as shown back in Listing 1.

That completes our whistle-stop tour of ADO.NET.

  • + Share This
  • 🔖 Save To Your Account