C# FirebirdClient.FbParameter类代码示例

        /// <summary>
        /// Updates the TrackOrder values for the tracks that remain for the PlayerID by incrementing any Tracks that have a TrackOrder value
        /// greater than the provided trackOrder.
        /// </summary>
        /// <param name="playerID">The ID of the Player.</param>
        /// <param name="trackOrder">The TrackOrder value.</param>
        /// <returns>The number of rows affected by the update.</returns>
        public static int AdjustTrackOrdersForDelete(int playerId, int trackOrder)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_MediaTrack ");
            sqlCommand.Append("SET TrackOrder = TrackOrder - 1 ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("PlayerID = @PlayerID ");
            sqlCommand.Append("AND TrackOrder > @TrackOrder ");
            sqlCommand.Append(";");

            FbParameter[] arParams = new FbParameter[2];

            arParams[0] = new FbParameter("@PlayerID", FbDbType.Integer);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = playerId;

            arParams[1] = new FbParameter("@TrackOrder", FbDbType.Integer);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = trackOrder;

            int rowsAffected = FBSqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);

            return rowsAffected;
        }

        public static int Create(
            string userId,
            string claimType,
            string claimValue)
        {
            FbParameter[] arParams = new FbParameter[3];

            arParams[0] = new FbParameter(":UserId", FbDbType.VarChar, 128);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = userId;

            arParams[1] = new FbParameter(":ClaimType", FbDbType.VarChar, -1);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = claimType;

            arParams[2] = new FbParameter(":ClaimValue", FbDbType.VarChar, -1);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = claimValue;

            string statement = "EXECUTE PROCEDURE mp_USERCLAIMS_INSERT ("
                + FBSqlHelper.GetParamString(arParams.Length) + ")";

            int newID = Convert.ToInt32(FBSqlHelper.ExecuteScalar(
                ConnectionString.GetWriteConnectionString(),
                CommandType.StoredProcedure,
                statement,
                arParams));

            return newID;
        }

        public DateTime CambiarFechaSacrificio(Producto AProducto)
        {
            string pSentencia = "UPDATE DRASPROD SET FECHA_SACRIFICIO = @FECHASAC WHERE CLAVE = @CLAVE RETURNING FECHA_SACRIFICIO";
            FbConnection con = _Conexiones.ObtenerConexion();

            FbCommand com = new FbCommand(pSentencia, con);
            com.Parameters.Add("@CLAVE", FbDbType.VarChar).Value = AProducto.Clave;
            com.Parameters.Add("@FECHASAC", FbDbType.TimeStamp).Value = AProducto.Fecha_Sacrificio;

            FbParameter pOutParameter = new FbParameter("@FECHA_SACRIFICIO", FbDbType.TimeStamp);
            pOutParameter.Direction = ParameterDirection.Output;
            com.Parameters.Add(pOutParameter);
            try
            {
                con.Open();
                com.ExecuteNonQuery();

            }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return (DateTime)pOutParameter.Value;
        }

        public async Task<int> Create(
            int siteId,
            string userId,
            string claimType,
            string claimValue)
        {

            FbParameter[] arParams = new FbParameter[4];

            arParams[0] = new FbParameter(":UserId", FbDbType.VarChar, 128);
            arParams[0].Value = userId;

            arParams[1] = new FbParameter(":ClaimType", FbDbType.VarChar, -1);
            arParams[1].Value = claimType;

            arParams[2] = new FbParameter(":ClaimValue", FbDbType.VarChar, -1);
            arParams[2].Value = claimValue;

            arParams[3] = new FbParameter(":SiteId", FbDbType.Integer);
            arParams[3].Value = siteId;

            string statement = "EXECUTE PROCEDURE mp_USERCLAIMS_INSERT ("
                + AdoHelper.GetParamString(arParams.Length) + ")";

            object result = await AdoHelper.ExecuteScalarAsync(
                writeConnectionString,
                CommandType.StoredProcedure,
                statement,
                arParams);

            int newID = Convert.ToInt32(result);

            return newID;
        }

        public static bool Delete(
            string loginProvider,
            string providerKey,
            string userId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("DELETE FROM mp_UserLogins ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("LoginProvider = @LoginProvider AND ");
            sqlCommand.Append("ProviderKey = @ProviderKey AND ");
            sqlCommand.Append("UserId = @UserId ");
            sqlCommand.Append(";");
            FbParameter[] arParams = new FbParameter[3];

            arParams[0] = new FbParameter("@LoginProvider", FbDbType.VarChar, 128);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = loginProvider;

            arParams[1] = new FbParameter("@ProviderKey", FbDbType.VarChar, 128);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = providerKey;

            arParams[2] = new FbParameter("@UserId", FbDbType.VarChar, 128);
            arParams[2].Direction = ParameterDirection.Input;
            arParams[2].Value = userId;

            int rowsAffected = FBSqlHelper.ExecuteNonQuery(
                ConnectionString.GetWriteConnectionString(),
                sqlCommand.ToString(),
                arParams);

            return (rowsAffected > -1);
        }

 public static void ExecuteProcedureNonQuery(string spName, FbParameter[] parameters, FbTransactionBehavior tranBeh)
 {
     OpenConnection();
     if ((!storedProcs.ContainsKey(spName))
         || (storedProcs[spName].Parameters.Count != parameters.Length))
     {
         string sql = "";
         for (int i = 0; i < parameters.Length; i++)
             sql += ",?";
         sql = "EXECUTE PROCEDURE " + spName + "(" + sql.Substring(1) + ")";
         FbCommand commandProcedure = new FbCommand(sql, mConnection);
         commandProcedure.CommandType = CommandType.StoredProcedure;
         commandProcedure.Parameters.AddRange(parameters);
         storedProcs[spName] = commandProcedure;
     }
     else
     {
         for (int i = 0; i < parameters.Length; i++)
             storedProcs[spName].Parameters[i].Value = parameters[i].Value;
     }
     FbTransactionOptions transactionOptions = new FbTransactionOptions();
     transactionOptions.TransactionBehavior = tranBeh;
     storedProcs[spName].Transaction = mConnection.BeginTransaction(transactionOptions);
     storedProcs[spName].ExecuteNonQuery();
     storedProcs[spName].Transaction.Commit();
 }

        /// <summary>
        /// Inserts a row in the mp_RedirectList table. Returns rows affected count.
        /// </summary>
        /// <param name="rowGuid"> rowGuid </param>
        /// <param name="siteGuid"> siteGuid </param>
        /// <param name="siteID"> siteID </param>
        /// <param name="oldUrl"> oldUrl </param>
        /// <param name="newUrl"> newUrl </param>
        /// <param name="createdUtc"> createdUtc </param>
        /// <param name="expireUtc"> expireUtc </param>
        /// <returns>int</returns>
        public int Create(
            Guid rowGuid,
            Guid siteGuid,
            int siteID,
            string oldUrl,
            string newUrl,
            DateTime createdUtc,
            DateTime expireUtc)
        {
            FbParameter[] arParams = new FbParameter[7];

            arParams[0] = new FbParameter("@RowGuid", FbDbType.Char, 36);
            arParams[0].Value = rowGuid.ToString();

            arParams[1] = new FbParameter("@SiteGuid", FbDbType.Char, 36);
            arParams[1].Value = siteGuid.ToString();

            arParams[2] = new FbParameter("@SiteID", FbDbType.Integer);
            arParams[2].Value = siteID;

            arParams[3] = new FbParameter("@OldUrl", FbDbType.VarChar, 255);
            arParams[3].Value = oldUrl;

            arParams[4] = new FbParameter("@NewUrl", FbDbType.VarChar, 255);
            arParams[4].Value = newUrl;

            arParams[5] = new FbParameter("@CreatedUtc", FbDbType.TimeStamp);
            arParams[5].Value = createdUtc;

            arParams[6] = new FbParameter("@ExpireUtc", FbDbType.TimeStamp);
            arParams[6].Value = expireUtc;

            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("INSERT INTO mp_RedirectList (");
            sqlCommand.Append("RowGuid, ");
            sqlCommand.Append("SiteGuid, ");
            sqlCommand.Append("SiteID, ");
            sqlCommand.Append("OldUrl, ");
            sqlCommand.Append("NewUrl, ");
            sqlCommand.Append("CreatedUtc, ");
            sqlCommand.Append("ExpireUtc )");

            sqlCommand.Append(" VALUES (");
            sqlCommand.Append("@RowGuid, ");
            sqlCommand.Append("@SiteGuid, ");
            sqlCommand.Append("@SiteID, ");
            sqlCommand.Append("@OldUrl, ");
            sqlCommand.Append("@NewUrl, ");
            sqlCommand.Append("@CreatedUtc, ");
            sqlCommand.Append("@ExpireUtc )");
            sqlCommand.Append(";");

            int rowsAffected = AdoHelper.ExecuteNonQuery(
                writeConnectionString,
                sqlCommand.ToString(),
                arParams);

            return rowsAffected;

        }

        public static void AddFeature(int siteId, int moduleDefId)
        {
            if (HasFeature(siteId, moduleDefId)) return;

            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("INSERT INTO mp_SiteModuleDefinitions ");
            sqlCommand.Append("( ");
            sqlCommand.Append("SiteID, ");
            sqlCommand.Append("ModuleDefID ");
            sqlCommand.Append(") ");

            sqlCommand.Append("VALUES ");
            sqlCommand.Append("( ");
            sqlCommand.Append("@SiteID, ");
            sqlCommand.Append("@ModuleDefID ");
            sqlCommand.Append(") ;");

            FbParameter[] arParams = new FbParameter[2];

            arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = siteId;

            arParams[1] = new FbParameter("@ModuleDefID", FbDbType.Integer);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = moduleDefId;

            FBSqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                sqlCommand.ToString(),
                arParams);
        }

        protected string _executeNonQuery(FbConnection conn, FbTransaction trans, string query, Dictionary<string, object> prms, bool isReturning)
        {
            string ret = "";

            using (FbCommand cmd = new FbCommand(query, conn, trans))
            {
                foreach (var prm in prms)
                    cmd.Parameters.AddWithValue(prm.Key, prm.Value);

                if (isReturning)
                {
                    FbParameter outparam = new FbParameter("@out", FbDbType.VarChar)
                    {
                        Direction = ParameterDirection.Output
                    };
                    cmd.Parameters.Add(outparam);

                    cmd.ExecuteNonQuery();
                    ret = outparam.Value as string;
                }
                else
                    cmd.ExecuteNonQuery();
            }

            return ret;
        }

        public CanalProgramado InsertarLoteSacrificio(CCall ACanal)
        {

            string pSentencia = "INSERT INTO DRASCCALL (GRANJA, LOTE, FECHA, CANALES) VALUES (@GRANJA, @LOTE, @FECHA, @CANALES) RETURNING CLAVE";
            FbConnection con = _Conexion.ObtenerConexion();

            FbCommand com = new FbCommand(pSentencia, con);
            com.Parameters.Add("@GRANJA", FbDbType.Integer).Value  = ACanal.Granja;
            com.Parameters.Add("@LOTE", FbDbType.VarChar).Value    = ACanal.Lote;
            com.Parameters.Add("@FECHA", FbDbType.Integer).Value   = ACanal.Fecha;
            com.Parameters.Add("@CANALES", FbDbType.Integer).Value = ACanal.Canales;

            FbParameter pOutParameter = new FbParameter("@CLAVE", FbDbType.Integer);
            pOutParameter.Direction = ParameterDirection.Output;
            com.Parameters.Add(pOutParameter);

            try
            {
                con.Open();
                com.ExecuteNonQuery();
            }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                }
            }
            return ObtenerCanalProgramado((int)pOutParameter.Value);
        }

        public LoteNoInventariable InsertarLoteNoInventariable(LoteNoInventariable ALote)
        {
            string pSentencia = "INSERT INTO LOTES_NO_INVENTARIABLES (LOTE) VALUES (@LOTE) RETURNING ID";
            FbConnection con = _Conexion.ObtenerConexion();

            FbCommand com = new FbCommand(pSentencia, con);
            com.Parameters.Add("@LOTE", FbDbType.Integer).Value = ALote.Lote;
            FbParameter pOutParameter = new FbParameter("@ID", FbDbType.Integer);
            pOutParameter.Direction = ParameterDirection.Output;
            com.Parameters.Add(pOutParameter);

            try
            {
                con.Open();
                com.ExecuteNonQuery();
            }
            finally
            {
                if (con.State == System.Data.ConnectionState.Open)
                {
                    con.Close();
                }
            }

            return ObtenerLote((int)pOutParameter.Value);
        }

        /// <summary>
        /// Inserts a row in the mp_BannedIPAddresses table. Returns new integer id.
        /// </summary>
        /// <param name="bannedIP"> bannedIP </param>
        /// <param name="bannedUTC"> bannedUTC </param>
        /// <param name="bannedReason"> bannedReason </param>
        /// <returns>int</returns>
        public int Add(
            string bannedIP,
            DateTime bannedUtc,
            string bannedReason)
        {

            #region Bit Conversion

            #endregion

            FbParameter[] arParams = new FbParameter[3];

            arParams[0] = new FbParameter(":BannedIP", FbDbType.VarChar, 50);
            arParams[0].Value = bannedIP;

            arParams[1] = new FbParameter(":BannedUTC", FbDbType.TimeStamp);
            arParams[1].Value = bannedUtc;

            arParams[2] = new FbParameter(":BannedReason", FbDbType.VarChar, 255);
            arParams[2].Value = bannedReason;

            int newID = Convert.ToInt32(AdoHelper.ExecuteScalar(
                writeConnectionString,
                CommandType.StoredProcedure,
                "EXECUTE PROCEDURE MP_BANNEDIPADDRESSES_INSERT ("
                + AdoHelper.GetParamString(arParams.Length) + ")",
                arParams));

            return newID;
        }

        public static bool ContratoTemItens(string contrato)
        {
            bool retorno = false;

            conn = AcessoDados.AcessoDados.getConn();

            FbCommand comando = new FbCommand("select COUNT(*) as contador from sci_licitacao_contrato_itens lci where lci.ctrcod = @CONTRATO",conn);

            FbParameter IDCONTRATO = new FbParameter("@CONTRATO", FbDbType.Integer);
            IDCONTRATO.Value = contrato;
            comando.Parameters.Add(IDCONTRATO);

            conn.Open();
            FbDataReader dr = comando.ExecuteReader();
            while (dr.Read()) {
                if (Convert.ToInt16(dr["contador"]) > 0)
                {
                    retorno = true;
                }
                else {
                    retorno = false;
                }
            }
            conn.Close();
            comando.Dispose();
            return retorno;
        }

        public static bool AccountLockout(Guid userGuid, DateTime lockoutTime)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("UPDATE mp_Users ");
            sqlCommand.Append("SET IsLockedOut = 1,  ");
            sqlCommand.Append("LastLockoutDate = @LockoutTime  ");
            sqlCommand.Append("WHERE UserGuid = @UserGuid  ;");

            FbParameter[] arParams = new FbParameter[2];

            arParams[0] = new FbParameter("@UserGuid", FbDbType.VarChar, 36);
            arParams[0].Direction = ParameterDirection.Input;
            arParams[0].Value = userGuid.ToString();

            arParams[1] = new FbParameter("@LockoutTime", FbDbType.TimeStamp);
            arParams[1].Direction = ParameterDirection.Input;
            arParams[1].Value = lockoutTime;

            int rowsAffected = FBSqlHelper.ExecuteNonQuery(
                GetConnectionString(),
                sqlCommand.ToString(),
                arParams);

            return (rowsAffected > 0);
        }

        public DbDataReader GetUserCountByYearMonth(int siteId)
        {
            StringBuilder sqlCommand = new StringBuilder();
            sqlCommand.Append("SELECT ");
            sqlCommand.Append("EXTRACT(YEAR FROM DateCreated) As Y,  ");
            sqlCommand.Append("EXTRACT(MONTH FROM DateCreated) As M, ");
            sqlCommand.Append("EXTRACT(YEAR FROM DateCreated) || '-' || EXTRACT(MONTH FROM DateCreated) As Label, ");
            sqlCommand.Append("COUNT(*) As Users ");

            sqlCommand.Append("FROM ");
            sqlCommand.Append("mp_Users ");
            sqlCommand.Append("WHERE ");
            sqlCommand.Append("SiteID = @SiteID ");
            sqlCommand.Append("GROUP BY EXTRACT(YEAR FROM DateCreated), EXTRACT(MONTH FROM DateCreated) ");
            sqlCommand.Append("ORDER BY EXTRACT(YEAR FROM DateCreated), EXTRACT(MONTH FROM DateCreated) ");
            sqlCommand.Append("; ");

            FbParameter[] arParams = new FbParameter[1];

            arParams[0] = new FbParameter("@SiteID", FbDbType.Integer);
            arParams[0].Value = siteId;

            return AdoHelper.ExecuteReader(
                readConnectionString,
                sqlCommand.ToString(),
                arParams);

        }