C# OleDb.OleDbParameter类代码示例

 //执行单条插入语句，并返回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);
 }