You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
675 lines
23 KiB
675 lines
23 KiB
using System;
|
|
using System.Collections;
|
|
using System.Collections.Specialized;
|
|
using System.Data;
|
|
using System.Collections.Generic;
|
|
using System.Text;
|
|
using MySql.Data.MySqlClient;
|
|
|
|
namespace RoadFlow.Data.ORACLE
|
|
{
|
|
/// <summary>
|
|
/// SQLSERVER助手类
|
|
/// </summary>
|
|
public class DBHelper
|
|
{
|
|
private string connectionString;
|
|
|
|
public DBHelper()
|
|
{
|
|
this.connectionString = RoadFlow.Utility.Config.PlatformConnectionStringORACLE;
|
|
}
|
|
public DBHelper(string connString)
|
|
{
|
|
this.connectionString = connString;
|
|
}
|
|
|
|
/// <summary>
|
|
/// 连接字符串
|
|
/// </summary>
|
|
public string ConnectionString
|
|
{
|
|
get { return this.connectionString; }
|
|
}
|
|
|
|
/// <summary>
|
|
/// 释放连接
|
|
/// </summary>
|
|
public void Dispose()
|
|
{
|
|
|
|
}
|
|
|
|
///// <summary>
|
|
///// 得到一个MySqlDataReader
|
|
///// </summary>
|
|
///// <param name="sql">sql语句</param>
|
|
///// <returns></returns>
|
|
//public MySqlDataReader GetDataReader(string sql)
|
|
//{
|
|
// MySqlConnection conn = new MySqlConnection(ConnectionString);
|
|
// conn.Open();
|
|
// using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
// {
|
|
// cmd.Prepare();
|
|
// return cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|
// }
|
|
//}
|
|
/// <summary>
|
|
/// 得到一个MySqlDataReader
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <returns></returns>
|
|
public DataTable GetDataReader(string sql)
|
|
{
|
|
var dt = GetDataTable(sql);
|
|
return dt;
|
|
}
|
|
|
|
///// <summary>
|
|
///// 得到一个MySqlDataReader
|
|
///// </summary>
|
|
///// <param name="sql">sql语句</param>
|
|
///// <returns></returns>
|
|
//public MySqlDataReader GetDataReader(string sql, MySqlParameter[] parameter)
|
|
//{
|
|
// MySqlConnection conn = new MySqlConnection(ConnectionString);
|
|
// conn.Open();
|
|
// using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
// {
|
|
// if (parameter != null && parameter.Length > 0)
|
|
// cmd.Parameters.AddRange(parameter);
|
|
// MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
|
|
// //cmd.Parameters.Clear();
|
|
// return dr;
|
|
// }
|
|
//}
|
|
|
|
/// <summary>
|
|
/// 得到一个MySqlDataReader
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <returns></returns>
|
|
public DataTable GetDataReader(string sql, MySqlParameter[] parameter)
|
|
{
|
|
var dt = GetDataTable(sql, parameter);
|
|
return dt;
|
|
}
|
|
|
|
|
|
/// <summary>
|
|
/// 得到一个DataTable
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <returns></returns>
|
|
public DataTable GetDataTable(string sql)
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
|
|
{
|
|
DataTable dt = new DataTable();
|
|
try
|
|
{
|
|
|
|
connection.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
|
|
{
|
|
MySqlDataReader dr = cmd.ExecuteReader();
|
|
dt.Load(dr);
|
|
dr.Close();
|
|
dr.Dispose();
|
|
return dt;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return dt;
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 得到一个DataTable
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <returns></returns>
|
|
public DataTable GetDataTable(string sql, MySqlParameter[] parameter)
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
|
|
{
|
|
DataTable dt = new DataTable();
|
|
try
|
|
{
|
|
connection.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, connection))
|
|
{
|
|
if (parameter != null && parameter.Length > 0)
|
|
cmd.Parameters.AddRange(parameter);
|
|
MySqlDataReader dr = cmd.ExecuteReader();
|
|
dt.Load(dr);
|
|
dr.Close();
|
|
dr.Dispose();
|
|
cmd.Parameters.Clear();
|
|
return dt;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return dt;
|
|
}
|
|
finally
|
|
{
|
|
if (connection.State == ConnectionState.Open)
|
|
{
|
|
connection.Close();
|
|
MySqlConnection.ClearPool(connection);//加入此行可释放连接
|
|
connection.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 得到数据集
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public DataSet GetDataSet(string sql)
|
|
{
|
|
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
|
|
{
|
|
DataSet ds = new DataSet();
|
|
try
|
|
{
|
|
connection.Open();
|
|
using (MySqlDataAdapter dap = new MySqlDataAdapter(sql, connection))
|
|
{
|
|
dap.Fill(ds);
|
|
return ds;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return ds;
|
|
}
|
|
finally
|
|
{
|
|
if (connection.State == ConnectionState.Open)
|
|
{
|
|
connection.Close();
|
|
MySqlConnection.ClearPool(connection);//加入此行可释放连接
|
|
connection.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行SQL
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public int Execute(string sql)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
{
|
|
//cmd.Prepare();
|
|
return cmd.ExecuteNonQuery();
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return 0;
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行SQL(事务)
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public int Execute(List<string> sqlList)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand())
|
|
{
|
|
int i = 0;
|
|
cmd.Connection = conn;
|
|
foreach (string sql in sqlList)
|
|
{
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.CommandText = sql;
|
|
//cmd.Prepare();
|
|
i += cmd.ExecuteNonQuery();
|
|
}
|
|
return i;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return 0;
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 执行带参数的SQL
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <param name="param"></param>
|
|
/// <returns></returns>
|
|
public int Execute2(string sql, MySqlParameter[] parameter)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
{
|
|
if (parameter != null && parameter.Length > 0)
|
|
cmd.Parameters.AddRange(parameter);
|
|
int i = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
//cmd.Prepare();
|
|
return i;
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return 0;
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行SQL语句，返回影响的记录数
|
|
/// </summary>
|
|
/// <param name="SQLString">SQL语句</param>
|
|
/// <returns>影响的记录数</returns>
|
|
public int Execute(string SQLString, params MySqlParameter[] parameter)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
using (MySqlCommand cmd = new MySqlCommand())
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
cmd.Connection = conn;
|
|
cmd.CommandText = SQLString;
|
|
// cmd.CommandType = CommandType.Text;//cmdType;
|
|
if (parameter != null && parameter.Length > 0)
|
|
cmd.Parameters.AddRange(parameter);
|
|
int rows = cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
//cmd.Prepare();
|
|
return rows;
|
|
}
|
|
catch (Exception E)
|
|
{
|
|
throw new Exception(E.Message);
|
|
}
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return 0;
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 执行SQL(事务)
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public int Execute(List<string> sqlList, List<MySqlParameter[]> parameterList)
|
|
{
|
|
if (sqlList.Count > parameterList.Count)
|
|
{
|
|
throw new Exception("参数错误");
|
|
}
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
|
|
conn.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand())
|
|
{
|
|
int i = 0;
|
|
cmd.Connection = conn;
|
|
for (int j = 0; j < sqlList.Count; j++)
|
|
{
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.CommandText = sqlList[j];
|
|
if (parameterList[j] != null && parameterList[j].Length > 0)
|
|
{
|
|
cmd.Parameters.AddRange(parameterList[j]);
|
|
}
|
|
i += cmd.ExecuteNonQuery();
|
|
cmd.Parameters.Clear();
|
|
//cmd.Prepare();
|
|
}
|
|
return i;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return 0;
|
|
}
|
|
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 得到一个字段的值
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public string ExecuteScalar(string sql)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
{
|
|
object obj = cmd.ExecuteScalar();
|
|
//cmd.Prepare();
|
|
return obj != null ? obj.ToString() : string.Empty;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return "";
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 得到一个字段的值
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public string ExecuteScalar(string sql, MySqlParameter[] parameter)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
{
|
|
if (parameter != null && parameter.Length > 0)
|
|
cmd.Parameters.AddRange(parameter);
|
|
object obj = cmd.ExecuteScalar();
|
|
cmd.Parameters.Clear();
|
|
//cmd.Prepare();
|
|
return obj != null ? obj.ToString() : string.Empty;
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return "";
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
/// <summary>
|
|
/// 得到一个字段的值
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public string GetFieldValue(string sql)
|
|
{
|
|
return ExecuteScalar(sql);
|
|
}
|
|
/// <summary>
|
|
/// 得到一个字段的值
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <param name="parameter"></param>
|
|
/// <returns></returns>
|
|
public string GetFieldValue(string sql, MySqlParameter[] parameter)
|
|
{
|
|
return ExecuteScalar(sql, parameter);
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取一个sql的字段名称
|
|
/// </summary>
|
|
/// <param name="sql">sql语句</param>
|
|
/// <returns></returns>
|
|
public string GetFields(string sql, MySqlParameter[] param)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
System.Text.StringBuilder names = new System.Text.StringBuilder(500);
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
{
|
|
if (param != null && param.Length > 0)
|
|
cmd.Parameters.AddRange(param);
|
|
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
|
|
for (int i = 0; i < dr.FieldCount; i++)
|
|
{
|
|
names.Append("[" + dr.GetName(i) + "]" + (i < dr.FieldCount - 1 ? "," : string.Empty));
|
|
}
|
|
cmd.Parameters.Clear();
|
|
dr.Close();
|
|
dr.Dispose();
|
|
//cmd.Prepare();
|
|
return names.ToString();
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
return "";
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 获取一个sql的字段名称
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <param name="param"></param>
|
|
/// <param name="tableName">表名 </param>
|
|
/// <returns></returns>
|
|
public string GetFields(string sql, MySqlParameter[] param, out string tableName)
|
|
{
|
|
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
|
|
{
|
|
try
|
|
{
|
|
conn.Open();
|
|
System.Text.StringBuilder names = new System.Text.StringBuilder(500);
|
|
using (MySqlCommand cmd = new MySqlCommand(sql, conn))
|
|
{
|
|
if (param != null && param.Length > 0)
|
|
cmd.Parameters.AddRange(param);
|
|
MySqlDataReader dr = cmd.ExecuteReader(CommandBehavior.SchemaOnly);
|
|
tableName = dr.GetSchemaTable().TableName;
|
|
for (int i = 0; i < dr.FieldCount; i++)
|
|
{
|
|
names.Append("[" + dr.GetName(i) + "]" + (i < dr.FieldCount - 1 ? "," : string.Empty));
|
|
}
|
|
cmd.Parameters.Clear();
|
|
dr.Close();
|
|
dr.Dispose();
|
|
//cmd.Prepare();
|
|
return names.ToString();
|
|
}
|
|
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
tableName = "";
|
|
return "";
|
|
}
|
|
finally
|
|
{
|
|
if (conn.State == ConnectionState.Open)
|
|
{
|
|
conn.Close();
|
|
MySqlConnection.ClearPool(conn);//加入此行可释放连接
|
|
conn.Dispose();
|
|
}
|
|
GC.Collect();
|
|
}
|
|
}
|
|
}
|
|
|
|
/// <summary>
|
|
/// 得到分页sql
|
|
/// </summary>
|
|
/// <param name="sql"></param>
|
|
/// <returns></returns>
|
|
public string GetPaerSql(string sql, int size, int number, out long count, MySqlParameter[] param = null)
|
|
{
|
|
string count1 = GetFieldValue(string.Format("select count(*) from ({0}) t ", sql), param);
|
|
long i;
|
|
count = count1.IsLong(out i) ? i : 0;
|
|
number = number < 1 ? 1 : number;
|
|
StringBuilder sql1 = new StringBuilder();
|
|
sql1.Append(sql);
|
|
sql1.AppendFormat(" LIMIT {0} , {1}", (number - 1) * size, size);
|
|
|
|
return sql1.ToString();
|
|
}
|
|
|
|
/// <summary>
|
|
/// 得到分页sql
|
|
/// </summary>
|
|
/// <param name="table">表名称</param>
|
|
/// <param name="fileds">查询列</param>
|
|
/// <param name="where">查询条件</param>
|
|
/// <param name="order">排序字段</param>
|
|
/// <param name="size">每页大小</param>
|
|
/// <param name="number">当前页</param>
|
|
/// <param name="count">查询结果总页数</param>
|
|
/// <param name="param">查询参数</param>
|
|
/// <returns></returns>
|
|
public string GetPaerSql(string table, string fileds, string where, string order, int size, int number, out long count, MySqlParameter[] param = null)
|
|
{
|
|
string where1 = string.Empty;
|
|
if (where.IsNullOrEmpty())
|
|
{
|
|
where1 = "";
|
|
}
|
|
else
|
|
{
|
|
where1 = where.Trim();
|
|
if (where1.StartsWith("and", StringComparison.CurrentCultureIgnoreCase))
|
|
{
|
|
where1 = where1.Substring(3);
|
|
}
|
|
}
|
|
string where2 = where1.IsNullOrEmpty() ? "" : " where " + where1;
|
|
string sql = string.Format("select bm.{0} from {2} bm {3}", fileds, order, table, where2);
|
|
|
|
|
|
string count1 = GetFieldValue(string.Format("select count(*) from {0} {1}", table, where2), param);
|
|
long i;
|
|
count = count1.IsLong(out i) ? i : 0;
|
|
|
|
StringBuilder sql1 = new StringBuilder();
|
|
sql1.Append(sql);
|
|
sql1.AppendFormat(" LIMIT {0} , {1}", ((number < 1 ? 1 : number) - 1) * size, size);
|
|
|
|
return sql1.ToString();
|
|
}
|
|
|
|
}
|
|
}
|
|
|