znyc.gps_service/GPSBusiness/Sql/SqlHelp.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Configuration; 
using log4net;
using System.ComponentModel;
using MySql.Data.MySqlClient;

namespace GPSBusiness.Sql
{
    public class SqlHelp  
    {
        //"Data Source=数据库地址;Initial Catalog=数据库名称;Persist Security Info=True;User ID=帐号;Password=密码";
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["ZNYCProjectGps"].ToString(); 
        //根据sql语句返回一个DataSet
        #region GetDataSet
        public static DataSet GetDataSet(string sql)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.Text, sql);
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = sqlcom;
                DataSet ds = new DataSet();
                sda.Fill(ds);
                sqlcom.Parameters.Clear();
                return ds;
            }
        }
        #endregion
        //根据sql语句返回一个MySqlDataAdapter
        #region ExecMySqlDataAdapter
        public static MySqlDataReader ExecMySqlDataAdapter(string sql)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            try
            {
                PrepareCommand(sqlcom, conn, CommandType.Text, sql);
                MySqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
                sqlcom.Parameters.Clear();
                return sdr;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }
        #endregion
        //执行sql语句，返回影响行数
        #region ExecCommand
        public static int ExecCommand(string sql)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.Text, sql);
                int rtn = sqlcom.ExecuteNonQuery();
                sqlcom.Parameters.Clear();
                return rtn;
            }
        }
        #endregion
        //执行SQL语句，无返回值
        #region ExecNon
        public static void ExecNon(string sql)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.Text, sql);
                sqlcom.ExecuteNonQuery();
                sqlcom.Parameters.Clear();
            }
        }
        #endregion
        //根据sql语句返回查询结果的第一行
        #region ExecuteScalar
        public static object ExecuteScalar(string sql)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.Text, sql);
                object rtn = sqlcom.ExecuteScalar();
                sqlcom.Parameters.Clear();
                return rtn;
            }
        }
        #endregion
        //执行存储过程无返回值，SQL语句含有参数
        #region ExecSPCommand
        public static void ExecSPCommand(string sql, System.Data.IDataParameter[] paramers)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql);
                foreach (System.Data.IDataParameter paramer in paramers)
                {
                    sqlcom.Parameters.Add(paramer);
                }
                sqlcom.ExecuteNonQuery();
                sqlcom.Parameters.Clear();
            }
        }
        #endregion
        //执行存储过程无返回值，SQL语句不含有参数
        #region ExecSPCommand2
        public static void ExecSPCommand2(string sql)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql);
                sqlcom.ExecuteNonQuery();
                sqlcom.Parameters.Clear();
            }
        }
        #endregion
        //执行存储过程，返回MySqlDataAdapter，SQL语句含有参数
        #region ExecSPMySqlDataAdapter
        public static MySqlDataReader ExecSPMySqlDataAdapter(string sql, System.Data.IDataParameter[] paramers)
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            MySqlCommand sqlcom = new MySqlCommand();
            try
            {
                PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql);
                foreach (System.Data.IDataParameter paramer in paramers)
                {
                    sqlcom.Parameters.Add(paramer);
                }

                MySqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
                sqlcom.Parameters.Clear();
                return sdr;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }
        #endregion
        //执行存储过程，SQL语句含有参数
        #region ExecSPSqlExecuteNonQuery
        public static int  ExecSPSqlExecuteNonQuery(string sql, System.Data.IDataParameter[] paramers)
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            MySqlCommand sqlcom = new MySqlCommand();
            try
            {
                PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql);
                foreach (System.Data.IDataParameter paramer in paramers)
                {
                    sqlcom.Parameters.Add(paramer);
                }
                sqlcom.ExecuteNonQuery();
                return Convert.ToInt32(sqlcom.Parameters["@Id"].Value);
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }
        #endregion
        //执行存储过程，返回MySqlDataAdapter，SQL语句不含有参数
        #region ExecSPMySqlDataAdapter2
        public static MySqlDataReader ExecSPMySqlDataAdapter2(string sql)
        {
            MySqlConnection conn = new MySqlConnection(ConnectionString);
            MySqlCommand sqlcom = new MySqlCommand();
            try
            {
                PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql);
                MySqlDataReader sdr = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
                sqlcom.Parameters.Clear();
                return sdr;
            }
            catch (Exception ex)
            {
                conn.Close();
                throw ex;
            }
        }
        #endregion
        //执行存储过程返回DataSet类型，SQL语句含有参数
        #region ExecSPDataSet
        public static DataSet  ExecSPDataSet(string sql, System.Data.IDataParameter[] paramers)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql);
                foreach (System.Data.IDataParameter paramer in paramers)
                {
                    sqlcom.Parameters.Add(paramer);
                }
                MySqlDataAdapter sda = new MySqlDataAdapter();
                sda.SelectCommand = sqlcom;
                DataSet ds = new DataSet();
                sda.Fill(ds);
                sqlcom.Parameters.Clear();
                return ds;
            }
        }
        #endregion
        //执行存储过程返回DataSet类型，SQL语句不含有参数
        #region ExecSPDataSet2
        public static DataSet ExecSPDataSet2(string sql)
        {
            MySqlCommand sqlcom = new MySqlCommand();
            using (MySqlConnection conn = new MySqlConnection(ConnectionString))
            {
                PrepareCommand(sqlcom, conn, CommandType.StoredProcedure, sql);
                MySqlDataAdapter sda = new MySqlDataAdapter
                {
                    SelectCommand = sqlcom
                };
                DataSet ds = new DataSet();
                sda.Fill(ds);
                sqlcom.Parameters.Clear();
                return ds;
            }
        }
        #endregion
        //
        // 执行存储过程返回DataSet类型，SQL语句含有参数
        #region PrepareCommand
        /// <summary>
        /// 执行存储过程返回DataSet类型，SQL语句含有参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="conn"></param>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, CommandType cmdType, string cmdText)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
        } 
        #endregion
        // 批量入库
        public static bool BulkInsert<T>(string tableName, IList<T> list)
        {
            using (var bulkCopy = new SqlBulkCopy(ConnectionString))
            {
                bulkCopy.BatchSize = list.Count;
                bulkCopy.DestinationTableName = tableName;
                var table = new DataTable();
                var props = TypeDescriptor.GetProperties(typeof(T), new Attribute[] { })
                 .Cast<PropertyDescriptor>()
                 .ToArray();
                foreach (var propertyInfo in props)
                {
                    bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
                    table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
                }
                var values = new object[props.Length];
                foreach (var item in list)
                {
                    for (var i = 0; i < values.Length; i++)
                    {
                        values[i] = props[i].GetValue(item);
                    }
                    table.Rows.Add(values);
                }
                bulkCopy.WriteToServer(table);
            }
            return true;
        }
    }
}