This is a lesson in our C# Contacts Management System Development free course. In the course we are developing a full Contacts Management System using C# windows forms and ms access database.
In this particular lesson we are creating what we are calling our Repository class. This class role will be to do CRUD operations. It will be responsible for:
- INSERTING contacts into MS Access.
- SELECTING contacts from MS Access.
- UPDATING contacts in our MS Access database.
- DELETING contacts from MS Access database.
- Realtime SEARCHING/FILTERING existing contacts.
This class is majorly independent of the UI. Thus you can easily re-use it even if you are not taking the course.
Step 1: Create Class, Add using Statements
Start by adding our using statements.
using ContactManager.Data.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;Among the namespaces added as you can see include the OleDb. Classes in that namespace will allow us manipulate MS Access database easily.
Step 2: Define Connection String and other OleDB objects
The connection string will include the path to our database as well as our database provider
private const string conString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Hp/Documents/DataBases/contactsDB.mdb;";
private readonly OleDbConnection con = new OleDbConnection(conString);
private OleDbCommand cmd;
private OleDbDataAdapter adapter;
private readonly DataTable dt = new DataTable();You can see we have instantiated an OleDbConnection, passing in our connection string to give us a representation of the connection to our database.We've also declared OleDbCommand as well as OleDbDataAdapter.
Step 3: How to add to MS Access
The method we will create shortly can allow you to insert or add a record to MS Access databsse:
First create the method:
public ResultModel add(string name, string phone1, String phone2, string email,
String remarks)
{As you can ses it;s receiving a bunch of parameters which are the attributes.
Then add a ResultModel object:
ResultModel resultModel = new ResultModel();ResultModel is our custom class we had created earlier. It will hold our results like list of contacts and corresponding message.
Then we Define the SQL statement for inserting:
const string sql =
"INSERT INTO contactsTB(C_Name,C_Phone1,C_Phone2,C_Email,C_Remarks) VALUES(@NAME,@PHONE1,@PHONE2,@EMAIL,@REMARKS)";Then instatiate the OleDbCommand passing in sql statement as well as OleDbConnection object:
cmd = new OleDbCommand(sql, con);Then add parameters to the Parameters property of our OleDbCommand object:
cmd.Parameters.AddWithValue("@NAME", name);
cmd.Parameters.AddWithValue("@PHONE1", phone1);
cmd.Parameters.AddWithValue("@PHONE2", phone2);
cmd.Parameters.AddWithValue("@EMAIL", email);
cmd.Parameters.AddWithValue("@REMARKS", remarks);We create a try-catch block which will host our query execution:
//OPEN CON AND EXEC INSERT
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
resultModel.Message = "SUCCESS";
}
con.Close();
}
catch (Exception ex)
{
con.Close();
resultModel.Message = ex.Message;
}The Open() method above of the OleDbConnection will establish a connecton to our MS Access database.
ExecuteNonQUery() will actually execute our OleDbCommand, returning an integer. That integer represents number of rows updated.
Then the close() will clone our connection to the database.
4. How to Retrieve/Select From MS Access
Selecting data is pretty easy when it comes to C# and MS Access.
Start by creating a method, then prepare a ResultModel object which will contain our list of contacts as well as a message:
public ResultModel retrieve()
{
ResultModel resultModel = new ResultModel();
Then our SQL statement for selecting our data as well as instantiation of our OleDbCommand:
//SQL STATEMENT
const string sql = "SELECT * FROM contactsTB ";
cmd = new OleDbCommand(sql, con);Then in a try-catch block we will open our connection:
con.Open();Then instantiate our OleDbDataAdapter, passing in our OleDbCommand object:
adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dt);You can see that we have also filled our datatable object using the Fill() method of our OleDbDataAdapter.
Then we convert our datatable to a generic List using LINQ syntax:
List<Contact> contacts = (from DataRow row in dt.Rows
select new Contact
{
Id = (int)row[0],
Name = row[1].ToString(),
Phone1 = row[2].ToString(),
Phone2 = row[3].ToString(),
Email = row[4].ToString(),
Remarks = row[5].ToString()
}).ToList();Then close our connection:
con.Close();Then clear our datatable:
dt.Rows.Clear();Then set the properties of our ResultModel object:
resultModel.Message = "SUCCESS";
resultModel.Contacts = contacts;Step 4: How to Search/Filter MS Access
Let's create a method to search our MS Access database. The method will receive a string which is our search term or query:
public ResultModel filter(string searchTerm)
{At the end of the day we will be returning a ResultModel object. This is just a custom class that will hold both our search results and messages:
ResultModel resultModel = new ResultModel();We then prepare an SQL statement that can search us data:
string sql = "SELECT * FROM contactsTB WHERE C_Name LIKE '%" + searchTerm + "%' OR C_Phone1 LIKE '%"
+ searchTerm + "%' OR C_Phone2 LIKE '%" + searchTerm + "%' OR C_Email LIKE '%" + searchTerm
+ "%' OR C_Remarks LIKE '%" + searchTerm + "%'";You can see we are using LIKE to compare our table columns to our search term.
Then pass the SQL to our OleDbCommand, along with OleDbConnection object:
cmd = new OleDbCommand(sql, con);Then open a try-catch block and open our connection, instantiate our OleDbDataAdapter and populate our datatable:
con.Open();
adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dt);Then convert the datatable to a List using LINQ syntax:
List<Contact> contacts = (from DataRow row in dt.Rows
select new Contact
{
Id = (int)row[0],
Name = row[1].ToString(),
Phone1 = row[2].ToString(),
Phone2 = row[3].ToString(),
Email = row[4].ToString(),
Remarks = row[5].ToString()
}).ToList();Now close our connection and clear our datatable
con.Close();
//CLEAR DATATABLE
dt.Rows.Clear();Now set our results to our ResultModel object:
resultModel.Message = "SUCCESS";
resultModel.Contacts = contacts;Step 5: How to Update our MS Access
As usual we start by creating a method to update our ms access database:
public ResultModel update(int id, string name, string phone1, String phone2,
string email, String remarks)
{The method is receiving the various parameters which need to be set as the new values in our database cells.
Then prepare a ResultModel:
ResultModel resultModel = new ResultModel();Then we define the SQL statement for updating our access database:
string sql = "UPDATE contactsTB SET C_Name='" + name + "',C_Phone1='" + phone1
+ "',C_Phone2='" + phone2 + "',C_Email='" + email + "',C_Remarks='" + remarks
+ "' WHERE ID=" + id + "";And now instantiate an OleDbCommand:
cmd = new OleDbCommand(sql, con);Then in a try-catch block open a connection:
con.Open();Then set our UpdateCommand:
adapter = new OleDbDataAdapter(cmd)
{
UpdateCommand = con.CreateCommand()
};
adapter.UpdateCommand.CommandText = sql;
Then execute the command and close the connection:
if (adapter.UpdateCommand.ExecuteNonQuery() > 0)
{
resultModel.Message = "SUCCESS";
}
con.Close();Step 6: How to Delete Data From SQL
We want to delete a given row. All we need is the row id. Let's create a method that receives the row id and proceeds to delete and return a response:
public ResultModel delete(int id)
{Then prepare a ResultModel:
ResultModel resultModel = new ResultModel();Then we write our SQL command for deleting as well as instantiating our OleDbCommand:
//SQL STATEMENTT
String sql = "DELETE FROM contactsTB WHERE ID=" + id + "";
cmd = new OleDbCommand(sql, con);Then in a try-catch block we open a connection and set our Delete Command:
con.Open();
adapter = new OleDbDataAdapter(cmd) { DeleteCommand = con.CreateCommand() };
adapter.DeleteCommand.CommandText = sql;Then execute the query and return a response:
if (cmd.ExecuteNonQuery() > 0)
{
resultModel.Message = "SUCCESS";
}
con.Close();Ful Code
here is the Full Code for this lesson:
using ContactManager.Data.Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
namespace ContactManager.Data.Repository
{
class ContactsRepository
{
private const string conString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Users/Hp/Documents/DataBases/contactsDB.mdb;";
private readonly OleDbConnection con = new OleDbConnection(conString);
private OleDbCommand cmd;
private OleDbDataAdapter adapter;
private readonly DataTable dt = new DataTable();
public ResultModel add(string name, string phone1, String phone2, string email,
String remarks)
{
ResultModel resultModel = new ResultModel();
//SQL STMT
const string sql =
"INSERT INTO contactsTB(C_Name,C_Phone1,C_Phone2,C_Email,C_Remarks) VALUES(@NAME,@PHONE1,@PHONE2,@EMAIL,@REMARKS)";
cmd = new OleDbCommand(sql, con);
//ADD PARAMS
cmd.Parameters.AddWithValue("@NAME", name);
cmd.Parameters.AddWithValue("@PHONE1", phone1);
cmd.Parameters.AddWithValue("@PHONE2", phone2);
cmd.Parameters.AddWithValue("@EMAIL", email);
cmd.Parameters.AddWithValue("@REMARKS", remarks);
//OPEN CON AND EXEC INSERT
try
{
con.Open();
if (cmd.ExecuteNonQuery() > 0)
{
resultModel.Message = "SUCCESS";
}
con.Close();
}
catch (Exception ex)
{
con.Close();
resultModel.Message = ex.Message;
}
return resultModel;
}
public ResultModel retrieve()
{
ResultModel resultModel = new ResultModel();
//SQL STATEMENT
const string sql = "SELECT * FROM contactsTB ";
cmd = new OleDbCommand(sql, con);
try
{
con.Open();
adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dt);
List<Contact> contacts = (from DataRow row in dt.Rows
select new Contact
{
Id = (int)row[0],
Name = row[1].ToString(),
Phone1 = row[2].ToString(),
Phone2 = row[3].ToString(),
Email = row[4].ToString(),
Remarks = row[5].ToString()
}).ToList();
con.Close();
//CLEAR DATATABLE
dt.Rows.Clear();
resultModel.Message = "SUCCESS";
resultModel.Contacts = contacts;
}
catch (Exception ex)
{
resultModel.Message = ex.Message;
con.Close();
}
return resultModel;
}
public ResultModel filter(string searchTerm)
{
ResultModel resultModel = new ResultModel();
//SQL STATEMENT
string sql = "SELECT * FROM contactsTB WHERE C_Name LIKE '%" + searchTerm + "%' OR C_Phone1 LIKE '%"
+ searchTerm + "%' OR C_Phone2 LIKE '%" + searchTerm + "%' OR C_Email LIKE '%" + searchTerm
+ "%' OR C_Remarks LIKE '%" + searchTerm + "%'";
cmd = new OleDbCommand(sql, con);
try
{
con.Open();
adapter = new OleDbDataAdapter(cmd);
adapter.Fill(dt);
List<Contact> contacts = (from DataRow row in dt.Rows
select new Contact
{
Id = (int)row[0],
Name = row[1].ToString(),
Phone1 = row[2].ToString(),
Phone2 = row[3].ToString(),
Email = row[4].ToString(),
Remarks = row[5].ToString()
}).ToList();
con.Close();
//CLEAR DATATABLE
dt.Rows.Clear();
resultModel.Message = "SUCCESS";
resultModel.Contacts = contacts;
}
catch (Exception ex)
{
resultModel.Message = ex.Message;
con.Close();
}
return resultModel;
}
public ResultModel update(int id, string name, string phone1, String phone2,
string email, String remarks)
{
ResultModel resultModel = new ResultModel();
//SQL STATEMENT
string sql = "UPDATE contactsTB SET C_Name='" + name + "',C_Phone1='" + phone1
+ "',C_Phone2='" + phone2 + "',C_Email='" + email + "',C_Remarks='" + remarks
+ "' WHERE ID=" + id + "";
cmd = new OleDbCommand(sql, con);
//OPEN CONNECTION,UPDATE,RETRIEVE DATAGRIDVIEW
try
{
con.Open();
adapter = new OleDbDataAdapter(cmd)
{
UpdateCommand = con.CreateCommand()
};
adapter.UpdateCommand.CommandText = sql;
if (adapter.UpdateCommand.ExecuteNonQuery() > 0)
{
resultModel.Message = "SUCCESS";
}
con.Close();
}
catch (Exception ex)
{
resultModel.Message = ex.Message;
con.Close();
}
return resultModel;
}
public ResultModel delete(int id)
{
ResultModel resultModel = new ResultModel();
//SQL STATEMENTT
String sql = "DELETE FROM contactsTB WHERE ID=" + id + "";
cmd = new OleDbCommand(sql, con);
//'OPEN CONNECTION,EXECUTE DELETE,CLOSE CONNECTION
try
{
con.Open();
adapter = new OleDbDataAdapter(cmd) { DeleteCommand = con.CreateCommand() };
adapter.DeleteCommand.CommandText = sql;
if (cmd.ExecuteNonQuery() > 0)
{
resultModel.Message = "SUCCESS";
}
con.Close();
}
catch (Exception ex)
{
resultModel.Message = ex.Message;
con.Close();
}
return resultModel;
}
}
}
//end
Now proceed to the next lesson.