using System;
using System.Data;
using System.Data.SqlClient;
class ResultSetInfo{
static void Main(string[] args) {
string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string sql = @"select FirstName, LastName from Employee";
SqlConnection conn = new SqlConnection(connString);
try {
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();
Console.WriteLine("Column Name: {0} {1}", reader.GetName(0).PadRight(25),reader.GetName(1));
Console.WriteLine("Data Type: {0} {1}", reader.GetDataTypeName(0).PadRight(25), reader.GetDataTypeName(1));
Console.WriteLine("Number of columns in a row: {0}", reader.FieldCount);
Console.WriteLine("'{0}' is at index {1} and its type is: {2}",
reader.GetName(0),reader.GetOrdinal("FirstName"),reader.GetFieldType(0));
Console.WriteLine("'{0}' is at index {1} and its type is: {2}",
reader.GetName(1), reader.GetOrdinal("LastName"),reader.GetFieldType(1));
reader.Close();
}
catch(Exception e)
{
Console.WriteLine("Error Occurred: " + e);
}
finally
{
conn.Close();
}
}
}
Author: coder
Result set info: column Name, column Type, type name
using System;
using System.Data;
using System.Data.SqlClient;
class ResultSetInfo{
static void Main(string[] args) {
string connString = "server=(local)SQLEXPRESS;database=MyDatabase;Integrated Security=SSPI";
string sql = @"select FirstName, LastName from Employee";
SqlConnection conn = new SqlConnection(connString);
try {
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
SqlDataReader reader = cmd.ExecuteReader();
Console.WriteLine("Column Name: {0} {1}", reader.GetName(0).PadRight(25),reader.GetName(1));
Console.WriteLine("Data Type: {0} {1}", reader.GetDataTypeName(0).PadRight(25), reader.GetDataTypeName(1));
while(reader.Read()) {
Console.WriteLine(" {0} {1}",
reader.GetString(0).ToString().PadRight(25),
reader.GetString(1));
}
reader.Close();
}
catch(Exception e)
{
Console.WriteLine("Error Occurred: " + e);
}
finally
{
conn.Close();
}
}
}
Static Helper for executing SQL statements against Oracle Requires the ODP.NET provider
//---------------------------------------------------------------------
// File: OracleDatabaseHelperEx.cs
//
// Summary:
//
// Copyright (c) Hammersmith & Fulham Bridge Partnership. All rights reserved.
//
// THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF ANY
// KIND, WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
// IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
// PURPOSE.
//---------------------------------------------------------------------
using System;
using System.Data;
using System.Data.OracleClient;
namespace BizUnit.Extensions.Utilities
{
/// <summary>
/// Static Helper for executing SQL statements against Oracle
/// Requires the ODP.NET provider
/// </summary>
public class OracleDatabaseHelperEx
{
#region constructor(s)
/// <summary>
/// Constructor for class, default constructor is private to prevent instances being
/// created as the class only has static methods
/// </summary>
public OracleDatabaseHelperEx()
{
}
#endregion
#region Static Methods
/// <summary>
/// Excecutes the SQL statement against the database and returns a DataSet with the results
/// </summary>
/// <param name="connectionString">Database connection string</param>
/// <param name="sqlCommand">SQL statement to execute</param>
/// <returns>DataSet with the results of the executed command</returns>
public DataSet ExecuteSqlCommand( string connectionString, string sqlCommand )
{
DataSet ds = new DataSet() ;
try
{
using ( OracleConnection connection = new OracleConnection( connectionString ) )
{
OracleDataAdapter adapter = new OracleDataAdapter( sqlCommand, connection ) ;
adapter.Fill( ds ) ;
} // connection
}
catch (Exception)
{
throw ;
}
return ds ;
}
/// <summary>
/// Executes the SQL statement and returns the first column of the first row in the resultset returned by the query.
/// </summary>
/// <param name="connectionString">Database connection string</param>
/// <param name="sqlCommand">SQL statement to execute</param>
/// <returns>The contents of the first column of the first row in the resultset</returns>
public int ExecuteScalar( string connectionString, string sqlCommand )
{
OracleConnection connection = null ;
object col = 0 ;
try
{
connection = new OracleConnection( connectionString ) ;
OracleCommand command = new OracleCommand( sqlCommand, connection ) ;
command.Connection.Open() ;
col = command.ExecuteScalar() ;
}
catch ( Exception )
{
throw;
}
finally
{
connection.Close() ;
}
return Convert.ToInt32( col ) ;
}
/// <summary>
/// Executes the SQL statement
/// </summary>
/// <param name="connectionString">Database connection string</param>
/// <param name="sqlCommand">SQL statement to execute</param>
public void ExecuteNonQuery( string connectionString, string sqlCommand )
{
OracleConnection connection = null ;
try
{
connection = new OracleConnection( connectionString ) ;
OracleCommand command = new OracleCommand( sqlCommand, connection ) ;
command.Connection.Open() ;
command.ExecuteNonQuery() ;
}
catch ( Exception )
{
throw;
}
finally
{
connection.Close() ;
}
}
#endregion
}
}
Connect to an Oracle server
using System;
using System.Data;
using System.Data.OracleClient;
class OracleConnect
{
static void Main()
{
string connString = @"server = myServer;uid = scott;password = tiger;";
OracleConnection conn = new OracleConnection(connString);
try
{
conn.Open();
Console.WriteLine("Connection opened.");
Console.WriteLine("Connection Properties:");
Console.WriteLine(" Connection String: {0}", conn.ConnectionString);
Console.WriteLine(" ServerVersion: {0}", conn.ServerVersion);
Console.WriteLine(" State: {0}", conn.State);
} catch (OracleException e) {
Console.WriteLine("Error: " + e);
} finally {
conn.Close();
Console.WriteLine("Connection closed.");
}
}
}
Oracle connection string for C#
using System;
using System.Data;
using System.Data.OleDb;
class OleDbConnectionOracle
{
public static void Main()
{
string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
myOleDbCommand.CommandText = "SELECT empno, ename, sal FROM emp WHERE empno = 7369";
myOleDbConnection.Open();
OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
myOleDbDataReader.Read();
Console.WriteLine("myOleDbDataReader[" empno"] = " + myOleDbDataReader["empno"]);
Console.WriteLine("myOleDbDataReader[" ename"] = " + myOleDbDataReader["ename"]);
Console.WriteLine("myOleDbDataReader[" sal"] = " + myOleDbDataReader["sal"]);
myOleDbDataReader.Close();
myOleDbConnection.Close();
}
}
How to use an OleDbConnection object to connect to an Oracle database
using System;
using System.Data;
using System.Data.OleDb;
class OleDbConnectionOracle
{
public static void Main()
{
string connectionString = "provider=MSDAORA;data source=ORCL;user id=SCOTT;password=TIGER";
OleDbConnection myOleDbConnection = new OleDbConnection(connectionString);
OleDbCommand myOleDbCommand = myOleDbConnection.CreateCommand();
myOleDbCommand.CommandText = "SELECT empno, ename, sal FROM emp WHERE empno = 7369";
myOleDbConnection.Open();
OleDbDataReader myOleDbDataReader = myOleDbCommand.ExecuteReader();
myOleDbDataReader.Read();
Console.WriteLine("myOleDbDataReader[" empno"] = " + myOleDbDataReader["empno"]);
Console.WriteLine("myOleDbDataReader[" ename"] = " + myOleDbDataReader["ename"]);
Console.WriteLine("myOleDbDataReader[" sal"] = " + myOleDbDataReader["sal"]);
myOleDbDataReader.Close();
myOleDbConnection.Close();
}
}
Committed insert
using System;
using System.Data;
using System.Data.OleDb;
public class Transact {
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 cmd = con.CreateCommand();
OleDbTransaction trans = con.BeginTransaction();
cmd.Transaction = trans;
cmd.CommandText ="INSERT INTO Employee VALUES (12,'CD','wwe',10)";
cmd.ExecuteNonQuery();
trans.Commit();
cmd.CommandText = "SELECT First_name FROM Employee";
OleDbDataReader reader = cmd.ExecuteReader();
while(reader.Read())
Console.WriteLine("{0}",
reader.GetString(0));
reader.Close();
con.Close();
}
}