//отметить категорию или нет public static bool GetCategorySelectionState(int categoryId, int taxID) { bool result = false; try { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "SELECT 'True' FROM [Catalog].[TaxSelectedCategories] WHERE [TaxID] = @TaxID AND [CategoryID] = @CategoryID;"; db.cmd.CommandType = CommandType.Text; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@CategoryID", categoryId); db.cmd.Parameters.AddWithValue("@TaxID", taxID); db.cnOpen(); using (var reader = db.cmd.ExecuteReader()) result = reader.Read(); db.cnClose(); } } catch (Exception ex) { Debug.LogError(ex); } return result; }
public static bool GetProductSelectionState(int productId, int taxID) { bool result = false; try { using (var da = new SQLDataAccess()) { da.cmd.CommandType = CommandType.Text; da.cmd.CommandText = "select count(Product.ProductID) from catalog.Product inner join catalog.TaxMappingOnProduct on TaxMappingOnProduct.ProductID = Product.ProductID where Product.ProductID=@ProductID and [TaxID] = @TaxID"; da.cmd.Parameters.Clear(); da.cmd.Parameters.AddWithValue("@ProductID", productId); da.cmd.Parameters.AddWithValue("@TaxID", taxID); da.cnOpen(); var useTax = SQLDataHelper.GetInt(da.cmd.ExecuteScalar()); result = useTax != 0; da.cnClose(); } } catch (Exception ex) { Debug.LogError(ex); } return result; }
public static void AddOrderByRequest(OrderByRequest orderByRequest) { using (var db = new SQLDataAccess()) { db.cmd.CommandText = " INSERT INTO [Order].[OrderByRequest] " + " ([ProductID], [ProductName], [ArtNo], [Quantity], [UserName], [Email], [Phone], [Comment], [IsComplete], [RequestDate], [OfferID] ) " + " VALUES (@ProductID, @ProductName, @ArtNo, @Quantity, @UserName, @Email, @Phone, @Comment, @IsComplete, @RequestDate, @OfferID); SELECT SCOPE_IDENTITY();"; db.cmd.CommandType = CommandType.Text; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@ProductID", orderByRequest.ProductId); db.cmd.Parameters.AddWithValue("@ProductName", orderByRequest.ProductName); db.cmd.Parameters.AddWithValue("@ArtNo", orderByRequest.ArtNo); db.cmd.Parameters.AddWithValue("@Quantity", orderByRequest.Quantity); db.cmd.Parameters.AddWithValue("@UserName", orderByRequest.UserName); db.cmd.Parameters.AddWithValue("@Email", orderByRequest.Email); db.cmd.Parameters.AddWithValue("@Phone", orderByRequest.Phone); db.cmd.Parameters.AddWithValue("@Comment", orderByRequest.Comment); db.cmd.Parameters.AddWithValue("@IsComplete", orderByRequest.IsComplete); db.cmd.Parameters.AddWithValue("@RequestDate", orderByRequest.RequestDate); db.cmd.Parameters.AddWithValue("@OfferID", orderByRequest.OfferId); db.cnOpen(); orderByRequest.OrderByRequestId = SQLDataHelper.GetInt(db.cmd.ExecuteScalar()); db.cnClose(); } }
public static void ClearOldSessionDataInDb() { using (var db = new SQLDataAccess(GetSessionServiceConnectionString())) { db.cmd.CommandText = "[dbo].[DeleteExpiredSessions]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cnOpen(); db.cmd.ExecuteNonQuery(); db.cnClose(); } }
protected void btnClearOrders_Click(object sender, EventArgs e) { try { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "DBCC CHECKIDENT (\'Order.Order\', RESEED, 0)"; db.cmd.CommandType = CommandType.Text; db.cnOpen(); db.cmd.ExecuteNonQuery(); db.cnClose(); } lblResetOrderResult.Text = @"- True - <span style='color:green;'>Success - Orders number successfully dropped to 0</span>"; } catch (Exception ex) { lblResetOrderResult.Text = string.Format("- False - <span style=\'color:red;\'>Fail - {0}</span>", ex.Message); } }
/// <summary> /// add category /// </summary> /// <param name="cat"></param> /// <param name="updateCache"></param> /// <param name="db"></param> /// <returns></returns> public static int AddCategory(Category cat, bool updateCache, SQLDataAccess db) { db.cmd.CommandText = "[Catalog].[sp_AddCategory]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@Description", cat.Description ?? (object)DBNull.Value); db.cmd.Parameters.AddWithValue("@BriefDescription", cat.BriefDescription ?? (object)DBNull.Value); db.cmd.Parameters.AddWithValue("@Name", cat.Name); db.cmd.Parameters.AddWithValue("@ParentCategory", cat.ParentCategoryId); db.cmd.Parameters.AddWithValue("@SortOrder", cat.SortOrder); db.cmd.Parameters.AddWithValue("@Enabled", cat.Enabled); db.cmd.Parameters.AddWithValue("@DisplayStyle", cat.DisplayStyle ?? String.Empty); db.cmd.Parameters.AddWithValue("@DisplayChildProducts", cat.DisplayChildProducts); db.cmd.Parameters.AddWithValue("@DisplayBrandsInMenu", cat.DisplayBrandsInMenu); db.cmd.Parameters.AddWithValue("@DisplaySubCategoriesInMenu", cat.DisplaySubCategoriesInMenu); db.cmd.Parameters.AddWithValue("@UrlPath", cat.UrlPath); db.cnOpen(); var id = SQLDataHelper.GetInt(db.cmd.ExecuteScalar()); db.cnClose(); if (updateCache) { CacheManager.Remove(CacheNames.GetCategoryCacheObjectName(cat.ParentCategoryId)); foreach (var category in GetChildCategoriesByCategoryId(0, true)) { CacheManager.Remove("MenuCatalog" + category.CategoryId); CacheManager.Remove("MenuCatalog_Alternative" + category.CategoryId); } CacheManager.Remove("MenuCatalog0"); CacheManager.Remove("MenuCatalog_Alternative0"); if (cat.ParentCategoryId == 0) { var cacheName = CacheNames.GetBottomMenuCacheObjectName(); if (CacheManager.Contains(cacheName)) CacheManager.Remove(cacheName); } } return id; }
public static KeyValuePair<float, float> GetMonthProgress() { KeyValuePair<float, float> res; using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Order].[sp_GetOrdersMonthProgress]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cnOpen(); using (SqlDataReader reader = db.cmd.ExecuteReader()) { reader.Read(); res = new KeyValuePair<float, float>(SQLDataHelper.GetFloat(reader, "Sum"), SQLDataHelper.GetFloat(reader, "Profit")); reader.Close(); } db.cnClose(); } return res; }
private DataTable fncCreaLista(string vSql) { DataTable dt = SQLDataAccess.Trae(vSql, cUtil.SQLConec(cParamXml.strConec)); return(dt); }
public static CallbackCustomer GetCallbackCustomer(int id) { return(SQLDataAccess.ExecuteReadOne <CallbackCustomer>("SELECT * FROM [Module].[" + _moduleName + "] Where ID=@ID", CommandType.Text, GetCallbackCustomerFromReader, new SqlParameter("@ID", id))); }
public static void UpdateOrderByRequest(OrderByRequest orderByRequest) { using (var db = new SQLDataAccess()) { db.cmd.CommandText = " UPDATE [Order].[OrderByRequest] SET [ProductID] = @ProductID, [ProductName] = @ProductName, [ArtNo] = @ArtNo, [Quantity] = @Quantity, [UserName] = @UserName, [Email] = @Email, [Phone] = @Phone, [Comment] = @Comment, [IsComplete] = @IsComplete, [RequestDate] = @RequestDate, [OfferID] = @OfferID " + " WHERE OrderByRequestId = @OrderByRequestId"; db.cmd.CommandType = CommandType.Text; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@OrderByRequestId", orderByRequest.OrderByRequestId); db.cmd.Parameters.AddWithValue("@ProductID", orderByRequest.ProductId); db.cmd.Parameters.AddWithValue("@ProductName", orderByRequest.ProductName); db.cmd.Parameters.AddWithValue("@ArtNo", orderByRequest.ArtNo); db.cmd.Parameters.AddWithValue("@Quantity", orderByRequest.Quantity); db.cmd.Parameters.AddWithValue("@UserName", orderByRequest.UserName); db.cmd.Parameters.AddWithValue("@Email", orderByRequest.Email); db.cmd.Parameters.AddWithValue("@Phone", orderByRequest.Phone); db.cmd.Parameters.AddWithValue("@Comment", orderByRequest.Comment); db.cmd.Parameters.AddWithValue("@IsComplete", orderByRequest.IsComplete); db.cmd.Parameters.AddWithValue("@RequestDate", orderByRequest.RequestDate); db.cmd.Parameters.AddWithValue("@OfferID", orderByRequest.OfferId); db.cnOpen(); db.cmd.ExecuteNonQuery(); db.cnClose(); } }
public static void DeleteTax(int taxId) { SQLDataAccess.ExecuteNonQuery("DELETE FROM [Catalog].[Tax] WHERE [TaxId] = @TaxId", CommandType.Text, new SqlParameter("@TaxId", taxId)); }
public static void DeleteCertificateTaxes() { SQLDataAccess.ExecuteScalar("DELETE FROM [Settings].[GiftCertificateTaxes]", CommandType.Text); }
public static PaymentMethod GetPaymentMethodByType(PaymentType type) { return(SQLDataAccess.ExecuteReadOne <PaymentMethod>( "SELECT top(1) * FROM [Order].[PaymentMethod] WHERE [PaymentType] = @PaymentType", CommandType.Text, reader => GetPaymentMethodFromReader(reader), new SqlParameter("@PaymentType", (int)type))); }
public static IEnumerable <int> GetAllPaymentMethodIDs() { return(SQLDataAccess.ExecuteReadColumnIEnumerable <int>("SELECT [PaymentMethodID] FROM [Order].[PaymentMethod]", CommandType.Text, "PaymentMethodID")); }
/// <summary> /// Execute a simple sql command with custom connectionString and see result /// </summary> /// <param name="strConnectionString"></param> /// <returns></returns> /// <remarks></remarks> public static bool PingDateBase(string strConnectionString) { var boolRes = false; try { using (var db = new SQLDataAccess(strConnectionString)) { db.cmd.CommandText = "SELECT GETDATE() AS NOWDATE"; db.cmd.CommandType = CommandType.Text; db.cmd.CommandTimeout = 3; object obj = null; db.cnOpen(); if (db.cnStatus() == ConnectionState.Open) { obj = db.cmd.ExecuteScalar(); } db.cnClose(); if ((obj != null) && (!(obj is DBNull))) { boolRes = true; } } } catch { boolRes = false; } return boolRes; }
/// <summary> /// Check if database version is current /// </summary> /// <returns></returns> /// <remarks></remarks> public static bool CheckDBVersion() { try { string strDbVersion = ""; using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Settings].[sp_GetInternalSetting]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@settingKey", "db_version"); db.cnOpen(); strDbVersion = (string)db.cmd.ExecuteScalar(); db.cnClose(); } return (strDbVersion == GetDbVersion); } catch { // nothing here } return false; }
protected void Page_PreRender(object sender, EventArgs e) { if (grid.UpdatedRow != null) { try { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "Update Catalog.Tax set name=@name, Enabled=@Enabled, RegNumber = @RegNumber, CountryId = @countryId where TaxID = @id"; db.cmd.CommandType = CommandType.Text; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@name", grid.UpdatedRow["Name"]); db.cmd.Parameters.AddWithValue("@Enabled", grid.UpdatedRow["Enable"]); db.cmd.Parameters.AddWithValue("@RegNumber", grid.UpdatedRow["RegNumber"]); db.cmd.Parameters.AddWithValue("@id", grid.UpdatedRow["ID"]); db.cmd.Parameters.AddWithValue("@countryId", grid.UpdatedRow["CountryID"]); db.cnOpen(); db.cmd.ExecuteNonQuery(); db.cnClose(); } } catch (Exception ex) { Debug.LogError(ex); } } DataTable data = _paging.PageItems; while (data.Rows.Count < 1 && _paging.CurrentPageIndex > 1) { _paging.CurrentPageIndex--; data = _paging.PageItems; } var clmn = new DataColumn("IsSelected", typeof(bool)) { DefaultValue = _inverseSelection }; data.Columns.Add(clmn); if ((_selectionFilter != null) && (_selectionFilter.Values != null)) { for (int i = 0; i <= data.Rows.Count - 1; i++) { int intIndex = i; if (Array.Exists(_selectionFilter.Values, c => c == data.Rows[intIndex]["ID"].ToString())) { data.Rows[i]["IsSelected"] = !_inverseSelection; } } } if (data.Rows.Count < 1) { goToPage.Visible = false; } grid.DataSource = data; grid.DataBind(); var countries = CountryService.GetAllCountries(); ddlCountryFilter.DataSource = countries; ddlCountryFilter.DataBind(); ddlCountryFilter.Items.Insert(0, new ListItem(Resources.Resource.Admin_Taxes_AnyCountry, "0")); if (grid.FooterRow != null) { var ddlCountry = ((DropDownList)grid.FooterRow.FindControl("ddlCountry")); ddlCountry.DataSource = countries; ddlCountry.DataBind(); } ddlCountryFilter.SelectedIndex = 0; pageNumberer.PageCount = _paging.PageCount; lblFound.Text = _paging.TotalRowsCount.ToString(); }
/// <summary> /// write aux to file directly /// </summary> private void CreateAux() { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "SELECT [StaticPageID], [PageName],[UrlPath] FROM [CMS].[StaticPage] WHERE [IndexAtSiteMap] = 1 and enabled=1 ORDER BY [SortOrder]"; db.cnOpen(); using (var read = db.cmd.ExecuteReader()) { bool tempHaveItem = read.HasRows; if (tempHaveItem) _sw.WriteLine("<b>" + Resource.Client_Sitemap_StaticPages + " </b> <ul>"); while (read.Read()) _sw.WriteLine("<li><a href='{0}'>{1}</a></li>", _prefUrl + UrlService.GetLink(ParamType.StaticPage, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["StaticPageID"])), read["PageName"]); if (tempHaveItem) _sw.WriteLine("</ul>"); } } }
private void GetSubCategories(int categoryId) { _sw.WriteLine("<ul>"); using (var db = new SQLDataAccess()) { db.cmd.CommandText = "SELECT [CategoryID], [Name], [ParentCategory],[UrlPath] FROM [Catalog].[Category] WHERE [Enabled] = 1 and HirecalEnabled=1 and ParentCategory=@categoryId AND [Products_Count] > 0 ORDER BY [SortOrder]"; db.cmd.Parameters.AddWithValue("@categoryId", categoryId); db.cnOpen(); using (var read = db.cmd.ExecuteReader()) { while (read.Read()) { _sw.WriteLine("<li>"); _sw.WriteLine("<a href='{0}'>{1}</a>", _prefUrl + UrlService.GetLink(ParamType.Category, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["CategoryID"])), HttpUtility.HtmlEncode(read["Name"])); GetSubCategories((int)read["CategoryID"]); GetProducts((int)read["CategoryID"]); _sw.WriteLine("</li>"); } } } _sw.WriteLine("</ul>"); }
private void GetProducts(int categoryId) { _sw.WriteLine("<ul>"); using (var db = new SQLDataAccess()) { db.cmd.CommandText = "SELECT Product.[ProductID] as ProductID, Product.[Name] as Name,ProductCategories.CategoryID as ParentCategory,[UrlPath] FROM [Catalog].[ProductCategories]" + " INNER JOIN [Catalog].[Product] ON [Product].ProductID = ProductCategories.ProductID WHERE CategoryEnabled =1 and [Enabled] = 1 and ProductCategories.Main = 1 " + "and (select Count(CategoryID) from Catalog.ProductCategories where ProductID=[Product].[ProductID])<> 0 and CategoryID=@categoryId "; db.cmd.Parameters.AddWithValue("@categoryId", categoryId); db.cnOpen(); using (var read = db.cmd.ExecuteReader()) while (read.Read()) _sw.WriteLine("<li><a href='{0}'>{1}</a></li>", _prefUrl + UrlService.GetLink(ParamType.Product, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["ProductID"])), HttpUtility.HtmlEncode(read["Name"])); } _sw.WriteLine("</ul>"); }
private void CreateNews() { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "SELECT [NewsID], [Title], [AddingDate],[UrlPath] FROM [Settings].[News] ORDER BY AddingDate DESC"; db.cnOpen(); using (var read = db.cmd.ExecuteReader()) { bool tempHaveItem = read.HasRows; if (tempHaveItem) _sw.WriteLine("<b>" + Resource.Client_Sitemap_News + " </b> <ul>"); while (read.Read()) _sw.WriteLine("<li><a href='{0}'>{1}</a></li>", _prefUrl + UrlService.GetLink(ParamType.News, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["NewsID"])), read["AddingDate"] + " :: " + read["Title"]); if (tempHaveItem) _sw.WriteLine("</ul>"); } } }
public static int GetMaxSortOrder() { return (SQLDataHelper.GetInt(SQLDataAccess.ExecuteScalar("Select max(sortorder) from [CMS].[Carousel]", CommandType.Text)) + 10); }
public ArticleRepository(SQLDataAccess access) { Access = access; }
/// <summary> /// Check if database version is not empty /// </summary> /// <returns></returns> /// <remarks></remarks> public static bool CheckDBStructure() { bool boolResult = false; try { using (var db = new SQLDataAccess()) { db.cmd.CommandText = @"declare @res bit set @res = 0 IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Settings].[Settings]') AND type in (N'U')) AND EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Settings].[InternalSettings]') AND type in (N'U')) AND EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Settings].[sp_GetInternalSetting]') AND type in (N'P', N'PC')) BEGIN set @res = 1 END SELECT @res AS result"; db.cmd.CommandType = CommandType.Text; db.cmd.Parameters.Clear(); db.cnOpen(); boolResult = (bool)db.cmd.ExecuteScalar(); db.cnClose(); } } catch { boolResult = false; } return boolResult; }
public static PaymentMethod GetPaymentMethod(int paymentMethodId) { return(SQLDataAccess.ExecuteReadOne <PaymentMethod>( "SELECT * FROM [Order].[PaymentMethod] WHERE [PaymentMethodID] = @PaymentMethodID", CommandType.Text, reader => GetPaymentMethodFromReader(reader), new SqlParameter("@PaymentMethodID", paymentMethodId))); }
void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { if ((Page.User.IsInRole("ProjectAdministrator") || Page.User.IsInRole("ProjectManager"))) { UserList.DataSourceID = "ProjectMembers"; //if (Page.User.ToString() != null && Page.User.ToString() != "") //{ // UserList.SelectedValue = Page.User.ToString(); //} //UserList.SelectedValue= if (Page.User.IsInRole("ProjectAdministrator")) { ProjectData.SortParameterName = "sortParameter"; ProjectData.SelectMethod = "GetAllProjects"; divTimeSheet.Visible = false; AllProjectList.DataBind(); ListItem select = new ListItem("Select", "0"); if (AllProjectList.Items.Contains(select).ToString() != "True") { AllProjectList.Items.Insert(0, new ListItem("Select All", "0")); } UserList.DataBind(); ProjectList.Items.Insert(0, new ListItem("Select", "0")); admin.Attributes.Add("style","width:0px;"); admin.Visible = false; } else if (Page.User.IsInRole("ProjectManager")) { ProjectData.SelectParameters.Add(new Parameter("userName", TypeCode.String, Page.User.Identity.Name)); ProjectData.SelectMethod = "GetProjectsByUserName"; SQLDataAccess objSQLDataAccess = new SQLDataAccess(); List<Category> objCategorylist = objSQLDataAccess.GetCategoriesByUserName(Convert.ToString("")); CategoryList.DataSource = objCategorylist; CategoryList.DataTextField = "Name"; CategoryList.DataValueField = "Id"; //CategoryList.DA =Convert.ToString(objCategorylist[0].Name); //CategoryList.SelectedValue = Convert.ToString(objCategorylist[0].Id); CategoryList.DataBind(); AllProjectList.DataBind(); ListItem select = new ListItem("Select", "0"); if (AllProjectList.Items.Contains(select).ToString() != "True") { AllProjectList.Items.Insert(0, new ListItem("Select All", "0")); } UserList.DataBind(); UserList.Items.Insert(0, new ListItem("Select", "0")); //ProjectList.Items.Insert(0, new ListItem("Select", "0")); } } else if (Page.User.IsInRole("TeamLeader")) { ProjectData.SelectParameters.Add(new Parameter("userName", TypeCode.String, Page.User.Identity.Name)); ProjectData.SelectMethod = "GetProjectsByUserName"; UserList.Items.Add(Page.User.Identity.Name); SQLDataAccess objSQLDataAccess = new SQLDataAccess(); List<Category> objCategorylist = objSQLDataAccess.GetCategoriesByUserName(Convert.ToString(Page.User.Identity.Name)); CategoryList.DataSource = objCategorylist; CategoryList.DataTextField = "Name"; CategoryList.DataValueField = "Id"; //CategoryList.DA =Convert.ToString(objCategorylist[0].Name); //CategoryList.SelectedValue = Convert.ToString(objCategorylist[0].Id); CategoryList.DataBind(); AllProjectList.DataBind(); ListItem select = new ListItem("Select", "0"); if (AllProjectList.Items.Contains(select).ToString() != "True") { AllProjectList.Items.Insert(0, new ListItem("Select All", "0")); } // ProjectList.Items.Insert(0, new ListItem("Select", "0")); / } else { ProjectData.SelectParameters.Add(new Parameter("userName", TypeCode.String, Page.User.Identity.Name)); ProjectData.SelectMethod = "GetProjectsByUserName"; CustomFacade objCustomFacade = new CustomFacade(); List<Project> listProject = new List<Project>(); //listProject = objCustomFacade.GetProjectsByUserName(Page.User.Identity.Name); UserList.Items.Add(Page.User.Identity.Name); //UserList.Items.Insert(0, new ListItem("Select", "0")); SQLDataAccess objSQLDataAccess = new SQLDataAccess(); List<Category> objCategorylist = objSQLDataAccess.GetCategoriesByUserName(Convert.ToString(Page.User.Identity.Name)); CategoryList.DataSource = objCategorylist; CategoryList.DataTextField = "Name"; CategoryList.DataValueField = "Id"; //CategoryList.DA =Convert.ToString(objCategorylist[0].Name); //CategoryList.SelectedValue = Convert.ToString(objCategorylist[0].Id); CategoryList.DataBind(); AllProjectList.DataBind(); ListItem select = new ListItem("Select", "0"); if (AllProjectList.Items.Contains(select).ToString() != "True") { AllProjectList.Items.Insert(0, new ListItem("Select All", "0")); } //ProjectList.Items.Insert(0, new ListItem("Select", "0")); } username.Text = "Welcome: " + Session["username"]; ProjectList.DataBind(); //ProjectList.Items.Insert(0, new ListItem("Select", "0")); //if (ProjectList.SelectedValue != "0" && ProjectList.SelectedValue != "") //{ // if (Session["username"] != null && Session["username"] != "") // { // UserList.SelectedItem.Text = Convert.ToString(Session["username"]); // } //} //if (ProjectListGridView.Rows.Count==0) //{ // lblexport.Visible =false; // lnkexport.Visible = false; //} if (ProjectList.Items.Count >= 1) { TimeEntryView.Visible = true; MessageView.Visible = false; } else { TimeEntryView.Visible = true; MessageView.Visible = false; } StartDate.Text = DateTime.Now.Date.AddDays(-7).ToShortDateString(); EndDate.Text = DateTime.Now.ToShortDateString(); ProjectListGridView.DataBind(); } if (UserList.Items.Count == 0) { AddEntry.Enabled = false; } }
public static void UpdateShift(ShiftModel shift, int id) { SQLDataAccess.UpdateShift(shift, id); }
public static Dictionary<DateTime, float> GetOrdersSumByDays(DateTime minDate, DateTime maxDate) { var sums = new Dictionary<DateTime, float>(); using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Order].[sp_GetSumByDays]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@MinDate", minDate); db.cmd.Parameters.AddWithValue("@MaxDate", maxDate); db.cnOpen(); using (SqlDataReader reader = db.cmd.ExecuteReader()) while (reader.Read()) { sums.Add(SQLDataHelper.GetDateTime(reader, "Date"), SQLDataHelper.GetFloat(reader, "Sum")); } db.cnClose(); return sums; } }
public static List <TaxElement> GetTaxes() { return(SQLDataAccess.ExecuteReadList("SELECT * FROM [Catalog].[Tax]", CommandType.Text, ReadTax)); }
public static void GetProfitPlan() { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Settings].[sp_GetLastProfitPlan]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cnOpen(); using (SqlDataReader reader = db.cmd.ExecuteReader()) { reader.Read(); SalesPlan = SQLDataHelper.GetFloat(reader, "SalesPlan"); ProfitPlan = SQLDataHelper.GetFloat(reader, "ProfitPlan"); } db.cnClose(); } }
public static IEnumerable <RedirectSeo> GetRedirectsSeo() { return(SQLDataAccess.ExecuteReadIEnumerable <RedirectSeo>("SELECT * FROM [Settings].[Redirect]", CommandType.Text, GetRedirectSeoFromReader)); }
public static List<KeyValuePair<string, int>> GetShippingMethodRating() { var result = new List<KeyValuePair<string, int>>(); using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Order].[sp_GetShippingRating]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cnOpen(); using (SqlDataReader reader = db.cmd.ExecuteReader()) while (reader.Read()) { result.Add( new KeyValuePair<string,int>( SQLDataHelper.GetString(reader, "ShippingMethod"), SQLDataHelper.GetInt(reader, "Rating"))); } db.cnClose(); } return result; }
private void CreateCategory() { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "SELECT [CategoryID], [Name], [ParentCategory],[UrlPath] FROM [Catalog].[Category] WHERE [Enabled] = 1 and HirecalEnabled=1 and ParentCategory=0 and CategoryID<>0 AND [Products_Count] > 0 ORDER BY [SortOrder]"; db.cnOpen(); using (var read = db.cmd.ExecuteReader()) { bool tempHaveItem = read.HasRows; if (tempHaveItem) _sw.WriteLine("<b>" + Resource.Client_Sitemap_Catalog + "</b><ul>"); while (read.Read()) { if (!read["CategoryID"].ToString().Trim().Equals("0")) { _sw.WriteLine("<li><a href='{0}'>{1}</a>", _prefUrl + UrlService.GetLink(ParamType.Category, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["CategoryID"])), read["Name"]); GetSubCategories((int)read["CategoryID"]); GetProducts((int)read["CategoryID"]); _sw.WriteLine("</li>"); } } if (tempHaveItem) _sw.WriteLine("</ul>"); } } }
public static void SetProfitPlan(float sales, float profit) { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Settings].[sp_SetPlan]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@SalesPlan", sales); db.cmd.Parameters.AddWithValue("@ProfitPlan", profit); db.cnOpen(); db.cmd.ExecuteNonQuery(); db.cnClose(); GetProfitPlan(); } }
public static List <CallbackCustomer> GetCallbackCustomers() { return(SQLDataAccess.ExecuteReadList <CallbackCustomer>("SELECT * FROM [Module].[" + _moduleName + "] ORDER BY [DateAdded] DESC", CommandType.Text, GetCallbackCustomerFromReader)); }
public static Dictionary<DateTime, int> GetOrdersCountByPeriod(DateTime minDate, DateTime maxDate) { var sums = new Dictionary<DateTime, int>(); using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Order].[sp_GetCountByMonths]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@MinDate", minDate); db.cmd.Parameters.AddWithValue("@MaxDate", maxDate); db.cnOpen(); using (SqlDataReader reader = db.cmd.ExecuteReader()) while (reader.Read()) { sums.Add( new DateTime(SQLDataHelper.GetInt(reader, "Year"), SQLDataHelper.GetInt(reader, "Month"), 1), SQLDataHelper.GetInt(reader, "Count")); } db.cnClose(); return sums; } }
public static void SetActive(int carouselID, bool active) { SQLDataAccess.ExecuteNonQuery("Update CMS.Carousel set Enabled=@Enabled where CarouselID=@CarouselID", CommandType.Text, new SqlParameter("@CarouselID", carouselID), new SqlParameter("@Enabled", active)); }
public static List <ProductModel> LoadProducts() { string sql = @"select Id, ProductName, SupplierName, URL, Username, Password from dbo.tblProduct;"; return(SQLDataAccess.LoadData <ProductModel>(sql)); }
public static int GetCarouselsCount() { return(SQLDataHelper.GetInt(SQLDataAccess.ExecuteScalar("Select Count(CarouselID) From CMS.Carousel Where Enabled=1", CommandType.Text))); }
public static void Delete(Guid customerId) { SQLDataAccess.ExecuteNonQuery("delete from [Order].OrderConfirmation where CustomerId=@CustomerId", CommandType.Text, new SqlParameter { ParameterName = "@CustomerId", Value = customerId }); }
public static int GetCountPaymentMethods() { return(SQLDataAccess.ExecuteScalar <int>("SELECT count(*) FROM [Order].[PaymentMethod] ", CommandType.Text)); }
public static void DeleteExpired() { SQLDataAccess.ExecuteNonQuery("delete from [Order].OrderConfirmation where DATEADD(month, 1, LastUpdate) > GetDate()", CommandType.Text); }
public static void DeletePaymentMethod(int paymentMethodId) { PhotoService.DeletePhotos(paymentMethodId, PhotoType.Payment); SQLDataAccess.ExecuteNonQuery("DELETE FROM [Order].[PaymentMethod] WHERE [PaymentMethodID] = @PaymentMethodID", CommandType.Text, new SqlParameter("@PaymentMethodID", paymentMethodId)); }
public static bool Validate(string DBName, string table, string email) { string sql = "SELECT * FROM " + table + " WHERE UserEmail ='" + email + "'"; return(SQLDataAccess.CheckUser(sql, DBName, email)); }
public static PaymentMethod GetPaymentMethodByName(string name) { return(SQLDataAccess.ExecuteReadOne <PaymentMethod>( "SELECT top(1) * FROM [Order].[PaymentMethod] WHERE [Name] = @Name", CommandType.Text, reader => GetPaymentMethodFromReader(reader), new SqlParameter("@Name", name))); }
public static bool Login(string DBName, string table, string email, string password) { string sql = "SELECT * FROM " + table + " WHERE UserEmail = '" + email + "' AND UserPassword = '******'"; return(SQLDataAccess.ValidateLogin(sql, DBName, email, password)); }
private static Dictionary <string, string> GetPaymentMethodParameters(int paymentMethodId) { return(SQLDataAccess.ExecuteReadDictionary <string, string>("SELECT Name, Value FROM [Order].[PaymentParam] WHERE [PaymentMethodID] = @PaymentMethodID", CommandType.Text, "Name", "Value", new SqlParameter("@PaymentMethodID", paymentMethodId))); }
public static IList<OrderItem> GetOrderItems(int orderId) { var result = new List<OrderItem>(); using (var da = new SQLDataAccess()) { da.cmd.CommandText = "[Order].[sp_GetOrderItems]"; da.cmd.CommandType = CommandType.StoredProcedure; da.cmd.Parameters.Clear(); da.cmd.Parameters.AddWithValue("@OrderID", orderId); da.cn.Open(); using (SqlDataReader reader = da.cmd.ExecuteReader()) while (reader.Read()) { result.Add(GetOrderItemFromReader(reader)); } da.cmd.CommandText = "[Order].[sp_GetSelectedOptionsByOrderItemId]"; da.cmd.CommandType = CommandType.StoredProcedure; foreach (OrderItem orditm in result) { da.cmd.Parameters.Clear(); da.cmd.Parameters.AddWithValue("@OrderItemId", orditm.OrderItemID); var evlist = new List<EvaluatedCustomOptions>(); using (var reader = da.cmd.ExecuteReader()) while (reader.Read()) { var ev = new EvaluatedCustomOptions { CustomOptionId = SQLDataHelper.GetInt(reader, "CustomOptionId"), CustomOptionTitle = SQLDataHelper.GetString(reader, "CustomOptionTitle"), OptionId = SQLDataHelper.GetInt(reader, "OptionId"), OptionPriceBc = SQLDataHelper.GetFloat(reader, "OptionPriceBC"), OptionPriceType = (OptionPriceType)SQLDataHelper.GetInt(reader, "OptionPriceType"), OptionTitle = SQLDataHelper.GetString(reader, "OptionTitle") }; evlist.Add(ev); } orditm.SelectedOptions = evlist; } da.cnClose(); } return result; }
public static void ClearOrderTaxes(int orderId) { SQLDataAccess.ExecuteNonQuery("delete from [Order].[OrderTax] where [OrderId] = @OrderId", CommandType.Text, new SqlParameter("@OrderId", orderId)); }
public static IList<OrderPriceDiscount> GetOrderPricesDiscounts() { var result = new List<OrderPriceDiscount>(); if (SettingsOrderConfirmation.EnableDiscountModule) { var str = CacheNames.GetOrderPriceDiscountCacheObjectName(); if (CacheManager.Contains(str)) { result = CacheManager.Get<List<OrderPriceDiscount>>(str); if (result != null) return result; } result = new List<OrderPriceDiscount>(); using (var da = new SQLDataAccess()) { da.cmd.CommandText = "SELECT PriceRange, PercentDiscount FROM [Order].OrderPriceDiscount ORDER BY PriceRange"; da.cmd.CommandType = CommandType.Text; da.cmd.Parameters.Clear(); da.cnOpen(); using (SqlDataReader reader = da.cmd.ExecuteReader()) while (reader.Read()) { var opd = new OrderPriceDiscount { PercentDiscount = SQLDataHelper.GetDouble(reader, "PercentDiscount"), PriceRange = SQLDataHelper.GetFloat(reader, "PriceRange") }; result.Add(opd); } da.cnClose(); CacheManager.Insert(str, result, 10); } } return result; }
public static IEnumerable <TaxElement> GetCertificateTaxes() { return(SQLDataAccess.ExecuteReadList <TaxElement>( "SELECT * FROM [Settings].[GiftCertificateTaxes] inner join Catalog.Tax on GiftCertificateTaxes.TaxID = Tax.TaxID", CommandType.Text, ReadTax)); }
public static void UpdateTax(TaxElement t) { using (var da = new SQLDataAccess()) { da.cmd.CommandText = @"UPDATE [Catalog].[Tax] SET [Name] = @name, [Enabled] = @enabled, [Priority] = @priority, [DependsOnAddress] = @dependsOnAddress, [ShowInPrice] = @showInPrice, [RegNumber] = @regNumber, [CountryID] = @countryId, [RateType] = @rateType, [FederalRate] = @federalRate WHERE [TaxId] = @TaxId"; da.cmd.CommandType = CommandType.Text; da.cmd.Parameters.Clear(); da.cmd.Parameters.AddWithValue("@TaxId", t.TaxId); da.cmd.Parameters.AddWithValue("@name", t.Name); da.cmd.Parameters.AddWithValue("@enabled", t.Enabled); da.cmd.Parameters.AddWithValue("@priority", t.Priority); da.cmd.Parameters.AddWithValue("@dependsOnAddress", (int)t.DependsOnAddress); da.cmd.Parameters.AddWithValue("@showInPrice", t.ShowInPrice); da.cmd.Parameters.AddWithValue("@regNumber", t.RegNumber ?? (object)DBNull.Value); da.cmd.Parameters.AddWithValue("@countryId", t.CountryID); da.cmd.Parameters.AddWithValue("@rateType", (int)t.Type); da.cmd.Parameters.AddWithValue("@federalRate", t.FederalRate); da.cnOpen(); da.cmd.ExecuteNonQuery(); t.RegionalRates.Any(); //DO NOT DELETE!!! предзагрузка региональных ставок da.cmd.CommandText = "DELETE FROM [Catalog].[TaxRegionRate] WHERE [TaxId] = @TaxId"; da.cmd.Parameters.Clear(); da.cmd.Parameters.AddWithValue("@TaxId", t.TaxId); da.cmd.ExecuteNonQuery(); da.cnClose(); if (t.RegionalRates.Count > 0) { da.cnOpen(); da.cmd.CommandText = "INSERT INTO [Catalog].[TaxRegionRate]([TaxId], [RegionID], [RegionRate]) VALUES (@TaxId, @regionId, @regionRate)"; foreach (var rr in t.RegionalRates) { da.cmd.Parameters.Clear(); da.cmd.Parameters.AddWithValue("@TaxId", t.TaxId); da.cmd.Parameters.AddWithValue("@regionId", rr.RegionId); da.cmd.Parameters.AddWithValue("@regionRate", rr.Rate); da.cmd.ExecuteNonQuery(); } da.cnClose(); } } }
public static TaxElement GetTax(int id) { return(SQLDataAccess.ExecuteReadOne("SELECT * FROM [Catalog].[Tax] WHERE [TaxId] = @id", CommandType.Text, ReadTax, new SqlParameter("@id", id))); }
public static void PayOrder(int orderId, bool pay, bool setLastPrice = true) { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "UPDATE [Order].[Order] SET [PaymentDate] = @PaymentDate WHERE [OrderID] = @OrderID"; db.cmd.CommandType = CommandType.Text; db.cmd.Parameters.Clear(); if (pay) db.cmd.Parameters.AddWithValue("@PaymentDate", DateTime.Now); else db.cmd.Parameters.AddWithValue("@PaymentDate", DBNull.Value); db.cmd.Parameters.AddWithValue("@OrderID", orderId); db.cnOpen(); db.cmd.ExecuteNonQuery(); db.cnClose(); foreach (var certificate in GiftCertificateService.GetOrderCertificates(orderId)) { GiftCertificateService.SendCertificateMails(certificate); } } if (setLastPrice) { var order = GetOrder(orderId); if (order != null) DoOrderPayed(order); } }
public static List <MovieModel> RetrieveData() { string sql = @"select * from dbo.Movies;"; return(SQLDataAccess.LoadData <MovieModel>(sql).ToList()); }
protected void Page_Load(object sender, EventArgs e) { if (Utils.ValidateRedirect().Length > 0) //Important check { Response.Redirect(Utils.ValidateRedirect(), true); } if (!IsPostBack) { #region NERDO_Related //arrlstNerdoPubIds = DAL.DAL.GetNerdoPubIds(); kvpaircoll = SQLDataAccess.GetKVPair("sp_NCIPL_getNerdoPubIdsURLS"); #endregion #region dummydata--remove before going live //Session["NCIPL_Pubs"] = "31,31"; //Session["NCIPL_Qtys"] = "123,1"; #endregion steps1.Activate("cell1"); this.shoppingcart = null; //destroy cart if it exists this.shoppingcart = new ProductCollection(); //***EAC Parse the NCIPL_Pubs and NCIPL_qtys..assume they have same dimensions string[] pubs = Session["NCIPLEX_Pubs"].ToString().Split(new Char[] { ',' }); string[] qtys = Session["NCIPLEX_Qtys"].ToString().Split(new Char[] { ',' }); for (var i = 0; i < pubs.Length; i++) { if (pubs[i].Trim() != "") { int pubid = Int32.Parse(pubs[i]); Product p = Product.GetPubByPubID(pubid); p.NumQtyOrdered = Int32.Parse(qtys[i]); this.shoppingcart.Add(p); //BLL will know what to do w/ dupes and 0 quantities } } grdViewItems.DataSource = this.shoppingcart; grdViewItems.DataBind(); } //Check for cart object here if (this.shoppingcart == null) { Response.Redirect("default.aspx?redirect=cart1", true); } if (this.shoppingcart.Count > 0) { //***EAC at this point we have a usable cart lblTot.Text = this.shoppingcart.TotalQty.ToString(); //lblCost.Text = this.shoppingcart.Cost.ToString("c"); /*Begin HITT 8716*/ if (this.shoppingcart.Cost > 0.0) { CostRecoveryInd = "1"; } else { CostRecoveryInd = ""; } this.ToggleCostDivs(CostRecoveryInd); /*End HITT 8716*/ Panel1.Visible = true; Panel2.Visible = false; } else//shopping cart is empty { Panel1.Visible = false; Panel2.Visible = true; } //Set the appropriate tab int intTotalQty = 0; if (Session["NCIPLEX_Qtys"] != null) { string[] qtys = Session["NCIPLEX_Qtys"].ToString().Split(new Char[] { ',' }); for (int i = 0; i < qtys.Length; i++) { if (qtys[i].Length > 0) { intTotalQty += Int32.Parse(qtys[i].ToString()); } } } //Display the master page tabs GlobalUtils.Utils UtilMethod = new GlobalUtils.Utils(); if (Session["NCIPLEX_Pubs"] != null) { Master.LiteralText = UtilMethod.GetTabHtmlMarkUp(Session["NCIPLEX_Qtys"].ToString(), "cart"); } else { Master.LiteralText = UtilMethod.GetTabHtmlMarkUp("", "cart"); } UtilMethod = null; }
/// <summary> /// create html for tooltip /// </summary> /// <param name="productId"></param> /// <returns></returns> public static string CreateTooltipContent(int productId) { var res = new StringBuilder(); var content = new StringBuilder(); int categoryCounter = 0; try { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Catalog].[sp_GetCategoriesPathesByProductID]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@ProductID", productId); db.cnOpen(); using (SqlDataReader reader = db.cmd.ExecuteReader()) while (reader.Read()) { content.Append("<br/> " + SQLDataHelper.GetString(reader, "CategoryPath")); categoryCounter++; } db.cnClose(); } } catch (Exception ex) { Debug.LogError(ex); } if (categoryCounter > 0) { var strHead = new StringBuilder(); strHead.Append("<div class=\'tooltipDiv\'><span class=\'tooltipBold\'>"); strHead.Append(string.Format(Resource.Admin_CategoriesService_ProductInCategories, categoryCounter)); strHead.Append("<br/><div style=\'height:5px;width:0px;\' />"); strHead.Append(Resource.Admin_CategoriesService_Categories); res.Append(strHead); res.Append(content); res.Append("</span></div>"); } else { res.Append(""); } return res.ToString(); }
public static List <EmployeeModel> LoadEmployees() { string sql = @"select Id, EmployeeId, FirstName, LastName, EmailAddress from dbo.Employees;"; return(SQLDataAccess.LoadData <EmployeeModel>(sql)); }
public static PaymentDetails GetPaymentDetails(int orderId) { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "[Order].[sp_GetPaymentDetails]"; db.cmd.CommandType = CommandType.StoredProcedure; db.cmd.Parameters.Clear(); db.cmd.Parameters.AddWithValue("@OrderID", orderId); db.cnOpen(); using (SqlDataReader reader = db.cmd.ExecuteReader()) if (reader.Read()) { return new PaymentDetails { CompanyName = SQLDataHelper.GetString(reader, "CompanyName"), INN = SQLDataHelper.GetString(reader, "INN"), Phone = SQLDataHelper.GetString(reader, "Phone") }; } db.cnClose(); } return null; }
private void CreateBrands() { using (var db = new SQLDataAccess()) { db.cmd.CommandText = "SELECT [BrandName], [BrandID], [UrlPath] FROM [Catalog].[Brand] Where enabled=1 ORDER BY BrandName"; db.cnOpen(); using (var read = db.cmd.ExecuteReader()) { bool tempHaveItem = read.HasRows; if (tempHaveItem) _sw.WriteLine("<b>" + Resource.Client_Sitemap_Brands + " </b> <ul>"); while (read.Read()) _sw.WriteLine("<li><a href='{0}'>{1}</a></li>", _prefUrl + UrlService.GetLink(ParamType.Brand, SQLDataHelper.GetString(read["UrlPath"]), SQLDataHelper.GetInt(read["BrandID"])), read["BrandName"]); if (tempHaveItem) _sw.WriteLine("</ul>"); } } }