Csharp/CSharp Tutorial/ADO.Net/OleDbConnection

Connecting to an OLE DB Data Source

using System;
using System.Data.OleDb;
    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);" +
                "Initial Catalog=AdventureWorks;Integrated Security=SSPI";
            string sqlSelect = "SELECT TOP 5 Title, FirstName, LastName FROM Person.Contact";
            using (OleDbConnection connection =new OleDbConnection(oledbConnectString))
            {
                OleDbCommand command = new OleDbCommand(sqlSelect, connection);
                connection.Open(  );
                OleDbDataReader reader = command.ExecuteReader(  );
                while (reader.Read(  ))
                    Console.WriteLine("{0} {1}", reader[0], reader[1]);
            }
        }
    }

Connection for OleDb

using System;
using System.Data;
using System.Data.OleDb;
    class ConnectionOleDb
    {
        static void Main()
        {
            OleDbConnection conn = new OleDbConnection(@"provider = sqloledb;data source = .\sqlexpress;integrated security = sspi;");
            try
            {
                conn.Open();
                Console.WriteLine("\tConnection String: {0}",conn.ConnectionString);
                Console.WriteLine("\tDatabase: {0}",conn.Database);
                Console.WriteLine("\tDataSource: {0}",conn.DataSource);
                Console.WriteLine("\tServerVersion: {0}",conn.ServerVersion);
                Console.WriteLine("\tState: {0}",conn.State);
            }
            catch (OleDbException e)
            {
                Console.WriteLine("Error: " + e);
            }
            finally
            {
                conn.Close();
                Console.WriteLine("Connection closed.");
            } 
        }
    }

Connection Pooling Sample

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
class MainClass
{
  static void Main(string[] args)
  {
        string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb";
        OleDbConnection conn = new OleDbConnection(ConnectionString);       
        
        conn.Open();
        conn.Close();
        OleDbConnection.ReleaseObjectPool();
  }
}

Connect to SqlServer using OleDbConnection

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

    class Program
    {
        static void Main(string[] args)
        {
            string oledbConnectString = "Provider=SQLOLEDB;Data Source=(local);Initial Catalog=AdventureWorks;User Id=sa;Password=password;";
            using (OleDbConnection connection = new OleDbConnection(oledbConnectString))
            {
                connection.Open(  );
                Console.WriteLine("ConnectionString = {0}\n", oledbConnectString);
                Console.WriteLine("State = {0}", connection.State);
                Console.WriteLine("DataSource = {0}", connection.DataSource);
                Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
            }
        }
    }

OleDbConnection connection state

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
class MainClass
{
    public static void Main()
    {
        using (OleDbConnection con = new OleDbConnection())
        {
            con.ConnectionString ="provider=sqloledb;server=(local)\\SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";    
            con.Open();
            if (con.State == ConnectionState.Open)
            {
                Console.WriteLine("OleDbConnection Information:");
                Console.WriteLine("  Connection State = " + con.State);
                Console.WriteLine("  Connection String = " +
                    con.ConnectionString);
                Console.WriteLine("  Database Source = " + con.DataSource);
                Console.WriteLine("  Database = " + con.Database);
                Console.WriteLine("  Server Version = " + con.ServerVersion);
                Console.WriteLine("  Timeout = " + con.ConnectionTimeout);
            }
            else
            {
                Console.WriteLine("OleDbConnection failed to open.");
                Console.WriteLine("  Connection State = " + con.State);
            }
        }
    }
}

OleDbConnection Information:
  Connection State = Open
  Connection String = provider=sqloledb;server=(local)\SQLEXPRESS;database=MyDatabase;Integrated Sec
urity=SSPI
  Database Source = (local)\SQLEXPRESS
  Database = MyDatabase
  Server Version = 09.00.1399
  Timeout = 15

Output properties for OleDbConnection

using System;
using System.Drawing;
using System.Collections;
using System.ruponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

public class MainClass{
    static void Main() {
       string ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb";
       OleDbConnection conn = new OleDbConnection(ConnectionString);
       // Open the connection
       if( conn.State != ConnectionState.Open)
           conn.Open();
       // Show the connection properties
       Console.WriteLine( "Connection String :"+conn.ConnectionString 
           +", DataSource :"+ conn.DataSource.ToString()
           +", Provider :"+ conn.Provider.ToString() 
           +", Server Version:"+ conn.ServerVersion.ToString()
           +", Connection Time Out:"+ conn.ConnectionTimeout.ToString() );
       // Close the connection
       if( conn.State == ConnectionState.Open)
           conn.Close();       
    }
}

Connection String :Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\Northwind.mdb, DataSource :c:\No
rthwind.mdb, Provider :Microsoft.Jet.OLEDB.4.0, Server Version:04.00.0000, Connection Time Out:15

Reading Access Data with OleDbConnection

using System;
using System.Data;            
using System.Data.OleDb;      
using System.Collections.Generic;
using System.Text;
  class Program{
    static void Main(string[] args){
           OleDbConnection thisConnection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=nwind.mdb");
          thisConnection.Open();
    
          OleDbCommand thisCommand = thisConnection.CreateCommand();
    
          thisCommand.rumandText = "SELECT CustomerID, CompanyName FROM Customers";
    
          OleDbDataReader thisReader = thisCommand.ExecuteReader();
    
          while (thisReader.Read()){
             Console.WriteLine("\t{0}\t{1}",
             thisReader["CustomerID"], thisReader["CompanyName"]);
          }
          thisReader.Close();
          thisConnection.Close();
       }  
  }

Retrieves a list of tables in a database

using System;
using System.Data;
using System.Data.OleDb;
public class GetSchema
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;Provider=SQLOLEDB;Initial Catalog=Northwind;Integrated Security=SSPI";
        OleDbConnection con = new OleDbConnection(connectionString);
        DataTable schema;
        con.Open();
        schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, null});
        con.Close();
        foreach (DataRow row in schema.Rows){
            Console.WriteLine(row["TABLE_TYPE"] + ": " + row["TABLE_NAME"]);
        }
    }
}

Retrieving Data from a Text File

using System;
using System.Data;
using System.Data.OleDb;
    class Program
    {
        static void Main(string[] args)
        {
            string sqlSelect = "SELECT * FROM [Category.txt]";
            string connectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
                @"Data Source=..\;" +
                "Extended Properties=\"text;HDR=yes;FMT=Delimited\";";
            OleDbDataAdapter da = new OleDbDataAdapter(sqlSelect, connectString);
            DataTable dt = new DataTable("Categories");
            da.Fill(dt);
            foreach (DataRow row in dt.Rows)
            {
                Console.WriteLine("{0}; {1}; {2}", row["CategoryID"],
                    row["CategoryName"], row["Description"]);
            }
        }
    }