using System;
using System.Data;
using System.Collections;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Windows.Forms;
using System.Data.SqlClient;
using SixGroup_Knowledge.PopView;

namespace SixGroup_Knowledge.Class
{
    public class SQLHelper
    {
        private string strConn="";//连接字符串
        private SqlConnection sqlConn = null;//连接通道

        public SQLHelper()//构造函数
        {
            try
            {
                //获得sql连接字符串
                //strConn = String.Format(ConfigurationManager.ConnectionStrings["connstr"].ConnectionString, Application.StartupPath + "\\DataBase\\knowledege.mdf");
                strConn = ConfigurationManager.ConnectionStrings["sqlserverstr"].ConnectionString;
                //MessageForm.show(strConn);
            }
            catch (Exception ex)
            {
                MessageForm.show("数据库连接字符串错误"+ex.Message,MessageForm.ERROR);
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        private void openConn()
        {
            //如果sqlconnection为空则获得
            if (sqlConn == null)
            {
                //获得sqlconnenction对象
                sqlConn = new SqlConnection(strConn);
                try
                {
                    //打开数据库连接通道
                    sqlConn.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("系统不支持本地数据库版本，即将使用SQL Server连接。" + ex.Message);
                    //当系统不支持本地数据库时使用 SQL SERVER服务器数据库
                    try
                    {
                        strConn = ConfigurationManager.ConnectionStrings["sqlserverstr"].ConnectionString;
                        sqlConn = new SqlConnection(strConn);
                        sqlConn.Open();
                    }
                    catch (Exception x)
                    {
                        MessageBox.Show(x.Message);
                    }
                }
            }
            //如果sqlconnection连接为关闭状态则重新打开连接
            else if (sqlConn.State == ConnectionState.Closed)
            {
                try
                {
                    sqlConn.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("系统不支持本地数据库版本，请使用SQL Server服务器数据。");
                }
            }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        private void closeConn()
        {
            //sql连接不为空并且状态为打开就关闭
            if (sqlConn != null && sqlConn.State == ConnectionState.Open)
            {
                try
                {
                    sqlConn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }
        /// <summary>
        /// 构造操作命令
        /// </summary>
        /// <param name="cmdText">带参命令</param>
        /// <param name="param">参数数组</param>
        /// <param name="values">参数值数组</param>
        /// <returns></returns>
        private SqlCommand CreateCommand(string cmdText, string[] param, object[] values)
        {
            SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
            for (int i = 0; i < param.Length; i++)
            {
                myCmd.Parameters.AddWithValue(param[i], values[i]);
            }

            return myCmd;
        }

        public SqlDataReader ExecuteReader(string cmdText)
        {
            return ExecuteReader(cmdText,null,null);
        }
        /// <summary>
        /// 根据SQL指令返回相应查询阅读器，在阅读器使用完后请及时关闭
        /// </summary>
        /// <param name="cmdText">查询语句</param>
        /// <param name="param">参数列表，无参可设置为null</param>
        /// <param name="values">参数值列表，只有当参数不为空时有效</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReader(string cmdText, string[] param, object[] values)
        {
            openConn();
            SqlCommand myCmd;
            if (param != null)
            {
                myCmd = this.CreateCommand(cmdText, param, values);
            }
            else
            {
                myCmd = new SqlCommand(cmdText, sqlConn);
            }
            try
            {
                return myCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据存储过程返回相应查询阅读器，在阅读器使用完后请及时关闭
        /// </summary>
        /// <param name="cmdText">存储过程名</param>
        /// <param name="parms">参数列表</param>
        /// <returns></returns>
        public SqlDataReader ExecuteReaderBySP(string cmdText, SqlParameter[] parms)
        {
            openConn();
            SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
            myCmd.CommandType = CommandType.StoredProcedure;
            if (parms != null)
            {
                myCmd.Parameters.AddRange(parms);
            }
            try
            {
                //保证对象被关闭时 其依赖的连接也被关闭
                return myCmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int ExecuteNoneQuery(string cmdText)
        {
            return ExecuteNoneQuery(cmdText,null,null);
        }

        /// <summary>
        /// 根据SQL指令返回受影响行数，主要用于数据库的更新、插入、删除等操作
        /// </summary>
        /// <param name="cmdText">sql命令语句</param>
        /// <param name="param">参数数组，若没有参数可以设置为空</param>
        /// <param name="values">参数值数组，只有当param不为空时有效</param>
        /// <returns></returns>
        public int ExecuteNoneQuery(string cmdText, string[] param, object[] values)
        {
            openConn();
            SqlCommand myCmd;
            if (param != null)
            {
                myCmd = this.CreateCommand(cmdText, param, values);
            }
            else
            {
                myCmd = new SqlCommand(cmdText, sqlConn);
            }
            try
            {
                return myCmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                return 0;
            }
            finally
            {
                closeConn();
            }
        }

        /// <summary>
        /// 根据SQL指令返回第一行第一列结果
        /// </summary>
        /// <param name="cmdText">sql命令语句</param>
        /// <param name="param">参数数组，若没有参数可以设置为空</param>
        /// <param name="values">参数值数组，只有当param不为空时有效</param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, string[] param, object[] values)
        {
            openConn();
            SqlCommand myCmd;
            if (param != null)
            {
                myCmd = this.CreateCommand(cmdText, param, values);
            }
            else
            {
                myCmd = new SqlCommand(cmdText, sqlConn);
            }
            try
            {
                return myCmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                closeConn();
            }
        }
        /// <summary>
        /// 带事务执行存储过程，该方法主要用于执行用于数据维护类的存储过程执行
        /// </summary>
        /// <param name="cmdText">存储过程名称</param>
        /// <param name="parms">SQL参数数组</param>
        public int ExecuteNoneQueryBySP(string cmdText, SqlParameter[] parms)
        {
            openConn();
            SqlTransaction tran = sqlConn.BeginTransaction();
            SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
            myCmd.CommandType = CommandType.StoredProcedure;
            if (parms != null)
            {
                myCmd.Parameters.AddRange(parms);
            }
            myCmd.Transaction = tran;
            try
            {
                int result = myCmd.ExecuteNonQuery();
                tran.Commit();
                return result;
            }
            catch (Exception ex)
            {
                tran.Rollback();
                throw ex;
            }
            finally
            {
                closeConn();
            }
        }

        public DataSet FillDataSet(string cmdText)
        {
            return FillDataSet(cmdText,null,null);
        }

        /// <summary>
        /// 根据命令语句返回数据集
        /// </summary>
        /// <param name="cmdText">命令语句</param>
        /// <param name="param">参数数组，若没有参数可以设置为空</param>
        /// <param name="values">参数值数组，只有当param不为空时有效</param>
        /// <returns></returns>
        public DataSet FillDataSet(string cmdText, string[] param, object[] values)
        {
            openConn();
            SqlCommand myCmd;
            if (param != null)
            {
                myCmd = this.CreateCommand(cmdText, param, values);
            }
            else
            {
                myCmd = new SqlCommand(cmdText, sqlConn);
            }
            SqlDataAdapter myAdp = new SqlDataAdapter(myCmd);
            DataSet ds = new DataSet();
            try
            {
                myAdp.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                closeConn();
            }
        }

        /// <summary>
        /// 执行特定存储过程并返回查询后的数据结果，该方法用于执行查询类的存储过程
        /// </summary>
        /// <param name="cmdText">存储过程名</param>
        /// <param name="parms">SQL参数数组,若没有参数可以设置为空</param>
        /// <returns></returns>
        public DataSet FillDataSetBySP(string cmdText, SqlParameter[] parms)
        {
            openConn();
            SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
            myCmd.CommandType = CommandType.StoredProcedure;
            if (parms != null)
            {
                myCmd.Parameters.AddRange(parms);
            }
            SqlDataAdapter myAdp = new SqlDataAdapter(myCmd);
            DataSet ds = new DataSet();
            try
            {
                myAdp.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                closeConn();
            }
        }
        /// <summary>
        /// 执行存储过程返回输出参数
        /// </summary>
        /// <param name="cmdText">存储过程名</param>
        /// <param name="parms">参数数组</param>
        /// <returns>包含所有输出值的ArrayList</returns>
        public ArrayList ExecuteSp(string cmdText, SqlParameter[] parms)
        {
            openConn();
            SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
            myCmd.CommandType = CommandType.StoredProcedure;
            if (parms != null)
            {
                myCmd.Parameters.AddRange(parms);
            }
            try
            {
                myCmd.ExecuteNonQuery();
                ArrayList al = new ArrayList();
                for (int i = 0; i < parms.Length; i++)
                {
                    if (parms[i].Direction == ParameterDirection.Output)
                    {
                        al.Add(parms[i]);
                    }
                }
                return al;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                closeConn();
            }
        }
        /// <summary>
        /// 将数据批量加载到数据库的表中
        /// </summary>
        /// <param name="dt">含有数据的数据表</param>
        /// <param name="tableName">目标数据表</param>
        public void BulkToDB(DataTable dt, string tableName)
        {
            openConn();
            SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConn);
            bulkCopy.DestinationTableName = tableName;
            bulkCopy.BatchSize = dt.Rows.Count;
            try
            {
                openConn();
                bulkCopy.WriteToServer(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                closeConn();
                if (bulkCopy != null)
                    bulkCopy.Close();
            }
        }

        //#region 批处理操作
        ///// <summary>
        ///// 批量数据导入操作
        ///// </summary>
        ///// <param name="dt">要批量导入的数据表</param>
        ///// <param name="destTableName">目标表名</param>
        ///// <param name="columnMappings">列映射集合</param>
        //public void BulkInsert(DataTable dt, string destTableName, List<SqlBulkCopyColumnMapping> columnMappings)
        //{
        //    SqlBulkCopy bulkCopy = new SqlBulkCopy(this.sqlConnection);
        //    bulkCopy.DestinationTableName = destTableName;
        //    bulkCopy.BatchSize = dt.Rows.Count;
        //    foreach (SqlBulkCopyColumnMapping map in columnMappings)
        //    {
        //        bulkCopy.ColumnMappings.Add(map);
        //    }
        //    try
        //    {
        //        bulkCopy.WriteToServer(dt);
        //    }
        //    catch (Exception ex)
        //    {
        //        throw ex;
        //    }
        //    finally
        //    {
        //        this.CloseConnection();
        //        bulkCopy.Close();
        //    }
        //}
        //#endregion
    }
}
