基于Dapper写的一个sqlhelp适用于多版本数据库

ConnectionInit方法用于初始化数据库连接对象，

只需要修改databasetype参数即可进行适用各个版本的数据库，

ExecuteNonQuery方法用于执行增、删、改操作，返回受影响的行数，

ExecuteTableSQL方法用于执行查询操作，返回datatable格式的数据，

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Dapper;

namespace DataManager
{
    public static class DaHelper
    {

        private static IDbConnection ConnectionObject = null;//连接数据类的对象
        private static string ConnectionString = "";//连接的字符串
        private static int DataBaseType = 0;//数据库的类型0=sqlserver,1=access,2=oracle,3=mysql

        /// <summary>
        /// 设置连接的字符串及数据库类型    
        /// </summary>
        /// <param name="str">连接的字符串</param>
        /// <param name="_type">数据库类型0=sqlserver,1=access,2=oracle,3=mysql</param>
        public static void ConnectionInit(string str, int _type)
        {
            ConnectionString = str;
            DataBaseType = _type;
            SetConnection();
        }

        /// <summary>
        /// 设置连接类的对象
        /// </summary>
        private static void SetConnection()
        {
            switch (DataBaseType)
            {
                case 0:
                    ConnectionObject = new System.Data.SqlClient.SqlConnection(ConnectionString);//连接sqlserver                  
                    break;
                case 1:
                    ConnectionObject = new System.Data.OleDb.OleDbConnection(ConnectionString);//连接access
                    break;
                case 2:
                    ConnectionObject = new System.Data.OracleClient.OracleConnection(ConnectionString);//连接oracle
                    //处理办法：
                    //在oracle 安装目录下 找到 Oracle.DataAccess.dll添加引用，然后 using Oracle.DataAccess.Client;
                    //其他的都不用动，即可。
                    //连接字符串中 如有 用的是 user=xxx 就改成user id=xxx
                    //把原来 Using 的System.Data.OracleClient去掉即可
                    break;
                case 3:
                    ConnectionObject = new MySql.Data.MySqlClient.MySqlConnection(ConnectionString);//连接mysql
                    break;
            }

        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private static void OpenConnection()
        {
            if (ConnectionObject.State == System.Data.ConnectionState.Closed)
            {
                ConnectionObject.Open();
            }
        }
        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private static void CloseConnection()
        {
            if (ConnectionObject.State == System.Data.ConnectionState.Open)
            {
                ConnectionObject.Close();
            }
        }

        /// <summary>
        /// 执行sql并且返回受影响的行数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="type"></param>
        /// <param name="para"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, object _object)
        {
            lock (ConnectionObject)
            {
                try
                {
                    OpenConnection();
                    return ConnectionObject.Execute(sql, _object);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    CloseConnection();
                }
            }
        }

        /// <summary>
        /// 执行查询的sql语句，并且返回datatable结果
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        public static DataTable ExecuteTableSQL(string sql, object _object)
        {
            lock (ConnectionObject)
            {
                try
                {
                    OpenConnection();
                    IDataReader idr = ConnectionObject.ExecuteReader(sql, _object);
                    return IDataReaderToDataTable(idr);
                }
                catch
                {
                    throw;
                }
                finally
                {
                    CloseConnection();
                }
            }

        }
        /// <summary>
        /// 把idatareader转换成datatable
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static DataTable IDataReaderToDataTable(IDataReader reader)
        {

            DataTable objDataTable = new DataTable();

            int intFieldCount = reader.FieldCount;
            for (int intCounter = 0; intCounter < intFieldCount; ++intCounter)
            {
                objDataTable.Columns.Add(reader.GetName(intCounter), typeof(string));
            }

            objDataTable.BeginLoadData();
            object[] objValues = new object[intFieldCount];


            while (reader.Read())
            {
                reader.GetValues(objValues);
                objDataTable.LoadDataRow(objValues, true);

            }
            reader.Close();
            objDataTable.EndLoadData();

            return objDataTable;
        }

 

 

初始化例子：

 

DataManager.DaHelper.ConnectionInit("server=127.0.0.1;uid=root;pwd=;database=TouchDisplay;pooling=false;CharSet=utf8;port=3306;", 3);//连接mysql数据库

 

增、删、改例子：

        /// <summary>
        /// 新增排队记录
        /// </summary>
        /// <param name="CarNo">车牌</param>
        /// <param name="QueueNo">排队号码</param>
        /// <param name="QueueIndex">排队序号</param>
        /// <param name="IcCard">Ic卡号</param>
        /// <param name="CargoNo">油品</param>
        /// <param name="ComeTime">登记时间</param>
        /// <param name="StateNo">状态</param>
        /// <returns></returns>
        public static int AddQueue(string CarNo, string QueueNo, int QueueIndex,string IcCard, string CargoName,DateTime ComeTime, int StateNo)
        {
            string sql = "Insert Into Table_Queue (CarNo,QueueNo,QueueIndex,IcCard,CargoName,ComeTime,StateNo) Values(@CarNo,@QueueNo,@QueueIndex,@IcCard,@CargoName,@ComeTime,@StateNo);";
            object para = new
            {
                CarNo = CarNo,
                QueueNo = QueueNo,
                QueueIndex = QueueIndex,
                IcCard=IcCard,
                CargoName = CargoName,
                ComeTime=ComeTime,
                StateNo = StateNo,
            };
            return DaHelper.ExecuteNonQuery(sql, para);
        }


        /// <summary>
        /// 根据车牌号码删除队列中的车辆
        /// </summary>
        /// <param name="CarNo"></param>
        /// <returns></returns>
        public static int RemoveCarDataByCarNo(string CarNo)
        {
            string sql = "delete from Table_Queue where CarNo=@CarNo and  StateNo = 0";
            object para = new
            {
                CarNo = CarNo,
            };
            return DaHelper.ExecuteNonQuery(sql, para);
        }

        /// <summary>
        /// 根据车牌号更新车辆信息
        /// </summary>
        /// <param name="CarNo"></param>
        /// <param name="QueueIndex"></param>
        /// <returns></returns>
        public static int UpdateQueueByCarNo(string CarNo, string IcCard, string CargoName)
        {
            string sql = "update Table_Queue set IcCard=@IcCard ,CargoName=@CargoName where CarNo=@CarNo and  StateNo = 0 or CarNo=@CarNo and  StateNo = 1";
            object para = new
            {
                CarNo = CarNo,
                IcCard = IcCard,
                CargoName = CargoName,
            };
            return DaHelper.ExecuteNonQuery(sql, para);
        }

 

 

查询例子：

        /// <summary>
        /// 获取排队列表
        /// </summary>
        /// <returns></returns>
        public static DataTable GetAllQueueing()
        {
            string sql = "select QueueNo ,CarNo ,CargoName  from Table_Queue where StateNo =0 order by QueueIndex";

            DataTable dt = DaHelper.ExecuteTableSQL(sql, null);
            return dt;
        }

 

 

 

the end