MysqlHelper

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using MySql;
using System.Configuration;
using System.Data.SqlClient;
using PwmeasModel;

namespace ClsPublicHelper
{
    public partial class MysqlHelper
    {
        private static string connStr = ConfigurationManager.ConnectionStrings["connstr"].ToString();
        //执行命令方法：insert ,update,delete
        /// <summary>
        /// 执行命令的方法insert ,update,delete
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static int ExecuteNonQury(string sql, params MySqlParameter[] ps)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {//创建连接对象
                MySqlCommand cmd = new MySqlCommand(sql, conn);//创建conmmand对象
                cmd.Parameters.AddRange(ps);//添加参数
                conn.Open();//打开连接                
                return cmd.ExecuteNonQuery();//执行命令，返回受影响的行数
            }

        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="lastid">最后插入的id值</param>
        /// <param name="ps"></param>
        /// <returns></returns>
        public static int ExecuteNonQury( string sql, out long lastid ,params MySqlParameter[] ps )
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {//创建连接对象
                MySqlCommand cmd = new MySqlCommand(sql, conn);//创建conmmand对象
                cmd.Parameters.AddRange(ps);//添加参数
                conn.Open();//打开连接   
                var Updatenum= cmd.ExecuteNonQuery();
                lastid = cmd.LastInsertedId;
                return Updatenum;//执行命令，返回受影响的行数
            }

        }
        /// <summary>
        /// 获取首行首列值的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns></returns>

        public static object ExcecuteScalar(string sql, params MySqlParameter[] ps)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                cmd.Parameters.AddRange(ps);
                conn.Open();
                
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// //获取结果集
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="ps"></param>
        /// <returns></returns>

        public static DataTable GetDataTaable(string sql, params MySqlParameter[] ps)
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                //构造适配器对象
                MySqlDataAdapter adapter = new MySqlDataAdapter(sql, conn);
                DataTable dt = new DataTable();//构造数据表，用于接受查询结果
                adapter.SelectCommand.Parameters.AddRange(ps);//添加参数
                ConnectionState C = conn.State;
                adapter.Fill(dt);//执行结果
                return dt;//返回结果集
               
            }
        }
        public static bool ExcuteTransactionSql( List<string> listSql )
        {
            using (MySqlConnection conn = new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlTransaction transtion = conn.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = transtion;
                try
                {
                    for (int i = 0; i < listSql.Count; i++)
                    {
                        string sql = listSql[i];
                        if (sql.Trim().Length > 1)
                        {
                            cmd.CommandText = sql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    transtion.Commit();
                    conn.Close();
                    return true;
                }
                catch (Exception)
                {

                    transtion.Rollback();
                    conn.Close();
                    return false;
                }
            }
        }
        public static  bool ExcuteTransactionSql(List<TransactionModel> sqls )
        {
            List< Dictionary<string, List<MySqlParameter>> >dt = new List<Dictionary<string, List<MySqlParameter>>>();
            using (MySqlConnection conn=new MySqlConnection(connStr))
            {
                conn.Open();
                MySqlTransaction transtion = conn.BeginTransaction();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.Transaction = transtion;
                try
                {
                    for (int i = 0; i < sqls.Count; i++)
                    {
                        var sql = sqls[i].sql;
                        var param = sqls[i].sparam.ToArray();
                        if (sql.Trim().Length > 1)
                        {
                            cmd.CommandText = sql;
                            cmd.Parameters.AddRange(param);
                            cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                    transtion.Commit();
                    conn.Close();
                    return true;
                }
                catch (Exception )
                {

                    transtion.Rollback();
                    conn.Close();
                    return false;
                }
            }
        }
        /// <summary>
        /// 大批量数据插入
        /// </summary>
        /// <param name="connectionString">数据库连接字符串</param>
        /// <param name="table">数据表</param>
        public static void BulkInsert(  DataTable table )
        {
            if (string.IsNullOrEmpty(table.TableName)) throw new Exception("DataTable.TableName属性不能为空");
            using (SqlBulkCopy bulk = new SqlBulkCopy(connStr))
            {
                bulk.BatchSize = 1000 ;
                bulk.BulkCopyTimeout = 0;
                bulk.DestinationTableName = table.TableName;
                foreach (DataColumn col in table.Columns)
                {
                    bulk.ColumnMappings.Add(col.ColumnName, col.ColumnName);
                }
                bulk.WriteToServer(table);
                bulk.Close();
            }
        }
        public static bool IsConnection(out string ErrStr)
        {
            try
            {
                using (MySqlConnection conn=new MySqlConnection(connStr))
                {
                    conn.Open();
                    ErrStr = null;
                    return true;
                }
            }
            catch (Exception ex)
            {

                ErrStr = ex.ToString();
                return false;
            }
        }
    }
}

public class TransactionModel
    {
        public string sql { get; set; }
        public List<MySqlParameter> sparam
        { get; set; }
    }