/******************************************************************************
 * 
 * 文件名:  TAccess.cs
 * 文件功能描述:  数据库访问底层、针对返回一行记录DataRow、一个数据集DataSet、DataTable,
 以及增加、修改、删除返回受影响的行数。
 * 作    者:  liangjw
 * 创建标示:  2011-12-21
 * 邮    箱： liangjw0504@163.com
 * 版权所有： Copyright (C) 【2013 liangjw】
 * 版本号:    V1.0
 * 备    注: 无
 * 修改者:   无
 * 修改描述: 无
 * 修改标示: 无
 * 
*******************************************************************************/
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
using System.Text.RegularExpressions;
using System.Collections;

    public class TAccess
    {
        
        #region 获取数据库连接字符串
        static string connectionString = string.Empty;
        public static string GetConnstr()
        {
            //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.		
            connectionString = ConfigurationManager.ConnectionStrings["DBConnectionString"].ConnectionString;
                return connectionString;
        }
        #endregion

        #region 获取连接字符串进行数据判断
        /// <summary>
        /// 获取连接字符串OleDbConn进行数据判断，返回连接
        /// </summary>
        /// <param name="connstr">数据库连接字符串</param>
        /// <returns>返回数据连接字符串</returns>
        public static OleDbConnection OleDbConn(string connstr)
        {
            OleDbConnection conn = new OleDbConnection();
            if (connstr.ToString() == "")
            {
                connstr = GetConnstr();
            }
            conn.ConnectionString = connstr;
            return conn;
        }
        #endregion

        #region 创建数据库连接命令,判断数据字符数组
        /// <summary>
        /// 创建数据库连接命令,判断数据字符数组
        /// </summary>
        /// <param name="conn">连接字符串</param>
        /// <param name="OleDb">OleDb语句</param>
        /// <param name="p">字符串数组</param>
        /// <param name="s">对象</param>
        /// <returns>返回命令对象</returns>
        public static OleDbCommand OleDbCmd(OleDbConnection conn, string OleDb, string[] p, params object[] s)
        {
            OleDbCommand cmd = new OleDbCommand(OleDb, conn);
            if (p == null || p.Length == 0)
            {
                Regex re = new Regex(@"@[a-zA-Z0-9]+", RegexOptions.IgnoreCase);
                Match m = re.Match(OleDb);
                int i = 0;
                List<string> str = new List<string>();
                while (m.Success)
                {
                    if (i == s.Length)
                    {
                        break;
                    }
                    if (!str.Contains(m.Value))
                    {
                        cmd.Parameters.AddWithValue(m.Value, s[i]);
                        str.Add(m.Value);
                        i++;
                    }
                    m = m.NextMatch();
                }
            }
            else
            {
                for (int i = 0; i < p.Length; i++)
                {
                    if (i == s.Length)
                    {
                        break;
                    }
                    cmd.Parameters.AddWithValue(p[i], s[i]);
                }
            }

            return cmd;
        }
        #endregion

        #region 得到数据库执行错误信息
        /// <summary>
        /// 得到数据库执行错误信息
        /// </summary>
        /// <param name="ex">错误信息</param>
        /// <param name="OleDb">执行OleDb语句</param>
        /// <param name="p"></param>
        /// <param name="s"></param>
        public static void getError(Exception ex, string OleDb, string[] p, object[] s)
        {
            string msg = ex.Message + "\r\n\r\nOleDb(ProcedureName)：\r\n" + OleDb;
            if (p != null) msg += "\r\n\r\nParams：\r\n" + string.Join(",", p);
            if (s != null) msg += "\r\n\r\nValues：\r\n" + string.Join("\r\n", Array.ConvertAll(s, new Converter<object, string>(c => c.ToString())));
            throw new Exception(msg);
        }
        #endregion

        #region 执行非查询ExecuteNonQuery返回受影响的行数，增加，修改，删除
        /// <summary>
        ///  执行非查询ExecuteNonQuery返回受影响的行数，增加，修改，删除
        /// </summary>
        /// <param name="OleDb"></param>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string OleDb, params object[] s)
        {
            return ExecuteNonQueryWithOther(OleDb, "", null, s);
        }

        public static int ExecuteNonQuery(string OleDb, string[] p, params object[] s)
        {
            return ExecuteNonQueryWithOther(OleDb, "", p, s);
        }

        public static int ExecuteNonQueryWithOther(string OleDb, string connstr, params object[] s)
        {
            return ExecuteNonQueryWithOther(OleDb, connstr, null, s);
        }

        public static int ExecuteNonQueryWithOther(string OleDb, string connstr, string[] p, params object[] s)
        {
            OleDbConnection conn = OleDbConn(connstr);
            conn.Open();
            OleDbCommand cmd = OleDbCmd(conn, OleDb, p, s);
            int result = 0;
            try
            {
                result = cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                getError(ex, OleDb, p, s);
            }
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return result;
        }
        #endregion

        #region 执行Sql语句返回DataRow一行数据信息
        /// <summary>
        /// 执行Sql语句返回DataRow一行数据信息
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <param name="s">参数集合</param>
        /// <returns></returns>
        public static DataRow ExecDr(string sql, params object[] s)
        {
            return ExecDrWithOther(sql, "", null, s);
        }
        public static DataRow ExecDr(string sql, string[] p, params object[] s)
        {
            return ExecDrWithOther(sql, "", p, s);
        }
        public static DataRow ExecDrWithOther(string sql, string connstr, params object[] s)
        {
            return ExecDrWithOther(sql, connstr, null, s);
        }
        public static DataRow ExecDrWithOther(string sql, string connstr, string[] p, params object[] s)
        {
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter();
            OleDbConnection conn = OleDbConn(connstr);
            conn.Open();
            OleDbCommand cmd = OleDbCmd(conn, sql, p, s);
            da.SelectCommand = cmd;
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                getError(ex, sql, p, s);
            }
            DataRow dr = null;
            try
            {
                dr = ds.Tables[0].Rows[0];
            }
            catch { }
            da.Dispose();
            ds.Dispose();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();
            return dr;
        }
        #endregion

        #region 执行OleDb语句返回DataTable数据信息
        /// <summary>
        /// 执行OleDb语句返回DataTable数据信息
        /// </summary>
        /// <param name="OleDb"> 执行查询OleDb语句</param>
        /// <param name="s">获取传递OleDb参数集合</param>
        /// <returns>根据条件返回一个dataTable</returns>
        public static DataTable ExecDt(string OleDb, params object[] s)
        {
            return ExecDtWithOther(OleDb, "", null, s);
        }
        public static DataTable ExecDt(string OleDb, string[] p, params object[] s)
        {
            return ExecDtWithOther(OleDb, "", p, s);
        }
        public static DataTable ExecDtWithOther(string OleDb, string connstr, params object[] s)
        {
            return ExecDtWithOther(OleDb, connstr, null, s);
        }
        public static DataTable ExecDtWithOther(string OleDb, string connstr, string[] p, params object[] s)
        {
            DataSet ds = new DataSet();
            OleDbDataAdapter da = new OleDbDataAdapter();
            OleDbConnection conn = OleDbConn(connstr);
            conn.Open();
            OleDbCommand cmd = OleDbCmd(conn, OleDb, p, s);
            da.SelectCommand = cmd;
            try
            {
                da.Fill(ds);
            }
            catch (Exception ex)
            {
                getError(ex, OleDb, p, s);
            }
            DataTable dt = null;
            try
            {
                dt = ds.Tables[0];
            }
            catch { }
            da.Dispose();
            ds.Dispose();
            cmd.Dispose();
            conn.Close();
            conn.Dispose();

            return dt;
        }
        #endregion

        #region 执行多条SQL语句，实现数据库事务。
        /// <summary>
        /// 执行多条SQL语句，实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">多条SQL语句</param>		
        public static void ExecuteSqlTran(ArrayList SQLStringList)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = conn;
                OleDbTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < SQLStringList.Count; n++)
                    {
                        string strsql = SQLStringList[n].ToString();
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.OleDb.OleDbException E)
                {
                    tx.Rollback();
                    throw new Exception(E.Message);
                }
            }
        }
        #endregion
    }
