SQL Classes Encapsulation (simplified stored procedure calls, queries and DataSet returns with .NET)

• Download source files - 4.56 Kb

Introduction

Suppose, you have an application with a SQL Server backend and have to deal with all the different SQL connection classes like SqlConnection, SqlDataAdapter, DataSet and SqlCommand. You also need to manage all these classes to work together so that you can return a DataSet or run some query in your code. This is a great tool that helps beginners understand how to connect to a SQL database. It is also a complete class that makes all SQL interactions very easy.

The class encapsulates all these functions. COperationsSQL does the following:

1. Encapsulates all the classes required to return a table, call a stored procedure, and return a query.
2. Formats data into a valid SQL string. For Example: tick marks around dates, varchars are automatically done (depending on the type of the variable that is passed in).
3. Easy connection testing to make sure a valid login and username has been passed in.
4. Structure that holds user accounts and roll info.
5. Structure for passing in parameters with variable names, format such as: myStoredProcedure @startDate = '05/01/2005', @endDate = '05/31/2005'.
6. Returns a Dataset for each stored procedure that is called.

How to use the class

You need to add the .cs file available in the download to your project.

Here are the constructors

public COperationsSQL(string Server, string Database, 
                      string LoginName, string Password)

Specify a Server, Database, LoginName, and Password. The constructor tests the function to make sure the Server, Database, LoginName and Password are valid.

public COperationsSQL(string ConnectionStringCoded)

The connection string can also be passed as a single string. For Example:

"server=MyServer;database=MyDatabase;uid=johndoe;pwd=1234"

Parsing will be done on this to extract the server, database and userid for later reference, if required.

public COperationsSQL(COperationsSQL rhs)

Gets the server, database, userid and password from an already instantiated class.

How to instantiate the class

Here is an example of how we can instantiate the class:

COperationsSQL connection = new COperationsSQL("MyServer", 
              "MyDataBase", "JohnDoe", "1234"); //Simple Enough

Note: an error will be thrown back if it's unable to connect to the database. If it's able to connect, the uID and RoleInfo for the person logging in will be stored in a structure that is available for use. Also note that logging in as SA is not permitted (you can change that if you want).

Once the class is instantiated, it is ready for use.

Methods

Following is a description of some of the methods that are available in the class and their use:

• .ExecuteSelectStatement: This method will return a DataSet for the SQL query that was coded. Here is an example of how it is used:

  (After instantiation of the class):

  C#

  System.Data.DataSet returnedValue = 
                 new System.Data.DataSet();
  returnedValue = connection.ExecuteSelectStatement(
                            "select * from sysojects");
  //This will display the first column 
  //of the first row in a message box.
  MessageBox.Show(
      returnedValue.Tables[0].Rows[0][0].ToString());

• .ExecuteStoredProcedure: This is the best thing about this class. Alright let's say that you have stored procedures to run with the following specifications:
  SQL

  --This stored procedure will save a person into a table
  --RETURNS one column named "ID" (the identity column ID) 
  --to signify @@identity (select @@identity [ID] at the 
  --end of the stored procedure)
  savePerson @firstName = 'John', @lastName = 'Doe', 
       @DOB = '01/01/1980', @currentAge = 25, @isAlive = 1
  
  --This procedure will return the ID, firstname, lastname, 
  --dob, currentage and isAlive when given an ID of the person
  getPerson @ID = 0 --we are assuming that John Doe is the 
                    --first person to get entered.

  This is how it needs to be typed in SQL to run. Now, there are two ways of executing this stored procedure in .NET using this class.

Examples

First way (this way uses regular variable, the variables that get passed into the stored procedure must be in the exact order specified by the SP):

//This is where the data/table will be
//stored for the returned person
System.Data.DataSet myDataSet = new DataSet();
//the name of the stored procedure to run
string saveSp = "savePerson";
//the name of the stored procedure to run
string getSp = "getPerson";
string firstName = "John"; //parameters
string lastName = "Doe"; //parameters
System.DateTime DOB = "01/01/1980";
int currentAge = 25;
bool isAlive = true;
//This is where the person id will be stored
int personID;

try
{
    //Instantiation of the class
    CUtilities.COperationsSQL myconn =
             new COperationsSQL("MyServer",
                "MyDataBase", "RobertFrost", "1234");
    //After the class has been instantiated,
    //we will then save a person into the database
    //NOTE: personID will also be set to the returned
    //value for the stored procedure
    //NOTE: the class willl automatically put ticks
    //        around whatever needs to have ticks
    //        and will change c# boolean to sql boolean.
    //        An error will be thrown if a SQL type
    //        has not been handled for.

    //There are two ways you can add the parameters to
    //the sp. Either as an ArrayList or an Array
    //of objects.
    object []param = new object[5];
    param[0] = firstName;
    param[1] = lastName;
    param[2] = DOB;
    param[3] = currentAge;
    param[4] = isAlive;

    //NOTE: when you use this method, the
    //PARAMETERS MUST BE PASSED IN CORRECT ORDER
    //We will now execute the storedprocedure
    //and set the personID in one step.
    personID = (int)myconn.ExcecuteStoredProcedure(saveSp,
                                param).Tables[0].Rows[0][0];
    //We will now execute the getPerson stored
    //procedure but with the ArrayList approach.
    System.Collections.ArrayList parameters =
                new System.Collections.ArrayList();
    parameters.Add(personID);
    //Lets say that the Stored procedure returns Columns
    //with column names: ID, FirstName, LastName, DOB,
    //CurrentAge,isAlive
    myDataSet =
        myconn.ExcecuteStoredProcedure(getSp, parameters);
    MessageBox.Show("Hello! My name is " +
                myDataSet.Tables[0].Rows[0]["FirstName"] +
                " " +
                myDataSet.Tables[0].Rows[0]["LastName"] +
                ". And my ID is : " +
                myDataSet.Tables[0].Rows[0]["ID"]);
}
catch(Exception error)
{
    MessageBox.Show(error.Message);
}

Second way (this way uses the structure available in the class so that the parameters can be passed in any order):

//This is where the data/table will
//be stored for the returned person
System.Data.DataSet myDataSet = new DataSet();
//the name of the stored procedure to run
string saveSp = "savePerson";
//the name of the stored procedure to run
string getSp = "getPerson";
string firstName = "John"; //parameters
string lastName = "Doe"; //parameters
System.DateTime DOB = "01/01/1980";
int currentAge = 25;
bool isAlive = true;
//This is where the person id will be stored
int personID;

try
{
    //Instantiation of the class
    CUtilities.COperationsSQL myconn =
        new COperationsSQL("MyServer",
           "MyDataBase", "RobertFrost", "1234");
    //After the class has been instantiated, we
    //will then save a person into the database
    //NOTE: personID will also be set to the
    //returned value for the stored procedure
    //NOTE: the class willl automatically put
    //        ticks around whatever needs to have ticks
    //        and will change c# boolean to sql boolean.
    //        An error will be thrown if a SQL type
    //        has not been handled for.

    //passing in the parameters out of order using
    //the sIdentifierItem Structure in the class
    CUtilities.COperationsSQL.sIdentifierItem []param =
           new CUtilities.COperationsSQL.sIdentifierItem[5];
    param[0] =
      new CUtilities.COperationsSQL.sIdentifierItem("@lastName",
                                            lastName, "varchar");
    param[1] =
      new CUtilities.COperationsSQL.sIdentifierItem("@firstName",
                                           firstName, "varchar");
    param[2] =
      new CUtilities.COperationsSQL.sIdentifierItem("@isAlive",
                                                 isAlive, "bit");
    param[3] =
      new CUtilities.COperationsSQL.sIdentifierItem("@dob",
                                                DOB, "datetime");
    param[4] =
      new CUtilities.COperationsSQL.sIdentifierItem("@currentage",
                                               currentAge, "int");

    //We will now execute the storedprocedure
    //and set the personID in one step.
    personID = (int)myconn.ExcecuteStoredProcedure(saveSp,
                                  param).Tables[0].Rows[0][0];

    //We will now execute the getPerson stored
    //procedure but with the ArrayList approach.
    System.Collections.ArrayList parameters =
                           new System.Collections.ArrayList();
    parameters.Add(personID);
    //Lets say that the Stored procedure returns Columns
    //with column names: ID, FirstName, LastName, DOB,
    //CurrentAge,isAlive
    myDataSet = myconn.ExcecuteStoredProcedure(getSp, parameters);
    MessageBox.Show("Hello! My name is " +
                    myDataSet.Tables[0].Rows[0]["FirstName"] +
                    " " +
                    myDataSet.Tables[0].Rows[0]["LastName"] +
                    ". And my ID is : " +
                    myDataSet.Tables[0].Rows[0]["ID"]);
}
catch(Exception error)
{
    MessageBox.Show(error.Message);
}

This is basically what the class is intended for, an easy interface between the application and the SQL Server backend. The best way to use this class is to make it a part of your own objects (for example a person object) and then do the interactions required to get data from the database and save data to the database. The entire class is available at the top as a link.

Add the .cs file to your project and enjoy.