Connecting to Local Database in C#

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;
            }
        }
    }
}

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.