- Databases and Object-Oriented Languages
- Installing SQL Server
- Starting SQL Server Management Studio Express
- Running Visual C# 2008 Express Edition
- Accessing the Database with C# Code
- Conclusion
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
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.