C# connecting to Oracle database
Petr Faltus development
OracleDBclient.cs
GitHub repository: oracle-database-connection-source-codes
git clone https://github.com/petrfaltus/oracle-database-connection-source-codes.git
Directories to OracleDBclient.cs in the repository: csharp
/* 1) Install 'Oracle Data Provider for .NET' 2) Check the right Oracle.ManagedDataAccess.dll from Oracle Data Provider for .NET in the bin subdirectory */ using System; using System.Data; using Oracle.ManagedDataAccess.Client; public class OracleDBclient { private const string db_host = "localhost"; private const int db_port = 1521; private const string db_srvname = "ORCLCDB.localdomain"; private const string db_username = "testuser"; private const string db_password = "T3stUs3r!"; private const string db_table = "cars"; private const string db_update_column = "remark"; private const string db_update_column_variable = "updatevar"; private const string db_column = "id"; private const string db_column_variable = "var"; private const int db_column_value = 1; private const string db_factorial_variable = "n"; private const int db_factorial_value = 4; private const string db_add_and_subtract_a_variable = "a"; private const int db_add_and_subtract_a_value = 12; private const string db_add_and_subtract_b_variable = "b"; private const int db_add_and_subtract_b_value = 5; private const string db_add_and_subtract_x_variable = "x"; private const string db_add_and_subtract_y_variable = "y"; private static string GetNow() { DateTime dateTimeNow = DateTime.Now; return dateTimeNow.ToString(); } public static void Main(string[] args) { // Build the connection string OracleConnectionStringBuilder connBuilder = new OracleConnectionStringBuilder(); connBuilder.DataSource = String.Format("{0}:{1}/{2}", db_host, db_port, db_srvname); connBuilder.UserID = db_username; connBuilder.Password = db_password; try { using (OracleConnection conn = new OracleConnection(connBuilder.ConnectionString)) { // Connect the database conn.Open(); Console.WriteLine("Data source: {0}", conn.DataSource); Console.WriteLine("Server version: {0}", conn.ServerVersion); Console.WriteLine("Connection timeout: {0}", conn.ConnectionTimeout); Console.WriteLine("State: {0}", conn.State); Console.WriteLine(); // UPDATE statement string new_comment = "C# " + GetNow(); string sql0 = String.Format("update {0} set {1}=:{2} where {3}!=:{4}", db_table, db_update_column, db_update_column_variable, db_column, db_column_variable); Console.WriteLine(sql0); using (var cmd = new OracleCommand(sql0, conn)) { OracleParameter par1 = new OracleParameter(db_update_column_variable, new_comment); cmd.Parameters.Add(par1); OracleParameter par2 = new OracleParameter(db_column_variable, db_column_value); cmd.Parameters.Add(par2); int updatedRows = cmd.ExecuteNonQuery(); Console.WriteLine("Total updated rows: {0}", updatedRows); } Console.WriteLine(); // Full SELECT statement string sql1 = String.Format("select * from {0}", db_table); Console.WriteLine(sql1); using (var cmd = new OracleCommand(sql1, conn)) using (OracleDataReader reader = cmd.ExecuteReader()) { int columns = reader.FieldCount; Console.WriteLine("Total columns: {0}", columns); for (int ii = 0; ii < columns; ii++) { Console.WriteLine(" - {0} {1}", reader.GetName(ii), reader.GetDataTypeName(ii)); } int number = 0; while (reader.Read()) { number++; Console.Write(number); for (int ii = 0; ii < columns; ii++) { string type = reader.GetDataTypeName(ii); string value = "?"; if (!reader.IsDBNull(ii)) { if (type.EndsWith("Varchar2")) { value = reader.GetString(ii); } else if (type.Equals("TimeStamp")) { value = reader.GetDateTime(ii).ToString(); } else if (type.Equals("Int16")) { value = reader.GetInt16(ii).ToString(); } else if (type.Equals("Int32")) { value = reader.GetInt32(ii).ToString(); } } else { value = "(null)"; } Console.Write(" '{0}'", value); } Console.WriteLine(); } } Console.WriteLine(); // SELECT WHERE statement string sql2 = String.Format("select count(*) from {0} where {1}!=:{2}", db_table, db_column, db_column_variable); Console.WriteLine(sql2); using (var cmd = new OracleCommand(sql2, conn)) { OracleParameter par1 = new OracleParameter(db_column_variable, db_column_value); cmd.Parameters.Add(par1); Object result = cmd.ExecuteScalar(); Console.WriteLine("Result: {0}", result); } Console.WriteLine(); // SELECT package function statement string sql3 = String.Format("select calculator.factorial(:{0}) from dual", db_factorial_variable); Console.WriteLine(sql3); using (var cmd = new OracleCommand(sql3, conn)) { OracleParameter par1 = new OracleParameter(db_factorial_variable, db_factorial_value); cmd.Parameters.Add(par1); Object result = cmd.ExecuteScalar(); Console.WriteLine("Result: {0}", result); } Console.WriteLine(); // CALL package procedure statement string sql4 = String.Format("calculator.add_and_subtract"); Console.WriteLine(sql4); using (var cmd = new OracleCommand(sql4, conn)) { cmd.CommandType = CommandType.StoredProcedure; OracleParameter par1 = new OracleParameter(db_add_and_subtract_a_variable, db_add_and_subtract_a_value); cmd.Parameters.Add(par1); OracleParameter par2 = new OracleParameter(db_add_and_subtract_b_variable, db_add_and_subtract_b_value); cmd.Parameters.Add(par2); OracleParameter par3 = new OracleParameter(db_add_and_subtract_x_variable, OracleDbType.Int32, ParameterDirection.Output); cmd.Parameters.Add(par3); OracleParameter par4 = new OracleParameter(db_add_and_subtract_y_variable, OracleDbType.Int32, ParameterDirection.Output); cmd.Parameters.Add(par4); cmd.ExecuteNonQuery(); Console.WriteLine("Result x: {0}", cmd.Parameters[db_add_and_subtract_x_variable].Value); Console.WriteLine("Result y: {0}", cmd.Parameters[db_add_and_subtract_y_variable].Value); } } } catch (OracleException oex) { Console.WriteLine("Oracle database error: {0}", oex.Message); } } }
Development tools
Developer ASCII table
Characters and HTML entities in the UTF-8 table
Predefined web CSS colors
CSS px to rem converter
🤝 Your IP address is 3.17.110.208
(ec2-3-17-110-208.us-east-2.compute.amazonaws.com
)