C# IDbDataParameter类代码示例

        /// <summary>
        /// 探索运行时的存储过程,返回IDbDataParameter参数数组.
        /// 初始化参数值为 DBNull.Value.
        /// </summary>
        /// <param>一个有效的数据库连接</param>
        /// <param>存储过程名称</param>
        /// <param>是否包含返回值参数</param>
        /// <returns>返回IDbDataParameter参数数组</returns>
        private static IDbDataParameter[] DiscoverSpParameterSet(IDbConnection connection, string spName, bool includeReturnValueParameter)
        {
            if (connection == null) throw new ArgumentNullException("IDbConnection 连接对象为NULL");
            if (spName == null || spName.Length == 0) throw new ArgumentNullException("存储过程名称为null");
            IDbCommand cmd = DataBaseFactory.GetIDbCommand();//. new IDbCommand(spName, connection);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = spName;
            connection.Open();
            // 检索cmd指定的存储过程的参数信息,并填充到cmd的Parameters参数集中.
           // SqlCommandBuilder.DeriveParameters(cmd); //这里考虑一下如何是区分不同数据库的填充
           
            connection.Close();
            // 如果不包含返回值参数,将参数集中的每一个参数删除.

            if (!includeReturnValueParameter)
            {
                cmd.Parameters.RemoveAt(0);
            }
            // 创建参数数组
            IDbDataParameter[] discoveredParameters = new IDbDataParameter[cmd.Parameters.Count];
            // 将cmd的Parameters参数集复制到discoveredParameters数组.
            cmd.Parameters.CopyTo(discoveredParameters, 0);
            // 初始化参数值为 DBNull.Value.
            foreach (IDbDataParameter discoveredParameter in discoveredParameters)
            {
                discoveredParameter.Value = DBNull.Value;
            }
            return discoveredParameters;
        }

        protected override IDbDataParameter OnParameterCreated(IDbDataParameter parameter, IDacParameter dacParameter)
        {
            var dbParam = (SqlCeParameter)parameter;

            if (DbSchemaInfo.Parameters.ShouldBeDateTime(dacParameter))
            {
                dbParam.DbType = DbType.DateTime;
                return dbParam;
            }

            if (DbSchemaInfo.Parameters.ShouldBeJson(dacParameter))
            {
                dbParam.SqlDbType = SqlDbType.NText;
                dbParam.Size = (dacParameter.Value.ToStringOrNull() ?? string.Empty).Length;
                return dbParam;
            }

            if (DbSchemaInfo.Parameters.ShouldBeUnicodeString(dacParameter))
            {
                dbParam.SqlDbType = SqlDbType.NVarChar;
                var len = (dacParameter.Value.ToStringOrNull() ?? string.Empty).Length;
                if (len > MaxLenOfStringBeforeEscalating)
                    throw new SisoDbException(ExceptionMessages.SqlCe4_ToLongIndividualStringValue);

                dbParam.Size = len;
                return dbParam;
            }

            return dbParam;
        }

        protected override void InitializeParameter(IDbDataParameter p, NamedParameter parameter, object value)
        {
            p.ParameterName = parameter.Name;
            p.Value = value ?? DBNull.Value;
            var sqlType = parameter.SqlType;

            if (parameter.SqlType != null)
            {
                if (sqlType.Length > 0)
                    p.Size = sqlType.Length;
                if (sqlType.Precision > 0)
                    p.Precision = sqlType.Precision;
                if (sqlType.Scale > 0)
                    p.Scale = sqlType.Scale;
                if (sqlType.Required)
                    (p as DbParameter).IsNullable = false;

                switch (sqlType.DbType)
                {
                    case DBType.DateTime:
                        p.DbType = DbType.String;
                        if (p.Value != DBNull.Value && !object.Equals(null, p.Value))
                            p.Value = ((DateTime)p.Value).ToString("yyyy/MM/dd HH:mm:ss");
                        break;
                    case DBType.Int64:
                        p.DbType = DbType.Int32;
                        if (p.Value != DBNull.Value && !object.Equals(null, p.Value))
                            p.Value = System.Convert.ToInt32((long)p.Value);
                        break;
                }
            }
            //p.DbType = SqlType.ToDbType(parameter.sqlType.DbType);
            ConvertDBTypeToNativeType(p, sqlType.DbType);
        }

        private static void SetDefaultParameterSize(IDbDataParameter dbParam, SqlType sqlType)
        {
            switch (dbParam.DbType)
            {
                case DbType.AnsiString:
                case DbType.AnsiStringFixedLength:
                    dbParam.Size = MaxAnsiStringSize;
                    break;

                case DbType.Binary:
                    if (sqlType is BinaryBlobSqlType)
                    {
                        dbParam.Size = MaxBinaryBlobSize;
                    }
                    else
                    {
                        dbParam.Size = MaxBinarySize;
                    }
                    break;
                case DbType.Decimal:
                    dbParam.Precision = MaxPrecision;
                    dbParam.Scale = MaxScale;
                    break;
                case DbType.String:
                case DbType.StringFixedLength:
                    dbParam.Size = IsText(dbParam, sqlType) ? MaxStringClobSize : MaxStringSize;
                    break;
                case DbType.DateTime2:
                    dbParam.Size = MaxDateTime2;
                    break;
                case DbType.DateTimeOffset:
                    dbParam.Size = MaxDateTimeOffset;
                    break;
            }
        }

        protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlTypes.SqlType sqlType)
        {
            base.InitializeParameter(dbParam, name, sqlType);

            if (sqlType.DbType == DbType.Currency)
                dbParam.DbType = DbType.Decimal;
        }

		public HowTo_Execute_SQLFunction_returnDataTable() {
			long _idgroup_search = 1L;


			DBConnection _con = DBConnectionsupport.CreateInstance(
				// set your db server type here
				DBServerTypes.PostgreSQL, 
				// and connectionstring
				"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
			);

			// set your function parameters
			IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
				_con.newDBDataParameter(
					"IDGroup_search_", 
					DbType.Int64, 
					ParameterDirection.Input, 
					_idgroup_search, 
					0
				)
			};

			// you now have a DataTable populated
			// with results from your sql function
			DataTable _datatable 
				= _con.Execute_SQLFunction_returnDataTable(
					"sp0_User_Record_open_byGroup_fullmode", 
					_dataparameters
				);

			_con.Dispose(); _con = null;


		}

		/// <summary>
		/// Executes a stored procedure.
		/// </summary>
		/// <param name="storedProcedure">Name of stored procedure to execute.</param>
		/// <param name="parameters">Parameters to pass to stored procedure.</param>
		/// <exception cref="MulhollandException">Wraps any data exception which may occur.</exception>
		public void ExecuteNonQuery(string storedProcedure, IDbDataParameter[] parameters)
		{			
			IDbCommand cmd = null;

			try
			{				
				cmd = new SqlCommand(storedProcedure, new SqlConnection(_connectionString));
				cmd.CommandType = CommandType.StoredProcedure;

				AttachParametersToCommand(cmd, parameters);

				cmd.Connection.Open();
				cmd.ExecuteNonQuery();
			}
			catch (Exception exc)
			{
				throw new MulhollandException("Encoutered an error performing data operation.", exc);
			}
			finally
			{
				if (cmd != null)
				{
					CloseConnection(cmd);
					cmd.Dispose();
				}
			}
		}

        public void UseStoredProcedure(string name, object args)
        {
            name.MustNotBeEmpty();
            _cmd.CommandType = CommandType.StoredProcedure;
            _cmd.CommandText = name;

            var provider = _db.Provider;
            var paramDict = CreateParamsDictionary(args);
            var allp = _cmd.Parameters;

            IDbDataParameter p = null;

            p = _cmd.CreateParameter();
            provider.SetupParameter(p, "ReturnValue", 0);
            allp.Add(p);
            _return = p;
            p.Direction = ParameterDirection.ReturnValue;

            foreach (var kv in paramDict)
            {
                p = _cmd.CreateParameter();
                var pname = kv.Key;
                if (kv.Key.StartsWith("_"))
                {
                    pname = kv.Key.Substring(1);
                    p.Direction = ParameterDirection.Output;
                }
                provider.SetupParameter(p, pname, kv.Value);
                allp.Add(p);
                _output.Add(p);
            }
        }

		public HowTo_Execute_SQLFunction() {
			long _iduser = 0L;


			DBConnection _con = DBConnectionsupport.CreateInstance(
				// set your db server type here
				DBServerTypes.PostgreSQL, 
				// and connectionstring
				"Server=127.0.0.1;Port=5432;User ID=postgres;Password=passpub;Database=OGen-NTier_UTs;"
			);

			// set your function parameters
			IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
				_con.newDBDataParameter(
					"IDUser_", 
					DbType.Int64, 
					ParameterDirection.Input, 
					_iduser, 
					0
				)
			};

			// call you function
			_con.Execute_SQLFunction(
				"sp0_User_delObject", 
				_dataparameters
			);

			_con.Dispose(); _con = null;


		}

 public ProductAttrValueInfo GetProductAttrValueInfo(string _proID,int _attrID)
 {
     ProductAttrValueInfo productAttrValueInfo = null;
     StringBuilder sql= new StringBuilder();
     sql.Append("SELECT * FROM");
     sql.Append(" [Tbl_ProductAttrValue]");
     sql.Append(" WHERE");
     sql.Append(" [ProID][email protected]");
     sql.Append(" AND");
     sql.Append(" [AttrID][email protected]");
     using(NetShopHelper dbhelper=new NetShopHelper())
     {
       IDbDataParameter[] p_Parms = new IDbDataParameter[]{
         dbhelper.CreateParameter("@ProID",_proID),
         dbhelper.CreateParameter("@AttrID",_attrID)
       };
       using(IDataReader reader=dbhelper.ExecuteReader(sql.ToString(),p_Parms))
       {
         if(reader.Read())
         {
             productAttrValueInfo = ProductAttrValueInfo.SetValue(reader);
         }
       }
     }
     return productAttrValueInfo;
 }

 protected override void InitializeParameter(IDbDataParameter dbParam, string name, SqlType sqlType)
 {
     if (sqlType is NpgsqlExtendedSqlType && dbParam is NpgsqlParameter)
         this.InitializeParameter(dbParam as NpgsqlParameter, name, sqlType as NpgsqlExtendedSqlType);
     else
         base.InitializeParameter(dbParam, name, sqlType);
 }

        public virtual void Add(IDbDataParameter item)
        {
            if (item == null)
                throw new ArgumentException("Item can not be null");

            parameters.Add(item);
        }

		/// <summary>
		/// Selects NET_Profile__default values from Database and assigns them to the appropriate DO_NET_Profile__default property.
		/// </summary>
		/// <param name="IFProfile_in">IFProfile</param>
		/// <param name="dbConnection_in">Database connection, making the use of Database Transactions possible on a sequence of operations across the same or multiple DataObjects</param>
		/// <returns>null if NET_Profile__default doesn't exists at Database</returns>
		public static SO_NET_Profile__default getObject(
			long IFProfile_in, 
			DBConnection dbConnection_in
		) {
			SO_NET_Profile__default _output = null;

			DBConnection _connection = (dbConnection_in == null)
				? DO__Utilities.DBConnection_createInstance(
					DO__Utilities.DBServerType,
					DO__Utilities.DBConnectionstring,
					DO__Utilities.DBLogfile
				) 
				: dbConnection_in;
			IDbDataParameter[] _dataparameters = new IDbDataParameter[] {
				_connection.newDBDataParameter("IFProfile_", DbType.Int64, ParameterDirection.InputOutput, IFProfile_in, 0)
			};
			_connection.Execute_SQLFunction("sp0_NET_Profile__default_getObject", _dataparameters);
			if (dbConnection_in == null) { _connection.Dispose(); }

			if (_dataparameters[0].Value != DBNull.Value) {
				_output = new SO_NET_Profile__default();

				if (_dataparameters[0].Value == System.DBNull.Value) {
					_output.IFProfile = 0L;
				} else {
					_output.IFProfile = (long)_dataparameters[0].Value;
				}

				_output.HasChanges = false;
				return _output;
			}

			return null;
		}

 //Datetime is a special case - we need to convert to string properly
 public override void SetDbParameterValue(IDbDataParameter parameter, object value)
 {
     if (value != null && value is DateTime)
     parameter.Value = SQLiteTypeRegistry.DateTimeToString(value);
       else
     base.SetDbParameterValue(parameter, value);
 }

		/// <summary>
		/// Executes astored procedure opening up an IDataReader to process the result.
		/// </summary>
		/// <param name="storedProcedure">Name of stored procedure to execute.</param>
		/// <param name="parameters">Parameters to pass to stored procedure.</param>
		/// <returns>IDataReader</returns>
		/// <exception cref="MulhollandException">Wraps any data exception which may occur.</exception>
		public IDataReader ExecuteReader(string storedProcedure, IDbDataParameter[] parameters)
		{
			IDbCommand cmd = null;
			IDataReader reader = null;

			try
			{				
				cmd = new SqlCommand(storedProcedure, new SqlConnection(_connectionString));
				cmd.CommandType = CommandType.StoredProcedure;
 
				AttachParametersToCommand(cmd, parameters);

				cmd.Connection.Open();
				reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
			}
			catch (Exception exc)
			{
				throw new MulhollandException("Encoutered an error performing data operation.", exc);
			}
			finally
			{
				if (cmd != null)
				{
					cmd.Dispose();
				}
			}

			return reader;
		}