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.
- System.Data.Oledb.OledbConnection
- 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
