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 Student GetStudent(string studentID)
{
Student student = new Student();
Console.WriteLine("Execute Get Student ");
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 = "GetStudent",
};
SqlParameter ASampleCommandParameter = new SqlParameter
{
ParameterName = "@StudentID",
SqlDbType = SqlDbType.VarChar,
Direction = ParameterDirection.Input,
SqlValue = studentID
};
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));
student.FirstName = ASampleDataReader.GetValue("FirstName").ToString();
student.LastName = ASampleDataReader.GetValue("LastName").ToString();
student.Email = ASampleDataReader.GetValue("Email").ToString();
}
}
BAIS3150.Close();
}
BAIS3150.Close();
return(student);
}
public Customer GetCustomer(string customerCode)
{
Customer customer = new Customer();
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 = "GetCustomer",
};
SqlParameter ASampleCommandParameter = new SqlParameter
{
ParameterName = "@CustomerID",
SqlDbType = SqlDbType.VarChar,
Direction = ParameterDirection.Input,
SqlValue = customerCode
};
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++)
{
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());
}
}
BAIS3150.Close();
}
BAIS3150.Close();
return(customer);
}
public Student GetStudent(string studentID)
{
Student student = new Student();
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 = "GetStudent",
};
SqlParameter ASampleCommandParameter = new SqlParameter
{
ParameterName = "@StudentID",
SqlDbType = SqlDbType.VarChar,
Direction = ParameterDirection.Input,
SqlValue = studentID
};
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));
student.FirstName = ASampleDataReader.GetValue("FirstName").ToString();
student.LastName = ASampleDataReader.GetValue("LastName").ToString();
student.Email = ASampleDataReader.GetValue("Email").ToString();
student.StudentID = ASampleDataReader.GetValue("StudentID").ToString();
}
}
BAIS3150.Close();
}
BAIS3150.Close();
return(student);
}
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 UCSUser GetStudent(string email)
{
UCSUser ucsuser = new UCSUser();
string user = "******";
string password = "******";
SqlConnection BAIS3150 = new SqlConnection();
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 = "GetUser",
};
SqlParameter ASampleCommandParameter = new SqlParameter
{
ParameterName = "@Email",
SqlDbType = SqlDbType.VarChar,
Direction = ParameterDirection.Input,
SqlValue = email
};
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));
ucsuser.Email = email;
ucsuser.UserName = (string)ASampleDataReader["UserName"];
ucsuser.HashPass = (string)ASampleDataReader["HashPass"];
ucsuser.SaltPass = (string)ASampleDataReader["SaltPass"];
ucsuser.Role = (string)ASampleDataReader["Role"];
ucsuser.Created = (DateTime)ASampleDataReader["Created"];
}
}
BAIS3150.Close();
}
BAIS3150.Close();
return(ucsuser);
}
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 <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);
}
public async Task <IActionResult> OnPost()
{
string uiEmail = Email;
string uiPassword = Password;
UCSUser ucsuser = new UCSUser();
string user = "******";
string password = "******";
SqlConnection BAIS3150 = new SqlConnection();
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 = "GetUser",
};
SqlParameter ASampleCommandParameter = new SqlParameter
{
ParameterName = "@Email",
SqlDbType = SqlDbType.VarChar,
Direction = ParameterDirection.Input,
SqlValue = uiEmail
};
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));
ucsuser.Email = uiEmail;
ucsuser.UserName = ASampleDataReader.GetValue("UserName").ToString();
ucsuser.HashPass = ASampleDataReader.GetValue("HashPass").ToString();
ucsuser.SaltPass = ASampleDataReader.GetValue("SaltPass").ToString();
ucsuser.Role = ASampleDataReader.GetValue("Role").ToString();
ucsuser.Created = DateTime.Parse(ASampleDataReader.GetValue("Created").ToString());
}
}
BAIS3150.Close();
}
BAIS3150.Close();
// Convert a C# string to a byte array
// byte[] bytes = Encoding.ASCII.GetBytes(ucsuser.HashPass);
// foreach (byte b in bytes)
// {
// Console.WriteLine(b);
// }
Message = $"${ucsuser.Role}{ucsuser.Email} {ucsuser.HashPass} :";
Console.WriteLine();
// Convert a C# string to a byte array
if (Email == ucsuser.Email)
{
if (CheckMatch(ucsuser.HashPass, uiPassword))
{
var claims = new List <Claim>
{
new Claim(ClaimTypes.Email, ucsuser.Email),
new Claim(ClaimTypes.Name, ucsuser.UserName)
};
var claimsIdentity = new ClaimsIdentity(claims, CookieAuthenticationDefaults.AuthenticationScheme);
claimsIdentity.AddClaim(new Claim(ClaimTypes.Role, ucsuser.Role));
AuthenticationProperties authProperties = new AuthenticationProperties
{
};
await HttpContext.SignInAsync(CookieAuthenticationDefaults.AuthenticationScheme, new ClaimsPrincipal(claimsIdentity), authProperties);
return(RedirectToPage("/Admin/Index"));
}
}
// Message = "Invalid Attempt";
return(Page());
}
