BooProd.Core - Context sensitive Connexion String

• Download source files - 40.1 Kb

Introduction

The purpose of this article is to assist .NET developers to access a SQL Server database without using high level embedded .NET components. You can find here an easy XML configuration file for defining your own context sensitive DB connections, and also easy templates to use for accessing SQL Server stored procedures and retrieving data. This package can save you a huge amount of time for building new DB enabled apps.

What is a context sensitive DB connection ?

When you create a DB application on your local machine, you use a connection string to access your database. When you try to put your application on the Internet (no matter if it's a Web application or not), there's a big problem. All your connection strings are pointing to your local DB server, and not to your production DB server. The solution is to rewrite all your connection strings . I joke, this is not very smart! In fact, you need these connection strings to be chosen dynamically, depending on which server the application is running. This is what I call a context sensitive DB connection.

Background

There's no background needed for using this package. Just open your Visual Studio .NET, and create a new solution. Then add the BooProd.Core project as an existing project. Create your new Windows C# app and put this project as reference. Then, see below for code use. I hope you also have an SQL Server 2000 DB which you can access somewhere :-)

Using the code

The purpose of this article is to help you use SQL Server DB from your C# application. First, we need to configure the SQL Server connection and initialize the package in order to use it. Then, we shall see some explicit access to the DB: insert data, retrieve data, get stored procedure results.

Local vs Production

One of the main functionality this package is based on the detection of the execution context. I consider that, in the life cycle of a project, there are two main stages. The first stage is the creation of the project on your local computer ("My Office" in the diagram). The second stage is to deploy this project from your local computer to your production server ("My Hosted location" in the diagram). Sometimes, there will be a pre-production server, but we'll consider it as a production server. Between the local and the production version of your files, the less modifications you do, the better it is. The purpose of this package is to have zero modification between local and production version..

In order to use context sensitive information, we need to know at the execution time, if we're on a local execution context, or on a production execution context. I've grounded the context detection on the host IP address executing the application. When you work on your local machine, you're generally on a private network and your computer has for example IP address 10.1.1.1. Suppose you work with another colleague of your team, with host IP address configured as 10.1.1.2. You need to be considered both as executing the project in a local context. So, I'll define that the common IP prefix between you and him will be "10.1.1.". All the hosts IP addresses starting with "10.1.1." will be considered as executing in a local context, and all the others as executing in a production context. For example, an host with private IP address "10.2.2.1" or public IP address "192.248.1.10" will be considered as a executing in a production context.

Configuration

The first thing to do is to fill the XML config file with your own DB parameters. A template of this file is in the BooProd.Core project, with name ExeContextTemplate.xml. ExeContext stands for execution context. Copy this file into the root of your project.

The first XML node is ExeConfig. The version attribute is for versioning the XML file for future use and for checking compatibility. The local_ip attribute allows to determine the prefix of all IP addresses considered as local.

The next XML node is ExeDBList, describing the context sensitive DB connections to your databases. Each DB connection is detailed by an ExeDB node. The alias attribute is a keyword (an alias) that will be used in your C# code each time you'll need to access the DB. The ConnectionString attribute is the connection string used for accessing the database, depending of a local or production execution context. You can put as much ExeDB nodes as needed.

...
<ExeConfig>
 <version>1.2</version>
 <local_ip>10.1.</local_ip>
</ExeConfig>
<ExeDBList>
 <ExeDB>
  <alias>BOOPROD</alias>
  <local><ConnectionString>data source=10.1.5.5,1136;
        initial catalog=BOOPROD_PROJECT;user id=boologin;password=boopwd;
        persist security info=True</ConnectionString></local>
  <prod><ConnectionString/></prod>
 </ExeDB>
</ExeDBList>
...

Dynamic vs Static Access

Dynamic Access

The first way to access values is to provide the associated alias string defined in the XML config file, like this:

ExeContext.WebSite("CODEPROJECT").URL

This will return the web site URL associated with de "CODEPROJECT" alias defined in the XML config file. This works pretty well but you can miswrite the alias, and it could be difficult to debug.

Static Access

The other way is more express and secure. Associated with the ExeContextTemplate.xml is provided an ExecTemplate.cs class. This class acts as a direct shortcut to values defined in the XML config file. This is more secure because you can check at compilation time that all your aliases are valid, and therefore the associated information. The drawback of this method is that you must write a small class and all aliases must map the XML config file. But, access to the information will be the quickest:

ExecTemplate.WS_CODEPROJECT.URL

This will return the web site URL associated with "CODEPROJECT" alias defined in the XML config file, using the XS_CODPROJECT property of the ExecTempate class.

Initialization

Once the configuration file has been made, you have to initialize the package before using it. You just need to do once. The best way to do this is on the main constructor form of your application.

public MainForm()
{
 InitializeComponent();
 ExecTemplate.autoInit();
}

Execution

Now, you're ready for accessing your SQL Server DB. The TestDBAccess project included in the ZIP file will show you different ways for accessing data. Configure the BOOPROD alias of the ExeContext.xml with the information needed for accessing a valid local database. Then use the DB.txt file to create the BOOPROD_PROJECT database, tables and SQL stored procedures. You can launch the TestDBAccess demo app and enjoy.

This demo app is not a login window, it's just way to show how to insert a value (the "Login" field) into the DB, find if a value (the "Login" field) into the DB, and display a list of values (the "List field") from the DB. The error code is displayed in the corresponding field. These are the very common functions used in all DB projects.

If you're not interested in using my package for accessing the DB, but just for having a context sensitive DB connection, then the static method ExeContext.DB("BOOPROD") will give you the solution ; just replace "BOOPROD" with your XML DB alias. Don't forget the initialization before.

execQuerySPR

Execute a Stored Procedure and get the Return value. This sample shows you how to call a stored procedure that returns a result set and a return value indicating whether there was an error or not. This is useful for inserting data into the DB and to inform the user of an error.

...
QConnection vQConnection= new QConnection(ExeContext.DB("BOOPROD")); 
vQConnection.addVarChar ("@pBLOGIN_login", pLogin);
vQConnection.addVarChar ("@pBLOGIN_password", pPassword);

SqlCommand vCommand= vQConnection.execQuerySPR("[BLOGIN_newFromLoginPassword]");
SqlDataReader vReader= vCommand.ExecuteReader();

BLogin vBLogin= new BLogin();
if (!vBLogin.read(vReader))
 vBLogin= null;

vReader.Close();
pResult= (int)(vCommand.Parameters["ReturnValue"].Value);
vQConnection.closeConnection();
...

execQuerySP

Execute a Stored Procedure. This sample code shows you how to call a stored procedure that returns a result set. This is useful for getting data from the DB.

...
QConnection vQConnection= new QConnection(ExeContext.DB("BOOPROD"));            
vQConnection.addVarChar("@pBLOGIN_login", pLogin);
vQConnection.addVarChar("@pBLOGIN_password", pPassword);
SqlDataReader vReader= vQConnection.execQuerySP("[BLOGIN_getWithLoginPassword]");
BLogin vBLogin= new BLogin();
if (!vBLogin.read(vReader))
 vBLogin= null;
vReader.Close();
vQConnection.closeConnection();
return vBLogin;
...

UML considerations

Don't worry about UML, it's very cool. You don't need to read thoroughly this section, vital stuff being included in the demo project.

Here is the main ExeContext class with the direct access class ExecTemplate.

With the QConnection class, you just need to use the constructor with the correct ExeDB instance provided by the ExecTemplate class, then use the add methods for adding your parameters to the query, and then the query method itself.

Points of Interest

1. 1^st of all, maybe you don't really understand why you need to encapsulate DB access into a new package? My feeling is that when you work within a team, everyone has to use the same development rules. So, here is a way to do this.
2. This package will execute itself on a local and a production environment with no modification. This is very cool.
3. This package is extensible as I only implement some useful types in the QConnection class. Feel free to add your own DB types.

History

This is my first article, but in my next ones (coming soon I hope), you will find some amazing features of this package, such as context dependent DB connection, and website dynamic URL computation, very useful in life cycle development ... I also will show you my own code writing rules for creating DB mapping objects.

• v1.2 - 2004/12/07

  Article introduction rewritten in order to best precise the purpose. New XML file direct access.

• v1.1 - 2004/11/19

  Due to some limitations of my XML file, I use now a full ConnectionString. Comments have been added in the XML file and version upgraded.

• v1.0 - 2004/11/15

  First version.