public static List <lineModel> LoadLine() { string sql = @"select lineId, lineName from [Line];"; return(SqlDataAccess.LoadData <lineModel>(sql)); }
public static List <EmployeeModel> LoadEmployees() { string sql = @"select Id, EmployeeId, FirstName, LastName, EmailAddress from dbo.Employee"; // This Return EmployeeModel <T> return(SqlDataAccess.LoadData <EmployeeModel>(sql)); }
public static List <ScheduleModel> LoadSchedule() { string sql = @"select orderId,partId,lineId,backendId,BEDate,EarliestStartDate, PlannedStartDate,LatestStartDate,SMTStart,SMTEnd from [Schedule] order by SMTStart;"; return(SqlDataAccess.LoadData <ScheduleModel>(sql)); }
public static List <int> CheckTable(string userId, string projectName) { string sql = $@"IF EXISTS( SELECT 1 FROM sys.tables WHERE name LIKE '{userId}@{projectName}') SELECT 1 ELSE SELECT 0"; return(SqlDataAccess.LoadData <int>(sql)); }
//For reading from the database public static List <AllBooks> LoadBooks() { //sql query for selecting (reading) from the book table string sql = @"select * from dbo.Books ORDER BY Title ASC;"; //Using function for reading from the book table return(SqlDataAccess.LoadData <AllBooks>(sql)); }
public static List <EmployeeModel> LoadEmployees() { //Load all employees with department name string sql = @"select E.Id, E.EmpName, E.EmpID, E.EmpContacts, E.EmpAge, D.DepName from dbo.[Employee] as E, dbo.[Department] as D where E.EmpDept = D.Id;"; return(SqlDataAccess.LoadData <EmployeeModel>(sql)); }
// this is the Business logic data access Employee Model public static List <EmployeeModel> LoadEmployees() { // this statement just returns everything string sql = @"select Id, EmployeeId, FirstName, LastName, EmailAddress from dbo.Employee;"; // load all our employees back return(SqlDataAccess.LoadData <EmployeeModel>(sql)); }
public static List <NFLHistory> LoadPickHistory(int week, string id) { string sql = @"SELECT G.HomeTeam, G.AwayTeam, G.Winner, U.GamePick FROM dbo.usrPicks as U, dbo.gamePicks as G WHERE U.GameKey = G.GameKey AND U.UserID ='" + id + "' AND U.Week = '" + week + "';"; return(SqlDataAccess.LoadData <NFLHistory>(sql)); }
//Methodto load data public static List <TagsModel> LoadTags() { //create the sql command string sql = @"SELECT Id, TagDescription, TagType, TimeStamp FROM dbo.Tags"; //call the sql data access to load the Tags data return(SqlDataAccess.LoadData <TagsModel>(sql)); }
//Methodto load data public static List <UserModel> LoadUser() { //create the sql command string sql = @"SELECT Id, UserName, FirstName, LastName FROM dbo.[User]"; //call the sql data access to load the User data return(SqlDataAccess.LoadData <UserModel>(sql)); }
public static List <RoommateModel> LoadRoommates() { //string sql = @"select RoommateId, FirstName, LastName // from dbo.Roommates;"; string sql = "sp_GetAllRoommates"; return(SqlDataAccess.LoadData <RoommateModel>(sql)); }
public static List <NFLLoadPickem> LoadPickem(string gKey, string user) { string sql = @"SELECT games.*, picks.* FROM dbo.gamePicks as games, dbo.usrPicks as picks WHERE games.GameKey ='" + gKey + "' AND picks.UserID ='" + user + "';"; return(SqlDataAccess.LoadData <NFLLoadPickem>(sql)); }
// Read Client Data public static List <ClientModel> LoadClient() { //Create SQL Query string sql = @"SELECT * FROM dbo.[Client];"; //load the data access to call the client data return(SqlDataAccess.LoadData <ClientModel>(sql)); }
// read employee data public static List <EmployeeModel> LoadEmployee() { //create SQL Query string sql = @"SELECT * FROM dbo.[Employee];"; // load the data access to call the employee data return(SqlDataAccess.LoadData <EmployeeModel>(sql)); }
public static List <ItemModel> GetBookById(int id) { var sql = "SELECT * FROM library_item " + "WHERE Category = 'book' " + $"AND Id = {id} " + $"AND Access = 'public'"; return(SqlDataAccess.LoadData <ItemModel>(sql)); }
//Methodto load data public static List <ProjectModel> LoadProjects() { //create the sql command string sql = @"select Id, Name, Description, DeadLine from dbo.Project;"; //call the sql data access to load the project data return(SqlDataAccess.LoadData <ProjectModel>(sql)); }
public static List <partModel> LoadPartId() { string sql = @"select partId from [Part];"; return(SqlDataAccess.LoadData <partModel>(sql)); }
//For reading only books with Genre Biography public static List <AllBooks> LoadBiography() { //sql query for reading only Biography books string sql = @"select * from dbo.Books where Genre = 'Biography';"; //Using functions to celect from the table with our query return(SqlDataAccess.LoadData <AllBooks>(sql)); }
//public static List<Province> LoadProvince(int countryId) //{ // string sql = @"dbo.Provinces_GetByCountryId @CountryId"; // var p = new DynamicParameters(); // p.Add("@CountryId", countryId); // return SqlDataAccess.LoadData<Province>(sql, p); //} public static List <Province> LoadProvince(string countryName) { string sql = @"dbo.Provinces_GetByCountryName @CountryName"; var p = new DynamicParameters(); p.Add("@CountryName", countryName); return(SqlDataAccess.LoadData <Province>(sql, p)); }
// Series public static List <SeriesModel> LoadSeries(string connectionString) { string sql = "SELECT * FROM series.view_ListSeries ORDER BY title;"; SqlDataAccess sqlDataAccess = new SqlDataAccess(); sqlDataAccess.GetConnectionString(connectionString); return(sqlDataAccess.LoadData <SeriesModel>(sql)); }
public static List <CommissionReportModel> LoadCommissionReport() { string sql = @"select p.ProductName, p.SalePrice, p.CommissionPercentage, (p.SalePrice * (p.CommissionPercentage/100)) as 'Commission', sp.FName as 'SalesPersonFName', sp.LName as 'SalesPersonLName', s.SaleDate from Products p join Sales s on s.ProductID=p.ProductID join SalesPerson sp on sp.SalesPersonID = s.SalesPersonID where s.SaleDate BETWEEN '4/1/2021' AND '6/30/2021' order by SalesPersonLName"; return(SqlDataAccess.LoadData <CommissionReportModel>(sql)); }
public static List <SalesListModel> LoadSales(DateTime beginDate, DateTime endDate) { string sql = @"select p.ProductName, p.Manufacturer, p.Style, c.FName as 'CustomerFName', c.LName as 'CustomerLName', s.SaleDate, p.SalePrice, sp.FName as 'SalesPersonFName', sp.LName as 'SalesPersonLName',(p.SalePrice * (p.CommissionPercentage/100)) as 'Commission' from Sales s join Customer c on s.CustomerID=c.CustomerID join Products p on s.ProductID=p.ProductID join SalesPerson sp on s.SalesPersonID=sp.SalesPersonID where s.SaleDate BETWEEN '" + beginDate + "' AND '" + endDate + "' "; return(SqlDataAccess.LoadData <SalesListModel>(sql)); }
public static List <orderModel> LoadOrder(string status) { orderModel data = new orderModel { status = status, }; string sql = @"select orderId,partId,projectName,lastMaterialDate,shipDate,quantity,status,priority from [Order] where status = @status;"; return(SqlDataAccess.LoadData <orderModel>(sql, data)); }
public static int get_id(int userid) { string sql = @"Select Member_ID from dbo.Member where User_ID = '" + userid + "'"; var result = SqlDataAccess.LoadData <MemberModel>(sql); foreach (var row in result) { return(row.Member_ID); } return(0); }
public static int get_id(string userName) { string sql = @"Select User_ID from dbo.Users where Username = '******'"; var result = SqlDataAccess.LoadData <UserModel>(sql); foreach (var row in result) { return(row.User_ID); } return(0); }
public static List <UsrSelections> UsrLosses(string userID, int week) { string sql = @"SELECT U.UserID, U.Week, U.GameKey, U.GamePick, G.Winner FROM dbo.usrPicks as U, dbo.gamePicks as G WHERE U.GameKey = G.GameKey AND U.GamePick <> '" + null + "' AND U.GamePick <> 'TIE' AND U.GamePick <> G.Winner AND U.UserID = '" + userID + "' AND U.Week = '" + week + "';"; return(SqlDataAccess.LoadData <UsrSelections>(sql)); }
public static int get_id(string studio_name) { string sql = @"Select Studio_ID from dbo.Studio_Details where Studio_Name = '" + studio_name + "'"; var result = SqlDataAccess.LoadData <StudioModel>(sql); foreach (var row in result) { return(row.Studio_ID); } return(0); }
public static int get_id_lname(string lname) { string sql = @"Select Cast_ID from dbo.Cast_Details where LastName = '" + lname + "'"; var result = SqlDataAccess.LoadData <CastModel>(sql); foreach (var row in result) { return(row.Cast_ID); } return(0); }
public static int get_id(string producer_name) { string sql = @"Select Producer_ID from dbo.Producer_Details where Producer_name = '" + producer_name + "'"; var result = SqlDataAccess.LoadData <ProducerModel>(sql); foreach (var row in result) { return(row.Producer_ID); } return(0); }
// Episodes public static List <EpisodeModel> LoadEpisodes(string connectionString, int id) { string sqlAllEpisodes = $"SELECT * FROM series.uf_list_all_episodes( { id } )"; SqlDataAccess sqlDataAccess = new SqlDataAccess(); sqlDataAccess.GetConnectionString(connectionString); List <EpisodeModel> episodeList = TransformEpisodeModel(sqlDataAccess.LoadData <EpisodeSqlModel>(sqlAllEpisodes)); return(episodeList); }