using System;using System.Collections.Generic;using System.Data;using System.Data.OracleClient;using System.Linq;using System.Reflection;using System.Text;using System.Threading.Tasks;namespace 数据链接{ public static class SqlHelper { #region 样本 //标准连接-SSPI //private static readonly string defaultConnectString = "Data Source=hndz;Integrated Security=SSPI;"; //标准连接 private static readonly string defaultConnectString = "Data Source=Test;User Id=UserTest;Password=123456;"; //标准链接 //private static readonly string defaultConnectString = "Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));User Id=UserTest;Password=123456;"; #endregion #region 变量 private static OracleConnection _con = null; public static string _constr = "Data Source=Test;User Id=UserTest;Password=123456;";//ConfigurationManager.ConnectionStrings["orcl"].ToString(); #endregion #region 属性 public static string constr { get { if (_constr == null || _constr.Equals(String.Empty)) { _constr = defaultConnectString; } return _constr; } set { _constr = value; } } /// /// 获取或设置数据库连接对象 /// public static OracleConnection Con { get { if (SqlHelper._con == null) { SqlHelper._con = new OracleConnection(); } if (SqlHelper._con.ConnectionString == null || SqlHelper._con.ConnectionString.Equals(string.Empty)) { SqlHelper._con.ConnectionString = SqlHelper.constr; } return SqlHelper._con; } set { SqlHelper._con = value; } } #endregion #region 方法 /// /// 执行并返回第一行第一列的数据库操作 /// /// Sql语句或存储过程名 /// Sql命令类型 /// Oracle命令参数数组 /// 第一行第一列的记录 public static int ExecuteScalar(string commandText, CommandType commandType, params OracleParameter[] param) { int result = 0; try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { try { cmd.CommandType = commandType; if (param != null) { cmd.Parameters.AddRange(param); } SqlHelper.Con.Open(); string x = cmd.CommandText; result = Convert.ToInt32(cmd.ExecuteScalar()); } catch { result = -1; } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return result; } /// /// 执行不查询的数据库操作 /// /// Oracle语句或存储过程名 /// Oracle命令类型 /// Oracle命令参数数组 /// 受影响的行数 public static int ExecuteNonQuery(string commandText, CommandType commandType, params OracleParameter[] param) { int result = 0; try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { try { //cmd.CommandType = commandType; if (param != null) { cmd.Parameters.AddRange(param); } SqlHelper.Con.Open(); result = cmd.ExecuteNonQuery(); } catch (Exception ex) { result = -1; } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return result; } /// /// 获取数据表 /// /// select命令 /// 参数表 /// /// 事务模式执行多行非查询语句 /// /// sql语句 /// 参数 /// 受影响行数 public static int ExecuteNonQueryTransaction(string commandText, List param) { int result = 0; try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { SqlHelper.Con.Open(); cmd.Transaction = cmd.Connection.BeginTransaction(); try { foreach (OracleParameter[] par in param) { cmd.Parameters.Clear(); cmd.Parameters.AddRange(par); result += cmd.ExecuteNonQuery(); } cmd.Transaction.Commit(); } catch { result = -1; try { cmd.Transaction.Rollback(); } catch { result = -2; } } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return result; } /// /// 执行返回一条记录的泛型对象 /// /// 泛型类型 /// 只进只读对象 /// 泛型对象 private static T ExecuteDataReader (IDataReader reader) { T obj = default(T); try { Type type = typeof(T); obj = (T)Activator.CreateInstance(type);//从当前程序集里面通过反射的方式创建指定类型的对象 //obj = (T)Assembly.Load(SqlHelper._assemblyName).CreateInstance(SqlHelper._assemblyName + "." + type.Name);//从另一个程序集里面通过反射的方式创建指定类型的对象 PropertyInfo[] propertyInfos = type.GetProperties();//获取指定类型里面的所有属性 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];//读取表中某一条记录里面的某一列 if (val != null && val != DBNull.Value) { Type valType = val.GetType(); if (valType == typeof(float) || valType == typeof(double) || valType == typeof(decimal)) { propertyInfo.SetValue(obj, Convert.ToDouble(val), null); } else if (valType == typeof(int)) { propertyInfo.SetValue(obj, Convert.ToInt32(val), null); } else if (valType == typeof(DateTime)) { propertyInfo.SetValue(obj, Convert.ToDateTime(val), null); } else if (valType == typeof(string)) { propertyInfo.SetValue(obj, Convert.ToString(val), null); } } break; } } } } catch { throw; } return obj; } /// /// 执行返回一条记录的泛型对象 /// /// 泛型类型 /// Oracle语句或存储过程名 /// Oracle命令类型 /// Oracle命令参数数组 /// 实体对象 public static T ExecuteEntity (string commandText, CommandType commandType, params OracleParameter[] param) { T obj = default(T); try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { cmd.CommandType = commandType; cmd.Parameters.AddRange(param); SqlHelper.Con.Open(); OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { obj = SqlHelper.ExecuteDataReader (reader); } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return obj; } /// /// 执行返回多条记录的泛型集合对象 /// /// 泛型类型 /// Oracle语句或存储过程名 /// Oracle命令类型 /// Oracle命令参数数组 /// 泛型集合对象 public static List ExecuteList (string commandText, CommandType commandType, params OracleParameter[] param) { List list = new List (); try { using (OracleCommand cmd = new OracleCommand(commandText, SqlHelper.Con)) { try { cmd.CommandType = commandType; if (param != null) { cmd.Parameters.AddRange(param); } SqlHelper.Con.Open(); OracleDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (reader.Read()) { T obj = SqlHelper.ExecuteDataReader (reader); list.Add(obj); } } catch (Exception ex) { list = null; } } } finally { if (SqlHelper.Con.State != ConnectionState.Closed) { SqlHelper.Con.Close(); } } return list; } #endregion }} 调用
SqlHelper.ExecuteNonQuery("INSERT INTO Table (A1,A2,A3,A4) VALUES('911','911',null,to_date('2018/11/19 18:00:38', 'yyyy-mm-dd hh24:mi:ss'))", CommandType.Text, null);//增加 SqlHelper.ExecuteNonQuery("delete from TABLE WHERE A1='955'", CommandType.Text, null);//删除 SqlHelper.ExecuteNonQuery("update TABLE SET A1='955' where A1='911'", CommandType.Text, null);//修改 SqlHelper.GetDataTable("select * from TABLE WHERE A1=911;", null);//查询 返回Datatable INSERT INTO TABLE(A1,A4) VALUES('911',to_date('2018/11/19', 'yyyy-mm-dd hh24:mi:ss'))INSERT INTO TABLE(A1,A4) VALUES('911',to_date('2018-11-19', 'yyyy-mm-dd hh24:mi:ss'))INSERT INTO TABLE(A1,A4) VALUES('911',to_date('2018-11-19 18:00:38', 'yyyy-mm-dd hh24:mi:ss'))