/// <summary> /// 获得所有群 /// </summary> public static List <exRoom> GetRooms() { List <exRoom> Rooms = new List <exRoom>(); string sql = "select * from Rooms"; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null); if (dr != null) { while (dr.Read()) { exRoom Room = new exRoom(); { Room.RoomID = Convert.ToString(dr["RoomID"]); Room.RoomName = Convert.ToString(dr["RoomName"]); Room.Notice = Convert.ToString(dr["Notice"]); Room.UserIDs = Convert.ToString(dr["Users"]); Room.OrderID = Convert.ToInt32(dr["OrderID"]); Room.CreateUserID = Convert.ToString(dr["CreateUserID"]); } Rooms.Add(Room); } dr.Close(); } dr.Dispose(); return(Rooms); }
/// <summary> /// 获得所有分组集合 /// </summary> public static List <exGroup> GetGroups() { List <exGroup> Groups = new List <exGroup>(); string sql = "select * from Groups order by orderID "; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null); if (dr != null) { while (dr.Read()) { exGroup group = new exGroup(); { group.GroupID = Convert.ToString(dr["GroupID"]); group.GroupName = Convert.ToString(dr["GroupName"]); group.SuperiorID = Convert.ToString(dr["SuperiorID"]); group.OrderID = Convert.ToInt32(dr["orderID"]); } Groups.Add(group); } dr.Close(); } dr.Dispose(); return(Groups); }
public Dictionary<String, long> GetHighScores() { Dictionary<String, long> topResults = new Dictionary<String, long>(); dbConnection.Open(); reader = this.selectTopScores.ExecuteReader(); while (reader.Read()) { topResults.Add(reader["name"].ToString(), (long)reader["result"]); } reader.Dispose(); dbConnection.Close(); return topResults; }
/// <summary> /// 获取用户资料 /// </summary> /// <param name="UserID"></param> /// <returns></returns> public static UserVcard GetUserVcard(string UserID) { UserVcard card = null; string sql = "select Vcard from UsersVcard where UserID=@UserID"; System.Data.SQLite.SQLiteParameter[] parameters = new System.Data.SQLite.SQLiteParameter[] { new System.Data.SQLite.SQLiteParameter("@UserID", UserID) }; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, parameters); if (dr != null && dr.Read()) { card = Factory.CreateInstanceObject(Convert.ToString(dr["Vcard"])) as UserVcard; dr.Close(); } dr.Dispose(); return(card); }
private void GetAllGZ() { //if (1==2) { //string sql = "select [gzDay],[gzMonth],[gzYear],DateValue=([DateValue])+' '+ substring(JieQi,4,5),[weekDay],[constellation],JieQi,[nlMonth],[nlDay] from [ChineseTenThousandCalendar] where left(ltrim(JieQi),2) in (" + JieQiHelper.GetInJieQis() + ")"; //string datasource = Application.StartupPath + "/JieQi.db"; //SqlLiteHelper.SqlServerToSqlLite(datasource, sql); } int myDiffday = 0; string jd = string.Empty; string jm = string.Empty; string jy = string.Empty; string jnd = string.Empty; string jnm = string.Empty; int day = 0; System.Data.SQLite.SQLiteDataReader reader = SelectJieQI.GetReadData(myDiffday); if (reader != null) { while (reader.Read()) { jieQi = reader["jieQi"].ToString().Trim() + " " + reader["DayGZ"].ToString().Trim() + "日" + reader["DateValue"].ToString(); jd = reader["DayGZ"].ToString(); jm = reader["MonthGZ"].ToString(); jy = reader["YearGZ"].ToString(); jnd = reader["NongLiDay"].ToString(); jnm = reader["NongLiMonth"].ToString();; DateTime nowdate = DateTime.Now.AddDays(myDiffday); DateTime jqt = DateTime.Parse(reader["DateValue"].ToString()); TimeSpan ts = nowdate - jqt; day = ts.Days; } reader.Close(); reader.Dispose(); } CalCurrentAllGZ(day, jd, jm, jy, jnd, jnm); }
/// <summary> /// 获得消息集合 /// </summary> /// <param name="MsgInfoClass">消息类型</param> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <returns></returns> public static List <IMLibrary3.Protocol.Message> GetMsg(IMLibrary3.Enmu.MessageType MessageType, int pageIndex, int pageSize) { List <IMLibrary3.Protocol.Message> Msgs = null; System.Data.SQLite.SQLiteParameter messageType = new System.Data.SQLite.SQLiteParameter("MessageType", MessageType); string sql = "select * from MsgRecord where MessageType=@MessageType " + " order by ID limit " + pageSize.ToString() + " offset " + ((pageIndex - 1) * pageSize).ToString(); System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, messageType); if (dr != null) { Msgs = new List <IMLibrary3.Protocol.Message>(); while (dr.Read()) { Msgs.Add(GetDrMsg(dr)); } } dr.Close(); dr.Dispose(); return(Msgs); }
/// <summary> /// Database has table with highscore. This method will search for lowest result. If there is smaller number of maximum allowed top scores it will return -1 /// </summary> /// <param name="maximumTopScores"></param> /// <returns></returns> public long getLowestHighscore(int maximumTopScores) { long numberOfScores = -1; long lowestScore = -1; dbConnection.Open(); reader = selectLowestResult.ExecuteReader(); int counter = 0; while (reader.Read()) { lowestScore = (long)reader["result"]; counter++; } reader.Dispose(); dbConnection.Close(); if (numberOfScores < maximumTopScores) { return -1; } return lowestScore; }
/// <summary> /// 获得组织机构版本 /// </summary> /// <param name="UserID"></param> /// <returns></returns> public static IMLibrary3.Protocol.OrgVersion GetOrgVersion(string UserID) { IMLibrary3.Protocol.OrgVersion orgVersion = null; #region 判断登录的用户本地数据库文件夹及文件是否存在,不存在返回空值 //System.IO.DirectoryInfo dInfo = new System.IO.DirectoryInfo(UserID); //if (!dInfo.Exists) // return null; //string FileNamePath = UserID + @"\Record.mdb"; //if (!System.IO.File.Exists(FileNamePath)) // return null; ////如果本地数据库存在,则设置数据库连接字符串 //DBFileNamePath = FileNamePath; //conStr = "data source=" + DBFileNamePath; #endregion try { string sql = "select * from OrgVersion"; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null); if (dr != null && dr.Read()) { orgVersion = new IMLibrary3.Protocol.OrgVersion(); orgVersion.GroupsVersion = Convert.ToString(dr["GroupsVersion"]).Trim(); orgVersion.UsersVersion = Convert.ToString(dr["UsersVersion"]).Trim(); orgVersion.RoomsVersion = Convert.ToString(dr["RoomsVersion"]).Trim(); } dr.Close(); dr.Dispose(); return(orgVersion); } catch (Exception ex) { MessageBox.Show(ex.Source + ex.Message); } return(null); }
/// <summary> /// 获得所有用户集合 /// </summary> public static List <exUser> GetUsers() { List <exUser> Users = new List <exUser>(); string sql = "select * from Users order by orderID"; System.Data.SQLite.SQLiteDataReader dr = SQLiteDBHelper.ExecuteReader(sql, null); if (dr != null) { while (dr.Read()) { exUser user = new exUser(); { user.UserID = Convert.ToString(dr["UserID"]); user.UserName = Convert.ToString(dr["UserName"]); user.GroupID = Convert.ToString(dr["GroupID"]); user.OrderID = Convert.ToInt32(dr["OrderID"]); } Users.Add(user); } dr.Close(); } dr.Dispose(); return(Users); }
public void InsertHighScore(String name, long result, int maximumTopScores) { insertHighscoreCommand.Parameters.Add(new SQLiteParameter("param1", name)); insertHighscoreCommand.Parameters.Add(new SQLiteParameter("param2", result)); dbConnection.Open(); int insertRes = insertHighscoreCommand.ExecuteNonQuery(); // leave only TOP maximumTopScores reader = selectTopScores.ExecuteReader(); List<long> topResults = new List<long>(); while (reader.Read()) { topResults.Add((long)(reader["result"])); } reader.Dispose(); long resultToRemove = -1; if (topResults.Count > maximumTopScores) { // read resultOfLast and go to next first that is different (maybe in some cases maximumTopScores and next one will have same result) long resultOfLast = topResults[maximumTopScores-1]; for (int i = maximumTopScores - 1; i < topResults.Count; i++) { if (!topResults[i].Equals(resultOfLast)) { resultToRemove = topResults[i]; } } } if (resultToRemove != -1) { removeInvalidHighscores.Parameters.Add(new SQLiteParameter("param1", resultToRemove)); int deleteRes = removeInvalidHighscores.ExecuteNonQuery(); } dbConnection.Close(); }
public void FreeQuery( SQLiteDataReader dataReader ) { dataReader.Dispose(); }