using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Data.OleDb;

namespace TestAccessDB

{

    public class DBHelper

    {

        protected static OleDbConnection conn = new OleDbConnection();

        protected static OleDbCommand comm = new OleDbCommand();

        public DBHelper()

        {

        }

        static string dbFile = AppDomain.CurrentDomain.BaseDirectory + "\\goods.mdb";

        private static void openConnection()

        {

            if (conn.State == ConnectionState.Closed)

            {

                // conn.ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + AppDomain.CurrentDomain.BaseDirectory + dbFile + ";Jet OLEDB:Database PassWord=sa";

                // conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ dbFile +";Persist Security Info=False;Jet OLEDB:Database Password=sa;";

                conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbFile + ";Persist Security Info=False;";

                comm.Connection = conn;

                try

                {

                    conn.Open();

                }

                catch (Exception e)

                { throw new Exception(e.Message); }

            }

        }

        private static void closeConnection()

        {

            if (conn.State == ConnectionState.Open)

            {

                conn.Close();

                conn.Dispose();

                comm.Dispose();

            }

        }

        /// <summary>

        /// 执行sql语句

        /// </summary>

        /// <param name="sqlstr"></param>

        public static int ExecuteNonQuery(string sqlstr)

        {

            int i = 0;

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

               i= comm.ExecuteNonQuery();

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return i;

        }

        /// <summary>

        /// 执行sql语句

        /// </summary>

        /// <param name="sqlstr"></param>

        public static object  executeScalarSql(string sqlstr)

        {

            object o;

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                 o= comm.ExecuteScalar();

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return o;

        }

        public static int batchInsert(int count)

        {

            int cnt = 0;

            try

            {

                for (int i = 1; i <= count; i++)

                {

                    string sqlstr = "insert into article(sourceID,[user],updateDate) values ('454545434','qwertteeeew_gfhfghgfh',#" + DateTime.Now + "#)";

                    openConnection();

                    comm.CommandType = CommandType.Text;

                    comm.CommandText = sqlstr;

                    cnt += comm.ExecuteNonQuery();

                }

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return cnt;

        }

        /// <summary>

        /// 返回指定sql语句的OleDbDataReader对象，使用时请注意关闭这个对象。

        /// </summary>

        /// <param name="sqlstr"></param>

        /// <returns></returns>

        public static OleDbDataReader dataReader(string sqlstr)

        {

            OleDbDataReader dr = null;

            try

            {

                openConnection();

                comm.CommandText = sqlstr;

                comm.CommandType = CommandType.Text;

                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

            }

            catch

            {

                try

                {

                    dr.Close();

                    closeConnection();

                }

                catch { }

            }

            return dr;

        }

        /// <summary>

        /// 返回指定sql语句的OleDbDataReader对象,使用时请注意关闭

        /// </summary>

        /// <param name="sqlstr"></param>

        /// <param name="dr"></param>

        public static void dataReader(string sqlstr, ref OleDbDataReader dr)

        {

            try

            {

                openConnection();

                comm.CommandText = sqlstr;

                comm.CommandType = CommandType.Text;

                dr = comm.ExecuteReader(CommandBehavior.CloseConnection);

            }

            catch

            {

                try

                {

                    if (dr != null && !dr.IsClosed)

                        dr.Close();

                }

                catch

                {

                }

                finally

                {

                    closeConnection();

                }

            }

        }

        /// <summary>

        /// 返回指定sql语句的dataset

        /// </summary>

        /// <param name="sqlstr"></param>

        /// <returns></returns>

        public static DataSet dataSet(string sqlstr)

        {

            DataSet ds = new DataSet();

            OleDbDataAdapter da = new OleDbDataAdapter();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(ds);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return ds;

        }

        /// <summary>

        /// 返回指定sql语句的dataset

        /// </summary>

        /// <param name="sqlstr"></param>

        /// <param name="ds"></param>

        public static void dataSet(string sqlstr, ref DataSet ds)

        {

            OleDbDataAdapter da = new OleDbDataAdapter();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(ds);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

        }

        /// <summary>

        /// 返回指定sql语句的datatable

        /// </summary>

        /// <param name="sqlstr"></param>

        /// <returns></returns>

        public static DataTable dataTable(string sqlstr)

        {

            DataTable dt = new DataTable();

            OleDbDataAdapter da = new OleDbDataAdapter();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(dt);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return dt;

        }

        /// <summary>

        /// 返回指定sql语句的datatable

        /// </summary>

        /// <param name="sqlstr"></param>

        /// <param name="dt"></param>

        public static void dataTable(string sqlstr, ref DataTable dt)

        {

            OleDbDataAdapter da = new OleDbDataAdapter();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(dt);

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

        }

        /// <summary>

        /// 返回指定sql语句的dataview

        /// </summary>

        /// <param name="sqlstr"></param>

        /// <returns></returns>

        public static DataView dataView(string sqlstr)

        {

            OleDbDataAdapter da = new OleDbDataAdapter();

            DataView dv = new DataView();

            DataSet ds = new DataSet();

            try

            {

                openConnection();

                comm.CommandType = CommandType.Text;

                comm.CommandText = sqlstr;

                da.SelectCommand = comm;

                da.Fill(ds);

                dv = ds.Tables[0].DefaultView;

            }

            catch (Exception e)

            {

                throw new Exception(e.Message);

            }

            finally

            {

                closeConnection();

            }

            return dv;

        }

    }

}