public int AddNewLang(IdentityMenuLang identity) { //Common syntax var sqlCmd = @"Menu_AddNewLang"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@MenuId", identity.MenuId }, { "@Title", identity.Title }, { "@LangCode", identity.LangCode } }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute Menu_AddNewLang. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public int UpdateLang(IdentityGroupPropertyLang identity) { //Common syntax var sqlCmd = @"M_GroupProperty_UpdateLang"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@Id", identity.Id }, { "@GroupName", identity.GroupName }, { "@LangCode", identity.LangCode }, { "@Description", identity.Description } }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute M_GroupProperty_UpdateLang. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public bool CheckAccessDuplicate(IdentityAccess identity) { var existed = false; try { using (var conn = new SqlConnection(_connectionString)) { var parameters = new Dictionary <string, object> { { "@AccessId", identity.Id }, { "@AccessName", identity.AccessName } }; var query = @"SELECT 1 FROM aspnetaccess WHERE 1=1 AND AccessName = @AccessName AND Id != @AccessId"; var result = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, query, parameters); if (Convert.ToBoolean(result)) { existed = true; } } } catch (Exception ex) { existed = false; } return(existed); }
public int Insert(IdentityDevice identity) { //Common syntax var sqlCmd = @"Device_Insert"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@Name", identity.Name }, { "@Code", identity.Code } }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute Device_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public int Insert(IdentityProjectCategory identity) { var sqlCmd = @"ProjectCategory_Insert"; var newId = 0; var paramaters = new Dictionary <string, object> { { "Code", identity.Code }, { "Name", identity.Name }, { "Status", identity.Status }, }; try { using (var connn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(connn, CommandType.StoredProcedure, sqlCmd, paramaters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute ProjectCategory_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public int Update(IdentityPost identity) { var sqlCmd = @"M_Post_Update"; var parameters = new Dictionary <string, object> { { "@Id", identity.Id }, { "@Title", identity.Title }, { "@Description", identity.Description }, { "@BodyContent", identity.BodyContent }, { "@IsHighlights", identity.IsHighlights }, { "@Cover", identity.Cover }, { "@UrlFriendly", identity.UrlFriendly }, { "@PostType", identity.PostType }, { "@Status", identity.Status } }; try { using (var conn = new SqlConnection(_connectionString)) { var result = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); return(Utils.ConvertToInt32(result)); } } catch (Exception ex) { var strError = "Failed to M_Post_Update. Error: " + ex.Message; throw new CustomSQLException(strError); } }
public int Insert(IdentityHTDefaultSetting identity) { //Common syntax var sqlCmd = @"HTDefault_Setting_Insert"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@Name", identity.Name }, { "@Code", identity.Code }, { "@EnumValue", identity.EnumValue }, { "@MaxLength", identity.MaxLength }, { "@StartPosition", identity.StartPosition }, { "@NumberOfCharacters", identity.NumberOfCharacters }, { "@Status", identity.Status } }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute HTDefault_Setting_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public void Delete(TUser user, string roleName) { using (var conn = new SqlConnection(_connectionString)) { var parameters = new Dictionary <string, object> { { "@RoleName", roleName } }; var idObject = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, @"SELECT Id FROM AspNetRoles WHERE Name=@RoleName", parameters); string roleId = idObject == null ? null : idObject.ToString(); if (!string.IsNullOrEmpty(roleId)) { using (var conn1 = new SqlConnection(_connectionString)) { var parameters1 = new Dictionary <string, object> { { "@Id", user.Id }, { "@RoleId", roleId } }; MsSqlHelper.ExecuteNonQuery(conn1, @"Delete FROM AspNetUserRoles WHERE UserId=@Id AND RoleId=@RoleId", parameters1); } } } }
/// <summary> /// 检查仓库情况 /// </summary> /// <param name="BARCODE"></param> /// <returns></returns> public int ChecksStorage(string BARCODE) { try { string checkSql = string.Format(@" SELECT COUNT(T.BARCODE) FROM (SELECT A.BARCODE FROM STC0006 (NOLOCK) AS A WHERE A.BARCODE ='{0}' UNION ALL SELECT B.BARCODE FROM STC0009 (NOLOCK) AS B WHERE B.BARCODE ='{0}') AS T ", BARCODE); return((int)db.ExecuteScalar(checkSql)); } catch { return(0); } }
public long CountQuery(string strQuery) { using (var conn = new SqlConnection(_connectionString)) { string sqlCount = string.Format("SELECT COUNT(1) FROM ({0}) a", strQuery); var result = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, sqlCount, null); if (result != null) { return(Convert.ToInt64(result.ToString())); } } return(0); }
private string GetRoleName(string roleId) { using (var conn = new SqlConnection(_connectionString)) { var parameters = new Dictionary <string, object> { { "@id", roleId } }; var result = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, @"SELECT Name FROM AspNetRoles WHERE Id = @id", parameters); if (result != null) { return(result.ToString()); } } return(null); }
public int Insert(IdentityShop identity) { //Common syntax var sqlCmd = @"M_Shop_Insert"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@IsInternal", identity.IsInternal }, { "@Name", identity.Name }, { "@Code", identity.Code }, { "@ProviderId", identity.ProviderId }, { "@Phone", identity.Phone }, { "@Address", identity.Address }, { "@ContactInfo", identity.ContactInfo }, { "@AreaId", identity.AreaId }, { "@CountryId", identity.CountryId }, { "@ProvinceId", identity.ProvinceId }, { "@DistrictId", identity.DistrictId }, { "@Openned", identity.Openned }, { "@PostCode", identity.PostCode }, { "@Description", identity.Description }, { "@CreatedBy", identity.CreatedBy }, { "@Status", identity.Status }, { "@ShopType", identity.ShopType }, { "@Email", identity.Email } }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute M_Shop_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
/// <summary> /// 检测数据库连接串是否有效 /// </summary> /// <param name="constr"></param> /// <returns></returns> public override bool IsConn(string constr) { try { string sql = "select getdate()"; object obj = MsSqlHelper.ExecuteScalar(constr, CommandType.Text, sql, null); if (obj != null) { return(true); } } catch (SqlException ex) { } return(false); }
public int Insert(IdentityProduct identity) { //Common syntax var sqlCmd = @"Product_Insert"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@Code", identity.Code }, { "@ProductCategoryId", identity.ProductCategoryId }, { "@ProviderId", identity.ProviderId }, { "@Name", identity.Name }, //{"@ShortDescription", identity.ShortDescription }, //{"@Detail", identity.Detail }, //{"@OtherInfo", identity.OtherInfo }, //{"@Cost", identity.Cost }, //{"@SaleOffCost", identity.SaleOffCost }, //{"@CurrencyId", identity.CurrencyId }, { "@MinInventory", identity.MinInventory }, { "@UnitId", identity.UnitId }, { "@CreatedBy", identity.CreatedBy }, //{"@CreatedDate", identity.CreatedDate }, //{"@LastUpdatedBy", identity.LastUpdatedBy }, //{"@LastUpdated", identity.LastUpdated }, { "@Status", identity.Status }, }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute Product_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public string GetByUserLoginInfo(UserLoginInfo login) { string userId; using (var conn = new SqlConnection(_connectionString)) { var parameters = new Dictionary <string, object> { { "@LoginProvider", login.LoginProvider }, { "@ProviderKey", login.ProviderKey } }; var userIdObject = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, @"SELECT UserId FROM AspNetUserLogins WHERE LoginProvider = @LoginProvider AND ProviderKey = @ProviderKey", parameters); userId = userIdObject == null ? null : userIdObject.ToString(); } return(userId); }
public int Insert(IdentityNavigation identity) { //Common syntax var sqlCmd = @"M_Navigation_Insert"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@ParentId", identity.ParentId }, { "@Area", identity.Area }, { "@Name", identity.Name }, { "@Title", identity.Title }, { "@Desc", identity.Desc }, { "@Action", identity.Action }, { "@Controller", identity.Controller }, { "@Visible", identity.Visible }, { "@Authenticate", identity.Authenticate }, { "@CssClass", identity.CssClass }, { "@SortOrder", identity.SortOrder }, { "@AbsoluteUri", identity.AbsoluteUri }, { "@Active", identity.Active }, { "@IconCss", identity.IconCss } }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute M_Navigation_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public int Insert(IdentityProject identity) { var sqlCmd = @"Project_Insert"; var newId = 0; var strNow = DateTime.Now.ToString("dd/mm/yyyy h:mm"); var parameters = new Dictionary <string, object> { { @"Code", identity.Code }, { @"ProjectCategoryId", identity.ProjectCategoryId }, { @"CompanyId", identity.CompanyId }, { @"Name", identity.Name }, { @"ShortDescription", identity.ShortDescription }, { @"Detail", identity.Detail }, { @"BeginDate", identity.BeginDate }, { @"FinishDate", identity.FinishDate }, { @"CreatedBy", identity.CreatedBy }, { @"CreatedDate", strNow }, { @"Status", identity.Status }, }; try { using (var conn = new SqlConnection(_connectinString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute Project_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public int Insert(IdentityCompany identity) { var sqlCmd = @"Company_Insert"; var newId = 0; var paramaters = new Dictionary <string, object> { { "Code", identity.Code }, { "Name", identity.Name }, { "CountryId", identity.CountryId }, { "ProvinceId", identity.ProvinceId }, { "DistrictId", identity.DistrictId }, { "Address", identity.Address }, { "Email", identity.Email }, { "Phone", identity.Phone }, { "IsEPE", identity.IsEPE }, { "Status", identity.Status }, }; try { using (var connn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(connn, CommandType.StoredProcedure, sqlCmd, paramaters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute Company_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public int Delete(int id) { var sqlCmd = @"M_Post_Delete"; var parameters = new Dictionary <string, object> { { "@Id", id } }; try { using (var conn = new SqlConnection(_connectionString)) { var result = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); return(Utils.ConvertToInt32(result)); } } catch (Exception ex) { var strError = "Failed to M_Post_Delete. Error: " + ex.Message; throw new CustomSQLException(strError); } }
public int InsertSlideItem(IdentitySlideItem identity) { //Common syntax var sqlCmd = @"M_SlideItem_Insert"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { { "@SlideId", identity.SlideId }, { "@Title", identity.Title }, { "@Description", identity.Description }, { "@ImageUrl", identity.ImageUrl }, { "@Link", identity.Link }, { "@LinkAction", identity.LinkAction }, { "@SortOrder", identity.SortOrder }, { "@Status", identity.Status } }; try { using (var conn = new SqlConnection(_connectionString)) { var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlCmd, parameters); newId = Convert.ToInt32(returnObj); } } catch (Exception ex) { var strError = "Failed to execute M_SlideItem_Insert. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
public int GoodsReceipt(IdentityWarehouse identity, IdentityWarehouseActivity activity) { //Common syntax var sqlGoodsReceiptCmd = @"Warehouse_GoodsReceipt_Item"; var newId = 0; //For parameters var parameters = new Dictionary <string, object> { //{"@Name", identity.Name}, //{"@Code", identity.Code }, //{"@CreatedBy", identity.CreatedBy}, //{"@Status", identity.Status} }; StringBuilder activityInsertCmd = new StringBuilder(); var cmdGoodsReceiptCreate = string.Empty; StringBuilder goodsReceiptDetailCmd = new StringBuilder(); try { using (var conn = new SqlConnection(_connectionString)) { if (identity.ProductList != null && identity.ProductList.Count > 0) { foreach (var item in identity.ProductList) { var itemParms = new Dictionary <string, object> { { "@ProductId", item.Id }, { "@WarehouseNum", item.WarehouseNum } }; var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlGoodsReceiptCmd, itemParms); newId = Utils.ConvertToInt32(returnObj); if (newId >= 0) { cmdGoodsReceiptCreate = string.Format("INSERT INTO tbl_goods_receipt(LotNo,CreatedBy) VALUES('{0}',{1}); SELECT SCOPE_IDENTITY();", string.Empty, activity.StaffId); var activityCmd = string.Format(@"INSERT INTO tbl_warehouse_activity (ActivityType,ProductId,NumberOfProducts,StaffId,DeviceId,ObjectId) VALUES ({0},{1},{2},{3},{4},{5}); ", activity.ActivityType, item.Id, item.WarehouseNum, activity.StaffId, activity.DeviceId, "##NEWID##"); var productDetailCmd = string.Format(@"INSERT INTO tbl_goods_receipt_detail (GoodsReceiptId,ProductId,NumberOfProducts) VALUES ({0},{1},{2});", "##NEWID##", item.Id, item.WarehouseNum); activityInsertCmd.Append(activityCmd); goodsReceiptDetailCmd.Append(productDetailCmd); } } var goodsReceiptId = 0; if (!string.IsNullOrEmpty(cmdGoodsReceiptCreate)) { var returnObjGoodsReceipt = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, cmdGoodsReceiptCreate, null); goodsReceiptId = Utils.ConvertToInt32(returnObjGoodsReceipt); if (goodsReceiptId > 0) { goodsReceiptDetailCmd.Replace("##NEWID##", goodsReceiptId.ToString()); var goodsReceiptDetailCmdCmdExec = goodsReceiptDetailCmd.ToString(); if (!string.IsNullOrEmpty(goodsReceiptDetailCmdCmdExec)) { //Insert details MsSqlHelper.ExecuteScalar(conn, CommandType.Text, goodsReceiptDetailCmdCmdExec, null); } } } activityInsertCmd.Replace("##NEWID##", goodsReceiptId.ToString()); var activityCmdExec = activityInsertCmd.ToString(); if (!string.IsNullOrEmpty(activityCmdExec)) { //Insert logs MsSqlHelper.ExecuteScalar(conn, CommandType.Text, activityCmdExec, null); } } } } catch (Exception ex) { var strError = "Failed to execute Warehouse_GoodsReceipt_Item. Error: " + ex.Message; throw new CustomSQLException(strError); } return(newId); }
//----------------------------------------------------------------------------------------------------------------------------- /// SelectScalar //----------------------------------------------------------------------------------------------------------------------------- public object SelectScalar(string connection_string, string command_text, MsDatParameters db_params) { return(MsSqlHelper.ExecuteScalar(connection_string, CommandType.Text, command_text, (SqlParameter[])db_params)); }
public double ReflectStockTake(IdentityWarehouse identity, IdentityWarehouseActivity activity) { //Common syntax var sqlReflectCmd = @"Warehouse_ReflectStockTake_Item"; double stockQTY = 0; //For parameters var parameters = new Dictionary <string, object> { //{"@Name", identity.Name}, //{"@Code", identity.Code }, //{"@CreatedBy", identity.CreatedBy}, //{"@Status", identity.Status} }; StringBuilder activityInsertCmd = new StringBuilder(); var cmdReflectStockCreate = string.Empty; StringBuilder reflectDetailCmd = new StringBuilder(); try { using (var conn = new SqlConnection(_connectionString)) { if (identity.ProductList != null && identity.ProductList.Count > 0) { foreach (var item in identity.ProductList) { var itemParms = new Dictionary <string, object> { { "@ProductId", item.Id } }; var returnObj = MsSqlHelper.ExecuteScalar(conn, CommandType.StoredProcedure, sqlReflectCmd, itemParms); stockQTY = Utils.ConvertToDouble(returnObj); if (stockQTY >= 0) { cmdReflectStockCreate = string.Format("INSERT INTO tbl_reflectstocktake(CreatedBy) VALUES({0}); SELECT SCOPE_IDENTITY();", activity.StaffId); var activityCmd = string.Format(@"INSERT INTO tbl_warehouse_activity (ActivityType,ProductId,NumberOfProducts,StaffId,DeviceId,ObjectId) VALUES ({0},{1},{2},{3},{4},{5}); ", activity.ActivityType, item.Id, stockQTY, activity.StaffId, activity.DeviceId, "##NEWID##"); var productDetailCmd = string.Format(@"INSERT INTO tbl_reflectstocktake_detail (ReflectStockTakeId,ProductId,NumberOfProducts) VALUES ({0},{1},{2});", "##NEWID##", item.Id, stockQTY); activityInsertCmd.Append(activityCmd); reflectDetailCmd.Append(productDetailCmd); } } var reflectStockId = 0; if (!string.IsNullOrEmpty(cmdReflectStockCreate)) { var returnObjReflectStock = MsSqlHelper.ExecuteScalar(conn, CommandType.Text, cmdReflectStockCreate, null); reflectStockId = Utils.ConvertToInt32(returnObjReflectStock); if (reflectStockId > 0) { reflectDetailCmd.Replace("##NEWID##", reflectStockId.ToString()); var reflectStockDetailCmdCmdExec = reflectDetailCmd.ToString(); if (!string.IsNullOrEmpty(reflectStockDetailCmdCmdExec)) { //Insert details MsSqlHelper.ExecuteScalar(conn, CommandType.Text, reflectStockDetailCmdCmdExec, null); } } } activityInsertCmd.Replace("##NEWID##", reflectStockId.ToString()); var activityCmdExec = activityInsertCmd.ToString(); if (!string.IsNullOrEmpty(activityCmdExec)) { //Insert logs MsSqlHelper.ExecuteScalar(conn, CommandType.Text, activityCmdExec, null); } } } } catch (Exception ex) { var strError = "Failed to execute Warehouse_GoodsIssue_Item. Error: " + ex.Message; throw new CustomSQLException(strError); } return(stockQTY); }
public override object ExecuteScalar(string sql) { return(MsSqlHelper.ExecuteScalar(ConnectionstringLocalTransaction, CommandType.Text, sql, null)); }