public IActionResult AddSP(Roles roles)
{
var zAParam = new ZAParam("RoleName", $"{roles.RoleName}", AllSQLType.VarChar, 150);
var zABase = new ZABase("[dbo].[Add_Role]", AllCmdType.SP, zAParam);
TempData["LastRoleIdAdded"] = ZADB_.Post <IEnumerable <Roles> >(zABase).FirstOrDefault()?.RoleId;
TempData["IsSuccess"] = TempData["LastRoleIdAdded"] != null;
return(RedirectToAction("Index"));
}
public IActionResult RmvSP(Roles roles)
{
var zAParam = new ZAParam("RoleId", roles.RoleId, AllSQLType.Guid);
var zABase = new ZABase("[dbo].[Rmv_Role]", AllCmdType.SP, zAParam);
TempData["LastRoleIdAdded"] = ZADB_.Post <IEnumerable <Roles> >(zABase).FirstOrDefault()?.RoleId;
TempData["IsSuccess"] = TempData["LastRoleIdAdded"] == null;
return(RedirectToAction("Index"));
}
public IActionResult Upd(Roles roles)
{
var cmd = $"UPDATE [Roles] SET [RoleName] = '{roles.RoleName}', [Available] = '{roles.Available}' WHERE [RoleId] = '{roles.RoleId}';" +
$"SELECT [RoleId] FROM [Roles] WHERE [RoleId] = '{roles.RoleId}' ORDER BY [RoleName] DESC;";
var zABase = new ZABase(cmd, AllCmdType.TSql);
TempData["LastRoleIdAdded"] = ZADB_.Post <IEnumerable <Roles> >(zABase).FirstOrDefault()?.RoleId;
TempData["IsSuccess"] = TempData["LastRoleIdAdded"] != null;
return(RedirectToAction("Index"));
}
public IActionResult Add(Roles roles)
{
// Add new role with T-SQL
var cmd = $"INSERT INTO [Roles]([RoleName]) VALUES('{roles.RoleName}');" +
$"SELECT [RoleId] FROM [Roles] WHERE [RoleName] = '{roles.RoleName}' ORDER BY [RoleName] DESC;";
var zABase = new ZABase(cmd, AllCmdType.TSql);
TempData["LastRoleIdAdded"] = ZADB_.Post <IEnumerable <Roles> >(zABase).FirstOrDefault()?.RoleId;
TempData["IsSuccess"] = TempData["LastRoleIdAdded"] != null;
return(RedirectToAction("Index"));
}
public IActionResult UpdSP(Roles roles)
{
var zAParam = new List <ZAParam>()
{
new ZAParam("RoleId", roles.RoleId, AllSQLType.Guid),
new ZAParam("RoleName", roles.RoleName, AllSQLType.VarChar, 150),
new ZAParam("Available", roles.Available, AllSQLType.Bit)
};
var zABase = new ZABase("[dbo].[Upd_Role]", AllCmdType.SP, zAParam);
TempData["LastRoleIdAdded"] = ZADB_.Post <IEnumerable <Roles> >(zABase).FirstOrDefault()?.RoleId;
TempData["IsSuccess"] = TempData["LastRoleIdAdded"] != null;
return(RedirectToAction("Index"));
}
private static void OnlySP(ZADB _db, AllCmdType _cmdType = AllCmdType.SP)
{
#region Insert and show field.
// You able to put all into ZADBBase field but is more easier to read for separate
object zAParam = new List <ZAParam>()
{
new ZAParam("UserName", "Test", AllSQLType.VarChar, 50),
new ZAParam("Password", "Test".ToHash(), AllSQLType.VarChar, 64),
new ZAParam("NameFull", "Test", AllSQLType.VarChar, 200),
new ZAParam("Email", "*****@*****.**", AllSQLType.VarChar, 150)
};
var zABase = new ZABase("[Add_User]", _cmdType, zAParam);
var userInsert = _db.Post <IEnumerable <Users> >(zABase);
ShowData("userInsert - SP", userInsert); // Show all data.
#endregion
#region Select * or all fields.
zABase = new ZABase("[Get_AllUser]", _cmdType);
var userSelectAll = _db.Post <IEnumerable <Users> >(zABase);
ShowData("userSelectAll - SP", userSelectAll); // Show all data.
#endregion
#region Select with some fields.
var UserId = userSelectAll.FirstOrDefault(x => x.UserName == "Test").UserId;
zAParam = new ZAParam("UserId", UserId, AllSQLType.Guid);
zABase = new ZABase("[Get_UserById]", _cmdType, zAParam);
var userSelectExact = _db.Post <IEnumerable <Users> >(zABase);
ShowData("userSelectExact - SP", userSelectExact); // Show all data.
#endregion
#region update and show field.
zAParam = new List <ZAParam>()
{
new ZAParam("UserName", "Test1", AllSQLType.VarChar, 50),
new ZAParam("Email", "*****@*****.**", AllSQLType.VarChar, 150)
};
zABase = new ZABase("[Upd_User]", _cmdType, zAParam);
var userUpdate = _db.Post <IEnumerable <Users> >(zABase);
ShowData("userUpdate - SP", userUpdate); // Show all data.
#endregion
#region Delete and show field.
zAParam = new ZAParam("UserName", "Test1", AllSQLType.VarChar, 50);
zABase = new ZABase("[Rmv_User]", _cmdType, zAParam);
var userDelete = _db.Post <IEnumerable <Users> >(zABase);
ShowData("userDelete - SP", userDelete); // Show all data.
#endregion
}
private static void OnlyTSQL(ZADB _db, AllCmdType _cmdType = AllCmdType.TSql)
{
#region Insert and show field.
// You able to put all into ZADBBase field but is more easier to read for separate
var password = "******".ToHash();
var cmd = "INSERT INTO [Users]([UserName],[Password],[NameFull],[Email]) VALUES('Test', '" + password + "', 'Test ZAORM', '*****@*****.**');";
var zABase = new ZABase(cmd, _cmdType);
//var userInsert = _db.Post(zABase);
//ShowData("user Insert - TSQL\nResult:", userInsert); // Show all data.
#endregion
#region Select * or all fields.
// I put top(10) for make the test more faster.
zABase = new ZABase("SELECT TOP(10) [UserId],[UserName],[Password],[NameFull],[Email],[Available] FROM [Users]", _cmdType);
var userSelectAll = _db.Post <IEnumerable <Users> >(zABase);
ShowData("userSelectAll - TSQL", userSelectAll); // Show all data.
#endregion
#region Select with some fields.
// I put top(10) for make the test more faster.
zABase = new ZABase("SELECT TOP(10) [UserId],[UserName],[NameFull],[Email] FROM [Users] WHERE [Available] = 1", _cmdType);
var userSelectExact = _db.Post <IEnumerable <Users> >(zABase);
ShowData("userSelectExact - TSQL", userSelectExact); // Show all data.
#endregion
#region update and show field.
// You able to put all into ZADBBase field but is more easier to read for separate
cmd = "UPDATE [Users] SET [UserName] = 'test1' WHERE [UserName] = 'Test'";
zABase = new ZABase(cmd, _cmdType);
var userUpdate = _db.Post(zABase);
ShowData("user Update - TSQL\nResult:", userUpdate); // Show all data.
#endregion
#region Delete and show field.
// You able to put all into ZADBBase field but is more easier to read for separate
cmd = "DELETE FROM [Users] WHERE [UserName] = 'test1'";
zABase = new ZABase(cmd, _cmdType);
var userDelete = _db.Post(zABase);
ShowData("userDelete - TSQL", userDelete); // Show all data.
#endregion
}
public IActionResult Index()
{
// Get all with T-SQL.
vMRoles_.GetAll = ZADB_.Post <IEnumerable <Roles> >(new ZABase("SELECT [RoleId], [RoleName], [Available] FROM [Roles] ORDER BY [RoleName] DESC;", AllCmdType.TSql));
// Get all roles by stored procedure.
var zABase = new ZABase("[dbo].[Get_AllRoles]", AllCmdType.SP);
vMRoles_.GetBySP = ZADB_.Post <IEnumerable <Roles> >(zABase);
// Get only row with T-SQL.
var role = vMRoles_.GetAll.FirstOrDefault();
if (role != null)
{
zABase = new ZABase($"SELECT [RoleId], [RoleName], [Available] FROM [Roles] WHERE [RoleId] = '{role.RoleId}' ORDER BY [RoleName] DESC;", AllCmdType.TSql);
vMRoles_.GetWithWhere = ZADB_.Post <IEnumerable <Roles> >(zABase).FirstOrDefault();
}
else
{
vMRoles_.GetWithWhere = new Roles();
}
return(View(vMRoles_));
}
