SqlHelper帮助类,通过反射生成
/// <summary>
/// 数据库查询帮助类库--自动生成Sql--通用
/// </summary>
public class SqlHelper
{
/// <summary>
/// 通用主键查询操作
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="id"></param>
/// <returns></returns>
public T Find<T>(int id) where T : BaseModel
{
Type type = typeof(T);
//string columnsString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));
//string sql = $"SELECT {columnsString} FROM [{type.GetMappingName()}] WHERE ID={id} ";string sql = $"{SqlBuilder<T>.GetFindSql()}{id}";
using (SqlConnection conn = new SqlConnection(ConfigrationManager.SqlConnectionString))
{
SqlCommand command = new SqlCommand(sql, conn);
conn.Open();
var reader = command.ExecuteReader();
if (reader.Read())
{
T t = (T)Activator.CreateInstance(type);
foreach (var prop in type.GetProperties())
{
string propName = prop.GetMappingName();//查询时as一下,可以省下一轮
prop.SetValue(t, reader[propName] is DBNull ? null : reader[propName]);//可空类型 设置成null而不是数据库查询的值
}
return t;
}
else
{
return default(T);
}
}
}public bool Insert<T>(T t) where T : BaseModel
{
Type type = t.GetType();
//string columnsString = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"[{p.GetMappingName()}]"));
//string valuesString = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"@{p.GetMappingName()}"));
//string sql = $"INSERT INTO [{type.GetMappingName()}] ({columnsString}) VALUES({valuesString});";//不能直接拼装值---Sql注入问题
string sql = SqlBuilder<T>.GetInsertSql();
var paraArray = type.GetProperties().Select(p => new SqlParameter($"@{p.GetMappingName()}", p.GetValue(t) ?? DBNull.Value)).ToArray();using (SqlConnection conn = new SqlConnection(ConfigrationManager.SqlConnectionString))
{
SqlCommand command = new SqlCommand(sql, conn);
command.Parameters.AddRange(paraArray);
conn.Open();
int iResult = command.ExecuteNonQuery();
return iResult == 1;
}
}
}/// <summary>
/// 用来完成sql语句的缓存
/// 每张表都是几个固定sql
/// 泛型缓存:适合不需要释放的 体积小点 不同类型不同数据
/// </summary>
public class SqlBuilder<T> where T : BaseModel
{
private static string _FindSql = null;
private static string _InsertSql = null;
static SqlBuilder()
{
Type type = typeof(T);
{
string columnsString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetMappingName()}]"));
_FindSql = $"SELECT {columnsString} FROM [{type.GetMappingName()}] WHERE ID= ";
}
{
string columnsString = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"[{p.GetMappingName()}]"));
string valuesString = string.Join(",", type.GetPropertiesWithoutKey().Select(p => $"@{p.GetMappingName()}"));
_InsertSql = $"INSERT INTO [{type.GetMappingName()}] ({columnsString}) VALUES({valuesString});";}
}
/// <summary>
/// 以Id= 结尾,可以直接添加参数
/// </summary>
/// <returns></returns>
public static string GetFindSql()
{
return _FindSql;
}
public static string GetInsertSql()
{
return _InsertSql;
}
}
过滤掉自定义属性,添加特性
/// <summary>
/// 数据库BaseModel
/// </summary>
public class BaseModel
{
[ZhaoxiKey]
public int Id { set; get; }
}[AttributeUsage(AttributeTargets.Property)]
public class ZhaoxiKeyAttribute : Attribute
{
}public static class FilterExtend
{
/// <summary>
/// 过滤掉主键 返回全部属性
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
public static IEnumerable<PropertyInfo> GetPropertiesWithoutKey(this Type type)
{
return type.GetProperties().Where(p => !p.IsDefined(typeof(ZhaoxiKeyAttribute), true));
}
}
表名和列名映射,添加特性
/// <summary>
/// 数据库是Company 但是程序是CompanyModel
/// </summary>
[ZhaoxiTable("Company")]
public class CompanyModel : BaseModel
{
[ZhaoxiColumn("Name")]
public string CompanyName { get; set; }public DateTime CreateTime { get; set; }
public int CreatorId { get; set; }
public Nullable<int> LastModifierId { get; set; }
public DateTime? LastModifyTime { get; set; }
}/// <summary>
/// 做表名称的别名
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class ZhaoxiTableAttribute : ZhaoxiAbstractMappingAttribute
{
public ZhaoxiTableAttribute(string tableName) : base(tableName)
{
}
}
[AttributeUsage(AttributeTargets.Property)]
public class ZhaoxiColumnAttribute : ZhaoxiAbstractMappingAttribute
{
public ZhaoxiColumnAttribute(string columnName):base(columnName)
{
}
}public abstract class ZhaoxiAbstractMappingAttribute : Attribute
{
private string _Name = null;
public ZhaoxiAbstractMappingAttribute(string name)
{
this._Name = name;
}public string GetName()
{
return this._Name;
}
}/// <summary>
///
/// </summary>
/// <param name="type">可以是type 也可以是property</param>
/// <returns></returns>
public static string GetMappingName(this MemberInfo type)
{
if (type.IsDefined(typeof(ZhaoxiAbstractMappingAttribute), true))
{
var attribute = type.GetCustomAttribute<ZhaoxiAbstractMappingAttribute>();
return attribute.GetName();
}
else
{
return type.Name;
}
}
读取新建的appsettings.json信息
/// <summary>
/// 固定读取根目录下面的appsettings.json
/// </summary>
public class ConfigrationManager
{
//有了IOC再去注入--容器单例
static ConfigrationManager()
{
var builder = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json");IConfigurationRoot configuration = builder.Build();
_SqlConnectionString = configuration["connectionString"];
}
private static string _SqlConnectionString = null;
public static string SqlConnectionString
{
get
{
return _SqlConnectionString;
}
}
}