DataControlMOD/DataControlMod/Common/DapperHelper.cs

using Dapper;
using DataControlMod.Common;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace PullChargeData.Helper
{
    public class DapperHelper
    {
        /// <summary>
        /// 建立连接
        /// </summary>
        /// <returns></returns>
        public static MySqlConnection MySqlConnection()
        {
            var conn = AppSettingsHelper.App("SQL", "mysql");

            var connection = new MySqlConnection(conn);
            connection.Open();
            return connection;
        }

        ///// <summary>
        ///// 数据库连接字符串
        ///// </summary>
        //private static readonly string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["db"].ConnectionString;

        ///// <summary>
        ///// 查询列表（返回DataTable）
        ///// </summary>
        ///// <returns></returns>
        //public static DataTable QueryToDataTable(string sql)
        //{
        //    DataTable table = new DataTable("MyTable");
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        var reader = con.ExecuteReader(sql);
        //        table.Load(reader);
        //        return table;
        //    }
        //}

        ///// <summary>
        ///// 查询列表
        ///// </summary>
        ///// <param name="sql">查询的sql</param>
        ///// <param name="param">替换参数</param>
        ///// <returns></returns>
        //public static List<T> Query(string sql, object param = null)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.Query<T>(sql, param).ToList();
        //    }
        //}

        ///// <summary>
        ///// 查询第一个数据
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static T QueryFirst(string sql, object param = null)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.Query<T>(sql, param).ToList().First();
        //    }
        //}

        ///// <summary>
        ///// 查询第一个数据没有返回默认值
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static T QueryFirstOrDefault(string sql, object param = null)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.Query<T>(sql, param).ToList().FirstOrDefault();
        //    }
        //}

        ///// <summary>
        ///// 查询单条数据
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static T QuerySingle(string sql, object param = null)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.Query<T>(sql, param).ToList().Single();
        //    }
        //}

        ///// <summary>
        ///// 查询单条数据没有返回默认值
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static T QuerySingleOrDefault(string sql, object param = null)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.Query<T>(sql, param).ToList().SingleOrDefault();
        //    }
        //}

        ///// <summary>
        ///// 增删改
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns>Number of rows affected</returns>
        //public static int Execute(string sql, object param)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.Execute(sql, param);
        //    }
        //}

        ///// <summary>
        ///// Reader获取数据
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static IDataReader ExecuteReader(string sql, object param)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.ExecuteReader(sql, param);
        //    }
        //}

        ///// <summary>
        ///// Scalar获取数据
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static object ExecuteScalar(string sql, object param)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.ExecuteScalar(sql, param);
        //    }
        //}

        ///// <summary>
        ///// Scalar获取数据
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static T ExecuteScalarForT(string sql, object param)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        return con.ExecuteScalar<T>(sql, param);
        //    }
        //}

        ///// <summary>
        ///// 带参数的存储过程
        ///// </summary>
        ///// <param name="sql"></param>
        ///// <param name="param"></param>
        ///// <returns></returns>
        //public static List<T> ExecutePro(string proc, object param)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        List<T> list = con.Query<T>(proc,
        //            param,
        //            null,
        //            true,
        //            null,
        //            CommandType.StoredProcedure).ToList();
        //        return list;
        //    }
        //}


        ///// <summary>
        ///// 事务1 - 全SQL
        ///// </summary>
        ///// <param name="sqlarr">多条SQL</param>
        ///// <param name="param">param</param>
        ///// <returns></returns>
        //public static int ExecuteTransaction(string[] sqlarr)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        using (var transaction = con.BeginTransaction())
        //        {
        //            try
        //            {
        //                int result = 0;
        //                foreach (var sql in sqlarr)
        //                {
        //                    result += con.Execute(sql, null, transaction);
        //                }

        //                transaction.Commit();
        //                return result;
        //            }
        //            catch (Exception ex)
        //            {
        //                transaction.Rollback();
        //                return 0;
        //            }
        //        }
        //    }
        //}

        ///// <summary>
        ///// 事务2 - 声明参数
        /////demo:
        /////dic.Add("Insert into Users values (@UserName, @Email, @Address)",
        /////        new { UserName = "jack", Email = "380234234@qq.com", Address = "上海" });
        ///// </summary>
        ///// <param name="Key">多条SQL</param>
        ///// <param name="Value">param</param>
        ///// <returns></returns>
        //public static int ExecuteTransaction(Dictionary<string, object> dic)
        //{
        //    using (IDbConnection con = new MySqlConnection(connectionString))
        //    {
        //        using (var transaction = con.BeginTransaction())
        //        {
        //            try
        //            {
        //                int result = 0;
        //                foreach (var sql in dic)
        //                {
        //                    result += con.Execute(sql.Key, sql.Value, transaction);
        //                }

        //                transaction.Commit();
        //                return result;
        //            }
        //            catch (Exception ex)
        //            {
        //                transaction.Rollback();
        //                return 0;
        //            }
        //        }
        //    }
        //}
    }
}