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:
- Encapsulates all the classes required to return a table, call a stored procedure, and return a query.
- 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).
- Easy connection testing to make sure a valid login and username has been passed in.
- Structure that holds user accounts and roll info.
- Structure for passing in parameters with variable names, format such as:
myStoredProcedure @startDate = '05/01/2005', @endDate = '05/31/2005'
.
- 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");
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):
System.Data.DataSet returnedValue =
new System.Data.DataSet();
returnedValue = connection.ExecuteSelectStatement(
"select * from sysojects");
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:
savePerson @firstName = 'John', @lastName = 'Doe',
@DOB = '01/01/1980', @currentAge = 25, @isAlive = 1
getPerson @ID = 0
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):
System.Data.DataSet myDataSet = new DataSet();
string saveSp = "savePerson";
string getSp = "getPerson";
string firstName = "John";
string lastName = "Doe";
System.DateTime DOB = "01/01/1980";
int currentAge = 25;
bool isAlive = true;
int personID;
try
{
CUtilities.COperationsSQL myconn =
new COperationsSQL("MyServer",
"MyDataBase", "RobertFrost", "1234");
object []param = new object[5];
param[0] = firstName;
param[1] = lastName;
param[2] = DOB;
param[3] = currentAge;
param[4] = isAlive;
personID = (int)myconn.ExcecuteStoredProcedure(saveSp,
param).Tables[0].Rows[0][0];
System.Collections.ArrayList parameters =
new System.Collections.ArrayList();
parameters.Add(personID);
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):
System.Data.DataSet myDataSet = new DataSet();
string saveSp = "savePerson";
string getSp = "getPerson";
string firstName = "John";
string lastName = "Doe";
System.DateTime DOB = "01/01/1980";
int currentAge = 25;
bool isAlive = true;
int personID;
try
{
CUtilities.COperationsSQL myconn =
new COperationsSQL("MyServer",
"MyDataBase", "RobertFrost", "1234");
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");
personID = (int)myconn.ExcecuteStoredProcedure(saveSp,
param).Tables[0].Rows[0][0];
System.Collections.ArrayList parameters =
new System.Collections.ArrayList();
parameters.Add(personID);
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.