#if __WIN__
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlTypes;
using System.IO;
using System.Reflection;
using System.Text;
using NewLife;
using NewLife.Collections;
using NewLife.IO;
using NewLife.Log;
using NewLife.Reflection;
namespace XCode.DataAccessLayer
{
/// <summary>SqlCe数据库。由 @Goon(12600112) 测试并完善正向反向工程</summary>
class SqlCe : FileDbBase
{
#region 属性
/// <summary>返回数据库类型。外部DAL数据库类请使用Other</summary>
public override DatabaseType Type => DatabaseType.SqlCe;
private static DbProviderFactory _Factory;
/// <summary>工厂</summary>
public override DbProviderFactory Factory
{
get
{
if (_Factory == null)
{
lock (typeof(SqlCe))
{
if (_Factory == null) _Factory = GetProviderFactory("System.Data.SqlServerCe.dll", "System.Data.SqlServerCe.SqlCeProviderFactory");
if (_Factory != null)
{
using var conn = _Factory.CreateConnection();
if (conn.ServerVersion.StartsWith("4"))
SqlCeProviderVersion = SQLCEVersion.SQLCE40;
else
SqlCeProviderVersion = SQLCEVersion.SQLCE35;
}
}
}
return _Factory;
}
}
/// <summary>SqlCe提供者版本</summary>
public static SQLCEVersion SqlCeProviderVersion { get; set; } = SQLCEVersion.SQLCE40;
/// <summary>SqlCe版本,默认4.0</summary>
public SQLCEVersion SqlCeVer { get; set; } = SQLCEVersion.SQLCE40;
protected override void OnSetConnectionString(ConnectionStringBuilder builder)
{
base.OnSetConnectionString(builder);
SqlCeVer = SQLCEVersion.SQLCE40;
var fn = DatabaseName;
if (!fn.IsNullOrEmpty() && File.Exists(fn))
{
try
{
SqlCeVer = SqlCeHelper.DetermineVersion(fn);
}
catch (Exception ex)
{
XTrace.WriteException(ex);
SqlCeVer = SQLCEVersion.SQLCE40;
}
}
}
#endregion
#region 方法
/// <summary>创建数据库会话</summary>
/// <returns></returns>
protected override IDbSession OnCreateSession() => new SqlCeSession(this);
/// <summary>创建元数据对象</summary>
/// <returns></returns>
protected override IMetaData OnCreateMetaData() => new SqlCeMetaData();
#endregion
#region 数据库特性
protected override String ReservedWordsStr => "ADD,ALL,ALTER,AND,ANY,APPLY,AS,ASC,AUTHORIZATION,BACKUP,BEGIN,BETWEEN,BREAK,BROWSE,BULK,BY,CASCADE,CASE,CAST,CHECK,CHECKPOINT,CLOSE,CLUSTERED,COALESCE,COLLATE,COLUMN,COMMIT,COMPUTE,CONSTRAINT,CONTAINS,CONTAINSTABLE,CONTINUE,CONVERT,CREATE,CROSS,CURRENT,CURRENT_DATE,CURRENT_TIME,CURRENT_TIMESTAMP,CURRENT_USER,CURSOR,DATABASE,DBCC,DEALLOCATE,DECLARE,DEFAULT,DELETE,DENY,DESC,DISK,DISTINCT,DISTRIBUTED,DOUBLE,DROP,DUMP,ELSE,END,ERRLVL,ESCAPE,EXCEPT,EXEC,EXECUTE,EXISTS,EXIT,EXTERNAL,FETCH,FILE,FILLFACTOR,FIRST,FOR,FOREIGN,FREETEXT,FREETEXTTABLE,FROM,FULL,FUNCTION,GOTO,GRANT,GROUP,HAVING,HOLDLOCK,IDENTITY,IDENTITY_INSERT,IDENTITYCOL,IF,IN,INDEX,INNER,INSERT,INTERSECT,INTO,IS,JOIN,KEY,KILL,LEFT,LIKE,LINENO,LOAD,NATIONAL,NEXT,NOCHECK,NONCLUSTERED,NOT,NULL,NULLIF,OF,OFF,OFFSET,OFFSETS,ON,ONLY,OPEN,OPENDATASOURCE,OPENQUERY,OPENROWSET,OPENXML,OPTION,OR,ORDER,OUTER,OVER,PERCENT,PIVOT,PLAN,PRIMARY,PRINT,PROC,PROCEDURE,PUBLIC,RAISERROR,READ,READTEXT,RECONFIGURE,REFERENCES,REPLICATION,RESTORE,RESTRICT,RETURN,REVERT,REVOKE,RIGHT,ROLLBACK,ROW,ROWCOUNT,ROWGUIDCOL,ROWS,RULE,SAVE,SCHEMA,SELECT,SESSION_USER,SET,SETUSER,SHUTDOWN,SOME,STATISTICS,SYSTEM_USER,TABLE,TEXTSIZE,THEN,TO,TOP,TRAN,TRANSACTION,TRIGGER,TRUNCATE,TSEQUAL,UNION,UNIQUE,UNPIVOT,UPDATE,UPDATETEXT,USE,USER,VALUES,VARYING,VIEW,WAITFOR,WHEN,WHERE,WHILE,WITH,WRITETEXT,XMLUNNEST";
///// <summary>当前时间函数</summary>
//public override String DateTimeNow { get { return "getdate()"; } }
///// <summary>最小时间</summary>
//public override DateTime DateTimeMin { get { return SqlDateTime.MinValue.Value; } }
///// <summary>格式化时间为SQL字符串</summary>
///// <param name="dateTime">时间值</param>
///// <returns></returns>
//public override String FormatDateTime(DateTime dateTime)
//{
// return String.Format("'{0:yyyy-MM-dd HH:mm:ss}'", dateTime);
//}
/// <summary>格式化关键字</summary>
/// <param name="keyWord">关键字</param>
/// <returns></returns>
public override String FormatKeyWord(String keyWord)
{
//if (String.IsNullOrEmpty(keyWord)) throw new ArgumentNullException("keyWord");
if (String.IsNullOrEmpty(keyWord)) return keyWord;
if (keyWord.StartsWith("[") && keyWord.EndsWith("]")) return keyWord;
return $"[{keyWord}]";
}
#endregion
#region 分页
public override SelectBuilder PageSplit(SelectBuilder builder, Int64 startRowIndex, Int64 maximumRows) => MSPageSplit.PageSplit(builder, startRowIndex, maximumRows, false, b => CreateSession().QueryCount(b));
#endregion
}
/// <summary>SqlCe会话</summary>
class SqlCeSession : FileDbSession
{
#region 构造函数
public SqlCeSession(IDatabase db) : base(db) { }
#endregion
protected override void CreateDatabase()
{
if (String.IsNullOrEmpty(FileName) || File.Exists(FileName)) return;
//FileSource.ReleaseFile(Assembly.GetExecutingAssembly(), "SqlCe.sdf", FileName, true);
DAL.WriteLog("创建数据库：{0}", FileName);
var sce = SqlCeEngine.Create(Database.ConnectionString);
if (sce != null) sce.CreateDatabase().Dispose();
}
/// <summary>执行插入语句并返回新增行的自动编号</summary>
/// <param name="sql">SQL语句</param>
/// <param name="type">命令类型，默认SQL文本</param>
/// <param name="ps">命令参数</param>
/// <returns>新增行的自动编号</returns>
public override Int64 InsertAndGetIdentity(String sql, CommandType type = CommandType.Text, params IDataParameter[] ps)
{
BeginTransaction(IsolationLevel.Serializable);
try
{
Int64 rs = Execute(sql, type, ps);
if (rs > 0) rs = ExecuteScalar<Int64>("Select @@Identity");
Commit();
return rs;
}
catch
{
Rollback(true);
throw;
}
}
/// <summary>返回数据源的架构信息</summary>
/// <param name="conn">连接</param>
/// <param name="collectionName">指定要返回的架构的名称。</param>
/// <param name="restrictionValues">为请求的架构指定一组限制值。</param>
/// <returns></returns>
public override DataTable GetSchema(DbConnection conn, String collectionName, String[] restrictionValues)
{
//sqlce3.5 不支持GetSchema
if (SqlCe.SqlCeProviderVersion < SQLCEVersion.SQLCE40 && collectionName.EqualIgnoreCase(DbMetaDataCollectionNames.MetaDataCollections))
return null;
return base.GetSchema(conn, collectionName, restrictionValues);
}
}
/// <summary>SqlCe元数据</summary>
class SqlCeMetaData : FileDbMetaData
{
#region 构架
protected override List<IDataTable> OnGetTables(String[] names)
{
#region 查表、字段信息、索引信息、主键信息
var session = Database.CreateSession();
// 表信息
var dt = session.Query(_AllTableNameSql).Tables[0];
var data = new NullableDictionary<String, DataTable>(StringComparer.OrdinalIgnoreCase)
{
["Columns"] = session.Query(_AllColumnSql).Tables[0],
["Indexes"] = session.Query(_AllIndexSql).Tables[0]
};
////数据类型DBType --〉DotNetType转换
//if (SqlCe.SqlCeProviderVersion < SQLCEVersion.SQLCE40)
// DataTypes = CreateSqlCeDataType(session.Query(_DataTypeSql).Tables[0]);
#endregion
if (dt == null || dt.Rows == null || dt.Rows.Count < 1) return null;
// 默认列出所有字段
var rows = dt.Select("TABLE_TYPE='table'");
if (rows == null || rows.Length < 1) return null;
return GetTables(rows, names, data);
}
/// <summary>
/// 快速取得所有表名
/// </summary>
/// <returns></returns>
public override IList<String> GetTableNames()
{
var list = new List<String>();
var dt = GetSchema(_.Tables, null);
if (dt?.Rows == null || dt.Rows.Count < 1) return list;
// 默认列出所有字段
var rows = dt.Select("TABLE_TYPE='table'");
foreach (var dr in rows)
{
list.Add(GetDataRowValue<String>(dr, _.TalbeName));
}
return list;
}
/// <summary>获取索引</summary>
/// <param name="table"></param>
/// <param name="indexes">索引</param>
/// <param name="indexColumns">索引列</param>
/// <returns></returns>
protected override List<IDataIndex> GetIndexes(IDataTable table, DataTable indexes, DataTable indexColumns)
{
var list = base.GetIndexes(table, indexes, indexColumns);
if (list != null && list.Count > 0)
{
// SqlCe的索引直接以索引字段的方式排布，所以需要重新组合起来
var dic = new Dictionary<String, IDataIndex>();
foreach (var item in list)
{
if (!dic.TryGetValue(item.Name, out var di))
{
dic.Add(item.Name, item);
}
else
{
var ss = new List<String>(di.Columns);
if (item.Columns != null && item.Columns.Length > 0 && !ss.Contains(item.Columns[0]))
{
ss.Add(item.Columns[0]);
di.Columns = ss.ToArray();
}
}
}
list.Clear();
foreach (var item in dic.Values)
{
list.Add(item);
}
}
return list;
}
public override String CreateTableSQL(IDataTable table)
{
var sql = base.CreateTableSQL(table);
var pks = table.PrimaryKeys;
if (String.IsNullOrEmpty(sql) || pks == null || pks.Length < 2) return sql;
// 处理多主键
var sb = new StringBuilder(sql.Length + 32 + pks.Length * 16);
sb.Append(sql);
sb.Append(";\r\n");
sb.AppendFormat("Alter Table {0} Add Constraint PK_{1} Primary Key ({2})", FormatName(table), table.TableName, pks.Join(",", FormatName));
//sql += ";" + Environment.NewLine;
//sql += String.Format("Alter Table {0} Add Constraint PK_{1} Primary Key ({2})", FormatName(table.TableName), table.TableName, sb);
return sb.ToString();
}
protected override String GetFieldConstraints(IDataColumn field, Boolean onlyDefine)
{
// 非定义时（修改字段），主键字段没有约束
if (!onlyDefine && field.PrimaryKey) return null;
var str = base.GetFieldConstraints(field, onlyDefine);
// 非定义时，自增字段没有约束
if (onlyDefine && field.Identity) str = " IDENTITY(1,1)" + str;
return str;
}
#endregion
#region 取得字段信息的SQL模版
private DataTable CreateSqlCeDataType(DataTable src)
{
var drs = src.Select();
foreach (var dr in drs)
{
dr["datatype"] = DBTypeToDotNetDataType(dr["typename"].ToString());
}
src.AcceptChanges();
return src;
}
private String DBTypeToDotNetDataType(String DBType)
{
return DBType switch
{
"smallint" => "System.Int16",
"int" => "System.Int32",
"bigint" => "System.Int64",
"nvarchar" or "char" or "nchar" or "ntext" or "text" or "varchar" => "System.String",
"bit" => "System.Boolean",
"smalldatetime" or "datetime" => "System.DateTime",
"float" => "System.Double",
"decimal" or "money" or "smallmoney" or "numeric" => "System.Decimal",
"real" => "System.Single",
"uniqueidentifier" => "System.Guid",
"tinyint" => "System.Byte",
"image" or "timestamp" or "binary" or "varbinary" => "System.Byte[]",
"variant" => "System.Object",
_ => "",
};
}
private readonly String _AllTableNameSql = "SELECT table_name,TABLE_TYPE FROM information_schema.tables WHERE TABLE_TYPE <> N'SYSTEM TABLE' ";
private readonly String _AllColumnSql =
"SELECT Column_name, is_nullable, data_type, character_maximum_length, numeric_precision, autoinc_increment as AUTOINCREMENT, autoinc_seed, column_hasdefault, column_default, column_flags, numeric_scale, table_name, autoinc_next " +
"FROM information_schema.columns " +
"WHERE SUBSTRING(COLUMN_NAME, 1,5) <> '__sys' " +
"ORDER BY ordinal_position ASC ";
private readonly String _AllIndexSql =
"SELECT TABLE_NAME, INDEX_NAME, PRIMARY_KEY, [UNIQUE], [CLUSTERED], ORDINAL_POSITION, COLUMN_NAME, COLLATION AS SORT_ORDER " +
"FROM Information_Schema.Indexes " +
"WHERE SUBSTRING(COLUMN_NAME, 1,5) <> '__sys' " +
"ORDER BY TABLE_NAME, INDEX_NAME, ORDINAL_POSITION";
//private readonly String _DataTypeSql =
// "SELECT TYPE_NAME as typename, DATA_TYPE as ProviderDbType,TYPE_NAME as datatype, COLUMN_SIZE as ColumnSize, LITERAL_PREFIX as LiteralPrefix, " +
// " LITERAL_SUFFIX as LiteralSuffix, CREATE_PARAMS as CreateParameters, IS_NULLABLE as IsNullable, CASE_SENSITIVE as IsCaseSensitive, " +
// " SEARCHABLE as IsSearchable, UNSIGNED_ATTRIBUTE as IsUnsigned, FIXED_PREC_SCALE, AUTO_UNIQUE_VALUE, LOCAL_TYPE_NAME, " +
// " MINIMUM_SCALE as MinimumScale, MAXIMUM_SCALE as MaximumScale, GUID , TYPELIB , VERSION , IS_LONG as IsLong, BEST_MATCH as IsBestMatch, IS_FIXEDLENGTH as IsFixedLength " +
// " FROM INFORMATION_SCHEMA.PROVIDER_TYPES ";
#endregion
/// <summary>数据类型映射</summary>
private static readonly Dictionary<Type, String[]> _DataTypes = new()
{
{ typeof(Byte[]), new String[] { "varbinary({0})", "timestamp", "binary({0})", "image" } },
{ typeof(Guid), new String[] { "uniqueidentifier" } },
{ typeof(Boolean), new String[] { "bit" } },
{ typeof(Byte), new String[] { "tinyint" } },
{ typeof(Int16), new String[] { "smallint" } },
{ typeof(Int32), new String[] { "int" } },
{ typeof(Int64), new String[] { "bigint" } },
{ typeof(Single), new String[] { "real" } },
{ typeof(Double), new String[] { "float" } },
{ typeof(Decimal), new String[] { "money", "numeric({0}, {1})" } },
{ typeof(DateTime), new String[] { "datetime" } },
{ typeof(String), new String[] { "nvarchar({0})", "ntext", "nchar({0})" } }
};
}
/// <summary>SqlCe版本</summary>
public enum SQLCEVersion
{
/// <summary>Sqlce Ver2.0</summary>
SQLCE20 = 0,
/// <summary>Sqlce Ver3.0</summary>
SQLCE30 = 1,
/// <summary>Sqlce Ver3.5</summary>
SQLCE35 = 2,
/// <summary>Sqlce Ver4.0</summary>
SQLCE40 = 3
}
/// <summary>SqlCe辅助类</summary>
public static class SqlCeHelper
{
static readonly Dictionary<Int32, SQLCEVersion> versionDictionary = new()
{
{ 0x73616261, SQLCEVersion.SQLCE20 },
{ 0x002dd714, SQLCEVersion.SQLCE30 },
{ 0x00357b9d, SQLCEVersion.SQLCE35 },
{ 0x003d0900, SQLCEVersion.SQLCE40 }
};
/// <summary>检查给定SqlCe文件的版本</summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static SQLCEVersion DetermineVersion(String fileName)
{
var versionLONGWORD = 0;
using (var fs = new FileStream(fileName, FileMode.Open))
{
fs.Seek(16, SeekOrigin.Begin);
using var reader = new BinaryReader(fs);
versionLONGWORD = reader.ReadInt32();
}
if (versionDictionary.ContainsKey(versionLONGWORD))
return versionDictionary[versionLONGWORD];
else
throw new ApplicationException("不能确定该sdf的版本！");
}
/// <summary>检测SqlServerCe3.5是否安装</summary>
/// <returns></returns>
public static Boolean IsV35Installed()
{
try
{
Assembly.Load("System.Data.SqlServerCe, Version=3.5.1.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
}
catch (FileNotFoundException)
{
return false;
}
try
{
var factory = DbProviderFactories.GetFactory("System.Data.SqlServerCe.3.5");
}
catch (ConfigurationException)
{
return false;
}
catch (ArgumentException)
{
return false;
}
return true;
}
/// <summary>检测SqlServerCe4是否安装</summary>
/// <returns></returns>
public static Boolean IsV40Installed()
{
try
{
Assembly.Load("System.Data.SqlServerCe, Version=4.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91");
}
catch (FileNotFoundException)
{
return false;
}
try
{
var factory = DbProviderFactories.GetFactory("System.Data.SqlServerCe.4.0");
}
catch (ConfigurationException)
{
return false;
}
catch (ArgumentException)
{
return false;
}
return true;
}
}
class SqlCeEngine : IDisposable
{
/// <summary></summary>
public static Type EngineType { get; set; } = "System.Data.SqlServerCe.SqlCeEngine".GetTypeEx(true);
/// <summary>引擎</summary>
public Object Engine { get; set; }
public static SqlCeEngine Create(String connstr)
{
if (EngineType == null) return null;
if (String.IsNullOrEmpty(connstr)) return null;
try
{
var e = EngineType.CreateInstance(connstr);
if (e == null) return null;
var sce = new SqlCeEngine()
{
Engine = e
};
return sce;
}
catch { return null; }
}
public void Dispose() => Engine.TryDispose();
public SqlCeEngine CreateDatabase() { Engine.Invoke("CreateDatabase"); return this; }
public SqlCeEngine Shrink() { Engine.Invoke("Shrink"); return this; }
}
}
#endif
|