1. Download and install ODT for VS2015_121025 to restart VS on Oralce's official website
2. Project reference Oracle.ManagedDataAccess.dll
3. Database connection string: user id = xxxx; password = xxxx; data source = 192.168.0. xxx: 1521/orcl (database name)
4. Use method:
OracleConnection con = new OracleConnection();
con.ConnectionString = "user id=his3;password=his3;data source=192.168.1.237/ORCL";
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.CommandType = System.Data.CommandType.Text;
cmd.CommandText = "select * from gy_yonghuxx";
cmd.Connection = con;
OracleDataAdapter da = new OracleDataAdapter();
da.SelectCommand = cmd;
DataSet ds = new DataSet();
da.Fill(ds);
con.Close();
da.Dispose();
cmd.Dispose();
con.Dispose();Be careful:
OleDbParameter is not as flexible as SqlParameter when it is used as a parameter. When SqlParameter is used, the order of definition and assignment can be freely written, while OledbParmeter requires strict order. Otherwise, although no error will occur, there may be unexpected situations such as no execution or execution error.
Tools:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.ManagedDataAccess.Client;
using System.Configuration;
using System.Data;
using System.Reflection;
namespace NCToOA
{
public class OracleHelper
{
#region variable
/// <summary>
/// Database Connection Objects
/// </summary>
private static OracleConnection _con = null;
public static string constr = ConfigurationManager.ConnectionStrings["OracleStr"].ToString();
#endregion
#region attribute
/// <summary>
/// Get or set database connection objects
/// </summary>
public static OracleConnection Con
{
get
{
if (OracleHelper._con == null)
{
OracleHelper._con = new OracleConnection();
}
if (OracleHelper._con.ConnectionString == "")
{
OracleHelper._con.ConnectionString = OracleHelper.constr;
}
return OracleHelper._con;
}
set
{
OracleHelper._con = value;
}
}
#endregion
#region Method
#region Perform database operations that return rows and columns
/// <summary>
/// Perform database operations that return rows and columns
/// </summary>
/// <param name="commandText">Oracle Statement or stored procedure name</param>
/// <param name="commandType">Oracle Command type</param>
/// <param name="param">Oracle Command parameter array</param>
/// <returns>Records in the first row and column</returns>
public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param)
{
int count = 0;
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
//try
//{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
OracleHelper.Con.Open();
count = Convert.ToInt32(cmd.ExecuteScalar());
//}
//catch (Exception ex)
//{
// count = 0;
//}
}
}
return count;
}
#endregion
#region Perform non-query database operations
/// <summary>
/// Perform non-query database operations
/// </summary>
/// <param name="commandText">Oracle Statement or stored procedure name</param>
/// <param name="commandType">Oracle Command type</param>
/// <param name="param">Oracle Command parameter array</param>
/// <returns>Number of rows affected</returns>
public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param)
{
int result = 0;
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
try
{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
OracleHelper.Con.Open();
result = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
result = 0;
}
}
}
return result;
}
#endregion
#region Execute generic objects that return a record
/// <summary>
/// Execute generic objects that return a record
/// </summary>
/// <typeparam name="T">generic types</typeparam>
/// <param name="reader">Entry-only Read-Only Objects</param>
/// <returns>Generic object</returns>
private static T ExecuteDataReader<T>(IDataReader reader)
{
T obj = default(T);
try
{
Type type = typeof(T);
obj = (T)Activator.CreateInstance(type);//Create a specified type of object by reflection from the current assembly
//Obj = (T) Assembly. Load (OracleHelper. _assemblyName). CreateInstance (OracleHelper. _assemblyName + "."+ type. Name); // Create a specified type of object by reflection from another assembly
PropertyInfo[] propertyInfos = type.GetProperties();//Gets all attributes in the specified type
foreach (PropertyInfo propertyInfo in propertyInfos)
{
for (int i = 0; i < reader.FieldCount; i++)
{
string fieldName = reader.GetName(i);
if (fieldName.ToLower() == propertyInfo.Name.ToLower())
{
object val = reader[propertyInfo.Name];//Read a column in a record in a table
if (val != null && val != DBNull.Value)
{
if (val.GetType() == typeof(decimal) || val.GetType() == typeof(int))
{
propertyInfo.SetValue(obj, Convert.ToInt32(val), null);
}
else if (val.GetType() == typeof(DateTime))
{
propertyInfo.SetValue(obj, Convert.ToDateTime(val), null);
}
else if (val.GetType() == typeof(string))
{
propertyInfo.SetValue(obj, Convert.ToString(val), null);
}
}
break;
}
}
}
}
catch (Exception ex)
{
throw;
}
return obj;
}
#endregion
#region Execute generic objects that return a record
/// <summary>
/// Execute generic objects that return a record
/// </summary>
/// <typeparam name="T">generic types</typeparam>
/// <param name="commandText">Oracle Statement or stored procedure name</param>
/// <param name="commandType">Oracle Command type</param>
/// <param name="param">Oracle Command parameter array</param>
/// <returns>Entity object</returns>
public static T ExecuteEntity<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
T obj = default(T);
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
cmd.CommandType = commandType;
cmd.Parameters.AddRange(param);
OracleHelper.Con.Open();
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
obj = OracleHelper.ExecuteDataReader<T>(reader);
}
}
}
return obj;
}
#endregion
#region Execute generic collection objects that return multiple records
/// <summary>
/// Execute generic collection objects that return multiple records
/// </summary>
/// <typeparam name="T">generic types</typeparam>
/// <param name="commandText">Oracle Statement or stored procedure name</param>
/// <param name="commandType">Oracle Command type</param>
/// <param name="param">Oracle Command parameter array</param>
/// <returns>Generic Collection Objects</returns>
public static List<T> ExecuteList<T>(string commandText, CommandType commandType, params OracleParameter[] param)
{
List<T> list = new List<T>();
using (OracleHelper.Con)
{
using (OracleCommand cmd = new OracleCommand(commandText, OracleHelper.Con))
{
try
{
cmd.CommandType = commandType;
if (param != null)
{
cmd.Parameters.AddRange(param);
}
OracleHelper.Con.Open();
OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
while (reader.Read())
{
T obj = OracleHelper.ExecuteDataReader<T>(reader);
list.Add(obj);
}
}
catch (Exception ex)
{
list = null;
}
}
}
return list;
}
#endregion
#endregion
}
}