public static async Task <int> Count <T>() { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.GetType <T>()); if (!isOnline) { if (!DbException.CheckDatabaseConnection(conn)) { return(0); } int count = await conn.ExecuteScalarAsync <int>("select count(*) from " + typeof(T).Name); return(count); } else { if (dbCon.IsConnect()) { string query = "select count(*) from " + typeof(T).Name.ToLower() + "s"; var cmd = new MySqlCommand(query, dbCon.Connection); var count = cmd.ExecuteScalar(); dbCon.Close(); return(Convert.ToInt32(count)); } dbCon.Close(); return(0); } }
public static async Task <bool> Delete <T>(object item) { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.ToString(item)); if (!isOnline) { if (!DbException.CheckDatabaseConnection(conn)) { return(false); } await conn.DeleteAsync((T)item); } else { PropertyInfo field = ObjectHelper.GetField(item, "Id"); if (field == null) { return(false); } int id = Convert.ToInt32(field.GetValue(item, null)); string query = "DELETE FROM " + typeof(T).Name.ToLower() + "s WHERE Id=" + id; var cmdDelete = new MySqlCommand(query, dbCon.Connection); cmdDelete.ExecuteNonQuery(); dbCon.Close(); } return(true); }
public static async Task <bool> DeleteAll <T>() { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.GetType <T>()); if (!isOnline) { if (!DbException.CheckDatabaseConnection(conn)) { return(false); } await conn.DeleteAllAsync(typeof(T)); } else { if (dbCon.IsConnect()) { string query = "DELETE FROM " + typeof(T).Name.ToLower() + "s"; var cmdDelete = new MySqlCommand(query, dbCon.Connection); cmdDelete.ExecuteNonQuery(); } dbCon.Close(); } return(true); }
public static async Task <T> Get <T>(object value, string field = IdField) where T : class, new() { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.GetType <T>() + " where " + field + " = " + value); if (!isOnline) { if (!DbException.CheckDatabaseConnection(conn)) { return(null); } var item = Expression.Parameter(typeof(T), "item"); var prop = Expression.Property(item, field); var soap = Expression.Constant(value); var equal = Expression.Equal(prop, soap); var lambda = Expression.Lambda <Func <T, bool> >(equal, item); var query = await conn.Table <T>().Where(lambda).FirstOrDefaultAsync(); return(query); } if (dbCon.IsConnect()) { string query = "SELECT * FROM " + typeof(T).Name.ToLower() + "s WHERE " + field + " = '" + SqlHelper.Escape(value.ToString()) + "' LIMIT 1;"; var cmd = new MySqlCommand(query, dbCon.Connection); var reader = cmd.ExecuteReader(); PropertyInfo[] props = ObjectHelper.GetReflectionProperties(new T()); while (reader.Read()) { T obj = new T(); for (int i = 0; i < reader.FieldCount; i++) { try { PropertyInfo propertyInfo = obj.GetType().GetProperty(props[i].Name); propertyInfo.SetValue(obj, Convert.ChangeType(reader.GetValue(i), propertyInfo.PropertyType), null); } catch { continue; } } reader.Close(); dbCon.Close(); Debug.WriteLine(ObjectHelper.GetCaller() + " RESULT: " + ObjectHelper.ToString(obj)); return(obj); } reader.Close(); } dbCon.Close(); return(null); }
public static async Task <List <T> > GetAll <T>() where T : class, new() { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.GetType <T>()); if (isOnline) { if (dbCon.IsConnect()) { string query = "SELECT * FROM " + typeof(T).Name.ToLower() + "s;"; var cmd = new MySqlCommand(query, dbCon.Connection); var reader = cmd.ExecuteReader(); List <T> list = new List <T>(); PropertyInfo[] props = ObjectHelper.GetReflectionProperties(new T()); while (reader.Read()) { T obj = new T(); for (int i = 0; i < reader.FieldCount; i++) { try { PropertyInfo propertyInfo = obj.GetType().GetProperty(props[i].Name); propertyInfo.SetValue(obj, Convert.ChangeType(reader.GetValue(i), propertyInfo.PropertyType), null); } catch (Exception e) { Debug.WriteLine(ObjectHelper.GetCaller() + " ERROR : " + e.Message + " - " + props[i].Name); } } list.Add(obj); } reader.Close(); dbCon.Close(); foreach (var res in list) { Debug.WriteLine(ObjectHelper.GetCaller() + " RESULT: " + ObjectHelper.ToString(res)); } return(list); } dbCon.Close(); } else { if (!DbException.CheckDatabaseConnection(conn)) { return(null); } var query = conn.Table <T>(); return(await query.ToListAsync()); } return(null); }
public static async Task <bool> CreateTable <T>() { if (!DbException.CheckDatabaseConnection(conn)) { return(false); } await conn.CreateTablesAsync(typeof(T)); return(true); }
public static async Task <bool> Update <T>(object item) { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.ToString(item)); if (!DbException.CheckDatabaseConnection(conn)) { return(false); } if (!isOnline) { await conn.UpdateAsync((T)item); return(true); } if (dbCon.IsConnect()) { int id = -1; PropertyInfo[] props = ObjectHelper.GetReflectionProperties(item, true); string query = "UPDATE " + item.GetType().Name.ToLower() + "s SET "; for (int i = 0; i < props.Length; i++) { if (props[i].Name == "Id") { id = Convert.ToInt32(item.GetType().GetProperty(props[i].Name).GetValue(item, null)); continue; } string value = Convert.ToString(item.GetType().GetProperty(props[i].Name).GetValue(item, null)); if (typeof(DateTime).IsAssignableFrom(props[i].PropertyType)) { DateTime dateTime = (DateTime)item.GetType().GetProperty(props[i].Name).GetValue(item, null); value = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } query = query + props[i].Name + " = '" + SqlHelper.Escape(value) + "'"; if (i != props.Length - 1) { query = query + ", "; } } query = query + " WHERE Id=" + id; var cmd = new MySqlCommand(query, dbCon.Connection); cmd.ExecuteNonQuery(); } dbCon.Close(); return(true); }
public static async Task <bool> InsertIfNotExists <T>(object item, int id) where T : class { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.ToString(item) + " - id: " + id); if (!isOnline) { if (!DbException.CheckDatabaseConnection(conn)) { return(false); } } if (!await Exists <T>(id)) { await Insert(item); return(true); } return(false); }
public static async Task <bool> Exists <T>(object value, string field = IdField) where T : class { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.GetType <T>() + " where " + field + " = " + value); if (!isOnline) { if (!DbException.CheckDatabaseConnection(conn)) { return(false); } var item = Expression.Parameter(typeof(T), "item"); var prop = Expression.Property(item, field); var soap = Expression.Constant(value); var equal = Expression.Equal(prop, soap); var lambda = Expression.Lambda <Func <T, bool> >(equal, item); var queryOffline = await conn.Table <T>().Where(lambda).CountAsync(); return(queryOffline > 0); } if (dbCon.IsConnect()) { string query = "SELECT EXISTS(SELECT 1 FROM " + typeof(T).Name.ToLower() + "s WHERE " + field + " ='" + SqlHelper.Escape(value.ToString()) + "' LIMIT 1)"; var cmd = new MySqlCommand(query, dbCon.Connection); var result = cmd.ExecuteScalar(); if (result == null) { return(false); } dbCon.Close(); return(Convert.ToInt32(result) > 0); } dbCon.Close(); return(false); }
public static async Task <List <T> > GetAllContains <T>(object value, string field = IdField) where T : class, new() { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.GetType <T>() + " where " + field + " contains " + value); if (isOnline) { if (dbCon.IsConnect()) { string query = "SELECT * FROM " + typeof(T).Name.ToLower() + "s WHERE " + field + @" LIKE ""%" + value + @"%"";"; var cmd = new MySqlCommand(query, dbCon.Connection); var reader = cmd.ExecuteReader(); List <T> list = new List <T>(); PropertyInfo[] props = ObjectHelper.GetReflectionProperties(new T()); while (reader.Read()) { T obj = new T(); for (int i = 0; i < reader.FieldCount; i++) { try { PropertyInfo propertyInfo = obj.GetType().GetProperty(props[i].Name); propertyInfo.SetValue(obj, Convert.ChangeType(reader.GetValue(i), propertyInfo.PropertyType), null); } catch { continue; } } list.Add(obj); } reader.Close(); dbCon.Close(); foreach (var res in list) { Debug.WriteLine(ObjectHelper.GetCaller() + " RESULT: " + ObjectHelper.ToString(res)); } return(list); } dbCon.Close(); } else { if (!DbException.CheckDatabaseConnection(conn)) { return(null); } var item = Expression.Parameter(typeof(T), "item"); var prop = Expression.Property(item, field); MethodInfo method = typeof(string).GetMethod("Contains", new[] { typeof(string) }); var soap = Expression.Constant(value); var containsMethodExp = Expression.Call(prop, method, soap); var lambda = Expression.Lambda <Func <T, bool> >(containsMethodExp, item); var query = await conn.Table <T>().Where(lambda).ToListAsync(); return(query); } return(null); }
public static async Task <int> Insert(object item, bool skipId = true) { Debug.WriteLine(ObjectHelper.GetCaller() + ": " + ObjectHelper.ToString(item)); if (!isOnline) { if (!DbException.CheckDatabaseConnection(conn)) { return(0); } return(await conn.InsertAsync(item)); } else { if (dbCon.IsConnect()) { PropertyInfo[] props = ObjectHelper.GetReflectionProperties(item, true); string query = "INSERT INTO " + item.GetType().Name.ToLower() + "s ("; string queryEnd = ""; for (int i = 0; i < props.Length; i++) { if (props[i].Name == "Id" && skipId) { continue; } query = query + props[i].Name; string value = Convert.ToString(item.GetType().GetProperty(props[i].Name).GetValue(item, null)); if (typeof(DateTime).IsAssignableFrom(props[i].PropertyType)) { DateTime dateTime = (DateTime)item.GetType().GetProperty(props[i].Name).GetValue(item, null); value = dateTime.ToString("yyyy-MM-dd HH:mm:ss"); } if (typeof(bool).IsAssignableFrom(props[i].PropertyType)) { queryEnd = queryEnd + SqlHelper.Escape(value); } else { queryEnd = queryEnd + "'" + SqlHelper.Escape(value) + "'"; } if (i != props.Length - 1) { query = query + ", "; queryEnd = queryEnd + ", "; } else { query = query + ")"; queryEnd = queryEnd + ")"; } } query = query + " VALUES (" + queryEnd + ";SELECT LAST_INSERT_ID();"; var cmd = new MySqlCommand(query, dbCon.Connection); var newId = cmd.ExecuteScalar(); Debug.WriteLine("Inserted ID:" + newId); return(Convert.ToInt32(newId)); } dbCon.Close(); } return(0); }
public static bool CheckDatabaseConnection() { return(DbException.CheckDatabaseConnection(conn)); }