最近有些地方用到c#，研究了一下，也有數據庫方面的操作，那就繼續封裝，自己用起來好用一點。
數據庫連接
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.ComponentModel;
using System.Drawing;using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Collections;using System.Configuration;
using System.Data.Common;namespace Simulation.Commons
{public class ConnectionPool{private static ConnectionPool cpool = null;//池管理對象private static Object objlock = typeof(ConnectionPool);//池管理對象實例private int size = 1;//池中連接數private int useCount = 0;//已經使用的連接數private ArrayList pool = null;//連接保存的集合private String ConnectionStr = "";//連接字符串public ConnectionPool(){//數據庫連接字符串ConnectionStr = "server=localhost;User ID=root;Password=root;database=Test;Charset=utf8;";//創建可用連接的集合pool = new ArrayList();}#region 創建獲取連接池對象public static ConnectionPool getPool(){lock (objlock){if (cpool == null){cpool = new ConnectionPool();}return cpool;}}#endregion#region 獲取池中的連接public MySqlConnection getConnection(){lock (pool){MySqlConnection tmp = null;try{//可用連接數量大于0if (pool.Count > 0){//取第一個可用連接tmp = (MySqlConnection)pool[0];//在可用連接中移除此鏈接pool.RemoveAt(0);//不成功if (!isUserful(tmp)){//可用的連接數據已去掉一個useCount--;tmp = getConnection();}}else{//可使用的連接小于連接數量if (useCount <= size){try{//創建連接tmp = CreateConnection(tmp);}catch (Exception ex){System.Diagnostics.Debug.WriteLine("getConnection 異常信息:{0}", ex.Message);}}}//連接為nullif (tmp == null){//達到最大連接數遞歸調用獲取連接否則創建新連接if (useCount <= size){tmp = getConnection();}else{tmp = CreateConnection(tmp);}}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("getConnection異常信息:{0}", ex.Message);}return tmp;}}#endregion#region 創建連接private MySqlConnection CreateConnection(MySqlConnection tmp){//創建連接MySqlConnection conn = new MySqlConnection(ConnectionStr);try{while (conn.State != ConnectionState.Open)conn.Open();//可用的連接數加上一個useCount++;tmp = conn;}catch (Exception ex){conn.Close();System.Diagnostics.Debug.WriteLine("CreateConnection異常信息:{0}", ex.Message);}return tmp;}#endregion#region 關閉連接,加連接回到池中public void closeConnection(MySqlConnection con){lock (pool){if (con != null){//將連接添加在連接池中pool.Add(con);}}}#endregion#region 目的保證所創連接成功,測試池中連接private bool isUserful(MySqlConnection con){//主要用于不同用戶bool result = true;if (con != null){string sql = "select 1";//隨便執行對數據庫操作MySqlCommand cmd = new MySqlCommand(sql, con);try{cmd.ExecuteScalar().ToString();}catch (Exception ex){con.Close();result = false;System.Diagnostics.Debug.WriteLine("isUserful異常信息:{0}", ex.Message);}}return result;}#endregion}
}數據庫操作封裝
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text.RegularExpressions;
using System.Data;
using MySql.Data.MySqlClient;using System.Reflection;
using System.Text;
using System.Web.SessionState;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using System.Dynamic;namespace Simulation.Commons
{public class SqlDatabase{public static List<dynamic> QueryRecords(string strSql){MySqlConnection conn = null;conn = ConnectionPool.getPool().getConnection();List<dynamic> ObjectstateList = new List<dynamic>();try{MySqlCommand cmd = new MySqlCommand(strSql, conn);DataTable resultTable = new DataTable();using (MySqlDataAdapter da = new MySqlDataAdapter()){da.SelectCommand = cmd;da.Fill(resultTable);foreach (DataRow row in resultTable.Rows){dynamic obj = Activator.CreateInstance(new ExpandoObject().GetType());IDictionary<string, object> dict = obj;foreach (DataColumn column in resultTable.Columns){dict[column.ColumnName] = row[column.ColumnName];}ObjectstateList.Add(obj);}}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("異常信息:{0}", ex.Message);}finally{ConnectionPool.getPool().closeConnection(conn);}return ObjectstateList;}public static JArray QueryRecord(string strSql){MySqlConnection conn = null;conn = ConnectionPool.getPool().getConnection();JArray jsonResult = new JArray();try{using (MySqlCommand cmd = new MySqlCommand(strSql, conn)){DataTable resultTable = new DataTable();using (MySqlDataAdapter da = new MySqlDataAdapter()){da.SelectCommand = cmd;da.Fill(resultTable);foreach (DataRow row in resultTable.Rows){JObject result = new JObject();foreach (DataColumn column in resultTable.Columns){switch (Type.GetTypeCode(row[column.ColumnName].GetType())){case TypeCode.String:string strValue = row[column.ColumnName].ToString();if (strValue.StartsWith("{") && strValue.EndsWith("}")){result[column.ColumnName] = JObject.Parse(strValue);}else if (strValue.StartsWith("[{") && strValue.EndsWith("}]")){result[column.ColumnName] = JArray.Parse(strValue);}else if (strValue.StartsWith("[") && strValue.EndsWith("]")){result[column.ColumnName] = JArray.Parse(strValue);}else{result[column.ColumnName] = row[column.ColumnName].ToString();}break;case TypeCode.Int32:result[column.ColumnName] = Convert.ToInt32(row[column.ColumnName].ToString());break;case TypeCode.Int64:result[column.ColumnName] = Convert.ToInt64(row[column.ColumnName].ToString());break;case TypeCode.Double:result[column.ColumnName] = Convert.ToDouble(row[column.ColumnName].ToString());break;default:result[column.ColumnName] = row[column.ColumnName].ToString();break;}}jsonResult.Add(result);}}}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("異常信息:{0}", ex.Message);}finally{ConnectionPool.getPool().closeConnection(conn);}return jsonResult;}public static bool InsertDB(string tableName, JArray jsonValue){bool result = false;MySqlConnection conn = null;conn = ConnectionPool.getPool().getConnection();try{string strValues = "";string strNames = "";bool isColumn = true;for (int i = 0; i < jsonValue.Count(); i++){string strValue = "";foreach (var it in (JObject)jsonValue[i]){if (strValue == ""){strValue = string.Format("'{0}'", it.Value);if (isColumn){strNames = string.Format("`{0}`", it.Key);}}else{strValue += string.Format(",'{0}'", it.Value);if (isColumn){strNames += string.Format(",`{0}`", it.Key);}}}if (strValues == ""){strValues = string.Format("({0})", strValue);}else{strValues += string.Format(",({0})", strValue);}}string strSql = string.Format("INSERT INTO {0} ({1}) VALUES {2} ", tableName, strNames, strValues);using (MySqlCommand cmd = new MySqlCommand()){cmd.CommandTimeout = 5000;cmd.Connection = conn;cmd.CommandText = strSql;int value = cmd.ExecuteNonQuery();if (value >= 0) result = true;else result = false;}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("異常信息:{0}", ex.Message);result = false;}finally{ConnectionPool.getPool().closeConnection(conn);}return result;}public static bool InsertDB(string tableName, JObject jsonValue){bool result = false;MySqlConnection conn = null;conn = ConnectionPool.getPool().getConnection();try{string strValues = "";string strNames = "";foreach (var it in jsonValue){if (strValues == ""){strValues = string.Format("'{0}'", it.Value);strNames = string.Format("`{0}`", it.Key);}else{strValues += string.Format(",'{0}'", it.Value);strNames += string.Format(",`{0}`", it.Key);}}string strSql = string.Format("INSERT INTO {0} ({1}) VALUES ({2}) ", tableName, strNames, strValues);using (MySqlCommand cmd = new MySqlCommand()){cmd.CommandTimeout = 5000;cmd.Connection = conn;cmd.CommandText = strSql;int value = cmd.ExecuteNonQuery();if (value >= 0) result = true;else result = false;}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("異常信息:{0}", ex.Message);result = false;}finally{ConnectionPool.getPool().closeConnection(conn);}return result; }public static bool UpdateDB(string tableName, JObject jsonValue, string strCondition){bool result = false;MySqlConnection conn = null;conn = ConnectionPool.getPool().getConnection();try{string strValues = "";foreach (var it in jsonValue){if (strValues == "" ){strValues = string.Format("{0}='{1}'",it.Key, it.Value);}else{strValues += string.Format(",{0}='{1}'", it.Key, it.Value);}}string strSql = string.Format("UPDATE {0} SET {1} {2} ", tableName, strValues, strCondition);using (MySqlCommand cmd = new MySqlCommand()){cmd.Connection = conn;cmd.CommandText = strSql;int nValue = cmd.ExecuteNonQuery();if (nValue >= 0) result = true;else result = false;}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("異常信息:{0}", ex.Message);result = false;}finally{ConnectionPool.getPool().closeConnection(conn);}return result;}public static bool DeleteDB(string strSql){bool result = false;MySqlConnection conn = null;conn = ConnectionPool.getPool().getConnection();try{using (MySqlCommand cmd = new MySqlCommand()){cmd.Connection = conn;cmd.CommandText = strSql;int nValue = cmd.ExecuteNonQuery();if (nValue >= 0) result = true;else result = false;}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("異常信息:{0}", ex.Message);result = false;}finally{ConnectionPool.getPool().closeConnection(conn);}return result;}public static int QueryCount(string strSql){int result = 0;MySqlConnection conn = null;conn = ConnectionPool.getPool().getConnection();try{string strSqlCount = string.Format(" SELECT COUNT(*) AS Cnt FROM ({0}) t ", strSql);using (MySqlCommand cmd = new MySqlCommand(strSqlCount, conn)){using (MySqlDataReader reader = cmd.ExecuteReader()){while (reader.Read()){result = reader.GetInt32("Cnt");}}}}catch (Exception ex){System.Diagnostics.Debug.WriteLine("異常信息:{0}", ex.Message);}finally{ConnectionPool.getPool().closeConnection(conn);}return result;}}
}返回都是以json格式。
)
