public static Organisation GetOrganisationByID(int id)
{
Organisation org = new Organisation();
string sql = "SELECT * FROM Organisations WHERE ID=@ID";
DbParameter parID = Database.AddParameter(Database.ADMIN_DB, "@ID", id);
DbDataReader reader = Database.GetData(Database.GetConnection(Database.ADMIN_DB), sql, parID);
reader.Read();
org = Create(reader);
reader.Close();
return org;
}
public static int InsertOrganisation(Organisation org)
{
string sql = "INSERT INTO Organisations VALUES(@Login,@Password,@DbName,@DbLogin,@DbPassword,@OrganisationName,@Address,@Email,@Phone)";
DbParameter par1 = Database.AddParameter(Database.ADMIN_DB, "@Login", org.Login);
DbParameter par2 = Database.AddParameter(Database.ADMIN_DB, "@Password", org.Password);
DbParameter par3 = Database.AddParameter(Database.ADMIN_DB, "@DbName", org.DbName);
DbParameter par4 = Database.AddParameter(Database.ADMIN_DB, "@DbLogin", org.DbLogin);
DbParameter par5 = Database.AddParameter(Database.ADMIN_DB, "@DbPassword", org.DbPassword);
DbParameter par6 = Database.AddParameter(Database.ADMIN_DB, "@OrganisationName", org.OrganisationName);
DbParameter par7 = Database.AddParameter(Database.ADMIN_DB, "@Address", org.Address);
DbParameter par8 = Database.AddParameter(Database.ADMIN_DB, "@Email", org.Email);
DbParameter par9 = Database.AddParameter(Database.ADMIN_DB, "@Phone", org.Phone);
return Database.InsertData(Database.GetConnection(Database.ADMIN_DB), sql, par1, par2, par3, par4, par5, par6, par7, par8, par9);
}
// PUT api/<controller>/5
public int Put(Organisation org)
{
return OrganisationDA.UpdateOrganisation(org);
}
// POST api/<controller>
public int Post(Organisation org)
{
return OrganisationDA.InsertOrganisation(org);
}
public static int UpdateOrganisation(Organisation org)
{
string sql = "UPDATE Organisations SET Login=@Login, Password=@Password, DbName=@DbName, DbLogin=@DbLogin, DbPassword=@DbPassword, OrganisationName=@OrganisationName, Address=@Address, Email=@Email, Phone=@Phone WHERE ID=@ID";
DbParameter par1 = Database.AddParameter(Database.ADMIN_DB, "@Login", org.Login);
DbParameter par2 = Database.AddParameter(Database.ADMIN_DB, "@Password", org.Password);
DbParameter par3 = Database.AddParameter(Database.ADMIN_DB, "@DbName", org.DbName);
DbParameter par4 = Database.AddParameter(Database.ADMIN_DB, "@DbLogin", org.DbLogin);
DbParameter par5 = Database.AddParameter(Database.ADMIN_DB, "@DbPassword", org.DbPassword);
DbParameter par6 = Database.AddParameter(Database.ADMIN_DB, "@OrganisationName", org.OrganisationName);
DbParameter par7 = Database.AddParameter(Database.ADMIN_DB, "@Address", org.Address);
DbParameter par8 = Database.AddParameter(Database.ADMIN_DB, "@Email", org.Email);
DbParameter par9 = Database.AddParameter(Database.ADMIN_DB, "@Phone", org.Phone);
DbParameter parID = Database.AddParameter(Database.ADMIN_DB, "@ID", org.ID);
return Database.ModifyData(Database.GetConnection(Database.ADMIN_DB), sql, par1, par2, par3, par4, par5, par6, par7, par8, par9, parID);
}
private static void CreateDatabase(Organisation o)
{
// create the actual database
//string create = File.ReadAllText(HostingEnvironment.MapPath(@"~/App_Data/create.txt")); only for the web
string create = File.ReadAllText(@"..\..\Data\create.txt"); // only for desktop
string sql = create.Replace("@@DbName", o.DbName).Replace("@@DbLogin", o.DbLogin).Replace("@@DbPassword", o.DbPassword);
foreach (string commandText in RemoveGo(sql))
{
Database.ModifyData(Database.GetConnection(Database.ADMIN_DB), commandText);
}
// create login, user and tables
DbTransaction trans = null;
try
{
trans = Database.BeginTransaction(Database.ADMIN_DB);
//string fill = File.ReadAllText(HostingEnvironment.MapPath(@"~/App_Data/fill.txt")); // only for the web
string fill = File.ReadAllText(@"..\..\Data\fill.txt"); // only for desktop
string sql2 = fill.Replace("@@DbName", o.DbName).Replace("@@DbLogin", o.DbLogin).Replace("@@DbPassword", o.DbPassword);
foreach (string commandText in RemoveGo(sql2))
{
Database.ModifyData(trans, commandText);
}
trans.Commit();
}
catch (Exception ex)
{
trans.Rollback();
Console.WriteLine(ex.Message);
}
}