Connecting to Databases like Access, Sql Server, Oracle in C#.Net

In this article I will discuss about different ways to connect to the databases.

.Net provides many ways to connect to the databases as mentioned below.

1. System.Data.Oledb.OledbConnection
2. System.Data.Odbc.OdbcConnection

Example to connect to the access database using OLEDB is given below.

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\emp.mdb");


            con.Open();
            OleDbCommand cmd = new OleDbCommand();
            cmd.CommandText = "delete from emp where name = 'sagar'";
            cmd.Connection = con;
            int a = cmd.ExecuteNonQuery();
            MessageBox.Show(a.ToString());
            //con.Close();
     
            DataSet ds = new DataSet();
            OleDbDataAdapter daEMP = new OleDbDataAdapter();

            OleDbCommand slctEMP = new OleDbCommand("SELECT * FROM emp",con);
            daEMP .SelectCommand = slctEMP ;
            daEMP .Fill(ds, "tblEMP");

             dataGridView1.DataSource = ds.Tables["tblEMP"];

Example to connect to the access database using ODBC with DSN is given below.

            System.Data.Odbc.OdbcConnection con1 = new OdbcConnection();
            con1.ConnectionString = "Dsn=accessdb;";
            con1.Open();

            using (OdbcCommand com = new OdbcCommand(
            "SELECT * FROM employee", con1))
            {
                using (OdbcDataReader reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string datacell= reader.GetString(1);
                        Console.WriteLine(datacell);
                        MessageBox.Show(datacell);
                    }
                }
            }


'Code to show data in the data grid view is given below.

            DataSet ds = new DataSet();


            OdbcDataAdapter daEMP = new OdbcDataAdapter();

            OdbcCommand slctEMP = new OdbcCommand("SELECT * FROM employee", con1);
            daEMP.SelectCommand = slctEMP;
            daEMP.Fill(ds, "tblEMP");

            dataGridView1.DataSource = ds.Tables["tblEMP"];
         
            con1.Close();

Example to connect to the sql server database using ODBC with DSN is given below.

            System.Data.Odbc.OdbcConnection con1 = new OdbcConnection();

            con1.ConnectionString = "Dsn=sqlserver;";

//Please note that only the connection string will be different to connect to different databases like sybase, //mysql, sql server, oracle etc. Rest of the code remains same. That is the beauty of the ODBC connections.


            con1.Open();

            using (OdbcCommand com = new OdbcCommand(
            "SELECT * FROM employee", con1))
            {
                using (OdbcDataReader reader = com.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string datacell= reader.GetString(1);
                        Console.WriteLine(datacell);
                        MessageBox.Show(datacell);
                    }
                }
            }


'Code to show data in the data grid view is given below.

            DataSet ds = new DataSet();


            OdbcDataAdapter daEMP = new OdbcDataAdapter();

            OdbcCommand slctEMP = new OdbcCommand("SELECT * FROM employee", con1);
            daEMP.SelectCommand = slctEMP;
            daEMP.Fill(ds, "tblEMP");

            dataGridView1.DataSource = ds.Tables["tblEMP"];
         
            con1.Close();

What do you think on this topic? Please express your opinion through comment below