//执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。
 public int ExecuteInsert(string sql, OleDbParameter[] parameters)
 {
   //Debug.WriteLine(sql);
   var connection = pool.New();
   try
   {
     OleDbCommand cmd = new OleDbCommand(sql, connection);
     try
     {
       connection.Open();
       if (parameters != null)
         cmd.Parameters.AddRange(parameters);
       cmd.ExecuteNonQuery();
       cmd.CommandText = @"select @@identity";
       int value = Int32.Parse(cmd.ExecuteScalar().ToString());
       return value;
     }
     catch (Exception e)
     {
       throw e;
     }
   }
   finally
   {
     pool.Delete(connection);
   }
 }

        public InsuranceFund GetInsuranceFundByID(int InsuranceFundID)
        {
            using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
                string sqlText = string.Format(@"select
                    InsuranceFundID,
                    PeopleID,
                    InsuranceFundType,
                    Number,
                    PaymentAddress,
                    PaymentStartDate,
                    PaymentEndDate,
                    BasicNum
                    from {0} where [email protected]", _InsuranceFundTableName);
                var paras = new OleDbParameter[1];
                paras[0] = new OleDbParameter("InsuranceFundID", InsuranceFundID);

                var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
                if (reader.Read()) {
                    int i = 0;
                    var InsuranceFund = new InsuranceFund();
                    InsuranceFund.InsuranceFundID = reader.GetInt32(i++);
                    InsuranceFund.PeopleID = reader.GetInt32(i++);
                    InsuranceFund.InsuranceFundType = (int)reader.GetInt32(i++);
                    InsuranceFund.Number = reader.GetString(i++);
                    InsuranceFund.PaymentAddress = reader.GetString(i++);
                    InsuranceFund.PaymentStartDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
                    InsuranceFund.PaymentEndDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
                    InsuranceFund.BasicNum = reader.GetFloat(i++);

                    return InsuranceFund;
                } else {
                    return null;
                }
            }
        }

 private OleDbParameter CreateDateTimeOleDbParameter(string parameterName, DateTime dt)
 {
     OleDbParameter p = new OleDbParameter(parameterName, OleDbType.DBTimeStamp);
     p.Direction = ParameterDirection.Input;
     p.Value = AccessConnectionHelper.RoundToSeconds(dt);
     return p;
 }

        public Ticket GetTicket(int ticketId)
        {
            Ticket ticket = null;
            String sql = "SELECT Id, AccountId, Comment, DateOpened, State FROM tblTickets WHERE [email protected]";

            using (OleDbConnection conn = this.GetConnection())
            {
                conn.Open();

                OleDbCommand sqlCmd = new OleDbCommand(sql, conn);
                OleDbParameter idParam = new OleDbParameter("@Id", ticketId);
                sqlCmd.Parameters.Add(idParam);

                using (OleDbDataReader reader = sqlCmd.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        reader.Read();
                        ticket.TicketNumber = ticketId;
                        ticket.AccountId = reader.GetInt32(reader.GetOrdinal("AccountId"));
                        ticket.Comment = reader.GetString(reader.GetOrdinal("Comment"));
                        ticket.DateOpened = reader.GetDateTime(reader.GetOrdinal("DateOpened"));
                        ticket.State = (TicketState)reader.GetByte(reader.GetOrdinal("State"));
                    }
                    reader.Close();
                }
                conn.Close();
             return ticket;
            }
        }

        public static bool Delete(string file, string client)
        {
            bool result = true;
            OleDbConnection connect = null;
            try
            {
                connect = openConnect();
                string strCommand = "DELETE FROM Infor WHERE FileName = @file AND FromClient = @client";
                OleDbCommand command = new OleDbCommand(strCommand, connect);
                OleDbParameter parameter ;

                parameter = new OleDbParameter("@file", OleDbType.VarChar);
                parameter.Value = file;
                command.Parameters.Add(parameter);

                parameter = new OleDbParameter("@client", OleDbType.VarChar);
                parameter.Value = client;
                command.Parameters.Add(parameter);

                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                result = false;
            }
            finally
            {
                if (connect == null && connect.State == System.Data.ConnectionState.Open)
                    connect.Close();
            }
            return result;
        }

        public RewardRecord GetRewardRecordByID(int recordID)
        {
            using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
                string sqlText = string.Format(@"select
                    RecordID,
                    PeopleID,
                    RewardDate,
                    RewardContent
                    from {0} where [email protected]", _RewardRecordTableName);
                var paras = new OleDbParameter[1];
                paras[0] = new OleDbParameter("RecordID", recordID);

                var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
                if (reader.Read()) {
                    int i = 0;
                    var RewardRecord = new RewardRecord();
                    RewardRecord.RecordID = reader.GetInt32(i++);
                    RewardRecord.PeopleID = reader.GetInt32(i++);
                    RewardRecord.RewardDate = reader.IsDBNull(i) ? new Nullable<DateTime>() : reader.GetDateTime(i++);
                    RewardRecord.RewardContent = reader.GetString(i++);
                    return RewardRecord;
                } else {
                    return null;
                }
            }
        }

        protected DbParameter genParameter(String paramName, OleDbType type, int size, Object value)
        {
            if (value == null)
                value = DBNull.Value;

            DbParameter param = null;

            if (type == OleDbType.DBTimeStamp)
            {
                if (value == DBNull.Value)
                {
                    param = new OleDbParameter(paramName, value);
                }
                else
                {
                    param = new OleDbParameter(paramName, ((Nullable<DateTime>)value).Value);
                }
            }
            else
            {
                param = new OleDbParameter(paramName, type, size);
                param.Value = value;
            }

            return param;
        }

    public override List<CGamesDetails> GetGamesInRange(decimal Min, decimal Max, int CategoryID)
    {
        using (OleDbConnection conn = new OleDbConnection(
            ConfigurationManager.ConnectionStrings["gameData_AccessConnection"].ConnectionString))
        {
            conn.Open();
            string cmdText = "SELECT GameID,GameTitle,GamePictureURL,GamePrice,CategoryID,GameReleaseDate FROM	tblGame WHERE	(GamePrice >= @min) AND (GamePrice <= @max) AND ([email protected])";
            OleDbCommand cmd = new OleDbCommand(cmdText, conn);

            OleDbParameter[] OleParam = new OleDbParameter[]
            {
                new OleDbParameter("min",OleDbType.Decimal),
                new OleDbParameter("max", OleDbType.Decimal),
                new OleDbParameter("category", OleDbType.Integer)
            };

            OleParam[0].Value = Min;
            OleParam[1].Value = Max;
            OleParam[2].Value = CategoryID;

            cmd.Parameters.AddRange(OleParam);

            IDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

            if (reader.Read())
                return GetGameDetailsCollectionFromReader(reader);
            else
                return null;
        }
    }

 /// <summary>
 /// 执行OleDbCommand(增删改)
 /// </summary>
 /// <param name="sql">sql语句</param>
 /// <param name="value">sql语句单个参数(对应表中字段)</param>
 /// <returns>受影响行数</returns>
 public static int ExecuteCommand(string sql, OleDbParameter value)
 {
     OleDbCommand cmd = new OleDbCommand(sql, GetDbConnection);
     cmd.Parameters.Add(value);
     int result = cmd.ExecuteNonQuery();
     return result;
 }

        public PeopleFamily GetPeopleFamilyByID(int familyPeopleID)
        {
            using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
                string sqlText = string.Format(@"select
                    PeopleFamilyID,
                    PeopleID,
                    Relation,
                    FamilyPeopleName,
                    WorkUnit,
                    Title,
                    PhoneNum,
                    Address
                    from {0} where [email protected]", _peopleFamilyTableName);
                var paras = new OleDbParameter[1];
                paras[0] = new OleDbParameter("PeopleFamilyID", familyPeopleID);

                var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
                if (reader.Read()) {
                    int i = 0;
                    var peopleFamily = new PeopleFamily();
                    peopleFamily.PeopleFamilyID = reader.GetInt32(i++);
                    peopleFamily.PeopleID = reader.GetInt32(i++);
                    peopleFamily.Relation = reader.GetString(i++);
                    peopleFamily.FamilyPeopleName = reader.GetString(i++);
                    peopleFamily.WorkUnit = reader.GetString(i++);
                    peopleFamily.Title = reader.GetString(i++);
                    peopleFamily.PhoneNum = reader.GetString(i++);
                    peopleFamily.Address = reader.GetString(i++);

                    return peopleFamily;
                } else {
                    return null;
                }
            }
        }

        public List<PeopleFamily> GetPeopleFamilyList(int peopleID)
        {
            string sqlText = string.Format(@"select
                FamilyPeopleID,
                Relation,
                FamilyPeopleName,
                WorkUnit,
                Title,
                PhoneNum,
                Address
                from {0}
                where [email protected]"
                , _peopleFamilyTableName);

            var paras = new OleDbParameter[1];
            paras[0] = new OleDbParameter("PeopleID", peopleID);

            var table = new OLESqlHelper().GetTable(sqlText, paras);

            var peopleFamilyList = new List<PeopleFamily>();
            foreach (DataRow row in table.Rows) {
                var peopleFamily = new PeopleFamily() {
                    PeopleFamilyID = (int)row["FamilyPeopleID"],
                    Relation = row["Relation"].ToString(),
                    FamilyPeopleName = row["FamilyPeopleName"].ToString(),
                    WorkUnit = row["WorkUnit"].ToString(),
                    Title = row["Title"].ToString(),
                    PhoneNum = row["PhoneNum"].ToString(),
                    Address = row["Address"].ToString()
                };
                peopleFamilyList.Add(peopleFamily);
            }
            return peopleFamilyList;
        }

        public List<PeoplePic> GetPeoplePicList(int peopleID)
        {
            string sqlText = string.Format(@"select
                PicID,
                PicTitle,
                PicPath
                from {0}
                where [email protected]"
                , _PeoplePicTableName);

            var paras = new OleDbParameter[1];
            paras[0] = new OleDbParameter("PeopleID", peopleID);

            var table = new OLESqlHelper().GetTable(sqlText, paras);

            var PeoplePicList = new List<PeoplePic>();
            foreach (DataRow row in table.Rows) {
                var PeoplePic = new PeoplePic() {
                    PicID = (int)row["PicID"],
                    PicTitle = row["PicTitle"].ToString(),
                    PicPath = row["PicPath"].ToString(),
                };
                PeoplePicList.Add(PeoplePic);
            }
            return PeoplePicList;
        }

        public PeoplePic GetPeoplePicByID(int PicID)
        {
            using (var connection = new OleDbConnection(ConfigManagement.AccessConStr)) {
                string sqlText = string.Format(@"select
                    PicID,
                    PeopleID,
                    PicTitle,
                    PicPath
                    from {0} where [email protected]", _PeoplePicTableName);
                var paras = new OleDbParameter[1];
                paras[0] = new OleDbParameter("PicID", PicID);

                var reader = new OLESqlHelper().GetReader(sqlText, connection, paras);
                if (reader.Read()) {
                    int i = 0;
                    var PeoplePic = new PeoplePic();
                    PeoplePic.PicID = reader.GetInt32(i++);
                    PeoplePic.PeopleID = reader.GetInt32(i++);
                    PeoplePic.PicTitle = reader.GetString(i++);
                    PeoplePic.PicPath = reader.GetString(i++);
                    return PeoplePic;
                } else {
                    return null;
                }
            }
        }

 //执行带参数的sql语句,返回影响的记录数（insert,update,delete)
 public int ExecuteNonQuery(string sql, OleDbParameter[] parameters)
 {
   //Debug.WriteLine(sql);
   //Debug.WriteLine(sql);
   var connection = pool.New();
   try
   {
     OleDbCommand cmd = new OleDbCommand(sql, connection);
     try
     {
       connection.Open();
       if (parameters != null)
         cmd.Parameters.AddRange(parameters);
       int rows = cmd.ExecuteNonQuery();
       return rows;
     }
     catch (Exception e)
     {
       throw e;
     }
   }
   finally
   {
     pool.Delete(connection);
   }
 }

 public List<DocumentType> getDocumentTypes()
 {
     var parameters = new OleDbParameter[] {};
     return
         CreateList<DocumentType>(
             BuildSqlSelect(DocumentType._TABLE_NAME, new String[] { ALL }, new String[] { }), parameters);
 }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                string sSQL = "INSERT INTO tblTest(Col1,Col2) VALUES(@Col1,@Col2)";
                OleDbConnection cn = new OleDbConnection(sCnnString);
                cn.Open();
                OleDbCommand cmd = new OleDbCommand(sSQL, cn);
                OleDbParameter pr = null;
                pr = new OleDbParameter("@Col1", OleDbType.VarWChar, 255);
                pr.Value = txtCol1.Text;
                cmd.Parameters.Add(pr);
                pr = new OleDbParameter("@Col2",OleDbType.LongVarWChar);
                pr.Value = txtCol2.Text;
                cmd.Parameters.Add(pr);

                cmd.ExecuteNonQuery();
                LoadDataToGrid();
                cn.Close();
                cmd = null;
                cn = null;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

 public List<LuNamespaceBean> getNamespaceLookup()
 {
     var parameters = new OleDbParameter[] { };
     return
         CreateList<LuNamespaceBean>(
             BuildSqlSelect(LuNamespaceBean._TABLE_NAME, new String[] { ALL }, new String[] { }), parameters);
 }

 protected void DataList1_SelectedIndexChanged(object sender, EventArgs e)
 {
     string IconPath = "Text";
     string path = HttpContext.Current.Server.MapPath("~/App_Data/ARC_ORG_DB.mdb");
     OleDbConnection connection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path);
     OleDbCommand sqlcmd = new OleDbCommand("SELECT DISTINCT [ID], [Event_Date], [Event_Name], [Additional_Info], [Icon_Path] FROM [Event_Calendar] WHERE ID = ?", connection);
     OleDbParameter sqlparameter = new OleDbParameter("Epath", DataList1.SelectedValue);
     OleDbDataAdapter adapter = new OleDbDataAdapter();
     DataSet ds = new DataSet("Event_Calendar");
     sqlcmd.Parameters.Add(sqlparameter);
     adapter.SelectCommand = sqlcmd;
     adapter.Fill(ds);
     foreach (DataRow row in ds.Tables[0].Rows)
     {
         TextBox_EventDate.Text = Convert.ToString(row["Event_Date"]);
         TextBox_EventName.Text = Convert.ToString(row["Event_Name"]);
         TextBoxEventInfo.Text = Convert.ToString(row["Additional_Info"]);
         IconPath = Convert.ToString(row["Icon_Path"]);
     }
     DropDownList3.DataBind();
     DropDownList3.ClearSelection();            
     ListItem li = DropDownList3.Items.FindByValue(IconPath);
     DropDownList3.SelectedIndex = DropDownList3.Items.IndexOf(li);
     Image1.ImageUrl = IconPath;
     DataList1.Visible = false;
     Panel1.Visible = true;
     
 }

        public List<RewardRecord> GetRewardRecordList(int peopleID)
        {
            string sqlText = string.Format(@"select
                RecordID,
                RewardDate,
                RewardContent
                from {0}
                where [email protected]"
                , _RewardRecordTableName);

            var paras = new OleDbParameter[1];
            paras[0] = new OleDbParameter("PeopleID", peopleID);

            var table = new OLESqlHelper().GetTable(sqlText, paras);

            var rewardRecordList = new List<RewardRecord>();
            foreach (DataRow row in table.Rows) {
                var rewardRecord = new RewardRecord() {
                    RecordID = (int)row["RecordID"],
                    RewardDate = (DateTime?)row["RewardDate"],
                    RewardContent = row["RewardContent"].ToString(),
                };
                rewardRecordList.Add(rewardRecord);
            }
            return rewardRecordList;
        }

        // load the data
        public Dictionary<string, string> load(int id)
        {
            string sql = "select * from admin where [email protected]";
            OleDbConnection con = getCon(null);

            OleDbCommand cmd = new OleDbCommand(sql, con);
            OleDbParameter param = new OleDbParameter("@id",id);
            cmd.Parameters.Add(param);

            OleDbDataReader reader = cmd.ExecuteReader();

            Dictionary<string, string> map = null;

            if (reader.Read())
            {
                map = new Dictionary<string, string>();

                map.Add(reader.GetName(0), reader.GetInt32(0) + "");
                map.Add(reader.GetName(1), reader.GetString(1));
                map.Add(reader.GetName(2), reader.GetString(2));
                map.Add(reader.GetName(3), reader.GetInt32(3) + "");
            }
            //Console.WriteLine("{0} {1} {2}", reader.GetName(0), reader.GetName(1), reader.GetName(2));
             //   Console.Read();

            //close the con
            closeAll(con,cmd,reader);

            return map;
        }