最近公司需要用c#,就简单看了一下三层架构。
三层架构(3-tier architecture) 通常意义上的三层架构就是将整个业务应用划分为:界面层(User Interface layer)、业务逻辑层(Business Logic Layer)、数据访问层(Data access layer)。
UI层:即表示层,就是展现给用户看到的界面
BLL:即业务逻辑层,就是实现功能的,用来写方法及其调用
DAL:即数据访问层,也就是说,是对数据库的操作。主要是存放对数据类的访问,即对数据库的添加、删除、修改、更新等基本操作
model(模型层)作为承载数据的媒介
简单的案例介绍
建立数据表,主要存放登录的用户名和密码!
在App.config中配置数据库连接信息。
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionStrings>
<add name="Conn" connectionString="Data Source=数据库名;Initial Catalog=;user id=登录数据库用户名;Password=登陆密码"/>
</connectionStrings>
</configuration>
//另一种连接方法
<appSettings>
<add key="conn" value="server=服务器名;database=数据库名;uid=用户名;password=密码;"/>
</appSettings>
DAL层(与数据库的交互)
建立SQLhelper帮助类:用于操作数据库,别人写好的,会用就行!
SQLhelper帮助类
using System;
using System.Data;
using System.Collections;
using System.Configuration; //记得这个asp默认没引用,你要去添加引用的程序集里引用进来
using System.Linq;
using System.Web;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace UMS.DbHelper
{
/// <summary>
/// SQL数据库操作类
/// </summary>
public class SQLHelper
{
private string strConn;
private SqlConnection sqlConn = null;
public SQLHelper()
{
strConn = ConfigurationManager.ConnectionStrings["UserData"].ConnectionString;
sqlConn = new SqlConnection(strConn);
}
/// <summary>
/// 打开数据库连接
/// </summary>
private void OpenConn()
{
if (sqlConn != null && sqlConn.State == ConnectionState.Closed)
{
sqlConn.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
private void CloseConn()
{
if (sqlConn != null && sqlConn.State == ConnectionState.Open)
{
sqlConn.Close();
}
}
/// <summary>
/// 构造操作命令
/// </summary>
/// <param name="cmdText">带参命令</param>
/// <param name="param">参数数组</param>
/// <param name="values">参数值数组</param>
/// <returns></returns>
private SqlCommand CreateCommand(string cmdText, string[] param, object [] values)
{
SqlCommand myCmd = new SqlCommand(cmdText,sqlConn);
for (int i = 0; i < param.Length; i++)
{
myCmd.Parameters.AddWithValue(param[i],values[i]);
}
return myCmd;
}
/// <summary>
/// 根据SQL指令返回相应查询阅读器,在阅读器使用完后请及时关闭
/// </summary>
/// <param name="cmdText">查询语句</param>
/// <param name="param">参数列表,无参可设置为null</param>
/// <param name="values">参数值列表,只有当参数不为空时有效</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string cmdText,string [] param,object [] values)
{
OpenConn();
SqlCommand myCmd;
if (param != null)
{
myCmd = this.CreateCommand(cmdText, param, values);
}
else
{
myCmd = new SqlCommand(cmdText,sqlConn);
}
return myCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 根据存储过程返回相应查询阅读器,在阅读器使用完后请及时关闭
/// </summary>
/// <param name="cmdText">存储过程名</param>
/// <param name="parms">参数列表</param>
/// <returns></returns>
public SqlDataReader ExecuteReaderBySP(string cmdText, SqlParameter[] parms)
{
OpenConn();
SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
myCmd.CommandType = CommandType.StoredProcedure;
if (parms != null)
{
myCmd.Parameters.AddRange(parms);
}
return myCmd.ExecuteReader(CommandBehavior.CloseConnection);
}
/// <summary>
/// 根据SQL指令返回受影响行数,主要用于数据库的更新、插入、删除等操作
/// </summary>
/// <param name="cmdText">sql命令语句</param>
/// <param name="param">参数数组,若没有参数可以设置为空</param>
/// <param name="values">参数值数组,只有当param不为空时有效</param>
/// <returns></returns>
public int ExecuteNoneQuery(string cmdText, string[] param, object[] values)
{
OpenConn();
SqlCommand myCmd;
if (param != null)
{
myCmd = this.CreateCommand(cmdText, param, values);
}
else
{
myCmd = new SqlCommand(cmdText,sqlConn);
}
try
{
return myCmd.ExecuteNonQuery();
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConn();
}
}
/// <summary>
/// 根据SQL指令返回第一行第一列结果
/// </summary>
/// <param name="cmdText">sql命令语句</param>
/// <param name="param">参数数组,若没有参数可以设置为空</param>
/// <param name="values">参数值数组,只有当param不为空时有效</param>
/// <returns></returns>
public object ExecuteScalar(string cmdText, string[] param, object[] values)
{
OpenConn();
SqlCommand myCmd;
if (param != null)
{
myCmd = this.CreateCommand(cmdText, param, values);
}
else
{
myCmd = new SqlCommand(cmdText,sqlConn);
}
try
{
return myCmd.ExecuteScalar();
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConn();
}
}
/// <summary>
/// 带事务执行存储过程,该方法主要用于执行用于数据维护类的存储过程执行
/// </summary>
/// <param name="cmdText">存储过程名称</param>
/// <param name="parms">SQL参数数组</param>
public int ExecuteNoneQueryBySP(string cmdText, SqlParameter[] parms)
{
OpenConn();
SqlTransaction tran = sqlConn.BeginTransaction();
SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
myCmd.CommandType = CommandType.StoredProcedure;
if (parms != null)
{
myCmd.Parameters.AddRange(parms);
}
myCmd.Transaction = tran;
try
{
int result=myCmd.ExecuteNonQuery();
tran.Commit();
return result;
}
catch (Exception ex)
{
tran.Rollback();
throw ex;
}
finally
{
CloseConn();
}
}
/// <summary>
/// 根据命令语句返回数据集
/// </summary>
/// <param name="cmdText">命令语句</param>
/// <param name="param">参数数组,若没有参数可以设置为空</param>
/// <param name="values">参数值数组,只有当param不为空时有效</param>
/// <returns></returns>
public DataSet FillDataSet(string cmdText, string[] param, object[] values)
{
OpenConn();
SqlCommand myCmd;
if (param != null)
{
myCmd = this.CreateCommand(cmdText, param, values);
}
else
{
myCmd = new SqlCommand(cmdText,sqlConn);
}
SqlDataAdapter myAdp = new SqlDataAdapter(myCmd);
DataSet ds = new DataSet();
try
{
myAdp.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConn();
}
}
/// <summary>
/// 执行特定存储过程并返回查询后的数据结果,该方法用于执行查询类的存储过程
/// </summary>
/// <param name="cmdText">存储过程名</param>
/// <param name="parms">SQL参数数组,若没有参数可以设置为空</param>
/// <returns></returns>
public DataSet FillDataSetBySP(string cmdText, SqlParameter[] parms)
{
OpenConn();
SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
myCmd.CommandType = CommandType.StoredProcedure;
if (parms != null)
{
myCmd.Parameters.AddRange(parms);
}
SqlDataAdapter myAdp = new SqlDataAdapter(myCmd);
DataSet ds = new DataSet();
try
{
myAdp.Fill(ds);
return ds;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConn();
}
}
/// <summary>
/// 执行存储过程返回输出参数
/// </summary>
/// <param name="cmdText">存储过程名</param>
/// <param name="parms">参数数组</param>
/// <returns>包含所有输出值的ArrayList</returns>
public ArrayList ExecuteSp(string cmdText, SqlParameter[] parms)
{
OpenConn();
SqlCommand myCmd = new SqlCommand(cmdText, sqlConn);
myCmd.CommandType = CommandType.StoredProcedure;
if (parms != null)
{
myCmd.Parameters.AddRange(parms);
}
try
{
myCmd.ExecuteNonQuery();
ArrayList al = new ArrayList();
for (int i = 0; i < parms.Length; i++)
{
if (parms[i].Direction == ParameterDirection.Output)
{
al.Add(parms[i]);
}
}
return al;
}
catch (Exception ex)
{
throw ex;
}
finally
{
CloseConn();
}
}
#region 批处理操作
/// <summary>
/// 批量数据导入操作
/// </summary>
/// <param name="dt">要批量导入的数据表</param>
/// <param name="destTableName">目标表名</param>
/// <param name="columnMappings">列映射集合</param>
public void BulkInsert(DataTable dt, string destTableName, List<SqlBulkCopyColumnMapping> columnMappings)
{
SqlBulkCopy bulkCopy = new SqlBulkCopy(this.sqlConn);
bulkCopy.DestinationTableName = destTableName;
bulkCopy.BatchSize = dt.Rows.Count;
foreach (SqlBulkCopyColumnMapping map in columnMappings)
{
bulkCopy.ColumnMappings.Add(map);
}
try
{
OpenConn();
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
this.CloseConn();
bulkCopy.Close();
}
}
#endregion
}
}
建立DBbase类,用于连接数据库。
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
class DBbase
{
//读取配置文件 连接数据库语句
public static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["Conn"].ConnectionString;
SqlConnection con = new SqlConnection(strCon);
//检测连接是否打开
public void chkConnection()
{
if (this.con.State == ConnectionState.Closed)
{
this.con.Open();
}
}
//执行语句,返回该语句查询的数据行的总行数
public int returnRowCount(string strSQL)
{
chkConnection();
try
{
SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].Rows.Count;
}
catch
{
return 0;
}
}
}
}
建立userDAL类,用于写操作数据的功能的方法(登录,增删改查等)。
userDAL类:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
class DBbase
{
//读取配置文件 连接数据库语句
public static string strCon = System.Configuration.ConfigurationManager.ConnectionStrings["Connect_MainDb"].ConnectionString;
//实例化连接对象 con
SqlConnection con = new SqlConnection(strCon);
//检测连接是否打开
public void chkConnection()
{
if (this.con.State == ConnectionState.Closed)
{
this.con.Open();
}
}
//执行语句,返回该语句查询的数据行的总行数
public int returnRowCount(string strSQL)
{
chkConnection();
try
{
SqlDataAdapter da = new SqlDataAdapter(strSQL, con);
DataSet ds = new DataSet();
da.Fill(ds);
return ds.Tables[0].Rows.Count;
}
catch
{
return 0;
}
}
}
}
Model层:model层其实就相当于一个中转站,用来存储用到的数据,贯穿三层,数据的赋值及提取。
建立userInfo类。
//用get,set方法,主要用于数据的赋值及提取,操作数据!
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace Model
{
public class userInfo
{
public string username { get; set; }
public string password { get; set; }
}
}
BLL层:业务逻辑,与客户端交互,主要写客户端用到的方法。
建立userBLL类:引用DAL和Model
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace BLL
{
public class userBLL
{
DAL.userDAL d_userAccess = new DAL.userDAL();
//登录的方法
public int userLogin(Model.userInfo m_userInfo)//model层的值传过来
{
//通过DAL层从数据中取出数据,与Model层的数据对比,返回行数,若存在返回值,则匹配成功
return d_userAccess.userLogin(m_userInfo.username, m_userInfo.password);//如果有返回值则登录成功
}
}
}
UI层:主要为前台界面
建立Windows窗体,用户登录界面为:
用户名:textbox1;密码:textbox2 登录:botton
具体实现为:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
namespace Emms
{
public partial class 登录界面 : Form
{
public 登录界面()
{
InitializeComponent();
}
//实例化model层中 userInfo类用于传递数据
Model.userInfo m_userInfo = new Model.userInfo();
//实例化BLL层中 userAccess方法衔接用户输入与数据库匹配
BLL.userBLL b_userAccess = new BLL.userBLL();
private void button1_Click(object sender, EventArgs e)
{
//获取username和password数据
m_userInfo.username = textBox1.Text.Trim().ToString();
m_userInfo.password = textBox2.Text.Trim().ToString();
//useLogin调用返回记录条数 大于1 则账号密码正确
if (b_userAccess.userLogin(m_userInfo) > 0)
{
MessageBox.Show("登录成功");
}
else
{
MessageBox.Show("登录失败");
}
}
}
}
至此案例便完成了,主要为UI层调用BLL层的方法,BLL层再通过DAL层获取数据,如有错误,欢迎大佬指正~
运行界面为:
目录结构为: