using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;

namespace ErpServer
{
    public class AccessHelper
    {
        public AccessHelper()
        {
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sqlstr"></param>
        public static int excuteSql(string connStr, string sqlstr)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                try
                {
                    conn.Open();
                    OleDbCommand comm = new OleDbCommand();
                    comm.Connection = conn;
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    return comm.ExecuteNonQuery();
                }
                catch (Exception e)
                {
                    return 0;
                    throw new Exception(connStr + "|" + e.Message);
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static void ExecuteSqlTran(string connStr, ArrayList SQLStringList)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                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);
                }
            }
        }

        public static void ExecuteMultiQuery(string conn, List<string> lst)
        {
            using (OleDbConnection inconn = new OleDbConnection(conn))
            {
                inconn.Open();
                OleDbTransaction myTrans = inconn.BeginTransaction();
                OleDbCommand incmd = inconn.CreateCommand();
                incmd.Transaction = myTrans;
                foreach (string sql in lst)
                {
                    incmd.CommandText = sql;
                    incmd.ExecuteNonQuery();
                }
                myTrans.Commit();
            }
        }

        public static object excuteScalar(string connStr, string sqlstr)
        {
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                try
                {
                    conn.Open();
                    OleDbCommand comm = new OleDbCommand();
                    comm.Connection = conn;
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    object val = comm.ExecuteScalar();
                    return val;
                }
                catch (Exception e)
                {
                    conn.Close();
                    conn.Dispose();
                    return null;
                    throw new Exception(connStr + "|" + e.Message);
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        /// <summary>
        /// 返回指定sql语句的OleDbDataReader对象，使用时请注意关闭这个对象。
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public static OleDbDataReader dataReader(string connStr, string sqlstr)
        {
            OleDbDataReader dr = null;
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                try
                {
                    conn.Open();
                    OleDbCommand comm = new OleDbCommand();
                    comm.Connection = conn;
                    comm.CommandText = sqlstr;
                    comm.CommandType = CommandType.Text;
                    dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
                    return dr;
                }
                catch
                {
                    dr.Close();
                    return null;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }
        /// <summary>
        /// 返回指定sql语句的datatable
        /// </summary>
        /// <param name="sqlstr"></param>
        /// <returns></returns>
        public static DataTable dataTable(string connStr, string sqlstr)
        {
            DataTable dt = new DataTable();

            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                try
                {
                    conn.Open();
                    OleDbCommand comm = new OleDbCommand();
                    comm.Connection = conn;
                    comm.CommandType = CommandType.Text;
                    comm.CommandText = sqlstr;
                    OleDbDataAdapter da = new OleDbDataAdapter(comm);
                    da.Fill(dt);
                }
                catch (Exception e)
                {
                    conn.Close();
                    conn.Dispose();
                    return null;
                    throw new Exception(sqlstr + "|" + e.Message);
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
                return dt;
            }
        }


        #region ExecuteDataset
        public static DataSet ExecuteDataset(string connectionString, string commandText)
        {
            return ExecuteDataset(connectionString, commandText, (OleDbParameter[])null);
        }

        public static DataSet ExecuteDataset(string connectionString, string commandText, params OleDbParameter[] commandParameters)
        {
            if (null == connectionString && string.Empty == connectionString) throw new ArgumentNullException("connectionString");
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                return ExecuteDataset(connection, commandText, commandParameters);
            }
        }

        public static DataSet ExecuteDataset(OleDbConnection connection, string commandText)
        {
            return ExecuteDataset(connection, commandText, (OleDbParameter[])null);
        }

        public static DataSet ExecuteDataset(OleDbConnection connection, string commandText, params OleDbParameter[] commandParameters)
        {
            if (null == connection) throw new ArgumentNullException("connection");
            try
            {
                OleDbCommand cmd = new OleDbCommand();
                bool mustCloseConnection = false;
                PrepareCommand(cmd, connection, (OleDbTransaction)null, commandText, commandParameters, out mustCloseConnection);
                using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                {
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    cmd.Parameters.Clear();
                    if (mustCloseConnection)
                        connection.Close();
                    return ds;
                }
            }
            catch
            {
                return null;
            }
            finally
            {
                if (connection != null)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
        }

        public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText)
        {
            return ExecuteDataset(transaction, commandText, (OleDbParameter[])null);
        }

        public static DataSet ExecuteDataset(OleDbTransaction transaction, string commandText, params OleDbParameter[] commandParameters)
        {
            if (null == transaction) throw new ArgumentNullException("transaction");
            if (null != transaction && null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
            OleDbCommand cmd = new OleDbCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, transaction.Connection, transaction, commandText, commandParameters, out mustCloseConnection);
            using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
            {
                DataSet ds = new DataSet();
                da.Fill(ds);
                cmd.Parameters.Clear();
                return ds;

            }
        }
        #endregion ExecuteDataset

        private static void PrepareCommand(OleDbCommand command, OleDbConnection connection, OleDbTransaction transaction, string commandText, OleDbParameter[] commandParameters, out bool mustCloseConnection)
        {
            if (null == command) throw new ArgumentNullException("command");
            if (null == commandText || string.Empty == commandText) throw new ArgumentNullException("commandText");
            if (ConnectionState.Closed == connection.State)
            {
                connection.Open();
                mustCloseConnection = true;
            }
            else
            {
                mustCloseConnection = false;
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (null != transaction)
            {
                if (null == transaction.Connection) throw new ArgumentException("transaction对象commited和rollbacked需要一个已打开的事务", "transaction");
                command.Transaction = transaction;
            }
            command.CommandType = CommandType.Text;
            if (null != commandParameters)
            {
                AttachParameters(command, commandParameters);
            }
        }

        private static void AttachParameters(OleDbCommand command, OleDbParameter[] commandParameters)
        {
            if (null == command) throw new ArgumentNullException("command");
            if (null != commandParameters)
            {
                foreach (OleDbParameter p in commandParameters)
                {
                    if (null != p)
                    {
                        if ((ParameterDirection.Input == p.Direction || ParameterDirection.InputOutput == p.Direction) && (null == p.Value))
                        {
                            p.Value = DBNull.Value;
                        }
                        command.Parameters.Add(p);
                    }

                }
            }

        }
    }
}