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(" Timeout = " + con.ConnectionTimeout); } else { Console.WriteLine("OleDbConnection failed to open."); Console.WriteLine(" Connection State = " + con.State); } } } }
how to execute a TableDirect command
using System;
using System.Data;
using System.Data.OleDb;
class ExecuteTableDirect
{
public static void Main()
{
OleDbConnection myOleDbConnection =new OleDbConnection(“provider=sqloledb;server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI”);
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
myOleDbCommand.CommandType = CommandType.TableDirect;
myOleDbCommand.CommandText = “Employee”;
myOleDbConnection.Open();
OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
for (int count = 1; count <= 2; count++) { myOleDbDataReader.Read(); Console.WriteLine("myOleDbDataReader[" ID"] = " + myOleDbDataReader["ID"]); Console.WriteLine("myOleDbDataReader[" FirstName"] = " + myOleDbDataReader["FirstName"]); Console.WriteLine("myOleDbDataReader[" LastName"] = " + myOleDbDataReader["LastName"]); } myOleDbDataReader.Close(); myOleDbConnection.Close(); } } [/csharp]
Read query result from OdbcCommand
using System;
using System.Data;
using System.Data.Odbc;
class CommandOdbcExample{
static void Main() {
OdbcConnection thisConnection = new OdbcConnection(“DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PASSWORD=;”);
OdbcCommand nonqueryCommand = thisConnection.CreateCommand();
try {
thisConnection.Open();
nonqueryCommand.CommandText = “CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)”;
Console.WriteLine(nonqueryCommand.CommandText);
nonqueryCommand.ExecuteNonQuery();
nonqueryCommand.CommandText = “INSERT INTO MyTable VALUES (?, ?)”;
nonqueryCommand.Parameters.Add(“@MyName”, OdbcType.VarChar, 30);
nonqueryCommand.Parameters.Add(“@MyNumber”, OdbcType.Int);
string[] names = { “A”, “B”, “C”, “D” } ;
int i;
for (i=1; i<=4; i++){
nonqueryCommand.Parameters["@MyName"].Value = names[i-1];
nonqueryCommand.Parameters["@MyNumber"].Value = i;
Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
}
nonqueryCommand.CommandText = "SELECT MyName, MyNumber FROM MyTable";
OdbcDataReader thisReader = nonqueryCommand.ExecuteReader();
while (thisReader.Read()) {
Console.WriteLine("Name and Number: {0} {1}", thisReader.GetValue(0), thisReader.GetValue(1));
}
thisReader.Close();
nonqueryCommand.CommandText = "DROP TABLE MyTable";
nonqueryCommand.ExecuteNonQuery();
}
catch (OdbcException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
thisConnection.Close();
Console.WriteLine("Connection Closed.");
}
}
}
[/csharp]
Pass parameters to OdbcCommand
using System;
using System.Data;
using System.Data.Odbc;
class CommandOdbcExample{
static void Main() {
OdbcConnection thisConnection = new OdbcConnection(“DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PASSWORD=;”);
OdbcCommand nonqueryCommand = thisConnection.CreateCommand();
try {
thisConnection.Open();
nonqueryCommand.CommandText = “CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)”;
Console.WriteLine(nonqueryCommand.CommandText);
nonqueryCommand.ExecuteNonQuery();
nonqueryCommand.CommandText = “INSERT INTO MyTable VALUES (?, ?)”;
nonqueryCommand.Parameters.Add(“@MyName”, OdbcType.VarChar, 30);
nonqueryCommand.Parameters.Add(“@MyNumber”, OdbcType.Int);
// nonqueryCommand.Prepare();
string[] names = { “A”, “B”, “C”, “D” } ;
int i;
for (i=1; i<=4; i++){
nonqueryCommand.Parameters["@MyName"].Value = names[i-1];
nonqueryCommand.Parameters["@MyNumber"].Value = i;
Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
}
nonqueryCommand.CommandText = "SELECT MyName, MyNumber FROM MyTable";
OdbcDataReader thisReader = nonqueryCommand.ExecuteReader();
while (thisReader.Read()) {
Console.WriteLine("Name and Number: {0} {1}", thisReader.GetValue(0), thisReader.GetValue(1));
}
thisReader.Close();
nonqueryCommand.CommandText = "DROP TABLE MyTable";
nonqueryCommand.ExecuteNonQuery();
}
catch (OdbcException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
thisConnection.Close();
Console.WriteLine("Connection Closed.");
}
}
}
[/csharp]
Create database through OdbcCommand
using System;
using System.Data;
using System.Data.Odbc;
class CommandOdbcExample{
static void Main() {
OdbcConnection thisConnection = new OdbcConnection(“DRIVER={MySQL ODBC 3.51 Driver};SERVER=localhost;DATABASE=test;UID=root;PASSWORD=;”);
OdbcCommand nonqueryCommand = thisConnection.CreateCommand();
try {
thisConnection.Open();
nonqueryCommand.CommandText = “CREATE TABLE MyTable (MyName VARCHAR (30), MyNumber integer)”;
Console.WriteLine(nonqueryCommand.CommandText);
nonqueryCommand.ExecuteNonQuery();
nonqueryCommand.CommandText = “INSERT INTO MyTable VALUES (?, ?)”;
nonqueryCommand.Parameters.Add(“@MyName”, OdbcType.VarChar, 30);
nonqueryCommand.Parameters.Add(“@MyNumber”, OdbcType.Int);
// nonqueryCommand.Prepare();
string[] names = { “A”, “B”, “C”, “D” } ;
int i;
for (i=1; i<=4; i++){
nonqueryCommand.Parameters["@MyName"].Value = names[i-1];
nonqueryCommand.Parameters["@MyNumber"].Value = i;
Console.WriteLine(nonqueryCommand.CommandText);
Console.WriteLine("Number of Rows Affected is: {0}", nonqueryCommand.ExecuteNonQuery());
}
nonqueryCommand.CommandText = "SELECT MyName, MyNumber FROM MyTable";
OdbcDataReader thisReader = nonqueryCommand.ExecuteReader();
while (thisReader.Read()) {
Console.WriteLine("Name and Number: {0} {1}", thisReader.GetValue(0), thisReader.GetValue(1));
}
thisReader.Close();
nonqueryCommand.CommandText = "DROP TABLE MyTable";
nonqueryCommand.ExecuteNonQuery();
}
catch (OdbcException ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
thisConnection.Close();
Console.WriteLine("Connection Closed.");
}
}
}
[/csharp]
Odbc Error Handler
using System; using System.Diagnostics; using System.Drawing; using System.Collections; using System.ComponentModel; using System.Windows.Forms; using System.Data; using System.Data.Odbc; public class MainClass { [STAThread] static void Main() { OdbcConnection cn; try { cn = new OdbcConnection(); cn.ConnectionString = "driver={SQL Serve};server=.;uid=admin;pwd=pw;database=biblio"; cn.Open(); } catch (OdbcException ex) { if (ex.InnerException != null) { Debug.WriteLine("InnerException:" + ex.InnerException.Message); } MessageBox.Show(ex.ToString()); Debug.WriteLine("Message:" + ex.Message.ToString()); Debug.WriteLine("Source:" + ex.Source.ToString()); Debug.WriteLine("StackTrace:" + ex.StackTrace.ToString()); Debug.WriteLine("TargetSite:" + ex.TargetSite.ToString()); // Not in OleDB if (ex.HelpLink != null) { Debug.WriteLine("HelpLink:" + ex.HelpLink); } } } }
ODBC connection
using System; using System.Data; using System.Data.Odbc; class OdbcProvider { static void Main(string[] args) { string connString = @"dsn=northwindodbc"; string sql = @"select * from employees"; OdbcConnection conn = null; OdbcDataReader reader = null; try { conn = new OdbcConnection(connString); conn.Open(); OdbcCommand cmd = new OdbcCommand(sql, conn); reader = cmd.ExecuteReader(); Console.WriteLine( "Querying database {0} with query {1} " , conn.Database , cmd.CommandText ); Console.WriteLine("First Name Last Name "); while(reader.Read()) { Console.WriteLine( "{0} | {1}" , reader["FirstName"].ToString().PadLeft(10) , reader[1].ToString().PadLeft(10) ); } } catch (Exception e) { Console.WriteLine("Error: " + e); } finally { reader.Close(); conn.Close(); } } }