/// <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();
}
