ERDBManager

ER DBManager

1. Introduction:
Welcome to ER DBManager.NET®. This documentation helps you understand ER DBManager.NET® and how to integrate the library into your applications.

DBManager is a library that is used to perform database(MS SQL, Oracle, Oledb, Odbc) operation (Connecting DB, Executing Command, Reading Data, Reading Output parameter, Executing DataSet etc) more easily in Microsoft™ .NET Platform.

2. Download and Integration:
You can download this ERDBManger form following link.
2.1 Download:
ERDBManager.dll
http://nchc.dl.sourceforge.net/project/erdbmanager/ERDBManager.dll
Source Code of ERDBManager.dll.
http://nchc.dl.sourceforge.net/project/erdbmanager/ERDBManagerSource.zip

2.2 Integration:

You can easily integrate this ERDBManager.dll in your project by doing following steps;
1.     Select your Project
2.     Select Reference from the project explorer
3.     Right click the reference
4.     Add reference
5.     Now select Brows tab
6.     Now specify the ERDBManager.dll form your hard drive
7.     Make it copy local True

3. Getting Started
This is the easiest way to add ERDBManager to your project. Just execute following steps.
3.1 Download ERDBManager.dll (according to 2.1)
3.2 Add reference of ERDBManager to your project (according to 2.2)
3.3 Start Coding:

After adding reference you need to do following.
Add following namespace.

using ERDBManager;

Now you need to do following things;
1.       Create DBManager instance
2.       Open Connection
3.       Perform Your Operation
4.       Close Connection
5.       Dispose DBManger object

Now create DBManager instance

DBManager oDBManager = new DBManager();
//Opening Connection
oDBManager.Open();
/*Perform Your Operation*/
oDBManager.ExecuteNonQuery(CommandType.Text, “your query”);
//Closing Connection
oDBManager.Close();
//Disposing DBManager
oDBManager.Dispose();

More details you will find later.

Preconditions
l .NET Framework 2.0 or later version is installed
The framework is installed during the installation of Visual Studio 2005/2008.
l SQL Server 2005 Express or other database is installed and running.
4. Building Application with ERDBManager

Let we describe all features of ERDBManger before developing our first Application.
4.1 Understanding ERDBManager
Initialize DBManger.
We can initialize DBManager in following ways.

DBManager oDBManager = new DBManager();//a
DBManager oDBManager = new DBManager(DataProvider.SqlServer);//b
DBManager oDBManager = new DBManager(DataProvider.SqlServer, "Connection string");//c

Description:
a. If we use DBManager oDBManager = new DBManager();
Then it will initialize with DataProvider.SqlServer and retrieve connection string from App.config or web.config file.(From “DBConString”  under  connectionStrings)

If you want use this then you need to add following code in App.config or Web.config file

<connectionStrings>
<add name="DBConString" connectionString="UserId=sa; Password=###; Persist Security Info=true;Data Source=urDataSource;Initial Catalog=Your DBName"/>
</connectionStrings>

b. If we use DBManager oDBManager = new DBManager(DataProvider.SqlServer);
Then it will initialize with DataProvider.SqlServer and Retrieve connection string from App.config or web.config file as described in a.
c. If we use DBManager oDBManager = new DBManager(DataProvider.SqlServer,”connection string”); Then it will initialize with given connection string and data provide.
Opening Connection:
Just use following code for opening connection.

oDBManager.Open();

Beginning Transaction:
If you need to maintain transaction then you can begin transaction in following way.
//Begin Transaction If Needed

oDBManager.BeginTransaction();

Adding Parameter:
If you need to add parametr value then you need do following thing

//Create Parameters If Needed
//You must add following code before adding parameter
oDBManager.CreateParameters();
//Adding Input Parameter Value
oDBManager.AddParameters("@studentName", “value”);
//Adding Output Parameter Value
oDBManager.AddOutParameters("@studentId", 10);

Getting Parameter Value:

string strStudentId = oDBManager.GetParameterValue("@studentId");

Execuiting Command:
We can execute command from DataBase Store Procedure  or SQL string Command.

// Execuiting from StoredProcedure
// Parameter Must be added before execuiting command
oDBManager.ExecuteNonQuery(CommandType.StoredProcedure, "[studentInfo_insert]");
// Execuiting from SQL String Project
// Parameter Must be added before execuiting command
oDBManager.ExecuteNonQuery(CommandType.Text, "sql query");

Execuiting Data Set:

DataTable dt = new DataTable();
dt = oDBManager.ExecuteDataSet(CommandType.Text, "sql Query").Tables[0] ;

Execuiting Scaler:

int val =(int) oDBManager.ExecuteScalar(CommandType.Text, "sql query");

Execuiting Rader:

oDBManager .ExecuteReader(CommandType.Text,"sql query");
if(oDBManager.DataReader.Read())
{
txtStudentName.Text = oDBManager.DataReader[1].ToString();
cmbDepartment.Text = oDBManager.DataReader[2].ToString();
}
oDBManager.DataReader.Dispose();

Commiting Transaction

//Commiting Transaction If Transaction is Begin
oDBManager.CommitTransaction();

Closing Connection:

//Closing Connection
oDBManager.Close();

Disposing  Memory

//Disposing DBManager
oDBManager.Dispose();

Hope operation fo DBManager is clear. Now we can develop our application.

4.2 Creating Sample Application:
1.       Create New windows Application
2.       Add reference of ERDBManager (consider 2.2)
3.       Add new App.config file.
4.       Now add following code to app.config file under configuration tag

<connectionStrings>
<add name="DBConString" connectionString="Userid = sa; Password=###; Persist
Security Info=true;Data Source=youdatasource;Initial Catalog=test_db"/>
</connectionStrings>

5.       Now Create a Database with two table named;  it will be good if you download following script and execute it to your database. Consider following Download link
http://space.dl.sourceforge.net/project/erdbmanager/DBScript_ForSampleApplication_For_MS_SQL.sql
a.       StudentInfo (Columns: StudentId,StudentName,Dept)
b.       Payment (Columns: StudentId,Semister,Amount)
6.       You can download sample project from following link
http://space.dl.sourceforge.net/project/erdbmanager/SampleApplicationWithERDBManager.zip

7.       Here I will describe a save operation which is used under save button click event
a.       Save Operation based on attached project

private void Save()
{
DBManager oDBManager = new DBManager();
//Opening Connection
oDBManager.Open();
try
{
//Begin Transaction
oDBManager.BeginTransaction();
//Create Parameters
oDBManager.CreateParameters();
//Adding Parameter Value
oDBManager.AddParameters("@studentName", txtStudentName.Text);
oDBManager.AddParameters("@studentDept", cmbDepartment.Text);
oDBManager.AddOutParameters("@studentId", 10);
oDBManager.ExecuteNonQuery(CommandType.StoredProcedure, "[studentInfo_insert]");
string strStudentId = oDBManager.GetParameterValue("@studentId");
//Adding data to subtable
foreach (DataGridViewRow dr in dataGridView1.Rows)
{
oDBManager.CreateParameters();
oDBManager.AddParameters("@studentId", strStudentId);
oDBManager.AddParameters("@semisterName", dr.Cells[0].Value.ToString());
oDBManager.AddParameters("@Amount", dr.Cells[1].Value.ToString());
string s = "insert into payment values('" + strStudentId + "','" + dr.Cells[0].Value.ToString() + "','" + dr.Cells[1].Value.ToString() + "')";
oDBManager.ExecuteNonQuery(CommandType.Text, s);
}

//Commiting Transaction
oDBManager.CommitTransaction();
//Closing Transaction
oDBManager.Close();
//Disposing DBManager
oDBManager.Dispose();
txtStudentId.Text = strStudentId;
}
catch (Exception ex)
{
oDBManager.RollBackTransaction();
oDBManager.Close();
oDBManager.Dispose();
throw ex;
}

}

b.      Let consider reading method
This method Retirve StudentId,Name,Dept by execuiting Reader and getting payment information by execuiting dataset

private void getStudentInfo()

{
dataGridView1.DataSource = null;
dataGridView1.Columns.Clear();
DBManager oDBManager = new DBManager();
oDBManager.Open();
//Execuiting Reader
oDBManager .ExecuteReader(CommandType.Text,"select * from StudentInfo where studentId = '"+txtStudentId.Text+"'"  );
if(oDBManager.DataReader.Read())
{
txtStudentName.Text = oDBManager.DataReader[1].ToString();
cmbDepartment.Text = oDBManager.DataReader[2].ToString();
}
oDBManager.DataReader.Dispose();
//Execuiting Data Set
DataTable dt = new DataTable();
dt = oDBManager.ExecuteDataSet(CommandType.Text, "Select * from Payment where studentId = '"+txtStudentId .Text+"'").Tables[0] ;
dataGridView1.DataSource = dt;
oDBManager.Close();
oDBManager.Dispose();
}

You can download All files from following link.
http://sourceforge.net/projects/erdbmanager/files/
Hope It wil help you for performing DB operation from .net application.

Lets Have a look of ERDBManager source code
You can download ERDBManager Source code from following link.
http://nchc.dl.sourceforge.net/project/erdbmanager/ERDBManagerSource.zip

If you want you can modify it.
Step 1: Defining enum for different Data Providers to be used.

public enum DataProvider
{
Oracle,SqlServer,OleDb,Odbc
}

Step 2: Defining IDBManager Interface.

using System;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Collections.Generic;
namespace ERDBManager
{
public enum DataProvider
{
Oracle,SqlServer,OleDb,Odbc
}
public interface IDBManager
{
DataProvider ProviderType
{
get;
set;
}
string ConnectionString
{
get;
set;
}
IDbConnection Connection
{
get;
}
IDbTransaction Transaction
{
get;
}
IDataReader DataReader
{
get;
}
IDbCommand Command
{
get;
}
List<IDbDataParameter> ParametersList
{
get;
}

void Open();

void BeginTransaction();

void CommitTransaction();

void RollBackTransaction();

void CreateParameters();

void ClearParameters();

void AddParameters(string paramName, object objValue);

void AddOutParameters(string paramName, int size);

string GetParameterValue(int index);

string GetParameterValue(string parameterName);

IDataReader ExecuteReader(CommandType commandType, string   commandText);

DataSet ExecuteDataSet(CommandType commandType, string    commandText);

object ExecuteScalar(CommandType commandType, string commandText);

int ExecuteNonQuery(CommandType commandType,string commandText);

void CloseReader();

void Close();

void Dispose();

}

}

Step 3: Defining DBManagerFactory Class.

using System;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;

namespace ERDBManager
{
public sealed class DBManagerFactory
{
private DBManagerFactory(){}

public static IDbConnection GetConnection(DataProvider providerType)

{

IDbConnection iDbConnection = null;

switch (providerType)

{

case DataProvider.SqlServer:

iDbConnection = new SqlConnection();

break;

case DataProvider.OleDb:

iDbConnection = new OleDbConnection();

break;

case DataProvider.Odbc:

iDbConnection = new OdbcConnection();

break;

case DataProvider.Oracle:

iDbConnection = new OracleConnection();

break;

default:

return null;

}

return iDbConnection;

}

public static IDbCommand GetCommand(DataProvider providerType)

{

switch (providerType)

{

case DataProvider.SqlServer:

return new SqlCommand();

case DataProvider.OleDb:

return new OleDbCommand();

case DataProvider.Odbc:

return new OdbcCommand();

case DataProvider.Oracle:

return new OracleCommand();

default:

return null;

}

}

public static IDbDataAdapter GetDataAdapter(DataProvider providerType)

{

switch (providerType)

{

case DataProvider.SqlServer:

return new SqlDataAdapter();

case DataProvider.OleDb:

return new OleDbDataAdapter();

case DataProvider.Odbc:

return new OdbcDataAdapter();

case DataProvider.Oracle:

return new OracleDataAdapter();

default:

return null;

}

}

public static IDbDataParameter GetParameterType(DataProvider providerType)

{

IDbDataParameter idbParam;

switch (providerType)

{

case DataProvider.SqlServer:

idbParam = new SqlParameter();

break;

case DataProvider.OleDb:

idbParam = new OleDbParameter();

break;

case DataProvider.Odbc:

idbParam = new OdbcParameter();

break;

case DataProvider.Oracle:

idbParam= new OracleParameter();

break;

default:

idbParam = null;

break;

}

return idbParam;

}

}

}

Step 4: Defining DBManager  Class.

using System;
using System.Data;
using System.Data.Odbc;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.OracleClient;
using System.Configuration;
using System.Collections.Generic;

namespace ERDBManager
{

public sealed class DBManager: IDBManager,IDisposable

{

private IDbConnection idbConnection;

private IDataReader idataReader;

private IDbCommand idbCommand;

private DataProvider providerType;

private IDbTransaction idbTransaction =null;

private List<IDbDataParameter> idbParametersList = new List<IDbDataParameter>();

private string strConnection;

public DBManager()

{

this.providerType = DataProvider.SqlServer;

this.ConnectionString = ConfigurationManager.ConnectionStrings["DBConString"].ToString();

}

public DBManager(DataProvider providerType)

{

this.providerType = providerType;

}

public DBManager(DataProvider providerType,string connectionString)

{

this.providerType = providerType;

this.strConnection = connectionString;

}

public IDbConnection Connection

{

get

{

return idbConnection;

}

}

public IDataReader DataReader

{

get

{

return idataReader;

}

set

{

idataReader = value;

}

}

public DataProvider ProviderType

{

get

{

return providerType;

}

set

{

providerType = value;

}

}

public string ConnectionString

{

get

{

return strConnection;

}

set

{

strConnection = value;

}

}

public IDbCommand Command

{

get

{

return idbCommand;

}

}

public IDbTransaction Transaction

{

get

{

return idbTransaction;

}

}

public List<IDbDataParameter> ParametersList

{

get

{

return idbParametersList;

}

}

public void Open()

{

idbConnection = DBManagerFactory.GetConnection(this.providerType);

idbConnection.ConnectionString =this.ConnectionString;

if (idbConnection.State !=ConnectionState.Open)

idbConnection.Open();

this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);

}

public void Close()

{

if (idbConnection.State !=ConnectionState.Closed)

idbConnection.Close();

}

public void Dispose()

{

GC.SuppressFinalize(this);

this.Close();

this.idbCommand.Dispose();

this.idbCommand = null;

if (this.idbTransaction != null)

{

idbTransaction.Dispose();

}

this.idbTransaction = null;

this.idbConnection = null;

if (this.DataReader != null)

{

this.DataReader.Dispose();

this.DataReader.Close();

}

this.idbParametersList.Clear();

}

public void CreateParameters()

{

idbParametersList = new List<IDbDataParameter>();

}

public void ClearParameters()

{

idbParametersList.Clear();

}

public void AddParameters(string paramName, object objValue)

{

IDbDataParameter oIDbDataParameter = DBManagerFactory.GetParameterType(this.ProviderType);

oIDbDataParameter.ParameterName = paramName;

oIDbDataParameter.Value = objValue;

idbParametersList.Add(oIDbDataParameter);

}

public void AddOutParameters(string paramName,int size)

{

IDbDataParameter oIDbDataParameter = DBManagerFactory.GetParameterType(this.ProviderType);

oIDbDataParameter.ParameterName = paramName;

oIDbDataParameter.Value = "0";

oIDbDataParameter.Direction = ParameterDirection.Output;

oIDbDataParameter.Size = size;

idbParametersList.Add(oIDbDataParameter);

}

public string GetParameterValue(string parameterName)

{

foreach (IDbDataParameter feIDbDataParameter in idbParametersList)

{

if (feIDbDataParameter.ParameterName == parameterName)

{

return feIDbDataParameter.Value.ToString();

}

}

return "";

}

public string GetParameterValue(int index)

{

if (index < idbParametersList.Count)

{

return idbParametersList[index].Value.ToString();

}

return "";

}

public void BeginTransaction()

{

if (this.idbTransaction == null)

{

idbTransaction = idbConnection.BeginTransaction();

}

this.idbCommand.Transaction =idbTransaction;

}

public void CommitTransaction()

{

if (this.idbTransaction != null)

this.idbTransaction.Commit();

idbTransaction = null;

}

public void RollBackTransaction()

{

if (this.idbTransaction != null)

this.idbTransaction.Rollback();

idbTransaction = null;

}

public IDataReader ExecuteReader(CommandType commandType, string commandText)

{

this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);

idbCommand.Connection = this.Connection;

PrepareCommand(idbCommand,this.Connection, this.Transaction,commandType,commandText, this.idbParametersList);

this.DataReader =idbCommand.ExecuteReader();

idbCommand.Parameters.Clear();

return this.DataReader;

}

public void CloseReader()

{

if (this.DataReader != null)

this.DataReader.Close();

}

private void AttachParameters(IDbCommand command, List<IDbDataParameter> commandParametersList)

{

foreach (IDbDataParameter idbParameter in commandParametersList)

{

if ((idbParameter.Direction == ParameterDirection.InputOutput) && (idbParameter.Value == null))

{

idbParameter.Value = DBNull.Value;

}

command.Parameters.Add(idbParameter);

}

}

private void PrepareCommand(IDbCommand command, IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, List<IDbDataParameter> commandParametersList)

{

command.Connection = connection;

command.CommandText = commandText;

command.CommandType = commandType;

if (transaction != null)

{

command.Transaction = transaction;

}

if (commandParametersList != null)

{

AttachParameters(command, commandParametersList);

}

}

public int ExecuteNonQuery(CommandType commandType, string commandText)

{

this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);

PrepareCommand(idbCommand,this.Connection, this.Transaction,commandType, commandText,this.idbParametersList );

int returnValue =idbCommand.ExecuteNonQuery();

idbCommand.Parameters.Clear();

return returnValue;

}

public object ExecuteScalar(CommandType commandType, string commandText)

{

this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);

PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.idbParametersList);

object returnValue = idbCommand.ExecuteScalar();

idbCommand.Parameters.Clear();

return returnValue;

}

public DataSet ExecuteDataSet(CommandType commandType, string commandText)

{

this.idbCommand =DBManagerFactory.GetCommand(this.ProviderType);

PrepareCommand(idbCommand, this.Connection, this.Transaction, commandType, commandText, this.idbParametersList);

IDbDataAdapter dataAdapter =DBManagerFactory.GetDataAdapter(this.ProviderType);

dataAdapter.SelectCommand = idbCommand;

DataSet dataSet = new DataSet();

dataAdapter.Fill(dataSet);

idbCommand.Parameters.Clear();

return dataSet;

}

}

}

For Any kinds of query
hasibul2363@gmail.com
END

  • http://www.shikders.com Roni Shikder

    Thanks HASIBUL. Its a good job. I’ll try with it.