public List <Item> GetItemsBySearchParam(string searchParam) { List <Item> itemList; ConfigurationBuilder DatabaseUsersBuilder = new ConfigurationBuilder(); DatabaseUsersBuilder.SetBasePath(Directory.GetCurrentDirectory()); DatabaseUsersBuilder.AddJsonFile("appsettings.json"); IConfiguration DatabaseUsersConfiguration = DatabaseUsersBuilder.Build(); SqlConnection BAIS3150 = new SqlConnection(); BAIS3150.ConnectionString = DatabaseUsersConfiguration.GetConnectionString("BAIS3150"); BAIS3150.Open(); SqlCommand ASampleCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = "SearchItems" }; SqlParameter ASampleCommandParameter = new SqlParameter { ParameterName = "@SearchParam", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, SqlValue = searchParam }; ASampleCommand.Parameters.Add(ASampleCommandParameter); SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); itemList = new List <Item>(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { while (ASampleDataReader.Read())// no value no read (returns true until no rows left to return) { Item item = new Item(); item.ItemCode = ASampleDataReader.GetValue("ItemCode").ToString(); item.ItemDescription = ASampleDataReader.GetValue("ItemDescription").ToString(); item.UnitPrice = decimal.Parse(ASampleDataReader.GetValue("UnitPrice").ToString()); item.QuantityOnHand = int.Parse(ASampleDataReader.GetValue("QuantityOnHand").ToString()); item.IsDeleted = bool.Parse(ASampleDataReader.GetValue("IsDeleted").ToString()); itemList.Add(item); } } } ASampleDataReader.Close(); BAIS3150.Close(); return(itemList); }
public ProgramName GetProgram(string programCode) { string user = "******"; string password = "******"; ProgramName program = new ProgramName(); SqlConnection BAIS3150 = new SqlConnection(); BAIS3150.ConnectionString = @$ "Persist Security Info=False;Database={user};User ID={user};Password={password};server=dev1.baist.ca;"; BAIS3150.Open(); SqlCommand GetProgramCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = "GetProgram" }; SqlParameter GetProgramParameter = new SqlParameter { ParameterName = "@ProgramCode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, SqlValue = programCode }; GetProgramCommand.Parameters.Add(GetProgramParameter); SqlDataReader ASampleDataReader; ASampleDataReader = GetProgramCommand.ExecuteReader(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); while (ASampleDataReader.Read()) // no value no read (returns true until no rows left to return) { for (int i = 0; i < ASampleDataReader.FieldCount; i++) { program.ProgramCode = programCode; program.Description = ASampleDataReader.GetValue("Description").ToString(); program.EnrolledStudents = GetStudentByProgramCode(programCode); } Console.WriteLine("-"); } } ASampleDataReader.Close(); BAIS3150.Close(); return(program); }
public List <Category> GetNorthwindCategories() { List <Category> CategoriesList; ConfigurationBuilder DatabaseUsersBuilder = new ConfigurationBuilder(); DatabaseUsersBuilder.SetBasePath(Directory.GetCurrentDirectory()); DatabaseUsersBuilder.AddJsonFile("appsettings.json"); IConfiguration DatabaseUsersConfiguration = DatabaseUsersBuilder.Build(); SqlConnection BAIS3150 = new SqlConnection(); BAIS3150.ConnectionString = DatabaseUsersConfiguration.GetConnectionString("BAIS3150"); SqlCommand ASampleCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = $"ihugya1.GetNorthwindCategories" }; SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); CategoriesList = new List <Category>(); if (ASampleDataReader.HasRows) { Console.WriteLine("\nColumns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.Write(ASampleDataReader.GetName(index) + " ,"); } Console.WriteLine("Values:"); Console.WriteLine("-------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { while (ASampleDataReader.Read())// no value no read (returns true until no rows left to return) { Category category = new Category(); category.CategoryName = ASampleDataReader.GetValue("CategoryName").ToString(); category.Description = ASampleDataReader.GetValue("Description").ToString(); category.Picture = ASampleDataReader.GetValue("Picture").ToString(); CategoriesList.Add(category); } } } ASampleDataReader.Close(); BAIS3150.Close(); return(CategoriesList); }
public ProgramName GetProgram(string programCode) { ProgramName program = new ProgramName(); string user, password; Console.Write("Please enter DB Name : "); user = Console.ReadLine(); Console.Write("Please enter DB Password : "******"ExecuteReadExample"); SqlConnection BAIS3150 = new SqlConnection(); //instantiation + declararation BAIS3150.ConnectionString = @$ "Persist Security Info=False;Database={user};User ID={user};Password={password};server=dev1.baist.ca;"; BAIS3150.Open(); SqlCommand ASampleCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = "GetPrograms" }; SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); while (ASampleDataReader.Read()) // no value no read (returns true until no rows left to return) { for (int i = 0; i < ASampleDataReader.FieldCount; i++) { program.ProgramCode = ASampleDataReader.GetValue("ProgramCode").ToString(); program.Description = ASampleDataReader.GetValue("Description").ToString(); } Console.WriteLine("-"); } } ASampleDataReader.Close(); BAIS3150.Close(); return(program); }
}//eoExecuteDeleteStudent static void ExecuteGetStudentByProgramCode(string user, string password) { Console.WriteLine("Execute Get Student By ProgramCode"); SqlConnection BAIS3150 = new SqlConnection(); //instantiation + declararation BAIS3150.ConnectionString = @$ "Persist Security Info=False;Database={user};User ID={user};Password={password};server=dev1.baist.ca;"; BAIS3150.Open(); SqlCommand ASampleCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = "GetStudentsByProgram" }; SqlParameter ASampleCommandParameter = new SqlParameter { ParameterName = "@ProgramCode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, SqlValue = "BAIST" }; ASampleCommand.Parameters.Add(ASampleCommandParameter); SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); while (ASampleDataReader.Read())// no value no read (returns true until no rows left to return) { for (int i = 0; i < ASampleDataReader.FieldCount; i++) { Console.WriteLine(ASampleDataReader.GetValue(i)); } Console.WriteLine("-"); } } ASampleDataReader.Close(); BAIS3150.Close(); }
public List <Customer> GetCustomerByParam(string searchParam) { List <Customer> customerList; ConfigurationBuilder DatabaseUsersBuilder = new ConfigurationBuilder(); DatabaseUsersBuilder.SetBasePath(Directory.GetCurrentDirectory()); DatabaseUsersBuilder.AddJsonFile("appsettings.json"); IConfiguration DatabaseUsersConfiguration = DatabaseUsersBuilder.Build(); SqlConnection BAIS3150 = new SqlConnection(); BAIS3150.ConnectionString = DatabaseUsersConfiguration.GetConnectionString("BAIS3150"); BAIS3150.Open(); SqlCommand ASampleCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = "SearchCustomers" }; SqlParameter ASampleCommandParameter = new SqlParameter { ParameterName = "@SearchParam", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, SqlValue = searchParam }; ASampleCommand.Parameters.Add(ASampleCommandParameter); SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); customerList = new List <Customer>(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { while (ASampleDataReader.Read())// no value no read (returns true until no rows left to return) { Customer customer = new Customer(); customer.CustomerID = int.Parse(ASampleDataReader.GetValue("CustomerID").ToString()); customer.CustomerName = ASampleDataReader.GetValue("CustomerName").ToString(); customer.Address = (ASampleDataReader.GetValue("Address").ToString()); customer.City = (ASampleDataReader.GetValue("City").ToString()); customer.Province = (ASampleDataReader.GetValue("Province").ToString()); customer.PostalCode = (ASampleDataReader.GetValue("PostalCode").ToString()); customerList.Add(customer); } } } ASampleDataReader.Close(); BAIS3150.Close(); return(customerList); }
public List <Student> GetStudentByProgramCode(string programCode) { List <Student> studentList; ConfigurationBuilder DatabaseUsersBuilder = new ConfigurationBuilder(); DatabaseUsersBuilder.SetBasePath(Directory.GetCurrentDirectory()); DatabaseUsersBuilder.AddJsonFile("appsettings.json"); IConfiguration DatabaseUsersConfiguration = DatabaseUsersBuilder.Build(); SqlConnection BAIS3150 = new SqlConnection(); BAIS3150.ConnectionString = DatabaseUsersConfiguration.GetConnectionString("BAIS3150"); BAIS3150.Open(); SqlCommand ASampleCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = "GetStudentsByProgram" }; SqlParameter ASampleCommandParameter = new SqlParameter { ParameterName = "@ProgramCode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, SqlValue = "BAIST" }; ASampleCommand.Parameters.Add(ASampleCommandParameter); SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); studentList = new List <Student>(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { while (ASampleDataReader.Read())// no value no read (returns true until no rows left to return) { Student student = new Student(); student.StudentID = ASampleDataReader.GetValue("StudentID").ToString(); student.FirstName = ASampleDataReader.GetValue("FirstName").ToString(); student.LastName = ASampleDataReader.GetValue("LastName").ToString(); student.Email = ASampleDataReader.GetValue("Email").ToString(); studentList.Add(student); Console.WriteLine($"{student.StudentID} {student.FirstName},{student.LastName},{student.Email}"); Console.WriteLine("-"); } } } ASampleDataReader.Close(); BAIS3150.Close(); return(studentList); }
public List <Student> GetStudentByProgramCode(string programCode) { List <Student> studentList; Console.WriteLine("Execute Get Student By ProgramCode"); string user, password; Console.Write("Please enter DB Name : "); user = Console.ReadLine(); Console.Write("Please enter DB Password : "******"Persist Security Info=False;Database={user};User ID={user};Password={password};server=dev1.baist.ca;"; BAIS3150.Open(); SqlCommand ASampleCommand = new SqlCommand { Connection = BAIS3150, CommandType = CommandType.StoredProcedure, CommandText = "GetStudentsByProgram" }; SqlParameter ASampleCommandParameter = new SqlParameter { ParameterName = "@ProgramCode", SqlDbType = SqlDbType.VarChar, Direction = ParameterDirection.Input, SqlValue = "BAIST" }; ASampleCommand.Parameters.Add(ASampleCommandParameter); SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); studentList = new List <Student>(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { while (ASampleDataReader.Read())// no value no read (returns true until no rows left to return) { Student student = new Student(); student.StudentID = ASampleDataReader.GetValue("StudentID").ToString(); student.FirstName = ASampleDataReader.GetValue("FirstName").ToString(); student.LastName = ASampleDataReader.GetValue("LastName").ToString(); student.Email = ASampleDataReader.GetValue("Email").ToString(); studentList.Add(student); Console.WriteLine($"{student.StudentID} {student.FirstName},{student.LastName},{student.Email}"); Console.WriteLine("-"); } } } ASampleDataReader.Close(); BAIS3150.Close(); return(studentList); }
public List <TeeTime> GetDailyTeeSheetByDay(DateTime searchParam, string user, string password) { List <TeeTime> itemList; Console.WriteLine("GetDailyTeeSheetByDay "); ConfigurationBuilder configurationBuilder = new ConfigurationBuilder(); configurationBuilder.SetBasePath(Directory.GetCurrentDirectory()); configurationBuilder.AddJsonFile("appsettings.json"); IConfiguration configuration = configurationBuilder.Build(); SqlConnection ClubBaistConnection = new SqlConnection(configuration.GetConnectionString("BAIS3230")); ClubBaistConnection.Open(); // SqlTransaction sqlTransaction = ClubBaistConnection.BeginTransaction(); SqlCommand ASampleCommand = new SqlCommand { Connection = ClubBaistConnection, CommandType = CommandType.StoredProcedure, CommandText = "GetDailyTeeSheet" }; SqlParameter ASampleCommandParameter2 = new SqlParameter { ParameterName = "@TeeSheetDay", SqlDbType = SqlDbType.Date, Direction = ParameterDirection.Input, SqlValue = searchParam.ToString("yyyy-MM-dd", System.Globalization.CultureInfo.InvariantCulture.DateTimeFormat) }; ASampleCommand.Parameters.Add(ASampleCommandParameter2); SqlDataReader ASampleDataReader; ASampleDataReader = ASampleCommand.ExecuteReader(); itemList = new List <TeeTime>(); if (ASampleDataReader.HasRows) { Console.WriteLine("Columns:"); Console.WriteLine("--------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { Console.WriteLine(ASampleDataReader.GetName(index)); } Console.WriteLine("Values:"); Console.WriteLine("-------"); for (int index = 0; index < ASampleDataReader.FieldCount; index++) { while (ASampleDataReader.Read())// no value no read (returns true until no rows left to return) { TeeTime item = new TeeTime(); item.TeeTimeDate = DateTime.Parse(ASampleDataReader.GetValue("TeeTimeDate").ToString()); item.TeeTimeTime = DateTime.Parse(ASampleDataReader.GetValue("TeeTimeTime").ToString()); item.TeeTimeDate = item.TeeTimeDate.Add(item.TeeTimeTime.TimeOfDay); item.MemberNumber = ASampleDataReader.GetValue("MemberNumber") as int? ?? default(int); item.NumPlayers = ASampleDataReader.GetValue("NumPlayers") as int? ?? default(int); item.NumCarts = ASampleDataReader.GetValue("NumCarts") as int? ?? default(int); item.EmployeeName = ASampleDataReader.GetValue("EmployeeName") as string; item.IsStandingTeeTime = ASampleDataReader.GetValue("IsStandingTeeTime") as bool? ?? default(bool); item.IsSpecialEvent = ASampleDataReader.GetValue("IsSpecialEvent") as bool? ?? default(bool); itemList.Add(item); } } } ASampleDataReader.Close(); ClubBaistConnection.Close(); return(itemList); }