Connecting to Local Database in C#

PMS Tutorials or Pharmacy Management System Tutorials are collection of articles for implementing a completely working software for pharmacy. The articles are intended for beginners and students who study C#. Also students with graduation project can use the articles for very fast learning and increasing ability to make complete software for the graduation project. As a beginning step, In this article we will see how to connect to a local database in C#.

1.1 Creating new project

To create the new Windows application project, select "File" -> "New" -> "Project..." from the main menu.

From the dialog box that appears, do the following:

1. Click Visual C# since C# will be used as the coding language of our project
2. Select Windows, so our project will be a Desktop windows application
3. Select Windows Forms Application
4. Write a name for the project, for example “MyPharmacy”
5. Finally click on “Ok” to close the dialog box and open the new project
    

1.2 Using SQL Server Database.Sdf Local Database

Here I will explain how to use a .sdf local database file in your Windows application.

1. Go to the Solution Explorer then right-click on your project then select "Add" -> "New Item"
2. Select "Local Database"
3. Click the “Add” button
   
4. Then go to the view menu and select "Server Explorer".
5. Here you can see the local database that is created by you.
   

1.3 How to create table in SDF Database?

Then go to Server Explorer and expand your database (Database1.sdf) then right-click on "Tables" -> then "Create Table".

 

1. Enter the table name “Drugs”.
2. Use “ID” as the column name for the drug number
3. Choose “numeric” value for the ID, it is a number
4. Choose “Yes” for Primary Key to make the ID field the primary key of the table
5. Enter “Name” to represent the second property of the name, or represent the drug name
6. Choose “nvarchar” for the name since it is composed of letters and characters
7. Click “Ok” to close the form

 

1.4 Adding Data to the Drugs table

From the server explorer, right-click on the Drugs table and select "Show Table Data" as shown in the following figure.

Fill in the drugs table with three or four types of drugs as shown in the following figure :

1.5. Creating class to connect to the database

Then from the menu that is shown select "Class" and provide a DBConnection in the Name TextBox. Then write the following code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
namespace MyPharmacy
{
    public class DBConnection
    {
        private SqlCeConnection conn;
        public DBConnection()
        {
            //constructor
        }
        ~DBConnection()
        {
            //destructor
            conn = null;
        }
        public void Disconnect()
        {
            conn.Close();
        }
        public string ConnectToDatabase()
        {
            try
            {
                conn = new SqlCeConnection(@"Data Source=|DataDirectory|\Database1.sdf");
                conn.Open();
                return "Connected";
            }
            catch(SqlCeException e)
            {
                conn = null;
                return e.Message;
            }
        }
    }
}

There are the following four main functions:

• ConnectToDatabase function to connect to the database
• Disconnect function to close the database connection
• The class constructor
• The class destructor

1.6. Using the connection class “DBConnection” in the form

Before starting we need to add a status bar to display the status of the database “connected” or “disconnected”. Ensure that Form1 is opened.

The status bar is added from the Toolbox by selecting status strip as shown in the following figure:

It will be added to the bottom of the form as shown in the following figure, right-click on it and then select "Properties":

From the properties change the Name to dbStatus, from the Items do the following:

1. Click on a small icon “…” beside the collection word
2. From the dialog box select “StatusLabel”
3. Click the "Add" button
4. Click “Ok” to accept the changes and close the form

 

To connect to the database using the main form, double-click on Form1 to open its code editor.

Create an object form the DBConnection class as follows:

public DBConnection DBConn = new DBConnection();

To connect to the database using the form, the database is connected in the Form Load event and disconnected in the Form Close event. So, as long as the form is opened it will be connected to the database, when it is closed it will be disconnected from the database.

Double-click on the form to open the code editor and use the ConnectToDatabase function in the Form1_Load procedure as follows:

private void Form1_Load(object sender, EventArgs e)
{
   dbStatus.Items[0].Text = DBConn.ConnectToDatabase();
}

Where Items[0] refers to the Label used to display the needed string. It is part of the status bar.

To add an even for the Form Close use the following procedure:

1. Click on the “Form1.cs[Design]” to open the form in design mode
2. From the Properties window click the icon “Events”
3. Double-click beside the “FormClosing” event

 

The code editor will be opened and the Form_Closing event will be created, write "DBConn.Disconnect();" inside the Form1_Closing event as in the following:

private void Form1_FormClosing(object sender, FormClosingEventArgs e)
{
    DBConn.Disconnect();
}

The overall code of Form1 should be as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace MyPharmacy
{
    public partial class Form1 : Form
    {
        public DBConnection DBConn = new DBConnection();
        public Form1()
        {
            InitializeComponent();
        }
        private void Form1_Load(object sender, EventArgs e)
        {
          dbStatus.Items[0].Text = DBConn.ConnectToDatabase();
        }
        private void Form1_FormClosing(object sender, FormClosingEventArgs e)
        {
            DBConn.Disconnect();
        }
    }
}

1.6. Displaying data from the Drugs table using the DBConn object

The displaying of the data is done in two steps, the first step is to update the DBConnection class and add a SQL command to get the contents of the Drugs table and the second step is to add a Grid onto Form1 to display the Drugs table data using the previous SQL command.

1.6.1 Add Function to DBConnection class to retrieve Drugs table data

Open the DBConnection class. From the Solution Explorer double-click on DBConnection.cs to open it. Add the following headers:

using System.Data;
using System.Windows.Forms;

Then add the following function

public DataTable getAllDrugs()
{
    try
    {
        string strCommand = "SELECT * FROM Drugs";
        DataTable dt = new DataTable();
        SqlCeDataAdapter da = new SqlCeDataAdapter(strCommand, conn);
        da.Fill(dt);
        return dt;
    }
    catch (SqlCeException e)
    {
        MessageBox.Show(e.Source + "\n" + e.Message + "\n" + e.StackTrace);
        return null;
    }
}

This defines a function for retrieving the contents of the drugs table, we used the SQL command “SELECT * FROM Drugs” then the SqlCeDataAdapter will be used to execute that command and fill a DataTable by output retrieved by the SQL command. The DataTable is the memory version of the database table.

The overall class code will be as follows:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data;
using System.Windows.Forms;
namespace MyPharmacy
{
    public class DBConnection
    {
        private SqlCeConnection conn;
        public DBConnection()
        {
            //constructor
        }
        ~DBConnection()
        {
            //destructor
            conn = null;
        }
        public void Disconnect()
        {
            conn.Close();
        }
        public string ConnectToDatabase()
        {
            try
            {
                conn = new SqlCeConnection(@"Data Source=|DataDirectory|\Database1.sdf");
                conn.Open();
                return "Connected";
            }
            catch(SqlCeException e)
            {
                conn = null;
                return e.Message;
            }
        }
        public DataTable getAllDrugs()
        {
            try
            {
                string strCommand = "SELECT * FROM Drugs";
                DataTable dt = new DataTable();
                SqlCeDataAdapter da = new SqlCeDataAdapter(strCommand, conn);
                da.Fill(dt);
                return dt;
            }
            catch (SqlCeException e)
            {
                MessageBox.Show(e.Source + "\n" + e.Message + "\n" + e.StackTrace);
                return null;
            }
        }
    }
}

1.6.2 Use the preceding function to display the contents of the Drugs table

Open Form1 in Design View, you can double-click on the form name form the Solution Explorer. Then from the toolbox select DataGridView as shown in the figure, drag the DataGridView to the form.

Then for the DataGridView that is placed on the form, do the following:

1. Click on the grid view, you can also change its size as you need.
2. From the properties window click the "Properties" Icon.
3. Write “dgvDrugs” as the name for the grid view.

 

Now to display the contents of the Drugs table in the grid view, double-click on Form1 to open the code editor, then change the code of Form1_Load to be as follows:

private void Form1_Load(object sender, EventArgs e)
{
      dbStatus.Items[0].Text = DBConn.ConnectToDatabase();
      if (dbStatus.Items[0].Text == "Connected")
      {
          dgvDrugs.DataSource = DBConn.getAllDrugs();
      }
}

The code tests to ensure that the database is connected then it uses DBConn’s getAllDrugs function to get a DataTable object, the object will be passed to the dgvDrugs’s DataSource to display the Drugs table contents.

Now run the program either by pressing the “F5” key from the keyboard or clicking on the Run icon from the tool bar as in the following:

The following form will be shown:

We have successfully loaded the contents of the Drugs table.

Exercise

Use the proceeding tutorial to add new table named Suppliers and show its contents in a new GridView.