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