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); } }