Csharp/CSharp Tutorial/ADO.Net/Oracle
Содержание
- 1 A connection string using integrated security for Oracle database
- 2 A connection string without integrated security for Oracle database
- 3 Connecting to an Oracle Database with OracleConnection
- 4 Connection String for Oracle database
- 5 Connect to an Oracle Database using .NET data provider for OLE DB
- 6 Connect to an Oracle Database using OdbcConnection
- 7 Do a delete command to Oracle database
- 8 Do an insert command to Oracle database
- 9 Do an update to Oracle database
- 10 Get connection state and server version
- 11 how to use an OleDbConnection object to connect to an Oracle database
- 12 Manual Loopup for Oracle database
- 13 No Connection Pooling
- 14 Read decimal from OracleDataReader
- 15 Read string from OracleDataReader
- 16 Rollback for Oracle database
- 17 Runs the CustomerAdd stored procedure.
- 18 Use OracleConnection to connect to Oracle database
- 19 Using OracleCommandBuilder
- 20 Using OracleCommand to do query
- 21 Using OracleCommand to do the query sql
- 22 Using OracleDataAdapter
- 23 Using OracleDataReader to read from Oracle database
- 24 Using OracleParameter
A connection string using integrated security for Oracle database
<source lang="csharp">Data Source=ORCL;Integrated Security=yes;</source>
A connection string without integrated security for Oracle database
<source lang="csharp">Data Source=ORCL;User Id=hr;Password=password;</source>
Connecting to an Oracle Database with OracleConnection
<source lang="csharp">using System; using System.Data.OracleClient; using System.Data.OleDb; using System.Data.Odbc;
class Program
{
static void Main(string[] args)
{
string oracleConnectString ="Data Source=ORCL;User Id=hr;Password=password;";
using (OracleConnection connection = new OracleConnection(oracleConnectString))
{
connection.Open( );
Console.WriteLine("---Microsoft .NET Provider for Oracle---");
Console.WriteLine("ConnectionString = {0}\n", oracleConnectString);
Console.WriteLine("State = {0}", connection.State);
Console.WriteLine("DataSource = {0}", connection.DataSource);
Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
}
} }</source>
Connection String for Oracle database
<source lang="csharp">using System; using System.Data; using System.Data.OracleClient;
class Class1{
static void Main(string[] args)
{
string connstr = "User Id=scott; Password=tiger; Data Source=oranet";
OracleConnection con = new OracleConnection();
con.ConnectionString = connstr;
Console.WriteLine("Connection String 1: {0}", con.ConnectionString);
con.Open();
Console.WriteLine("Connection String 2: {0}", con.ConnectionString);
con.Close();
Console.WriteLine("Connection String 3: {0}", con.ConnectionString);
con.Dispose();
}
}</source>
Connect to an Oracle Database using .NET data provider for OLE DB
<source lang="csharp">using System; using System.Data.OracleClient; using System.Data.OleDb; using System.Data.Odbc;
class Program
{
static void Main(string[] args)
{
string oledbConnectString = "Provider=MSDAORA;Data Source=ORCL;User Id=hr;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);
}
}
}</source>
Connect to an Oracle Database using OdbcConnection
<source lang="csharp">using System; using System.Data.OracleClient; using System.Data.OleDb; using System.Data.Odbc;
class Program
{
static void Main(string[] args)
{
string odbcConnectString = "Driver={Oracle in OraDb10g_home1};Server=ORCL;uid=hr;pwd=password;";
using (OdbcConnection connection = new OdbcConnection(odbcConnectString))
{
connection.Open( );
Console.WriteLine("ConnectionString = {0}\n", odbcConnectString);
Console.WriteLine("State = {0}", connection.State);
Console.WriteLine("DataSource = {0}", connection.DataSource);
Console.WriteLine("ServerVersion = {0}", connection.ServerVersion);
}
}
}</source>
Do a delete command to Oracle database
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class MainClass
{
public static void Main()
{
OracleConnection oraConn = new OracleConnection();
string connString = "User Id=oranetuser; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.ConnectionString = connString;
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
if (oraConn.State == ConnectionState.Open)
{
string sqlDelete = "delete from PlayerTable where player_num = :p_num";
OracleCommand cmdDelete = new OracleCommand();
cmdDelete.rumandText = sqlDelete;
cmdDelete.Connection = oraConn;
OracleParameter pPlayerNum = new OracleParameter();
pPlayerNum.DbType = DbType.Decimal;
pPlayerNum.Value = 1;
pPlayerNum.ParameterName = "p_num";
cmdDelete.Parameters.Add(pPlayerNum);
cmdDelete.ExecuteNonQuery();
cmdDelete.Dispose();
}
}
}</source>
Do an insert command to Oracle database
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient; public class MainClass {
public static void Main()
{
OracleConnection oraConn = new OracleConnection();
string connString = "User Id=oranetuser; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.ConnectionString = connString;
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Exception Caught");
}
}
if (oraConn.State == ConnectionState.Open)
{
string sqlInsert = "insert into PlayerTable (player_num, last_name, first_name, position, club) ";
sqlInsert += "values (:p_num, :p_last, :p_first, :p_pos, :p_club)";
OracleCommand cmdInsert = new OracleCommand();
cmdInsert.rumandText = sqlInsert;
cmdInsert.Connection = oraConn;
OracleParameter pPlayerNum = new OracleParameter();
pPlayerNum.DbType = DbType.Decimal;
pPlayerNum.Value = 10;
pPlayerNum.ParameterName = "p_num";
OracleParameter pLastName = new OracleParameter();
pLastName.Value = "Last";
pLastName.ParameterName = "p_last";
OracleParameter pFirstName = new OracleParameter();
pFirstName.Value = "FirstName";
pFirstName.ParameterName = "p_first";
OracleParameter pPosition = new OracleParameter();
pPosition.Value = "A";
pPosition.ParameterName = "p_pos";
OracleParameter pClub = new OracleParameter();
pClub.Value = "abc";
pClub.ParameterName = "p_club";
cmdInsert.Parameters.Add(pPlayerNum);
cmdInsert.Parameters.Add(pLastName);
cmdInsert.Parameters.Add(pFirstName);
cmdInsert.Parameters.Add(pPosition);
cmdInsert.Parameters.Add(pClub);
cmdInsert.ExecuteNonQuery();
cmdInsert.Dispose();
}
}
}</source>
Do an update to Oracle database
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class MainClass
{
public static void Main()
{
OracleConnection oraConn = new OracleConnection();
string connString = "User Id=oranetuser; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.ConnectionString = connString;
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
if (oraConn.State == ConnectionState.Open)
{
string sqlUpdate = "update PlayerTable ";
sqlUpdate += "set last_name = :p_last, ";
sqlUpdate += "first_name = :p_first, ";
sqlUpdate += "position = :p_pos, ";
sqlUpdate += "club = :p_club " ;
sqlUpdate += "where player_num = :p_num";
OracleCommand cmdUpdate = new OracleCommand();
cmdUpdate.rumandText = sqlUpdate;
cmdUpdate.Connection = oraConn;
OracleParameter pPlayerNum = new OracleParameter();
pPlayerNum.DbType = DbType.Decimal;
pPlayerNum.Value = 10;
pPlayerNum.ParameterName = "p_num";
OracleParameter pLastName = new OracleParameter();
pLastName.Value = "Tom";
pLastName.ParameterName = "p_last";
OracleParameter pFirstName = new OracleParameter();
pFirstName.Value = "Gib";
pFirstName.ParameterName = "p_first";
OracleParameter pPosition = new OracleParameter();
pPosition.Value = "a";
pPosition.ParameterName = "p_pos";
OracleParameter pClub = new OracleParameter();
pClub.Value = "abc";
pClub.ParameterName = "p_club";
cmdUpdate.Parameters.Add(pLastName);
cmdUpdate.Parameters.Add(pFirstName);
cmdUpdate.Parameters.Add(pPosition);
cmdUpdate.Parameters.Add(pClub);
cmdUpdate.Parameters.Add(pPlayerNum);
cmdUpdate.ExecuteNonQuery();
MessageBox.Show("Record Updated Successfully" , "Record Updated");
cmdUpdate.Dispose();
}
}
}</source>
Get connection state and server version
<source lang="csharp">using System; using System.Data.OracleClient;
class Class1
{
static void Main(string[] args)
{
String connString = "User Id=oranetuser;Password=demo";
OracleConnection oraConn = new OracleConnection(connString);
try
{
oraConn.Open();
Console.WriteLine(oraConn.ConnectionString.ToString() + "\n");
Console.WriteLine(oraConn.State.ToString() + "\n");
Console.WriteLine(oraConn.ServerVersion.ToString());
}
catch (Exception ex)
{
Console.WriteLine("Error occured: " + ex.Message);
}
finally
{
if (oraConn.State == System.Data.ConnectionState.Open)
{
oraConn.Close();
}
}
}
}</source>
how to use an OleDbConnection object to connect to an Oracle database
<source lang="csharp">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.rumandText =
"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();
}
}</source>
Manual Loopup for Oracle database
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class MainClass
{
public static void Main()
{
OracleConnection oraConn = new OracleConnection();
string connString = "User Id=oranetuser; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.ConnectionString = connString;
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
if (oraConn.State == ConnectionState.Open)
{
string sqlLookup = "select last_name, first_name, from PlayerTable ";
sqlLookup += "where player_num = :player_num";
OracleCommand cmdLookup = new OracleCommand();
cmdLookup.rumandText = sqlLookup;
cmdLookup.Connection = oraConn;
OracleParameter pPlayerNum = new OracleParameter();
pPlayerNum.DbType = DbType.Decimal;
pPlayerNum.Value = 10;
pPlayerNum.ParameterName = "player_num";
cmdLookup.Parameters.Add(pPlayerNum);
OracleDataReader dataReader = cmdLookup.ExecuteReader();
if (dataReader.Read()){
Console.WriteLine(dataReader.GetString(0));
Console.WriteLine(dataReader.GetString(1));
Console.WriteLine(dataReader.GetString(2));
Console.WriteLine(dataReader.GetString(3));
}else{
MessageBox.Show("No record for Player Number Found" , "No Record Found");
}
dataReader.Close();
dataReader.Dispose();
cmdLookup.Dispose();
}
}
}</source>
No Connection Pooling
<source lang="csharp">using System; using System.Data.OracleClient;
class Class1
{
static void Main(string[] args)
{
string l_connect = "User Id=oranetuser;Password=demo;Data Source=oranet;pooling=false";
OracleConnection conn_1 = new OracleConnection(l_connect);
conn_1.Open();
conn_1.Dispose();
OracleConnection conn_2 = new OracleConnection(l_connect);
conn_2.Open();
conn_2.Dispose();
}
}</source>
Read decimal from OracleDataReader
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.Button btnGetIDs;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.ruboBox cbEmpIds;
private System.Windows.Forms.Label lblFirstName;
private System.Windows.Forms.Label lblLastName;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Button btnLookup1;
private System.Windows.Forms.Button btnLookup2;
private System.Windows.Forms.Label lblEmail;
private System.Windows.Forms.Label lblPhone;
private System.Windows.Forms.Label lblEmailText;
private System.Windows.Forms.Label lblPhoneText;
private System.Windows.Forms.Button btnReset;
private System.Windows.Forms.Button btnNoBinds;
private System.Data.OracleClient.OracleConnection oraConn;
private System.Data.OracleClient.OracleCommand cmdGetIDs;
private System.Data.OracleClient.OracleCommand cmdLookup1;
private System.Data.OracleClient.OracleCommand cmdLookup2;
private System.Data.OracleClient.OracleCommand cmdNoBinds1;
private System.Data.OracleClient.OracleCommand cmdNoBinds2;
public Form1()
{
this.btnConnect = new System.Windows.Forms.Button();
this.btnGetIDs = new System.Windows.Forms.Button();
this.cbEmpIds = new System.Windows.Forms.ruboBox();
this.label1 = new System.Windows.Forms.Label();
this.lblFirstName = new System.Windows.Forms.Label();
this.lblLastName = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label5 = new System.Windows.Forms.Label();
this.btnLookup1 = new System.Windows.Forms.Button();
this.btnLookup2 = new System.Windows.Forms.Button();
this.lblEmail = new System.Windows.Forms.Label();
this.lblPhone = new System.Windows.Forms.Label();
this.lblEmailText = new System.Windows.Forms.Label();
this.lblPhoneText = new System.Windows.Forms.Label();
this.btnReset = new System.Windows.Forms.Button();
this.btnNoBinds = new System.Windows.Forms.Button();
this.oraConn = new System.Data.OracleClient.OracleConnection();
this.cmdGetIDs = new System.Data.OracleClient.OracleCommand();
this.cmdLookup1 = new System.Data.OracleClient.OracleCommand();
this.cmdLookup2 = new System.Data.OracleClient.OracleCommand();
this.cmdNoBinds1 = new System.Data.OracleClient.OracleCommand();
this.cmdNoBinds2 = new System.Data.OracleClient.OracleCommand();
this.SuspendLayout();
this.btnConnect.Location = new System.Drawing.Point(32, 44);
this.btnConnect.Text = "C&onnect";
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
this.btnGetIDs.Location = new System.Drawing.Point(32, 80);
this.btnGetIDs.Text = "&Get IDs";
this.btnGetIDs.Click += new System.EventHandler(this.btnGetIDs_Click);
this.cbEmpIds.DropDownStyle = System.Windows.Forms.ruboBoxStyle.DropDownList;
this.cbEmpIds.Location = new System.Drawing.Point(144, 44);
this.cbEmpIds.Size = new System.Drawing.Size(68, 21);
this.label1.Location = new System.Drawing.Point(144, 24);
this.label1.Size = new System.Drawing.Size(72, 16);
this.label1.Text = "&Employee ID:";
this.lblFirstName.Location = new System.Drawing.Point(228, 48);
this.lblFirstName.Size = new System.Drawing.Size(116, 16);
this.lblLastName.Location = new System.Drawing.Point(364, 48);
this.lblLastName.Size = new System.Drawing.Size(128, 16);
this.label4.Location = new System.Drawing.Point(228, 24);
this.label4.Size = new System.Drawing.Size(100, 16);
this.label4.Text = "&First Name:";
this.label5.Location = new System.Drawing.Point(364, 24);
this.label5.Size = new System.Drawing.Size(100, 16);
this.label5.Text = "&Last Name:";
this.btnLookup1.Location = new System.Drawing.Point(32, 116);
this.btnLookup1.Text = "Lookup &1";
this.btnLookup1.Click += new System.EventHandler(this.btnLookup1_Click);
this.btnLookup2.Location = new System.Drawing.Point(32, 152);
this.btnLookup2.Text = "Lookup &2";
this.btnLookup2.Click += new System.EventHandler(this.btnLookup2_Click);
this.lblEmail.Location = new System.Drawing.Point(228, 88);
this.lblEmail.Size = new System.Drawing.Size(100, 16);
this.lblEmail.Text = "E&mail:";
this.lblPhone.Location = new System.Drawing.Point(364, 88);
this.lblPhone.Size = new System.Drawing.Size(100, 16);
this.lblPhone.Text = "Phone &Number:";
this.lblEmailText.Location = new System.Drawing.Point(228, 112);
this.lblEmailText.Size = new System.Drawing.Size(116, 16);
this.lblPhoneText.Location = new System.Drawing.Point(364, 112);
this.lblPhoneText.Size = new System.Drawing.Size(128, 16);
this.btnReset.Location = new System.Drawing.Point(32, 224);
this.btnReset.Text = "&Reset";
this.btnReset.Click += new System.EventHandler(this.btnReset_Click);
this.btnNoBinds.Location = new System.Drawing.Point(32, 188);
this.btnNoBinds.Text = "No &Binds";
this.btnNoBinds.Click += new System.EventHandler(this.btnNoBinds_Click);
this.oraConn.ConnectionString = "user id=hr;data source=oranet;password=demo";
this.cmdGetIDs.rumandText = "SELECT EMPLOYEE_ID FROM EMPLOYEES ORDER BY EMPLOYEE_ID";
this.cmdGetIDs.Connection = this.oraConn;
this.cmdLookup1.rumandText = "SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE (EMPLOYEE_ID = :p_id)";
this.cmdLookup1.Connection = this.oraConn;
this.cmdLookup1.Parameters.Add(new System.Data.OracleClient.OracleParameter(":p_id", System.Data.OracleClient.OracleType.Number, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(6)), ((System.Byte)(0)), "EMPLOYEE_ID", System.Data.DataRowVersion.Current, null));
this.cmdLookup2.rumandText = "SELECT EMAIL, PHONE_NUMBER FROM EMPLOYEES WHERE (FIRST_NAME = :p_first) AND (LAST" +
"_NAME = :p_last)";
this.cmdLookup2.Connection = this.oraConn;
this.cmdLookup2.Parameters.Add(new System.Data.OracleClient.OracleParameter(":p_first", System.Data.OracleClient.OracleType.VarChar, 20, "FIRST_NAME"));
this.cmdLookup2.Parameters.Add(new System.Data.OracleClient.OracleParameter(":p_last", System.Data.OracleClient.OracleType.VarChar, 25, "LAST_NAME"));
this.cmdNoBinds1.Connection = this.oraConn;
this.cmdNoBinds2.Connection = this.oraConn;
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(534, 264);
this.Controls.Add(this.btnNoBinds);
this.Controls.Add(this.btnReset);
this.Controls.Add(this.lblPhoneText);
this.Controls.Add(this.lblEmailText);
this.Controls.Add(this.lblPhone);
this.Controls.Add(this.lblEmail);
this.Controls.Add(this.btnLookup2);
this.Controls.Add(this.btnLookup1);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.lblLastName);
this.Controls.Add(this.lblFirstName);
this.Controls.Add(this.label1);
this.Controls.Add(this.cbEmpIds);
this.Controls.Add(this.btnGetIDs);
this.Controls.Add(this.btnConnect);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
}
static void Main()
{
Application.Run(new Form1());
}
private void btnConnect_Click(object sender, System.EventArgs e)
{
if (oraConn.State != ConnectionState.Open)
{
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
}
private void btnGetIDs_Click(object sender, System.EventArgs e)
{
OracleDataReader dataReader = cmdGetIDs.ExecuteReader();
while (dataReader.Read())
{
cbEmpIds.Items.Add(dataReader.GetDecimal(0));
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
}
private void btnLookup1_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
cmdLookup1.Parameters[0].Value = Convert.ToDecimal(selectedItem.ToString());
OracleDataReader dataReader = cmdLookup1.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
}
private void btnLookup2_Click(object sender, System.EventArgs e)
{
cmdLookup2.Parameters[0].Value = lblFirstName.Text;
cmdLookup2.Parameters[1].Value = lblLastName.Text;
OracleDataReader dataReader = cmdLookup2.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
private void btnReset_Click(object sender, System.EventArgs e)
{
cbEmpIds.SelectedIndex = -1;
lblFirstName.Text = "";
lblLastName.Text = "";
lblEmailText.Text = "";
lblPhoneText.Text = "";
}
private void btnNoBinds_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
OracleDataReader dataReader;
cmdNoBinds1.rumandText = "select first_name, last_name from employees where employee_id = " + selectedItem.ToString();
dataReader = cmdNoBinds1.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
cmdNoBinds2.rumandText = "select email, phone_number from employees where first_name = "" + lblFirstName.Text + "" and last_name = "" + lblLastName.Text +""";
dataReader = cmdNoBinds2.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
}
}</source>
Read string from OracleDataReader
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.Button btnGetIDs;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.ruboBox cbEmpIds;
private System.Windows.Forms.Label lblFirstName;
private System.Windows.Forms.Label lblLastName;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Button btnLookup1;
private System.Windows.Forms.Button btnLookup2;
private System.Windows.Forms.Label lblEmail;
private System.Windows.Forms.Label lblPhone;
private System.Windows.Forms.Label lblEmailText;
private System.Windows.Forms.Label lblPhoneText;
private System.Windows.Forms.Button btnReset;
private System.Windows.Forms.Button btnNoBinds;
private System.Data.OracleClient.OracleConnection oraConn;
private System.Data.OracleClient.OracleCommand cmdGetIDs;
private System.Data.OracleClient.OracleCommand cmdLookup1;
private System.Data.OracleClient.OracleCommand cmdLookup2;
private System.Data.OracleClient.OracleCommand cmdNoBinds1;
private System.Data.OracleClient.OracleCommand cmdNoBinds2;
public Form1()
{
this.btnConnect = new System.Windows.Forms.Button();
this.btnGetIDs = new System.Windows.Forms.Button();
this.cbEmpIds = new System.Windows.Forms.ruboBox();
this.label1 = new System.Windows.Forms.Label();
this.lblFirstName = new System.Windows.Forms.Label();
this.lblLastName = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label5 = new System.Windows.Forms.Label();
this.btnLookup1 = new System.Windows.Forms.Button();
this.btnLookup2 = new System.Windows.Forms.Button();
this.lblEmail = new System.Windows.Forms.Label();
this.lblPhone = new System.Windows.Forms.Label();
this.lblEmailText = new System.Windows.Forms.Label();
this.lblPhoneText = new System.Windows.Forms.Label();
this.btnReset = new System.Windows.Forms.Button();
this.btnNoBinds = new System.Windows.Forms.Button();
this.oraConn = new System.Data.OracleClient.OracleConnection();
this.cmdGetIDs = new System.Data.OracleClient.OracleCommand();
this.cmdLookup1 = new System.Data.OracleClient.OracleCommand();
this.cmdLookup2 = new System.Data.OracleClient.OracleCommand();
this.cmdNoBinds1 = new System.Data.OracleClient.OracleCommand();
this.cmdNoBinds2 = new System.Data.OracleClient.OracleCommand();
this.SuspendLayout();
this.btnConnect.Location = new System.Drawing.Point(32, 44);
this.btnConnect.Text = "C&onnect";
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
this.btnGetIDs.Location = new System.Drawing.Point(32, 80);
this.btnGetIDs.Text = "&Get IDs";
this.btnGetIDs.Click += new System.EventHandler(this.btnGetIDs_Click);
this.cbEmpIds.DropDownStyle = System.Windows.Forms.ruboBoxStyle.DropDownList;
this.cbEmpIds.Location = new System.Drawing.Point(144, 44);
this.cbEmpIds.Size = new System.Drawing.Size(68, 21);
this.label1.Location = new System.Drawing.Point(144, 24);
this.label1.Size = new System.Drawing.Size(72, 16);
this.label1.Text = "&Employee ID:";
this.lblFirstName.Location = new System.Drawing.Point(228, 48);
this.lblFirstName.Size = new System.Drawing.Size(116, 16);
this.lblLastName.Location = new System.Drawing.Point(364, 48);
this.lblLastName.Size = new System.Drawing.Size(128, 16);
this.label4.Location = new System.Drawing.Point(228, 24);
this.label4.Size = new System.Drawing.Size(100, 16);
this.label4.Text = "&First Name:";
this.label5.Location = new System.Drawing.Point(364, 24);
this.label5.Size = new System.Drawing.Size(100, 16);
this.label5.Text = "&Last Name:";
this.btnLookup1.Location = new System.Drawing.Point(32, 116);
this.btnLookup1.Text = "Lookup &1";
this.btnLookup1.Click += new System.EventHandler(this.btnLookup1_Click);
this.btnLookup2.Location = new System.Drawing.Point(32, 152);
this.btnLookup2.Text = "Lookup &2";
this.btnLookup2.Click += new System.EventHandler(this.btnLookup2_Click);
this.lblEmail.Location = new System.Drawing.Point(228, 88);
this.lblEmail.Size = new System.Drawing.Size(100, 16);
this.lblEmail.Text = "E&mail:";
this.lblPhone.Location = new System.Drawing.Point(364, 88);
this.lblPhone.Size = new System.Drawing.Size(100, 16);
this.lblPhone.Text = "Phone &Number:";
this.lblEmailText.Location = new System.Drawing.Point(228, 112);
this.lblEmailText.Size = new System.Drawing.Size(116, 16);
this.lblPhoneText.Location = new System.Drawing.Point(364, 112);
this.lblPhoneText.Size = new System.Drawing.Size(128, 16);
this.btnReset.Location = new System.Drawing.Point(32, 224);
this.btnReset.Text = "&Reset";
this.btnReset.Click += new System.EventHandler(this.btnReset_Click);
this.btnNoBinds.Location = new System.Drawing.Point(32, 188);
this.btnNoBinds.Text = "No &Binds";
this.btnNoBinds.Click += new System.EventHandler(this.btnNoBinds_Click);
this.oraConn.ConnectionString = "user id=hr;data source=oranet;password=demo";
this.cmdGetIDs.rumandText = "SELECT EMPLOYEE_ID FROM EMPLOYEES ORDER BY EMPLOYEE_ID";
this.cmdGetIDs.Connection = this.oraConn;
this.cmdLookup1.rumandText = "SELECT FIRST_NAME, LAST_NAME FROM EMPLOYEES WHERE (EMPLOYEE_ID = :p_id)";
this.cmdLookup1.Connection = this.oraConn;
this.cmdLookup1.Parameters.Add(new System.Data.OracleClient.OracleParameter(":p_id", System.Data.OracleClient.OracleType.Number, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(6)), ((System.Byte)(0)), "EMPLOYEE_ID", System.Data.DataRowVersion.Current, null));
this.cmdLookup2.rumandText = "SELECT EMAIL, PHONE_NUMBER FROM EMPLOYEES WHERE (FIRST_NAME = :p_first) AND (LAST" +
"_NAME = :p_last)";
this.cmdLookup2.Connection = this.oraConn;
this.cmdLookup2.Parameters.Add(new System.Data.OracleClient.OracleParameter(":p_first", System.Data.OracleClient.OracleType.VarChar, 20, "FIRST_NAME"));
this.cmdLookup2.Parameters.Add(new System.Data.OracleClient.OracleParameter(":p_last", System.Data.OracleClient.OracleType.VarChar, 25, "LAST_NAME"));
this.cmdNoBinds1.Connection = this.oraConn;
this.cmdNoBinds2.Connection = this.oraConn;
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(534, 264);
this.Controls.Add(this.btnNoBinds);
this.Controls.Add(this.btnReset);
this.Controls.Add(this.lblPhoneText);
this.Controls.Add(this.lblEmailText);
this.Controls.Add(this.lblPhone);
this.Controls.Add(this.lblEmail);
this.Controls.Add(this.btnLookup2);
this.Controls.Add(this.btnLookup1);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.lblLastName);
this.Controls.Add(this.lblFirstName);
this.Controls.Add(this.label1);
this.Controls.Add(this.cbEmpIds);
this.Controls.Add(this.btnGetIDs);
this.Controls.Add(this.btnConnect);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
}
static void Main()
{
Application.Run(new Form1());
}
private void btnConnect_Click(object sender, System.EventArgs e)
{
if (oraConn.State != ConnectionState.Open)
{
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
}
private void btnGetIDs_Click(object sender, System.EventArgs e)
{
OracleDataReader dataReader = cmdGetIDs.ExecuteReader();
while (dataReader.Read())
{
cbEmpIds.Items.Add(dataReader.GetDecimal(0));
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
}
private void btnLookup1_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
cmdLookup1.Parameters[0].Value = Convert.ToDecimal(selectedItem.ToString());
OracleDataReader dataReader = cmdLookup1.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
}
private void btnLookup2_Click(object sender, System.EventArgs e)
{
cmdLookup2.Parameters[0].Value = lblFirstName.Text;
cmdLookup2.Parameters[1].Value = lblLastName.Text;
OracleDataReader dataReader = cmdLookup2.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
private void btnReset_Click(object sender, System.EventArgs e)
{
cbEmpIds.SelectedIndex = -1;
lblFirstName.Text = "";
lblLastName.Text = "";
lblEmailText.Text = "";
lblPhoneText.Text = "";
}
private void btnNoBinds_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
OracleDataReader dataReader;
cmdNoBinds1.rumandText = "select first_name, last_name from employees where employee_id = " + selectedItem.ToString();
dataReader = cmdNoBinds1.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
cmdNoBinds2.rumandText = "select email, phone_number from employees where first_name = "" + lblFirstName.Text + "" and last_name = "" + lblLastName.Text +""";
dataReader = cmdNoBinds2.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
}
}
}</source>
Rollback for Oracle database
<source lang="csharp">using System; using System.Data; using System.Data.OracleClient;
class Class1{
static void Main(string[] args)
{
string connStr = "User Id=oranetuser; Password=demo; Data Source=oranet";
OracleConnection oraConn = new OracleConnection(connStr);
oraConn.Open();
testRollback(oraConn);
oraConn.Close();
oraConn.Dispose();
}
private static void testRollback(OracleConnection con)
{
string sqlUpdate = "update trans_test ";
sqlUpdate += "set balance = balance + :amount ";
sqlUpdate += "where acct_id = :acct_id";
string sqlSelect = "select acct_id, balance ";
sqlSelect += "from trans_test ";
sqlSelect += "where acct_id = :acct_id";
OracleParameter amount = new OracleParameter();
amount.DbType = DbType.Decimal;
amount.Precision = 12;
amount.Scale = 2;
amount.Value = 500;
amount.ParameterName = "amount";
OracleParameter acct_id = new OracleParameter();
acct_id.DbType = DbType.Decimal;
acct_id.Precision = 2;
acct_id.Value = 2;
acct_id.ParameterName = "acct_id";
OracleCommand cmdUpdate = new OracleCommand();
cmdUpdate.Connection = con;
cmdUpdate.rumandText = sqlUpdate;
cmdUpdate.Parameters.Add(amount);
cmdUpdate.Parameters.Add(acct_id);
OracleTransaction trans = con.BeginTransaction();
cmdUpdate.Transaction = trans;
cmdUpdate.ExecuteNonQuery();
OracleCommand cmdSelect = new OracleCommand();
cmdSelect.Connection = con;
cmdSelect.rumandText = sqlSelect;
cmdSelect.Transaction = trans;
OracleParameter acct_id2 = new OracleParameter();
acct_id2.DbType = DbType.Decimal;
acct_id2.Precision = 2;
acct_id2.Value = 2;
acct_id2.ParameterName = "acct_id";
cmdSelect.Parameters.Add(acct_id2);
OracleDataReader reader = cmdSelect.ExecuteReader();
if (reader.Read())
{
Console.WriteLine(reader.GetDecimal(0).ToString());
Console.WriteLine(reader.GetDecimal(1).ToString());
}
trans.Rollback();
reader.Close();
reader = cmdSelect.ExecuteReader();
if (reader.Read())
{
Console.WriteLine(reader.GetDecimal(0).ToString());
Console.WriteLine(reader.GetDecimal(1).ToString());
}
}
}</source>
Runs the CustomerAdd stored procedure.
<source lang="csharp">using System; using System.Data; using System.Data.SqlClient; public class AddCustomer {
public static void Main()
{
string connectionString = "Data Source=localhost;Initial Catalog=store;Integrated Security=SSPI";
string procedure = "CustomerAdd";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(procedure, con);
cmd.rumandType = CommandType.StoredProcedure;
SqlParameter param;
param = cmd.Parameters.Add("@FullName", SqlDbType.NVarChar, 50);
param.Value = "AAA";
param = cmd.Parameters.Add("@Email", SqlDbType.NVarChar, 50);
param.Value = "j@my.ru";
param = cmd.Parameters.Add("@Password", SqlDbType.NVarChar, 50);
param.Value = "password";
param = cmd.Parameters.Add("@CustomerID", SqlDbType.Int);
param.Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Console.WriteLine(param.Value);
}
}</source>
Use OracleConnection to connect to Oracle database
<source lang="csharp">using System; using System.Data.OracleClient;
class Class1
{
[STAThread]
static void Main(string[] args)
{
String connString = "User Id=oranetuser;Password=demo;Data Source=oranet";
OracleConnection oraConnection = new OracleConnection(connString);
oraConnection.Open();
Console.WriteLine("Connection String: ");
Console.WriteLine(oraConnection.ConnectionString.ToString() + "\n");
Console.WriteLine("Current Connection State: ");
Console.WriteLine(oraConnection.State.ToString() + "\n");
Console.WriteLine("Oracle Database Server Version: ");
Console.WriteLine(oraConnection.ServerVersion.ToString());
}
}</source>
Using OracleCommandBuilder
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class Form1 : System.Windows.Forms.Form
{
public Form1()
{
this.btnConnect = new System.Windows.Forms.Button();
this.btnClear = new System.Windows.Forms.Button();
this.btnLoad = new System.Windows.Forms.Button();
this.dgPlayerTable = new System.Windows.Forms.DataGrid();
this.btnUpdate = new System.Windows.Forms.Button();
this.btnBind = new System.Windows.Forms.Button();
((System.ruponentModel.ISupportInitialize)(this.dgPlayerTable)).BeginInit();
this.SuspendLayout();
this.btnConnect.Location = new System.Drawing.Point(16, 12);
this.btnConnect.Text = "C&onnect";
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
this.btnClear.Location = new System.Drawing.Point(16, 84);
this.btnClear.Text = "&Clear Grid";
this.btnClear.Click += new System.EventHandler(this.btnClear_Click);
this.btnLoad.Location = new System.Drawing.Point(16, 120);
this.btnLoad.Text = "&Load Grid";
this.btnLoad.Click += new System.EventHandler(this.btnLoad_Click);
this.dgPlayerTable.DataMember = "";
this.dgPlayerTable.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dgPlayerTable.Location = new System.Drawing.Point(108, 12);
this.dgPlayerTable.Size = new System.Drawing.Size(476, 244);
this.btnUpdate.Location = new System.Drawing.Point(16, 156);
this.btnUpdate.Text = "U&pdate";
this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
this.btnBind.Location = new System.Drawing.Point(16, 48);
this.btnBind.Text = "&Bind";
this.btnBind.Click += new System.EventHandler(this.btnBind_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(594, 266);
this.Controls.Add(this.btnBind);
this.Controls.Add(this.btnUpdate);
this.Controls.Add(this.dgPlayerTable);
this.Controls.Add(this.btnLoad);
this.Controls.Add(this.btnClear);
this.Controls.Add(this.btnConnect);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "OracleDataAdapter Sample";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ruponentModel.ISupportInitialize)(this.dgPlayerTable)).EndInit();
this.ResumeLayout(false);
}
static void Main()
{
Application.Run(new Form1());
}
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.Button btnClear;
private System.Windows.Forms.Button btnLoad;
private System.Windows.Forms.DataGrid dgPlayerTable;
private System.Windows.Forms.Button btnUpdate;
private System.Windows.Forms.Button btnBind;
public OracleConnection oraConn;
public OracleDataAdapter oraAdapter;
public OracleCommandBuilder oraBuilder;
public DataSet dsPlayerTable;
private void btnConnect_Click(object sender, System.EventArgs e)
{
string connString = "User Id=oranetuser; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.ConnectionString = connString;
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
oraConn = new OracleConnection();
}
private void btnBind_Click(object sender, System.EventArgs e)
{
if (oraConn.State == ConnectionState.Open)
{
string strSelect = "select player_num, last_name, first_name, from PlayerTable order by player_num";
oraAdapter = new OracleDataAdapter(strSelect, oraConn);
oraBuilder = new OracleCommandBuilder(oraAdapter);
dsPlayerTable = new DataSet("dsPlayerTable");
oraAdapter.Fill(dsPlayerTable,"PlayerTable");
dgPlayerTable.SetDataBinding(dsPlayerTable,"PlayerTable");
btnBind.Enabled = false;
}
}
private void btnClear_Click(object sender, System.EventArgs e)
{
dsPlayerTable.Clear();
dgPlayerTable.SetDataBinding(null,null);
}
private void btnLoad_Click(object sender, System.EventArgs e)
{
btnClear_Click(sender, e);
oraAdapter.Fill(dsPlayerTable,"PlayerTable");
dgPlayerTable.SetDataBinding(dsPlayerTable,"PlayerTable");
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
oraAdapter.Update(dsPlayerTable,"PlayerTable");
}
}</source>
Using OracleCommand to do query
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.Button btnRetrieve;
private System.Windows.Forms.ListBox listJobs;
public Form1()
{
this.btnConnect = new System.Windows.Forms.Button();
this.btnRetrieve = new System.Windows.Forms.Button();
this.listJobs = new System.Windows.Forms.ListBox();
this.SuspendLayout();
this.btnConnect.Location = new System.Drawing.Point(12, 28);
this.btnConnect.Text = "C&onnect";
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
this.btnRetrieve.Location = new System.Drawing.Point(12, 64);
this.btnRetrieve.Text = "&Retrieve";
this.btnRetrieve.Click += new System.EventHandler(this.btnRetrieve_Click);
this.listJobs.Font = new System.Drawing.Font("Courier New", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((System.Byte)(0)));
this.listJobs.ItemHeight = 15;
this.listJobs.Location = new System.Drawing.Point(100, 28);
this.listJobs.Size = new System.Drawing.Size(524, 244);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(634, 284);
this.Controls.Add(this.listJobs);
this.Controls.Add(this.btnRetrieve);
this.Controls.Add(this.btnConnect);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
this.MaximizeBox = false;
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "TableDirect Sample";
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
}
static void Main()
{
Application.Run(new Form1());
}
private void btnConnect_Click(object sender, System.EventArgs e)
{
string connString = "User Id=hr; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.ConnectionString = connString;
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
}
private OracleConnection oraConn;
private void Form1_Load(object sender, System.EventArgs e)
{
oraConn = new OracleConnection();
}
private void btnRetrieve_Click(object sender, System.EventArgs e)
{
OracleCommand cmdEmployees = new OracleCommand();
cmdEmployees.Connection = oraConn;
cmdEmployees.rumandType = CommandType.Text;
cmdEmployees.rumandText = "SELECT * FROM JOBS";
string headText = "Job".PadRight(12);
headText += "Title".PadRight(37);
headText += "Min Salary".PadRight(12);
headText += "Max Salary".PadRight(12);
string headSep = "----";
if (oraConn.State == ConnectionState.Open)
{
try
{
OracleDataReader dataReader = cmdEmployees.ExecuteReader();
listJobs.Items.Add(headText);
listJobs.Items.Add(headSep);
string textLine = "";
while (dataReader.Read())
{
textLine = dataReader.GetString(0);
textLine += dataReader.GetString(1);
textLine += dataReader.GetDecimal(2).ToString();
textLine += dataReader.GetDecimal(3).ToString();
listJobs.Items.Add(textLine);
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
cmdEmployees.Dispose();
}
}</source>
Using OracleCommand to do the query sql
<source lang="csharp">using System; using System.Data; using System.Data.OracleClient; class Class1{
static void Main(string[] args)
{
string connstr = "User Id=scott; Password=tiger; Data Source=oranet";
OracleConnection con = new OracleConnection(connstr);
con.Open();
string sql = "select empno, ename from emp";
OracleCommand cmd = new OracleCommand(sql, con);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
Console.Write(dr[0].ToString() + "\t");
Console.Write(dr[1].ToString() + "\t");
Console.Write(dr[2].ToString() + "\t");
Console.WriteLine(dr[3].ToString());
}
// close and dispose the objects
dr.Close();
dr.Dispose();
cmd.Dispose();
con.Close();
con.Dispose();
}
}</source>
Using OracleDataAdapter
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class Form1 : System.Windows.Forms.Form
{
public Form1()
{
this.btnConnect = new System.Windows.Forms.Button();
this.btnClear = new System.Windows.Forms.Button();
this.btnLoad = new System.Windows.Forms.Button();
this.dgPlayerTable = new System.Windows.Forms.DataGrid();
this.btnUpdate = new System.Windows.Forms.Button();
this.btnBind = new System.Windows.Forms.Button();
((System.ruponentModel.ISupportInitialize)(this.dgPlayerTable)).BeginInit();
this.SuspendLayout();
this.btnConnect.Location = new System.Drawing.Point(16, 12);
this.btnConnect.Text = "C&onnect";
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
this.btnClear.Location = new System.Drawing.Point(16, 84);
this.btnClear.Text = "&Clear Grid";
this.btnClear.Click += new System.EventHandler(this.btnClear_Click);
this.btnLoad.Location = new System.Drawing.Point(16, 120);
this.btnLoad.Text = "&Load Grid";
this.btnLoad.Click += new System.EventHandler(this.btnLoad_Click);
this.dgPlayerTable.DataMember = "";
this.dgPlayerTable.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dgPlayerTable.Location = new System.Drawing.Point(108, 12);
this.dgPlayerTable.Size = new System.Drawing.Size(476, 244);
this.btnUpdate.Location = new System.Drawing.Point(16, 156);
this.btnUpdate.Text = "U&pdate";
this.btnUpdate.Click += new System.EventHandler(this.btnUpdate_Click);
this.btnBind.Location = new System.Drawing.Point(16, 48);
this.btnBind.Text = "&Bind";
this.btnBind.Click += new System.EventHandler(this.btnBind_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(594, 266);
this.Controls.Add(this.btnBind);
this.Controls.Add(this.btnUpdate);
this.Controls.Add(this.dgPlayerTable);
this.Controls.Add(this.btnLoad);
this.Controls.Add(this.btnClear);
this.Controls.Add(this.btnConnect);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
this.Name = "Form1";
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "OracleDataAdapter Sample";
this.Load += new System.EventHandler(this.Form1_Load);
((System.ruponentModel.ISupportInitialize)(this.dgPlayerTable)).EndInit();
this.ResumeLayout(false);
}
static void Main()
{
Application.Run(new Form1());
}
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.Button btnClear;
private System.Windows.Forms.Button btnLoad;
private System.Windows.Forms.DataGrid dgPlayerTable;
private System.Windows.Forms.Button btnUpdate;
private System.Windows.Forms.Button btnBind;
public OracleConnection oraConn;
public OracleDataAdapter oraAdapter;
public OracleCommandBuilder oraBuilder;
public DataSet dsPlayerTable;
private void btnConnect_Click(object sender, System.EventArgs e)
{
string connString = "User Id=oranetuser; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn.ConnectionString = connString;
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
oraConn = new OracleConnection();
}
private void btnBind_Click(object sender, System.EventArgs e)
{
if (oraConn.State == ConnectionState.Open)
{
string strSelect = "select player_num, last_name, first_name, from PlayerTable order by player_num";
oraAdapter = new OracleDataAdapter(strSelect, oraConn);
oraBuilder = new OracleCommandBuilder(oraAdapter);
dsPlayerTable = new DataSet("dsPlayerTable");
oraAdapter.Fill(dsPlayerTable,"PlayerTable");
dgPlayerTable.SetDataBinding(dsPlayerTable,"PlayerTable");
btnBind.Enabled = false;
}
}
private void btnClear_Click(object sender, System.EventArgs e)
{
dsPlayerTable.Clear();
dgPlayerTable.SetDataBinding(null,null);
}
private void btnLoad_Click(object sender, System.EventArgs e)
{
btnClear_Click(sender, e);
oraAdapter.Fill(dsPlayerTable,"PlayerTable");
dgPlayerTable.SetDataBinding(dsPlayerTable,"PlayerTable");
}
private void btnUpdate_Click(object sender, System.EventArgs e)
{
oraAdapter.Update(dsPlayerTable,"PlayerTable");
}
}</source>
Using OracleDataReader to read from Oracle database
<source lang="csharp">using System; using System.Data; using System.Data.OracleClient;
class Class1
{
static void Main(string[] args)
{
string connString = "User Id=" + args[0].ToString() + ";";
connString += "Password=" + args[1].ToString() + ";";
connString += "Data Source=" + args[2].ToString();
OracleConnection oraConn = new OracleConnection();
oraConn.ConnectionString = connString;
string sqlStatement = "select * from " + args[3].ToString();
int fieldCount = 0;
int i = 0;
oraConn.Open();
if (oraConn.State == ConnectionState.Open)
{
OracleCommand cmdSQL = new OracleCommand(sqlStatement,oraConn);
OracleDataReader dataReader = cmdSQL.ExecuteReader();
fieldCount = dataReader.FieldCount;
for (i = 0; i < fieldCount; i++)
{
Console.Write(dataReader.GetName(i));
if (i < fieldCount - 1)
{
Console.Write(",");
}
}
Console.WriteLine();
while (dataReader.Read())
{
for (i = 0; i < fieldCount; i++)
{
if (!dataReader.IsDBNull(i))
{
Console.Write(dataReader[i].ToString());
}
else
{
Console.Write("(null)");
}
if (i < fieldCount - 1)
{
Console.Write(",");
}
}
}
}
if (oraConn.State == ConnectionState.Open)
{
oraConn.Close();
}
oraConn.Dispose();
}
}</source>
Using OracleParameter
<source lang="csharp">using System; using System.Drawing; using System.Collections; using System.ruponentModel; using System.Windows.Forms; using System.Data; using System.Data.OracleClient;
public class Form1 : System.Windows.Forms.Form
{
private System.Windows.Forms.Button btnConnect;
private System.Windows.Forms.Button btnGetIDs;
private System.Windows.Forms.Label label1;
private System.Windows.Forms.ruboBox cbEmpIds;
private System.Windows.Forms.Label lblFirstName;
private System.Windows.Forms.Label lblLastName;
private System.Windows.Forms.Label label4;
private System.Windows.Forms.Label label5;
private System.Windows.Forms.Button btnLookup1;
private System.Windows.Forms.Button btnLookup2;
private System.Windows.Forms.Label lblEmail;
private System.Windows.Forms.Label lblPhone;
private System.Windows.Forms.Label lblEmailText;
private System.Windows.Forms.Label lblPhoneText;
private System.Windows.Forms.Button btnReset;
private System.Windows.Forms.Button btnNoBinds;
private System.ruponentModel.Container components = null;
public Form1()
{
this.btnConnect = new System.Windows.Forms.Button();
this.btnGetIDs = new System.Windows.Forms.Button();
this.cbEmpIds = new System.Windows.Forms.ruboBox();
this.label1 = new System.Windows.Forms.Label();
this.lblFirstName = new System.Windows.Forms.Label();
this.lblLastName = new System.Windows.Forms.Label();
this.label4 = new System.Windows.Forms.Label();
this.label5 = new System.Windows.Forms.Label();
this.btnLookup1 = new System.Windows.Forms.Button();
this.btnLookup2 = new System.Windows.Forms.Button();
this.lblEmail = new System.Windows.Forms.Label();
this.lblPhone = new System.Windows.Forms.Label();
this.lblEmailText = new System.Windows.Forms.Label();
this.lblPhoneText = new System.Windows.Forms.Label();
this.btnReset = new System.Windows.Forms.Button();
this.btnNoBinds = new System.Windows.Forms.Button();
this.SuspendLayout();
this.btnConnect.Location = new System.Drawing.Point(32, 44);
this.btnConnect.Text = "C&onnect";
this.btnConnect.Click += new System.EventHandler(this.btnConnect_Click);
this.btnGetIDs.Location = new System.Drawing.Point(32, 80);
this.btnGetIDs.Text = "&Get IDs";
this.btnGetIDs.Click += new System.EventHandler(this.btnGetIDs_Click);
this.cbEmpIds.DropDownStyle = System.Windows.Forms.ruboBoxStyle.DropDownList;
this.cbEmpIds.Location = new System.Drawing.Point(144, 44);
this.cbEmpIds.Size = new System.Drawing.Size(68, 21);
this.label1.Location = new System.Drawing.Point(144, 24);
this.label1.Size = new System.Drawing.Size(72, 16);
this.label1.Text = "&Employee ID:";
this.lblFirstName.Location = new System.Drawing.Point(228, 48);
this.lblFirstName.Size = new System.Drawing.Size(116, 16);
this.lblLastName.Location = new System.Drawing.Point(364, 48);
this.lblLastName.Size = new System.Drawing.Size(128, 16);
this.label4.Location = new System.Drawing.Point(228, 24);
this.label4.Size = new System.Drawing.Size(100, 16);
this.label4.Text = "&First Name:";
this.label5.Location = new System.Drawing.Point(364, 24);
this.label5.Size = new System.Drawing.Size(100, 16);
this.label5.Text = "&Last Name:";
this.btnLookup1.Location = new System.Drawing.Point(32, 116);
this.btnLookup1.Text = "Lookup &1";
this.btnLookup1.Click += new System.EventHandler(this.btnLookup1_Click);
this.btnLookup2.Location = new System.Drawing.Point(32, 152);
this.btnLookup2.Text = "Lookup &2";
this.btnLookup2.Click += new System.EventHandler(this.btnLookup2_Click);
this.lblEmail.Location = new System.Drawing.Point(228, 88);
this.lblEmail.Size = new System.Drawing.Size(100, 16);
this.lblEmail.Text = "E&mail:";
this.lblPhone.Location = new System.Drawing.Point(364, 88);
this.lblPhone.Size = new System.Drawing.Size(100, 16);
this.lblPhone.Text = "Phone &Number:";
this.lblEmailText.Location = new System.Drawing.Point(228, 112);
this.lblEmailText.Size = new System.Drawing.Size(116, 16);
this.lblPhoneText.Location = new System.Drawing.Point(364, 112);
this.lblPhoneText.Size = new System.Drawing.Size(128, 16);
this.btnReset.Location = new System.Drawing.Point(32, 224);
this.btnReset.Text = "&Reset";
this.btnReset.Click += new System.EventHandler(this.btnReset_Click);
this.btnNoBinds.Location = new System.Drawing.Point(32, 188);
this.btnNoBinds.Text = "No &Binds";
this.btnNoBinds.Click += new System.EventHandler(this.btnNoBinds_Click);
this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
this.ClientSize = new System.Drawing.Size(534, 264);
this.Controls.Add(this.btnNoBinds);
this.Controls.Add(this.btnReset);
this.Controls.Add(this.lblPhoneText);
this.Controls.Add(this.lblEmailText);
this.Controls.Add(this.lblPhone);
this.Controls.Add(this.lblEmail);
this.Controls.Add(this.btnLookup2);
this.Controls.Add(this.btnLookup1);
this.Controls.Add(this.label5);
this.Controls.Add(this.label4);
this.Controls.Add(this.lblLastName);
this.Controls.Add(this.lblFirstName);
this.Controls.Add(this.label1);
this.Controls.Add(this.cbEmpIds);
this.Controls.Add(this.btnGetIDs);
this.Controls.Add(this.btnConnect);
this.FormBorderStyle = System.Windows.Forms.FormBorderStyle.Fixed3D;
this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
this.Text = "Oracle Parameter Sample";
this.Load += new System.EventHandler(this.Form1_Load);
this.ResumeLayout(false);
}
static void Main()
{
Application.Run(new Form1());
}
private OracleConnection oraConn;
private void btnConnect_Click(object sender, System.EventArgs e)
{
string connString = "User Id=hr; Password=demo; Data Source=oranet";
if (oraConn.State != ConnectionState.Open)
{
try
{
oraConn = new OracleConnection(connString);
oraConn.Open();
MessageBox.Show(oraConn.ConnectionString, "Successful Connection");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
}
}
private void btnGetIDs_Click(object sender, System.EventArgs e)
{
OracleCommand cmdEmpId = new OracleCommand();
cmdEmpId.rumandText = "select employee_id from employees order by employee_id";
cmdEmpId.Connection = oraConn;
try
{
OracleDataReader dataReader = cmdEmpId.ExecuteReader();
while (dataReader.Read())
{
cbEmpIds.Items.Add(dataReader.GetDecimal(0));
}
dataReader.Dispose();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Exception Caught");
}
finally
{
cmdEmpId.Dispose();
}
}
private void Form1_Load(object sender, System.EventArgs e)
{
oraConn = new OracleConnection();
}
private void btnLookup1_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
OracleCommand cmdEmpName = new OracleCommand();
cmdEmpName.rumandText = "select first_name, last_name from employees where employee_id = :p_id";
cmdEmpName.Connection = oraConn;
OracleParameter p_id = new OracleParameter();
p_id.DbType = DbType.Decimal;
p_id.Value = Convert.ToDecimal(selectedItem.ToString());
p_id.ParameterName = "p_id";
cmdEmpName.Parameters.Add(p_id);
OracleDataReader dataReader = cmdEmpName.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
cmdEmpName.Dispose();
}
}
private void btnLookup2_Click(object sender, System.EventArgs e)
{
OracleCommand cmdEmpInfo = new OracleCommand();
cmdEmpInfo.rumandText = "select email, phone_number from employees where first_name = :p_first and last_name = :p_last";
cmdEmpInfo.Connection = oraConn;
OracleParameter p1 = new OracleParameter();
OracleParameter p2 = new OracleParameter();
p1.ParameterName = "p_first";
p2.ParameterName = "p_last";
p1.Value = lblFirstName.Text;
p2.Value = lblLastName.Text;
cmdEmpInfo.Parameters.Add(p2);
cmdEmpInfo.Parameters.Add(p1);
OracleDataReader dataReader = cmdEmpInfo.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
cmdEmpInfo.Dispose();
}
private void btnReset_Click(object sender, System.EventArgs e)
{
cbEmpIds.SelectedIndex = -1;
lblFirstName.Text = "";
lblLastName.Text = "";
lblEmailText.Text = "";
lblPhoneText.Text = "";
}
private void btnNoBinds_Click(object sender, System.EventArgs e)
{
object selectedItem = cbEmpIds.SelectedItem;
if (selectedItem != null)
{
OracleCommand cmdNoBinds = new OracleCommand();
cmdNoBinds.Connection = oraConn;
OracleDataReader dataReader;
cmdNoBinds.rumandText = "select first_name, last_name from employees where employee_id = " + selectedItem.ToString();
dataReader = cmdNoBinds.ExecuteReader();
if (dataReader.Read())
{
lblFirstName.Text = dataReader.GetString(0);
lblLastName.Text = dataReader.GetString(1);
}
dataReader.Close();
cmdNoBinds.rumandText = "select email, phone_number from employees where first_name = "" + lblFirstName.Text + "" and last_name = "" + lblLastName.Text +""";
dataReader = cmdNoBinds.ExecuteReader();
if (dataReader.Read())
{
lblEmailText.Text = dataReader.GetString(0);
lblPhoneText.Text = dataReader.GetString(1);
}
dataReader.Close();
dataReader.Dispose();
cmdNoBinds.Dispose();
}
}
}</source>
