Saturday 5 October 2013

Connect Access Database with C#


Connect Access Database with C#

To connect Database with your Windows Application to make Application for querying the database and retrieve the desired result from database . Database in widely used in management software's . Them main purpose of making Management software is to store the database of the work being done in the organisation in to a secure place and easily accessible place .

Database dependent applications are widely used in the market . ERP projects of organisation are highly Database dependent applications . The main two types of database that are widely used in .NET Applications :-

* Microsoft Access Database
* MSSQL Server ( Microsoft Structured Query Language )


The main Languages used in .NET Platform for connecting windows application to database are :-

* C#
* VB#

Microsoft Access Database is most simplest approach to connect your windows application to database . Microsoft is easy to use and very effective database Software that uses simple Query approach.  In old Microsoft Access Database is used with VB 6 to connect VB 6 Applications to Database and Microsoft also extend it for VB.NET to connect .NET Platform applications to Database

DOWNLOAD This Demo To Connect Access Database with C#


Steps to Make Microsoft Access Database:

1. Go to desired location you want to make your Database
2. Right click then click on new and 
3. Then click Microsoft Access 2007 Database
4. Create Desired Table with required Fields 
5. Save it as Microsoft Access database 2003 format 
6. Click OK 

DOWNLOAD THIS Demo Database To Connect Access Database with C#


Steps To Make This Demo Project :-


1. Start your Visual Studio 2010 
2. At start page click on Create New Project 
3. In Dialog Box provide Project name and path where to save this project and click ok
4. Now from Toolbox Drag four Textboxes 
5. Drag 1 Button and make design as show in below image

Design of Form 1 


6. Now copy the code given in code section below :-


Code To Connect Access Database with C#



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;
using System.Data.OleDb;
namespace access_db_csharp
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        OleDbConnection con=new OleDbConnection ();
        OleDbCommand cmd=new OleDbCommand ();
        string connectionstring = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = C:\test.mdb";
                
               private void button1_Click(object sender, EventArgs e)
        {
            cmd.CommandText = "insert into info_table values (" + textBox1 .Text + ",'" + textBox2.Text + "','" + textBox3.Text + "'," + textBox4.Text + ")";
            cmd.CommandType = CommandType.Text;
            cmd.Connection = con;
            cmd.ExecuteNonQuery();
            MessageBox.Show("Done");


                    }

        private void Form1_Load(object sender, EventArgs e)
        {
            con.ConnectionString = connectionstring;
            con.Open();
        }
    }
}

Explanation of Code


Imports System.Data.OleDb


This line of code tells we are importing Oledb Namespace. Oledb stands for Object Linking and Embedding . For using Access database with .net applications we have to inherit classes from this oledb Namespace . Oledb Namespace contains all classes that are required to connect vb.net/c# application to Microsoft Access
Database




Public Class Form1
    Dim connection As New OleDbConnection
    Dim command As New OleDbCommand
    Dim ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & "C:\test.mdb
End Class

Now In These lines of code we declared 3 things
1. OledbConnection :- OledbConnection is a class contained in System.Data.Oledb Namespace and it is used to make connectivity to Access Database . It receives the connection string that tells the path of Microsoft Access Database to connect to it. In this line of code we have created instance of Oledbconnection class

2. OledbCommand :- OledbCommand is a class contained in S
ystem.Data.Oledb Namespace and it is used to define or specify the command that we are going to execute on Microsoft Access Database. In this line of code we are creating instance of OledbCommand to use this instance in further code

3. Connection string :- Now we are creating a variable named connectionString that will receive the string or path that tell how we connect to our Access database. It receives two parameters :-

Provider=Microsoft.Jet.OLEDB.4.0 -- 
Data Source=" & "C:\test.mdb

Now Provider is main part here in connection string . Provider is different for different approaches used for connecting data to database . The Connection string contains information that the Provider needs to know to connect to database Once connected then rest of job is done by provider


    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        connection.ConnectionString = ConnectionString
        connection.Open()
    End Sub

Now in this block of code we have initialized our OledbConnection instance with connection string variable that we have initialized in previous step. So in this code we have given the Oledbconnectin instance means connection some information about Provider to use and location of database file on computer

Then we have used connection.open() method opens port for Enter into Access Database. this opens a pipe to execute query in database 

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        command.CommandText = "insert into info_table values (" & TextBox1.Text & ",'" & TextBox2.Text & "','" & TextBox3.Text & "'," & TextBox4.Text & ")"
        command.CommandType = CommandType.Text
        command.Connection = connection
        command.ExecuteNonQuery()
        MsgBox("Record inserted ")
    End Sub


Now at last we have used insert query that is coded on Button Click Event . In this code I have told which commandtext to choose and what will be commandType test or storeprocedure . and also we have initialized the command with Oledbconnection instance. Then we have execute command using ExecuteNonQuery() method. This method returns the number of rows affected the database 

Share this

5 Responses to "Connect Access Database with C#"

  1. good explanatory tutorial for beginners

    ReplyDelete
  2. I've read some excellent stuff here. Definitely worth
    bookmarking for revisiting. I wonder how a lot attempt you set to create such a great informative website.



    my page :: Clash Of Clans Free Gems No Survey

    ReplyDelete
  3. What's up, I desire to subscribe for this web site to get hottest updates, therefore where can i do it please
    assist.

    Here is my web-site :: cheat codes for clash
    of clans (corvalryan.com)

    ReplyDelete
  4. on demand i am giving the feature for subscription for this blog

    ReplyDelete
  5. Hi, i read your blog from time to time and i own a similar one and i was just wondering if
    you get a lot of spam remarks? If so how do you reduce it, any plugin or anything you
    can advise? I get so much lately it's driving me mad
    so any help is very much appreciated.

    Take a look at my weblog - ps3 game

    ReplyDelete