本文介绍了三层应用架构设计的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我有三层应用程序如下：
I have three layer application as follows:
Presentation > Entities (VO) > Business Layer (BAL) > Data Layer (DAL)
我不喜欢这个解决方案中的内容：
- 在BAL类中仍有System.Data参考
- 每个BAL类需要DAL类
- 每个VO实体都有自己的BAL类，每个BAL类都包含它自己的DAL类。是否可以为所有BAL类创建一个通用DAL类，而不是每次为BAL创建DAL？
- 在BAL中使用System.Data是否可以？
public partial class FrmLogin : Form
{
private readonly UserBal _userBal;
public FrmLogin()
{
InitializeComponent();
_userBal = new UserBal();
}
private void btnSearch_Click(object sender, EventArgs e)
{
var userVo = _userBal.SearchByName(txtUsername.Text);
}
}
VO示例类：
VO example class:
public class UserVo
{
public int IdUser { get; set; }
public string Firstname { get; set; }
public string Lastname { get; set; }
public string Email { get; set; }
}
BAL示例类：
BAL example class:
public class UserBal
{
private readonly UserDal _userDal;
public UserBal()
{
_userDal = new UserDal();
}
public UserVo SearchByName(string name)
{
var userVo = new UserVo();
var dataTable = _userDal.SearchByName(name);
foreach (DataRow dr in dataTable.Rows)
{
userVo.IdUser = int.Parse(dr["t01_id"].ToString());
userVo.Firstname = dr["t01_firstname"].ToString();
userVo.Lastname = dr["t01_lastname"].ToString();
userVo.Email = dr["t01_email"].ToString();
}
return userVo;
}
public UserVo SearchById(string _id)
{
var userVo = new UserVo();
var dataTable = _userDal.SearchById(_id);
foreach (DataRow dr in dataTable.Rows)
{
userVo.IdUser = int.Parse(dr["t01_id"].ToString());
userVo.Firstname = dr["t01_firstname"].ToString();
userVo.Lastname = dr["t01_lastname"].ToString();
userVo.Email = dr["t01_email"].ToString();
}
return userVo;
}
}
DAL示例类：
DAL example class:
public class UserDal
{
private readonly DbManager _dbManager;
public UserDal()
{
_dbManager = new DbManager("DBConnection");
}
public DataTable SearchByName(string username)
{
var parameters = new List<IDbDataParameter>
{
_dbManager.CreateParameter("@FirstName", 50, username, DbType.String),
};
//_dbManager.SearchByName returns DataTable object
return _dbManager.SearchByName("SELECT * FROM tbUsers WHERE FirstName=@FirstName", CommandType.Text, parameters.ToArray());
}
public DataTable SearchById(string id)
{
var parameters = new List<IDbDataParameter>
{
_dbManager.CreateParameter("@Id", 50, id, DbType.Int32),
};
//_dbManager.SearchById returns DataTable object
return _dbManager.SearchById("SELECT * FROM tbUsers WHERE Id=@Id", CommandType.Text, parameters.ToArray());
}
}
我的尝试：
如上所示代码的当前设计
What I have tried:
as above shown current design of code
推荐答案
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace DataAccess
{
public class SqlServer : IDisposable
{
// Flag: Has Dispose already been called?
bool disposed = false;
protected Dictionary<int, SqlParameter> _parameters { get; set; }
protected int _commandTimeout { get; set; }
/// <summary>
/// Name of connection in config file
/// </summary>
public string ConnectionName { get; set; }
/// <summary>
/// Full connectionstring
/// </summary>
public string ConnectionString { get; set; }
public enum ReturnType
{
DataTable,
DataReader,
Scalar,
DataSet,
NoResult
}
/// <summary>
/// Instantiate object
/// </summary>
public SqlServer()
:this("DefaultConnection")
{
}
/// <summary>
/// Instantiate object
/// </summary>
/// <param name="connectionName">Connection Name attribute in app or web config file</param>
public SqlServer(string connectionName)
{
ConnectionName = connectionName;
}
/// <summary>
/// Get SQL Connection
/// </summary>
/// <param name="connectionName">Connection Name attribute in app or web config file</param>
/// <returns>SQL Connection</returns>
private SqlConnection getConnection(string connectionName)
{
SqlConnection sqlConn;
string connString;
try
{
connString = (ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString != null) ? ConfigurationManager.ConnectionStrings[ConnectionName].ConnectionString : ConnectionString;
sqlConn = new SqlConnection(connString);
sqlConn.Open();
}
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
return sqlConn;
} // private SqlConnection getConnection()
/// <summary>
/// Adds the parameters to a SQL command
/// </summary>
/// <param name="commandText">The SQL query to execute</param>
/// <param name="parameters">Parameters to pass to the SQL query</param>
private static void AddParameters(SqlCommand command, Dictionary<string, object> parameters)
{
if (parameters == null)
{
return;
}
foreach (var param in parameters)
{
var parameter = command.CreateParameter();
parameter.ParameterName = param.Key;
parameter.Value = param.Value ?? DBNull.Value;
command.Parameters.Add(parameter);
}
}
/// <summary>
/// Executes SQL Statement
/// </summary>
/// <param name="SQL">SQL String</param>
/// <param name="parms">Dictionary of sql parameters, collection expects the dictionary key to start with 1 and go in sequential order.</param>
/// <param name="returnDataType">Enum of datatype you want returned.</param>
/// <returns>Specified returnDataType Object</returns>
public object executeSQL(string SQL, ReturnType returnDataType)
{
SqlCommand sqlComm = new SqlCommand();
SqlDataAdapter sqlDA = null;
try
{
sqlComm = new SqlCommand(SQL, getConnection(ConnectionName));
sqlComm.CommandType = CommandType.Text;
switch (returnDataType)
{
case ReturnType.DataReader:
return sqlComm.ExecuteReader();
case ReturnType.DataTable:
DataTable dtResult = new DataTable();
sqlDA = new SqlDataAdapter(sqlComm);
sqlDA.Fill(dtResult);
return dtResult;
case ReturnType.DataSet:
sqlDA = new SqlDataAdapter(sqlComm);
DataSet dsResult = new DataSet();
sqlDA.Fill(dsResult);
return dsResult;
case ReturnType.Scalar:
return sqlComm.ExecuteScalar();
case ReturnType.NoResult:
return sqlComm.ExecuteNonQuery();
default:
return sqlComm.ExecuteReader();
}//end switch
} //end try
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlComm != null)
{
//can't close connection on a datareader
if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
sqlComm.Dispose();
}
if (sqlDA != null) { sqlDA.Dispose(); }
}
} //public object executeSQL
/// <summary>
/// Executes SQL Statement
/// </summary>
/// <param name="SQL">SQL String</param>
/// <param name="parms">Dictionary of sql parameters, collection expects the dictionary key to start with 1 and go in sequential order.</param>
/// <param name="returnDataType">Enum of datatype you want returned.</param>
/// <returns>Specified returnDataType Object</returns>
public object executeSQL(string SQL, Dictionary<string,object> parameters, ReturnType returnDataType)
{
SqlCommand sqlComm = new SqlCommand();
SqlDataAdapter sqlDA = null;
try
{
sqlComm = new SqlCommand(SQL, getConnection(ConnectionName));
sqlComm.CommandType = CommandType.Text;
AddParameters(sqlComm, parameters);
switch (returnDataType)
{
case ReturnType.DataReader:
return sqlComm.ExecuteReader();
case ReturnType.DataTable:
DataTable dtResult = new DataTable();
sqlDA = new SqlDataAdapter(sqlComm);
sqlDA.Fill(dtResult);
return dtResult;
case ReturnType.DataSet:
sqlDA = new SqlDataAdapter(sqlComm);
DataSet dsResult = new DataSet();
sqlDA.Fill(dsResult);
return dsResult;
case ReturnType.Scalar:
return sqlComm.ExecuteScalar();
case ReturnType.NoResult:
return sqlComm.ExecuteNonQuery();
default:
return sqlComm.ExecuteReader();
}//end switch
} //end try
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlComm != null)
{
//can't close connection on a datareader
if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
sqlComm.Dispose();
}
if (sqlDA != null) { sqlDA.Dispose(); }
}
} //public object executeSQL
/// <summary>
/// Executes stored procedure
/// </summary>
/// <param name="storedProcName">Name of stored procedure</param>
/// <param name="parms">Dictionary of sql parameters, collection expects the dictionary key to start with 1 and go in sequential order.</param>
/// <param name="returnDataType">Enum of datatype you want returned.</param>
/// <returns>Specified returnDataType Object</returns>
public object executeProcedure(string storedProcName, Dictionary<int, SqlParameter> parms, ReturnType returnDataType)
{
SqlCommand sqlComm = new SqlCommand();
SqlDataAdapter sqlDA = null;
try
{
sqlComm = new SqlCommand(storedProcName, getConnection(ConnectionName));
sqlComm.CommandType = CommandType.StoredProcedure;
for (int i = 1; i <= parms.Count; i++)
{
sqlComm.Parameters.Add(parms[i]);
}
switch (returnDataType)
{
case ReturnType.DataReader:
return sqlComm.ExecuteReader();
case ReturnType.DataTable:
DataTable dtResult = new DataTable();
sqlDA = new SqlDataAdapter(sqlComm);
sqlDA.Fill(dtResult);
return dtResult;
case ReturnType.DataSet:
sqlDA = new SqlDataAdapter(sqlComm);
DataSet dsResult = new DataSet();
sqlDA.Fill(dsResult);
return dsResult;
case ReturnType.Scalar:
return sqlComm.ExecuteScalar();
case ReturnType.NoResult:
return sqlComm.ExecuteNonQuery();
default:
return sqlComm.ExecuteReader();
}//end switch
} //end try
catch (SqlException ex)
{
throw ex;
}
catch (Exception ex)
{
throw ex;
}
finally
{
if (sqlComm != null)
{
//can't close connection on a datareader
if (sqlComm.Connection.State == ConnectionState.Open & returnDataType != ReturnType.DataReader) { sqlComm.Connection.Close(); }
sqlComm.Dispose();
}
if (sqlDA != null) { sqlDA.Dispose(); }
}
} //public object executeProcedure
// Dispose() calls Dispose(true)
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
// Protected implementation of Dispose pattern.
protected virtual void Dispose(bool disposing)
{
if (disposed)
return;
if (disposing)
{
// Free any other managed objects here.
}
disposed = true;
}
}
}
这篇关于三层应用架构设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！