HI WELCOME TO Sirees
Showing posts with label ado.net. Show all posts
Showing posts with label ado.net. Show all posts

DB2 Connection Strings in Ado.Net

DB2 database is most useful RDBMS developed by IBM. Basically it is not easy to remember different database connection strings in Ado.Net. In Ado.net to make connection to DB2 we have multiple options. We have different connection string to connect to the DB2 database. So I am sharing some connection strings to connect to the DB2 database using different drivers.

Using ODBC

  1. // ODBC without DSN
  2. using System.Data.Odbc;
  3. OdbcConnection conn = new OdbcConnection();
  4. conn.ConnectionString = "Driver={IBM DB2 ODBC DRIVER};DataBase=DataBaseName; HostName=ServerName; Protocol=TCPIP;Port=PortNumber;Uid=UserName;Pwd=Secret";
  5. conn.Open();

Using OLEDB

  1. // OleDb -- Microsoft Driver
  2. using System.Data.OleDb;
  3. OleDbConnection conn = new OleDbConnection();
  4. conn.ConnectionString = "Driver=DB2OLEDB; Network Transport Library=TCPIP; Network Address=xxx.xxx.xxx.xxx; Package Collection=CollectionName; Initial Catalog=DataBaseName; User id=UserName; Password=Secret;";
  5. conn.Open();
  6. // OleDb -- IBM Driver
  7. using System.Data.OleDb;
  8. OleDbConnection conn = new OleDbConnection();
  9. conn.ConnectionString = "Driver=IBMDADB2; DataBase=DataBaseName; HostName=ServerName; Protocol=TCPIP; Port=PortNumber; Uid=UserName; Pwd=Secret;";
  10. conn.Open();

Using .Net DataProvider

  1. // .NET DataProvider from IBM
  2. using IBM.Data.DB2;
  3. Db2Connection conn = new Db2Connection();
  4. conn.ConnectionString = "DataBase=DataBaseName;Uid=UserName;Pwd=Secret";
  5. conn.Open();

MySql Connection Strings in Ado.Net

Today, MySql is open source database in the world. In Ado.net to make connection to MySql , we have different connection strings. Basically it is not easy to remember different database connection strings in Ado.Net. So I am sharing some connection strings to connect to the MySql database using different drivers.

Using ODBC

  1. // ODBC -- MyODBC Driver -- remote database
  2. using System.Data.Odbc;
  3. OdbcConnection conn = new OdbcConnection();
  4. conn.ConnectionString = "Driver={MySql}; Server=db.domain.com; Option=131072; Port=3306; Stmt=; DataBase=DataBaseName; Uid=UserName; Pwd=Secret;" ;
  5. conn.Open();

Using OLEDB

  1. // OleDb
  2. using System.Data.OleDb;
  3. OleDbConnection conn = new OleDbConnection();
  4. conn.ConnectionString = "Provider=MySqlProv; Data Source=ServerName; User id=UserName; Password=Secret";
  5. conn.Open();

Using .Net DataProvider

  1. // .NET DataProvider from CoreLab
  2. using CoreLab.MySql;
  3. MySqlConnection conn = new MySqlConnection();
  4. conn.ConnectionString ="Host=ServerName; DataBase=DataBaseName; Protocol=TCP; Port=3306; Direct=true; Compress=false; Pooling=true; Min Pool Size=0; Max Pool Size=100; Connection Lifetime=0; User id=UserName;Password=Secret";
  5. conn.Open();

Oracle Connection Strings in Ado.Net

Oracle is most used database in the world. In Ado.net to make connection to Oracle we have multiple options. For this purpose we have different connection string to connect to the Oracle. Basically it is not easy to remember different database connection strings in Ado.Net. So I am sharing some connection strings to connect to the Oracle database using different drivers.

Using ODBC

  1. // ODBC -- New Microsoft Driver
  2. using System.Data.Odbc;
  3. OdbcConnection conn = new OdbcConnection();
  4. conn.ConnectionString = "Driver={Microsoft ODBC for Oracle};Server=OracleServer.world;Uid=UserName;Pwd=Secret;";
  5. conn.Open();
  6. // ODBC -- Oracle Driver
  7. using System.Data.Odbc;
  8. OdbcConnection conn = new OdbcConnection();
  9. conn.ConnectionString = "Driver={Oracle ODBC Driver};Dbq=myDataBase;Uid=UserName;Pwd=Secret;";
  10. conn.Open();

Using OLEDB

  1. // OleDb -- Oracle Driver -- Standard Connection
  2. using System.Data.OleDb;
  3. OleDbConnection conn = new OleDbConnection();
  4. conn.ConnectionString = "Driver=OraOLEDB.Oracle;Data Source=ServerName;User id=UserName;Password=Secret;";
  5. conn.Open();
  6. // OleDb -- Oracle Driver -- Trusted Connection
  7. using System.Data.OleDb;
  8. OleDbConnection conn = new OleDbConnection();
  9. conn.ConnectionString = "Driver=OraOLEDB.Oracle;Data Source=ServerName;OSAuthent=1;";
  10. conn.Open();
  11. // or
  12. using System.Data.OleDb;
  13. OleDbConnection conn = new OleDbConnection();
  14. conn.ConnectionString = "Driver=OraOLEDB.Oracle;Data Source=ServerName;User id=admin;Password=pwd";
  15. conn.Open();

Microsoft Access Connection Strings

In Ado.net we have multiple options to connect to Microsoft Access database. For this purpose we have different connection string to connect to the Microsoft Access database. Basically it is not easy to remember different database connection strings in Ado.Net. So I am sharing some connection strings to connect to the Microsoft Access database using different drivers.

Using ODBC

  1. // ODBC -- Standard Security
  2. using System.Data.Odbc;
  3. OdbcConnection conn = new OdbcConnection();
  4. conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; Dbq=c:\myPath\myDb.mdb; Uid=Admin; Pwd=;password";
  5. conn.Open();
  6. // ODBC -- Workgroup (System Database)
  7. using System.Data.Odbc;
  8. OdbcConnection conn = new OdbcConnection();
  9. conn.ConnectionString = "Driver={Microsoft Access Driver (*.mdb)}; Dbq=c:\myPath\myDb.mdb; SystemDb=c:\myPath\myDb.mdw;";
  10. conn.Open();

Using OLEDB

  1. // OleDb with MS Jet -- Standard Security
  2. using System.Data.OleDb;
  3. OleDbConnection conn = new OleDbConnection();
  4. conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\mypath\myDb.mdb; User id=admin;Password=password";
  5. conn.Open();
  6. // OleDb with MS Jet -- Workgroup (System Database)
  7. using System.Data.OleDb;
  8. OleDbConnection conn = new OleDbConnection();
  9. conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\mypath\myDb.mdb; System Database=c:\mypath\myDb.mdw";
  10. conn.Open();

Microsoft SQL Server Connection Strings

Sql Server database is more compatible database with Ado.Net. Since Sql Server and Ado.Net, both are the product of Microsoft. Basically it is not easy to remember different database connection strings in Ado.Net. In Ado.net to make connection to Sql Server we have multiple options. We have different connection string to connect to the Sql Server database. So I am sharing some connection strings to connect to the Sql Server database using different drivers.

Using ODBC

  1. // ODBC -- Standard Connection
  2. using System.Data.Odbc;
  3. OdbcConnection conn = new OdbcConnection();
  4. conn.ConnectionString = "Driver={SQL Server}; Server=ServerName; DataBase=DataBaseName; Uid=UserName; Pwd=Secret";
  5. conn.Open();
  6. // ODBC -- Trusted Connection
  7. using System.Data.Odbc;
  8. OdbcConnection conn = new OdbcConnection();
  9. conn.ConnectionString = "Driver={SQL Server}; Server=ServerName; DataBase=DataBaseName; Uid=admin; Pwd=password";
  10. conn.Open();
  11. // or
  12. OdbcConnection conn = new OdbcConnection();
  13. conn.ConnectionString = "Driver={SQL Server}; Server=ServerName; DataBase=DataBaseName; Trusted_Connection=Yes;";
  14. conn.Open();

Using OLEDB

  1. // OleDb -- Standard Connection
  2. using System.Data.OleDb;
  3. OleDbConnection conn = new OleDbConnection();
  4. conn.ConnectionString = "Driver=SQLOLEDB; Data Source=ServerName; Initial Catalog=DataBaseName; User id=UserName; Password=Secret;";
  5. conn.Open();
  6. // OleDb -- Trusted Connection
  7. using System.Data.OleDb;
  8. OleDbConnection conn = new OleDbConnection();
  9. conn.ConnectionString = "Driver=SQLOLEDB; Data Source=ServerName; Initial Catalog=DataBaseName; Integrated Security=SSPI;";
  10. conn.Open();

Using .Net DataProvider

  1. // .NET DataProvider -- Standard Connection
  2. using System.Data.SqlClient;
  3. SqlConnection conn = new SqlDbConnection();
  4. conn.ConnectionString ="Data Source=ServerName; Initial Catalog=DataBaseName; User id=UserName; Password=Secret;";
  5. conn.Open();
  6. // .NET DataProvider -- Trusted Connection
  7. using System.Data.SqlClient;
  8. SqlConnection conn = new SqlConnection();
  9. conn.ConnectionString = "Data Source=ServerName; Initial Catalog=DataBaseName; Integrated Security=SSPI;";
  10. conn.Open();

Introduction to Ado.net

Before Ado.net we use Ado (Active Database Object) to access data from database. Basically Ado has automatic driver detection technique and it has only one drawback that it only provide a connected environment so efficiency of system may decrease.
ADO.NET is a new database technology used by .Net platform (introduced in 2002). Infact it is a set of classes used to communicate between an application front end and a database.It supports both connected & disconnection mode of data access.

Mandatory Namespaces used in ADO.Net

In any .NET data access page, before you connect to a database, you first have to import all the necessary namespaces that will allow you to work with the objects required. Namespaces used in ADO.Net are :
  1. System.Data

    It contains the common classes for connecting, fetching data from database. Classes are like as DataTable, DataSet, DataView etc.
  2. System.Data.SqlClient

    It contains classes for connecting, fetching data from Sql Server database. Classes are like as SqlDataAdapter,SqlDataReader etc.
  3. System.Data.OracleClient

    It contains classes for connecting, fetching data from Oracle database. Classes are like as OracleDataAdapter,OracleDataReader etc.
  4. System.Data.OleDb

    It contains classes for connecting, fetching data from any database(like msaccess, db2, oracle, sqlserver, mysql). Classes are like as OleDbDataAdapter,OleDbDataReader etc.
  5. System.Data.Odbc

    It contains classes for connecting, fetching data from any database(like msaccess, db2, oracle, sqlserver, mysql). Classes are like as OdbcDataAdapter,OdbcDataReader etc.

Component of ADO.NET architecture

  1. Data Provider

    Data provider is a set of ADO.Net classes that allow us to access a database. Basically, it is a bridge between our application (We can say front-end) and data source. There are following Data Provider :
    1. SqlServer Data Provider:-It is used to access data from SqlServer database (for version 7.0 or later).
    2. Oracle Data Provider:-It is used to access data from oracle database (for version 8i or later).
    3. OleDb Data Provider:-It is used to access data from any database (msaccess, mysql, db2).
    4. Odbc Data Provider :-It is used to access data from any database (msaccess, mysql, db2).
  2. Data Set

    Basically it is a small Data structure that may contain multiple data tables from multiple sources.The information in dataset is created inform of XML and is stored with .xsd extention.It support disconnected mode of data access.It has both scrolling mode means forward and backward scrolling mode (fetching of data).DataSet can have multiple Datatable from multiple sources but DataReader is able to read only single Datatable.