public int DataUpdate(FunctionModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" UPDATE FUNCTIONS SET NAME=@NAME,CATEGORY=@CATEGORY,PARENT_SN=@PARENT_SN,URL=@URL,SORT=@SORT ,MODE=@MODE,MDATE=GETDATE(),MUSER=@MUSER WHERE SN=@SN; "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "SN", DbType.String, req.FUNCTIONS.SN); Db.AddInParameter(cmd, "NAME", DbType.String, req.FUNCTIONS.NAME); Db.AddInParameter(cmd, "CATEGORY", DbType.String, req.FUNCTIONS.CATEGORY); Db.AddInParameter(cmd, "PARENT_SN", DbType.Int16, req.FUNCTIONS.PARENT_SN); Db.AddInParameter(cmd, "URL", DbType.String, req.FUNCTIONS.URL); Db.AddInParameter(cmd, "SORT", DbType.Int16, req.FUNCTIONS.SORT); Db.AddInParameter(cmd, "MODE", DbType.String, req.FUNCTIONS.MODE); //Db.AddInParameter(cmd, "MDATE", DbType.DateTime, req.FUNCTIONS.MDATE); Db.AddInParameter(cmd, "MUSER", DbType.Int16, req.FUNCTIONS.MUSER); count = Db.ExecuteNonQuery(cmd); } return(count); }
public USER_INFO UserLoginAuthority(string UserId, string Password) { USER_INFO users = null; string sql = @" SELECT U.SN, U.NAME, U.PASSWORD, U.FORCE_PWD, G.ROLES_SN FROM USERS U LEFT JOIN GRANTS G ON U.SN=G.USERS_SN WHERE U.ID=@ID AND U.MODE='Y' "; using (DbCommand cmd = Db.GetSqlStringCommand(sql)) { Db.AddInParameter(cmd, "ID", DbType.String, UserId); using (IDataReader reader = this.Db.ExecuteReader(cmd)) { if (reader.Read() && reader["PASSWORD"] as string == Password) { users = new USER_INFO() { SN = reader["SN"] as Int16? ?? null, ID = UserId, NAME = reader["NAME"] as string, FORCE_PWD = reader["FORCE_PWD"] as Int16? ?? null, ROLES_SN = reader["ROLES_SN"] as Int32? ?? null }; } } } return(users); }
public int DataUpdate(PhraseModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" UPDATE PHRASE SET PHRASE_GROUP=@PHRASE_GROUP,PHRASE_KEY=@PHRASE_KEY,PHRASE_VALUE=@PHRASE_VALUE,PHRASE_DESC=@PHRASE_DESC,SORT=@SORT,MODE=@MODE,MDATE=GETDATE(),MUSER=@MUSER WHERE SN=@SN; "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "SN", DbType.String, req.PHRASE.SN); Db.AddInParameter(cmd, "PHRASE_GROUP", DbType.String, req.PHRASE.PHRASE_GROUP); Db.AddInParameter(cmd, "PHRASE_KEY", DbType.String, req.PHRASE.PHRASE_KEY); Db.AddInParameter(cmd, "PHRASE_VALUE", DbType.String, req.PHRASE.PHRASE_VALUE); Db.AddInParameter(cmd, "PHRASE_DESC", DbType.String, req.PHRASE.PHRASE_DESC); Db.AddInParameter(cmd, "SORT", DbType.String, req.PHRASE.SORT); Db.AddInParameter(cmd, "MODE", DbType.String, req.PHRASE.MODE); Db.AddInParameter(cmd, "MUSER", DbType.String, req.PHRASE.MUSER); count = Db.ExecuteNonQuery(cmd); } return(count); }
public void DeleteItem(string hospitalId, string moveId, string productId) { var sql = @" delete goods_runtime from goods_runtime r, move_form_serial s where r.apply_id = s.move_id and r.serial_id = s.to_serial and r.hospital_id = @p_hospital_id and s.move_id = @p_move_id and s.product_id = @p_product_id; delete goods_inventory_runtime where apply_id = @p_move_id and hospital_id = @p_hospital_id and product_id = @p_product_id; delete move_form_serial where move_id = @p_move_id and product_id = @p_product_id; delete move_form_items where move_id = @p_move_id and product_id = @p_product_id; "; var cmd = Db.GetSqlStringCommand(sql); Db.AddInParameter(cmd, "p_move_id", DbType.String, moveId); Db.AddInParameter(cmd, "p_hospital_id", DbType.String, hospitalId); Db.AddInParameter(cmd, "p_product_id", DbType.String, productId); Db.ExecuteNonQuery(cmd, DbTrans); }
public ROLES ModificationQuery(int?SN) { ROLES row = null; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" SELECT SN,NAME,MODE,CDATE,CUSER,MDATE,MUSER FROM ROLES WHERE SN=@SN "; Db.AddInParameter(cmd, "SN", DbType.Int32, SN); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { row = new ROLES { SN = reader["SN"] as Int32? ?? null, NAME = reader["NAME"] as string, MODE = reader["MODE"] as string, CDATE = reader["CDATE"] as DateTime?, CUSER = reader["CUSER"] as string, MDATE = reader["MDATE"] as DateTime?, MUSER = reader["MUSER"] as string }; } } } return(row); }
public List <GRANTS> ByRolesQuery(int?ROLES_SN) { List <GRANTS> GRANTS = new List <GRANTS>(); using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" SELECT U.SN,U.NAME,G.ROLES_SN FROM USERS U LEFT JOIN GRANTS G ON U.SN=G.USERS_SN AND G.ROLES_SN=@ROLES_SN "; Db.AddInParameter(cmd, "ROLES_SN", DbType.Int32, ROLES_SN); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { GRANTS row = new GRANTS { ROLES_SN = reader["ROLES_SN"] as Int32? ?? null, USERS_SN = reader["SN"] as Int16? ?? null, USERS_NAME = reader["NAME"] as string }; GRANTS.Add(row); } } } return(GRANTS); }
public int DataCreate(RolesModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" INSERT ROLES (NAME,MODE,CDATE,CUSER,MDATE,MUSER) VALUES (@NAME,@MODE,GETDATE(),@CUSER,GETDATE(),@MUSER) SET @SN = SCOPE_IDENTITY(); "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "NAME", DbType.String, req.ROLES.NAME); Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.MODE); Db.AddInParameter(cmd, "CDATE", DbType.String, req.ROLES.CDATE); Db.AddInParameter(cmd, "CUSER", DbType.String, req.ROLES.CUSER); Db.AddInParameter(cmd, "MDATE", DbType.String, req.ROLES.MDATE); Db.AddInParameter(cmd, "MUSER", DbType.String, req.ROLES.MUSER); Db.AddOutParameter(cmd, "SN", DbType.Int32, 1); count = Db.ExecuteNonQuery(cmd); req.ROLES.SN = Db.GetParameterValue(cmd, "SN") as Int32? ?? null; } return(count); }
public int DataCreate(FunctionModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" INSERT INTO FUNCTIONS (NAME,CATEGORY,PARENT_SN,URL,SORT,MODE,CDATE,CUSER,MDATE,MUSER) VALUES (@NAME,@CATEGORY,@PARENT_SN,@URL,@SORT,@MODE,GETDATE(),@CUSER,GETDATE(),@MUSER) SET @SN = SCOPE_IDENTITY();"; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "NAME", DbType.String, req.FUNCTIONS.NAME); Db.AddInParameter(cmd, "CATEGORY", DbType.String, req.FUNCTIONS.CATEGORY); Db.AddInParameter(cmd, "PARENT_SN", DbType.Int16, req.FUNCTIONS.PARENT_SN); Db.AddInParameter(cmd, "URL", DbType.String, req.FUNCTIONS.URL); Db.AddInParameter(cmd, "SORT", DbType.Int16, req.FUNCTIONS.SORT); Db.AddInParameter(cmd, "MODE", DbType.String, req.FUNCTIONS.MODE); //Db.AddInParameter(cmd, "CDATE", DbType.DateTime, req.FUNCTIONS.CDATE); Db.AddInParameter(cmd, "CUSER", DbType.Int16, req.FUNCTIONS.CUSER); //Db.AddInParameter(cmd, "MDATE", DbType.DateTime, req.FUNCTIONS.MDATE); Db.AddInParameter(cmd, "MUSER", DbType.Int16, req.FUNCTIONS.MUSER); Db.AddOutParameter(cmd, "SN", DbType.Int16, 1); count = Db.ExecuteNonQuery(cmd); req.FUNCTIONS.SN = Db.GetParameterValue(cmd, "SN") as Int16? ?? null; } return(count); }
public void DeletePrinter(int id) { var command = Db.GetStoredProcCommand(DeletePrinterStoredProcedureName); Db.AddInParameter(command, "@Id", System.Data.DbType.String, id); Db.ExecuteNonQuery(command); }
public int DataCreate(PhraseModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { Int64?PHRASE_SN = new Sequence("SCC").GetSeqBigInt("PHRASE"); string sql = @" INSERT PHRASE (SN,PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC,SORT,MODE,CDATE,CUSER,MDATE,MUSER) VALUES (@SN,@PHRASE_GROUP,@PHRASE_KEY,@PHRASE_VALUE,@PHRASE_DESC,@SORT,@MODE,GETDATE(),@CUSER,GETDATE(),@MUSER); "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "SN", DbType.Int32, PHRASE_SN); Db.AddInParameter(cmd, "PHRASE_GROUP", DbType.String, req.PHRASE.PHRASE_GROUP); Db.AddInParameter(cmd, "PHRASE_KEY", DbType.String, req.PHRASE.PHRASE_KEY); Db.AddInParameter(cmd, "PHRASE_VALUE", DbType.String, req.PHRASE.PHRASE_VALUE); Db.AddInParameter(cmd, "PHRASE_DESC", DbType.String, req.PHRASE.PHRASE_DESC); Db.AddInParameter(cmd, "SORT", DbType.String, req.PHRASE.SORT); Db.AddInParameter(cmd, "MODE", DbType.String, req.PHRASE.MODE); Db.AddInParameter(cmd, "CUSER", DbType.String, req.PHRASE.CUSER); Db.AddInParameter(cmd, "MUSER", DbType.String, req.PHRASE.MUSER); count = Db.ExecuteNonQuery(cmd); req.PHRASE.SN = (int?)PHRASE_SN; } return(count); }
public FEELING ModificationQuery(int?SN) { FEELING row = null; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" SELECT FROM FEELING WHERE SN=@SN "; Db.AddInParameter(cmd, "SN", DbType.Int32, SN); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { row = new FEELING { }; } } } return(row); }
public void AddPrinter(string serial, string mark, int model, int copiesCounter) { var command = Db.GetStoredProcCommand(AddPrinterStoredProcedureName); Db.AddInParameter(command, "@serial", System.Data.DbType.String, serial); Db.AddInParameter(command, "@mark", System.Data.DbType.String, mark); Db.AddInParameter(command, "@model", System.Data.DbType.Int32, model); Db.AddInParameter(command, "@copiesCounter", System.Data.DbType.Int32, copiesCounter); Db.ExecuteScalar(command); }
public void UpdatePrinter(int id, string serial, string mark, int model, int copiesCounter) { var command = Db.GetStoredProcCommand(UpdatePrinterStoredProcedureName); Db.AddInParameter(command, "@Id", System.Data.DbType.String, id); Db.AddInParameter(command, "@serial", System.Data.DbType.String, serial); Db.AddInParameter(command, "@mark", System.Data.DbType.String, mark); Db.AddInParameter(command, "@model", System.Data.DbType.Int32, model); Db.AddInParameter(command, "@copiesCounter", System.Data.DbType.Int32, copiesCounter); Db.ExecuteNonQuery(command); }
public CASE_OWNER ModificationQuery(int?SN) { CASE_OWNER row = null; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" SELECT SN,NAME1,NAME2,NAME3,NAME4,NAME5,NAME6,TEL1,TEL2,TEL3,TEL4,TEL5,TEL6,YEARS_OLD,ADDRESS,TEACHER1,TEACHER2,TEACHER3,FAMILY,RESIDENCE,NOTE,PROBLEM,EXPERIENCE,SUGGEST,CDATE,CUSER,MDATE,MUSER FROM CASE_OWNER WHERE SN=@SN "; Db.AddInParameter(cmd, "SN", DbType.Int32, SN); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { row = new CASE_OWNER { SN = reader["SN"] as int? ?? null, NAME1 = reader["NAME1"] as string, NAME2 = reader["NAME2"] as string, NAME3 = reader["NAME3"] as string, NAME4 = reader["NAME4"] as string, NAME5 = reader["NAME5"] as string, NAME6 = reader["NAME6"] as string, TEL1 = reader["TEL1"] as string, TEL2 = reader["TEL2"] as string, TEL3 = reader["TEL3"] as string, TEL4 = reader["TEL4"] as string, TEL5 = reader["TEL5"] as string, TEL6 = reader["TEL6"] as string, YEARS_OLD = reader["YEARS_OLD"] as string, ADDRESS = reader["ADDRESS"] as string, FAMILY = reader["FAMILY"] as string, EXPERIENCE = reader["EXPERIENCE"] as string, SUGGEST = reader["SUGGEST"] as string, CDATE = reader["CDATE"] as DateTime?, CUSER = reader["CUSER"] as string, MDATE = reader["MDATE"] as DateTime?, MUSER = reader["MUSER"] as string }; } } } return(row); }
public List <MoveFormEntity> FindAll(DateRangeCondition condition, PagerInfo pager) { pager.ComputePageCount(QueryCount(condition)); var list = new List <MoveFormEntity>(); var orderSql = " ORDER BY "; if (pager.OrderFields.Count > 0) { foreach (var field in pager.OrderFields) { orderSql += field.Field + (field.Desc ? " DESC" : "") + ","; } } else { orderSql += "form_no DESC"; } var sql = string.Format(@"SELECT {0} FROM move_form WHERE status != 'deleted' and {1}", MOVE_COLUMNS, GetConditionSql(condition)); sql = @"SELECT * FROM ( SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + MOVE_COLUMNS + @" FROM (" + sql + @") t ) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize "; var dc = Db.GetSqlStringCommand(sql); AddParameter(dc, condition); Db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex); Db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize); using (IDataReader reader = Db.ExecuteReader(dc)) { while (reader.Read()) { var entity = new MoveFormEntity(); entity.Init(reader); list.Add(entity); } } return(list); }
public FeelingRetrieveRes ReportData(FeelingRetrieveReq req) { FeelingRetrieveRes res = new FeelingRetrieveRes() { FEELING = new List <FEELING>(), Pagination = new Pagination { PageCount = 0, RowCount = 0, PageNumber = 0, MinNumber = 0, MaxNumber = 0, StartTime = DateTime.Now } }; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @"SELECT TOP(@TOP) FROM FEELING{0} ORDER BY SN DESC;"; string where = ""; Db.AddInParameter(cmd, "TOP", DbType.Int32, 1000000); if (where.Length > 0) { where = " WHERE" + where.Substring(4); } sql = String.Format(sql, where); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { var row = new FEELING { }; res.FEELING.Add(row); } } } res.Pagination.EndTime = DateTime.Now; return(res); }
public int DataDelete(CaseOwnerModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" DELETE FROM CASE_OWNER WHERE SN=@SN; "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "SN", DbType.String, req.CASE_OWNER.SN); count = Db.ExecuteNonQuery(cmd); } return(count); }
public MoveFormSerialEntity SaveSerial(MoveFormSerialEntity moveSerial) { string sql = string.Empty; if (string.IsNullOrEmpty(moveSerial.Id)) { sql = string.Format(@"insert into move_form_serial({0}) values( @p_id ,@p_move_id ,@p_form_no ,@p_product_id ,@p_from_serial ,@p_to_serial ,@p_count ,@p_package_count)", SERIAL_COLUMNS); moveSerial.Id = Guid.NewGuid().ToString(); } else { sql = @"update move_form_serial set move_id = @p_move_id ,form_no = @p_form_no ,product_id = @p_product_id ,from_serial = @p_from_serial ,to_serial = @p_to_serial ,count = @p_count ,package_count = @p_package_count where id = @p_id"; } DbCommand dc = Db.GetSqlStringCommand(sql); Db.AddInParameter(dc, "p_id", DbType.String, moveSerial.Id); Db.AddInParameter(dc, "p_move_id", DbType.String, moveSerial.MoveId); Db.AddInParameter(dc, "p_form_no", DbType.Int32, moveSerial.FormNo); Db.AddInParameter(dc, "p_product_id", DbType.String, moveSerial.ProductId); Db.AddInParameter(dc, "p_from_serial", DbType.String, moveSerial.FromSerial); Db.AddInParameter(dc, "p_to_serial", DbType.String, moveSerial.ToSerial); Db.AddInParameter(dc, "p_count", DbType.Int32, moveSerial.Count); Db.AddInParameter(dc, "p_package_count", DbType.Int32, moveSerial.PackageCount); Db.ExecuteNonQuery(dc, DbTrans); return(moveSerial); }
public int DataCreate(CaseOwnerModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" INSERT CASE_OWNER (NAME1,NAME2,NAME3,NAME4,NAME5,NAME6,TEL1,TEL2,TEL3,TEL4,TEL5,TEL6,YEARS_OLD,ADDRESS,TEACHER1,TEACHER2,TEACHER3,FAMILY,RESIDENCE,NOTE,PROBLEM,EXPERIENCE,SUGGEST,CDATE,CUSER,MDATE,MUSER) VALUES (@NAME1,@NAME2,@NAME3,@NAME4,@NAME5,@NAME6,@TEL1,@TEL2,@TEL3,@TEL4,@TEL5,@TEL6,@YEARS_OLD,@ADDRESS,@TEACHER1,@TEACHER2,@TEACHER3,@FAMILY,@RESIDENCE,@NOTE,@PROBLEM,@EXPERIENCE,@SUGGEST,GETDATE(),@CUSER,GETDATE(),@MUSER) SET @SN = SCOPE_IDENTITY(); "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "NAME1", DbType.String, req.CASE_OWNER.NAME1); Db.AddInParameter(cmd, "NAME2", DbType.String, req.CASE_OWNER.NAME2); Db.AddInParameter(cmd, "NAME3", DbType.String, req.CASE_OWNER.NAME3); Db.AddInParameter(cmd, "NAME4", DbType.String, req.CASE_OWNER.NAME4); Db.AddInParameter(cmd, "NAME5", DbType.String, req.CASE_OWNER.NAME5); Db.AddInParameter(cmd, "NAME6", DbType.String, req.CASE_OWNER.NAME6); Db.AddInParameter(cmd, "TEL1", DbType.String, req.CASE_OWNER.TEL1); Db.AddInParameter(cmd, "TEL2", DbType.String, req.CASE_OWNER.TEL2); Db.AddInParameter(cmd, "TEL3", DbType.String, req.CASE_OWNER.TEL3); Db.AddInParameter(cmd, "TEL4", DbType.String, req.CASE_OWNER.TEL4); Db.AddInParameter(cmd, "TEL5", DbType.String, req.CASE_OWNER.TEL5); Db.AddInParameter(cmd, "TEL6", DbType.String, req.CASE_OWNER.TEL6); Db.AddInParameter(cmd, "YEARS_OLD", DbType.String, req.CASE_OWNER.YEARS_OLD); Db.AddInParameter(cmd, "ADDRESS", DbType.String, req.CASE_OWNER.ADDRESS); Db.AddInParameter(cmd, "TEACHER1", DbType.String, req.CASE_OWNER.TEACHER1); Db.AddInParameter(cmd, "TEACHER2", DbType.String, req.CASE_OWNER.TEACHER2); Db.AddInParameter(cmd, "TEACHER3", DbType.String, req.CASE_OWNER.TEACHER3); Db.AddInParameter(cmd, "FAMILY", DbType.String, req.CASE_OWNER.FAMILY); Db.AddInParameter(cmd, "RESIDENCE", DbType.String, req.CASE_OWNER.RESIDENCE); Db.AddInParameter(cmd, "NOTE", DbType.String, req.CASE_OWNER.NOTE); Db.AddInParameter(cmd, "PROBLEM", DbType.String, req.CASE_OWNER.PROBLEM); Db.AddInParameter(cmd, "EXPERIENCE", DbType.String, req.CASE_OWNER.EXPERIENCE); Db.AddInParameter(cmd, "SUGGEST", DbType.String, req.CASE_OWNER.SUGGEST); Db.AddInParameter(cmd, "CUSER", DbType.String, req.CASE_OWNER.CUSER); Db.AddInParameter(cmd, "MUSER", DbType.String, req.CASE_OWNER.MUSER); Db.AddOutParameter(cmd, "SN", DbType.Int32, 1); count = Db.ExecuteNonQuery(cmd); req.CASE_OWNER.SN = Db.GetParameterValue(cmd, "SN") as Int32? ?? null; } return(count); }
/// <summary> /// /// </summary> /// <returns></returns> public string UserFunctionAuthority(USER_INFO UserInfo) { List <AUTHORITY> authority = new List <AUTHORITY>(); try { string sql = @" SELECT F.SN, F.NAME, F.CATEGORY, F.URL, F.PARENT_SN, A.ROLES_SN FROM FUNCTIONS F LEFT JOIN AUTHORITY A ON F.SN=A.FUNCTIONS_SN AND A.ROLES_SN=@ROLES_SN WHERE F.MODE='Y' ORDER BY F.SORT "; using (DbCommand cmd = Db.GetSqlStringCommand(sql)) { Db.AddInParameter(cmd, "ROLES_SN", DbType.String, UserInfo.ROLES_SN); using (IDataReader reader = this.Db.ExecuteReader(cmd)) { while (reader.Read()) { AUTHORITY data = new AUTHORITY { //USERS_SN = reader["USERS_SN"] as Int16? ?? null, //FUNCTIONS_SN = reader["SN"] as Int16? ?? null, ROLES_SN = reader["ROLES_SN"] as Int32? ?? null, FUNCTIONS = new FUNCTIONS() { SN = reader["SN"] as Int16? ?? null, NAME = reader["NAME"] as string, CATEGORY = reader["CATEGORY"] as string, URL = reader["URL"] as string, PARENT_SN = reader["PARENT_SN"] as Int16? ?? null } }; authority.Add(data); } } } } catch { throw; } return(ConstructSidebar(authority, 0, -2)); }
private void PhraseQuery(string[] phrase_group) { using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" SELECT PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC FROM PHRASE WHERE PHRASE_GROUP IN ({0}) AND MODE='Y' ORDER BY PHRASE_GROUP,SORT"; string values = ""; foreach (string s in phrase_group) { values += ",@" + s; Db.AddInParameter(cmd, s, DbType.String, s); } sql = string.Format(sql, values.TrimStart(',')); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { List <ItemList> rows = null; string group = null; while (reader.Read()) { if (group != reader["PHRASE_GROUP"] as string) { rows = new List <ItemList>(); group = reader["PHRASE_GROUP"] as string; pharse.Add(group, rows); } ItemList row = new ItemList { Key = reader["PHRASE_KEY"].ToString(), Value = reader["PHRASE_VALUE"].ToString(), Desc = reader["PHRASE_DESC"].ToString() }; rows.Add(row); } } } }
public MoveFormEntity FindOne(string id) { var sql = string.Format("select {0} from move_form where id = @p_id", MOVE_COLUMNS); var dc = Db.GetSqlStringCommand(sql); Db.AddInParameter(dc, "p_id", DbType.String, id); using (var reader = Db.ExecuteReader(dc)) { if (reader.Read()) { var entity = new MoveFormEntity(); entity.Init(reader); return(entity); } } return(null); }
public Int64?GetSeqBigInt(string Type) { Int64?NewSeqNo = null; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @"USP_SEQ_BIGINT"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = sql; Db.AddInParameter(cmd, "CUR_TYPE", DbType.String, Type); using (IDataReader reader = Db.ExecuteReader(cmd)) { reader.Read(); NewSeqNo = reader["NEW_NO"] as Int64? ?? null; } } return(NewSeqNo); }
public MoveFormItemEntity FindOneItem(string formId, string itemId) { var sql = string.Format("select {0} from move_form_items where move_id = @p_move_id and id = @p_id", ITEM_COLUMNS); var dc = Db.GetSqlStringCommand(sql); Db.AddInParameter(dc, "p_move_id", DbType.String, formId); Db.AddInParameter(dc, "p_id", DbType.String, itemId); using (var reader = Db.ExecuteReader(dc)) { if (reader.Read()) { var entity = new MoveFormItemEntity(); entity.Init(reader); return(entity); } } return(null); }
public MoveFormSerialEntity FindToSerial(string formId, string toSerial) { var sql = string.Format("select {0} from move_form_serial where move_id = @p_move_id and to_serial = @p_to_serial", SERIAL_COLUMNS); var dc = Db.GetSqlStringCommand(sql); Db.AddInParameter(dc, "p_move_id", DbType.String, formId); Db.AddInParameter(dc, "p_to_serial", DbType.String, toSerial); using (var reader = Db.ExecuteReader(dc)) { if (reader.Read()) { var entity = new MoveFormSerialEntity(); entity.Init(reader); return(entity); } } return(null); }
public string GetSeqNo(string Type, string Pre, int Len) { string NewSeqNo = ""; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @"USP_SEQ"; cmd.CommandType = CommandType.StoredProcedure; cmd.CommandText = sql; Db.AddInParameter(cmd, "CUR_TYPE", DbType.String, Type); Db.AddInParameter(cmd, "CUR_PRE", DbType.String, Pre); Db.AddInParameter(cmd, "LEN", DbType.Int32, Len); using (IDataReader reader = Db.ExecuteReader(cmd)) { reader.Read(); NewSeqNo = reader["NEW_NO"].ToString(); } } return(NewSeqNo); }
public int DataUpdate(RolesModifyReq req) { int count = 0; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" UPDATE ROLES SET NAME=@NAME,MODE=@MODE,MDATE=GETDATE(),MUSER=@MUSER WHERE SN=@SN; "; cmd.CommandType = CommandType.Text; cmd.CommandText = sql; Db.AddInParameter(cmd, "SN", DbType.String, req.ROLES.SN); Db.AddInParameter(cmd, "NAME", DbType.String, req.ROLES.NAME); Db.AddInParameter(cmd, "MODE", DbType.String, req.ROLES.MODE); Db.AddInParameter(cmd, "MUSER", DbType.String, req.ROLES.MUSER); count = Db.ExecuteNonQuery(cmd); } return(count); }
private void AddParameter(DbCommand dc, DateRangeCondition condition) { Db.AddInParameter(dc, "p_user_id", DbType.String, condition.UserId); Db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId); int formNo; if (!string.IsNullOrEmpty(condition.Content) && int.TryParse(condition.Content, out formNo)) { Db.AddInParameter(dc, "p_form_no", DbType.Int32, formNo); } if (condition.BeginDate.HasValue) { Db.AddInParameter(dc, "p_begin_date", DbType.DateTime, condition.BeginDate); } if (condition.EndDate.HasValue) { Db.AddInParameter(dc, "p_end_date", DbType.DateTime, condition.EndDate); } }
public PHRASE ModificationQuery(int?SN) { PHRASE row = null; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" SELECT SN,PHRASE_GROUP,PHRASE_KEY,PHRASE_VALUE,PHRASE_DESC,SORT,MODE,CDATE,CUSER,MDATE,MUSER FROM PHRASE WHERE SN=@SN "; Db.AddInParameter(cmd, "SN", DbType.Int32, SN); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { row = new PHRASE { SN = reader["SN"] as Int32? ?? null, PHRASE_GROUP = reader["PHRASE_GROUP"] as string, PHRASE_KEY = reader["PHRASE_KEY"] as string, PHRASE_VALUE = reader["PHRASE_VALUE"] as string, PHRASE_DESC = reader["PHRASE_DESC"] as string, SORT = reader["SORT"] as Int32? ?? null, MODE = reader["MODE"] as string, CDATE = reader["CDATE"] as DateTime?, CUSER = reader["CUSER"] as string, MDATE = reader["MDATE"] as DateTime?, MUSER = reader["MUSER"] as string }; } } } return(row); }
public FUNCTIONS ModificationQuery(Int16?SN) { FUNCTIONS row = null; using (DbCommand cmd = Db.CreateConnection().CreateCommand()) { string sql = @" SELECT SN, NAME, MODE, CATEGORY, URL, PARENT_SN, SORT, CDATE, CUSER, MDATE, MUSER FROM FUNCTIONS WHERE SN=@SN; "; Db.AddInParameter(cmd, "SN", DbType.Int32, SN); cmd.CommandType = CommandType.Text; cmd.CommandText = sql; using (IDataReader reader = Db.ExecuteReader(cmd)) { while (reader.Read()) { row = new FUNCTIONS { SN = reader["SN"] as Int16? ?? null, NAME = reader["NAME"] as string, MODE = reader["MODE"] as string, CATEGORY = reader["CATEGORY"] as string, URL = reader["URL"] as string, PARENT_SN = reader["PARENT_SN"] as Int16? ?? null, SORT = reader["SORT"] as Int16? ?? null, CDATE = reader["CDATE"] as DateTime? ?? null, CUSER = reader["CUSER"] as Int16? ?? null, MDATE = reader["MDATE"] as DateTime? ?? null, MUSER = reader["MUSER"] as Int16? ?? null }; } } } return(row); }