/// <summary> /// Queries the specified where. /// </summary> /// <param name="where">The where.</param> /// <returns>List<User>.</returns> public static List <Authority> Query(string where) { List <Authority> list = new List <Authority>(); Sqlite sqlite = new Sqlite(); string text = "SELECT A.id,A.name,R.id,R.name FROM auth A,role R WHERE A.roleid=R.id "; if (!string.IsNullOrEmpty(where)) { text = text + " AND " + where; } SQLiteDataReader reader = sqlite.Query(text); while (reader.Read()) { Authority auth = new Authority(); auth.Id = reader.GetInt32(0); auth.Name = reader.GetString(1); Role role = new Role(); role.Id = reader.GetInt32(2); role.Name = reader.GetString(3); auth.Role = role; list.Add(auth); } sqlite.Close(); return(list); }
/// <summary> /// Queries the specified where. /// </summary> /// <param name="where">The where.</param> /// <returns>List<User>.</returns> public static List <User> Query(string where) { List <User> list = new List <User>(); Sqlite sqlite = new Sqlite(); string text = "SELECT U.ID,U.USERNAME,U.PASSWORD,R.ID,R.NAME FROM user U,role R WHERE R.ID=U.ROLEID "; if (!string.IsNullOrEmpty(where)) { text = text + " AND " + where; } SQLiteDataReader sQLiteDataReader = sqlite.Query(text); while (sQLiteDataReader.Read()) { User user = new User(); user.Id = sQLiteDataReader.GetInt32(0); user.UserName = sQLiteDataReader.GetString(1); user.Password = sQLiteDataReader.GetString(2); Role role = new Role(); role.Id = sQLiteDataReader.GetInt32(3); role.Name = sQLiteDataReader.GetString(4); user.Role = role; user.RoleId = role.Id; list.Add(user); } sqlite.Close(); return(list); }
public static List <object[]> Query(DateTime?startDate, DateTime?endDate, string user) { StringBuilder stringBuilder = new StringBuilder("select * from log "); ArrayList arrayList = new ArrayList(); if (!string.IsNullOrEmpty(user)) { arrayList.Add("user = '******'"); } DateTime dateTime; if (startDate.HasValue && startDate.HasValue) { ArrayList arrayList2 = arrayList; dateTime = startDate.Value; arrayList2.Add("logtime > datetime('" + dateTime.ToString(ConstDef.shortDate) + "')"); } if (endDate.HasValue && endDate.HasValue) { ArrayList arrayList3 = arrayList; dateTime = endDate.Value; arrayList3.Add("logtime < datetime('" + dateTime.ToString(ConstDef.shortDate) + "')"); } string text = ""; if (arrayList.Count > 0) { string[] value = (string[])arrayList.ToArray(typeof(string)); text = string.Join(" and ", value); } if (!string.IsNullOrEmpty(text)) { stringBuilder.Append(" where " + text); } stringBuilder.Append(" order by logtime desc"); List <object[]> list = new List <object[]>(); Sqlite sqliteHelper = new Sqlite(); SQLiteDataReader sQLiteDataReader = sqliteHelper.Query(stringBuilder.ToString()); while (sQLiteDataReader.Read()) { object[] array = new object[5] { sQLiteDataReader.GetString(0), sQLiteDataReader.GetString(4), sQLiteDataReader.GetString(3), sQLiteDataReader.GetString(2), sQLiteDataReader.GetDateTime(1) }; list.Add(array); } sqliteHelper.Close(); return(list); }
static Role() { Sqlite sqlite = new Sqlite(); SQLiteDataReader sQLiteDataReader = sqlite.Query("select * from role;"); while (sQLiteDataReader.Read()) { int key = sQLiteDataReader.GetInt32(0); string value = sQLiteDataReader.GetString(1); dicRole.Add(key, value); } sqlite.Close(); }
public static void Delete(string loglevel, string eventType, DateTime?startDate, DateTime?endDate, string user) { StringBuilder stringBuilder = new StringBuilder("delete from log "); ArrayList arrayList = new ArrayList(); if (!string.IsNullOrEmpty(loglevel)) { arrayList.Add("loglevel = '" + loglevel + "'"); } if (!string.IsNullOrEmpty(eventType)) { arrayList.Add("eventtype = '" + eventType + "'"); } if (!string.IsNullOrEmpty(user)) { arrayList.Add("user = '******'"); } DateTime value; if (startDate.HasValue && startDate.HasValue) { ArrayList arrayList2 = arrayList; value = startDate.Value; arrayList2.Add("logtime > datetime('" + value.ToString("yyyy-MM-dd HH:mm:ss") + "')"); } if (endDate.HasValue && endDate.HasValue) { ArrayList arrayList3 = arrayList; value = endDate.Value; arrayList3.Add("logtime < datetime('" + value.ToString("yyyy-MM-dd HH:mm:ss") + "')"); } string text = ""; if (arrayList.Count > 0) { string[] value2 = (string[])arrayList.ToArray(typeof(string)); text = string.Join(" and ", value2); } if (!string.IsNullOrEmpty(text)) { stringBuilder.Append(" where " + text); } Sqlite sqliteHelper = new Sqlite(); sqliteHelper.Query(stringBuilder.ToString()); sqliteHelper.Close(); }
/// <summary> /// Finds the specified where. /// </summary> /// <param name="where">The where.</param> /// <returns>Role.</returns> public static Role Find(string where) { Role role = new Role(); Sqlite sqlite = new Sqlite(); string text = "select * from role "; if (!string.IsNullOrEmpty(where)) { text = text + " where " + where; } SQLiteDataReader sQLiteDataReader = sqlite.Query(text); while (sQLiteDataReader.Read()) { role.Id = sQLiteDataReader.GetInt32(0); role.Name = sQLiteDataReader.GetString(1); } sqlite.Close(); return(role); }
/// <summary> /// Queries the specified where. /// </summary> /// <param name="where">The where.</param> /// <returns>List<Role>.</returns> public static List <Role> Query(string where) { List <Role> list = new List <Role>(); string text = "select id,name from role "; if (!string.IsNullOrEmpty(where)) { text = text + " where " + where; } Sqlite sqlite = new Sqlite(); SQLiteDataReader sQLiteDataReader = sqlite.Query(text); while (sQLiteDataReader.Read()) { Role role = new Role(); role.Id = sQLiteDataReader.GetInt32(0); role.Name = sQLiteDataReader.GetString(1); list.Add(role); } sqlite.Close(); return(list); }
/// <summary> /// Finds the specified username. /// </summary> /// <param name="username">The username.</param> /// <returns>User.</returns> public static User Find(string username) { User user = null; string sql = "SELECT U.ID,U.USERNAME,U.PASSWORD,R.ID,R.NAME FROM user U,role R WHERE R.ID=U.ROLEID AND U.USERNAME ='******'"; Sqlite sqlite = new Sqlite(); SQLiteDataReader sQLiteDataReader = sqlite.Query(sql); while (sQLiteDataReader.Read()) { user = new User(); user.Id = sQLiteDataReader.GetInt32(0); user.UserName = sQLiteDataReader.GetString(1); user.Password = sQLiteDataReader.GetString(2); Role role = new Role(); role.Id = sQLiteDataReader.GetInt32(3); role.Name = sQLiteDataReader.GetString(4); user.Role = role; user.RoleId = role.Id; } sqlite.Close(); return(user); }