private static string BuildGetSql(EntityMapper mapper, IOrmProvider provider)
{
StringBuilder sqlBuilder = new StringBuilder();
sqlBuilder.Append("SELECT ");
StringBuilder whereBuilder = new StringBuilder();
whereBuilder.Append(" WHERE ");
int i = 0;
foreach (var colMapper in mapper.MemberMappers.Values)
{
if (i > 0)
{
sqlBuilder.Append(",");
}
if (colMapper.IsPrimaryKey)
{
if (i > 0)
{
whereBuilder.Append(" AND ");
}
whereBuilder.Append(GetSetParameterSql(provider, colMapper));
}
sqlBuilder.Append(GetAliasParameterSql(provider, colMapper));
i++;
}
sqlBuilder.Append(" FROM " + provider.GetTableName(mapper.TableName));
return(sqlBuilder.ToString() + whereBuilder.ToString());
}
public DefaultHost(
IDbProvider dbProvider,
IOrmProvider ormProvider)
{
_dbProvider = dbProvider;
_ormProvider = ormProvider;
}
private static string BuildUpdateSql(EntityMapper mapper, IOrmProvider provider, string[] updateParameters)
{
var builder = new StringBuilder("UPDATE " + provider.GetTableName(mapper.TableName) + " SET ");
int index = 0;
foreach (var propName in updateParameters)
{
if (index > 0)
{
builder.Append(",");
}
builder.Append(GetSetParameterSql(provider, mapper.MemberMappers[propName]));
index++;
}
builder.Append(" WHERE ");
index = 0;
foreach (var colMapper in mapper.PrimaryKeys)
{
if (index > 0)
{
builder.Append(" AND ");
}
builder.Append(GetSetParameterSql(provider, colMapper));
index++;
}
return(builder.ToString());
}
private static string BuildCreateSql(EntityMapper mapper, IOrmProvider provider)
{
StringBuilder insertBuilder = new StringBuilder();
insertBuilder.Append("INSERT INTO " + provider.GetTableName(mapper.TableName) + " (");
StringBuilder valueBuilder = new StringBuilder();
valueBuilder.Append(") VALUES(");
int i = 0;
foreach (var colMapper in mapper.MemberMappers.Values)
{
if (colMapper.IsAutoIncrement)
{
continue;
}
if (i > 0)
{
insertBuilder.Append(",");
}
if (i > 0)
{
valueBuilder.Append(",");
}
insertBuilder.Append(provider.GetColumnName(colMapper.FieldName));
valueBuilder.Append(provider.ParamPrefix + colMapper.MemberName);
i++;
}
valueBuilder.Append(")");
return(insertBuilder.ToString() + valueBuilder.ToString());
}
public static void RegisterProvider(string connString, IOrmProvider provider, bool isDefaultProvider = true)
{
if (isDefaultProvider)
{
DefaultConnString = connString;
DefaultProvider = provider;
}
providers.Add(connString, provider);
}
static void Main(string[] args)
{
//生成简单查询脚本
var sqlProvider = SqlProvider.CreateProvider();
var sql = sqlProvider.Select("username", "realname", "age")
.From("sys_user").Where(new KeyValue()
{
Name = "username",
Value = "bouyei"
}).SqlString;
//结果:Select username,realname,age From sys_user Where username='******'
////ado.net 使用例子
string connectionString = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;
AdoProvider dbProvider = AdoProvider.CreateProvider(connectionString, ProviderType.SqlServer);
var adort = dbProvider.Query(new DbExecuteParameter()
{
CommandText = "select * from [user]"
});
DataTable dt = new DataTable();
var qrt = dbProvider.QueryToTable(new DbExecuteParameter("select * from [user]"), dt);
//entity framework 使用例子
IOrmProvider ormProvider = OrmProvider.CreateProvider("DbConnection");
try
{
User item = ormProvider.GetById <User>(1);
UserDto ud = new UserDto()
{
UserName = "******"
};
var query = ormProvider.Query <User>().FirstOrDefault();
//使用mapper修改对象
EntityMapper.MapTo <UserDto, User>(ud, item);
ormProvider.Update(item);
//保存修改
int rt = ormProvider.SaveChanges();
}
catch (Exception ex)
{
Console.Write(ex.Message);
}
}
private static string BuildDeleteSql(EntityMapper mapper, IOrmProvider provider)
{
StringBuilder builder = new StringBuilder();
builder.Append("DELETE FROM " + provider.GetTableName(mapper.TableName) + " WHERE ");
int i = 0;
foreach (var colMapper in mapper.PrimaryKeys)
{
if (i > 0)
{
builder.Append(" AND ");
}
builder.Append(GetSetParameterSql(provider, colMapper));
i++;
}
return(builder.ToString());
}
internal static string GetPagingCache(int hashKey, string connString, string sql, int pageIndex, int pageSize, string orderBy, IOrmProvider provider)
{
string result = String.Empty;
if (!PagingCache.TryGetValue(hashKey, out result))
{
result = provider.GetPagingExpression(sql, pageIndex * pageSize, pageSize, orderBy);
PagingCache.TryAdd(hashKey, result);
}
return(result);
}
internal static Action <IDbCommand, object> GetActionCache(int hashKey, string sql, Type paramType, IOrmProvider provider)
{
Action <IDbCommand, object> result;
if (!ActionCache.TryGetValue(hashKey, out result))
{
var mapper = new EntityMapper(Nullable.GetUnderlyingType(paramType) ?? paramType);
var colMappers = mapper.MemberMappers.Values.Where(p => Regex.IsMatch(sql, @"[?@:]" + p.MemberName + "([^a-z0-9_]+|$)", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.CultureInvariant));
result = CreateParametersHandler(provider.ParamPrefix, mapper);
ActionCache.TryAdd(hashKey, result);
}
return(result);
}
public HomeController()
{
dbProvider = Manager.dbProvider;
}
private static Action <IDbCommand, TEntity> GetActionCache(int hashKey, string sql, IOrmProvider provider, bool isPk)
{
Action <IDbCommand, TEntity> result;
if (!actionCache.TryGetValue(hashKey, out result))
{
if (isPk)
{
result = RepositoryHelper.CreateParametersHandler <TEntity>(provider.ParamPrefix, typeof(TEntity), Mapper.PrimaryKeys);
}
else
{
var colMappers = Mapper.MemberMappers.Values.Where(p => Regex.IsMatch(sql, @"[?@:]" + p.MemberName + "([^a-z0-9_]+|$)", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.CultureInvariant));
result = RepositoryHelper.CreateParametersHandler <TEntity>(provider.ParamPrefix, typeof(TEntity), colMappers);
}
actionCache.TryAdd(hashKey, result);
}
return(result);
}
private static string GetSqlCache(int hashKey, string connString, string sqlKey, IOrmProvider provider)
{
string result = sqlKey;
switch (sqlKey)
{
case "GET":
if (!sqlCache.TryGetValue(hashKey, out result))
{
result = BuildGetSql(Mapper, provider);
sqlCache.TryAdd(hashKey, result);
}
break;
case "CREATE":
if (!sqlCache.TryGetValue(hashKey, out result))
{
result = BuildCreateSql(Mapper, provider);
sqlCache.TryAdd(hashKey, result);
}
break;
case "DELETE":
if (!sqlCache.TryGetValue(hashKey, out result))
{
result = BuildDeleteSql(Mapper, provider);
sqlCache.TryAdd(hashKey, result);
}
break;
case "UPDATE":
if (!sqlCache.TryGetValue(hashKey, out result))
{
var list = Mapper.MemberMappers.Keys.Where(f => (!Mapper.PrimaryKeys.Select(m => m.MemberName).Contains(f))).ToArray();
result = BuildUpdateSql(Mapper, provider, list);
sqlCache.TryAdd(hashKey, result);
}
break;
}
return(result);
}
private static string GetAliasParameterSql(IOrmProvider provider, MemberMapper colMapper)
{
return(provider.GetColumnName(colMapper.FieldName) + " AS " + provider.GetPropertyName(colMapper.MemberName));
}
private static string GetSetParameterSql(IOrmProvider provider, MemberMapper colMapper)
{
return(provider.GetColumnName(colMapper.FieldName) + "=" + provider.ParamPrefix + colMapper.MemberName);
}
private static string GetUpdateFieldsSql <TFields>(Expression <Func <TEntity, TFields> > fieldsExpression, IOrmProvider provider)
{
var builder = new StringBuilder("UPDATE " + provider.GetTableName(Mapper.TableName) + " SET ");
var expression = ((LambdaExpression)fieldsExpression).Body;
if (expression.NodeType == ExpressionType.New)
{
var newExpression = expression as NewExpression;
int index = 0;
foreach (var item in newExpression.Arguments)
{
if (item.NodeType == ExpressionType.MemberAccess)
{
var exp = item as MemberExpression;
if (index > 0)
{
builder.Append(",");
}
builder.Append(GetSetParameterSql(provider, Mapper.MemberMappers[exp.Member.Name]));
index++;
}
else
{
throw new Exception("不支持的Linq表达式");
}
}
}
else if (expression.NodeType == ExpressionType.MemberAccess)
{
var exp = expression as MemberExpression;
builder.Append(GetSetParameterSql(provider, Mapper.MemberMappers[exp.Member.Name]));
}
else
{
throw new Exception("不支持的Linq表达式");
}
builder.Append(" WHERE ");
foreach (var colMapper in Mapper.PrimaryKeys)
{
builder.Append(GetSetParameterSql(provider, colMapper));
}
return(builder.ToString());
}
