public void CreateTable()
{
try
{
//var db = new Repository(); //Stare repo.
var db = new RepositoryAdapter(DbPath); //Nowe repo.
List <string> sqlList = new List <string>();
sqlList.Add("CREATE TABLE[Customers](" +
" [ID] INTEGER PRIMARY KEY AUTOINCREMENT" +
", [Name] text NULL " +
", [LastName] text NULL " +
", [Number] integer NULL )");
sqlList.Add("CREATE TABLE[Services](" +
" [ID] INTEGER PRIMARY KEY AUTOINCREMENT" +
", [Name] text NULL " +
", [Type] text NULL " +
", [Price] integer NULL )");
sqlList.Add("CREATE TABLE[Transactions](" +
" [ID] INTEGER PRIMARY KEY AUTOINCREMENT" +
", [CustomerID] INTEGER NULL " +
", [ServiceID] INTEGER NULL " +
", [Date] text NULL " +
", [User] text NULL )");
//db.ExecuteNonQuery(DbPath, sqlList); // Stara metoda
db.ExecuteNonQuery(sqlList); // Nowa metoda
}
catch (Exception ex)
{
throw;
}
}
public List <string[]> Get(tables table, string where = "")
{
//var db = new Repository(); //Stare repo
var db = new RepositoryAdapter(_path); //Nowe repo
//var result = db.ExecuteReader(_path, $"SELECT * FROM [{table.ToString()}] {where}"); //Stara metoda.
var result = db.ExecuteReader($"SELECT * FROM [{table.ToString()}] {where}"); //Nowa metoda
switch (table)
{
case tables.Customers:
foreach (var item in result)
{
item[0] = $"ID: {item[0]}";
item[1] = $"Nazwa1: {item[1]}";
item[2] = $"Nazwa2: {item[2]}";
item[3] = $"Numer: {item[3]}";
}
break;
case tables.Services:
foreach (var item in result)
{
item[0] = $"ID: {item[0]}";
item[1] = $"Nazwa: {item[1]}";
item[2] = $"Typ: {item[2]}";
item[3] = $"Cena: {item[3]}";
}
break;
case tables.Transactions:
foreach (var item in result)
{
//string[] customer = db.ExecuteReader(_path, $"SELECT * FROM [{tables.Customers.ToString()}] where ID={item[1]}")[0];
//string[] service = db.ExecuteReader(_path, $"SELECT * FROM [{tables.Services.ToString()}] where ID={item[2]}")[0]; // Stare metody
string[] customer = db.ExecuteReader($"SELECT * FROM [{tables.Customers.ToString()}] where ID={item[1]}")[0];
string[] service = db.ExecuteReader($"SELECT * FROM [{tables.Services.ToString()}] where ID={item[2]}")[0]; // Nowe metody.
item[0] = $"ID: {item[0]}";
item[1] = $"Klient: {String.Join("*", customer)}";
item[2] = $"Usługa: {String.Join("*", service)}";
item[3] = $"Data: " + item[3];
item[4] = $"Kto: " + item[4];
}
break;
default:
break;
}
return(result);
}
public string Add(tables table, string[] one)
{
if (one[0] == "")
{
return("Nie dodano - nieprawidłowe dane!");
}
//var db = new Repository(); //Stare repo
var db = new RepositoryAdapter(_path); //Nowe repo
var sql = new List <string>();
switch (table)
{
case tables.Customers:
if (Get(table, $" where Name='{one[0]}' and LastName='{one[1]}' and Number={one[2]}").Count > 0)
{
return("Klient istnieje w bazie!");
}
sql.Add($"Insert into [{table.ToString()}] (Name, LastName, Number) Values ('{one[0]}','{one[1]}',{one[2]})");
//db.ExecuteNonQuery(_path, sql); //Stara metoda
db.ExecuteNonQuery(sql); //Nowa metoda
return("Dodano");
case tables.Services:
if (Get(table, $" where Name='{one[0]}' and Type='{one[1]}' and Price={one[2]}").Count > 0)
{
return("Usługa istnieje w bazie!");
}
sql.Add($"Insert into [{table.ToString()}] (Name, Type, Price) Values ('{one[0]}','{one[1]}',{one[2]})");
//db.ExecuteNonQuery(_path, sql); //Stara metoda
db.ExecuteNonQuery(sql); //Nowa metoda
return("Dodano");
case tables.Transactions:
if (Get(tables.Customers, $" where ID={one[0]}").Count > 0 && Get(tables.Services, $" where ID={one[1]}").Count > 0)
{
sql.Add($"Insert into [{table.ToString()}] (CustomerID, ServiceID, Date, User) Values ({one[0]},{one[1]},'{one[2]}','{one[3]}')");
//db.ExecuteNonQuery(_path, sql); //Stara metoda
db.ExecuteNonQuery(sql); //Nowa metoda
return("Dodano");
}
else
{
return("Nie dodano, błędne ID klienta lub usługi!");
}
default:
return("Nie dodano - błędny typ tabeli!");
}
}