public List <DogModel> Get_DogsInactiveAndActive()
{
List <DogModel> output = new List <DogModel>();
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
output = connection.Query <DogModel>("SELECT * FROM Dog ;").ToList();
foreach (DogModel dogModel in output)
{
dogModel.Characteristics = connection.Query <CharacteristicsModel>("SELECT c.* FROM characteristics c INNER JOIN dog_to_characteristics dc on dc.id = c.id WHERE active = 1 AND dogId = " + dogModel.Id).ToList();
dogModel.Diseases = connection.Query <DiseasesModel>("SELECT d.* FROM diseases d INNER JOIN dog_to_diseases dd on dd.id = d.id WHERE active = 1 AND dogId = " + dogModel.Id).ToList();
if (dogModel.CustomerList == null)
{
dogModel.CustomerList = new List <CustomerModel>();
}
dogModel.CustomerList = connection.Query <CustomerModel>("SELECT c.* FROM customer c INNER JOIN customer_to_dog cd on customerId = c.id WHERE dogId = " + dogModel.Id).ToList();
}
}
return(output);
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
return(new List <DogModel>());
}
}
public DogModel AddDogToDatabase(DogModel dModel)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
dModel.Id = connection.Query <int>(@"INSERT INTO Dog (name, breed, color, gender, birthday,permanentcastrated, castratedsince, effectiveuntil, create_date, edit_date, active) VALUES(@Name, @Breed, @Color, @Gender, @Birthday, @PermanentCastrated, @CastratedSince, @EffectiveUntil, datetime('now'), null, 1 ); SELECT last_insert_rowid();", dModel).First();
if (dModel.Diseases != null && dModel.Diseases.Count > 0)
{
foreach (DiseasesModel disModel in dModel.Diseases)
{
disModel.Id = connection.Query <int>(@"INSERT INTO diseases (name, active) VALUES(@name, 1); SELECt last_insert_rowid()", disModel).First();
connection.Query("INSERT INTO dog_to_diseases (dogId, diseasesId) VALUES(" + dModel.Id + " ," + disModel.Id + ")");
}
}
if (dModel.Characteristics != null && dModel.Characteristics.Count > 0)
{
foreach (CharacteristicsModel chaModel in dModel.Characteristics)
{
chaModel.Id = connection.Query <int>(@"INSERT INTO characteristics (description, active) VALUES (@Description, 1); SELECT last_insert_rowid()", chaModel).First();
connection.Query("INSERT INTO dog_to_characteristics (dogId, characteristicsId) VALUES(" + dModel.Id + " , " + chaModel.Id + ")");
}
}
}
return(dModel);
}
public List <DogModel> SearchResultDogs(string searchText, bool activeAndInactive)
{
List <DogModel> results = new List <DogModel>();
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
if (activeAndInactive)
{
results = connection.Query <DogModel>($"SELECT * FROM dog WHERE name like '%{searchText}%' OR breed like '%{searchText}%' OR " +
$"color like '%{searchText}%' OR gender like '%{searchText}%' OR birthday like '%{searchText}%'").ToList();
}
else
{
results = connection.Query <DogModel>($"SELECT * FROM dog WHERE (name like '%{searchText}%' OR breed like '%{searchText}%' OR " +
$"color like '%{searchText}%' OR gender like '%{searchText}%' OR birthday like '%{searchText}%') AND active = 1").ToList();
}
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
return(results);
}
public List <UserModel> SearchResultUser(string searchText, bool showInactive)
{
List <UserModel> output = new List <UserModel>();
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
if (showInactive)
{
output = connection.Query <UserModel>($"SELECT * FROM user WHERE username like '%{searchText}%'").ToList();
}
else
{
output = connection.Query <UserModel>($"SELECT * FROM user WHERE username like '%{searchText}%' AND isactive = 1").ToList();
}
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
return(output);
}
public List <ProductModel> SearchResultProducts(string searchText, bool showInactive)
{
List <ProductModel> output = new List <ProductModel>();
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
if (showInactive == true)
{
output = connection.Query <ProductModel>($"SELECT * FROM product WHERE shortdescription like '%{searchText}%' or longdescription like '%{searchText}%' or itemnumber like '%{searchText}%'").ToList();
}
else
{
output = connection.Query <ProductModel>($"SELECT * FROM product WHERE shortdescription like '%{searchText}%' AND active = 1 or longdescription like '%{searchText}%' AND active = 1 or itemnumber like '%{searchText}%' AND active = 1").ToList();
}
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
return(output);
}
public List <CustomerModel> SearchResultsCustomer(string searchText, bool activeAndInactive)
{
List <CustomerModel> results = new List <CustomerModel>();
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
if (activeAndInactive)
{
results = connection.Query <CustomerModel>($"SELECT * FROM customer where firstname like '%{searchText}%' OR lastname like '%{searchText}%' OR Street like '%{searchText}%'" +
$"OR housenumber like '%{searchText}%' OR zipcode like '%{searchText}%' OR city like '%{searchText}%' OR phonenumber like '%{searchText}%' OR " +
$"mobilenumber like '%{searchText}%' OR email like '%{searchText}%' or birthday like '%{searchText}%'").ToList();
}
else
{
results = connection.Query <CustomerModel>($"SELECT * FROM customer where (firstname like '%{searchText}%' OR lastname like '%{searchText}%' OR Street like '%{searchText}%'" +
$"OR housenumber like '%{searchText}%' OR zipcode like '%{searchText}%' OR city like '%{searchText}%' OR phonenumber like '%{searchText}%' OR " +
$"mobilenumber like '%{searchText}%' OR email like '%{searchText}%' or birthday like '%{searchText}%') AND active = 1").ToList();
}
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
return(results);
}
public CustomerModel AddCustomer(CustomerModel customerModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
customerModel.Id = connection.Query <int>(@"INSERT INTO Customer (salution, firstname, lastname, street,
housenumber, zipcode, city, phonenumber, mobilenumber, email,
birthday, create_date, edit_date,active) VALUES(
@Salution, @FirstName, @LastName, @Street, @Housenumber, @ZipCode,
@City, @PhoneNumber, @MobileNumber, @Email, @Birthday, datetime('now'),
null, 1); SELECT last_insert_rowid()", customerModel).First();
if (customerModel.Notes != null && customerModel.Notes.Count > 0)
{
foreach (NoteModel nModel in customerModel.Notes)
{
nModel.Id = connection.Query <int>(@"INSERT INTO note (description, active) VALUES (@Description, 1); SELECT last_insert_rowid()", nModel).First();
connection.Execute("INSERT INTO note_to_customer (customerId, noteId) Values(" + customerModel.Id + ", " + nModel.Id + ")");
}
}
if (customerModel.OwnedDogs != null && customerModel.OwnedDogs.Count > 0)
{
foreach (DogModel dModel in customerModel.OwnedDogs)
{
if (dModel.Id > 0)
{
AddDogToCustomer(dModel, customerModel);
}
else
{
AddDogToDatabase(dModel);
AddDogToCustomer(dModel, customerModel);
}
}
}
}
return(customerModel);
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
return(null);
}
}
public void TestDapper()
{
var dataSource = @"test.sqlite";
SQLiteConnection.CreateFile(dataSource);
var builder = new SQLiteConnectionStringBuilder
{
DataSource = dataSource,
LegacyFormat = false,
Version = 3,
SyncMode = SynchronizationModes.Off,
JournalMode = SQLiteJournalModeEnum.Wal
};
using (var tran = new TransactionScope(TransactionScopeOption.Required))
using (var connection = new SQLiteConnection(builder.ToString()))
{
connection.Open();
CreateSampleSchema(connection);
try
{
connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 1, Name = "Name-1" });
connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 2, Name = "Name-2" });
connection.Execute("INSERT INTO table1 VALUES (@Id, @Name)", new { Id = 3, Name = "Name-3" });
dynamic dynamicObj = connection.Query("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();
Console.WriteLine("Id: {0}", dynamicObj.id);
Console.WriteLine("Name: {0}", dynamicObj.name);
var mappingObj = connection.Query<Table1>("SELECT id, name FROM table1 LIMIT 1").FirstOrDefault();
Console.WriteLine("Id: {0}", mappingObj.Id);
Console.WriteLine("Name: {0}", mappingObj.Name);
dynamic results = connection.Query("SELECT id, name FROM table1");
foreach (dynamic item in results)
{
Console.WriteLine(item);
}
tran.Complete();
}
catch
{
}
}
}
public IEnumerable<Inventory> AvailableReefers()
{
var connection = new SQLiteConnection(dbPath);
connection.Open();
var Reefers = connection.Query<Inventory>("select * from inventory where status != 'major' and type == 'reefer' and reserved == 0");
return Reefers;
}
public IToken Find(string userId)
{
if (string.IsNullOrWhiteSpace(userId))
return null;
using (var connection = new SQLiteConnection(ConnectionString))
{
connection.Open();
var token = connection.Query<Token>(
@"SELECT UserId, OrganisationId, ConsumerKey, ConsumerSecret, TokenKey, TokenSecret, ExpiresAt, Session, SessionExpiresAt
FROM tokens
WHERE UserId = @UserId",
new
{
userId
}).FirstOrDefault();
if (null != token && token.ExpiresAt.HasValue)
{
// This is done because SQLite seems to be storing it as local time.
token.ExpiresAt = token.ExpiresAt.Value.ToUniversalTime();
}
return token;
}
}
public IEnumerable<String> CustomerNames()
{
var connection = new SQLiteConnection(dbPath);
connection.Open();
var names = connection.Query<String>("select distinct customer_name from reservations");
return names;
}
public void AddDogToCustomer(DogModel dModel, CustomerModel cModel)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query("INSERT INTO customer_to_dog (customerId, dogId) VALUES (" + cModel.Id + " ," + dModel.Id + ")");
}
}
public void DeleteAppointmentModel(AppointmentModel appointmentModel)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query($"UPDATE appointment SET isActive = 0 WHERE '{appointmentModel.Id}' = id ");
}
}
public IEnumerable<Inventory> AvailableDryVans()
{
var connection = new SQLiteConnection(dbPath);
connection.Open();
var DryVans = connection.Query<Inventory>("select * from inventory where status != 'major' and type == 'dry_van' and reserved == 0");
return DryVans;
}
public IEnumerable<Reservations> CustomerReservations()
{
var connection = new SQLiteConnection(dbPath);
connection.Open();
var reservationcounts = connection.Query<Reservations>("select * from reservations order by");
return reservationcounts;
}
public IEnumerable<Inventory> AvailableFlatBeds()
{
var connection = new SQLiteConnection(dbPath);
connection.Open();
var FlatBeds = connection.Query<Inventory>("select * from inventory where status != 'major' and type == 'flatbed' and reserved == 0");
return FlatBeds;
}
public IEnumerable<Inventory> AllReservations()
{
var connection = new SQLiteConnection(dbPath);
connection.Open();
var reservations = connection.Query<Inventory>("select * from inventory");
return reservations;
}
public ProfileViewModel GetDepartmentEmployees(string departmentName)
{
IEnumerable<EmployeeProfile> employees;
using (var conn =new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
var departmentId = conn.Query<long>("SELECT Id from Department_Lookup where Name = @name LIMIT 1", new {name = departmentName.Capitalize()});
employees = conn.Query<EmployeeProfile>("SELECT dl.Id, dl.Name, ProfileId, FirstName, LastName, Role, Department, ImageUrl, CartoonUrl, Tags, Extension " +
" FROM employeeprofile e inner join department_lookup dl on e.department = dl.id" +
" group by dl.Id, dl.Name, ProfileId, FirstName, LastName, Role, Department, ImageUrl, Tags " +
" Having dl.Id = @departmentId ",
new { departmentId = departmentId});
}
return new ProfileViewModel(employees.LoadHelpers());
}
public IEnumerable<Log> GetLogs(DateTime? from = null)
{
var date = from.HasValue ? from.Value : DateTime.MinValue;
using (var connection = new SQLiteConnection(ConnectionString))
{
var logs = connection.Query<Log>("select * from Log where date > @date", date);
return logs;
}
}
private static IEnumerable<MajorCity> QueryCities(string query, object paramaters = null)
{
using (IDbConnection con = new SQLiteConnection("Data Source=MajorCities.db"))
{
con.Open();
return con.Query<MajorCity>(query, paramaters);
}
}
public ProductModel AddProductToDataStore(ProductModel productModel)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
productModel.ItemNumber = connection.Query <int>($"INSERT INTO product (shortdescription, longdescription, price, active, create_date, edit_date) VALUES ('{productModel.Shortdescription}', '{productModel.Longdescription}', '{productModel.Price}', {productModel.Active}, datetime('now'), null); SELECT last_insert_rowid();", productModel).First();
return(productModel);
}
}
public static IEnumerable<EmployeeProfile> LoadHelpers(this IEnumerable<EmployeeProfile> profiles)
{
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
foreach (var profile in profiles)
{
profile.ImageUrl = LoadDefaultProfileImage(profile);
profile.RoleLookup = conn.Query<Role_Lookup>(CreateQuery(profileRoleQuery, "@RoleId"), new { RoleId = profile.Role }).SingleOrDefault();
profile.SeatingFloorLookup = conn.Query<SeatingFloor_Lookup>(CreateQuery(profileFloorQuery, "@FloorId"), new { FloorId = profile.SeatingFloor }).SingleOrDefault();
profile.DepartmentLookup = conn.Query<Department_Lookup>(CreateQuery(profileDepartmentQuery, "@DepartmentId"), new { DepartmentId = profile.Department }).SingleOrDefault();
}
conn.Close();
}
return profiles;
}
public AppointmentModel AddAppointmentToDataStore(AppointmentModel appointmentModel)
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
{
appointmentModel.Id = connection.Query <int>($"INSERT INTO appointment (dogID, date_from, date_to, isdailyguest, days, create_date, isActive) VALUES ('{appointmentModel.dogFromCustomer.Id}','{appointmentModel.date_from}','{appointmentModel.date_to}','{appointmentModel.isdailyguest}','{appointmentModel.days}', datetime('now'), 1); SELECT last_insert_rowid();", appointmentModel).First();
}
}
return(appointmentModel);
}
public static EmployeeProfile LoadHelper(this EmployeeProfile profile)
{
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
if (profile != null)
{
conn.Open();
profile.ImageUrl = LoadDefaultProfileImage(profile);
profile.RoleLookup = conn.Query<Role_Lookup>(CreateQuery(profileRoleQuery, "@RoleId"), new { RoleId = profile.Role }).SingleOrDefault();
profile.SeatingFloorLookup = conn.Query<SeatingFloor_Lookup>(CreateQuery(profileFloorQuery, "@FloorId"), new { FloorId = profile.SeatingFloor}).SingleOrDefault();
profile.DepartmentLookup = conn.Query<Department_Lookup>(CreateQuery(profileDepartmentQuery, "@DepartmentId"), new { DepartmentId = profile.Department }).SingleOrDefault();
conn.Close();
}
}
return profile;
}
/*
public static Choices GetGrammar(string contextname = null)
{
if (String.IsNullOrEmpty(contextname))
{
var commands = new Choices();
using (SQLiteConnection sqlite = new SQLiteConnection(GlobalManager.SQLCHAIN))
{
List<SENTENCES> sentence = sqlite.Query<SENTENCES>("SELECT * from SENTENCES").ToList();
foreach (SENTENCES sen in sentence)
{
sen.CMD = sqlite.Query<COMMANDS>(String.Format("SELECT * FROM COMMANDS where COMMANDS.ID in (select CMDID from TRIGGERCMD where SENID = {0})", sen.SENID)).Single();
commands.Add(new SemanticResultValue(sen.SENTENCE, sen.CMD.CMD));
}
}
return commands;
}
return null;
}*/
public static Context GetContext(string ContextName = null)
{
SYSCONTEXT = new Context();
using (SQLiteConnection sqlite = new SQLiteConnection(GlobalManager.SQLCHAIN))
{
string sql = "select * from SENTENCES INNER JOIN COMMANDS on COMMANDS.ID in (select CMDID from TRIGGERCMD where SENTENCES.SENID = TRIGGERCMD.SENID) INNER JOIN MODULES on MODULES.NAME = COMMANDS.MODULENAME";
SYSCONTEXT.SENTENCESLIST = new List<SENTENCES>();
SYSCONTEXT.SENTENCESLIST = sqlite.Query<SENTENCES, COMMANDS, MODULES, SENTENCES>(sql, (sentence, command, module) => { command.MODULE = module; sentence.CMD = command; return sentence; }).ToList<SENTENCES>();
}
return SYSCONTEXT;
}
public HttpResponseMessage GetPersons()
{
var dbpath = System.Web.Hosting.HostingEnvironment.MapPath("~/App_Data");
var dbfile = dbpath + "/people.db3";
using (var conn = new SQLiteConnection("Data Source=" + dbfile))
{
var people = conn.Query<Person>("select * from person");
return Request.CreateResponse(HttpStatusCode.OK, people);
}
}
public Department_Lookup GetDepartment(int deptId)
{
Department_Lookup department;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
department = conn.Query<Department_Lookup>("SELECT Id, Name, Parent_Id FROM Department_Lookup where Id = @DeptId ", new { DeptId = deptId }).SingleOrDefault();
conn.Close();
}
return department;
}
public IEnumerable<SeatingFloor_Lookup> GetAllSeatingFloors()
{
IEnumerable<SeatingFloor_Lookup> floors;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
floors = conn.Query<SeatingFloor_Lookup>("SELECT Id, Name FROM Floor_Lookup order by Id");
conn.Close();
}
return floors;
}
public Department_Lookup GetDepartment(string departmentname)
{
Department_Lookup department;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
department = conn.Query<Department_Lookup>("SELECT Id, Name, Parent_Id FROM Department_Lookup where Name = @Name ", new { Name = departmentname.Capitalize() }).SingleOrDefault();
conn.Close();
}
return department;
}
public List <CustomerModel> Get_CustomerAll()
{
List <CustomerModel> output;
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
output = connection.Query <CustomerModel>("SELECT * FROM CUSTOMER WHERE active = 1;").ToList();
foreach (CustomerModel customerModel in output)
{
customerModel.Notes = connection.Query <NoteModel>("SELECT n.* FROM note n INNER JOIN note_to_customer nc on noteId = n.id WHERE active = 1 AND customerId = " + customerModel.Id).ToList();
customerModel.OwnedDogs = connection.Query <DogModel>("SELECT d.* FROM dog d INNER JOIN customer_to_dog cd on dogId = d.id WHERE customerId = " + customerModel.Id).ToList();
foreach (DogModel dogModel in customerModel.OwnedDogs)
{
dogModel.Characteristics = connection.Query <CharacteristicsModel>("SELECT c.* FROM characteristics c INNER JOIN dog_to_characteristics dc on dc.id = c.id WHERE dogId = " + dogModel.Id).ToList();
dogModel.Diseases = connection.Query <DiseasesModel>("SELECT d.* FROM diseases d INNER JOIN dog_to_diseases dd on dd.id = d.id WHERE dogId = " + dogModel.Id).ToList();
dogModel.CustomerList = connection.Query <CustomerModel>("SELECT c.* FROM customer c INNER JOIN customer_to_dog cd on customerId = c.id WHERE dogId = " + dogModel.Id).ToList();
}
}
}
return(output);
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
return(new List <CustomerModel>());
}
}
public IContent Lookup(string token)
{
string connString = ConfigurationManager.ConnectionStrings["WilliamsonFamilyConnectionString"].ConnectionString;
//var connection = new ProfiledDbConnection(new SQLiteConnection(connString), MiniProfiler.Current);
using (var conn = new SQLiteConnection(connString))
{
conn.Open();
var content = conn.Query<Content>("SELECT * FROM Content WHERE Token = @token", new { @token = token }, null, true, null, null).FirstOrDefault();
conn.Close();
return content;
}
}
public NoteModel AddNoteToCustomer(CustomerModel customerModel, string note)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
NoteModel nModel = new NoteModel();
nModel.Description = note;
nModel.Id = connection.Query <int>("INSERT INTO note (description, active) Values('" + note + "', 1); SELECT last_insert_rowid();").First();
connection.Query("UPDATE customer SET edit_date = datetime('now') WHERE customer.id= " + customerModel.Id);
connection.Execute("INSERT INTO note_to_customer (customerId, noteId) Values(" + customerModel.Id + ", " + nModel.Id + ")");
customerModel.Notes = connection.Query <NoteModel>("SELECT n.* FROM note n INNER JOIN note_to_customer nc on noteId = n.id WHERE active = 1 AND customerId = " + customerModel.Id).ToList();
return(nModel);
}
}
catch (SQLiteException sqLiteEx)
{
Console.WriteLine(sqLiteEx.Message);
return(null);
}
}
public ProfileViewModel GetEmployeesByFloor()
{
IEnumerable<EmployeeProfile> profiles;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
profiles = conn.Query<EmployeeProfile>("SELECT ProfileId, firstName, LastName, ImageUrl, seatNo, seatingfloor, role, department " +
" FROM employeeprofile" +
" order by seatingfloor, seatNo");
conn.Close();
}
return new ProfileViewModel(profiles.LoadHelpers());
}
public void UpdateUser(UserModel userModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query($" UPDATE user SET edit_date = datetime('now'), isactive = {userModel.IsActive}, username = '******' , password = '******', isadmin = {userModel.IsAdmin} WHERE '{userModel.Id}' = id");
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
}
public void DeleteDiseases(DiseasesModel diseasesModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query("DELETE FROM diseases WHERE id = " + diseasesModel.Id);
}
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
}
}
public ProfileViewModel GetNewStarters()
{
IEnumerable<EmployeeProfile> profiles;
using (DbConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["reedDirectory"].ConnectionString))
{
conn.Open();
profiles = conn.Query<EmployeeProfile>("SELECT ProfileId, FirstName, LastName, Role, Department, ImageUrl, CartoonUrl, Tags, CreatedOn, Extension " +
" FROM employeeprofile" +
" where createdon >= date('now', '-1 month')");
conn.Close();
}
return new ProfileViewModel(profiles.LoadHelpers());
}
public void InsertUserToDatabase(UserModel userModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query(@"INSERT INTO user (username, password, isadmin,isactive,create_date) VALUES(@Username, @Password, @IsAdmin,1, datetime('now') );", userModel);
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
}
public void DeleteCharacteristics(CharacteristicsModel characteristicsModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query("DELETE FROM characteristics WHERE id = " + characteristicsModel.Id);
}
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
}
}
public void EditAppointmentModel(AppointmentModel appointmentModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query($"UPDATE appointment SET edit_date = datetime('now'), dogID='{appointmentModel.dogID}', date_from='{appointmentModel.date_from}', date_to='{appointmentModel.date_to}', isdailyguest='{appointmentModel.isdailyguest}', days='{appointmentModel.days}', isActive=1 WHERE '{appointmentModel.Id}' = id ");
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
}
public void DeleteUserFromDataBase(UserModel userModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query($" UPDATE user SET edit_date = datetime('now'), isactive = 0 WHERE {userModel.Id} = id");
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
}
/// <summary>
/// Load SYSTEM Context
/// </summary>
private static void LoadSysContext()
{
using (SQLiteConnection sqlite = new SQLiteConnection(GlobalManager.SQLCHAIN))
{
string sql = "select * from SENTENCES INNER JOIN COMMANDS on COMMANDS.ID in (select CMDID from TRIGGERCMD where SENTENCES.SENID = TRIGGERCMD.SENID)AND COMMANDS.MODULENAME = 'SYSTEM' INNER JOIN MODULES on MODULES.NAME = COMMANDS.MODULENAME";
SYSCONTEXT.SENTENCESLIST = sqlite.Query<SENTENCES, COMMANDS, MODULES, SENTENCES>
(sql, (sentence, command, module) => {
command.MODULE = module;
sentence.CMD = command;
return sentence;
}).ToList<SENTENCES>();
SYSCONTEXT.NAME = "System";
}
}
public void UpdateProduct(ProductModel productModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query($"UPDATE product SET edit_date = datetime('now'), shortdescription = '{productModel.Shortdescription}', longdescription = '{productModel.Longdescription}', price = '{productModel.Price}', active = {productModel.Active} WHERE '{productModel.ItemNumber}' = itemnumber ");
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
}
static void Main(string[] args)
{
string inputFilePath = args[0];
var inputFile = new FileInfo(inputFilePath);
var directory = inputFile.Directory;
var outputFilePath = Path.Combine(directory.FullName, "data.txt");
SQLiteConnection connection = new SQLiteConnection("Data Source=" + inputFilePath);
var data = connection.Query("select * from person");
var serializeObject = JsonConvert.SerializeObject(data);
var byteArray = Encoding.UTF8.GetBytes(serializeObject);
var result = Convert.ToBase64String(byteArray);
var resultByte = Encoding.UTF8.GetBytes(result);
// File.WriteAllText(outputFilePath, result, Encoding.UTF8);
FileStream fileStream = new FileStream(outputFilePath, FileMode.Create);
fileStream.Write(resultByte, 0, resultByte.Length);
fileStream.Close();
fileStream = new FileStream(outputFilePath, FileMode.Open);
int count = 0;
long length = fileStream.Length;
byte[] receiveStream = new byte[length];
while (true)
{
int readLength = fileStream.Length - count > 1000 ? 1000 : (int)fileStream.Length - count;
int num = fileStream.Read(receiveStream, count, readLength);
if (num == 0) break;
count += num;
}
var receive1 = Encoding.UTF8.GetString(receiveStream, 0, (int)length);
var byteValue1 = Convert.FromBase64String(receive1);
string decodeReceive1 = Encoding.UTF8.GetString(byteValue1, 0, byteValue1.Length);
if (decodeReceive1 == serializeObject)
{
Console.WriteLine("Convert complete.");
Console.ReadLine();
}
fileStream.Close();
}
public void DeleteDogToCustomerRelation(CustomerModel cModel, DogModel dModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query("DELETE FROM customer_to_dog WHERE customerId = " + cModel.Id + " AND dogId = " + dModel.Id);
UpdateDog(dModel);
}
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
}
}
public void DeleteCustomer(CustomerModel customerModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query("UPDATE Customer SET edit_date = datetime('now'), active = 0 WHERE id= " + customerModel.Id);
}
}
catch (SQLiteException sqLiteEx)
{
Console.WriteLine(sqLiteEx.Message);
return;
}
}
public void DeleteProductFromDataStore(ProductModel productModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query("UPDATE product SET edit_date = datetime('now'), active = 0 WHERE itemnumber = " + productModel.ItemNumber);
}
}
catch (SQLiteException sqLiteEx)
{
Console.WriteLine(sqLiteEx.Message);
return;
}
}
public void DeleteNoteFromList(NoteModel noteModel, CustomerModel customerModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query("UPDATE note SET active = 0 WHERE note.id = " + noteModel.Id + ";");
Get_Customer(customerModel);
UpdateCustomer(customerModel);
}
}
catch (SQLiteException sqLiteEx)
{
Console.WriteLine(sqLiteEx.Message);
}
}
public override Cookie GetCookie(string domain, string name)
{
var cookieFilePath = Path.Combine(GetUserDataDirectoryPath(), "Cookies");
var connString = string.Format(@"Data Source={0}", cookieFilePath);
using (var conn = new SQLiteConnection(connString)) {
conn.Open();
var query = string.Format("select * from cookies where host_key like '.{0}' and name like '{1}' limit 1", domain, name);
var row = conn.Query(query).FirstOrDefault();
if (row == null) {
return null;
}
var cookie = new Cookie(row.name, row.value, row.path, row.host_key);
conn.Close();
return cookie;
}
}
public List <CustomerModel> GetAllCustomerForDog(DogModel dogModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
dogModel.CustomerList = connection.Query <CustomerModel>(@"SELECT c.* FROM customer c INNER JOIN customer_to_dog cd on customerId = c.id WHERE dogId = @id", dogModel).ToList();
return(dogModel.CustomerList);
}
}
catch (SQLiteException ex)
{
Console.WriteLine(ex.Message);
return(new List <CustomerModel>());
}
}
public void UpdateDog(DogModel dModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query(@"UPDATE Dog SET edit_date = datetime('now'), name = @Name, breed = @Breed, color = @Color, gender = @Gender, birthday = @Birthday,
permanentcastrated = @PermanentCastrated,
castratedsince = @CastratedSince, effectiveuntil = @EffectiveUntil, active = @Active WHERE id = @Id", dModel);
}
}
catch (SQLiteException sqLiteEx)
{
Console.WriteLine(sqLiteEx.Message + "\r\n" + sqLiteEx.StackTrace);
}
}
public void UpdateCustomer(CustomerModel cModel)
{
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
connection.Query(@"UPDATE Customer SET edit_date = datetime('now'),firstname = @Firstname, lastname = @LastName, street = @Street, birthday = @Birthday,
housenumber = @HouseNumber, zipcode = @ZipCode, city = @City, phonenumber = @PhoneNumber, mobilenumber = @MobileNumber, email = @Email,
active = @Active WHERE id = @Id", cModel);
}
}
catch (SQLiteException sqLiteEx)
{
Console.WriteLine(sqLiteEx.Message);
}
}
public List <UserModel> GetAllUser()
{
List <UserModel> output = new List <UserModel>();
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
output = connection.Query <UserModel>("SELECT * FROM user").ToList();
}
}
catch (SQLiteException sqEx)
{
Console.WriteLine(sqEx.Message);
}
return(output);
}
public bool IsAppointmentInDataStore(AppointmentModel appointmentModel)
{
bool isInDatabase = false;
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
List <AppointmentModel> AppointmentModelList = new List <AppointmentModel>();
AppointmentModelList = connection.Query <AppointmentModel>($"Select * FROM appointment WHERE '{appointmentModel.dogFromCustomer.Id}' = dogID AND " +
$"date_from = '{appointmentModel.date_from}' AND " +
$"date_to = '{appointmentModel.date_to}' AND isActive= '1' ").ToList();
if (AppointmentModelList.Count >= 1)
{
isInDatabase = true;
}
}
return(isInDatabase);
}
public Stream GetCache(Uri uri)
{
// compute hash
var hash = Utils.ComputeStringMD5Hash(uri.AbsoluteUri, Encoding.UTF8);
var selectByHashSql = SqlLibrary.Instance.Require("SELECT_INDEX_BY_HASH");
using (var connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", _sqliteFilePath)))
{
try
{
var cacheIndex = connection
.Query<CacheIndex>(selectByHashSql, new { @Hash = hash })
.FirstOrDefault();
if(cacheIndex == null)
{
return null;
}
var cacheFilePath = GetCacheFilePhysicalPath(cacheIndex.Path);
if(!File.Exists(cacheFilePath))
{
return null;
}
var mem = new MemoryStream();
using (var fs = new FileStream(cacheFilePath, FileMode.Open))
{
fs.CopyTo(mem);
mem.Flush();
mem.Position = 0;
}
return mem;
} catch (Exception ex) {
// TODO: log the excpetion here
return null;
}
}
}
public DogModel GetDog(int id)
{
DogModel dogModel = new DogModel();
try
{
using (IDbConnection connection = new System.Data.SQLite.SQLiteConnection(GlobalConfig.CnnString(db)))
{
dogModel = connection.Query <DogModel>($"SELECT * FROM Dog WHERE id = {id};").First();
}
}
catch (SQLiteException e)
{
Console.WriteLine(e.Message);
}
return(dogModel);
}
public void SqlLiteTableChanged(ListBox listBox, UiShow uiShow)
{
IEnumerable<object> enumerable;
using (SQLiteConnection connection = new SQLiteConnection(SqLiteConnStr.ToString()))
{
connection.Open();
string sql = $"PRAGMA table_info({listBox.SelectedItem})";
enumerable = connection.Query(sql, null, null, true, null, null);
}
StringBuilder builder = new StringBuilder();
builder.AppendFormat("public class {0}{1}{{{1}", listBox.SelectedItem, Environment.NewLine);
foreach (dynamic obj2 in enumerable)
{
string columnName = (string)obj2.name;
bool isNullable = (bool)(obj2.notnull == 0L);
string dataType = (string)obj2.type;
builder.AppendLine(GenerateField(columnName, isNullable, dataType));
}
uiShow.txtClass.Text = builder.Append("}").ToString();
}
public void ShowSqLiteTables(UiShow uiShow, string path)
{
if (uiShow != null)
{
SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
{
DataSource = path,
};
SqLiteConnStr = builder;
using (SQLiteConnection connection = new SQLiteConnection(SqLiteConnStr.ToString()))
{
connection.Open();
uiShow.lbTableName.Items.Clear();
IEnumerable<object> enumerable =
connection.Query("SELECT tbl_name FROM sqlite_master WHERE TYPE='table' ORDER BY name ASC;",
null, null, true, null, null);
uiShow.lbTableName.Items.AddRange(
(from d in enumerable select ((dynamic)d).tbl_name).ToArray<object>());
}
}
}
public void Set(IContent content)
{
string connString = ConfigurationManager.ConnectionStrings["WilliamsonFamilyConnectionString"].ConnectionString;
//var connection = new ProfiledDbConnection(new SQLiteConnection(connString), MiniProfiler.Current);
using (var conn = new SQLiteConnection(connString))
{
conn.Open();
var contentExists = conn.Query<Content>("SELECT * FROM Content WHERE Token = @token", new { @token = content.Token }, null, true, null, null).FirstOrDefault();
if (contentExists == null)
conn.Execute("INSERT INTO Content (Token, Value) VALUES (@Token, @Value)", new { @token = content.Token, @value = content.Value }, null, null, null);
else
conn.Execute(@"
UPDATE Content
SET Token = @Token,
Value = @Value
WHERE ContentID = @ContentID", new { @token = content.Token, @value = content.Value, @ContentID = content.ContentID }, null, null, null);
conn.Close();
}
}
public bool IsCached(Uri uri)
{
var hash = Utils.ComputeStringMD5Hash(uri.AbsoluteUri, Encoding.UTF8);
var selectByHashSql = SqlLibrary.Instance.Require("SELECT_INDEX_BY_HASH");
using (var connection = new SQLiteConnection(string.Format("Data Source={0};Version=3;", _sqliteFilePath)))
{
var cacheIndex = connection
.Query<CacheIndex>(selectByHashSql, new { @Hash = hash })
.FirstOrDefault();
if(cacheIndex == null)
{
return false;
}
var cacheFilePath = GetCacheFilePhysicalPath(cacheIndex.Path);
return File.Exists(cacheFilePath);
}
}
public override void SetCookie(string domain, Cookie cookie)
{
var insertQuery = string.Format("insert into cookies values({0}, '.{1}', '{2}', '{3}', '/', {4}, 0, 0, {5}, 1, 1",
DateTime.UtcNow.ToFileTimeUtc(),
domain,
cookie.Name,
cookie.Value,
DateTime.UtcNow.AddYears(1).ToFileTimeUtc(),
DateTime.UtcNow.ToFileTimeUtc()
);
var updateQuery = string.Format("update cookies set `value` = '{0}' where host_key like '.{1}' and name like '{2}';",
cookie.Value, domain, cookie.Name);
var query = (GetCookie(domain, cookie.Name) == null) ? insertQuery : updateQuery;
var cookieFilePath = Path.Combine(GetUserDataDirectoryPath(), "Cookies");
using (var conn = new SQLiteConnection(string.Format(@"Data Source={0}", cookieFilePath))) {
conn.Open();
conn.Query(query);
conn.Close();
}
}