using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text.RegularExpressions;
using System.Threading;
using System.Threading.Tasks;
using NewLife;
using NewLife.Collections;
using NewLife.Log;
using NewLife.Reflection;
using NewLife.Web;
namespace XCode.DataAccessLayer
{
/// <summary>数据库基类</summary>
/// <remarks>
/// 数据库类的职责是抽象不同数据库的共同点,理应最小化,保证原汁原味,因此不做缓存等实现。
/// 对于每一个连接字符串配置,都有一个数据库实例,而不是每个数据库类型一个实例,因为同类型数据库不同版本行为不同。
/// </remarks>
abstract class DbBase : DisposeBase, IDatabase
{
#region 构造函数
static DbBase()
{
var root = AppDomain.CurrentDomain.BaseDirectory;
if (Runtime.IsWeb) root = root.CombinePath("bin");
// 根据进程版本,设定x86或者x64为DLL目录
var dir = Environment.Is64BitProcess ? "x64" : "x86";
dir = root.CombinePath(dir);
//if (Directory.Exists(dir)) SetDllDirectory(dir);
// 不要判断是否存在,因为可能目录还不存在,一会下载驱动后将创建目录
if (Runtime.Windows) SetDllDirectory(dir);
var set = NewLife.Setting.Current;
root = set.GetPluginPath();
//dir = Environment.Is64BitProcess ? "x64" : "x86";
//dir = root.CombinePath(dir);
if (Runtime.Windows) SetDllDirectory(root);
}
/// <summary>实例化</summary>
public DbBase()
{
var set = Setting.Current;
Migration = set.Migration;
TraceSQLTime = set.TraceSQLTime;
RetryOnFailure = set.RetryOnFailure;
NameFormat = set.NameFormat;
ShowSQL = set.ShowSQL;
SQLMaxLength = set.SQLMaxLength;
UseParameter = set.UseParameter;
}
/// <summary>销毁资源时,回滚未提交事务,并关闭数据库连接</summary>
/// <param name="disposing"></param>
protected override void Dispose(Boolean disposing)
{
base.Dispose(disposing);
if (_metadata != null)
{
// 销毁本数据库的元数据对象
try
{
_metadata.Dispose();
}
catch (Exception ex)
{
XTrace.WriteException(ex);
}
_metadata = null;
}
}
/// <summary>释放所有会话</summary>
internal void ReleaseSession()
{
var st = _store;
if (st != null) _store = new AsyncLocal<IDbSession>();
}
#endregion
#region 属性
/// <summary>返回数据库类型。外部DAL数据库类请使用Other</summary>
public virtual DatabaseType Type => DatabaseType.None;
/// <summary>工厂</summary>
public abstract DbProviderFactory Factory { get; }
/// <summary>连接名</summary>
public String ConnName { get; set; }
protected internal String _ConnectionString;
/// <summary>链接字符串</summary>
public virtual String ConnectionString
{
get => _ConnectionString;
set
{
#if DEBUG
XTrace.WriteLine("{0} 设定 {1}", ConnName, value);
#endif
var builder = new ConnectionStringBuilder(value);
OnSetConnectionString(builder);
// 只有连接字符串改变,才释放会话
var connStr = builder.ConnectionString;
#if DEBUG
XTrace.WriteLine("{0} 格式 {1}", ConnName, connStr);
#endif
if (_ConnectionString != connStr)
{
_ConnectionString = connStr;
ReleaseSession();
}
}
}
protected void CheckConnStr()
{
if (ConnectionString.IsNullOrWhiteSpace())
throw new XCodeException("[{0}]未指定连接字符串!", ConnName);
}
/// <summary>设置连接字符串时允许从中取值或修改,基类用于读取拥有者Owner,子类重写时应调用基类</summary>
/// <param name="builder"></param>
protected virtual void OnSetConnectionString(ConnectionStringBuilder builder)
{
if (builder.TryGetAndRemove(nameof(Owner), out var value) && !value.IsNullOrEmpty()) Owner = value;
if (builder.TryGetAndRemove(nameof(ShowSQL), out value) && !value.IsNullOrEmpty()) ShowSQL = value.ToBoolean();
// 参数化,需要兼容写错了一年的UserParameter
if (builder.TryGetAndRemove(nameof(UseParameter), out value) && !value.IsNullOrEmpty()) UseParameter = value.ToBoolean();
//if (builder.TryGetAndRemove("UserParameter", out value) && !value.IsNullOrEmpty()) UseParameter = value.ToBoolean();
if (builder.TryGetAndRemove(nameof(Migration), out value) && !value.IsNullOrEmpty()) Migration = (Migration)Enum.Parse(typeof(Migration), value, true);
if (builder.TryGetAndRemove(nameof(TablePrefix), out value) && !value.IsNullOrEmpty()) TablePrefix = value;
if (builder.TryGetAndRemove(nameof(Readonly), out value) && !value.IsNullOrEmpty()) Readonly = value.ToBoolean();
if (builder.TryGetAndRemove(nameof(DataCache), out value) && !value.IsNullOrEmpty()) DataCache = value.ToInt();
// 反向工程生成sql中表名和字段名称大小写
if (builder.TryGetAndRemove(nameof(NameFormat), out value) && !value.IsNullOrEmpty()) NameFormat = (NameFormats)Enum.Parse(typeof(NameFormats), value, true);
if (builder.TryGetAndRemove(nameof(CommandTimeout), out value) && !value.IsNullOrEmpty()) CommandTimeout = value.ToInt();
// 连接字符串去掉provider,可能有些数据库不支持这个属性
if (builder.TryGetAndRemove("provider", out value) && !value.IsNullOrEmpty()) { }
// 数据库名称
var db = builder["Database"] ?? builder["Db"];
if (db.IsNullOrEmpty()) db = builder["Initial Catalog"];
DatabaseName = db;
}
/// <summary>拥有者</summary>
public virtual String Owner { get; set; }
/// <summary>数据库名</summary>
public String DatabaseName { get; set; }
internal protected String _ServerVersion;
/// <summary>数据库服务器版本</summary>
public virtual String ServerVersion
{
get
{
var ver = _ServerVersion;
if (ver != null) return ver;
_ServerVersion = String.Empty;
using var conn = OpenConnection();
return _ServerVersion = conn.ServerVersion;
}
}
/// <summary>反向工程。Off 关闭;ReadOnly 只读不执行;On 打开,新建;Full 完全,修改删除</summary>
public Migration Migration { get; set; }
/// <summary>跟踪SQL执行时间,大于该阀值将输出日志</summary>
public Int32 TraceSQLTime { get; set; }
/// <summary>本连接数据只读</summary>
public Boolean Readonly { get; set; }
/// <summary>失败重试。执行命令超时后的重试次数,默认0不重试</summary>
public Int32 RetryOnFailure { get; set; }
/// <summary>数据层缓存有效期。单位秒</summary>
public Int32 DataCache { get; set; }
/// <summary>表前缀。所有在该连接上的表名都自动增加该前缀</summary>
public String TablePrefix { get; set; }
/// <summary>反向工程表名、字段名大小写设置</summary>
public NameFormats NameFormat { get; set; }
/// <summary>批大小。用于批量操作数据,默认5000</summary>
public Int32 BatchSize { get; set; } = 5_000;
/// <summary>命令超时。查询执行超时时间,默认0秒不限制</summary>
public Int32 CommandTimeout { get; set; }
#endregion
#region 方法
private AsyncLocal<IDbSession> _store = new();
/// <summary>创建数据库会话,数据库在每一个线程都有唯一的一个实例</summary>
/// <returns></returns>
public IDbSession CreateSession()
{
// 会话可能已经被销毁
var session = _store.Value;
if (session != null && !session.Disposed) return session;
session = OnCreateSession();
CheckConnStr();
_store.Value = session;
return session;
}
/// <summary>创建数据库会话</summary>
/// <returns></returns>
protected abstract IDbSession OnCreateSession();
/// <summary>唯一实例</summary>
private IMetaData _metadata;
/// <summary>创建元数据对象,唯一实例</summary>
/// <returns></returns>
public IMetaData CreateMetaData()
{
if (_metadata != null && !_metadata.Disposed) return _metadata;
lock (this)
{
if (_metadata != null && !_metadata.Disposed) return _metadata;
_metadata = OnCreateMetaData();
// 减少一步类型转换
if (_metadata is DbMetaData meta) meta.Database = this;
return _metadata;
}
}
/// <summary>创建元数据对象</summary>
/// <returns></returns>
protected abstract IMetaData OnCreateMetaData();
/// <summary>打开连接</summary>
/// <returns></returns>
public virtual DbConnection OpenConnection()
{
if (Factory == null) throw new InvalidOperationException($"无法找到{Type}的ADO.NET驱动,需要从Nuget引用!");
var conn = Factory.CreateConnection();
conn.ConnectionString = ConnectionString;
conn.Open();
return conn;
}
/// <summary>打开连接</summary>
/// <returns></returns>
public virtual async Task<DbConnection> OpenConnectionAsync()
{
if (Factory == null) throw new InvalidOperationException($"无法找到{Type}的ADO.NET驱动,需要从Nuget引用!");
var conn = Factory.CreateConnection();
conn.ConnectionString = ConnectionString;
await conn.OpenAsync();
return conn;
}
/// <summary>是否支持该提供者所描述的数据库</summary>
/// <param name="providerName">提供者</param>
/// <returns></returns>
public virtual Boolean Support(String providerName) => !providerName.IsNullOrEmpty() && providerName.ToLower().Contains(Type.ToString().ToLower());
#endregion
#region 下载驱动
protected static IList<String> GetLinkNames(String assemblyFile, Boolean strict = false)
{
var links = new List<String>();
var name = Path.GetFileNameWithoutExtension(assemblyFile);
if (!name.IsNullOrEmpty())
{
var arch = (RuntimeInformation.OSArchitecture + "").ToLower();
// 可能是在x64架构上跑x86
if (arch == "x64" && !Environment.Is64BitProcess) arch = "x86";
var platform = "";
if (Runtime.Linux)
platform = "linux";
else if (Runtime.OSX)
platform = "osx";
else
platform = "win";
// 多目标匹配,不区分先后顺序,统一匹配后按照版本和时间排序
links.Add($"{name}.{platform}-{arch}");
links.Add($"{name}.{platform}");
var ver = Environment.Version;
if (ver.Major <= 3)
links.Add($"{name}_netcore{ver.Major}{ver.Minor}");
else
links.Add($"{name}_net{ver.Major}{ver.Minor}");
if (ver.Major >= 3) links.Add($"{name}_netstandard21");
links.Add($"{name}_netstandard20");
// 有些数据库驱动不区分x86/x64,并且逐步以Fx4为主,所以来一个默认
if (!strict && !links.Contains(name)) links.Add(name);
}
return links;
}
/// <summary>获取提供者工厂</summary>
/// <param name="assemblyFile"></param>
/// <param name="className"></param>
/// <param name="strict"></param>
/// <param name="ignoreError"></param>
/// <returns></returns>
public static DbProviderFactory GetProviderFactory(String assemblyFile, String className, Boolean strict = false, Boolean ignoreError = false)
{
try
{
var links = GetLinkNames(assemblyFile, strict);
var type = PluginHelper.LoadPlugin(className, null, assemblyFile, links.Join(","));
// 反射实现获取数据库工厂
var file = assemblyFile;
var plugin = NewLife.Setting.Current.GetPluginPath();
file = plugin.CombinePath(file);
// 如果还没有,就写异常
if (type == null)
{
if (assemblyFile.IsNullOrEmpty()) return null;
if (!File.Exists(file)) throw new FileNotFoundException("缺少文件" + file + "!", file);
}
if (type == null)
{
XTrace.WriteLine("驱动文件[{0}]无效或不适用于当前环境,准备删除后重新加载[{1}]!", assemblyFile, file);
try
{
File.Delete(file);
}
catch (UnauthorizedAccessException) { }
catch (Exception ex) { XTrace.Log.Error(ex.ToString()); }
type = PluginHelper.LoadPlugin(className, null, file, links.Join(","));
// 如果还没有,就写异常
if (!File.Exists(file)) throw new FileNotFoundException("缺少文件" + file + "!", file);
}
//if (type == null) return null;
if (type == null) throw new XCodeException("无法加载驱动[{0}],请从nuget正确引入数据库驱动!", assemblyFile);
var asm = type.Assembly;
if (DAL.Debug) DAL.WriteLog("{2}驱动{0} 版本v{1}", asm.Location, asm.GetName().Version, className.TrimEnd("Client", "Factory"));
var field = type.GetFieldEx("Instance");
if (field == null) return Activator.CreateInstance(type) as DbProviderFactory;
return Reflect.GetValue(null, field) as DbProviderFactory;
}
catch
{
if (ignoreError) return null;
throw;
}
}
[DllImport("kernel32.dll", CharSet = CharSet.Unicode)]
static extern Int32 SetDllDirectory(String pathName);
#endregion
#region 分页
/// <summary>构造分页SQL,优先选择max/min,然后选择not in</summary>
/// <remarks>
/// 两个构造分页SQL的方法,区别就在于查询生成器能够构造出来更好的分页语句,尽可能的避免子查询。
/// MS体系的分页精髓就在于唯一键,当唯一键带有Asc/Desc/Unkown等排序结尾时,就采用最大最小值分页,否则使用较次的TopNotIn分页。
/// TopNotIn分页和MaxMin分页的弊端就在于无法完美的支持GroupBy查询分页,只能查到第一页,往后分页就不行了,因为没有主键。
/// </remarks>
/// <param name="sql">SQL语句</param>
/// <param name="startRowIndex">开始行,0表示第一行</param>
/// <param name="maximumRows">最大返回行数,0表示所有行</param>
/// <param name="keyColumn">唯一键。用于not in分页</param>
/// <returns>分页SQL</returns>
public virtual String PageSplit(String sql, Int64 startRowIndex, Int64 maximumRows, String keyColumn)
{
// 从第一行开始,不需要分页
if (startRowIndex <= 0 && maximumRows < 1) return sql;
#region Max/Min分页
// 如果要使用max/min分页法,首先keyColumn必须有asc或者desc
if (!String.IsNullOrEmpty(keyColumn))
{
var kc = keyColumn.ToLower();
if (kc.EndsWith(" desc") || kc.EndsWith(" asc") || kc.EndsWith(" unknown"))
{
var str = PageSplitMaxMin(sql, startRowIndex, maximumRows, keyColumn);
if (!String.IsNullOrEmpty(str)) return str;
// 如果不能使用最大最小值分页,则砍掉排序,为TopNotIn分页做准备
var p = keyColumn.IndexOf(' ');
if (p > 0) keyColumn = keyColumn[..p];
}
}
#endregion
//检查简单SQL。为了让生成分页SQL更短
var tablename = CheckSimpleSQL(sql);
if (tablename != sql)
sql = tablename;
else
sql = $"({sql}) XCode_Temp_a";
// 取第一页也不用分页。把这代码放到这里,主要是数字分页中要自己处理这种情况
if (startRowIndex <= 0 && maximumRows > 0)
return $"Select Top {maximumRows} * From {sql}";
if (String.IsNullOrEmpty(keyColumn)) throw new ArgumentNullException(nameof(keyColumn), "这里用的not in分页算法要求指定主键列!");
if (maximumRows < 1)
sql = $"Select * From {sql} Where {keyColumn} Not In(Select Top {startRowIndex} {keyColumn} From {sql})";
else
sql = $"Select Top {maximumRows} * From {sql} Where {keyColumn} Not In(Select Top {startRowIndex} {keyColumn} From {sql})";
return sql;
}
/// <summary>按唯一数字最大最小分析</summary>
/// <param name="sql">SQL语句</param>
/// <param name="startRowIndex">开始行,0表示第一行</param>
/// <param name="maximumRows">最大返回行数,0表示所有行</param>
/// <param name="keyColumn">唯一键。用于not in分页</param>
/// <returns>分页SQL</returns>
public static String PageSplitMaxMin(String sql, Int64 startRowIndex, Int64 maximumRows, String keyColumn)
{
// 唯一键的顺序。默认为Empty,可以为asc或desc,如果有,则表明主键列是数字唯一列,可以使用max/min分页法
var isAscOrder = keyColumn.ToLower().EndsWith(" asc");
// 是否使用max/min分页法
var canMaxMin = false;
// 如果sql最外层有排序,且唯一的一个排序字段就是keyColumn时,可用max/min分页法
// 如果sql最外层没有排序,其排序不是unknown,可用max/min分页法
var ms = reg_Order.Matches(sql);
if (ms != null && ms.Count > 0 && ms[0].Index > 0)
{
#region 有OrderBy
// 取第一页也不用分页。把这代码放到这里,主要是数字分页中要自己处理这种情况
if (startRowIndex <= 0 && maximumRows > 0)
return $"Select Top {maximumRows} * From {CheckSimpleSQL(sql)}";
var p = keyColumn.IndexOf(' ');
if (p > 0) keyColumn = keyColumn[..p];
sql = sql[..ms[0].Index];
var strOrderBy = ms[0].Groups[1].Value.Trim();
// 只有一个排序字段
if (!String.IsNullOrEmpty(strOrderBy) && !strOrderBy.Contains(","))
{
// 有asc或者desc。没有时,默认为asc
if (strOrderBy.ToLower().EndsWith(" desc"))
{
var str = strOrderBy[..^" desc".Length].Trim();
// 排序字段等于keyColumn
if (str.ToLower() == keyColumn.ToLower())
{
isAscOrder = false;
canMaxMin = true;
}
}
else if (strOrderBy.ToLower().EndsWith(" asc"))
{
var str = strOrderBy[..^" asc".Length].Trim();
// 排序字段等于keyColumn
if (str.ToLower() == keyColumn.ToLower())
{
isAscOrder = true;
canMaxMin = true;
}
}
else if (!strOrderBy.Contains(" ")) // 不含空格,是唯一排序字段
{
// 排序字段等于keyColumn
if (strOrderBy.ToLower() == keyColumn.ToLower())
{
isAscOrder = true;
canMaxMin = true;
}
}
}
#endregion
}
else
{
// 取第一页也不用分页。把这代码放到这里,主要是数字分页中要自己处理这种情况
if (startRowIndex <= 0 && maximumRows > 0)
{
//数字分页中,业务上一般使用降序,Entity类会给keyColumn指定降序的
//但是,在第一页的时候,没有用到keyColumn,而数据库一般默认是升序
//这时候就会出现第一页是升序,后面页是降序的情况了。这里改正这个BUG
if (keyColumn.ToLower().EndsWith(" desc") || keyColumn.ToLower().EndsWith(" asc"))
return $"Select Top {maximumRows} * From {CheckSimpleSQL(sql)} Order By {keyColumn}";
else
return $"Select Top {maximumRows} * From {CheckSimpleSQL(sql)}";
}
if (!keyColumn.ToLower().EndsWith(" unknown")) canMaxMin = true;
var p = keyColumn.IndexOf(' ');
if (p > 0) keyColumn = keyColumn[..p];
}
if (canMaxMin)
{
if (maximumRows < 1)
sql = $"Select * From {CheckSimpleSQL(sql)} Where {keyColumn}{(isAscOrder ? ">" : "<")}(Select {(isAscOrder ? "max" : "min")}({keyColumn}) From (Select Top {startRowIndex} {keyColumn} From {CheckSimpleSQL(sql)} Order By {keyColumn} {(isAscOrder ? "Asc" : "Desc")}) XCode_Temp_a) Order By {keyColumn} {(isAscOrder ? "Asc" : "Desc")}";
else
sql = $"Select Top {maximumRows} * From {CheckSimpleSQL(sql)} Where {keyColumn}{(isAscOrder ? ">" : "<")}(Select {(isAscOrder ? "max" : "min")}({keyColumn}) From (Select Top {startRowIndex} {keyColumn} From {CheckSimpleSQL(sql)} Order By {keyColumn} {(isAscOrder ? "Asc" : "Desc")}) XCode_Temp_a) Order By {keyColumn} {(isAscOrder ? "Asc" : "Desc")}";
return sql;
}
return null;
}
private static readonly Regex reg_SimpleSQL = new(@"^\s*select\s+\*\s+from\s+([\w\[\]\""\""\']+)\s*$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
/// <summary>检查简单SQL语句,比如Select * From table</summary>
/// <param name="sql">待检查SQL语句</param>
/// <returns>如果是简单SQL语句则返回表名,否则返回子查询(sql) XCode_Temp_a</returns>
internal protected static String CheckSimpleSQL(String sql)
{
if (String.IsNullOrEmpty(sql)) return sql;
var ms = reg_SimpleSQL.Matches(sql);
if (ms == null || ms.Count <= 0 || ms[0].Groups.Count < 2 ||
String.IsNullOrEmpty(ms[0].Groups[1].Value)) return $"({sql}) XCode_Temp_a";
return ms[0].Groups[1].Value;
}
private static readonly Regex reg_Order = new(@"\border\s*by\b([^)]+)$", RegexOptions.Compiled | RegexOptions.IgnoreCase);
/// <summary>检查是否以Order子句结尾,如果是,分割sql为前后两部分</summary>
/// <param name="sql"></param>
/// <returns></returns>
internal protected static String CheckOrderClause(ref String sql)
{
if (!sql.ToLower().Contains("order")) return null;
// 使用正则进行严格判断。必须包含Order By,并且它右边没有右括号),表明有order by,且不是子查询的,才需要特殊处理
var ms = reg_Order.Matches(sql);
if (ms == null || ms.Count <= 0 || ms[0].Index < 1) return null;
var orderBy = sql[ms[0].Index..].Trim();
sql = sql[..ms[0].Index].Trim();
return orderBy;
}
/// <summary>构造分页SQL</summary>
/// <remarks>
/// 两个构造分页SQL的方法,区别就在于查询生成器能够构造出来更好的分页语句,尽可能的避免子查询。
/// MS体系的分页精髓就在于唯一键,当唯一键带有Asc/Desc/Unkown等排序结尾时,就采用最大最小值分页,否则使用较次的TopNotIn分页。
/// TopNotIn分页和MaxMin分页的弊端就在于无法完美的支持GroupBy查询分页,只能查到第一页,往后分页就不行了,因为没有主键。
/// </remarks>
/// <param name="builder">查询生成器</param>
/// <param name="startRowIndex">开始行,0表示第一行</param>
/// <param name="maximumRows">最大返回行数,0表示所有行</param>
/// <returns>分页SQL</returns>
public virtual SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows)
{
// 从第一行开始,不需要分页
if (startRowIndex <= 0 && maximumRows < 1) return builder;
var sql = PageSplit(builder.ToString(), startRowIndex, maximumRows, builder.Key);
var sb = new SelectBuilder();
sb.Parse(sql);
return sb;
}
#endregion
#region 数据库特性
/// <summary>长文本长度</summary>
public virtual Int32 LongTextLength => 4000;
/// <summary>
/// 保留字字符串,其实可以在首次使用时动态从Schema中加载
/// </summary>
protected virtual String ReservedWordsStr => null;
private Dictionary<String, Boolean> _ReservedWords = null;
/// <summary>
/// 保留字
/// </summary>
private Dictionary<String, Boolean> ReservedWords
{
get
{
if (_ReservedWords == null)
{
var dic = new Dictionary<String, Boolean>(StringComparer.OrdinalIgnoreCase);
var ss = (ReservedWordsStr + "").Split(',', StringSplitOptions.RemoveEmptyEntries);
foreach (var item in ss)
{
var key = item.Trim();
if (!dic.ContainsKey(key)) dic.Add(key, true);
}
_ReservedWords = dic;
}
return _ReservedWords;
}
}
/// <summary>是否保留字</summary>
/// <param name="word"></param>
/// <returns></returns>
internal Boolean IsReservedWord(String word) => !word.IsNullOrEmpty() && ReservedWords.ContainsKey(word);
/// <summary>格式化时间为SQL字符串</summary>
/// <remarks>
/// 优化DateTime转为全字符串,平均耗时从25.76ns降为15.07。
/// 调用非常频繁,每分钟都有数百万次调用。
/// </remarks>
/// <param name="dateTime">时间值</param>
/// <returns></returns>
public virtual String FormatDateTime(DateTime dateTime) => "'" + dateTime.ToFullString() + "'";
/// <summary>格式化关键字</summary>
/// <param name="keyWord">表名</param>
/// <returns></returns>
public virtual String FormatKeyWord(String keyWord) => keyWord;
/// <summary>格式化名称,如果是关键字,则格式化后返回,否则原样返回</summary>
/// <param name="name">名称</param>
/// <returns></returns>
public virtual String FormatName(String name)
{
if (name.IsNullOrEmpty()) return name;
// 优先使用内置关键字
var rws = ReservedWords;
if (rws.Count > 0)
{
if (rws.ContainsKey(name)) return FormatKeyWord(name);
}
else
{
if (CreateMetaData() is DbMetaData md && md.ReservedWords.Contains(name)) return FormatKeyWord(name);
}
return name;
}
/// <summary>格式化表名,考虑表前缀和Owner</summary>
/// <param name="table">表</param>
/// <returns></returns>
public virtual String FormatName(IDataTable table) => FormatName(table, true);
/// <summary>格式化表名,考虑表前缀和Owner</summary>
/// <param name="table">表</param>
/// <param name="formatKeyword">是否格式化关键字</param>
/// <returns></returns>
public virtual String FormatName(IDataTable table, Boolean formatKeyword)
{
if (table == null) return null;
var name = table.TableName;
// 检查自动表前缀
var pf = TablePrefix;
if (!pf.IsNullOrEmpty()) name = pf + name;
// 名称格式化,只有表名跟名称相同时才处理。否则认为用户指定了表名
switch (NameFormat)
{
case NameFormats.Upper:
name = name.ToUpper();
break;
case NameFormats.Lower:
name = name.ToLower();
break;
case NameFormats.Underline:
if (table.TableName == table.Name)
name = ChangeUnderline(name).ToLower();
else
name = name.ToLower();
break;
case NameFormats.Default:
default:
break;
}
return formatKeyword ? FormatName(name) : name;
}
/// <summary>格式化字段名,考虑大小写</summary>
/// <param name="column">字段</param>
/// <returns></returns>
public virtual String FormatName(IDataColumn column)
{
if (column == null) return null;
var name = column.ColumnName;
// 名称格式化,只有字段名名跟名称相同时才处理。否则认为用户指定了字段名
switch (NameFormat)
{
case NameFormats.Upper:
name = name.ToUpper();
break;
case NameFormats.Lower:
name = name.ToLower();
break;
case NameFormats.Underline:
if (column.ColumnName == column.Name)
name = ChangeUnderline(name).ToLower();
else
name = name.ToLower();
break;
case NameFormats.Default:
default:
break;
}
return FormatName(name);
}
/// <summary>把驼峰命名转为下划线</summary>
/// <param name="name"></param>
/// <returns></returns>
private static String ChangeUnderline(String name)
{
var sb = Pool.StringBuilder.Get();
// 遇到大写字母时,表示新一段开始,增加下划线
for (var i = 0; i < name.Length; i++)
{
var ch = name[i];
if (i > 0 && Char.IsUpper(ch))
{
// 前一个小写字母,新的开始
if (Char.IsLower(name[i - 1]))
sb.Append('_');
// 后一个字母小写,新的开始
else if (i < name.Length - 1 && Char.IsLower(name[i + 1]) && Char.IsLower(name[i - 1]))
sb.Append('_');
}
sb.Append(ch);
}
return sb.Put(true);
}
/// <summary>格式化数据为SQL数据</summary>
/// <param name="column">字段</param>
/// <param name="value">数值</param>
/// <returns></returns>
public virtual String FormatValue(IDataColumn column, Object value)
{
var isNullable = true;
Type type = null;
if (column != null)
{
type = column.DataType;
isNullable = column.Nullable;
}
else if (value != null)
type = value.GetType();
// 如果类型是Nullable的,则获取对应的类型
type = Nullable.GetUnderlyingType(type) ?? type;
if (type == typeof(String))
{
if (value == null) return isNullable ? "null" : "''";
//!!! 为SQL格式化数值时,如果字符串是Empty,将不再格式化为null
//if (String.IsNullOrEmpty(value.ToString()) && isNullable) return "null";
return "'" + value.ToString().Trim('\0').Replace("'", "''") + "'";
}
else if (type == typeof(DateTime))
{
if (value == null) return isNullable ? "null" : "''";
var dt = Convert.ToDateTime(value);
//if (dt <= DateTime.MinValue || dt >= DateTime.MaxValue) return isNullable ? "null" : "''";
if (isNullable && (dt <= DateTime.MinValue || dt >= DateTime.MaxValue)) return "null";
return FormatDateTime(dt);
}
else if (type == typeof(Boolean))
{
if (value == null) return isNullable ? "null" : "";
return Convert.ToBoolean(value) ? "1" : "0";
}
else if (type == typeof(Byte[]))
{
var bts = (Byte[])value;
if (bts == null || bts.Length <= 0) return isNullable ? "null" : "0x0";
return "0x" + BitConverter.ToString(bts).Replace("-", null);
}
else if (type == typeof(Guid))
{
if (value == null) return isNullable ? "null" : "''";
return $"'{value}'";
}
if (value == null) return isNullable ? "null" : "";
// 枚举
if (!type.IsInt() && type.IsEnum) type = typeof(Int32);
// 转为目标类型,比如枚举转为数字
value = value.ChangeType(type);
if (value == null) return isNullable ? "null" : "";
return value.ToString();
}
/// <summary>格式化模糊搜索的字符串。处理转义字符</summary>
/// <param name="column">字段</param>
/// <param name="format">格式化字符串</param>
/// <param name="value">数值</param>
/// <returns></returns>
public virtual String FormatLike(IDataColumn column, String format, String value) => String.Format(format, FormatName(column), value);
/// <summary>格式化参数名</summary>
/// <param name="name">名称</param>
/// <returns></returns>
public virtual String FormatParameterName(String name)
{
if (name.IsNullOrEmpty()) return name;
return ParamPrefix + name;
}
internal protected virtual String ParamPrefix => "@";
/// <summary>字符串相加</summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <returns></returns>
public virtual String StringConcat(String left, String right) => (!left.IsNullOrEmpty() ? left : "\'\'") + "+" + (!right.IsNullOrEmpty() ? right : "\'\'");
/// <summary>创建参数</summary>
/// <param name="name">名称</param>
/// <param name="value">值</param>
/// <param name="field">字段</param>
/// <returns></returns>
public virtual IDataParameter CreateParameter(String name, Object value, IDataColumn field) => CreateParameter(name, value, field?.DataType);
/// <summary>创建参数</summary>
/// <param name="name">名称</param>
/// <param name="value">值</param>
/// <param name="type">类型</param>
/// <returns></returns>
public virtual IDataParameter CreateParameter(String name, Object value, Type type = null)
{
if (value == null && type == null) throw new ArgumentNullException(nameof(value));
var dp = Factory.CreateParameter();
dp.ParameterName = FormatParameterName(name);
dp.Direction = ParameterDirection.Input;
try
{
if (type == null)
{
type = value?.GetType();
// 参数可能是数组
if (type != null && type != typeof(Byte[]) && type.IsArray) type = type.GetElementTypeEx();
}
else
{
// 可空类型
type = Nullable.GetUnderlyingType(type) ?? type;
if (value is not null and not IList) value = value.ChangeType(type);
}
// 写入数据类型
switch (type.GetTypeCode())
{
case TypeCode.Boolean:
dp.DbType = DbType.Boolean;
break;
case TypeCode.Char:
case TypeCode.SByte:
case TypeCode.Byte:
dp.DbType = DbType.Byte;
break;
case TypeCode.Int16:
case TypeCode.UInt16:
dp.DbType = DbType.Int16;
break;
case TypeCode.Int32:
case TypeCode.UInt32:
dp.DbType = DbType.Int32;
break;
case TypeCode.Int64:
case TypeCode.UInt64:
dp.DbType = DbType.Int64;
break;
case TypeCode.Single:
dp.DbType = DbType.Double;
break;
case TypeCode.Double:
dp.DbType = DbType.Double;
break;
case TypeCode.Decimal:
dp.DbType = DbType.Decimal;
break;
case TypeCode.DateTime:
dp.DbType = DbType.DateTime;
break;
case TypeCode.String:
dp.DbType = DbType.String;
break;
default:
break;
}
dp.Value = value;
}
catch (Exception ex)
{
throw new Exception($"创建字段{name}/{type.Name}的参数时出错", ex);
}
return dp;
}
/// <summary>创建参数数组</summary>
/// <param name="ps"></param>
/// <returns></returns>
public virtual IDataParameter[] CreateParameters(IDictionary<String, Object> ps) => ps?.Select(e => CreateParameter(e.Key, e.Value)).ToArray();
/// <summary>根据对象成员创建参数数组</summary>
/// <param name="model"></param>
/// <returns></returns>
public virtual IDataParameter[] CreateParameters(Object model)
{
if (model == null) return new IDataParameter[0];
if (model is IDataParameter[] dps) return dps;
if (model is IDataParameter dp) return new[] { dp };
if (model is IDictionary<String, Object> dic) return CreateParameters(dic);
var list = new List<IDataParameter>();
foreach (var pi in model.GetType().GetProperties(true))
{
list.Add(CreateParameter(pi.Name, pi.GetValue(model, null), pi.PropertyType));
}
return list.ToArray();
}
/// <summary>是否支持Schema。默认true</summary>
public Boolean SupportSchema { get; set; } = true;
#endregion
#region 辅助函数
/// <summary>已重载。</summary>
/// <returns></returns>
public override String ToString() => $"[{ConnName}] {Type} {ServerVersion}";
protected static String ResolveFile(String file)
{
if (file.IsNullOrEmpty()) return file;
var cfg = NewLife.Setting.Current;
file = file.Replace("|DataDirectory|", cfg.DataPath);
file = file.Replace(@"~\App_Data", cfg.DataPath);
file = file.TrimStart("~");
// 过滤掉不必要的符号
file = new FileInfo(file.GetBasePath()).FullName;
return file;
}
//internal ICache _SchemaCache = new MemoryCache { Expire = 10, Period = 10 * 60, };
#endregion
#region Sql日志输出
/// <summary>是否输出SQL语句,默认为XCode调试开关XCode.Debug</summary>
public Boolean ShowSQL { get; set; }
/// <summary>SQL最大长度,输出日志时的SQL最大长度,超长截断,默认4096,不截断用0</summary>
public Int32 SQLMaxLength { get; set; }
#endregion
#region 参数化
/// <summary>参数化添删改查。默认关闭</summary>
public Boolean UseParameter { get; set; }
#endregion
}
}
|