C# Project on Database for Beginners

• Download source - 1.3 MB

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

1. User runs the software.
2. Software waits for username and password.
3. User enters username and password.
4. Software open database connection and query with the user input.
5. If Software found the User, it allows User to enter next win form.
6. Software passes username to the next win form.
7. 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  
{
// you can use any name for the class instead of 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{
}

//now declare object of car class
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() // Constructor function
   {
   }
}

For dbConnection Class

public class dbConnection
{
   public dbConnection()  // Constructor function
   {
   }
}

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 <derived_class> : <base_class>
class SUV : car
{
}<br /><br />//class <derived_class> : <base_class> <br />class Truck: 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 <derived_class> : <base_class>
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() // Constructor function
 {
 }
}

class SUV: car
{                 
}

In my project I did the same:

public abstract class dbConnection
{
   public dbConnection()  // Constructor function
   {
   }
}

//class <derived_class> : <base_class>
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
    {
        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [STAThread]

        static void Main() //every c# code has a main function
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new frmLogin()); // frmLogin is my login Win Form Name
        }
    }
}

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()  // constructor Function
{
    string strProject = "YourServer"; //Enter your SQL server instance name
    string strDatabase = "YourDatabase"; //Enter your database name
    string strUserID = "testUser"; // Enter your SQL Server User Name
    string strPassword = "testPassword"; // Enter your SQL Server Password
    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() // Open database Connection
{
    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() // database connection close
{
    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.

/* user class */
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(); //create a object for dbProcess class
dbUser LogUser=new dbUser (); //create a object for dbUser class
bool LoginOk = false; // login Flag

private void UserLogin()
{

    if (TxtUserName.Text == "") // check Username is Empty or Not
    {
        return;
    }

    if (TxtPassword.Text == "") // check Password is Empty or Not
    {
        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;

        /// <summary>
        /// The main entry point for the application.
        /// </summary>
        [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; // Store username into static variable
        Program.LoginID = LogUser.LoginID; // Store username into static variable
        Program.LogType = LogUser.LogType; // Store username into static variable
        // I can get them in FrmMain Window.

        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.

1. Class
2. Abstract Class
3. Constructors
4. SqlClient for Database Connection Class
5. SqlConnection for database connection
6. SqlTransaction for check transaction status
7. SqlCommand for execute T-SQL statement or Store Procedure
8. SqlDataReader for reading rows from sql server table.
9. DataSet for represent data in memory
10. SqlDataReader for represent commands and database connection to fill Dataset.
11. try-catch for error handle
12. Throwing and exception for error handle with message.
13. Variable and Method Scope in Microsoft.net