1.如下
using Oracle.ManagedDataAccess.Client; using System; using System.Collections.Generic; using System.Data; using System.Linq; using System.Text; using System.Threading.Tasks; namespace DataMove { public class DBSupport :IDisposable { //public static string conncet = System.Configuration.ConfigurationManager.AppSettings["conn"].ToString(); string conn; public DBSupport(string connect) { conn = connect; if (mQracleConnecting == null) mQracleConnecting = new OracleConnection(connect); if (mQracleConnecting.State != ConnectionState.Open) mQracleConnecting.Open(); } #region DB OracleConnection mQracleConnecting = null; public OracleConnection QracleConnecting { get { return mQracleConnecting; } } public DataTable DBGetDataTable(string sql) { try { DataTable dataSet = new DataTable(); OracleDataAdapter OraDA = new OracleDataAdapter(sql, mQracleConnecting); OraDA.Fill(dataSet); return dataSet; } catch (Exception) { FileSupport.Instance.Write("数据库连接异常" + conn); return null; } } // 执行SQL语句,返回所影响的行数 public int ExecuteSQL(string sql) { int Cmd = 0; OracleCommand command = new OracleCommand(sql, QracleConnecting); try { Cmd = command.ExecuteNonQuery(); } catch(Exception ex) { FileSupport.Instance.Write(ex.ToString()); } return Cmd; } //public int ExecuteSQL_Update(string sql) //{ // OracleDataAdapter OraDA = new OracleDataAdapter(sql, QracleConnecting); // return OraDA.UpdateCommand.ExecuteNonQuery(); //} //public int ExecuteSQL_Insert(string sql) //{ // OracleDataAdapter OraDA = new OracleDataAdapter(sql, QracleConnecting); // return OraDA.InsertCommand.ExecuteNonQuery(); //} public int ExecuteSQL_Insert(object item) { var type = item.GetType(); var tablename = type.Name; var atts = type.GetProperties(); var keys=""; var values =""; foreach(var a in atts) { var aname = a.Name; var value = a.GetValue(item); keys += aname+","; values += GetValue2String(value) + ","; } keys=keys.Trim(','); values = values.Trim(','); var sql = "INSERT INTO "+tablename+" ( " + keys + " ) VALUES ( " + values +" )"; return ExecuteSQL(sql); } #endregion /// <summary> /// 获取对象列表 /// </summary> /// <typeparam name="T">表所对应的对象名称</typeparam> /// <param name="sql">查询语句</param> /// <returns>返回获取到的对象实例列表</returns> public List<T> QueryObjectList<T>(string sql) where T : new() { var dataset = ReturnDataSet(sql, "table"); if (dataset != null) { var table = dataset.Tables[0]; return ConvertTableToObject<T>(table); } return null; } public DataSet ReturnDataSet(string sql, string DataSetName) { DataSet dataSet = new DataSet(); OracleDataAdapter OraDA = new OracleDataAdapter(sql, QracleConnecting); OraDA.Fill(dataSet, DataSetName); return dataSet; } public List<T> ConvertTableToObject<T>(DataTable t) where T : new() { if (t == null) return null; List<T> list = new List<T>(); foreach (DataRow row in t.Rows) { T obj = new T(); GetObject(t.Columns, row, obj); if (obj != null && obj is T) list.Add(obj); } return list; } public T ConvertToObject<T>(DataRow row) where T : new() { object obj = new T(); if (row != null) { DataTable t = row.Table; GetObject(t.Columns, row, obj); } if (obj != null && obj is T) return (T)obj; else return default(T); } void GetObject(DataColumnCollection cols, DataRow dr, Object obj) { Type t = obj.GetType(); var props = t.GetProperties(); foreach (var pro in props) { if (cols.Contains(pro.Name)) { if (dr[pro.Name] != DBNull.Value) { try { switch (pro.PropertyType.Name) { case "Int32": { Int32 value = Convert.ToInt32(dr[pro.Name]); pro.SetValue(obj, value, null); } break; case "Double": { double value = Convert.ToDouble(dr[pro.Name]); pro.SetValue(obj, value, null); } break; case "Single": { float value = Convert.ToSingle(dr[pro.Name]); pro.SetValue(obj, value, null); } break; case "Int64": { Int64 value = Convert.ToInt64(dr[pro.Name]); pro.SetValue(obj, value, null); } break; case "Int16": { Int16 value = Convert.ToInt16(dr[pro.Name]); pro.SetValue(obj, value, null); } break; case "Decimal": { Decimal value = Convert.ToDecimal(dr[pro.Name]); pro.SetValue(obj, value, null); } break; default: { pro.SetValue(obj, dr[pro.Name], null); } break; } } catch { pro.SetValue(obj, null, null); } finally { } } else { pro.SetValue(obj, null, null); } } } } string GetValue2String(Object obj) { if (obj == null) return "null"; Type t = obj.GetType(); try { switch (t.Name) { case "String": { return "'"+ obj.ToString()+"'"; } case "DateTime": { return "to_date('" + obj.ToString() + "','YYYY-MM-DD hh24:mi:ss')"; } default: { return obj.ToString(); } } } catch { return ""; } } public void Dispose() { if(mQracleConnecting.State == ConnectionState.Open) mQracleConnecting.Close(); } } }
转载于:https://www.cnblogs.com/leolzi/p/7910222.html
相关资源：对Oracle数据库操作进行了封装