public static Account GetAccount(Account account) { SqlConnection conn = DAO.Connection(); string queryAccount = "SELECT AccountName, AccountBank, AccountType, Balance, Grade, Flow FROM [Account] WHERE AccountID ='" + account.AccountID + "'"; try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = queryAccount; SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { account.AccountName = reader["AccountName"].ToString().Trim(); account.AccountBank = Convert.ToInt16(reader["AccountBank"]); account.AccountType = Convert.ToBoolean(reader["AccountType"]); account.Balance = Convert.ToSingle(reader["Balance"]); account.Grade = Convert.ToInt16(reader["Grade"]); account.Flow = Convert.ToSingle(reader["Flow"]); } } reader.Close(); conn.Close(); } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); } return(account); }
public static string GetBankName(int id) { SqlConnection conn = DAO.Connection(); string queryName = "SELECT Name FROM [Bank] WHERE ID ='" + id.ToString() + "'"; try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = queryName; SqlDataReader dataReader = command.ExecuteReader(); if (!dataReader.HasRows) { dataReader.Close(); conn.Close(); return("银行名缺失"); } else { while (dataReader.Read()) { return(dataReader["Name"].ToString()); } dataReader.Close(); conn.Close(); return("银行名缺失"); } } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); return("程序错误"); } }
public static IList <Account> GetAccountList(User user, Account account) { SqlConnection conn = DAO.Connection(); IList <Account> accounts = new List <Account>(); string queryAccount = "SELECT AccountID, AccountName, AccountBank, AccountType, Balance, Grade, Flow FROM [Account] WHERE UserID ='" + user.UserId + "'"; try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = queryAccount; SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { if (account.AccountID != dataReader["AccountID"].ToString().Trim()) { accounts.Add(new Account(dataReader["AccountID"].ToString().Trim(), dataReader["AccountName"].ToString().Trim(), Convert.ToInt16(dataReader["AccountBank"]), Convert.ToBoolean(dataReader["AccountType"]), Convert.ToSingle(dataReader["Balance"]), Convert.ToInt16(dataReader["Grade"]), user.UserId, Convert.ToSingle(dataReader["Flow"]))); } } dataReader.Close(); conn.Close(); } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); } return(accounts); }
public static IList <Bank> GetBankList() { SqlConnection conn = DAO.Connection(); string queryName = "SELECT ID, Name FROM [Bank]"; IList <Bank> list = new List <Bank>(); try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = queryName; SqlDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { list.Add(new Bank(Convert.ToInt16(dataReader["ID"]), dataReader["Name"].ToString().Trim())); } dataReader.Close(); conn.Close(); return(list); } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); return(new List <Bank>()); } }
// 返回UserID public static int DeleteAcount(Account account) { SqlConnection conn = DAO.Connection(); string delAccount = "DELETE FROM Account WHERE AccountID = '" + account.AccountID + "'"; try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = delAccount; int rows = command.ExecuteNonQuery(); if (rows == 1) { return(0); } else { Console.WriteLine("影响行数为{0}", rows); return(-1); } } catch (Exception exception) { Debug.WriteLine(exception.Message.ToString()); return(-1); } }
public static Authority AccessAuthority(int ID) { Authority authority = new Authority(); SqlConnection conn = DAO.Connection(); string queryId = "SELECT Draw, Loan FROM [Authority] WHERE Id='" + ID.ToString() + "'"; try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = queryId; SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { authority.Draw = Convert.ToSingle(reader["Draw"]); authority.Loan = Convert.ToSingle(reader["Loan"]); } reader.Close(); conn.Close(); return(authority); } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); return(null); } }
// 返回UserID public static int Access(Account account, bool status, float amount) { SqlConnection conn = DAO.Connection(); Authority authority = DAO.AccessAuthority(account.Grade); if (status) { if (amount < 0) { amount *= (-1); } if (amount > authority.Draw) { return(1); } } else { if (amount > 0) { amount *= (-1); } if (Math.Abs(account.Balance + amount) > authority.Loan) { return(2); } } string accessAmount = "UPDATE Account SET Balance = " + Convert.ToSingle(account.Balance + amount) + ", Flow = " + Convert.ToSingle(account.Flow + Math.Abs(amount)) + " WHERE AccountID = '" + account.AccountID + "'"; try { conn.Open(); SqlCommand command = conn.CreateCommand(); //Console.WriteLine(authority.Loan); //Console.WriteLine(Convert.ToSingle(account.Balance + amount)); //Console.WriteLine(accessAmount); command.CommandText = accessAmount; int rows = command.ExecuteNonQuery(); if (rows == 1) { conn.Close(); return(0); } else { Console.WriteLine("影响行数为{0}", rows); conn.Close(); return(-1); } } catch (Exception exception) { Debug.WriteLine(exception.Message.ToString()); return(-1); } }
// 返回UserID public static void Login(User user) { SqlConnection conn = DAO.Connection(); // 查询账户是否存在 string queryUser = "******" + user.UserName + "'"; try { // 打开数据链接 conn.Open(); //Console.WriteLine("检查用户名{0}是否存在...", user.UserName); // 新建一个命令对象 SqlCommand command = conn.CreateCommand(); // 写入SQL语句 command.CommandText = queryUser; // 读取对应的结果集 SqlDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { //Console.WriteLine(reader["UserID"]); if (reader["Password"].ToString().CompareTo(user.Password) == 0) { user.UserId = reader["UserID"].ToString(); return; } else { user.UserId = "2"; } } } else { user.UserId = "1"; } reader.Close(); conn.Close(); } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); user.UserId = "3"; } }
// 返回UserID public static int AddUser(User user) { int minLength = 4; if (user.Password.Length < minLength) { return(1); } else { int status = DAO.CheckRepeatUserName(user.UserId, user.UserName); if (status == 0) { SqlConnection conn = DAO.Connection(); string addUser = "******"; addUser = string.Format(addUser, user.UserId, user.UserName, user.Password); //Console.WriteLine(addAccount); try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = addUser; int rows = command.ExecuteNonQuery(); if (rows == 1) { return(0); } else { Console.WriteLine("影响行数为{0}", rows); return(-1); } } catch (Exception exception) { Debug.WriteLine(exception.Message.ToString()); return(-1); } } else { return(2); } } }
// 返回UserID public static int Transfer(Account souAccount, Account tarAccount, float amount) { SqlConnection conn = DAO.Connection(); if (amount > DAO.AccessAuthority(souAccount.Grade).Draw) { return(1); } if (souAccount.Balance - amount < 0) { return(2); } string sourceAmount = "UPDATE Account SET Balance = " + Convert.ToSingle(souAccount.Balance - amount) + ", Flow = " + Convert.ToSingle(souAccount.Flow + amount) + " WHERE AccountID = '" + souAccount.AccountID + "'"; string targetAmount = "UPDATE Account SET Balance = " + Convert.ToSingle(tarAccount.Balance + amount) + ", Flow = " + Convert.ToSingle(tarAccount.Flow + amount) + " WHERE AccountID = '" + tarAccount.AccountID + "'"; conn.Open(); SqlTransaction sqlTransaction = conn.BeginTransaction(); SqlCommand command = conn.CreateCommand(); try { command.CommandText = sourceAmount; command.Transaction = sqlTransaction; command.ExecuteNonQuery(); command = conn.CreateCommand(); command.CommandText = targetAmount; command.Transaction = sqlTransaction; command.ExecuteNonQuery(); sqlTransaction.Commit(); return(0); } catch (Exception exception) { sqlTransaction.Rollback(); Debug.WriteLine(exception.Message.ToString()); return(-1); } finally { conn.Close(); } }
public static DataSet GetAccountSet(User user) { SqlConnection conn = DAO.Connection(); // 查询对应ID下的多个账户 string queryAccount = "SELECT AccountID, AccountName, AccountBank, AccountType, Balance, Grade, Flow FROM [Account] WHERE UserID ='" + user.UserId + "'"; try { conn.Open(); SqlDataAdapter dataAdapter = new SqlDataAdapter(queryAccount, conn); DataSet dataSet = new DataSet(); dataAdapter.Fill(dataSet, "Account"); conn.Close(); return(dataSet); } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); return(new DataSet()); } }
// 返回UserID public static string CheckUser(string userID) { if (userID.ToString().Trim() == "") { return(""); } else { SqlConnection conn = DAO.Connection(); // 查询账户是否存在 string queryId = "SELECT UserName FROM [User] WHERE UserId='" + userID + "'"; try { // 打开数据链接 conn.Open(); //Console.WriteLine("检查用户名{0}是否存在...", user.UserName); // 新建一个命令对象 SqlCommand command = conn.CreateCommand(); // 写入SQL语句 command.CommandText = queryId; // 读取对应的结果集 SqlDataReader reader = command.ExecuteReader(); while (reader.Read()) { return(reader["UserName"].ToString()); } reader.Close(); conn.Close(); return(""); } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); return(""); } } }
// 返回UserID public static int AddAcount(Account account) { int minLength = 2; if (account.AccountID == "") { return(1); } else { if (account.AccountName == "") { return(2); } else if (account.AccountName.Length < minLength) { return(3); } else { if (account.AccountBank == -1) { return(4); } else { if (account.Grade > -1) { SqlConnection conn = DAO.Connection(); string addAccount = "INSERT INTO Account VALUES ('{0}', N'{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}')"; addAccount = string.Format(addAccount, account.AccountID, account.AccountName, account.AccountBank, account.AccountType, account.Balance, account.Grade, account.UserID, account.Flow); //Console.WriteLine(addAccount); try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = addAccount; int rows = command.ExecuteNonQuery(); if (rows == 1) { return(0); } else { Console.WriteLine("影响行数为{0}", rows); return(-1); } } catch (Exception exception) { Debug.WriteLine(exception.Message.ToString()); return(-1); } } else { return(5); } } } } }
// 返回UserID public static int ChangeUserName(string OldUserName, string NewUserName, User user) { int minLength = 2; if (OldUserName == "") { return(1); } else { if (OldUserName != user.UserName) { return(2); } else { if (NewUserName.Length < minLength) { return(3); } else { if (OldUserName == NewUserName) { return(4); } else { int status = DAO.CheckRepeatUserName(user.UserId, NewUserName); if (status == 0) { SqlConnection conn = DAO.Connection(); string updateUserName = "******" + NewUserName + "' WHERE UserID = '" + user.UserId + "'"; //Console.WriteLine(updateUserName); try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = updateUserName; int rows = command.ExecuteNonQuery(); conn.Close(); if (rows > 0) { return(0); } else { return(-1); } } catch (Exception exception) { Debug.WriteLine(exception.Message.ToString()); return(-1); } } else if (status == 2) { return(5); } else if (status == 3) { return(6); } else { return(7); } } } } } }
// 返回UserID public static int ChangePassword(string OldPsw, string NewPsw, string ConfirmPsw, User user) { int minLength = 3; if (OldPsw == "") { return(1); } else { if (OldPsw != user.Password) { return(2); } else { if (NewPsw.Length < minLength) { return(3); } else if (!Utils.CheckValidChar(NewPsw)) { return(4); } else { if (Convert.ToBoolean(ConfirmPsw.CompareTo(NewPsw))) { return(5); } else { SqlConnection conn = DAO.Connection(); string updatePsw = "UPDATE [User] SET Password='******' WHERE UserID = '" + user.UserId + "'"; //Console.WriteLine(updatePsw); try { conn.Open(); SqlCommand command = conn.CreateCommand(); command.CommandText = updatePsw; int rows = command.ExecuteNonQuery(); conn.Close(); if (rows > 0) { return(0); } else { return(-1); } } catch (Exception exception) { Debug.WriteLine(exception.Message.ToString()); return(-1); } } } } } }
// 返回UserID public static int CheckRepeatUserName(string userID, string userName) { SqlConnection conn = DAO.Connection(); // 查询账户是否存在 string queryUserName = "******" + userName + "'"; try { // 打开数据链接 conn.Open(); //Console.WriteLine("检查用户名{0}是否存在...", user.UserName); // 新建一个命令对象 SqlCommand command = conn.CreateCommand(); // 写入SQL语句 command.CommandText = queryUserName; // 读取对应的结果集 SqlDataReader reader = command.ExecuteReader(); if (!reader.HasRows) { return(0); } else { int rows = 0; string id = ""; string name = ""; while (reader.Read()) { id = reader["UserID"].ToString(); name = reader["UserName"].ToString(); rows++; } //Console.WriteLine(id); if (rows == 1) { if (id == userID) { reader.Close(); conn.Close(); return(1); } else { reader.Close(); conn.Close(); if (name != userName) { return(0); } return(2); } } else { reader.Close(); conn.Close(); return(3); } } } catch (Exception e) { Debug.WriteLine(e.Message.ToString()); return(4); } }