C# 数据库封装

最近有些地方用到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
                {
                    //可用连接数量大于0
                    if (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);
                            }
                        }
                    }
                    //连接为null
                    if (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格式。