Database Access In ASP.Net using C#



This document covers database access via stored procedures for MS SQL Server 2005 using windows authentication.

To do this, I've written a database class, which can be used to connect to and process each result set row one by one...
All you write, is the 'process each row one by one' code...



So in my test database I have a list of games, and I have a stored procedure I can execute called "GetGames".
GetGames returns a list of games as a result set that looks like:
GameName
-----------
Deadspace
Halo 3
Left 4 Dead
(Note: In reality, GetGames returns a list of about 800 games or so...)

To view the results of that stored procedure via asp.net and C#, we create two files: GamesList.aspx.cs and GamesList.aspx



So first we create GamesList.aspx.cs, which is the c# code behind the page:
    // the following would go into: GamesList.aspx.cs

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Configuration;

public class GamesList
  : System.Web.UI.Page
{
    ///////////////////////////////////////////////////////

    /// <summary>
    /// Method used to process a single row/result from the GetGames stored procedure
    /// </sumary>
    private string ProcessFirstGameCallback()
    {
    	return "<table>\n"
    	     + "<tr><th align='left'>Game Name</th></tr>\n";
    }

    /// <summary>
    /// Method used to process a single row/result from the GetGames stored procedure
    /// </sumary>
    private string ProcessLastGameCallback()
    {
    	return "</table>\n"
    	     + "<br/>\n";
    }

    /// <summary>
    /// Method used to process a single row/result from the GetGames stored procedure
    /// </sumary>
    private string ProcessGameCallback(SqlDataReader reader)
    {
		return "<tr><td>" + (string)reader["GameName"] + "</td></tr>\n";
    }

    /// <summary>
    /// Method used to process when no results are returned from the GetGames stored procedure
    /// </sumary>
    private string NoGamesToProcessCallback()
    {
    	return "No games exist.<br/>\n";
    }

    /// <summary>
    /// List all players from the database in a table and return it
    /// </summary>
    public string ListAllGames()
    {
        // Note: This connection string is one bit you'll have to change...
        // But this works for me...
        string connectionString = ConfigurationSettings.AppSettings["SQLConnectionString"];
        Dictionary<string, object> parameters = new Dictionary<string, object>();
    	return Database.ExecuteStoredProcedure(connectionString,
    	                                       "GetGames",
    	                                       parameters,
    	                                       ProcessFirstGameCallback,
    	                                       ProcessGameCallback,
    	                                       ProcessLastGameCallback,
    	                                       NoGamesToProcessCallback);
    }

    ///////////////////////////////////////////////////////
}
ProcessFirstGameCallback and ProcessLastGameCallback only really exist to wrap the results into a table...



Next we create GamesList.aspx, which is the page itself:
// the following would go into: GamesList.aspx

<%@ Page Language="C#" AutoEventWireup="false" Src="GamesList.aspx.cs" Inherits="GamesList" %>

<html>
   <head>
      <title>Games List</title>
   </head>
   <body>

       <% Response.Write(ListAllGames()); %>

   </body>
</html>

</body>
</html>



Then we need to modify our web.config file (placed in the same directory as GamesList.aspx.cs and GamesList.aspx) to include the SQLConnectionString...

i.e. The web.config file becomes:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <system.web>

  </system.web>

  <appSettings>
         <add key="SQLConnectionString" value="Server=ROBB\SQLEXPRESS;DataBase=xboxlivedata;Trusted_Connection=Yes;" />
  </appSettings>

</configuration>



Finally, we just need the Database helper class, available from here

We place that file into the "App_Code" directory of the asp.net web-application and that's it.

And that's it, you'll never have to write a single line of database access code ever again...

The above code was tested using MS SQL Server 2005, Apache 2.2 web server and mod_aspdotnet.