Connect MS Access Database to C# Console Application (Select-Insert-Update-Delete)

Here in this article, I will show you how to connect a C# Application with MS Access 2016 database.

Step 1: Open MS Access, click on a Blank Desktop Database. Give the database name “dbSchool.accdb“. and then click Create.

Step 2: Now create a Table in database, You can name a table anything you want, here I named it “Student” . There are three columns in the table ID, FirstName and LastName like the following,

Step 3: Now open Visual Studio, start a new Console Application and give any name you want.

Step 4: Now Drag and Drop database file from the Documents to the Project Directory folder.

Step 5: Write a Namespace for connectivity as in the following code snippet.


using System.Data;
using System.Data.OleDb;

using System.Data;

using System.Data.OleDb;

Step 6: Define global variables.


static OleDbConnection con;
static OleDbCommand cmd;
static OleDbDataReader reader;

static OleDbConnection con;

static OleDbCommand cmd;

static OleDbDataReader reader;

Step 7: Create a method for get the list of students.


public static void GetStudent()
        {
            int counter = 0;
            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT * FROM Student";
            con.Open();
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {//www.csharp-console-example.com
                counter++;
                Console.WriteLine(reader[0] + "-" + reader[1] + " " + reader[2]);
            }
            con.Close();
            Console.WriteLine("====================================");
            Console.WriteLine("Number of Students :" + counter);
            Console.WriteLine("====================================");
        }

public static void GetStudent()

{

int counter = 0;

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "SELECT * FROM Student";

con.Open();

reader = cmd.ExecuteReader();

while (reader.Read())

{//www.csharp-console-example.com

counter++;

Console.WriteLine(reader[0] + "-" + reader[1] + " " + reader[2]);

}

con.Close();

Console.WriteLine("====================================");

Console.WriteLine("Number of Students :" + counter);

Console.WriteLine("====================================");

}

Step 8: Create a method for insert a student.


public static void InsertStudent()
        {
            Console.Write("First Name : ");
            string fname = Console.ReadLine();
            Console.Write("Last Name : ");
            string lname = Console.ReadLine();
            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "INSERT INTO Student (FirstName,LastName) VALUES ('" + fname + "','" + lname +"')";
            //www.csharp-console-example.com
            con.Open();
            int sonuc = cmd.ExecuteNonQuery();
            con.Close();
            if (sonuc > 0)
            {
                Console.WriteLine("Inserted");
            }
            else
            {
                Console.WriteLine("Three are errors. The record was not inserted.");
            }
        }

public static void InsertStudent()

{

Console.Write("First Name : ");

string fname = Console.ReadLine();

Console.Write("Last Name : ");

string lname = Console.ReadLine();

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "INSERT INTO Student (FirstName,LastName) VALUES ('" + fname + "','" + lname +"')";

//www.csharp-console-example.com

con.Open();

int sonuc = cmd.ExecuteNonQuery();

con.Close();

if (sonuc > 0)

{

Console.WriteLine("Inserted");

}

else

{

Console.WriteLine("Three are errors. The record was not inserted.");

}

Step 9: Create a method for updating the data of the student


public static void UpdateStudent()
        {
            Console.Write("ID : ");
            int id = Convert.ToInt32(Console.ReadLine());
            Console.Write("First Name : ");
            string fname = Console.ReadLine();
            Console.Write("Last Name : ");
            string lname = Console.ReadLine();
            //www.csharp-console-example.com
            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "UPDATE Student SET FirstName='" + fname + "',LastName='" + lname + "' WHERE Id=" + id;

            con.Open();
            int sonuc = cmd.ExecuteNonQuery();
            con.Close();
            if (sonuc > 0)
            {
                Console.WriteLine("Updated");
            }
            else
            {
                Console.WriteLine("Three are errors. The record was not updated");
            }
            //www.csharp-console-example.com
        }

public static void UpdateStudent()

{

Console.Write("ID : ");

int id = Convert.ToInt32(Console.ReadLine());

Console.Write("First Name : ");

string fname = Console.ReadLine();

Console.Write("Last Name : ");

string lname = Console.ReadLine();

//www.csharp-console-example.com

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "UPDATE Student SET FirstName='" + fname + "',LastName='" + lname + "' WHERE Id=" + id;

con.Open();

int sonuc = cmd.ExecuteNonQuery();

con.Close();

if (sonuc > 0)

{

Console.WriteLine("Updated");

}

else

{

Console.WriteLine("Three are errors. The record was not updated");

}

//www.csharp-console-example.com

}

Step 10: Create a method for delete a record of the student by id column


public static void DeleteStudent()
        {
            Console.Write("Id : ");
            int id = Convert.ToInt32(Console.ReadLine());

            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "DELETE FROM Student WHERE Id=" + id + "";
            //www.csharp-console-example.com
            con.Open();
            int sonuc = cmd.ExecuteNonQuery();
            con.Close();
            if (sonuc > 0)
            {
                Console.WriteLine("Deleted.");
            }
            else
            {
                Console.WriteLine("Three are errors. The record was not deleted.");
            }
        }

public static void DeleteStudent()

{

Console.Write("Id : ");

int id = Convert.ToInt32(Console.ReadLine());

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "DELETE FROM Student WHERE Id=" + id + "";

//www.csharp-console-example.com

con.Open();

int sonuc = cmd.ExecuteNonQuery();

con.Close();

if (sonuc > 0)

{

Console.WriteLine("Deleted.");

}

else

{

Console.WriteLine("Three are errors. The record was not deleted.");

}

Step 11: Main method


static void Main(string[] args)
 {
 while(true)
 {
 Console.WriteLine("1.List of Students");
 Console.WriteLine("2.Insert");
 Console.WriteLine("3.Update");
 Console.WriteLine("4.Delete");
 Console.WriteLine("====================================");
 Console.Write("Select : ");
 string sec = Console.ReadLine();
 Console.WriteLine("====================================");
 if (sec == "1")
 {//www.csharp-console-example.com
 GetStudent();
 }
 else if (sec == "2")
 {
 InsertStudent();
 Console.WriteLine("====================================");
 GetStudent();
 }
 else if (sec == "3")
 {
 UpdateStudent();
 Console.WriteLine("====================================");
 GetStudent();
 }
 else if (sec == "4")
 {
 DeleteStudent();
 Console.WriteLine("====================================");
 GetStudent();
 }
 Console.Write("Continue (e/h) : ");
 string onay = Console.ReadLine();
 if (onay != "e")
 {
 break;
 }
 }
 }

static void Main(string[] args)

{

while(true)

{

Console.WriteLine("1.List of Students");

Console.WriteLine("2.Insert");

Console.WriteLine("3.Update");

Console.WriteLine("4.Delete");

Console.WriteLine("====================================");

Console.Write("Select : ");

string sec = Console.ReadLine();

Console.WriteLine("====================================");

if (sec == "1")

{//www.csharp-console-example.com

GetStudent();

}

else if (sec == "2")

{

InsertStudent();

Console.WriteLine("====================================");

GetStudent();

}

else if (sec == "3")

{

UpdateStudent();

Console.WriteLine("====================================");

GetStudent();

}

else if (sec == "4")

{

DeleteStudent();

Console.WriteLine("====================================");

GetStudent();

}

Console.Write("Continue (e/h) : ");

string onay = Console.ReadLine();

if (onay != "e")

{

break;

}

Finally, finished the project.

Here are the all codes:


using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;

namespace ConsoleApplication_Database
{
    class Program
    {
        static OleDbConnection con;
        static OleDbCommand cmd;
        static OleDbDataReader reader;
        public static void GetStudent()
        {
            int counter = 0;
            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT * FROM Student";
            con.Open();
            reader = cmd.ExecuteReader();
            while (reader.Read())
            {//www.csharp-console-example.com
                counter++;
                Console.WriteLine(reader[0] + "-" + reader[1] + " " + reader[2]);
            }
            con.Close();
            Console.WriteLine("====================================");
            Console.WriteLine("Number of Students :" + counter);
            Console.WriteLine("====================================");
        }

        public static void InsertStudent()
        {
            Console.Write("First Name : ");
            string fname = Console.ReadLine();
            Console.Write("Last Name : ");
            string lname = Console.ReadLine();
            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "INSERT INTO Student (FirstName,LastName) VALUES ('" + fname + "','" + lname +"')";
            //www.csharp-console-example.com
            con.Open();
            int sonuc = cmd.ExecuteNonQuery();
            con.Close();
            if (sonuc > 0)
            {
                Console.WriteLine("Inserted");
            }
            else
            {
                Console.WriteLine("Three are errors. The record was not inserted.");
            }
        }

        public static void UpdateStudent()
        {
            Console.Write("ID : ");
            int id = Convert.ToInt32(Console.ReadLine());
            Console.Write("First Name : ");
            string fname = Console.ReadLine();
            Console.Write("Last Name : ");
            string lname = Console.ReadLine();
            //www.csharp-console-example.com
            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "UPDATE Student SET FirstName='" + fname + "',LastName='" + lname + "' WHERE Id=" + id;

            con.Open();
            int sonuc = cmd.ExecuteNonQuery();
            con.Close();
            if (sonuc > 0)
            {
                Console.WriteLine("Updated");
            }
            else
            {
                Console.WriteLine("Three are errors. The record was not updated");
            }
            //www.csharp-console-example.com
        }

        public static void DeleteStudent()
        {
            Console.Write("Id : ");
            int id = Convert.ToInt32(Console.ReadLine());

            con = new OleDbConnection();
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";
            cmd = new OleDbCommand();
            cmd.Connection = con;
            cmd.CommandText = "DELETE FROM Student WHERE Id=" + id + "";
            //www.csharp-console-example.com
            con.Open();
            int sonuc = cmd.ExecuteNonQuery();
            con.Close();
            if (sonuc > 0)
            {
                Console.WriteLine("Deleted.");
            }
            else
            {
                Console.WriteLine("Three are errors. The record was not deleted.");
            }
        }


        static void Main(string[] args)
        {
            while(true)
            {
                Console.WriteLine("1.List of Students");
                Console.WriteLine("2.Insert");
                Console.WriteLine("3.Update");
                Console.WriteLine("4.Delete");
                Console.WriteLine("====================================");
                Console.Write("Select : ");
                string sec = Console.ReadLine();
                Console.WriteLine("====================================");
                if (sec == "1")
                {//www.csharp-console-example.com
                    GetStudent();
                }
                else if (sec == "2")
                {
                    InsertStudent();
                    Console.WriteLine("====================================");
                    GetStudent();
                }
                else if (sec == "3")
                {
                    UpdateStudent();
                    Console.WriteLine("====================================");
                    GetStudent();
                }
                else if (sec == "4")
                {
                    DeleteStudent();
                    Console.WriteLine("====================================");
                    GetStudent();
                }
                Console.Write("Continue (e/h) : ");
                string onay = Console.ReadLine();
                if (onay != "e")
                {
                    break;
                }
            }
        }
    }
}

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using System.Data;

using System.Data.OleDb;

namespace ConsoleApplication_Database

{

class Program

{

static OleDbConnection con;

static OleDbCommand cmd;

static OleDbDataReader reader;

public static void GetStudent()

{

int counter = 0;

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "SELECT * FROM Student";

con.Open();

reader = cmd.ExecuteReader();

while (reader.Read())

{//www.csharp-console-example.com

counter++;

Console.WriteLine(reader[0] + "-" + reader[1] + " " + reader[2]);

}

con.Close();

Console.WriteLine("====================================");

Console.WriteLine("Number of Students :" + counter);

Console.WriteLine("====================================");

}

public static void InsertStudent()

{

Console.Write("First Name : ");

string fname = Console.ReadLine();

Console.Write("Last Name : ");

string lname = Console.ReadLine();

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "INSERT INTO Student (FirstName,LastName) VALUES ('" + fname + "','" + lname +"')";

//www.csharp-console-example.com

con.Open();

int sonuc = cmd.ExecuteNonQuery();

con.Close();

if (sonuc > 0)

{

Console.WriteLine("Inserted");

}

else

{

Console.WriteLine("Three are errors. The record was not inserted.");

}

public static void UpdateStudent()

{

Console.Write("ID : ");

int id = Convert.ToInt32(Console.ReadLine());

Console.Write("First Name : ");

string fname = Console.ReadLine();

Console.Write("Last Name : ");

string lname = Console.ReadLine();

//www.csharp-console-example.com

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "UPDATE Student SET FirstName='" + fname + "',LastName='" + lname + "' WHERE Id=" + id;

con.Open();

int sonuc = cmd.ExecuteNonQuery();

con.Close();

if (sonuc > 0)

{

Console.WriteLine("Updated");

}

else

{

Console.WriteLine("Three are errors. The record was not updated");

}

//www.csharp-console-example.com

}

public static void DeleteStudent()

{

Console.Write("Id : ");

int id = Convert.ToInt32(Console.ReadLine());

con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=dbSchool.accdb";

cmd = new OleDbCommand();

cmd.Connection = con;

cmd.CommandText = "DELETE FROM Student WHERE Id=" + id + "";

//www.csharp-console-example.com

con.Open();

int sonuc = cmd.ExecuteNonQuery();

con.Close();

if (sonuc > 0)

{

Console.WriteLine("Deleted.");

}

else

{

Console.WriteLine("Three are errors. The record was not deleted.");

}

static void Main(string[] args)

{

while(true)

{

Console.WriteLine("1.List of Students");

Console.WriteLine("2.Insert");

Console.WriteLine("3.Update");

Console.WriteLine("4.Delete");

Console.WriteLine("====================================");

Console.Write("Select : ");

string sec = Console.ReadLine();

Console.WriteLine("====================================");

if (sec == "1")

{//www.csharp-console-example.com

GetStudent();

}

else if (sec == "2")

{

InsertStudent();

Console.WriteLine("====================================");

GetStudent();

}

else if (sec == "3")

{

UpdateStudent();

Console.WriteLine("====================================");

GetStudent();

}

else if (sec == "4")

{

DeleteStudent();

Console.WriteLine("====================================");

GetStudent();

}

Console.Write("Continue (e/h) : ");

string onay = Console.ReadLine();

if (onay != "e")

{

break;

}

Outputs:

Connect MS Access Database to C# Console Application (Select-Insert-Update-Delete)

Like this:

Leave a Comment X

Like this:

You may also like

Leave a Comment X