我们来综合前面所学的所有知识，顺便来解答一下在特性那一章留下来的作业。
首先我们来写一个抽象特性类作为其他特性的基类，并且需要实现一个Validate的这么一个方法：
public abstract class AbstractValidateAttribute: Attribute
{
public abstract bool Validate(object value);
}
我们只需要新建一个以Attribute结尾的类并且让他继承上面的AbstractValidateAttribute类，通用模板为：
public class FunctionAttribute: AbstractValidateAttribute
{
public abstract bool Validate(object value)
{
// TODO：实现逻辑
}
}
比如我们需要验证邮箱：
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public class EmailAttribute: AbstractValidateAttribute
{
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public override bool Validate(object value)
{
return oValue != null && Regex.IsMatch(value.ToString(), @"^\w+([-+.]\w+)*@\w+([-.]\w+)*.\w+([-.]\w+)*$");
}
}
我们需要验证数据库字段长度是否符合我们的要求：
[AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)]
public class LengAttribute : AbstractValidateAttribute
{
private int _Min = 0;
private int _Max = 0;
public LengAttribute(int min, int max)
{
this._Min = min;
this._Max = max;
}
public override bool Validate(object value)
{
if (value != null && !string.IsNullOrWhiteSpace(value.ToString()))
{
int length = value.ToString().Length;
if (length > this._Min && length < this._Max)
{
return true;
}
}
return false;
}
}
我们如果实体类字段和数据库字段名不一致，则需要使用Mapping进行映射：
[Column("State")]
public int Status { get; set; }
[AttributeUsage(AttributeTargets.Property)]
public class ColumnAttribute: Attribute
{
private string _Name = default;
public ColumnAttribute(string name)
{
this._Name = name;
}
public string GetColumnName()
{
return this._Name;
}
}
我们有时候还需要获得这个属性的字段名和验证这个数据是否正确，我们写一个AttributeHelper的类。
public static class AttributeHelper
{
public static string GetColumnName(this PropertyInfo prop)
{
if (prop.IsDefined(typeof(ColumnAttribute), true))
{
ColumnAttribute attribute = (ColumnAttribute)prop.GetCustomAttribute(typeof(ColumnAttribute), true);
return attribute.GetColumnName();
}
else
{
return prop.Name;
}
}
public static bool Validate<T>(this T tModel) where T : BaseModel
{
Type type = tModel.GetType();
foreach (var prop in type.GetProperties())
{
if (prop.IsDefined(typeof(AbstractValidateAttribute), true))
{
object[] attributeArray = prop.GetCustomAttributes(typeof(AbstractValidateAttribute), true);
foreach (AbstractValidateAttribute attribute in attributeArray)
{
if (!attribute.Validate(prop.GetValue(tModel)))
{
return false;// 表示终止
// throw new Exception($"{prop.Name}的值{prop.GetValue(tModel)}不对");
}
}
}
}
return true;
}
}
公用组件Farmwork中肯定需要有一个基础类BaseModel作为所有的Model的父类，这样就可以统一管理他们。
namespace Ruanmou.Framework.Model
{
public class BaseModel
{
public int Id { get; set; }
}
}
我们还需要读取数据库连接信息，然后通过反射获得他们的类信息。
public class StaticConstant
{
/// <summary>
/// sqlserver数据库连接
/// </summary>
public static string SqlServerConnString = ConfigurationManager.ConnectionStrings["Customers"].ConnectionString;
private static string DALTypeDll = ConfigurationManager.AppSettings["DALTypeDll"];
public static string DALDllName = DALTypeDll.Split(',')[1];
public static string DALTypeName = DALTypeDll.Split(',')[0];
}
需要在核心程序（Console程序中）配置App.config
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<startup>
<supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5" />
</startup>
<connectionStrings>
<add name="Customers" connectionString="Data Source=localhost; Database=Test; User ID=sa; Password=#chen0215; MultipleActiveResultSets=True" providerName="System.Data.SqlClient" />
<!--ConfigurationManager.ConnectionStrings["SQL_DB_CONNECTION"]-->
</connectionStrings>
<appSettings>
<add key="DALTypeDll" value="Ruanmou.Libraries.DAL.BaseDAL,Ruanmou.Libraries.DAL"/>
</appSettings>
</configuration>
现在Farmwork组件写的差不多了，就能开始写其他类库了，比如Model层：
注意这里有写字段为可空类型，需要自己去掌握去判断哪个是可空的：
namespace Ruanmou.Libraries.Model
{
public class Company : BaseModel
{
[Leng(2, 8)]
public string Name { get; set; }
public int CreatorId { get; set; }
public DateTime CreateTime { get; set; }
/// 必须是可空类型，才能跟数据库对应，自己把可空类型写出来
public int? LastModifierId { get; set; }
public DateTime? LastModifyTime { get; set; }
}
}
namespace Ruanmou.Libraries.Model
{
public class User : BaseModel
{
public string Name { get; set; }
public string Account { get; set; }
public string Password { get; set; }
public string Email { get; set; }
public string Mobile { get; set; }
public int CompanyId { get; set; }
public string CompanyName { get; set; }
//public int State { get; set; }
[Column("State")]
public int Status { get; set; }
public int UserType { get; set; }
public DateTime LastLoginTime { get; set; }
public int CreatorId { get; set; } // 非空没有完成
public DateTime CreateTime { get; set; }
public int LastModifierId { get; set; }
public DateTime LastModifyTime { get; set; }
}
}
现在的Model层写完了可以写业务层了，先定义一个IDAL的接口层：
public interface IBaseDAL
{
T Find<T>(int id) where T: BaseModel;
List<T> FindAll<T>() where T: BaseModel;
void Update<T>(T t) where T: BaseModel;
void Insert<T>(T t) where T: BaseModel;
void Delete<T>(int id) where T: BaseModel;
}
然后我们来实现这个接口，我们先来看看Find接口：
public T Find<T>(int id) where T: BaseModel
{
Type type = typeof(T);
string columnString = string.Join(",", type.GetProperties().Select(p => $"{p.GetColumnName()}"));
string sql = $"SELECT {columnString} FROM [{type.Name}] WHERE Id = {id}";
T t = (T)Activator.CreateInstance(type);
using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
conn.Open();
SqlDataReader reader = command.ExecuteReder();
if (reader.Read())
{
foreach (var prop in type.GetProperties())
{
prop.SetValue(t, reader[prop.Name] ?? DBNull.Value )
}
}
}
}
}
public List<T> FindAll<T>() where T : BaseModel
{
Type type = typeof(T);
string columnString = string.Join(",", type.GetProperties().Select(p => $"{p.GetColumnName()}"));
string sql = $"SELECT {columnString} FROM [{type.Name}]";
List<T> list = new List<T>();
using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
{
SqlCommand command = new SqlCommand(sql, conn);
conn.Open();
SqlDataReader reader = command.ExecuteReader();
list = this.ReaderToList<T>(reader);
}
return list;
}
而ReaderToList如下：
#region PrivateMethod
private List<T> ReaderToList<T>(SqlDataReader reader) where T : BaseModel
{
Type type = typeof(T);
List<T> list = new List<T>();
while (reader.Read())
{
T t = (T)Activator.CreateInstance(type);
foreach (var prop in type.GetProperties())
{
object oValue = reader[prop.GetColumnName()];
prop.SetValue(t, oValue is DBNull ? null: oValue);
}
list.Add(t);
}
return list;
}
#endregion
我们来看一下修改：
public void Update<T>(T t) where T : BaseModel
{
// update 表名 set 字段名1=值1,字段名2=值2,....[where 条件];
if (!t.Validate<T>())
{
throw new Exception("数据不正确");
}
Type type = typeof(T);
var propArray = type.GetProperties().Where(p => !p.Name.Equals("Id"));
string columnString = string.Join(",", propArray.Select(p => $"[{p.GetColumnName()}]=@{p.GetColumnName()}"));
var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
string sql = $"UPDATE [{type.Name}] SET {columnString} WHERE Id={t.Id}";
using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddRange(parameters);
conn.Open();
int iResult = command.ExecuteNonQuery();
if (iResult == 0)
{
throw new Exception("Update数据不存在");
}
}
}
}
还有插入：
public void Insert<T>(T t) where T : BaseModel
{
// insert into 表名(字段名1,字段名2,.....) values(值1,值2,......);
//Type type = typeof (T);
//var propArray = type.GetProperties().Where(p => !p.Name.Equals("Id"));
//string columnString = string.Join(",", propArray.Select(p => $"[{p.GetColumnName()}]"));
//string valueString = string.Join(",", propArray.Select(p => $"@{p.GetColumnName()}"));
//var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
//string sql = $"INSERT INTO [{type.Name}] ({columnString}) VALUES ({valueString})";
//using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
//{
// using (SqlCommand command = new SqlCommand(sql, conn))
// {
// command.Parameters.AddRange(parameters);
// conn.Open();
// int iResult = command.ExecuteNonQuery();
// if (iResult == 0)
// {
// throw new Exception("Insert 失败");
// }
// }
//}
Type type = typeof(T);
var propArray = type.GetProperties().Where(p => !p.Name.Equals("Id"));
string columnString = string.Join(", ", propArray.Select(p => $"[{p.GetColumnName()}]"));
string valueString = string.Join(",", propArray.Select(p => $"@{p.GetColumnName()}"));
string sql = $"INSERT INTO [{type.Name}] ({columnString}) VALUES ({valueString})";
var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();
using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
{
using(SqlCommand command = new SqlCommand(sql, conn))
{
command.Parameters.AddRange(parameters);
conn.Open();
int iReuslt = command.ExecuteNonQuery();
if (iReuslt == 0)
{
throw new Exception("Insert 失败");
}
}
}
}
再来个删除：
public void Delete<T>(int id) where T : BaseModel
{
// delete from 表名 [where 条件];
Type type = typeof(T);
string sql = $"DELETE FROM [{type.Name}] WHERE Id = {id}";
using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
conn.Open();
var iResult = command.ExecuteNonQuery();
if (iResult == 0)
{
throw new Exception("删除失败");
}
}
}
}
上面代码太长了，能不能优化一下，我们写了一个简单的SqlHelper进行代码简化：
namespace LeadDream.Libaries.DAL
{
/// <summary>
/// SqlHelper类
/// </summary>
public class SqlHelper
{
// 如果项目中只连接了一个数据库 那么可以在此定义一个固定的连接字符串
private static string connectionStr = ConfigurationManager.ConnectionStrings["SQLConnString"].ConnectionString;
#region ExecuteNonQuery
/// <summary>
/// 执行sql语句 返回受影响行数
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="cmdType">command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行sql语句 返回当前插入的记录id(其实是ExecuteScalar转换成id返回)
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="cmdType">command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="returnID">返回id</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, out int returnID, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection conn = new SqlConnection(connectionString))
{
returnID = 0;
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
returnID = (Int32)cmd.ExecuteScalar();
cmd.Parameters.Clear();
return returnID;
}
}
/// <summary>
/// 执行sql语句 返回受影响行数(数据库连接SqlConnection)
/// </summary>
/// <param name="connection">SqlConnection连接</param>
/// <param name="cmdType">command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 执行sql语句 返回受影响行数(事务SqlTransaction)
/// </summary>
/// <param name="trans">SqlTransaction事务</param>
/// <param name="cmdType">command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static int ExecuteNonQuery(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
int val = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return val;
}
#endregion
#region ExecuteScalar
/// <summary>
/// 执行sql语句 返回结果集第一行第一列
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static object ExecuteScalar(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
using (SqlConnection connection = new SqlConnection(connectionString))
{
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
}
/// <summary>
/// 执行sql语句 返回结果集第一行第一列(事务)
/// </summary>
/// <param name="trans">SqlTransaction事务</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static object ExecuteScalar(SqlTransaction trans, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, trans.Connection, trans, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
/// <summary>
/// 执行sql语句 返回结果集第一行第一列(数据库连接)
/// </summary>
/// <param name="connection">SqlConnection连接</param>
/// <param name="cmdType">Command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static object ExecuteScalar(SqlConnection connection, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, null, cmdType, cmdText, commandParameters);
object val = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return val;
}
#endregion
#region ExecuteReader
/// <summary>
/// 执行sql语句 返回reader
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="cmdType">command类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static SqlDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection(connectionString);
try
{
PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return rdr;
}
catch
{
conn.Close();
throw;
}
}
#endregion
#region DataSet
/// <summary>
/// 执行sql语句 返回DataSet结果集(无参数)
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandType">command类型</param>
/// <param name="commandText">sql语句</param>
/// <returns></returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行sql语句 返回DataSet结果集
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandType">command类型</param>
/// <param name="commandText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//创建数据库连接 完成后dispose
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
//根据数据库连接 调用具体方法
return ExecuteDataset(cn, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 执行sql语句 返回DataSet结果集(数据库连接SqlConnection)
/// </summary>
/// <param name="connection">SqlConnection连接</param>
/// <param name="commandType">command类型</param>
/// <param name="commandText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
//创建command
SqlCommand cmd = new SqlCommand();
//准备command对象
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
//创建SqlDataAdapter和DataSet
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
//把结果集装进dataset
da.Fill(ds);
//返回
return ds;
}
#endregion DataSet
#region DataTable
/// <summary>
/// 执行sql语句 返回DataTable结果集(无参数)
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandType">command类型</param>
/// <param name="commandText">sql语句</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText)
{
return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null);
}
/// <summary>
/// 执行sql语句 返回DataTable结果集
/// </summary>
/// <param name="connectionString">连接字符串</param>
/// <param name="commandType">command类型</param>
/// <param name="commandText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
cn.Open();
return ExecuteDataTable(cn, commandType, commandText, commandParameters);
}
}
/// <summary>
/// 执行sql语句 返回DataTable结果集
/// </summary>
/// <param name="connection">SqlConnection连接</param>
/// <param name="commandType">command类型</param>
/// <param name="commandText">sql语句</param>
/// <param name="commandParameters">参数</param>
/// <returns></returns>
public static DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters)
{
SqlCommand cmd = new SqlCommand();
PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}
#endregion DataTable
#region PrepareCommand
/// <summary>
/// 准备Command对象
/// </summary>
/// <param name="cmd">SqlCommand 对象</param>
/// <param name="conn">SqlConnection 对象</param>
/// <param name="trans">SqlTransaction 对象</param>
/// <param name="cmdType">Command 类型</param>
/// <param name="cmdText">sql语句</param>
/// <param name="cmdParms">参数</param>
private static void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
{
//打开SqlConnection连接
if (conn.State != ConnectionState.Open)
conn.Open();
//给Command的Connection CommandText设置值
cmd.Connection = conn;
cmd.CommandText = cmdText;
//如果是事务 设置事务
if (trans != null)
cmd.Transaction = trans;
//Command类型
cmd.CommandType = cmdType;
//Command参数
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
//在此可以操作参数 比如把参数值里的英文逗号全部改为中文逗号 或者其他操作
//switch (parm.SqlDbType)
//{
// case SqlDbType.Char:
// case SqlDbType.NChar:
// case SqlDbType.NText:
// case SqlDbType.NVarChar:
// case SqlDbType.Text:
// case SqlDbType.VarChar:
// if (parm.Value != null && parm.Value != DBNull.Value && parm.ParameterName != "@SQLClause")
// {
// string tmp = parm.Value.ToString();
// tmp = tmp.Replace(",", "，");
// tmp = tmp.Replace("'", "＇");
// parm.Value = tmp;
// }
// break;
//}
//把参数添加到Command中的Parameters中
cmd.Parameters.Add(parm);
}
}
}
#endregion
}
}
其实我们还可以通过委托解耦去掉重复代码
private T ExecuteSql<T>(string sql, Func<SqlCommand, T> func)
{
using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString))
{
using (SqlCommand command = new SqlCommand(sql, conn))
{
conn.Open();
SqlTransaction sqlTransaction = conn.BeginTransaction();
try
{
command.Transaction = sqlTransaction;
//command.Parameters.AddRange(parameters);
T tResult = func.Invoke(command);
sqlTransaction.Commit();
return tResult;
//int iResult = command.ExecuteNonQuery();
//if (iResult == 0)
// throw new Exception("Update数据不存在");
}
catch (Exception ex)
{
sqlTransaction.Rollback();
throw;
}
}
}
}
