Simple Database Module

 The goal of the Module is to support the simple process for database connection

[C# + ACCESS]
[VB.NET + ACCESS]

1.Create a new project [File >New> Project > Visual c# > name it]

2. Add new class on project[ Right Click > Add > Class > name it dCon] A new class will be created

3.Import or use the ff. Libraries. At the top of your class, add the following lines

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms; 

4. Change the property of the class, make it public and static

    Public- the method or class can be accessed outside the class

    Static - Create one static instance[Only load once]

  public static class dCon1
    {
    }

5. Create datatypes, constructor or methods etc to be used. 

      public static OleDbConnection dbConnect;
      public static OleDbDataAdapter da;
      public static DataTable dt; 

[we created a new oledbconnect, data adapter and datatable. All static and public] 

[datatable is responsible for getting/setting the datas to be read or write by the adapter]

6.Create the methods. First write the method to connect or open or establish a connection to the database[access]

    public static void loadConnection()
      {
          string conStr = "Provider=Microsoft.ACE.oledb.12.0;Data Source=Database/gameDb.accdb";
          dbConnect = new OleDbConnection(conStr);
          dbConnect.Open();
      }

 

 [We created a public static loadConnection method, this must be called first to load the connection]

[Change the Data Source to the location of your database and its name, Provider is the driver that connects your database to your program.]

[Create the connect] - dbConnect= new OleDbConnection(conStr);

[Then open it]

7. Create a method that will accept a sql and execute it

 public static void execute(string sql)
      {
          dt = new DataTable(); //create/recreate the datable as new. So we dont have to create a new datable instance
          if (dbConnect.State == System.Data.ConnectionState.Open)
          {
              da = new OleDbDataAdapter(sql, dbConnect);
              da.Fill(dt);
          }
      }

If the connection is open, recreate the dataadapter and send the sql.

Store the data to the datatable dt.

8. Create a method that will accept a listview and fill it with the datas in the datatable dt

  public static void transferData(ListView _lv)
      {
          _lv.Clear();
          ListView newLv = new ListView();
          newLv = _lv;
          foreach (DataColumn d in dt.Columns)
          {
              ColumnHeader c = new ColumnHeader();
              c.Text = d.ColumnName;
              c.Width = _lv.Width / dt.Columns.Count;
              c.TextAlign = HorizontalAlignment.Center;
              _lv.Columns.Add(c);
          }
          foreach (DataRow row in dt.Rows)
          {
              string[] items = new string[dt.Columns.Count];
              int ctr = 0;
              foreach (var i in row.ItemArray)
              {
                  items[ctr] = i.ToString();
                  ctr++;
              }
              ListViewItem item = new ListViewItem(items);
              _lv.Items.Add(item);
          }

      }  

[Any listview will do since it will automatically fetch the column names then adds the data to the rows of the listview] 

Now we're done..

You can now use it in your form methods by typing dCon.execute("") etc

Dont forget to loadtheconnection first before using the methods execute or transferData.

On your first form_load event type.

dCon.loadConnection();

Examples:

1. Select all the information in the products table and display it on the listview

 >dCon.execute("select * from products")

>transferData(listView1);

2. Check if a user with the username admin and password admin exists.

>dCon.execute("select * from tblUsers where username='admin' and password='admin' ")

>if(dCon.dt.Rows.Count != 0) MessageBox.Show("user exists!")

3.Get the registration date of the user admin from the USERS table and prompt it

>dCon.execute("select * from tblUsers where username='admin'") ;

> DateTime dateOfBirth  = DateTime.Parse(dCon.dt.Rows[0]["dateRegistration"].ToString());

4. Insert a new record to the tblUsers then display it on a listview

>dCon.execute("Insert into tblUsers(username,password,dateRegistration) values('kevin','ilovechiro143ahahaha','DateTime.Now'");

>dCon.transfer(listView1);

5. Update password of user kevin

>dCon.execute("UPDATE tblUsers set password='ilovechiro' where username='kevin' ");

6.Delete user where username is kevin

>dCon.execute("DELETE FROM tblUsers where username='kevin'");

MORE UPDATES TO COME