Introduction
This article is only for beginners who just try to connect database using
class. Main objective of this article is given Beginner C# developers a project
starting idea. I am facing several problems starting with C# project. So I want
to help others, so that they can get idea and start a project, using Class
concept for database connection and querying. As a example I take a C# project
with Login screen.
Here is a basic procedure
- User runs the software.
- Software waits for username and password.
- User enters username and password.
- Software open database connection and query with the user input.
- If Software found the User, it allows User to enter next win form.
- Software passes username to the next win form.
- User get a message with username in next win form
My aim is not to use entity framework, or LINQ to SQL in this project.
Background
I am a beginner C# developer. I tried Google for getting a
clear idea about a C# project. But not find a simple example. Most of the
examples are partial what I want to achieve. Finally I make a workable version
of my goal. Hope this might help other new C# developer for start their
journey.
Using the code
Create table script:
CREATE TABLE [dbo].[RegLogUser](
[LoginID] [int] IDENTITY(1,1) NOT NULL,
[UserName] [varchar](50) NULL,
[Password] [varchar](50) NULL,
[LogType] [bit] NULL)
Hope you run this script in your MS SQL server database. I
use MS SQL server 2008, but you can use it with other versions. For C# project
I use Visual Studio 2010, but it works with other versions.
In this project I want to make a class for database connection. So what is
Class? There are millions of article you can find on Class. In short we can say
Class is a set of common item, like Car is a Class, Bird is a Class.
If we want to define a Car Class we can write in C#. Format is class
<name>
class car {
}
Connect SQL server we do the same, define a Class name dbConnection
class dbConnection
{
}
Let say it is a taxi cab that mean it's a public transport. In C# how we make something publicly accessible? Just put a keyword before the Class name.
public class car
{
}
Same concept we can use in our dbConnection Class to make it publicly accessible.
public class dbConnection
{
}
If you have passion for cars then you know about Porsche. Let say, we have a car name
Porsche. In Object oriented programming language we say Porsche is an object.
Object is a representation of something which belongs to real-world. In C# we
declare object in this format: <class name> <Object name> = new <class constructor>;
public class car{
}
car Porsche=new car();
Now we have a new item Constructor. First we have to know that if we don't declare any constructor in C# Class, it makes a constructor for that Class.
Also we can define a Class Constructor. Constructor must have the same name which we declare in Class name.
public class car
{
public car()
{
}
}
For dbConnection Class
public class dbConnection
{
public dbConnection()
{
}
}
I have one SUV and one Truck in my garage. I need to write two new class for them.
class SUV{
} <br /><br />class Truck{
}
They both belongs to car class, but have different characteristics. So I derived SUV and Truck class from car class.
class SUV : car
{
}<br /><br />
Here, car is a base class and I create a new derived class SUV and Truck. In my project I use another class just for database operation named
dbProcess. So my project codes become this:
class dbProcess : dbConnection
{
}
Now, I want car class can only accessible from its derived classes SUV and Truck, so I put an
abstract keyword in my car class definition.
public abstract class car
{
}
In short here is the full code
public abstract class car{
public car()
{
}
}
class SUV: car
{
}
In my project I did the same:
public abstract class dbConnection
{
public dbConnection()
{
}
}
class dbProcess : dbConnection
{
}
I start visual studio 2010 and create a new project under "Windows Forms Application" using C#.
By default VS (Visual Studio) create a file name Program.cs. This is the starting file of the project. Every C# program must have a main() function.
"Program.cs" have that main() function.
Program.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace VIMS
{
static class Program
{
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new frmLogin());
}
}
}
For connecting database we need two reference, provided by Microsoft under .NET framework.
using System.Data;
using System.Data.SqlClient;
Now we declare a two member variable in dbConnection class
because if we want to connect a database we need to open a connection.
SqlConnection class helps us to do it. Also for transaction status we use
SqlTransaction class to declare a transaction member variable.
public SqlConnection conn;
public SqlTransaction transaction;
In constructor I make a simple string with all the database
parameter value and create an instance of conn object.
public dbConnection()
{
string strProject = "YourServer";
string strDatabase = "YourDatabase";
string strUserID = "testUser";
string strPassword = "testPassword";
string strconn = "data source=" + strProject +
";Persist Security Info=false;database=" + strDatabase +
";user id=" + strUserID + ";password=" +
strPassword + ";Connection Timeout = 0";
conn = new SqlConnection(strconn);
}
Every connection needs to open using SqlConnection class
open method. So what I do I put it in a separate method so that I can call
it when it required.
public void openConnection()
{
conn.Close();
conn.Open();
transaction = conn.BeginTransaction();
}
It's a good practice I think to close connection when query execution is complete, so that network can be free. As this close need to use many times, again I create a method.
public void closeConnection()
{
transaction.Commit();
conn.Close();
}
Sometimes we need to update more than one table in a single
connection. But if we got any error when multiple update or modify query
executed then there is a chance of lose data integrity. So that we can put a
transaction class who monitor this thing. If there is any error then it can be
roll back to its previous state.
public void errorTransaction()
{
transaction.Rollback();
conn.Close();
}
In my project I need to update/delete tables with date value so i made a method for this particular purpose.
protected void ExecuteSQL(string sSQL)
{
SqlCommand cmdDate = new SqlCommand(" SET DATEFORMAT dmy", conn, transaction);
cmdDate.ExecuteNonQuery();
SqlCommand cmd = new SqlCommand(sSQL, conn, transaction);
cmd.ExecuteNonQuery();
}
My plan is update/delete tables without date value
protected void OnlyExecuteSQL(string sSQL)
{
SqlCommand cmd = new SqlCommand(sSQL, conn);
cmd.ExecuteNonQuery();
}
Need to display data as grid view.
protected DataSet FillData(string sSQL, string sTable)
{
SqlCommand cmd = new SqlCommand(sSQL, conn, transaction);
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
adapter.Fill(ds, sTable);
return ds;
}
Sometimes need to get data row wise not hole set at a time.
protected SqlDataReader setDataReader(string sSQL)
{
SqlCommand cmd = new SqlCommand(sSQL, conn, transaction);
cmd.CommandTimeout = 300;
SqlDataReader rtnReader;
rtnReader = cmd.ExecuteReader();
return rtnReader;
}
For consolidated code check dbConnection.cs file in the project. Now dbUser.cs helps me to declare my dbUser class. In this class I create four
properties to hold my login information data and one method to check user with database.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace VIMS.Class
{
class dbLogUser : dbConnection<br /> {<br /><br /> public int LoginID { get; set; }<br /> public string UserName { get; set; }<br /> public string Password { get; set; }<br /> public bool LogType { get; set; }<br /><br /> public bool ValidRegLogUser()<br /> {<br /> bool _UserValid = false;<br /><br /> using (SqlCommand cmd = new SqlCommand())<br /> {<br /> openConnection();<br /> SqlDataReader conReader;<br /> conReader = null;<br /> cmd.CommandText = "Select * from RegLogUser where username=@userName and UserPassword=@UserPassword";<br /> cmd.Connection = conn;<br /> cmd.Transaction = transaction;<br /> cmd.CommandType = CommandType.Text;<br /> cmd.Parameters.Add("@userName", SqlDbType.VarChar).Value = UserName;<br /> cmd.Parameters.Add("@UserPassword", SqlDbType.VarChar).Value = Password;<br /><br /> try<br /> {<br /> conReader = cmd.ExecuteReader();<br /><br /> while (conReader.Read())<br /> {<br /> LoginID = Convert.ToInt32(conReader["LoginID"]);<br /> LogType = (bool)conReader["LogType"];<br /> _UserValid = true;<br /> }<br /> }<br /> catch (Exception ex)<br /> {<br /><br /> errorTransaction();<br /> throw new ApplicationException("Something wrong happened in the Login module :", ex);<br /> }<br /> finally<br /> {<br /> conReader.Close();<br /> closeConnection();<br /> }<br /> }<br /><br /> return _UserValid;<br /> }<br /><br /> }
}
In frmLogin win form I use two textbox to get username and password and two buttons (For my personal Interest I put a picturebox instead of Button). When user entre username, password and click OK button then I call a fucntion name UserLogin().
dbProcess MainDB = new dbProcess();
dbUser LogUser=new dbUser ();
bool LoginOk = false;
private void UserLogin()
{
if (TxtUserName.Text == "")
{
return;
}
if (TxtPassword.Text == "")
{
return;
}
LogUser.UserName = TxtUserName.Text .Trim();
LogUser.Password = TxtPassword.Text.Trim();
LoginOk=LogUser.ValidRegLogUser();
if (LoginOk)
{
FrmMain MainForm = new FrmMain();
this.Hide();
MainForm.Show();
}
else
{
MessageBox.Show("Please check username and password",
"Error Connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Till now everything is fine, but my objective is after
successful login, user can move to another Windows Form and get a message with
his name.
As I come from VB.net background I become completely frustrated in this point.
Why? In VB.NET I can use Global variable. But In C# there is no concept for global variable. :(
I Google and found lots of information on this topic. But can't decide which
way I can achieve this goal in my project.
(Alternative
of Global Variable).
I dig down more and come up with an idea called static.
static is a keyword used in c# so that it keeps its value during the
application life time.
public static string UserName = "";
Now big question come up where I put this static string variable
in my project so that I can get the value during the application life time. As
I told you before I am novice in C# development so i chose the shortcut way to
do it (May be this is not the right way, but It works in this project, Hope
someone correct me). I put it in the Program.cs file. Now Program class become like this:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Windows.Forms;
namespace VIMS
{
static class Program
{
public static string UserName = "";
public static int LoginID = 0;
public static bool LogType = false;
[STAThread]
static void Main()
{
Application.EnableVisualStyles();
Application.SetCompatibleTextRenderingDefault(false);
Application.Run(new frmLogin());
}
}
}
Also change in my UserLogin Method in frmLogin class.
private void UserLogin()
{
TxtUserName.Text = "test";
TxtPassword.Text = "123456";
if (TxtUserName.Text == "")
{
return;
}
if (TxtPassword.Text == "")
{
return;
}
LogUser.UserName = TxtUserName.Text .Trim();
LogUser.Password = TxtPassword.Text.Trim();
LoginOk=LogUser.ValidRegLogUser();
if (LoginOk)
{
Program.UserName = LogUser.UserName;
Program.LoginID = LogUser.LoginID;
Program.LogType = LogUser.LogType;
FrmMain MainForm = new FrmMain();
this.Hide();
MainForm.Show();
}
else
{
MessageBox.Show("Please check username and password",
"Error Connection", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Finally I put a message box in my 2nd window form named FrmMain at load event (just for testing).
I like to address one thing, in this code i don't use encrypted password, but I strongly suggest you to make your password encrypted.
private void FrmMain_Load(object sender, EventArgs e)
{
MessageBox.Show( Program.UserName,"Successful Login" ,
MessageBoxButtons .OK ,MessageBoxIcon.Information );
}
Points of Interest
There are lots of scopes to achieve this with different
ways. You can work on that or give me suggestion about the miss leading concept
(Which may be my lack of knowledge about C#) I use in this project. So that we
beginner programmer can get clear idea about this kind of project development.
Here are some key points I like to share with you.
- Class
- Abstract Class
- Constructors
- SqlClient for Database Connection Class
- SqlConnection for database connection
- SqlTransaction for check transaction status
- SqlCommand for execute T-SQL statement or Store Procedure
- SqlDataReader
for reading rows from sql server table.
- DataSet for represent data in memory
- SqlDataReader for represent commands and database connection to fill Dataset.
- try-catch for error handle
- Throwing and exception for error handle with message.
- Variable and Method Scope in Microsoft.net
I am very positive about life and a born explorer. I set my personal goal to be a good human being,