手写 ORM 探究

1. ORM是什么：
object relation mapping:orm
对象-关系映射
对象类--数据库表数据结构

2.常见的框架：
1.nhibernate
2.ef6
3.linq to sql
4.dapper
5.sugar,
6.martsql
7.EFCORE

3.CRUD增删改查,事务，延迟，缓存

4.手写ORM
自动生成sql,通用

5.读写分离：
主库：增删改 20%
从库：查询 80%，可以多个
主从库：发布订阅 ，发布服务器

6. 事务，缓存，表达式目录数解析多条件查询
可用性+最终一致性

7.分布式事务：CAP
分区容错
一致性
可用性

{
"connectionString": "Data Source=XYZPC; Database=Customers; User ID=sa; Password=Passw0rd; MultipleActiveResultSets=True"
}

/// <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;
}
}
}

 -------------------find------------------

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);
}
}
}

----------------insert-----------------

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;
}

}

------------------------find,insert     sql-----------------------

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});";

}
}