public void SaveOrUpdate(Party entity, BuddyBillerRepository db)
{
var sql = @"MERGE INTO Parties
USING (VALUES (@Id,@Type,@Name,@Email,@PhoneNumber,@Address,@Added_Date,@Added_By_Id,@IsActive)) AS
s(Id,Type,Name,Email,PhoneNumber,Address,Added_Date,Added_By_Id,IsActive)
ON Parties.id = s.id
WHEN MATCHED THEN
UPDATE
SET Type=s.Type
,Name=s.Name
,Email=s.Email
,PhoneNumber=s.PhoneNumber
,Address=s.Address
,Added_Date=s.Added_Date
,Added_By_Id=s.Added_By_Id
,IsActive=s.IsActive
WHEN NOT MATCHED THEN
INSERT (Type,Name,Email,PhoneNumber,Address,Added_Date,Added_By_Id,IsActive)
VALUES (s.Type,s.Name,s.Email,s.PhoneNumber,s.Address,s.Added_Date,s.Added_By_Id,s.IsActive);";
object[] parameters =
{
new SqlParameter("@id", entity.Id),
new SqlParameter("@Type", entity.Type),
new SqlParameter("@Name", entity.Name),
new SqlParameter("@Email", entity.Email),
new SqlParameter("@PhoneNumber", entity.PhoneNumber),
new SqlParameter("@Address", entity.Address),
new SqlParameter("@Added_Date", entity.Added_Date),
new SqlParameter("@Added_By_Id", '1'), // TO:DO extend to stamp user session id
new SqlParameter("@IsActive", entity.IsActive)
};
db.Database.ExecuteSqlCommand(sql, parameters);
}
public void SaveOrUpdate(Product entity, BuddyBillerRepository db)
{
var sql = @"MERGE INTO Products
USING (VALUES (@Id,@Name,@Description,@Rate,@Qty,@Added_Date,@ProductType_Id,@Added_By_Id,@IsActive)) AS
s(Id,Name,Description,Rate,Qty,Added_Date,ProductType_Id,Added_By_Id,IsActive)
ON Products.id = s.id
WHEN MATCHED THEN
UPDATE
SET Name=s.Name
,Description=s.Description
,Rate=s.Rate
,Qty=s.Qty
,Added_Date=s.Added_Date
,ProductType_Id=s.ProductType_Id
,Added_By_Id=s.Added_By_Id
,IsActive=s.IsActive
WHEN NOT MATCHED THEN
INSERT (Name,Description,Rate,Qty,Added_Date,ProductType_Id,Added_By_Id,IsActive)
VALUES (s.Name,s.Description,s.Rate,s.Qty,s.Added_Date,s.ProductType_Id,s.Added_By_Id,s.IsActive);";
object[] parameters =
{
new SqlParameter("@Id", entity.Id),
new SqlParameter("@Name", entity.Name),
new SqlParameter("@Description", entity.Description),
new SqlParameter("@Rate", entity.Rate),
new SqlParameter("@Qty", entity.Qty),
new SqlParameter("@Added_Date", entity.Added_Date),
new SqlParameter("@ProductType_Id", entity.ProductType.Id),
new SqlParameter("@Added_By_Id", '1'), // TO:DO extend to stamp user session id
new SqlParameter("@IsActive", entity.IsActive)
};
db.Database.ExecuteSqlCommand(sql, parameters);
}
public bool loginCheck(LoginBll l)
{
//Create a boolean variable and set its value to false and return it
bool isSuccess = false;
try
{
using (BuddyBillerRepository db = new BuddyBillerRepository())
{
var usercontext = from user in db.Users
where user.username.Equals(l.Username) &&
user.password.Equals(l.Password) &&
user.user_type.Equals(l.UserType)
select user;
if (usercontext.Any())
{
//Login Sucessful
isSuccess = true;
}
}
// //SQL Query to check login
//string sql = "SELECT * FROM tbl_users WHERE username=@username AND password=@password AND user_type=@user_type";
////Creating SQL Command to pass value
//SqlCommand cmd = new SqlCommand(sql, conn);
////cmd.Parameters.AddWithValue("@username", l.username);
////cmd.Parameters.AddWithValue("@password", l.password);
////cmd.Parameters.AddWithValue("@user_type", l.user_type);
//SqlDataAdapter adapter = new SqlDataAdapter(cmd);
//DataTable dt = new DataTable();
//adapter.Fill(dt);
////Checking The rows in DataTable
//if(dt.Rows.Count>0)
//{
// //Login Sucessful
// isSuccess = true;
//}
//else
//{
// //Login Failed
// isSuccess = false;
//}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
return(isSuccess);
}
public static SqlConnection RepositoryConnectionBuilder()
{
string myconnstrng = ConfigurationManager.ConnectionStrings["temp"].ConnectionString;
SqlConnection conn = new SqlConnection(myconnstrng);
BuddyBillerRepository db = new BuddyBillerRepository();
return(conn);
}
