Intended Audience: Absolute beginners
Prerequisite: Microsoft Visual Studio, Microsoft SQL Server (Any version)
If you are a novice to C# programming (like I was), and if you are asked to write a small program as an assignment of your course module by connecting to a Ms-SQL database, this article might help you to solve thousands of your questions.
I'm writing this sample in a very simple manner to make it more understandable, Even when I started programming, the practice that I used was to design the form (UI-User Interface) and then think about the backend development (not even knowing about the 3-tier architecture). So what I did was double click on the button, create a SqlConnection and a SqlCommand, assign appropriate values, and if I want to read data from the database executed to a SqlDataReader or filled to a dataset through SqlDataAdaptor. So for each place I had to duplicate the same coding, which is not a good practice. So My friend told me to write a simple wrapper class and use an object from that class in each place that I called the database operations.
This is how I have done it in the year 2007,
I broke the solution into two projects, UI in a standard windows application (of course you can use WPF application or ASP.Net) and the database wrapper as a separate class library (.dll) project.
If you have a doubt about naming your projects, just try to practice the following naming convention.
Say if your project title is "My First Project" then name your solution as it is. (See the below images) and when naming the projects in the Visual Studio always start with
MFP.SomeName.
Ok let's go for a hands-on self exercise.
- Open Visual Studio 2012, (or any version you have)
- Go to File à New à Project
3. Locate Other Project Types, If you are using VS2010/2008 or 2005 there won't be a note called Templates, other project types appear as the root node. Select Visual Studio Solutions, then select Blank Solution. Put your project name in the Name text box. In the project location, you can change your solution folder,. Finally, click OK.
4. Then locate the solution explorer sliding toolbar, usually in the right corner, if not left.
Right-click on the Solution
à New
à New Project
5. Select Class Library (right side) from Visual C# node (left side). Type the project name as follows,
<shorten the name of the solution>.<component/layer/tier name> and click OK
Ex. MFP.DataLayer
6. Now rename the Class1.cs as DBWrapper.cs by Right click on Class1.cs à Rename à Type "DBWrapper".
7. The best practice is to remove all the non-used namespaces from the code. The advantage you get in here is a quick response from Visual Studio IntelliSense ( listing down the matching key words into a list box while you are typing) and fewer collisions due to the same class name within different namespaces. So first remove all unused namespaces from the code by ;
Click on any using statement à Organize Using à Remove Unused Usings
if you don't have any idea why these using statements are placed at the top of the code, please read
Once you remove unused Usings statement, all the using statements will be removed, now include System namespace, and following two name spaces.
System.Data à Since we are using built in DataSet class objects
System.Data.SqlClient à Since we are using SqlConnection,...etc class objects
using System;
using System.Data;
using System.Data.SqlClient;
namespace MFP.DataLayer
{
public class DBWrapper
{
}
}
8. Let's declare our class variables needed for the database wrapper class.
using System;
using System.Data;
using System.Data.SqlClient;
namespace MFP.DataLayer
{
public class DBWrapper
{
private SqlConnection oSqlCon;
private SqlCommand oSqlCom;
private SqlDataAdapter oSqlDtAdptr;
private SqlDataReader oSqlDtRdr;
private SqlConnectionStringBuilder oConStringBuilder;
}
}
9. Basically, you can connect to a SQL Server using two authentication methods.
- SqlServer Authentication
- Windows Authentication
Read this article to grab more knowledge:
Choosing an Authentication Mode - MSDN
So I'm going to implement multiple class constructors as follows,
Constructor 1 : Accepts SQL server instance name, and the database name
Constructor 2 : Accepts SQL server instance name, database name, database login username, database login password
using System;
using System.Data;
using System.Data.SqlClient;
namespace MFP.DataLayer
{
public class DBWrapper
{
private SqlConnection oSqlCon;
private SqlCommand oSqlCom;
private SqlDataAdapter oSqlDtAdptr;
private SqlDataReader oSqlDtRdr;
private SqlConnectionStringBuilder oConStringBuilder;
public DBWrapper(string sqlInstanceName,string dbName)
{
// Constructor 1
}
public DBWrapper(string sqlInstanceName,string dbName, string dbUserName,string dbPass)
{
// Constructor 2
}
}
}
10. Now fill the constructor 1 as follows.
public DBWrapper(string sqlInstanceName,string dbName)
{
oConStringBuilder = new SqlConnectionStringBuilder();
oConStringBuilder.DataSource = sqlInstanceName;
oConStringBuilder.InitialCatalog = dbName;
// since you are not giving the sql user name password, you must
// enable integrated security
oConStringBuilder.IntegratedSecurity = true;
// create an object from the SqlConnectionClass
oSqlCon = new SqlConnection();
// set the connection string from SqlCommandBuilder object
oSqlCon.ConnectionString = oConStringBuilder.ConnectionString;
}
And constructor 2 as follows.
public DBWrapper(string SqlServerInstanceName, string DataBaseName, string DbUserName,string DbPassword)
{
oConStringBuilder = new SqlConnectionStringBuilder();
oConStringBuilder.DataSource = SqlServerInstanceName;
oConStringBuilder.InitialCatalog = DataBaseName;
oConStringBuilder.UserID = DbUserName;
oConStringBuilder.Password = DbPassword;
//since you are giving the sql user name password, its optional to
// disable integrated security
// oConStringBuilder.IntegratedSecurity = false;
// create an object from the SqlConnectionClass
oSqlCon = new SqlConnection();
// set the connection string from SqlCommandBuilder object
oSqlCon.ConnectionString = ConStringBuilder.ConnectionString;
}
It's not mandatory to use the
ConnectionStringBuilder object, you can even directly assign the connection string to the
oSqlCon.ConnectionString="Data Source = .....". Hardcoded configurations are not technically accepted at the industry. So better to place the connection string in your app.config file and map it here.
11. Let's create one method for Insert, Update and Delete.
public int InsertUpdateDelete(string SqlCommandAsString)
{
try
{
// check whether the connection is not open
if (oSqlCon.State != ConnectionState.Open)
{
oSqlCon.Open();
}
using (oSqlCom = new SqlCommand())
{
// set the connection for the commnad
oSqlCom.Connection = oSqlCon;
// assign the insert query as a text to the sql command
oSqlCom.CommandText = SqlCommandAsString;
// this will return no of rows affected, by executing the query
return oSqlCom.ExecuteNonQuery();
}
}
catch(Exception Ex)
{
throw Ex;
}
finally
{
if (oSqlCon.State == ConnectionState.Open)
{
oSqlCon.Close();
}
}
}
When you execute this code, you can verify whether data has been actually saved to the database i.e. any records in db have changed by examining the returned value from this. If you try to insert one record return value must be 1, if it is an update statement and if it has altered (n) no of records in the database, the return value should be n.
12. Next, write a method to take data out of the database. At this moment, I'll explain it using datasets.
The advantage of using the data set is, it will make it easier for you to bind the data into the controllers i.e data grids, combo boxes etc.
Place the following code segment below to the InsertUpdateDelete method.
public DataSet FillDataSet(string SqlSelectCommandAsString)
{
try
{
// check whether the connection is not open
if (oSqlCon.State != ConnectionState.Open)
{
oSqlCon.Open();
}
DataSet ds = new DataSet();
using (oSqlCom = new SqlCommand())
{
// set the connection for the commnad
oSqlCom.Connection = oSqlCon;
// assign the insert query as a text to the sql command
oSqlCom.CommandText = SqlSelectCommandAsString;
using (oSqlDtAdptr = new SqlDataAdapter())
{
oSqlDtAdptr.SelectCommand = oSqlCom;
oSqlDtAdptr.Fill(ds);
return ds;
}
}
}
catch (Exception Ex)
{
throw Ex;
}
finally
{
if (oSqlCon.State == ConnectionState.Open)
{
oSqlCon.Close();
}
}
}
13. So this very basic DB Wrapper class is now capable of performing all basic database operations, (Create, Read, Update & Delete). We usually use the term CRUD for these standard operations.
Now, your full class should look like this,
1: using System;
2: using System.Data;
3: using System.Data.SqlClient;
4: namespace MFP.DataLayer
5: {
6: public class DBWrapper
7: {
8: private SqlConnection oSqlCon;
9: private SqlCommand oSqlCom;
10: private SqlDataAdapter oSqlDtAdptr;
11: private SqlDataReader oSqlDtRdr;
12: private SqlConnectionStringBuilder oConStringBuilder;
13: public DBWrapper(string SqlServerInstanceName,string DataBaseName)
14: {
15: oConStringBuilder = new SqlConnectionStringBuilder();
16: oConStringBuilder.DataSource = SqlServerInstanceName;
17: oConStringBuilder.InitialCatalog = DataBaseName;
18: // since you are not giving the sql user name password, you must enable integrated security
19: oConStringBuilder.IntegratedSecurity = true;
20: // create an object from the SqlConnectionClass
21: oSqlCon = new SqlConnection();
22: // set the connection string from SqlCommandBuilder object
23: oSqlCon.ConnectionString = oConStringBuilder.ConnectionString;
24: }
25: public DBWrapper(string SqlServerInstanceName, string DataBaseName, string DbUserName,string DbPassword)
26: {
27: oConStringBuilder = new SqlConnectionStringBuilder();
28: oConStringBuilder.DataSource = SqlServerInstanceName;
29: oConStringBuilder.InitialCatalog = DataBaseName;
30: oConStringBuilder.UserID = DbUserName;
31: oConStringBuilder.Password = DbPassword;
32: // since you are giving the sql user name password, its optional to disable integrated security
33: // oConStringBuilder.IntegratedSecurity = false;
34: // create an object from the SqlConnectionClass
35: oSqlCon = new SqlConnection();
36: // set the connection string from SqlCommandBuilder object
37: oSqlCon.ConnectionString = oConStringBuilder.ConnectionString;
38: }
39: public int InsertUpdateDelete(string SqlCommandAsString)
40: {
41: try
42: {
43: // check whether the connection is not open
44: if (oSqlCon.State != ConnectionState.Open)
45: {
46: oSqlCon.Open();
47: }
48: using (oSqlCom = new SqlCommand())
49: {
50: // set the connection for the commnad
51: oSqlCom.Connection = oSqlCon;
52: // assign the insert query as a text to the sql command
53: oSqlCom.CommandText = SqlCommandAsString;
54: return oSqlCom.ExecuteNonQuery();
55: }
56: }
57: catch(Exception Ex)
58: {
59: throw Ex;
60: }
61: finally
62: {
63: if (oSqlCon.State == ConnectionState.Open)
64: {
65: oSqlCon.Close();
66: }
67: }
68: }
69: public DataSet FillDataSet(string SqlSelectCommandAsString)
70: {
71: try
72: {
73: // check whether the connection is not open
74: if (oSqlCon.State != ConnectionState.Open)
75: {
76: oSqlCon.Open();
77: }
78: DataSet ds = new DataSet();
79: using (oSqlCom = new SqlCommand())
80: {
81: // set the connection for the commnad
82: oSqlCom.Connection = oSqlCon;
83: // assign the insert query as a text to the sql command
84: oSqlCom.CommandText = SqlSelectCommandAsString;
85: using (oSqlDtAdptr = new SqlDataAdapter())
86: {
87: oSqlDtAdptr.SelectCommand = oSqlCom;
88: oSqlDtAdptr.Fill(ds);
89: return ds;
90: }
91: }
92: }
93: catch (Exception Ex)
94: {
95: throw Ex;
96: }
97: finally
98: {
99: if (oSqlCon.State == ConnectionState.Open)
100: {
101: oSqlCon.Close();
102: }
103: }
104: }
105: }
106: }
Let's create a sample UI.
1. Right-click on the Solution and get the add new project dialogue as you did above.
2. Design the form as below
3. Add the reference of your database wrapper project to the UI project as follows.
then tick the DataLayer project as follows, and click OK.
4. Now double-click on the View All Button in the form designer.
Replace all the using statements from the following
using System;
using System.Windows.Forms;
using MFP.DataLayer;
Note : We have added the project reference, and now we have listed it at the top, so now we should be able to use the DBWrapper class as it's in the same project.
5. Now create an object from the DBWrapper Class as follows
6. Let's show all the students in the database on the data grid. We call this as data binding, in simple words setting the data source to a collection type i.e dataset or an array of objects.
Double-click on the "Read All" button and type the following code.
Note : This is not a good practice to pass the queries from the UI, but this is just for the demonstration without making things complicated.
