Database : Connect from C#

For creating any database related software, we need to know how to do the following 3 basic things-

1. How to connect with database
2. How to execute non-query commands (insert, update etc.)
3. How to get the results of query (select)

Here, I’ll try to show these basic things using C#, .net, MS Access database and Odbc. (Don’t worry about code. A complete code is given at the last.)

Database Specification:

There is an access file name "TestDB.mdb" which contains a table "person(personId, name, address)". Here personId is an AutoNumber, and other two are Text.

Connection with database:

You need to initiate a OdbcConnection object which needs a connection string. Some sample connection strings are as follows:

1. "Driver={SQL Server};Server=(local);Trusted_Connection=Yes;Database=AdventureWorks;"
2. "Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=Yes"
3. "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\bin\Northwind.mdb"
4. "Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls"
5. "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin"
6. "DSN=dsnname"
   

Sample Code:

string connnectionString = "Dsn=MyDsn";

OdbcConnection conn = new OdbcConnection(connectionString);

Execute non-query commands:

Simple steps are as follows:

1. Open the OdbcConnection.
2. Create an OdbcCommand with that connection and the SqlCommand.
3. Then, execute the command.
4. Finally, close the connection.
   

Sample Code:

conn.Open();

string query = "insert into person(name, address) values ('Alor Chhota', 'Dhaka, Bangladesh');";

odbcCommand.Connection = conn;

odbcCommand.CommandText = query;

int noOfAffectedRows = odbcCommand.ExecuteNonQuery();

conn.Close();

Execute Query:

Steps:

1. Open the OdbcConnection.
2. Create an OdbcCommand with that connection and the needed query.
3. Create an OdbcDataAdapter with the OdbcCommand
4. Fill a dataset by the OdbcDataAdapter. This dataset contains the query results.
5. As usual, close the connection.
   

Sample Code:

conn.Open();

string query = "select * from person";

odbcCommand.Connection = conn;

odbcCommand.CommandText = query;

odbcDataAdapter.SelectCommand = odbcCommand;

DataSet dataSet = new DataSet();

odbcDataAdapter.Fill(dataSet);

conn.Close();

Complete Code:

You can use the following DatabaseManager class for your convenience. Here the constructor takes the connection string. And there are two functions for executing query and non-query commands.

Class: DatabaseManager

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Windows.Forms;

namespace DatabaseTest
{
public class DatabaseManager
{
private string m_ConnectionString;
private OdbcDataAdapter m_OdbcDataAdapter;
private OdbcConnection m_OdbcConnection;
private OdbcCommand m_OdbcCommand;

public DatabaseManager(string connectionString)
{
this.m_ConnectionString = connectionString;
this.m_OdbcConnection = new OdbcConnection(this.m_ConnectionString);
m_OdbcDataAdapter = new OdbcDataAdapter();
m_OdbcCommand = new OdbcCommand();
}

public DataTable executeQuery(string query)
{
DataTable dataTable = null;
try

{
this.m_OdbcConnection.Open();
this.m_OdbcCommand.Connection = this.m_OdbcConnection;
this.m_OdbcCommand.CommandText = query;
this.m_OdbcDataAdapter.SelectCommand = this.m_OdbcCommand;
DataSet dataSet = new DataSet();
this.m_OdbcDataAdapter.Fill(dataSet);
dataTable = dataSet.Tables[0];
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
}
finally
{
if (this.m_OdbcConnection.State != ConnectionState.Closed)
this.m_OdbcConnection.Close();
}
return dataTable;
}

public int excecuteNonQuery(string query)
{

int noOfAffectedRows = -1;
try
{
this.m_OdbcConnection.Open();
this.m_OdbcCommand.Connection = this.m_OdbcConnection;
this.m_OdbcCommand.CommandText = query;
noOfAffectedRows = this.m_OdbcCommand.ExecuteNonQuery();
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
}
finally
{
if (this.m_OdbcConnection.State != ConnectionState.Closed)
this.m_OdbcConnection.Close();
}
return noOfAffectedRows;
}
}
}

Testing Code:

string connectionString = @"Driver={Microsoft Access Driver (*.mdb)};DBQ=D:\TestDB.mdb";

DatabaseManager dm = new DatabaseManager(connectionString);

dm.excecuteNonQuery("insert into person(name, address) values ('Ashis Saha', 'Mohammadpur, Dhaka.');");

dm.excecuteNonQuery("insert into person(name, address) values ('Rakib Shahriar', 'Nazrul Islam Hall, BUET.');");

DataTable dt = dm.executeQuery("select * from person");

this.dataGridView1.DataSource = dt; // for showing in windows form