How to write generic code access in ADO.NET

[anonieko]

M. Chand is a .NET consultant, author and the admin and founder of C#
Corner. He has been working with .NET technology since pre beta
releases.

http://www.dotnetwire.com/frame_redirect.asp?newsid=3171



using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.Data.Odbc;

namespace GenericDataAccessApp
{
public class GenericAdoNetComp
{
private IDbConnection idbConn = null;
private IDbDataAdapter idbAdapter = null;
private DbDataAdapter dbAdapter = null;
private IDataReader iReader = null;

public GenericAdoNetComp()
{
}

// GetConnection returns IDbConnection
public IDbConnection GetConnection(int connType,
string connString)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbConn = new OleDbConnection(connString);
break;
case 2: // Sql Data Provider
idbConn = new SqlConnection(connString);
break;
case 3: // ODBC Data Provider
idbConn = new OdbcConnection(connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbConn;
}

// GetDataAdapter returns IDbDataAdapter
public IDbDataAdapter GetDataAdapter(int connType,
string connString, string sql)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbAdapter = new OleDbDataAdapter(sql, connString);
break;
case 2: // Sql Data Provider
idbAdapter = new SqlDataAdapter(sql, connString);
break;
case 3: // ODBC Data Provider
idbAdapter = new OdbcDataAdapter(sql, connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbAdapter;
}
}


}



public class Client
{
private void ConnectBtn_Click(object sender, System.EventArgs e)
{
GenericAdoNetComp genDP = new GenericAdoNetComp();
sql = "SELECT * FROM Employees";

if (radioButton1.Checked)
{
connString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\\Northwind.mdb";
conn = genDP.GetConnection(1, connString);
adapter = genDP.GetDataAdapter(1, connString, sql);
}
else if (radioButton2.Checked)
{
connString =
"Data Source=MCB;Initial Catalog=Northwind;user
id=sa;password=;";
conn = genDP.GetConnection(2, connString);
adapter = genDP.GetDataAdapter(2, connString, sql);
}
else if (radioButton3.Checked)
{
// Construct your connection string here
conn = genDP.GetConnection(3, connString);
adapter = genDP.GetDataAdapter(3, connString, sql);
}

try
{
conn.Open();
// Fill a DataSet
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGrid1.DataSource = ds.Tables[0].DefaultView;
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
finally
{
conn.Close();
}
}

}

 

[W.G. Ryan - MVP]

Did you have a question?

 

[Cor Ligthert [MVP]]

Anonieke,

Have a look at the factory class

http://msdn2.microsoft.com/en-us/library/system.data.common.dbproviderfactory.aspx

a sample in VB net on our website.

http://www.vb-tips.com/default.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

I hope this ehlps,

Cor

 

[Otis Mukinfus]

On 30 Jan 2006 04:38:57 -0800, (e-mail address removed) wrote:
Interesting.
What was your question?

I have one. What do you think will happen in the finally block of the
last method if the connection is not open for some reason?

Did M. Chand write this code?

M. Chand is a .NET consultant, author and the admin and founder of C#
Corner. He has been working with .NET technology since pre beta
releases.

http://www.dotnetwire.com/frame_redirect.asp?newsid=3171



using System;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using Microsoft.Data.Odbc;

namespace GenericDataAccessApp
{
public class GenericAdoNetComp
{
private IDbConnection idbConn = null;
private IDbDataAdapter idbAdapter = null;
private DbDataAdapter dbAdapter = null;
private IDataReader iReader = null;

public GenericAdoNetComp()
{
}

// GetConnection returns IDbConnection
public IDbConnection GetConnection(int connType,
string connString)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbConn = new OleDbConnection(connString);
break;
case 2: // Sql Data Provider
idbConn = new SqlConnection(connString);
break;
case 3: // ODBC Data Provider
idbConn = new OdbcConnection(connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbConn;
}

// GetDataAdapter returns IDbDataAdapter
public IDbDataAdapter GetDataAdapter(int connType,
string connString, string sql)
{
switch (connType)
{
case 1: // OleDb Data Provider
idbAdapter = new OleDbDataAdapter(sql, connString);
break;
case 2: // Sql Data Provider
idbAdapter = new SqlDataAdapter(sql, connString);
break;
case 3: // ODBC Data Provider
idbAdapter = new OdbcDataAdapter(sql, connString);
break;
// case 3: // Add your custom data provider
default:
break;
}
return idbAdapter;
}
}


}



public class Client
{
private void ConnectBtn_Click(object sender, System.EventArgs e)
{
GenericAdoNetComp genDP = new GenericAdoNetComp();
sql = "SELECT * FROM Employees";

if (radioButton1.Checked)
{
connString =
"Provider=Microsoft.Jet.OLEDB.4.0; Data
Source=c:\\Northwind.mdb";
conn = genDP.GetConnection(1, connString);
adapter = genDP.GetDataAdapter(1, connString, sql);
}
else if (radioButton2.Checked)
{
connString =
"Data Source=MCB;Initial Catalog=Northwind;user
id=sa;password=;";
conn = genDP.GetConnection(2, connString);
adapter = genDP.GetDataAdapter(2, connString, sql);
}
else if (radioButton3.Checked)
{
// Construct your connection string here
conn = genDP.GetConnection(3, connString);
adapter = genDP.GetDataAdapter(3, connString, sql);
}

try
{
conn.Open();
// Fill a DataSet
DataSet ds = new DataSet();
adapter.Fill(ds);
dataGrid1.DataSource = ds.Tables[0].DefaultView;
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
finally
{
conn.Close();
}
}

}

Otis Mukinfus
http://www.otismukinfus.com
http://www.tomchilders.com