protected void Page_Load(object sender, EventArgs e) { if (Request.QueryString.AllKeys.Length > 0) { DataBaseConfig dt = new DataBaseConfig(); if (Request.QueryString["op"] == "getcustomer") { dt.oCommand = new SqlCommand("select * from Customer where Id=" + Request.QueryString["Id"]); dt.oDataReader = dt.ExecuteReader(dt.oCommand); if (dt.oDataReader.Read()) { //{"id_customer":7,"tax_office":"elazığ vergi bürosu","tax_number":1231231} Response.Write(dt.oDataReader["Id"] + ":" + dt.oDataReader["Tax_Office"] + ":" + dt.oDataReader["Tax_Number"]); } } else if (Request.QueryString["op"] == "getproductprice") { dt.oCommand = new SqlCommand("select * from Product where Id=" + Request.QueryString["Id"]); dt.oDataReader = dt.ExecuteReader(dt.oCommand); if (dt.oDataReader.Read()) { //{ "id_product":6,"name":"Product1","price":"120.0000","tax_rate":"0.0500"} Response.Write(dt.oDataReader["Id"] + ":" + dt.oDataReader["Name"] + ":" + dt.oDataReader["Price"] + ":" + dt.oDataReader["Tax_Rate"]); } } } }
public async Task Update(T entity, string updateSql) { using (IDbConnection conn = DataBaseConfig.Connection()) { await conn.ExecuteAsync(updateSql, entity); } }
public static ResultMsg Execute(IDictionary <string, DataTable> dics) { var resultMsg = new ResultMsg(); using (var conn = DataBaseConfig.GetConnection()) { var trans = conn.BeginTransaction(); var sqlBulk = new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepNulls, trans) { BulkCopyTimeout = 600 }; try { foreach (var dic in dics) { sqlBulk.DestinationTableName = dic.Key; sqlBulk.WriteToServer(dic.Value); } trans.Commit(); resultMsg.MsgState = true; } catch (Exception ex) { trans.Rollback(); resultMsg.MsgState = false; resultMsg.MsgData = "操作失败,详细原因:" + ex.Message; } return(resultMsg); } }
public async Task Delete(Guid Id, string deleteSql) { using (IDbConnection conn = DataBaseConfig.Connection()) { await conn.ExecuteAsync(deleteSql, new { Id = Id }); } }
public async Task Insert(T entity, string insertSql) { using (IDbConnection conn = DataBaseConfig.Connection()) { await conn.ExecuteAsync(insertSql, entity); } }
public PageData <News> GetNewsList(int channelId, int pageIndex, int pageSize) { int startIndex = (pageIndex - 1) * pageSize; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { StringBuilder sql = new StringBuilder(); sql.Append(" SELECT N.*,C.name as channelName FROM News N"); sql.Append(" left join Channels C on N.channelId=C.channelId"); if (channelId != -1) { sql.Append(" where N.channelId=@channelId"); } sql.Append(" ORDER BY N.id "); sql.Append(" OFFSET (@startIndex * @pageSize) ROWS"); sql.Append(" FETCH NEXT @pageSize ROWS ONLY"); var dataList = conn.Query <News>(sql.ToString(), new { channelId, startIndex, pageSize }).ToList(); StringBuilder sqlCount = new StringBuilder(); sqlCount.Append(" select count(1) from News N"); sqlCount.Append(" left join Channels C on N.channelId=C.channelId "); if (channelId != -1) { sql.Append(" and N.channelId=@channelId"); } var count = conn.QueryFirstOrDefault <int>(sqlCount.ToString(), new { channelId }); return(new PageData <News>(dataList, pageIndex, pageSize, count)); } }
protected void Page_Load(object sender, EventArgs e) { if (!IsPostBack) { if (Request.QueryString.AllKeys.Length > 0) { if (Request.QueryString["Id"] != null) { DataBaseConfig db = new DataBaseConfig(); db.oCommand = new SqlCommand("Select * from Customer Where Id = " + Request.QueryString["Id"]); db.oDataReader = db.ExecuteReader(db.oCommand); if (db.oDataReader.Read()) { Firstname.Text = db.oDataReader["Firstname"].ToString(); Lastname.Text = db.oDataReader["Lastname"].ToString(); Companyname.Text = db.oDataReader["Companyname"].ToString(); Addres.Text = db.oDataReader["Addres"].ToString(); Email.Text = db.oDataReader["Email"].ToString(); Phone.Text = db.oDataReader["Phone"].ToString(); Tax_Number.Text = db.oDataReader["Tax_Number"].ToString(); Tax_Office.Text = db.oDataReader["Tax_Office"].ToString(); } } } } }
/// <summary> /// 通过角色名获取所有ASM /// </summary> /// <returns></returns> public async Task <List <Users> > GetASMByRole() { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <Users>("select * from SIDE_VW_GetASMByRole", null).ToList())); } }
private static string GetWebserviceDatabaseConfig() { var webDBconfigService = new WebDBConfigService(); var dbAccount = webDBconfigService.WS_AUTHENDB_DBConfig(DBConfig.ProjectID); if (String.IsNullOrEmpty(dbAccount.Status) || dbAccount.Status != "0000") { String strError = "Project ID : " + DBConfig.ProjectID + " >> " + dbAccount.Status + " : " + dbAccount.Detail; } var dbConfig = new DataBaseConfig(); dbConfig.DecrptedServername = AuthenDecrypt.Decrypt.textDecrpyt(dbAccount.ServerName.ToString()); dbConfig.DatabaseName = DBConfig.POPDBname; dbConfig.DecryptedUsername = AuthenDecrypt.Decrypt.textDecrpyt(dbAccount.UserName.ToString()); dbConfig.DecryptedPassword = AuthenDecrypt.Decrypt.textDecrpyt(dbAccount.Password.ToString()); var connectionString = String.Format("Data Source={0};Initial Catalog={1};User Id={2};Password={3};Max Pool Size=400;Connect Timeout=600" , dbConfig.DecrptedServername , dbConfig.DatabaseName , dbConfig.DecryptedUsername , dbConfig.DecryptedPassword); return(connectionString); }
/// <summary> /// 修改一个用户 /// </summary> /// <param name="entity">要修改的用户</param> /// <param name="connectionString">链接字符串</param> /// <returns></returns> public Task <Boolean> Update(User entity, String connectionString = null) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) { string updateSql = @"UPDATE dbo.auth_User SET TenantId = @tenantid, Name = @name, Password = @password, SecurityStamp = @securitystamp, FullName = @fullname, Surname = @surname, PhoneNumber = @phonenumber, IsPhoneNumberConfirmed = @isphonenumberconfirmed, EmailAddress = @emailaddress, IsEmailConfirmed = @isemailconfirmed, EmailConfirmationCode = @emailconfirmationcode, IsActive = @isactive, PasswordResetCode = @passwordresetcode, LastLoginTime = @lastlogintime, IsLockoutEnabled = @islockoutenabled, AccessFailedCount = @accessfailedcount, LockoutEndDateUtc = @lockoutenddateutc WHERE Id = @Id"; return(Task.FromResult <Boolean>(conn.Execute(updateSql, entity) > 0)); } }
/// <summary> /// 获取所有用户 /// </summary> /// <param name="connectionString">链接字符串</param> /// <returns></returns> public Task <IEnumerable <User> > GetAllEntity(String connectionString = null) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) { string querySql = @"SELECT Id , TenantId , Name , Password , SecurityStamp , FullName , Surname , PhoneNumber , IsPhoneNumberConfirmed , EmailAddress , IsEmailConfirmed , EmailConfirmationCode , IsActive , PasswordResetCode , LastLoginTime , IsLockoutEnabled , AccessFailedCount , LockoutEndDateUtc FROM dbo.auth_User;"; return(Task.FromResult <IEnumerable <User> >(conn.Query <User>(querySql))); } }
/// <summary> /// 根据主键Id获取一个用户 /// </summary> /// <param name="id">主键Id</param> /// <param name="connectionString">链接字符串</param> /// <returns></returns> public Task <User> GetEntityById(Int64 id, String connectionString = null) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection(connectionString)) { string querySql = @"SELECT Id , TenantId , Name , Password , SecurityStamp , FullName , Surname , PhoneNumber , IsPhoneNumberConfirmed , EmailAddress , IsEmailConfirmed , EmailConfirmationCode , IsActive , PasswordResetCode , LastLoginTime , IsLockoutEnabled , AccessFailedCount , LockoutEndDateUtc FROM dbo.auth_User WHERE Id = @Id"; return(Task.FromResult <User>(conn.QueryFirstOrDefault <User>(querySql, new { Id = id }))); } }
/// <summary> /// 修改 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Update(PublishHouse model) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection(null)) { return(conn.Update(model)); } }
/// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> public long Create(PublishHouse model) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection(null)) { return(conn.Insert(model)); } }
/// <summary> /// 添加 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Create(Books model) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(conn.Insert(model) > 0); } }
public async Task <List <MD_WHS_Channel> > GetMD_WHS_Channel() { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <MD_WHS_Channel>("select distinct [WHS_Channel] from SIDE_MD_WHS_Channel").ToList())); } }
/// <summary> /// 删除 /// </summary> /// <param name="model"></param> /// <returns></returns> public bool Delete(Books model) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(conn.Delete(model)); } }
public async Task <List <MD_Product_Channel> > GetMD_Product_Channel() { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <MD_Product_Channel>("select ID,CodeName from SIDE_MD_Product_Channel").ToList())); } }
public void CreateEasyJobTable(EntityDB.IDatabaseService db) { bool exists = true; try { db.ExecSqlString("select * from __wayeasyjob"); } catch { exists = false; } if (!exists) { db.ExecSqlString("CREATE TABLE [__wayeasyjob](contentConfig varchar(1000) NOT NULL)"); var dbconfig = new DataBaseConfig(); try { dbconfig.LastUpdatedID = Convert.ToInt32(db.ExecSqlString("select lastID from __EasyJob")); } catch { } db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", dbconfig.ToJsonString()); } }
/// <summary> /// /// </summary> /// <returns></returns> public int AddMD_POS_Type(MD_POS_Type model) { StringBuilder strSql = new StringBuilder(); StringBuilder strSqlupdate = new StringBuilder(); // 修改有限顺序序号 strSqlupdate.Append(" if exists(select * from SIDE_MD_POS_Type where POS_Channel = @POS_Channel and POS_Segment = @POS_Segment and Type_Sequence = @Type_Sequence)"); strSqlupdate.Append(" begin"); strSqlupdate.Append(" update SIDE_MD_POS_Type set Type_Sequence = Type_Sequence + 1 where POS_Channel = @POS_Channel and POS_Segment = @POS_Segment and Type_Sequence>= @Type_Sequence"); strSqlupdate.Append(" end "); strSql.Append("insert into SIDE_MD_POS_Type( POS_Channel, POS_Segment, POS_Type, Type_Sequence, Type_Status )"); strSql.Append(" values ( @POS_Channel, @POS_Segment, @POS_Type, @Type_Sequence, @Type_Status )"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@POS_Channel", model.POS_Channel, DbType.String, ParameterDirection.Input); parameters.Add("@POS_Segment", model.POS_Segment, DbType.String, ParameterDirection.Input); parameters.Add("@POS_Type", model.POS_Type, DbType.String, ParameterDirection.Input); parameters.Add("@Type_Sequence", model.Type_Sequence, DbType.Int32, ParameterDirection.Input); parameters.Add("@Type_Status", 1, DbType.String, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { conn.Execute(strSqlupdate.ToString(), parameters); return(conn.Execute(strSql.ToString(), parameters)); } }
public void CreateEasyJobTable(EntityDB.IDatabaseService db) { bool exists = true; try { db.ExecSqlString("select * from __wayeasyjob"); } catch (Exception ex) { exists = false; } if (!exists) { db.ExecSqlString("CREATE TABLE [__wayeasyjob](contentConfig TEXT NOT NULL)"); var dbconfig = new DataBaseConfig(); try { dbconfig.LastUpdatedID = Convert.ToInt32(db.ExecSqlString("select lastID from __EasyJob")); } catch { } db.ExecSqlString("insert into __wayeasyjob (contentConfig) values (@p0)", dbconfig.ToJsonString()); } //try //{ // db.ExecSqlString("CREATE TABLE [] ([lastID] integer NOT NULL)"); // db.ExecSqlString("insert into (lastID) values (0)"); //} //catch //{ //} }
public int AddMD_BulkShipTo(MD_BulkShipTo model) { StringBuilder strSql = new StringBuilder(); strSql.Append("insert into SIDE_MD_BulkShipTo(BRANCH_EN,BRANCH_CN,BLR,Province,City,StoreFormat,WHS_Channel,Bulk_Ship_to_Code_Core,Bulk_Ship_to_Code_Originals,Bulk_Ship_to_Code_Neo,Bulk_Ship_to_Code_Kids )"); strSql.Append(" values (@BRANCH_EN,@BRANCH_CN,@BLR,@Province,@City,@StoreFormat,@WHS_Channel,@Bulk_Ship_to_Code_Core,@Bulk_Ship_to_Code_Originals,@Bulk_Ship_to_Code_Neo,@Bulk_Ship_to_Code_Kids )"); DynamicParameters parameters = new DynamicParameters(); parameters.Add("@BRANCH_EN", model.BRANCH_EN, DbType.String, ParameterDirection.Input); parameters.Add("@BRANCH_CN", model.BRANCH_CN, DbType.String, ParameterDirection.Input); parameters.Add("@WHS_Channel", model.WHS_Channel, DbType.String, ParameterDirection.Input); parameters.Add("@BLR", model.BLR, DbType.String, ParameterDirection.Input); parameters.Add("@City", model.City, DbType.String, ParameterDirection.Input); parameters.Add("@Province", model.Province, DbType.String, ParameterDirection.Input); parameters.Add("@StoreFormat", model.StoreFormat, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Core", model.Bulk_Ship_to_Code_Core, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Originals", model.Bulk_Ship_to_Code_Originals, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Neo", model.Bulk_Ship_to_Code_Neo, DbType.String, ParameterDirection.Input); parameters.Add("@Bulk_Ship_to_Code_Kids", model.Bulk_Ship_to_Code_Kids, DbType.String, ParameterDirection.Input); using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(conn.Execute(strSql.ToString(), parameters)); } }
public async Task <T> Detail(Guid Id, string detailSql) { using (IDbConnection conn = DataBaseConfig.Connection()) { return(await conn.QueryFirstOrDefault(detailSql, new { Id = Id })); } }
public async Task PutUser(Users entity) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { IDbTransaction transaction = conn.BeginTransaction(); try { string deleteSql = "DELETE FROM [dbo].[SIDE_PLM_EmployeeRole] WHERE EMPLOYEE=@ID"; await conn.ExecuteAsync(deleteSql, new { ID = entity.ID }, transaction); foreach (var role in entity.Roles) { string insertSql = @"INSERT INTO SIDE_PLM_EmployeeRole(EMPLOYEE,ROLE) SELECT @ID,ID FROM SIDE_PLM_Role WHERE NameCN=@RoleName"; await conn.ExecuteAsync(insertSql, new { ID = entity.ID, RoleName = role.RoleName }, transaction); } transaction.Commit(); } catch (Exception exception) { transaction.Rollback(); } } }
public async Task <List <T> > Select(string selectSql) { using (IDbConnection conn = DataBaseConfig.Connection()) { return(await Task.Run(() => conn.Query <T>(selectSql).ToList())); } }
public async Task <Users> GetUserAndRoles(int Id) { Users userList = new Users(); string selectSql = @"SELECT id,ntAccount,name,RoleName,RoleNameShort FROM SIDE_PLM_V_Users WHERE ID=@Id "; using (IDbConnection connection = DataBaseConfig.GetSqlConnection()) { var lookUp = new Dictionary <int, Users>(); userList = connection.Query <Users, Roles, Users>(selectSql, (user, role) => { if (!lookUp.TryGetValue(user.ID, out Users u)) { lookUp.Add(user.ID, u = user); u = user; } u.Roles.Add(role); return(u); }, splitOn: "RoleName", param: new { Id = Id } ).First(); return(userList); } }
public async Task <List <object> > GetCustomerGroupByGroup(string group) { using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { return(await Task.Run(() => conn.Query <object>("select distinct [Customer] from SIDE_PLM_BLR_Customer_Group where [group]=@group", new { group }).ToList())); } }
public async Task <Users> GetUserAndRolesByAccount(string account) { account = "ap\\" + account; Users userList = new Users(); string selectSql = @"SELECT id,ntAccount,name,RoleName,RoleNameShort,RoleID FROM SIDE_PLM_V_Users WHERE ntAccount=@ntAccount "; using (IDbConnection connection = DataBaseConfig.GetSqlConnection()) { var lookUp = new Dictionary <int, Users>(); var result = await connection.QueryAsync <Users, Roles, Users>(selectSql, (user, role) => { if (!lookUp.TryGetValue(user.ID, out Users u)) { lookUp.Add(user.ID, u = user); u = user; } u.Roles.Add(role); return(u); }, splitOn : "RoleName", param : new { ntAccount = account } ); return(result.First()); } }
//根据Year Season POSCode 查询是否存在审批记录 public async Task <bool> IsUpdateBLR(string YEAR, string SEASON, string POSCode) { string sql = @" select * from SIDE_PLM_BLR_POSReview where [Year] = @YEAR and [Season] = @SEASON and [POSCode] = @POSCode"; using (IDbConnection conn = DataBaseConfig.GetSqlConnection()) { BLR_POSReview result = await Task.Run(() => conn.QueryFirstOrDefault <BLR_POSReview>(sql, new { YEAR, SEASON, POSCode })); if (result == null) { //未提交 return(true); } else { if (result.PosState == "Submitted By AS" || result.PosState == "Approved By ASM") { return(false); } else { return(true); } } } }
public async Task ExcuteSQL(string excuteSql) { using (MySqlConnection conn = DataBaseConfig.GetSqlConnection()) { await conn.ExecuteAsync(excuteSql); } }