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>