1.DBHelper升级
【Account类】
namespace AdoNet_07
{
/// <summary>
/// 银行账号（数据模型）
/// </summary>
public class Account
{
public string CardId { get; set; }//卡号
public string Pwd { get; set; }//密码
public string Name { get; set; }//姓名
public double UserMoney { get; set; }//存款
public string TelePhone { get; set; }//电话
}
}【DBHelper类】
using System.Data.SqlClient;
using System.Data;
namespace AdoNet_07
{
/// <summary>
/// 数据库帮助类(使用工具类)，里面的内容最好写成静态的
/// 也有的程序员会写成SQLHelper
/// 添加、删除、修改、查询的通用方法
/// </summary>
public static class DBHelper
{
private static string connStr = "server=.;uid=sa;pwd=你的密码;database=BankDB";
/// <summary>
/// 返回受影响的行数
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static int ExecuteNonQuery(string sql,params SqlParameter[] paras)//sql：预留窗口 params：可选参数标识（只支持一维数组）
{
int result = -1;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
result = command.ExecuteNonQuery();
}
return result;//返回受影响的行数
}
/// <summary>
/// 返回一行一列
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static object ExecuteScalar(string sql,params SqlParameter[] paras)
{
object result = -1;
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
result = command.ExecuteScalar();//返回第一行第一列
}
return result;
}
/// <summary>
/// 执行返回游标对象（可以使用循环返回对行多列的数据）
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] paras)
{
//游标对象不能够释放，要一直使用，所以不能够用using
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);//从数据库中只读只进一行数据
//CommandBehavior.CloseConnection 执行关闭游标对象时，顺带关闭数据库的连接
return reader;//返回一个游标对象
}
/// <summary>
/// 查询、返回一个临时表
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataTable GetDataTable(string sql,params SqlParameter[] paras)
{
DataTable dt = new DataTable();//存放数据的容器
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(command);//数据适配器
adapter.Fill(dt);
}
return dt;
}
/// <summary>
/// 查询、返回一个数据集
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql, params SqlParameter[] paras)
{
DataSet ds = new DataSet();//存放数据的容器
using (SqlConnection conn = new SqlConnection(connStr))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paras);
SqlDataAdapter adapter = new SqlDataAdapter(command);//数据适配器
adapter.Fill(ds);
}
return ds;
}
}
}
【AccountManager类】
using System.Data.SqlClient;//访问sqlserver数据库的命名空间
using System.Data;
namespace AdoNet_07
{
/// <summary>
/// 银行账号的业务类
/// </summary>
public class AccountManager
{
/// <summary>
/// 系统界面
/// </summary>
public void InitView()
{
Console.WriteLine("==========================欢迎进入中国邮政储蓄银行ATM机系统==========================");
//创建一个账户
Account account = new Account();
Console.WriteLine("请输入您的银行卡号：");
account.CardId = Console.ReadLine();
Console.WriteLine("请输入您的银行密码：");
account.Pwd = Console.ReadLine();
Account loginAccount = Login(account);
if (loginAccount != null)
{
Console.WriteLine($"欢迎{loginAccount.Name}（卡号：{loginAccount.CardId}）登陆！");
}
else
{
Console.WriteLine("卡号或密码错误！！");
}
}
/// <summary>
/// 登陆账户
/// </summary>
/// <param name="account"></param>
/// <returns></returns>
public Account Login(Account account)
{
Account loginAccount = null;
string sql = $@"select CardId,[Name],UserMoney from Account
where CardID = @cardid and Pwd = @pwd";
SqlParameter[] paras = {
new SqlParameter("@cardid", account.CardId),
new SqlParameter("@pwd", account.Pwd)
};
using (SqlDataReader reader = DBHelper.ExecuteReader(sql, paras))
{
//reader.Read()执行前进（下一行）
if (reader.Read())
{
loginAccount = new Account();//创建对象，分配内存
loginAccount.CardId = reader["cardId"].ToString();
loginAccount.Name = reader["Name"].ToString();
loginAccount.UserMoney = Convert.ToDouble(reader["UserMoney"]);
}
}//CommandBehavior.CloseConnection 执行关闭游标对象时，顺带关闭数据库的连接
return loginAccount;
}
/// <summary>
/// 删除账户
/// </summary>
public void DeleteAccount()
{
string sql = $@"delete from Account where CardID = @cardid";
SqlParameter[] paras =
{
new SqlParameter("@cardid",10005)
};
int result = DBHelper.ExecuteNonQuery(sql, paras);
if (result > 0)
{
Console.WriteLine("删除成功！");
}
else
{
Console.WriteLine("删除失败！");
}
}
/// <summary>
/// 添加账户
/// </summary>
public void AddAccount()
{
string sql = $@"insert into Account
values('10006','888888','jhon',12000,'1566855475')";
int result = DBHelper.ExecuteNonQuery(sql);
if (result > 0)
{
Console.WriteLine("添加成功！");
}
else
{
Console.WriteLine("添加失败！");
}
}
/// <summary>
/// 统计开户人员
/// </summary>
public void AcoountCount()
{
string sql = "select count(cardid) from Account";
int result = (int)DBHelper.ExecuteScalar(sql);
Console.WriteLine($"银行系统中的总人数：{result}");
}
/// <summary>
/// 根据卡号获取用户名
/// </summary>
public string GetNameByCardId(int cardId)
{
string sql = "select name from Account where cardid=@cardid";
SqlParameter[] paras =
{
new SqlParameter("@cardid",cardId)
};
return DBHelper.ExecuteScalar(sql, paras).ToString();
}
/// <summary>
/// 获取所有的开户人信息
/// </summary>
public List<Account> GetAccountList()
{
List<Account> list = new List<Account>();//泛型集合，用于存放多个账户
string sql = "select cardid,name,usermoney,telephone from Account";
using (SqlDataReader reader = DBHelper.ExecuteReader(sql))
{
//reader.Read()执行前进（下一行）
while (reader.Read())
{
Account account = new Account();//创建对象，分配内存
account.CardId = reader["cardId"].ToString();
account.Name = reader["Name"].ToString();
account.UserMoney = Convert.ToDouble(reader["UserMoney"]);
account.TelePhone = reader["telephone"].ToString();
list.Add(account);//把对象循环添加到集合
}
}
return list;
}
/// <summary>
/// 显示账号信息（多行多列）
/// </summary>
public void ShowAccountList()
{
Console.WriteLine("==========================目前已经开户的人员列表==========================");
List<Account> list = GetAccountList();
foreach (Account account in list)
{
Console.WriteLine($"卡号：{account.CardId}，姓名：{account.Name}，余额：{account.UserMoney}，电话：{account.TelePhone}");
}
}
/// <summary>
/// 获取转账信息
/// </summary>
/// <returns></returns>
public DataTable GetAccountRecord()
{
string sql = @"select PayAccount,a.Name PayName,ReceiveAccount,b.Name ReceiveName,TransferMoney,CreateTime,Remark
from AccountRecord c
left join Account a
on(a.CardID=c.PayAccount)
left join Account b
on(b.CardID=c.ReceiveAccount)";
return DBHelper.GetDataTable(sql);
}
/// <summary>
/// 显示转账信息
/// </summary>
public void ShowAccountRecord()
{
Console.WriteLine("==========================转账信息==========================");
DataTable dt_AccountRecord = GetAccountRecord();
foreach (DataRow row in dt_AccountRecord.Rows)
{
Console.WriteLine($"支付账号：{row["PayAccount"]}，付款人姓名：{row["PayName"]}" +
$"收款账号：{row["ReceiveAccount"]}，收款人姓名：{row["ReceiveName"]}" +
$"转账金额：{row["TransferMoney"]}" +
$"创建时间：{row["CreateTime"]}，备注：{row["Remark"]}");
}
}
}
}【调用】
namespace AdoNet_07
{
class Program
{
static void Main(string[] args)
{
AccountManager accountManager = new AccountManager();
//accountManager.InitView();
//accountManager.AcoountCount();
//string name = accountManager.GetNameByCardId(10001);
//Console.WriteLine(name);
//accountManager.ShowAccountList();
accountManager.ShowAccountRecord();
Console.ReadLine();
}
}
}【显示账户信系的结果】
【显示转账记录的结果】
2.分层架构（两层架构）
【案例】
①ABO_08 UI：显示层（UI、WEB、View）
②（类库）ADO_08 DAL：数据访问层
③（类库）TAOBAO.Models：辅助层（Models、Entity(实体)）
【注】由于“显示层”要调用“数据访问层”，所以添加一个“辅助层”
【层与层之间的关系】
“显示层”要调用“数据访问层”：TAOBAO.UI项目引用类库ADO_08 DAL，并写using ADO_08_DAL;
分层的所有层都需要引用辅助层，并写using TAOBAO_08_Models;
【具体操作结果见TAOBAO（Ado_08 UI、Ado_08 DAL、Ado_08 Models）】
https://download.csdn.net/download/weixin_45406160/13455567
3.修改配置文件
【添加一个“属性”节点】可以写多个库
<connectionStrings>
<add name="connStr" connectionString="server=.;uid=sa;pwd=你的密码;database=BankDB"/>
</connectionStrings>
【添加System.Configuration】
【在DBHelper中引入命名空间】using System.Configuration
using System.Configuration;
private static string connStr = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;//获取节点对象的属性【添加一个“设置”节点】
<appSettings>
<add key="projectName" value="LZL银行系统"/>
<add key="version" value="1.0"/>
</appSettings>【在 DBHelper 中使用 】
ConfigurationManager.AppSettings["projrectName"];【ABO_08 UI 的配置文件App.cofig】
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<connectionStrings>
<add name="ConnStr" connectionString="server=.;uid=sa;pwd=你的密码;database=BankDB"/>
</connectionStrings>
<appSettings>
<add key="projectName" value="LZL银行系统"/>
<add key="version" value="1.0"/>
</appSettings>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
</startup>
</configuration>
1189
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
