How to write generic code access in ADO.NET

30 Jan 2006 12:38 PM

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();
        }
    }

}

30 Jan 2006 1:42 PM

W.G. Ryan - MVP

Did you have a question?
<anoni***@hotmail.com> wrote in message
Show quote

news:1138624737.049523.305460@g14g2000cwa.googlegroups.com...
> 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();
>        }
>    }
>
> }
>

30 Jan 2006 2:37 PM

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

31 Jan 2006 1:04 AM

Otis Mukinfus

On 30 Jan 2006 04:38:57 -0800, anoni***@hotmail.com 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?

Show quote

>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