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 { /// /// SQLSERVER助手类 /// public class DBHelper { private string connectionString; public DBHelper() { this.connectionString = RoadFlow.Utility.Config.PlatformConnectionStringORACLE; } public DBHelper(string connString) { this.connectionString = connString; } /// /// 连接字符串 /// public string ConnectionString { get { return this.connectionString; } } /// /// 释放连接 /// public void Dispose() { } ///// ///// 得到一个MySqlDataReader ///// ///// sql语句 ///// //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); // } //} /// /// 得到一个MySqlDataReader /// /// sql语句 /// public DataTable GetDataReader(string sql) { var dt = GetDataTable(sql); return dt; } ///// ///// 得到一个MySqlDataReader ///// ///// sql语句 ///// //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; // } //} /// /// 得到一个MySqlDataReader /// /// sql语句 /// public DataTable GetDataReader(string sql, MySqlParameter[] parameter) { var dt = GetDataTable(sql, parameter); return dt; } /// /// 得到一个DataTable /// /// sql语句 /// 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; } } } /// /// 得到一个DataTable /// /// sql语句 /// 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(); } } } /// /// 得到数据集 /// /// /// 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(); } } } /// /// 执行SQL /// /// /// 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; } } } /// /// 执行SQL(事务) /// /// /// public int Execute(List 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(); } } } /// /// 执行带参数的SQL /// /// /// /// 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(); } } } /// /// 执行SQL语句,返回影响的记录数 /// /// SQL语句 /// 影响的记录数 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(); } } } /// /// 执行SQL(事务) /// /// /// public int Execute(List sqlList, List 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(); } } } /// /// 得到一个字段的值 /// /// /// 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(); } } } /// /// 得到一个字段的值 /// /// /// 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(); } } } /// /// 得到一个字段的值 /// /// /// public string GetFieldValue(string sql) { return ExecuteScalar(sql); } /// /// 得到一个字段的值 /// /// /// /// public string GetFieldValue(string sql, MySqlParameter[] parameter) { return ExecuteScalar(sql, parameter); } /// /// 获取一个sql的字段名称 /// /// sql语句 /// 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(); } } } /// /// 获取一个sql的字段名称 /// /// /// /// 表名 /// 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(); } } } /// /// 得到分页sql /// /// /// 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(); } /// /// 得到分页sql /// /// 表名称 /// 查询列 /// 查询条件 /// 排序字段 /// 每页大小 /// 当前页 /// 查询结果总页数 /// 查询参数 /// 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(); } } }