public static class OracleDBHelper
{
public static OracleCommand cmd = null;
public static OracleConnection conn = null;
public static string connstr = ConfigurationManager.ConnectionStrings["DbInsured_oracle"].ConnectionString;
#region 获取Guid方法
public static string GetGuid()
{
string guid = Guid.NewGuid().ToString();
return guid;
}
#endregion
#region 建立数据库连接对象
/// <summary>
/// 建立数据库连接
/// </summary>
/// <returns>返回一个数据库的连接OracleConnection对象</returns>
public static OracleConnection init()
{
try
{
conn = new OracleConnection(connstr);
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
return conn;
}
#endregion
#region 设置OracleCommand对象
/// <summary>
/// 设置OracleCommand对象
/// </summary>
/// <param name="cmd">OracleCommand对象 </param>
/// <param name="cmdText">命令文本</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
private static void SetCommand(OracleCommand cmd, string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
cmd.Connection = conn;
cmd.CommandText = cmdText;
cmd.CommandType = cmdType;
if (cmdParms != null)
{
cmd.Parameters.AddRange(cmdParms);
}
}
#endregion
#region 执行相应的sql语句，返回相应的DataSet对象
/// <summary>
/// 执行相应的sql语句，返回相应的DataSet对象
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <returns>返回相应的DataSet对象</returns>
public static DataSet GetDataSet(string sqlstr)
{
DataSet set = new DataSet();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
adp.Fill(set);
conn.Close();
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
return set;
}
#endregion
#region 执行相应的sql语句，返回相应的DataSet对象
/// <summary>
/// 执行相应的sql语句，返回相应的DataSet对象
/// </summary>
/// <param name="sqlstr">sql语句</param>
/// <param name="tableName">表名</param>
/// <returns>返回相应的DataSet对象</returns>
public static DataSet GetDataSet(string sqlstr, string tableName)
{
DataSet set = new DataSet();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(sqlstr, conn);
adp.Fill(set, tableName);
conn.Close();
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
return set;
}
#endregion
#region 执行不带参数sql语句，返回所影响的行数
/// <summary>
/// 执行不带参数sql语句，返回所影响的行数
/// </summary>
/// <param name="cmdstr">增，删，改sql语句</param>
/// <returns>返回所影响的行数</returns>
public static int ExecuteNonQuery(string cmdText)
{
int count;
try
{
init();
cmd = new OracleCommand(cmdText, conn);
count = cmd.ExecuteNonQuery();
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return count;
}
#endregion
#region 执行带参数sql语句或存储过程，返回所影响的行数
/// <summary>
/// 执行带参数sql语句或存储过程，返回所影响的行数
/// </summary>
/// <param name="cmdText">带参数的sql语句和存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回所影响的行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
int count;
try
{
init();
cmd = new OracleCommand();
SetCommand(cmd, cmdText, cmdType, cmdParms);
count = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return count;
}
#endregion
#region 执行不带参数sql语句，返回一个从数据源读取数据的OracleDataReader对象
/// <summary>
/// 执行不带参数sql语句，返回一个从数据源读取数据的OracleDataReader对象
/// </summary>
/// <param name="cmdstr">相应的sql语句</param>
/// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
public static OracleDataReader ExecuteReader(string cmdText)
{
OracleDataReader reader;
try
{
init();
cmd = new OracleCommand(cmdText, conn);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return reader;
}
#endregion
#region 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
/// <summary>
/// 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
/// </summary>
/// <param name="cmdText">sql语句或存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
public static OracleDataReader ExecuteReader(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
OracleDataReader reader;
try
{
init();
cmd = new OracleCommand();
SetCommand(cmd, cmdText, cmdType, cmdParms);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return reader;
}
#endregion
#region 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的DataSet对象
/// <summary>
/// 执行带参数的sql语句或存储过程，返回一个从数据源读取数据的OracleDataReader对象
/// </summary>
/// <param name="cmdText">sql语句或存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">参数集合</param>
/// <returns>返回一个从数据源读取数据的OracleDataReader对象</returns>
public static DataSet ExecuteReaderByDataSet(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
DataSet set = new DataSet();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(cmdText, conn);
adp.SelectCommand.Parameters.AddRange(cmdParms);
adp.Fill(set, "personInfo");
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return set;
}
/// <summary>
/// 不带参数DataSet
/// </summary>
/// <returns></returns>
public static DataSet ExecuteReaderByDataSet(string cmdText)
{
DataSet set = new DataSet();
try
{
init();
OracleDataAdapter adp = new OracleDataAdapter(cmdText, conn);
adp.Fill(set, "Ds");
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return set;
}
#endregion
/// <summary>
/// 读取数据
/// </summary>
/// <param name="cmdText"></param>
/// OracleDataAdapter读取数据
/// <returns></returns>
public static DataSet ExecuteDataReader(string cmdText)
{
OracleDataAdapter odataAdapter;
DataSet ds = new DataSet();
try
{
init();
odataAdapter = new OracleDataAdapter(cmdText, conn);
odataAdapter.Fill(ds, "personInfo");
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return ds;
}
#region 执行不带参数sql语句,返回结果集首行首列的值object
/// <summary>
/// 执行不带参数sql语句,返回结果集首行首列的值object
/// </summary>
/// <param name="cmdstr">相应的sql语句</param>
/// <returns>返回结果集首行首列的值object</returns>
public static object ExecuteScalar(string cmdText)
{
object obj;
try
{
init();
cmd = new OracleCommand(cmdText, conn);
obj = cmd.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return obj;
}
#endregion
#region 执行带参数sql语句或存储过程,返回结果集首行首列的值object
/// <summary>
/// 执行带参数sql语句或存储过程,返回结果集首行首列的值object
/// </summary>
/// <param name="cmdText">sql语句或存储过程名</param>
/// <param name="cmdType">命令类型</param>
/// <param name="cmdParms">返回结果集首行首列的值object</param>
/// <returns></returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType, OracleParameter[] cmdParms)
{
object obj;
try
{
init();
cmd = new OracleCommand();
SetCommand(cmd, cmdText, cmdType, cmdParms);
obj = cmd.ExecuteScalar();
conn.Close();
}
catch (Exception ex)
{
throw new Exception(ex.Message.ToString());
}
return obj;
}
#endregion
#region DataSet装换为泛型集合
/// <summary>
/// DataSet装换为泛型集合
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="p_DataSet">DataSet</param>
/// <param name="p_TableIndex">待转换数据表索引</param>
/// <returns></returns>
/// 2008-08-01 22:46 HPDV2806
public static IList<T> DataSetToIList<T>(DataSet p_DataSet, int p_TableIndex)
{
if (p_DataSet == null || p_DataSet.Tables.Count < 0)
return null;
if (p_TableIndex > p_DataSet.Tables.Count - 1)
return null;
if (p_TableIndex < 0)
p_TableIndex = 0;
DataTable p_Data = p_DataSet.Tables[p_TableIndex];
// 返回值初始化
IList<T> result = new List<T>();
for (int j = 0; j < p_Data.Rows.Count; j++)
{
T _t = (T)Activator.CreateInstance(typeof(T));
PropertyInfo[] propertys = _t.GetType().GetProperties();
foreach (PropertyInfo pi in propertys)
{
for (int i = 0; i < p_Data.Columns.Count; i++)
{
// 属性与字段名称一致的进行赋值
if (pi.Name.ToLower().Equals(p_Data.Columns[i].ColumnName.ToLower()))
{
try
{
// 数据库NULL值单独处理
if (p_Data.Rows[j][i].GetType() == typeof(DateTime))
{
pi.SetValue(_t, p_Data.Rows[j][i], null);
}else if (p_Data.Rows[j][i] != DBNull.Value)
pi.SetValue(_t, p_Data.Rows[j][i].ToString(), null);
else
pi.SetValue(_t, null, null);
break;
}
catch (Exception ex)
{
throw;
}
}
}
}
result.Add(_t);
}
return result;
}
#endregion
#region 防止SQL注入方法
public static string FilterSql(string s)
{
if (string.IsNullOrEmpty(s)) return string.Empty;
s = s.Trim().ToLower();
s = s.Replace("=", "");
s = s.Replace("'", "");
s = s.Replace(";", "");
s = s.Replace(" or ", "");
s = s.Replace("select", "");
s = s.Replace("update", "");
s = s.Replace("insert", "");
s = s.Replace("delete", "");
s = s.Replace("declare", "");
s = s.Replace("exec", "");
s = s.Replace("drop", "");
s = s.Replace("create", "");
s = s.Replace("%", "");
s = s.Replace("--", "");
return s;
}
#endregion
}
