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;" + @"Data Source=.sqlexpress;" + "Initial Catalog=Northwind;" + "Integrated Security=SSPI"; con.Open(); if (con.State == ConnectionState.Open) { Console.WriteLine(" Connection String = " + con.ConnectionString); } else { Console.WriteLine("OleDbConnection failed to open."); Console.WriteLine(" Connection State = " + con.State); } } } }
Author: coder
OleDbConnection: 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;" + @"Data Source=.sqlexpress;" + "Initial Catalog=Northwind;" + "Integrated Security=SSPI"; con.Open(); if (con.State == ConnectionState.Open) { Console.WriteLine(" Connection State = " + con.State); } else { Console.WriteLine("OleDbConnection failed to open."); Console.WriteLine(" Connection State = " + con.State); } } } }
OleDbConnection string
using System; using System.Diagnostics; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; using System.Data.OleDb; public class MainClass { static void Main() { try { OleDbConnection cnOleDb = new OleDbConnection("Provider=SQLOLEDB.1;Password=pw;Persist Security Info=False;User ID=admin;Initial Catalog=biblio;Data Source=betav9;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=BETAV9;Use Encryption for Data=False;Tag with column collation when possible=False"); cnOleDb.Open(); } catch (OleDbException exOleDb) { MessageBox.Show(exOleDb.Message); } catch (Exception ex) { MessageBox.Show(ex.Message); } } }
Read data from ole db connection
using System; using System.Data.OleDb; public class ReadFromOleDb { [STAThread] static void Main(string[] args) { String sConn = "provider=sqloledb;server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI"; String sSQL = "select id, firstname, lastname from Employee"; OleDbConnection oConn = new OleDbConnection(sConn); oConn.Open(); OleDbCommand oCmd = new OleDbCommand(sSQL, oConn); OleDbDataReader oReader = oCmd.ExecuteReader(); int idxID = oReader.GetOrdinal("id"); int idxFirstName = oReader.GetOrdinal("firstname"); int idxLastName = oReader.GetOrdinal("lastname"); while(oReader.Read()) { Console.WriteLine("{0} {1} {2}", oReader.GetValue(idxID), oReader.GetValue(idxFirstName), oReader.GetValue(idxLastName)); } } }
OleDbConnection connection: ConnectionString, Database, DataSource, ServerVersion and State
using System; using System.Data; using System.Data.OleDb; class AccessConnect { static void Main() { string connString = @"provider = microsoft.jet.oledb.4.0;data source = Employee.mdb;"; OleDbConnection conn = new OleDbConnection(connString); try { conn.Open(); Console.WriteLine("Connection opened."); Console.WriteLine("Connection Properties:"); Console.WriteLine(" Connection String: {0}", conn.ConnectionString); Console.WriteLine(" Database: {0}",conn.Database); Console.WriteLine(" DataSource: {0}",conn.DataSource); Console.WriteLine(" ServerVersion: {0}", conn.ServerVersion); Console.WriteLine(" State: {0}", conn.State); } catch (OleDbException e) { Console.WriteLine("Error: " + e); } finally { conn.Close(); Console.WriteLine("Connection closed."); } } }
Pass parameter to OleDbCommand
using System; using System.Data; using System.Data.OleDb; public class Prepare { public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); Console.WriteLine("Made the connection to the database"); OleDbCommand cmd1 = con.CreateCommand(); cmd1.CommandText = "SELECT ID FROM Employee " + "WHERE id BETWEEN ? AND ?"; OleDbParameter p1 = new OleDbParameter(); OleDbParameter p2 = new OleDbParameter(); cmd1.Parameters.Add(p1); cmd1.Parameters.Add(p2); p1.Value = "01"; p2.Value = "03"; OleDbDataReader reader = cmd1.ExecuteReader(); while(reader.Read()) Console.WriteLine("{0}", reader.GetInt32(0)); reader.Close(); con.Close(); } }
Create table and populate data
using System; using System.Data; using System.Data.OleDb; public class Create { public static void Main () { String connect = "Provider=Microsoft.JET.OLEDB.4.0;data source=.Employee.mdb"; OleDbConnection con = new OleDbConnection(connect); con.Open(); OleDbCommand cmd = con.CreateCommand(); cmd.CommandText = "CREATE TABLE Customer (CustomerID " + "CHAR(4), CustomerName VARCHAR(25), Address " + "VARCHAR(25), BalanceDue DECIMAL)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES (1234,'F F','22 First St.',67.00)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES (5678,'D D','33 Second St.',130.95)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES (4321,'M M','44 Third St.',0)"; cmd.ExecuteNonQuery(); cmd.CommandText = "INSERT INTO Customer VALUES (8765,'C C','55 Fourth St.', 0)"; cmd.ExecuteNonQuery(); con.Close(); } }