// move product to a new category public static bool MoveProductToCategory(string productId, string oldCategoryId, string newCategoryId) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "CatalogMoveProductToCategory"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@ProductID"; param.Value = productId; param.DbType = DbType.Int32; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@OldCategoryID"; param.Value = oldCategoryId; param.DbType = DbType.Int32; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@NewCategoryID"; param.Value = newCategoryId; param.DbType = DbType.Int32; comm.Parameters.Add(param); // result will represent the number of changed rows int result = -1; try { // execute the stored procedure result = GenericDataAccess.ExecuteNonQuery(comm); } catch { // any errors are logged in GenericDataAccess, we ignore them here } // result will be 1 in case of success return(result != -1); }
// Update category details public static bool UpdateCategory(string id, string name, string description) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "CatalogUpdateCategory"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@CategoryId"; param.Value = id; param.DbType = DbType.Int32; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@CategoryName"; param.Value = name; param.DbType = DbType.String; param.Size = 50; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@CategoryDescription"; param.Value = description; param.DbType = DbType.String; param.Size = 1000; comm.Parameters.Add(param); // result will represent the number of changed rows int result = -1; try { // execute the stored procedure result = GenericDataAccess.ExecuteNonQuery(comm); } catch { // any errors are logged in GenericDataAccess, we ignore them here } // result will be 1 in case of success return(result != -1); }
public static int DeleteWebVisitor(int WebVisitorId) { GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "DeleteWebVisitor"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@WebVisitorId"; param.Value = WebVisitorId; param.DbType = DbType.Int32; comm.Parameters.Add(param); // return the result table return(gda.ExecuteNonQuery(comm)); }
public void fillLstTarifaByServicio(int id_cliente, int id_servicio) { try { this.comm = GenericDataAccess.CreateCommand( "select ms.id, cm.sku, cm.nombre, ms.precio " + "from cliente_mercancia cm " + "join mercancia_servicio ms on " + " cm.id = ms.id_cliente_mercancia " + " and cm.id_cliente = ?id_cliente " + " and ms.id_servicio = ?id_servicio "); GenericDataAccess.AddInParameter(this.comm, "?id_cliente", DbType.Int32, id_cliente); GenericDataAccess.AddInParameter(this.comm, "?id_servicio", DbType.Int32, id_servicio); this.dt = GenericDataAccess.ExecuteSelectCommand(this.comm); var qry = from result in this.dt.AsEnumerable() select new { id = result.Field <Int32>("id"), sku = result.Field <string>("sku"), nombre = result.Field <string>("nombre"), precio = result.Field <decimal>("precio") }; foreach (var item in qry) { Cliente_mercancia o = new Cliente_mercancia() { Id = item.id, Sku = item.sku, Nombre = item.nombre, Tarifa = item.precio }; this._lst.Add(o); } } catch { throw; } }
// Retrieve the list of products on catalog promotion public static DataTable GetProductsOnFrontPromo(string pageNumber, out int howManyPages) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "CatalogGetProductsOnFrontPromo"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@DescriptionLength"; param.Value = BalloonShopConfiguration.ProductDescriptionLength; param.DbType = DbType.Int32; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@PageNumber"; param.Value = pageNumber; param.DbType = DbType.Int32; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@ProductsPerPage"; param.Value = BalloonShopConfiguration.ProductsPerPage; param.DbType = DbType.Int32; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@HowManyProducts"; param.Direction = ParameterDirection.Output; param.DbType = DbType.Int32; comm.Parameters.Add(param); // execute the stored procedure and save the results in a DataTable DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); // calculate how many pages of products and set the out parameter int howManyProducts = Int32.Parse(comm.Parameters ["@HowManyProducts"].Value.ToString()); howManyPages = (int)Math.Ceiling((double)howManyProducts / (double)BalloonShopConfiguration.ProductsPerPage); // return the page of products return(table); }
// Retrieve the order details (the products that are part of that order) public static DataTable GetDetails(string orderID) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "OrderGetDetails"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@OrderId"; param.Value = orderID; param.DbType = DbType.Int32; comm.Parameters.Add(param); // return the results DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); return(table); }
public static List <CommerceLibOrderInfo> GetOrdersByStatus( int status) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "CommerceLibOrdersGetByStatus"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@Status"; param.Value = status; param.DbType = DbType.Int32; comm.Parameters.Add(param); // obtain the results return(ConvertDataTableToOrders( GenericDataAccess.ExecuteSelectCommand(comm))); }
//This method deletes an author using the "DeleteAuthor" stored procedure. The names of the method parameters should match the names of the fields in the corresponding data control (e.g. Gridview, Detailsview, etc.) //The method returns an int representing the number of rows affected. This can be used to verify a successful operation, or it can be discarded public static int DeleteAuthor(string au_id) { //************************** ADD CODE FOR DELETING AN AUTHOR HERE **************************** DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "DeleteAuthor"; //This is the name of the stored procedure to be executed DbParameter param = comm.CreateParameter(); param = comm.CreateParameter(); param.ParameterName = "@au_id"; param.DbType = DbType.String; param.Value = au_id; comm.Parameters.Add(param); int rowsAffected = GenericDataAccess.ExecuteNonQuery(comm); return(rowsAffected); }
public static bool InsertDeviceList(string DeviceName, string DeviceModel, string ImagePath) { // get a configured DbCommand object GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "InsertDeviceList"; // create a new parameter DbParameter param1 = comm.CreateParameter(); param1.ParameterName = "@DeviceName"; param1.Value = DeviceName; param1.DbType = DbType.String; comm.Parameters.Add(param1); DbParameter param2 = comm.CreateParameter(); param2.ParameterName = "@DeviceModel"; param2.Value = DeviceModel; param2.DbType = DbType.String; comm.Parameters.Add(param2); DbParameter param3 = comm.CreateParameter(); param3.ParameterName = "@pic"; param3.Value = ImagePath; param3.DbType = DbType.String; comm.Parameters.Add(param3); // return the result table if (gda.ExecuteNonQuery(comm) > 0) { return(true); } else { return(false); } }
// Retrieve the recent orders public static DataTable GetByRecent(int count) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "OrdersGetByRecent"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@Count"; param.Value = count; param.DbType = DbType.Int32; comm.Parameters.Add(param); // return the result table DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); return(table); }
public static DataTable GetPagingInfo(int PageIndex, int PageSize, string OrderBy, out int TotalCount) { // get a configured DbCommand object GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "GetPagingInfo"; DbParameter param2 = comm.CreateParameter(); param2.ParameterName = "@PageIndex"; param2.Value = PageIndex; param2.DbType = DbType.Int32; comm.Parameters.Add(param2); DbParameter param3 = comm.CreateParameter(); param3.ParameterName = "@PageSize"; param3.Value = PageSize; param3.DbType = DbType.Int32; comm.Parameters.Add(param3); DbParameter param1 = comm.CreateParameter(); param1.ParameterName = "@OrderBy"; param1.Value = OrderBy; param1.DbType = DbType.String; comm.Parameters.Add(param1); DbParameter param4 = comm.CreateParameter(); param4.ParameterName = "@TotalCount"; param4.Direction = ParameterDirection.Output; param4.DbType = DbType.Int32; comm.Parameters.Add(param4); DataTable table = gda.ExecuteSelectCommand(comm); TotalCount = Convert.ToInt32(comm.Parameters["@TotalCount"].Value); return(table); }
public static DataTable GetTiming(string locationid) { // get a configured DbCommand object GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "GetWorkingHoursById"; DbParameter param2 = comm.CreateParameter(); param2.ParameterName = "@LocationId"; param2.Value = locationid; param2.DbType = DbType.Int32; comm.Parameters.Add(param2); // return the result table return(gda.ExecuteSelectCommand(comm)); }
// retrieve the list of products in a category public static DataTable GetAllProductsInCategory(string categoryId) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "CatalogGetAllProductsInCategory"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@CategoryID"; param.Value = categoryId; param.DbType = DbType.Int32; comm.Parameters.Add(param); // execute the stored procedure and save the results in a DataTable DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); return(table); }
public static DataTable GetCMSInfo(int PageIndex, int PageSize, string OrderBy, out int TotalCount) { GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); comm.CommandText = "GetLocationList"; DbParameter param1 = comm.CreateParameter(); param1.ParameterName = "@PageIndex"; param1.Value = PageIndex; param1.DbType = DbType.Int32; comm.Parameters.Add(param1); DbParameter param2 = comm.CreateParameter(); param2.ParameterName = "@PageSize"; param2.Value = PageSize; param2.DbType = DbType.Int32; comm.Parameters.Add(param2); DbParameter param3 = comm.CreateParameter(); param3.ParameterName = "@OrderBy"; param3.Value = OrderBy; param3.DbType = DbType.String; comm.Parameters.Add(param3); DbParameter param7 = comm.CreateParameter(); param7.ParameterName = "@TotalCount"; param7.Direction = ParameterDirection.Output; param7.DbType = DbType.Int32; comm.Parameters.Add(param7); // return the result table DataTable table = gda.ExecuteSelectCommand(comm); TotalCount = Convert.ToInt32(comm.Parameters["@TotalCount"].Value); return(table); }
// Add a new shopping cart item public static bool AddItem(string productId) { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "ShoppingCartAddItem"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = shoppingCartId; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param); // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@ProductID"; param.Value = productId; param.DbType = DbType.Int32; comm.Parameters.Add(param); //changed for compatibility // create a new parameter param = comm.CreateParameter(); param.ParameterName = "@Attributes"; param.Value = "attributes"; param.DbType = DbType.String; comm.Parameters.Add(param); // returns true in case of success or false in case of an error try { // execute the stored procedure and return true if it executes // successfully, or false otherwise return(GenericDataAccess.ExecuteNonQuery(comm) != -1); } catch { // prevent the exception from propagating, but return false to // signal the error return(false); } }
//取区间消费根据RegionID用于删除消费时删除区间// public static DataTable GetItemListByRegionId(int userId, int regionId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetItemListByRegionId_v4"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@UserID"; param.Value = userId; param.DbType = DbType.Int32; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@RegionID"; param.Value = regionId; param.DbType = DbType.Int32; comm.Parameters.Add(param); return(GenericDataAccess.ExecuteCommand(comm)); }
//This method deletes an author using the "DeleteAuthor" stored procedure. The names of the method parameters should match the names of the fields in the corresponding data control (e.g. Gridview, Detailsview, etc.) //The method returns an int representing the number of rows affected. This can be used to verify a successful operation, or it can be discarded public static int DeleteAuthor(string au_id) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "DeleteAuthor"; //This is the name of the stored procedure to be executed //The following code adds the neccessary parameters to the DbCommand object DbParameter param = comm.CreateParameter(); param.ParameterName = "@au_id"; param.DbType = DbType.String; param.Value = au_id; comm.Parameters.Add(param); int rowsAffected = GenericDataAccess.ExecuteNonQuery(comm); return(rowsAffected); }
public bool IsSRDSRDueExist(string Id) { bool ret = false; DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandType = CommandType.Text; comm.CommandText = @"Select count(SrId) From IM_SR_DSR_ORDER_DUE WHERE SrId=@SrId"; CreateParameter.AddParam(comm, "@SrId", Id, DbType.String); string strRet = GenericDataAccess.ExecuteScalar(comm); if (Convert.ToInt16(strRet) > 0) { ret = true; } if (comm.Connection.State != ConnectionState.Closed) { comm.Connection.Close(); } return(ret); }
//取消费列表通过当前用户和日期//ItemList.aspx public static DataTable GetItemList(string date, int userId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetItemList_v4"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@ItemBuyDate"; param.Value = DateTime.Parse(date); param.DbType = DbType.DateTime; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@UserID"; param.Value = userId; param.DbType = DbType.Int32; comm.Parameters.Add(param); return(GenericDataAccess.ExecuteCommand(comm)); }
//取专题列表明细// public static DataTable GetZhuanTiListDetail(int zhuanTiId, int userId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "GetZhuanTiListDetail_v4"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@ZhuanTiID"; param.Value = zhuanTiId; param.DbType = DbType.Int32; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@UserID"; param.Value = userId; param.DbType = DbType.Int32; comm.Parameters.Add(param); return(GenericDataAccess.ExecuteCommand(comm)); }
public static bool IsUserIdExist(string userId) { bool ret = false; DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandType = CommandType.Text; comm.CommandText = @"Select count(USER_ID) From IM_USERS WHERE USER_ID=@USER_ID"; CreateParameter.AddParam(comm, "@USER_ID", userId, DbType.String); string strRet = GenericDataAccess.ExecuteScalar(comm); if (Convert.ToInt16(strRet) > 0) { ret = true; } if (comm.Connection.State != ConnectionState.Closed) { comm.Connection.Close(); } return(ret); }
//根据ItemAppID取消费列表,返回DataTable// public static DataTable SyncCheckItemListByItemAppId(int itemAppId, int userId) { DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandText = "SyncCheckItemListByItemAppId_v4"; DbParameter param = comm.CreateParameter(); param.ParameterName = "@ItemAppID"; param.Value = itemAppId; param.DbType = DbType.Int32; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@UserID"; param.Value = userId; param.DbType = DbType.Int32; comm.Parameters.Add(param); return(GenericDataAccess.ExecuteCommand(comm)); }
public static DataTable GetRecordsForUser(int CustomerId) { // get a configured DbCommand object GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "GetCustomerListByCustomerId"; // create a new parameter DbParameter param2 = comm.CreateParameter(); param2.ParameterName = "@CustomerId"; param2.Value = CustomerId; param2.DbType = DbType.Int32; comm.Parameters.Add(param2); //return datatable return(gda.ExecuteSelectCommand(comm)); }
// Retrieve shopping cart items public static DataTable GetItems() { // get a configured DbCommand object DbCommand comm = GenericDataAccess.CreateCommand(); // set the stored procedure name comm.CommandText = "ShoppingCartGetItems"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@CartID"; param.Value = shoppingCartId; param.DbType = DbType.String; param.Size = 36; comm.Parameters.Add(param); // return the result table DataTable table = GenericDataAccess.ExecuteSelectCommand(comm); return(table); }
public static DataTable GetMailListByMailId(int MailId) { // get a configured DbCommand object GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "GetMailListByMailId"; DbParameter param1 = comm.CreateParameter(); param1.ParameterName = "@MailId"; param1.Value = MailId; param1.DbType = DbType.Int32; comm.Parameters.Add(param1); // return the result table return(gda.ExecuteSelectCommand(comm)); }
public bool IsCompanyExist(string companyName) { bool ret = false; DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandType = CommandType.Text; comm.CommandText = @"Select count(CompanyId) From IM_Company WHERE CompanyName=@CompanyName"; CreateParameter.AddParam(comm, "@CompanyName", companyName, DbType.String); string strRet = GenericDataAccess.ExecuteScalar(comm); if (Convert.ToInt16(strRet) > 0) { ret = true; } if (comm.Connection.State != ConnectionState.Closed) { comm.Connection.Close(); } return(ret); }
public bool IsPaymentInfoExist(string paymentId) { bool ret = false; DbCommand comm = GenericDataAccess.CreateCommand(); comm.CommandType = CommandType.Text; comm.CommandText = @"Select count(PaymentId) From IM_SR_DSR_PAYMENT_DETAILS WHERE PaymentId=@PaymentId"; CreateParameter.AddParam(comm, "@PaymentId", paymentId, DbType.String); string strRet = GenericDataAccess.ExecuteScalar(comm); if (Convert.ToInt16(strRet) > 0) { ret = true; } if (comm.Connection.State != ConnectionState.Closed) { comm.Connection.Close(); } return(ret); }
public static bool ChangePassword(int UserID, string Old, string New) { // get a configured DbCommand object GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "ChangePassword"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@UserID"; param.Value = UserID; param.DbType = DbType.Int32; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@OldPassword"; param.Value = Old; param.DbType = DbType.String; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@NewPassword"; param.Value = New; param.DbType = DbType.String; comm.Parameters.Add(param); int retval = gda.ExecuteNonQuery(comm); if (retval > 0) { return(true); } else { return(false); } }
public static DataTable Login_Verify(string Email, string Password, string OperationType, int UserType) { // get a configured DbCommand object GenericDataAccess gda = new GenericDataAccess(); DbCommand comm = gda.CreateCommand(); // set the stored procedure name comm.CommandText = "cw_UserDetails"; // create a new parameter DbParameter param = comm.CreateParameter(); param.ParameterName = "@Email"; param.Value = Email; param.DbType = DbType.String; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@Password"; param.Value = Password; param.DbType = DbType.String; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@OperationType"; param.Value = OperationType; param.DbType = DbType.String; comm.Parameters.Add(param); param = comm.CreateParameter(); param.ParameterName = "@UserTypeId"; param.Value = UserType; param.DbType = DbType.Int32; comm.Parameters.Add(param); // return the result table DataTable table = gda.ExecuteSelectCommand(comm); return(table); }
public void fillLstTarifaByClienteMercancia(int id_cliente, int total = 0) { try { this.comm = GenericDataAccess.CreateCommand( "select s.id, s.nombre, count(cm.id) cantidad " + "from servicio s " + "left join mercancia_servicio ms on " + " ms.id_servicio = s.id " + "left join cliente_mercancia cm on " + " cm.id = ms.id_cliente_mercancia " + " and cm.id_cliente = ?id_cliente " + "group by s.id"); GenericDataAccess.AddInParameter(this.comm, "?id_cliente", DbType.Int32, id_cliente); this.dt = GenericDataAccess.ExecuteSelectCommand(this.comm); var qry = from result in this.dt.AsEnumerable() select new { id = result.Field <Int32>("id"), servicio = result.Field <string>("nombre"), cantidad = result.Field <Int64>("cantidad") }; foreach (var item in qry) { Servicio o = new Servicio() { Id = item.id, Nombre = item.servicio, Tarifas = item.cantidad, Total_mercancia = total }; this._lst.Add(o); } } catch { throw; } }