C# IO.ExecuteScalarQuery方法代码示例

        public int AddMarketTargets(MarketTarget marketTargets)
        {
            try
            {
                List<SqlParameter> spParams = new List<SqlParameter>();
                spParams.Add(Param.CreateParam("MARKETID", SqlDbType.VarChar, marketTargets.marketId));
                spParams.Add(Param.CreateParam("COMPANYID", SqlDbType.Int, marketTargets.companyId));
                spParams.Add(Param.CreateParam("UPDATEDBY", SqlDbType.VarChar, Security.GetCurrentUserId));
                spParams.Add(Param.CreateParam("DIRECTBUCKET1", SqlDbType.Int, marketTargets.directBucket1));
                spParams.Add(Param.CreateParam("DIRECTBUCKET2", SqlDbType.Int, marketTargets.directBucket2));
                spParams.Add(Param.CreateParam("DIRECTBUCKET3", SqlDbType.Int, marketTargets.directBucket3));
                spParams.Add(Param.CreateParam("DIRECTBUCKET4", SqlDbType.Int, marketTargets.directBucket4));
                spParams.Add(Param.CreateParam("DIRECTBUCKET5", SqlDbType.Int, marketTargets.directBucket5));
                spParams.Add(Param.CreateParam("DIRECTBUCKET6", SqlDbType.Int, marketTargets.directBucket6));
                spParams.Add(Param.CreateParam("AGENCYBUCKET1", SqlDbType.Int, marketTargets.agencyBucket1));
                spParams.Add(Param.CreateParam("AGENCYBUCKET2", SqlDbType.Int, marketTargets.agencyBucket2));
                spParams.Add(Param.CreateParam("AGENCYBUCKET3", SqlDbType.Int, marketTargets.agencyBucket3));
                spParams.Add(Param.CreateParam("AGENCYBUCKET4", SqlDbType.Int, marketTargets.agencyBucket4));
                spParams.Add(Param.CreateParam("AGENCYBUCKET5", SqlDbType.Int, marketTargets.agencyBucket5));
                spParams.Add(Param.CreateParam("AGENCYBUCKET6", SqlDbType.Int, marketTargets.agencyBucket6));
                using (IO io = new IO(WebCommon.KPIConnectionString))
                {
                    marketTargets.targetId = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("KPI_ADDMARKETTARGETS", spParams)));
                }

                return marketTargets.targetId;
            }
            catch (Exception ex)
            {
                WebCommon.LogExceptionInfo(ex);
                throw new Exception("An error occurred while trying to add the targets for the Market.");
            }
        }

 public static bool CheckExistingEffectiveDate(Hashtable values)
 {
     int count;
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         count = (int)io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("MASTERDATA_CHECKEXISTINGPROFITCENTERRATEEFFECTIVEDATE",
             Param.CreateParam("PROFITCENTERID", SqlDbType.Int, values["PROFITCENTERID"]),
             Param.CreateParam("EFFECTIVEDATE", SqlDbType.Date, values["EFFECTIVEDATE"])));
     }
     return (count > 0);
 }

        public void AddUpdateBooking(Booking booking)
        {
            bool isAdding = (booking.bookingId == -1);
            int successCode = 0;
            try
            {
                List<System.Data.SqlClient.SqlParameter> spParams = new List<System.Data.SqlClient.SqlParameter>();
                spParams.Add(Param.CreateParam("LOCATIONID", SqlDbType.Int, booking.locationId));
                spParams.Add(Param.CreateParam("BOOKINGID", SqlDbType.Int, booking.bookingId));
                spParams.Add(Param.CreateParam("BOOKINGDESC", SqlDbType.VarChar, booking.bookingDesc));
                spParams.Add(Param.CreateParam("STARTDATE", SqlDbType.DateTime, booking.bookingStartDate));
                spParams.Add(Param.CreateParam("ENDDATE", SqlDbType.DateTime, booking.bookingEndDate));
                spParams.Add(Param.CreateParam("AENTID", SqlDbType.VarChar, booking.aeNtId));
                if (booking.bookingStatusId != -1)
                {
                    spParams.Add(Param.CreateParam("BOOKINGSTATUSID", SqlDbType.Int, booking.bookingStatusId));
                }
                if (!String.IsNullOrEmpty(booking.bookingComments))
                {
                    spParams.Add(Param.CreateParam("BOOKINGCOMMENTS", SqlDbType.VarChar, booking.bookingComments));
                }
                spParams.Add(Param.CreateParam("ENTEREDBY", SqlDbType.VarChar, Security.GetCurrentUserId));
                using (IO io = new IO(WebCommon.ConnectionString))
                {
                    successCode = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("Production_AddUpdateBooking", spParams)));
                }
                if (successCode == -1)
                {
                    throw new Exception("Booking date collision");
                }

            }
            catch (Exception ex)
            {
                WebCommon.LogExceptionInfo(ex);
                if (successCode == -1)
                {
                    throw new Exception(string.Format("An error occurred while trying to {0} the Booking. The dates conflicted with an existing Booking.", (isAdding ? "Add" : "Update")));
                }
                else
                {
                    throw new Exception(string.Format("An error occurred while trying to {0} the Booking.", (isAdding ? "Add" : "Update")));
                }
            }
        }

 public bool AddMTAInvoiceNote(int id, string invoiceNumber, string notes)
 {
     try
     {
         using (IO io = new IO(WebCommon.ConnectionString))
         {
             io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("COLLECTIONS_ADDMTAINVOICENOTE",
                 Param.CreateParam("INVOICENUMBER", SqlDbType.Int, Convert.ToInt32(invoiceNumber)),
                 Param.CreateParam("NOTES", SqlDbType.VarChar, notes),
                 Param.CreateParam("ENTEREDBY", SqlDbType.VarChar, Security.GetCurrentUserId)));
         }
         return true;
     }
     catch (Exception ex)
     {
         WebCommon.LogExceptionInfo(ex);
         return false;
     }
 }

 public static double CollectedToDate(int companyId, string reportDate, string startOfMonth)
 {
     double amountCollected = 0.00;
     string storedProcName = (companyId == 1 ? "KPI_DATA_AMOUNTCOLLECTED" : "KPI_DATA_AMOUNTCOLLECTED_TOC");
     try
     {
         using (IO io = new IO(WebCommon.KPIConnectionString))
         {
             amountCollected = Convert.ToDouble(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc(storedProcName,
                 Param.CreateParam("REPORTDATE", SqlDbType.Date, reportDate),
                 Param.CreateParam("REPORTMONTH", SqlDbType.Int, Convert.ToDateTime(startOfMonth).Month),
                 Param.CreateParam("REPORTYEAR", SqlDbType.Int, Convert.ToDateTime(startOfMonth).Year))));
         }
     }
     catch (Exception ex)
     {
         WebCommon.LogExceptionInfo(ex);
         throw new Exception("An error occurred while trying to retrieve the Amount Collected.");
     }
     return amountCollected;
 }

 public static bool CheckExistingRateDetailEffectiveDate(Hashtable values)
 {
     int count;
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         count = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("MASTERDATA_CHECKEXISTINGRATEDETAILEFFECTIVEDATE",
             Param.CreateParam("RATEID", SqlDbType.Int, values["RATEID"]),
             Param.CreateParam("RATEEFFECTIVEDATE", SqlDbType.Date, values["RATEEFFECTIVEDATE"]))));
     }
     return (count > 0);
 }

 public static bool CheckExistingRateDetailName(string rateDetailName)
 {
     int count;
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         count = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("MASTERDATA_CHECKEXISTINGRATENAME", Param.CreateParam("RATENAME", SqlDbType.VarChar, rateDetailName))));
     }
     return (count > 0);
 }

 /// <summary>TBD</summary>
 /// <param name="aeId">TBD</param>
 /// <param name="companyId">TBD</param>
 /// <param name="flatRateEffectiveDate">TBD</param>
 /// <returns>TBD</returns>
 public static bool CheckExistingFlatRateEffectiveDate(string aeId, int companyId, DateTime flatRateEffectiveDate)
 {
     List<System.Data.SqlClient.SqlParameter> spParams = new List<System.Data.SqlClient.SqlParameter>();
     spParams.Add(Param.CreateParam("AEID", SqlDbType.VarChar, aeId));
     spParams.Add(Param.CreateParam("COMPANYID", SqlDbType.Int, companyId));
     spParams.Add(Param.CreateParam("FLATRATEEFFECTIVEDATE", SqlDbType.Date, flatRateEffectiveDate));
     int count = 0;
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         count = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("MASTERDATA_CHECKEXISTINGFLATRATEEFFECTIVEDATE", spParams)));
     }
     return (count > 0);
 }

 public static bool CheckExistingCommissionAmountYear(string aeId, int companyId, int year)
 {
     int count;
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         count = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("MASTERDATA_CHECKEXISTINGCOMMISSIONAMOUNTYEAR",
             Param.CreateParam("AEID", SqlDbType.VarChar, aeId),
             Param.CreateParam("COMPANYID", SqlDbType.Int, companyId),
             Param.CreateParam("YEAR", SqlDbType.Int, year))));
     }
     return (count > 0);
 }

 public static bool CheckExistingEffectiveDate(Hashtable values)
 {
     try
     {
         using (IO io = new IO(WebCommon.ConnectionString))
         {
             return ((int)io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("COMMISSIONS_CHECKEXISTINGCONTRACTCOMMISSIONEFFECTIVEDATE",
                 Param.CreateParam("CONTRACTNUMBER", SqlDbType.Int, values["CONTRACTNUMBER"]),
                 Param.CreateParam("COMPANYID", SqlDbType.Int, values["COMPANYID"]),
                 Param.CreateParam("EFFECTIVEDATE", SqlDbType.Date, values["EFFECTIVEDATE"]))) > 0);
         }
     }
     catch (Exception ex)
     {
         WebCommon.LogExceptionInfo(ex);
         throw new Exception("An error occurred while checking for an existing effective date.");
     }
 }

 public Hashtable GetNewBatch()
 {
     int batchId;
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         batchId = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("DigitalLibrary_AddBatch", Param.CreateParam("UPLOADEDBY", SqlDbType.VarChar, Security.GetCurrentUserId))));
     }
     Hashtable results = new Hashtable();
     results.Add("BATCHID", batchId);
     results.Add("USERID", Security.GetCurrentUserId);
     return results;
 }

 public int AddNonRevContract(Hashtable values)
 {
     List<System.Data.SqlClient.SqlParameter> spParams = new List<System.Data.SqlClient.SqlParameter>();
     spParams.Add(Param.CreateParam("PRODUCTCLASSID", SqlDbType.Int, values["PRODUCTCLASSID"]));
     spParams.Add(Param.CreateParam("AGENCYID", SqlDbType.VarChar, values["AGENCYID"]));
     spParams.Add(Param.CreateParam("ADVERTISERID", SqlDbType.VarChar, values["ADVERTISERID"]));
     spParams.Add(Param.CreateParam("AEID", SqlDbType.VarChar, values["AEID"]));
     spParams.Add(Param.CreateParam("PROGRAM", SqlDbType.VarChar, values["PROGRAM"]));
     spParams.Add(Param.CreateParam("MEDIAFORMID", SqlDbType.Int, values["MEDIAFORMID"]));
     spParams.Add(Param.CreateParam("PROFITCENTERID", SqlDbType.Int, values["PROFITCENTERID"]));
     spParams.Add(Param.CreateParam("QUANTITY", SqlDbType.Int, values["QUANTITY"]));
     spParams.Add(Param.CreateParam("COMPANYID", SqlDbType.Int, values["COMPANYID"]));
     int contractNumber;
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         contractNumber = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("DIGITALLIBRARY_ADDNONREVENUECONTRACT", spParams)));
     }
     return contractNumber;
 }

 public void Save()
 {
     List<System.Data.SqlClient.SqlParameter> spParams = new List<System.Data.SqlClient.SqlParameter>();
     spParams.Add(Param.CreateParam("PRODUCTCLASSID", SqlDbType.Int, this.ProductClassId));
     spParams.Add(Param.CreateParam("AGENCYID", SqlDbType.VarChar, this.AgencyId));
     spParams.Add(Param.CreateParam("ADVERTISERID", SqlDbType.VarChar, this.AdvertiserId));
     spParams.Add(Param.CreateParam("AEID", SqlDbType.VarChar, this.AEId));
     spParams.Add(Param.CreateParam("PROGRAM", SqlDbType.VarChar, this.Program));
     spParams.Add(Param.CreateParam("MEDIAFORMID", SqlDbType.Int, this.MediaFormId));
     spParams.Add(Param.CreateParam("PROFITCENTERID", SqlDbType.Int, this.ProfitCenterId));
     spParams.Add(Param.CreateParam("QUANTITY", SqlDbType.Int, this.Quantity));
     spParams.Add(Param.CreateParam("COMPANYID", SqlDbType.Int, this.CompanyId));
     using (IO io = new IO(WebCommon.ConnectionString))
     {
         this.ContractNumber = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc("DIGITALLIBRARY_ADDNONREVENUECONTRACT", spParams)));
     }
 }

 /// <summary>TBD</summary>
 /// <param name="uidCmpgn">TBD</param>
 /// <param name="attachmentId">TBD</param>
 /// <param name="isCompletion">TBD</param>
 /// <returns>TBD</returns>
 public bool IsCCNEMailSent(int uidCmpgn, int attachmentId, bool isCompletion)
 {
     try
     {
         string storedProcName = (isCompletion ? "QUATTRO_CCNREPORT_CHECKFORCOMPLETIONEMAIL" : "QUATTRO_CCNREPORT_CHECKFORNOTIFICATIONEMAIL");
         int result;
         using (IO io = new IO(WebCommon.ConnectionString))
         {
             result = Convert.ToInt32(io.ExecuteScalarQuery(IO.CreateCommandFromStoredProc(storedProcName,
                 Param.CreateParam("UIDCMPGN", SqlDbType.Int, uidCmpgn),
                 Param.CreateParam("ATTACHMENTID", SqlDbType.Int, attachmentId))));
         }
         return (result != 0);
     }
     catch (Exception ex)
     {
         WebCommon.LogExceptionInfo(ex);
         return false;
     }
 }

 /// <summary>TBD</summary>
 public void Save()
 {
     using (IO io = new IO(WebCommon.DigitalAvailsConnectionString))
     {
         using (SqlCommand cmd = IO.CreateCommandFromStoredProc("DigitalAvails_AddSpot"))
         {
             cmd.Parameters.Add(Param.CreateParam("STATIONID", SqlDbType.Int, StationId));
             cmd.Parameters.Add(Param.CreateParam("STATIONSPOTID", SqlDbType.Int, StationSpotId));
             cmd.Parameters.Add(Param.CreateParam("SPOTTYPEID", SqlDbType.Int, SpotTypeId));
             cmd.Parameters.Add(Param.CreateParam("SPOTDATE", SqlDbType.Date, SpotDate));
             cmd.Parameters.Add(Param.CreateParam("YEAR", SqlDbType.Int, Year));
             cmd.Parameters.Add(Param.CreateParam("MONTH", SqlDbType.Int, Month));
             cmd.Parameters.Add(Param.CreateParam("WEEK", SqlDbType.Int, Week));
             cmd.Parameters.Add(Param.CreateParam("CAMPAIGNNAME", SqlDbType.VarChar, CampaignName.Trim()));
             if (!String.IsNullOrEmpty(Description.Trim()))
             {
                 cmd.Parameters.Add(Param.CreateParam("DESCRIPTION", SqlDbType.VarChar, Description.Trim()));
             }
             if (!String.IsNullOrEmpty(CampaignNumber.Trim()))
             {
                 cmd.Parameters.Add(Param.CreateParam("CAMPAIGNNUMBER", SqlDbType.VarChar, CampaignNumber));
             }
             cmd.Parameters.Add(Param.CreateParam("ENTEREDBY", SqlDbType.VarChar, EnteredBy));
             cmd.Parameters.Add(Param.CreateParam("ENTEREDON", SqlDbType.DateTime, EnteredOn));
             cmd.Parameters.Add(Param.CreateParam("LASTUPDATEDBY", SqlDbType.VarChar, LastUpdatedBy));
             cmd.Parameters.Add(Param.CreateParam("LASTUPDATEDON", SqlDbType.DateTime, LastUpdatedOn));
             int result = Convert.ToInt32(io.ExecuteScalarQuery(cmd));
             if (result < 0)
             {
                 throw new SpotConflictException(string.Format("A Spot Conflict Exception occurred. Spot Date: {0}. Result: {1}.", this.SpotDate.ToShortDateString(), result));
             }
             this.SpotId = result;
         }
     }
 }