Issues with Creating DAL (Data Access Layer)

Some of the basic issues with creating DAL (Data Access Layer) are shared below.

• When any TableAdapter is added in a strongly typed DataSet (name: Northwind)
  
  1. a namespace (name: NorthwindTableAdapters) is created for the TableAdapters in the DataSet.

• When a TableAdapter (name: Products) is added to a DataSet (name: Northwind)
  
  1. A DataTable type (name: Northwind.ProductsDataTable) is created in the DataSet
  2. The created DataTable contains a collection of DataRows (name: Northwind.ProductsRow)
  3. A TableAdapter is created in the TableAdapters' namespace (name: NorthwindTableAdapters.ProductsTableAdapter)
     
     
• SCOPE_IDENTITY() function in SQLServer returns the last identity value inserted. Details found here.
  
  
• In case of insert query in TableAdapter, the default 'ExecutionMode' value is 'NonQuery' meaning the query will return the number of afftected rows. The ExecutionMode value 'Scalar' means that the query will return the value actually returned by the query (not number of afftected rows).
  

[+/-] Ringkasan saja...

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

[+/-] Ringkasan saja...