C# SQLHandler类代码示例

 public static void DeleteSearchTerm(string Ids, AspxCommonInfo aspxCommonObj)
 {
     List<KeyValuePair<string, object>> parameter = CommonParmBuilder.GetParamSPUC(aspxCommonObj);
     parameter.Add(new KeyValuePair<string, object>("@SearchTermID", Ids));
     SQLHandler sqlH = new SQLHandler();
     sqlH.ExecuteNonQuery("usp_Aspx_DeleteSearchTerm", parameter);
 }

        public static List<Roles> GetAllRoles(int portalID,bool isAll,string userName)
        {
            List<Roles> lstRoles = new List<Roles>();
            string StoredProcedureName = "sp_PortalRoleList";
            SQLHandler sagesql = new SQLHandler();
            List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", portalID));
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@IsAll", isAll));
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserName", userName));

            SqlDataReader SQLReader = null;
            try
            {
                SQLReader = sagesql.ExecuteAsDataReader(StoredProcedureName, ParaMeterCollection);
                while (SQLReader.Read())
                {
                    Roles obj = new Roles();
                    obj.RoleID = new Guid(SQLReader["RoleID"].ToString());
                    obj.RoleName = SQLReader["RoleName"].ToString();
                    lstRoles.Add(obj);
                }
            }
            catch(Exception)
            {

            }
            finally
            {
                if (SQLReader != null)
                {
                    SQLReader.Close();
                }
            }
            return lstRoles;
        }

 public void AddUpdateCoupons(int couponID, int couponTypeID, string couponCode, string couponAmount, string validateFrom, string validateTo,
 string isActive, int storeID, int portalID, string cultureName, string userName, string settingIDs, string settingValues, string portalUser_UserName)
 {
     try
     {
         List<KeyValuePair<string, object>> parameter = new List<KeyValuePair<string, object>>();
         parameter.Add(new KeyValuePair<string, object>("@CouponID", couponID));
         parameter.Add(new KeyValuePair<string, object>("@CouponTypeID", couponTypeID));
         parameter.Add(new KeyValuePair<string, object>("@CouponCode", couponCode));
         parameter.Add(new KeyValuePair<string, object>("@CouponAmount", couponAmount));
         parameter.Add(new KeyValuePair<string, object>("@ValidateFrom", validateFrom));
         parameter.Add(new KeyValuePair<string, object>("@ValidateTo", validateTo));
         parameter.Add(new KeyValuePair<string, object>("@IsActive", isActive));
         parameter.Add(new KeyValuePair<string, object>("@StoreID", storeID));
         parameter.Add(new KeyValuePair<string, object>("@PortalID", portalID));
         parameter.Add(new KeyValuePair<string, object>("@CultureName", cultureName));
         parameter.Add(new KeyValuePair<string, object>("@UserName", userName));
         parameter.Add(new KeyValuePair<string, object>("@SettingIDs", settingIDs));
         parameter.Add(new KeyValuePair<string, object>("@SettingValues", settingValues));
         parameter.Add(new KeyValuePair<string, object>("@portalUser_UserName", portalUser_UserName));
         SQLHandler sqlH = new SQLHandler();
         sqlH.ExecuteNonQuery("usp_Aspx_AddUpdateCoupons", parameter);
     }
     catch (Exception ex)
     {
         throw ex;
     }
 }

 /// <summary>
 /// To Bind Attribute Type dropdown
 /// </summary>
 /// <returns></returns>
 public List<AttributesInputTypeInfo> GetAttributesInputType()
 {
     List<AttributesInputTypeInfo> ml;
     SQLHandler sqlH = new SQLHandler();
     ml = sqlH.ExecuteAsList<AttributesInputTypeInfo>("dbo.usp_Aspx_AttributesInputTypeGetAll");
     return ml;
 }

        /// <summary>
        /// Connects to database and returns roles details by role name.
        /// </summary>
        /// <param name="RoleName">Role name.</param>
        /// <returns>Role details.</returns>
        public RolesManagementInfo GetRoleIDByRoleName(string RoleName)
        {
            SqlDataReader reader = null;
            try
            {

                SQLHandler SQLH = new SQLHandler();
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@RoleName", RoleName));
                reader = SQLH.ExecuteAsDataReader("[dbo].[sp_GetRoleIDByRoleName]", ParamCollInput);
                RolesManagementInfo objList = new RolesManagementInfo();
                while (reader.Read())
                {
                    objList.ApplicationId = new Guid(reader["ApplicationId"].ToString());
                    objList.RoleId = new Guid(reader["RoleId"].ToString());
                    objList.RoleName = reader["RoleName"].ToString();
                    objList.LoweredRoleName = reader["LoweredRoleName"].ToString();
                    objList.Description = reader["Description"].ToString();
                }
                reader.Close();
                return objList;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (reader != null)
                {
                    reader.Close();
                }
            }
        }

        public void ParseIPN(int orderId, string transId, string status, int storeId, int portalId, string userName, int customerId, string sessionCode)
        {
            var ph = new PayPalHandler();
            try
            {

                var ot = new OrderDetailsCollection();
                var odinfo = new OrderDetailsInfo();
                var cms = new CartManageSQLProvider();
                var cf = new CommonInfo {StoreID = storeId, PortalID = portalId, AddedBy = userName};
                // UpdateOrderDetails
            
                var sqlH = new SQLHandler();
                // use split to split array we already have using "=" as delimiter
                // WcfSession ws = new WcfSession();
                odinfo.OrderID = orderId;//ws.GetSessionVariable("OrderID");
                odinfo.ResponseReasonText = status;
                odinfo.TransactionID = transId;
                ot.ObjOrderDetails = odinfo;
                ot.ObjCommonInfo = cf;
                odinfo.OrderStatusID = 8;
                AspxOrderController.UpdateOrderDetails(ot);

               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

 public static List<PricingRuleAttributeInfo> GetPricingRuleAttributes(AspxCommonInfo aspxCommonObj)
 {
     SQLHandler sqlHandler = new SQLHandler();
     List<KeyValuePair<string, object>> paramList = CommonParmBuilder.GetParamSPUC(aspxCommonObj);
     List<PricingRuleAttributeInfo> lstPriceRuleAttr = sqlHandler.ExecuteAsList<PricingRuleAttributeInfo>("usp_Aspx_GetPricingRuleAttr", paramList);
     return lstPriceRuleAttr;
 }

        public int AddProfile(string Name, int PropertyTypeID, string DataType, bool IsRequired, bool IsActive, DateTime AddedOn, int PortalID, string AddedBy)
        {
            try
            {
                string sp = "[dbo].[sp_ProfileAdd]";
                SQLHandler SQLH = new SQLHandler();
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();

                ParamCollInput.Add(new KeyValuePair<string, object>("@Name", Name));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PropertyTypeID", PropertyTypeID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@DataType", DataType));
                ParamCollInput.Add(new KeyValuePair<string, object>("@IsRequired", IsRequired));
                ParamCollInput.Add(new KeyValuePair<string, object>("@IsActive", IsActive));
                ParamCollInput.Add(new KeyValuePair<string, object>("@AddedOn", AddedOn));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@AddedBy", AddedBy));

                int PID = SQLH.ExecuteNonQueryAsGivenType<int>(sp, ParamCollInput, "@ProfileID");
                return PID;
            }
            catch (Exception)
            {

                throw;
            }
        }

 protected void btnSubmit_Click(object sender, EventArgs e)
 {
     if(passBox.Text == confirmBox.Text)
     {
         SQLHandler sql = new SQLHandler();
         int status = sql.CreateUser(emailBox.Text, passBox.Text, nameBox.Text);
         if(status == 0)
         {
             statusLabel.ForeColor = Color.Green;
             statusLabel.Text = "User created successfully!";
         }
         else if(status == 1)
         {
             statusLabel.ForeColor = Color.Red;
             statusLabel.Text = "Backend error, please try again.";
         }
         else if (status == 2)
         {
             statusLabel.ForeColor = Color.Red;
             statusLabel.Text = "Name already taken.";
         }
         else if (status == 3)
         {
             statusLabel.ForeColor = Color.Red;
             statusLabel.Text = "Email already being used.";
         }
     }
     else
     {
         statusLabel.ForeColor = Color.Red;
         statusLabel.Text = "Passwords doesn't match!";
     }
 }

        public static void NotificationSaveUpdateSettings(NotificationSettingsInfo saveUpdateInfo, AspxCommonInfo aspxCommonObj)
        {
            try
            {
                List<KeyValuePair<string, object>> parameterCollection = CommonParmBuilder.GetParamSPUC(aspxCommonObj);
                parameterCollection.Add(new KeyValuePair<string, object>("@AllActive", saveUpdateInfo.AllActive));
                parameterCollection.Add(new KeyValuePair<string, object>("@UserNotificationActive", saveUpdateInfo.UserNotificationActive));
                parameterCollection.Add(new KeyValuePair<string, object>("@UserNotificationCount", saveUpdateInfo.UserNotificationCount));
                parameterCollection.Add(new KeyValuePair<string, object>("@SubscriptionNotificationActive ", saveUpdateInfo.SubscriptionNotificationActive));
                parameterCollection.Add(new KeyValuePair<string, object>("@SubscriptionNotificationCount", saveUpdateInfo.SubscriptionNotificationCount));
                parameterCollection.Add(new KeyValuePair<string, object>("@OutofStockNotificationActive", saveUpdateInfo.OutofStockNotificationActive));
                parameterCollection.Add(new KeyValuePair<string, object>("@OutofStockNotificationCount", saveUpdateInfo.OutofStockNotificationCount));
                parameterCollection.Add(new KeyValuePair<string, object>("@ItemsLowStockNotificationActive", saveUpdateInfo.ItemsLowStockNotificationActive));
                parameterCollection.Add(new KeyValuePair<string, object>("@ItemsLowStockCount", saveUpdateInfo.ItemsLowStockCount));
                parameterCollection.Add(new KeyValuePair<string, object>("@OrdersNotificationAtive", saveUpdateInfo.OrdersNotificationAtive));
                parameterCollection.Add(new KeyValuePair<string, object>("@OrdersNotificationCount", saveUpdateInfo.OrdersNotificationCount));
                SQLHandler sqlH = new SQLHandler();
                sqlH.ExecuteNonQuery("[dbo].[usp_Aspx_NotificationSaveUpdateSettings]", parameterCollection);

            }
            catch (Exception e)
            {
                throw e;
            }
        }

 /// <summary>
 ///Connect to database and obtain active template.
 /// </summary>
 /// <param name="PortalID">PortalID</param>
 /// <returns>Object of TemplateInfo class.</returns>
 public static TemplateInfo GetActiveTemplate(int PortalID)
 {   
     SQLHandler sagesql = new SQLHandler();
     List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
     ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", PortalID));
     return (sagesql.ExecuteAsObject<TemplateInfo>("usp_sftemplate_GetActiveTemplate",ParaMeterCollection));
 }

    private void LoadSettingtoControl()
    {
        try
        {
            ddlSubscriptionType.Items.Clear();
            BindSubscriptionTypeList();

            List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@UserModuleID", usermoduleIDControl));
            ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", GetPortalID));
            SQLHandler objSql = new SQLHandler();
            NewsLetterSettingsInfo newsLetterSettingObj = objSql.ExecuteAsObject<NewsLetterSettingsInfo>("dbo.sp_NewsLetterSettingsGetAll", ParaMeterCollection);

            if (newsLetterSettingObj != null)
            {
                ddlSubscriptionType.SelectedIndex = ddlSubscriptionType.Items.IndexOf(ddlSubscriptionType.Items.FindByValue(newsLetterSettingObj.SubscriptionType.ToString()));
                txtSubscriptionModuleTitle.Text = newsLetterSettingObj.SubscriptionModuleTitle.ToString();
                txtSubscriptionHelpText.Text = newsLetterSettingObj.SubscriptionHelpText.ToString();
                txtTextBoxWaterMark.Text = newsLetterSettingObj.TextBoxWaterMarkText.ToString()!=""?newsLetterSettingObj.TextBoxWaterMarkText.ToString():"Email Address Required";
                txtSubmitButtonText.Text = newsLetterSettingObj.SubmitButtonText.ToString();
            }
        }
        catch (Exception ex)
        {
            ProcessException(ex);
        }
    }

 public TickerInfo getTickerObject(int tickerID)
 {
     List<KeyValuePair<string, object>> Parameter = new List<KeyValuePair<string, object>>();
     Parameter.Add(new KeyValuePair<string, object>("@tickerID", tickerID));
     SQLHandler sqlH = new SQLHandler();
     return sqlH.ExecuteAsObject<TickerInfo>("[usp_TickerGetByTickerID]", Parameter);
 }

 public List<ContactUsInfo> ContactUsGetAll(int portalID)
 {
     List<KeyValuePair<string, object>> ParaMeterCollection = new List<KeyValuePair<string, object>>();
     ParaMeterCollection.Add(new KeyValuePair<string, object>("@PortalID", portalID));
     SQLHandler sqlH = new SQLHandler();
     return sqlH.ExecuteAsList<ContactUsInfo>("sp_ContactUsGetAll", ParaMeterCollection);
 }

        public void SaveLinks(List<CDNInfo> objInfo)
        {
            SQLHandler sagesql = new SQLHandler();
            string sp = "[dbo].[usp_CDNSaveLink]";
            foreach (CDNInfo cdn in objInfo)
            {
                List<KeyValuePair<string, object>> ParamCollInput = new List<KeyValuePair<string, object>>();
                ParamCollInput.Add(new KeyValuePair<string, object>("@URL", cdn.URL));
                ParamCollInput.Add(new KeyValuePair<string, object>("@IsJS", cdn.IsJS));
                ParamCollInput.Add(new KeyValuePair<string, object>("@URLOrder", cdn.URLOrder));
                ParamCollInput.Add(new KeyValuePair<string, object>("@PortalID", cdn.PortalID));
                ParamCollInput.Add(new KeyValuePair<string, object>("@Mode", cdn.Mode));
                try
                {
                    sagesql.ExecuteNonQuery(sp, ParamCollInput);

                }
                catch (Exception)
                {

                    throw;
                }

            }

        }