HOWTO: Share an SQL Connection between multiple forms within the same project

[Killer_Typo]

Howdy everyone again, Just thought that i would post another snippet for those interested in working with databases and C# in the windows forms environment.

I did some searching on this and found that there was not really any clear concrete answer on how to do this. After some searching i was able to get the general idea and build it into something bigger.

I am using Visual C#.NET Express 2005 from the www.microsoft.com website.

So first thing we need to do is open up our application
[IMG]http://img110.imageshack.us/img110/7967/opencsharpbf2.th.jpg[/IMG]

next we need to create a new project

File>New Project

[IMG]http://img64.imageshack.us/img64/8017/newprojvg7.th.jpg[/IMG]

We are going to build a class library
[IMG]http://img110.imageshack.us/img110/3376/classlibrarybx8.th.jpg[/IMG]

now you should be looking at an empty class. We still need to add some things before we can begin.

First we need to use the system.data.odbc namespace

using System.Data.Odbc;

followed by giving our class a namespace.

I named it System.Data.Odbc.Custom so that when accessing it can be found easily within the Odbc namespace.

namespace System.Data.Odbc.Custom

and finally giving the class a name

public static class ShareSQLSettings

[IMG]http://img64.imageshack.us/img64/3239/usingsystemdataodbcas2.th.jpg[/IMG]

(the class is set to static so that when the variables are assigned they are read only :) ) or so i have been told *cough*

now comes the fun part, actually writing some real code!!

we need to build a list of private variables that will be get/set later.

/*
Global Variables to Handle Database Connection
*/
private static string username; //username on the server
private static string password; //password on the server
private static string host; //connection option to use
private static string driver; //the driver to interface to the database
private static System.Data.Odbc.OdbcConnection odbcCon; //open connection to server
private static System.Data.Odbc.OdbcCommand odbcCom; //run a query on the database
private static System.Data.Odbc.OdbcDataReader odbcRead; //read the query

each of these variables serves a very specific purpose, username/password/host/driver are components required in SQL connection, i am personally using MySQL so this may slightly vary for those of you using other databases (such as oracle or microsoft SQL...etc).

once you have built those variables you now need to build the get or set functions for them which will be public. these are the only variables which should be public, doing this would allow you to get a variable and then test it before assigning it to the private variable, allowing you to keep the front end code much cleaner.

i actually test all queries using a seperate class that looks for SQL injection attacks before assigning the variables. This keeps me from having to repetativly re-use my old code.

the public functions use a simple get/set method.

the get part retreives the value that was set.

the set portion simply sets the private variable to value that was passed.

/*
Get or Set the variables
*/
  ///<summary>
  /// gets or sets the Username
  ///</summary>
  public static string Username { get { return username; } set { username = value; } }
  ///<summary>
  /// gets or sets the Password
  ///</summary>
  public static string Password { get { return password; } set { password = value; } }
  ///<summary>
  /// gets or sets the Database to connect to
  ///</summary>
  public static string Database { get { return database; } set { database = value; } }
  ///<summary>
  /// gets or sets the Host
  ///</summary>
  public static string Host { get { return host; } set { host = value; } }
  ///<summary>
  /// gets or sets the driver
  ///</summary>
  public static string Driver { get { return driver; } set { driver = value; } }

after we have built the gets and sets we can take care of the calling of the SQLConnection.

/*
Open and Close the connection to the database
*/
   ///<summary>
   /// Opens a Connection to the specified Database
   ///</summary>
   public static void odbcConOpen()
   {
        odbcCon = new OdbcConnection();
        string strConString;
        if (host == "")
        {
             host = "localhost";
        }
          strConString = "DRIVER=" + driver + ";SERVER="+host+";USER=" + username + ";PASSWORD=" + password +";";
        odbcCon.ConnectionString = strConString;
        odbcCon.Open();
     }
   ///<summary>
   /// Closes the current connection to the SQL database
   ///</summary>
   public static void odbcConClose()
   {
        odbcCon.Close();
   }

obviously i have not written any try/catch statements to check for improper statements or nullreferenceexceptions but that is something i will leave up to the reader to do as it is not really required on the backend, on the front end of my applications i have try and catch statements that will simply stop the user from proceeding foward if they have not correctly established a connection to the desired SQL Server.

the final step is compilling the DLL and then adding it as a reference to the project.

Press F6 when you have completed typing your code to build the project. navigate to the projects build directory and retrieve the DLL. i usually move the DLL Into the resources directory of my current project but that is not completely required.

to add a new reference to the project start by opening the desired project
[IMG]http://img163.imageshack.us/img163/9079/newprojopenxq5.th.jpg[/IMG]
next go to Project>Add Reference
[IMG]http://img399.imageshack.us/img399/184/newreferenceje5.th.jpg[/IMG]
Finally browse for the reference to add
[IMG]http://img163.imageshack.us/img163/1676/dllreferencelw5.th.jpg[/IMG]

once it has been added you can simply initialize the connection in one form but run queries and other such things from another.

as long as the connection is established through the static class, any form which uses the System.Data.Odbc.Custom namespace will be able to call the connection that has been established and use it for queries and the such.

another example could be to get/set a string to hold a query and then write a public function to return a System.Data.Odbc.OdbcDataReader that would then in turn call the string you passed earlier and return the results of the query in a datareader.

the possibilities are endless!

:)

cheers and i hope this helps someone.