C# Database.AddInParameter方法代码示例

		public static void CreateDataAdapterCommands(Database db, ref DbCommand insertCommand, ref DbCommand updateCommand, ref DbCommand deleteCommand)
        {
            insertCommand = db.GetStoredProcCommand("RegionInsert");
            updateCommand = db.GetStoredProcCommand("RegionUpdate");
            deleteCommand = db.GetStoredProcCommand("RegionDelete");

            db.AddInParameter(insertCommand, "vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
            db.AddInParameter(insertCommand, "vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);

            db.AddInParameter(updateCommand, "vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
            db.AddInParameter(updateCommand, "vRegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);

            db.AddInParameter(deleteCommand, "vRegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
        }

		public static void CreateDataAdapterCommands(Database db, ref DbCommand insertCommand, ref DbCommand updateCommand, ref DbCommand deleteCommand)
		{
			insertCommand = db.GetSqlStringCommand("INSERT INTO Region VALUES(@RegionID, @RegionDescription)");
			updateCommand = db.GetSqlStringCommand("UPDATE region SET [email protected] WHERE [email protected]");
			deleteCommand = db.GetSqlStringCommand("DELETE FROM Region WHERE [email protected]");

			db.AddInParameter(insertCommand, "@RegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
			db.AddInParameter(insertCommand, "@RegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);

			db.AddInParameter(updateCommand, "@RegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
			db.AddInParameter(updateCommand, "@RegionDescription", DbType.String, "RegionDescription", DataRowVersion.Default);

			db.AddInParameter(deleteCommand, "@RegionID", DbType.Int32, "RegionID", DataRowVersion.Default);
		}

        public void Save(Database db)
        {
            DbCommand dbCommand = db.GetStoredProcCommand("AG_SaveYoGomeeUser");

            db.AddInParameter(dbCommand, "YoGomeeUserID", DbType.Int32, YoGomeeUserID);
            db.AddInParameter(dbCommand, "FirstName", DbType.String, FirstName);
            db.AddInParameter(dbCommand, "LastName", DbType.String, LastName);
            db.AddInParameter(dbCommand, "EmailAddress", DbType.String, EmailAddress);
            db.AddInParameter(dbCommand, "Password", DbType.String, Password);
            db.AddInParameter(dbCommand, "PhoneNumber", DbType.String, PhoneNumber);
            db.AddInParameter(dbCommand, "PhoneModel", DbType.Int32, PhoneModel);
            db.AddInParameter(dbCommand, "ISOCountry", DbType.String, ISOCountry);
            db.AddInParameter(dbCommand, "SignupIPAddress", DbType.String, SignupIPAddress);
            db.AddInParameter(dbCommand, "Created", DbType.DateTime, Created);

            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {

                // get the returned ID
                if (dr.Read())
                {
                    int ID = Int32.Parse(dr[0].ToString());
                    //if the ID is NOT zero then the query was an insert
                    if (ID != 0)
                        this.YoGomeeUserID = ID;
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Instanciates a Country object from the database via the CountryID
        /// </summary>
        public Country(int CountryID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetCountryByCountryID");
            db.AddInParameter(dbCommand, "CountryID", DbType.Int32, CountryID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("CountryID")) { this._countryID = (int)dr["CountryID"]; }
                    if (list.IsColumnPresent("CountryCode")) { this._countryCode = (string)dr["CountryCode"]; }
                    if (list.IsColumnPresent("Name")) { this._name = (string)dr["Name"]; }

                }
                else
                {
                    throw new Exception("There is no Country in the database with the ID " + CountryID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Instanciates a YoGomeeUser object from the database via the YoGomeeUserID
        /// </summary>
        public YoGomeeUser(int YoGomeeUserID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetYoGomeeUserByYoGomeeUserID");
            db.AddInParameter(dbCommand, "YoGomeeUserID", DbType.Int32, YoGomeeUserID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("YoGomeeUserID")) { this._yoGomeeUserID = (int)dr["YoGomeeUserID"]; }
                    if (list.IsColumnPresent("FirstName")) { this._firstName = (string)dr["FirstName"]; }
                    if (list.IsColumnPresent("LastName")) { this._lastName = (string)dr["LastName"]; }
                    if (list.IsColumnPresent("EmailAddress")) { this._emailAddress = (string)dr["EmailAddress"]; }
                    if (list.IsColumnPresent("Password")) { this._password = (string)dr["Password"]; }
                    if (list.IsColumnPresent("PhoneNumber")) { this._phoneNumber = (string)dr["PhoneNumber"]; }
                    if (list.IsColumnPresent("PhoneModel")) { this._phoneModel = (int)dr["PhoneModel"]; }
                    if (list.IsColumnPresent("ISOCountry")) { this._iSOCountry = (string)dr["ISOCountry"]; }
                    if (list.IsColumnPresent("SignupIPAddress")) { this._signupIPAddress = (string)dr["SignupIPAddress"]; }
                    if (list.IsColumnPresent("Created")) { this._created = (DateTime)dr["Created"]; }

                }
                else
                {
                    throw new Exception("There is no YoGomeeUser in the database with the ID " + YoGomeeUserID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Instanciates a GeoName object from the database via the GeoNameID
        /// </summary>
        public GeoName(int GeoNameID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetGeoNameByGeoNameID");
            db.AddInParameter(dbCommand, "GeoNameID", DbType.Int32, GeoNameID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("GeoNameID")) { this._geoNameID = (int)dr["GeoNameID"]; }
                    if (list.IsColumnPresent("Name")) { this._name = (string)dr["Name"]; }
                    if (list.IsColumnPresent("Latitude")) { this._latitude = (decimal)dr["Latitude"]; }
                    if (list.IsColumnPresent("Longitude")) { this._longitude = (decimal)dr["Longitude"]; }
                    if (list.IsColumnPresent("FeatureClassID")) { this._featureClassID = (int)dr["FeatureClassID"]; }
                    if (list.IsColumnPresent("FeatureCodeID")) { this._featureCodeID = (int)dr["FeatureCodeID"]; }
                    if (list.IsColumnPresent("CountryID")) { this._countryID = (int)dr["CountryID"]; }
                    if (list.IsColumnPresent("Admin1ID")) { this._admin1ID = (int)dr["Admin1ID"]; }
                    if (list.IsColumnPresent("Population")) { this._population = (int)dr["Population"]; }
                    if (list.IsColumnPresent("Elevation")) { this._elevation = (int)dr["Elevation"]; }
                    if (list.IsColumnPresent("gtopo3")) { this._gtopo3 = (int)dr["gtopo3"]; }
                    if (list.IsColumnPresent("TimeZoneID")) { this._timeZoneID = (int)dr["TimeZoneID"]; }
                    if (list.IsColumnPresent("ModDate")) { this._modDate = (DateTime)dr["ModDate"]; }

                }
                else
                {
                    throw new Exception("There is no GeoName in the database with the ID " + GeoNameID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Instanciates a FeatureCode object from the database via the FeatureCodeID
        /// </summary>
        public FeatureCode(int FeatureCodeID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetFeatureCodeByFeatureCodeID");
            db.AddInParameter(dbCommand, "FeatureCodeID", DbType.Int32, FeatureCodeID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("FeatureCodeID")) { this._featureCodeID = (int)dr["FeatureCodeID"]; }
                    if (list.IsColumnPresent("FeatureCodeCD")) { this._featureCodeCD = (string)dr["FeatureCodeCD"]; }
                    if (list.IsColumnPresent("FeatureClassID")) { this._featureClassID = (int)dr["FeatureClassID"]; }
                    if (list.IsColumnPresent("Name")) { this._name = (string)dr["Name"]; }
                    if (list.IsColumnPresent("Description")) { this._description = (string)dr["Description"]; }

                }
                else
                {
                    throw new Exception("There is no FeatureCode in the database with the ID " + FeatureCodeID);
                }

                dr.Close();
            }
        }

 private void InsertSystemLog(LogEntity entity, Database database)
 {
     DbCommand command = database.GetSqlStringCommand(string.Format(INSERT_SYSTEM_LOG_SQL, logTableName));
     database.AddInParameter(command, "GUID", DbType.Guid, entity.ExtendedProperties.ContainsKey("Guid") ? entity.ExtendedProperties["Guid"] : Guid.NewGuid());
     database.AddInParameter(command, "LogDate", DbType.DateTime, entity.ExtendedProperties.ContainsKey("LogDate") ? entity.ExtendedProperties["LogDate"] : DateTime.Now);
     database.AddInParameter(command, "LogType", DbType.Int32, entity.ExtendedProperties["LogType"]);
     database.AddInParameter(command, "LogUserLoginID", DbType.String, entity.ExtendedProperties["LogUserLoginID"]);
     database.AddInParameter(command, "LogUserIP", DbType.String, entity.ExtendedProperties["LogUserIP"]);
     database.AddInParameter(command, "LogSummary", DbType.String, GetLogSummary(entity));
     database.AddInParameter(command, "LogDescription", DbType.String, GetLogDescription(entity));
     database.AddInParameter(command, "OperationEntityCharacter", DbType.String, entity.ExtendedProperties.ContainsKey("OperationEntityCharacter") ? entity.ExtendedProperties["OperationEntityCharacter"] : DBNull.Value);
     database.AddInParameter(command, "OperationEntityGuid", DbType.Guid, entity.ExtendedProperties.ContainsKey("OperationEntityGuid") ? new Guid(entity.ExtendedProperties["OperationEntityGuid"].ToString()) : Guid.Empty);
     database.AddInParameter(command, "OperationEntityOriginalValue", DbType.String, entity.ExtendedProperties.ContainsKey("OperationEntityOriginalValue") ? entity.ExtendedProperties["OperationEntityOriginalValue"] : DBNull.Value);
     database.AddInParameter(command, "OperationEntityCurrentValue", DbType.String, entity.ExtendedProperties.ContainsKey("OperationEntityCurrentValue") ? entity.ExtendedProperties["OperationEntityCurrentValue"] : DBNull.Value);
     database.AddInParameter(command, "OperationEntityName", DbType.String, entity.ExtendedProperties.ContainsKey("OperationEntityName") ? entity.ExtendedProperties["OperationEntityName"] : DBNull.Value);
     database.AddInParameter(command, "Level", DbType.Int32, entity.ExtendedProperties["Level"]);
     database.ExecuteNonQuery(command);
 }

        /// <summary>
        /// Instanciates a ISOLanguage object from the database via the ISOLanguageID
        /// </summary>
        public ISOLanguage(int ISOLanguageID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetISOLanguageByISOLanguageID");
            db.AddInParameter(dbCommand, "ISOLanguageID", DbType.Int32, ISOLanguageID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("ISOLanguageID")) { this._iSOLanguageID = (int)dr["ISOLanguageID"]; }
                    if (list.IsColumnPresent("ISO639_1")) { this._iSO639_1 = (string)dr["ISO639_1"]; }
                    if (list.IsColumnPresent("ISO639_2")) { this._iSO639_2 = (string)dr["ISO639_2"]; }
                    if (list.IsColumnPresent("ISO639_3")) { this._iSO639_3 = (string)dr["ISO639_3"]; }
                    if (list.IsColumnPresent("Name")) { this._name = (string)dr["Name"]; }

                }
                else
                {
                    throw new Exception("There is no ISOLanguage in the database with the ID " + ISOLanguageID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Instanciates a GomeeMap object from the database via the GomeeMapID
        /// </summary>
        public GomeeMap(int GomeeMapID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetGomeeMapByGomeeMapID");
            db.AddInParameter(dbCommand, "GomeeMapID", DbType.Int32, GomeeMapID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("GomeeMapID")) { this._gomeeMapID = (int)dr["GomeeMapID"]; }
                    if (list.IsColumnPresent("YoGomeeUserID")) { this._yoGomeeUserID = (int)dr["YoGomeeUserID"]; }
                    if (list.IsColumnPresent("GomeeID")) { this._gomeeID = (int)dr["GomeeID"]; }
                    if (list.IsColumnPresent("ProximityHit")) { this._proximityHit = (bool)dr["ProximityHit"]; }
                    if (list.IsColumnPresent("ProximityDateTime")) { this._proximityDateTime = (DateTime)dr["ProximityDateTime"]; }
                    if (list.IsColumnPresent("ReceivedGomee")) { this._receivedGomee = (bool)dr["ReceivedGomee"]; }
                    if (list.IsColumnPresent("ReceivedGomeeDateTime")) { this._receivedGomeeDateTime = (DateTime)dr["ReceivedGomeeDateTime"]; }

                }
                else
                {
                    throw new Exception("There is no GomeeMap in the database with the ID " + GomeeMapID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Instanciates a Gomee object from the database via the GomeeID
        /// </summary>
        public Gomee(int GomeeID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetGomeeByGomeeID");
            db.AddInParameter(dbCommand, "GomeeID", DbType.Int32, GomeeID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("GomeeID")) { this._gomeeID = (int)dr["GomeeID"]; }
                    if (list.IsColumnPresent("YoGomeeUserID")) { this._yoGomeeUserID = (int)dr["YoGomeeUserID"]; }
                    if (list.IsColumnPresent("Active")) { this._active = (bool)dr["Active"]; }
                    if (list.IsColumnPresent("Latitude")) { this._latitude = (decimal)dr["Latitude"]; }
                    if (list.IsColumnPresent("Longitude")) { this._longitude = (decimal)dr["Longitude"]; }
                    if (list.IsColumnPresent("Expiration")) { this._expiration = (DateTime)dr["Expiration"]; }
                    if (list.IsColumnPresent("DoesExpire")) { this._doesExpire = (bool)dr["DoesExpire"]; }
                    if (list.IsColumnPresent("GomeeType")) { this._gomeeType = (int)dr["GomeeType"]; }
                    if (list.IsColumnPresent("RawValue1")) { this._rawValue1 = (string)dr["RawValue1"]; }
                    if (list.IsColumnPresent("RawValue2")) { this._rawValue2 = (string)dr["RawValue2"]; }
                    if (list.IsColumnPresent("Created")) { this._created = (DateTime)dr["Created"]; }

                }
                else
                {
                    throw new Exception("There is no Gomee in the database with the ID " + GomeeID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Instanciates a Metadata object from the database via the MetaDataID
        /// </summary>
        public Metadata(int MetaDataID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetMetadataByMetaDataID");
            db.AddInParameter(dbCommand, "MetaDataID", DbType.Int32, MetaDataID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("MetaDataID")) { this._metaDataID = (int)dr["MetaDataID"]; }
                    if (list.IsColumnPresent("GeoNamesModDate")) { this._geoNamesModDate = (DateTime)dr["GeoNamesModDate"]; }

                }
                else
                {
                    throw new Exception("There is no Metadata in the database with the ID " + MetaDataID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// Calls the database and gets all the GeoName objects for this FeatureClass
        /// </summary>
        private List<GeoName> GetGeoNameByFeatureClassID()
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetGeoNameByFeatureClassID");
            db.AddInParameter(dbCommand, "FeatureClassID", DbType.Int32, FeatureClassID);

            List<GeoName> arr = null;

            // Populate the datareader
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                // Call the PopulateObject method passing the datareader to return the object array
                arr = yoGomee.Data.GeoName.PopulateObject(dr);
                dr.Close();
            }

            return arr;
        }

        /// <summary>
        /// Instanciates a Friend object from the database via the FriendID
        /// </summary>
        public Friend(int FriendID)
        {
            db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("AG_GetFriendByFriendID");
            db.AddInParameter(dbCommand, "FriendID", DbType.Int32, FriendID);

            //execute the stored procedure
            using (IDataReader dr = db.ExecuteReader(dbCommand))
            {
                ColumnFieldList list = new ColumnFieldList(dr);

                if (dr.Read())
                {
                    if (list.IsColumnPresent("FriendID")) { this._friendID = (int)dr["FriendID"]; }
                    if (list.IsColumnPresent("YoGomeeUserID1")) { this._yoGomeeUserID1 = (int)dr["YoGomeeUserID1"]; }
                    if (list.IsColumnPresent("YoGomeeUserID2")) { this._yoGomeeUserID2 = (int)dr["YoGomeeUserID2"]; }
                    if (list.IsColumnPresent("User1Accepted")) { this._user1Accepted = (bool)dr["User1Accepted"]; }
                    if (list.IsColumnPresent("User2Accepted")) { this._user2Accepted = (bool)dr["User2Accepted"]; }
                    if (list.IsColumnPresent("UnfriendedUserID")) { this._unfriendedUserID = (int)dr["UnfriendedUserID"]; }

                }
                else
                {
                    throw new Exception("There is no Friend in the database with the ID " + FriendID);
                }

                dr.Close();
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbBase"></param>
        /// <param name="command"></param>
        /// <param name="dbParamCollection"></param>
        private static void AddParamToCommand(Database dbBase, DbCommand command, DBParamCollection dbParamCollection)
        {
            if (dbParamCollection == null || dbParamCollection.Count == 0)
            {
                return;
            }

            foreach (DbParameter dbParam in dbParamCollection)
            {
                dbBase.AddInParameter(command, dbParam.ParameterName, dbParam.DbType, dbParam.Value);
            }
        }