public static AccountInfo FindBackChecked(string account, string key) { string sql = string.Format("select * from UsersTable where Account ='{0}' and Key='{1}'", account, Encryption.EncryptBase64(key)); using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql)) { if (reader == null) { return(null); } int result = 0; while (reader.Read() && result < 1) { result++; _account.ID = string.IsNullOrEmpty(reader["ID"].ToString()) ? 0 : int.Parse(reader["ID"].ToString()); _account.Person = reader["Person"].ToString(); _account.Account = reader["Account"].ToString(); _account.Password = reader["Password"].ToString(); _account.Key = reader["Key"].ToString(); _account.Power = string.IsNullOrEmpty(reader["Power"].ToString()) ? 0 : int.Parse(reader["Power"].ToString()); } if (result != 1) { return(null); } } return(_account); }
private void delete_toolStripMenuItem_Click(object sender, EventArgs e) { if (XtraMessageBox.Show("确认删除选中的缴费记录吗?删除后数据不可恢复,请谨慎操作。", "消息", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.No) { return; } VerificationForm form = new VerificationForm(); form.ShowDialog(); if (!form.Result) { return; } int[] selectRows = gridView1.GetSelectedRows(); int[] rowsID = new int[selectRows.Length]; for (int i = 0; i < selectRows.Length; i++) { rowsID[i] = (gridView1.GetRow(selectRows[i]) as PayRecordInfo).ID; } int result = SQLiteControl.RealyDelete("PayRecordTable", "ID", rowsID); if (result <= 0) { XtraMessageBox.Show("删除选中的缴费记录失败", "消息", MessageBoxButtons.YesNo, MessageBoxIcon.Error); return; } Query(); }
/// <summary> /// 账号密码完全验证 /// </summary> /// <param name="Account"></param> /// <returns></returns> public static bool AccountFullChecked(string Account, string Password) { string sql = string.Format("select * from UsersTable where Account='{0}' and Password='******'", Account, Password); using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql)) { if (reader == null) { return(false); } int result = 0; while (reader.Read()) { ++result; _account.ID = string.IsNullOrEmpty(reader["ID"].ToString()) ? 0 : int.Parse(reader["ID"].ToString()); _account.Person = reader["Person"].ToString(); _account.Account = reader["Account"].ToString(); _account.Password = reader["Password"].ToString(); _account.Key = reader["Key"].ToString(); _account.Power = string.IsNullOrEmpty(reader["Power"].ToString()) ? 0 : int.Parse(reader["Power"].ToString()); } if (result == 1) { return(true); } } return(false); }
private static async Task CreateTable(SQLiteControl command) { var sql = new StringBuilder(); sql.Clear(); sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_temporary ("); sql.AppendLine($" video_id TEXT NOT NULL,"); sql.AppendLine($" tick INTEGER NOT NULL,"); sql.AppendLine($"PRIMARY KEY (video_id)"); sql.AppendLine($")"); await command.ExecuteNonQueryAsync(sql.ToString()); sql.Clear(); sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_history ("); sql.AppendLine($" video_id TEXT NOT NULL,"); sql.AppendLine($" tick INTEGER NOT NULL,"); sql.AppendLine($"PRIMARY KEY (video_id, tick)"); sql.AppendLine($")"); await command.ExecuteNonQueryAsync(sql.ToString()); sql.Clear(); sql.AppendLine($"CREATE TABLE IF NOT EXISTS t_nico_favorite ("); sql.AppendLine($" mylist_id TEXT NOT NULL,"); sql.AppendLine($" tick INTEGER NOT NULL,"); sql.AppendLine($"PRIMARY KEY (mylist_id)"); sql.AppendLine($")"); await command.ExecuteNonQueryAsync(sql.ToString()); }
public static async Task Initialize(SQLiteControl command) { Temporaries = new StatefulModel.SortedObservableCollection <TNicoTemporary, DateTime>( await command.SelectNicoTemporary(), x => x.Date, true ); Histories = new StatefulModel.SortedObservableCollection <VNicoHistory, DateTime>( await command.SelectNicoHistory(), x => x.Date, true ); Favorites = new StatefulModel.SortedObservableCollection <TNicoFavorite, DateTime>( await command.SelectNicoFavorite(), x => x.Date, true ); var combo = XDocument.Load(CoreUtil.RelativePathToAbsolutePath(NicoComboPath)).Root; Combos = combo.Descendants("combo") .SelectMany(xml => { return(xml.Descendants("item") .Select(tag => new ComboboxItemModel( (string)xml.Attribute("group"), (string)tag.Attribute("value"), (string)tag.Attribute("display") ))); }) .ToArray(); }
public static int AddRecord(StudentInfo stu) { string sql = string.Format("insert into CheckRecordTable values(NULL, {0},'{1}', '{2}', {3}, '{4}')", AccountInfo.AccountSession.ID, AccountInfo.AccountSession.Person, DateTime.Now.ToString("yyyy-MM-dd"), stu.ID, stu.Name); return(SQLiteControl.ExecuteNonQuery(sql)); }
public LoginForm() { InitializeComponent(); //连接Sqlite数据库 if (!SQLiteControl.ConnectToDatabase(@"./DataBase/database.sqlite")) { XtraMessageBoxArgs args = ControlHelper.XtraMessageBoxArgs("消息", "连接数据库失败", new DialogResult[] { DialogResult.Yes }); XtraMessageBox.Show(args); Application.Exit(); } account_textEdit.Focus(); //每次登录前Copy一份DB FileInfo file = new FileInfo(@"./DataBase/database.sqlite"); if (!File.Exists(@"./DataBase/" + DateTime.Today.ToString("yyyy-MM-dd") + ".sqlite")) { file = file.CopyTo(@"./DataBase/" + DateTime.Today.ToString("yyyy-MM-dd") + ".sqlite"); } //解密文件 //else //{ // file = new FileInfo(@"./DataBase/" + DateTime.Today.ToString("yyyy-MM-dd") + ".sqlite"); // file.Decrypt(); //} PreLoad(); login_simpleButton.Focus(); }
/// <summary> /// 创建学生 /// </summary> /// <param name="stu"></param> /// <returns></returns> public static int CreateStudentInfo(StudentInfo stu) { string sql = string.Format("insert into StudentsTable values(NULL,'{0}','{1}','{2}','{3}','{4}','{5}',{6},{7},{8},{9},'{10}',{11}, {12})", stu.SerialNum, stu.Name, stu.Sex, stu.Parents, stu.Contacts, stu.Address, stu.Tuition, stu.Remaining, stu.ClassHours, stu.Pay ? 1 : 0, stu.LastPayDate.ToString("yyyy-MM-dd"), stu.NotPay, 0); return(SQLiteControl.ExecuteNonQuery(sql)); }
public static int Updata(StudentInfo stu) { string sql = string.Format("update StudentsTable set Name='{0}', Sex='{1}',Parents='{2}',Contacts='{3}',Address='{4}',Tuition={5},Remaining={6},ClassHours={7},Pay={8},LastPayDate='{9}',NotPay={10} where ID={11}", stu.Name, stu.Sex, stu.Parents, stu.Contacts, stu.Address, stu.Tuition, stu.Remaining, stu.ClassHours, stu.Pay ? 1 : 0, stu.LastPayDate.ToString("yyyy-MM-dd"), stu.NotPay, stu.ID); return(SQLiteControl.ExecuteNonQuery(sql)); }
public static int CreateRecord(PayRecordInfo payRecord) { string sql = string.Format("insert into PayRecordTable values(NULL,{0},'{1}','{2}',{3},'{4}','{5}',{6},{7},{8},{9},{10},'{11}')", payRecord.StudentID, payRecord.StudentName, payRecord.LastDateTime.ToString("yyyy-MM-dd"), payRecord.OperationID, payRecord.OperationPerson, payRecord.Remark, payRecord.Tuition, payRecord.Remaining, payRecord.ClassHours, payRecord.Pay ? 1 : 0, payRecord.NotPay, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")); return(SQLiteControl.ExecuteNonQuery(sql)); }
/// <summary> /// 修改班级信息 /// </summary> /// <param name="arg"></param> /// <returns></returns> public static int ModifyGroup(GroupClassInfo arg) { string sql = string.Format( "update GroupClassTable set GroupName = '{0}', ClassTeacher = '{1}' where ID ={2}", arg.GroupName, arg.ClassTeacher, arg.ID); return(SQLiteControl.ExecuteNonQuery(sql)); }
/// <summary> /// 创建班级 /// </summary> /// <param name="arg"></param> /// <returns></returns> public static int CreateGroup(GroupClassInfo arg) { string sql = string.Format("insert into GroupClassTable values(NULL, '{0}', '{1}','{2}','{3}','{4}', 0)", arg.GroupName, arg.CreatePerson, arg.CreateDate.ToString("yyyy-MM-dd"), arg.PersonID, arg.ClassTeacher); return(SQLiteControl.ExecuteNonQuery(sql)); }
public static Task <SQLiteControl> GetControl() { var path = @"lib\database.sqlite3"; var work = System.AppDomain.CurrentDomain.BaseDirectory.TrimEnd('\\'); var full = Path.Combine(work, path); Directory.CreateDirectory(Path.GetDirectoryName(full)); return(SQLiteControl.CreateAsync(path)); }
public static List <AccountInfo> GetAll() { string sql = "select * from UsersTable where Power >= 0"; using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql)) { List <AccountInfo> accounts = Looper(reader); return(accounts); } }
public static int Modify(AccountInfo account) { return(SQLiteControl.SQLiteUpDate("UsersTable", "ID", new int[] { account.ID }, new string[] { "Person", "Account", "Password", "Key" }, new string[] { account.Person, account.Account, Encryption.EncryptBase64(account.Password), Encryption.EncryptBase64(account.Key) })); }
/// <summary> /// 真正的删除 /// </summary> /// <param name="IDs"></param> /// <returns></returns> public static int RealyDelete(int[] IDs) { //删除GroupClassTable的内容 int result = SQLiteControl.RealyDelete("GroupClassTable", "ID", IDs); //删除关联的StudentsCheckinTable内容 if (result > 0) { result = SQLiteControl.RealyDelete("StudentsCheckinTable", "GroupID", IDs); } return(result); }
public static async Task <IEnumerable <TNicoFavorite> > SelectNicoFavorite(this SQLiteControl command) { var sql = new StringBuilder(); sql.Clear(); sql.AppendLine($"SELECT mylist_id, tick FROM t_nico_favorite ORDER BY tick DESC"); using (var reader = await command.ExecuteReaderAsync(sql.ToString())) { return(await reader.GetRows(r => new TNicoFavorite(r))); } }
public static async Task <IEnumerable <VNicoHistory> > SelectNicoHistory(this SQLiteControl command) { var sql = new StringBuilder(); sql.Clear(); sql.AppendLine($"SELECT video_id, tick, cnt FROM v_nico_history ORDER BY tick DESC"); using (var reader = await command.ExecuteReaderAsync(sql.ToString())) { return(await reader.GetRows(r => new VNicoHistory(r))); } }
public static async Task DeleteNicoHistory(this SQLiteControl command, string videoid) { var sql = new StringBuilder(); sql.Clear(); sql.AppendLine($"DELETE FROM t_nico_history WHERE video_id = ?"); var parameters = new[] { Sqlite3Util.CreateParameter(DbType.String, videoid), }; await command.ExecuteNonQueryAsync(sql.ToString(), parameters); }
public static List <PayRecordInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total) { string sql = string.Format("select * from PayRecordTable"); SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql); total = Looper(reader).Count; sql = string.Format("select * from PayRecordTable where (StudentName like '%{0}%' " + "or OperationPerson like '%{0}%') " + "limit {1} offset {2}", key, pageSize, (pageIndex - 1) * pageSize); reader = SQLiteControl.ExecuteReader(sql); var result = Looper(reader); reader.Close(); return(result); }
public static bool RegisterAccount(AccountInfo account) { string sql = string.Format( "insert into UsersTable values(NULL, '{0}', '{1}', '{2}', '{3}', {4})", account.Person, account.Account, account.Password, account.Key, account.Power); int result = SQLiteControl.ExecuteNonQuery(sql); if (result != 1) { return(false); } return(true); }
private static async Task CreateView(SQLiteControl command) { var sql = new StringBuilder(); sql.Clear(); sql.AppendLine($"CREATE VIEW IF NOT EXISTS v_nico_history AS"); sql.AppendLine($"SELECT"); sql.AppendLine($" video_id,"); sql.AppendLine($" MAX(tick) tick,"); sql.AppendLine($" COUNT(video_id) cnt"); sql.AppendLine($"FROM"); sql.AppendLine($" t_nico_history"); sql.AppendLine($"GROUP BY"); sql.AppendLine($" video_id"); await command.ExecuteNonQueryAsync(sql.ToString()); }
/// <summary> /// 为小组添加成员 /// </summary> /// <returns></returns> public static int AddMembers(int GroupID, int[] StudentsID) { if (StudentsID.Length <= 0) { return(-1); } string sql = string.Format("insert into StudentsCheckinTable values(NULL, {0}, {1}", GroupID, StudentsID[0]); for (int i = 1; i < StudentsID.Length; i++) { sql += string.Format("),(NULL, {0}, {1}", GroupID, StudentsID[i]); } sql += ")"; return(SQLiteControl.ExecuteNonQuery(sql)); }
public static bool SimpleDelete(int[] id) { string sql = string.Format("delete from UsersTable where ID in ({0}", id[0]); for (int i = 1; i < id.Length; i++) { sql += string.Format(", {0}", id[i]); } sql += ")"; int result = SQLiteControl.ExecuteNonQuery(sql); if (result > 0) { return(true); } return(false); }
/// <summary> /// 简单的查询 /// </summary> /// <param name="key"></param> /// <returns></returns> public static List <AccountInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total) { string sql = "select * from UsersTable where Power >= 0"; SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql); List <AccountInfo> accounts = Looper(reader); total = accounts == null ? 0 : accounts.Count; sql = string.Format( "select * from UsersTable where (Person like '%{0}%' or Account like '%{0}%') and Power >= 0 limit {1} offset {2}", key, pageSize, (pageIndex - 1) * pageSize); reader = SQLiteControl.ExecuteReader(sql); accounts.Clear(); accounts = Looper(reader); reader.Dispose(); return(accounts); }
/// <summary> /// 查询被删除的学生信息 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="key"></param> /// <param name="total"></param> /// <returns></returns> public static List <StudentInfo> RealyQuerry(int pageIndex, int pageSize, string key, out int total) { string sql = string.Format("select * from StudentsTable where IsDelete = 1"); SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql); total = Looper(reader).Count; reader.Close(); sql = string.Format( "select * from StudentsTable where (SerialNum like '%{0}%' " + "or Name like '%{0}%' or Parents like '%{0}%' " + "or Address like '%{0}%') " + "and IsDelete = 1 " + "limit {1} offset {2}", key, pageSize, (pageIndex - 1) * pageSize); reader = SQLiteControl.ExecuteReader(sql); return(Looper(reader)); }
/// <summary> /// 查询 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="key"></param> /// <param name="total"></param> /// <returns></returns> public static List <GroupClassInfo> SimpleQuery(int pageIndex, int pageSize, string key, out int total) { string sql = string.Format("select * from GroupClassTable where IsDelete is NULL or IsDelete = 0"); SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql); total = Looper(reader).Count; sql = string.Format( "select * from GroupClassTable where (GroupName like '%{0}%' " + "or ClassTeacher like '%{0}%') " + "and (IsDelete is NULL or IsDelete = 0) " + "limit {1} offset {2}", key, pageSize, (pageIndex - 1) * pageSize); reader = SQLiteControl.ExecuteReader(sql); var result = Looper(reader); reader.Close(); return(result); }
public static async Task MergeNicoHistory(this SQLiteControl command, string videoid) { var sql = new StringBuilder(); sql.Clear(); sql.AppendLine($"INSERT OR REPLACE INTO t_nico_history ("); sql.AppendLine($" video_id, tick"); sql.AppendLine($")"); sql.AppendLine($"VALUES ("); sql.AppendLine($" ?, ?"); sql.AppendLine($")"); var parameters = new[] { Sqlite3Util.CreateParameter(DbType.String, videoid), Sqlite3Util.CreateParameter(DbType.Int64, DateTime.Now.Ticks), }; await command.ExecuteNonQueryAsync(sql.ToString(), parameters); }
public static async Task MergeNicoFavorite(this SQLiteControl command, string mylistid, DateTime date) { var sql = new StringBuilder(); sql.Clear(); sql.AppendLine($"INSERT OR REPLACE INTO t_nico_favorite ("); sql.AppendLine($" mylist_id, tick"); sql.AppendLine($")"); sql.AppendLine($"VALUES ("); sql.AppendLine($" ?, ?"); sql.AppendLine($")"); var parameters = new[] { Sqlite3Util.CreateParameter(DbType.String, mylistid), Sqlite3Util.CreateParameter(DbType.Int64, date.Ticks), }; await command.ExecuteNonQueryAsync(sql.ToString(), parameters); }
/// <summary> /// 账号检查 /// </summary> /// <param name="Account"></param> /// <returns></returns> public static bool AccountChecked(string Account) { string sql = string.Format("select * from UsersTable where Account='{0}'", Account); using (SQLiteDataReader reader = SQLiteControl.ExecuteReader(sql)) { if (reader == null) { return(false); } int result = 0; while (reader.Read()) { ++result; } if (result == 1) { return(true); } } return(false); }