public int SaveUser(UserDetails userDetails) { int iValue = 0; DynamicParameters param = new DynamicParameters(); param.Add("@Name", userDetails.Name); param.Add("@RoleId", userDetails.RoleId); param.Add("@Active", userDetails.Active?1:0); //var id = dbConnection.Query<int>(newUser, param, commandType: CommandType.StoredProcedure).SingleOrDefault(); const string sql = @"INSERT INTO dbo.UserMaster (Name,RoleId,Active) Values (@Name,@RoleId,@Active); SELECT CAST(SCOPE_IDENTITY() as int)"; var id = dbConnection.Query <int>(sql, new { Name = userDetails.Name, RoleId = userDetails.RoleId, Active = userDetails.Active ? 1 : 0 }).Single(); iValue = Convert.ToInt32(id); if (iValue > 0) { DynamicParameters paramNew = new DynamicParameters(); paramNew.Add("@UserID", iValue); paramNew.Add("@UserName", userDetails.UserName); paramNew.Add("@Password", userDetails.Password); var newid = dbConnection.Query <int>(newUserAuthentication, paramNew, commandType: CommandType.StoredProcedure).SingleOrDefault(); } return(iValue); }
public List <PROutstandingListMainModel> GetOutstandingPR() { var obj = db.Query <PROutstandingListMainModel>("Select a.ID,a.PRNo,a.PRDate from TH_PurchaseRequisition a Where Status=1").ToList(); db.Close(); return(obj); }
//User authentication. returns true or false public override bool ValidateUser(string username, string password) { bool isValidUser = false; string _Password = string.Empty; try { _Password = EncodePassword(password); List <userListModel> trxs = _db.Query <userListModel>(";Exec AuthenticateUser @UserName, @Password", new { UserName = username, Password = _Password }).ToList(); //List<User> trxs = con.Fetch<User>(";Exec getAuthenticateUser @UserName, @Password", new { UserName= username, Password= _Password }); isValidUser = trxs[0].UserName.ToString().ToUpper().Trim().Equals(username.ToUpper()); FormsAuthentication.SetAuthCookie(trxs[0].UserName.ToString().ToUpper(), false); } catch (Exception ee) { } return(isValidUser); }
public static bool ValidateUser(string username, string password) { bool isValidUser = false; string _Password = string.Empty; try { _Password = EncodePassword(password); System.Data.IDbConnection _db = DapperConnection(); List <userListModel> trxs = _db.Query <userListModel>(";Exec AuthenticateUser @UserName, @Password", new { UserName = username, Password = _Password }).ToList(); //List<User> trxs = con.Fetch<User>(";Exec getAuthenticateUser @UserName, @Password", new { UserName= username, Password= _Password }); isValidUser = trxs[0].UserName.ToString().ToUpper().Trim().Equals(username.ToUpper()); } catch (Exception ee) { } return(isValidUser); }
public Core.Domain.Building Find(int id) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); var obj = db.Query <Core.Domain.Building>("Select * from tblBuilding Where id=" + id).FirstOrDefault(); db.Close(); return(obj); }
public List <Core.Domain.Building> GetList() { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); var obj = db.Query <Core.Domain.Building>("Select * from tblBuilding").ToList(); db.Close(); return(obj); }
public int Add(TypeDTO type) { using (System.Data.IDbConnection connection = Connection.GetConnection()) { string sqlExpression = "Type_Add @name"; return(connection.Query <int>(sqlExpression, type).FirstOrDefault()); } }
protected int LastInsertId(System.Data.IDbConnection connection, IDbTransaction transaction) { if (provider == DbProvider.SQLite) { return((int)((SQLiteConnection)connection).LastInsertRowId); } return(connection.Query <int>("SELECT lastval();", transaction: transaction).First()); }
public void Delete(Todo item1) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "delete from Todo WHERE Todoid = @Todoid"; dbConnection.Open(); dbConnection.Query(sQuery, item1); } }
public void Done(int id) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "UPDATE Todo SET Done = 1 WHERE Todoid = @Todoid"; dbConnection.Open(); dbConnection.Query(sQuery, new { Todoid = id }); } }
public UserAuthentication GetUserAuthentication(UserAuthentication userCred) { UserAuthentication userAuth = new UserAuthentication(); try { DynamicParameters param = new DynamicParameters(); param.Add("@UserName", userCred.UserName); param.Add("@Password", userCred.Password); return(userAuth = dbConnection.Query <UserAuthentication>(valUser, param, commandType: CommandType.StoredProcedure).SingleOrDefault()); } catch (Exception ex) { throw; } finally { } }
public void Update(Accident item1) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = @"UPDATE AccidentTable SET Verified = @Verified WHERE Id = @Id"; dbConnection.Open(); dbConnection.Query(sQuery, item1); } }
// public void Done(Accident item1) // { // using (System.Data.IDbConnection dbConnection = GetConnection()) // { // string sQuery = "UPDATE AccidentTable SET " // + " Done = @Done " // + " WHERE Todoid = @Todoid"; // dbConnection.Open(); // dbConnection.Query(sQuery, item1); // } // } public Accident Delete(int item) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "delete from AccidentTable WHERE Id = @id"; dbConnection.Open(); List <Accident> accdnt = dbConnection.Query <Accident>(sQuery, new { id = item }).ToList(); return(accdnt.FirstOrDefault()); } }
public void Done(Todo item1) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "UPDATE Todo SET " + " Done = @Done " + " WHERE Todoid = @Todoid"; dbConnection.Open(); dbConnection.Query(sQuery, item1); } }
public EditPurchaseReqHeader GetPurchaseReqHeader(int id) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); var obj = db.Query <EditPurchaseReqHeader>(" Select Th_PurchaseRequisition.PRNO, Th_PurchaseRequisition.PRDate,Remarks " + " From Th_PurchaseRequisition " + " Where Th_PurchaseRequisition.ID = " + id + "").FirstOrDefault(); db.Close(); return(obj); }
public void Update(Todo item1) { using (System.Data.IDbConnection dbConnection = GetConnection()) { string sQuery = "UPDATE Todo SET Task = @Task," + " Done = @Done ," + " Description = @Description" + " WHERE Todoid = @Todoid"; dbConnection.Open(); dbConnection.Query(sQuery, item1); } }
public List <ReqitemCategory> GetPurchaseReqCategory(int id) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); var obj = db.Query <ReqitemCategory>(" Select distinct tblcategory.Name from tblCategory, Td_PurchaseRequisition, tblitem " + " where Td_PurchaseRequisition.ItemId = tblitem.id" + " and tblCategory.ID = tblitem.CategoryID" + " and Td_PurchaseRequisition.prid =" + id + " order by tblcategory.name").ToList(); db.Close(); return(obj); }
public IEnumerable <ModuleAccess> GetModuleAccessRights(int _UserID) { System.Data.IDbConnection _db = DapperConnection(); List <ModuleAccess> modulerights = _db.Query <ModuleAccess>(";Exec getUserMainMenu @UserName", new { UserName = Membership.GetUser().UserName.ToString() }).ToList(); return(modulerights); }
public List <BuildingViewModel> GetListView() { string sql = " Select a.id,a.BuildingNo,b.Name as TankName from tblBuilding a ,tbltank b " + " Where a.TankID = b.id"; db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); var obj = db.Query <Services.Building.BuildingViewModel>(sql).ToList(); db.Close(); return(obj); }
public List <EditPurchaseReqDetail> GetPurchaseReqDetail(int id) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); var obj = db.Query <EditPurchaseReqDetail>(" Select Th_PurchaseRequisition.ID as PRID,b.id ,tblCategory.Name as Category,c.Name as Brand, " + " a.ID as itemID, a.Name as ItemName, b.Unit,b.Specs, b.Qty" + " From tblitem a inner join TD_PurchaseRequisition b on a.id = b.ItemId" + " Left outer join tblBrand c on c.Id = a.BrandID" + " inner join Th_PurchaseRequisition on Th_PurchaseRequisition.id = b.PRID" + " inner join tblCategory on tblCategory.id = a.CategoryID" + " Where Th_PurchaseRequisition.id= " + id + " order by tblCategory.Name").ToList(); db.Close(); return(obj); }
public string NewPurchaseReq(List <Core.Domain.Purchase_Requisition.PurchaseRequisitionDetail> PurchaseItem, Core.Domain.Purchase_Requisition.PurchaseRequisition PurchaseHeader) { db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); string sql; //First Add Header int ID = 0; string NewPrNO = "PR-" + DateTime.Now.ToLongTimeString(); sql = " INSERT INTO TH_PurchaseRequisition(PRNo,PRDate,KitchenID,Remarks)VALUES ('" + NewPrNO + "',@PRDate,1,@Remarks) " + "Select Cast(SCOPE_IDENTITY() AS int)"; try { ID = db.Query <int>(sql, new { PurchaseHeader.PRDate, PurchaseHeader.Remarks }).SingleOrDefault(); } catch (Exception e) { if (e.Message.IndexOf("") > 0) { db.Close(); return("-1"); } } foreach (PurchaseRequisitionDetail obj in PurchaseItem) { sql = " Insert into TD_PurchaseRequisition (PRID,ItemID,Qty,Unit,Specs) " + " Values (" + ID + ",@ItemID,@Qty,@Unit,@Specs)"; db.Execute(sql, new { obj.ItemId, obj.Qty, obj.Unit, obj.Specs }); } return(NewPrNO); }
public DLCustomerDetails GetCustomerByID(int custID) { DLCustomerDetails customerDetails = new DLCustomerDetails(); con = connManager.GetConnection(); con.Open(); try { DynamicParameters param = new DynamicParameters(); param.Add("@CustomerID", custID); return(customerDetails = dbConnection.Query <DLCustomerDetails>(getCustomerByID, param, commandType: CommandType.StoredProcedure).SingleOrDefault()); } catch (Exception ex) { throw; } finally { con.Close(); } }
public int InsertBuilding(Core.Domain.Building model, ref string ErrorMsg) { int ID = 0; db = new SqlConnection(ConfigurationManager.ConnectionStrings["cnConsumption"].ConnectionString); string sql = " INSERT INTO tblBuilding(BuildingNo,BuildingName,TankID) " + " VALUES (@BuildingNo,@BuildingName,@TankID)" + " Select Cast(SCOPE_IDENTITY() AS int)"; try { ID = db.Query <int>(sql, new { model.BuildingNo, model.BuildingName, model.TankID }).SingleOrDefault(); } catch (Exception e) { if (e.Message.IndexOf("IX_tblItem-Name") > 0) { ErrorMsg = "Item Already Exist...."; } else { ErrorMsg = e.Message; } db.Close(); return(0); } model.ID = ID; db.Close(); return(ID); }
public IEnumerable <PriorityCode> FindPriorityCodeList() { return(_db.Query <PriorityCode>(listSQL)); }
public ICollection <UserId> FindUserIdList() { string runSQL = listSQL + " Order by AD_FULL_NAME"; return(_db.Query <UserId>(listSQL).ToList()); }
public ICollection <CommentType> FindTypeList() { return(_db.Query <CommentType>(listSQL).ToList()); }
public ICollection <LogComment> FindCommentList(string Log) { string getSQL = listSQL + " Where LOG_NUM = " + Log; return(_db.Query <LogComment>(getSQL).ToList()); }
public ICollection <StatusCode> FindStatusCodeList() { string runSQL = listSQL + " Order by Status"; return(_db.Query <StatusCode>(listSQL).ToList()); }