龙岩网站建设方式,免密码wifi万能连接,泰安建设信息网站,可以推广网站最近有些地方用到c##xff0c;研究了一下#xff0c;也有数据库方面的操作#xff0c;那就继续封装#xff0c;自己用起来好用一点。
数据库连接
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using Sys…最近有些地方用到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 serverlocalhost;User IDroot;Passwordroot;databaseTest;Charsetutf8;;//创建可用连接的集合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 Listdynamic QueryRecords(string strSql){MySqlConnection conn null;conn ConnectionPool.getPool().getConnection();Listdynamic ObjectstateList new Listdynamic();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());IDictionarystring, 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格式。
