652 lines
24 KiB
C#
652 lines
24 KiB
C#
using System;
|
||
using CYQ.Data.Table;
|
||
using System.Data;
|
||
using CYQ.Data.SQL;
|
||
using System.IO;
|
||
using System.Collections.Generic;
|
||
using System.Text;
|
||
|
||
|
||
namespace CYQ.Data.Tool
|
||
{
|
||
/// <summary>
|
||
/// 数据库工具类[都是静态方法]
|
||
/// </summary>
|
||
public static partial class DBTool
|
||
{
|
||
|
||
private static StringBuilder _ErrorMsg = new StringBuilder();
|
||
|
||
/// <summary>
|
||
/// 获取异常的信息
|
||
/// </summary>
|
||
public static string ErrorMsg
|
||
{
|
||
get
|
||
{
|
||
return _ErrorMsg.ToString();
|
||
}
|
||
set
|
||
{
|
||
_ErrorMsg.Length = 0;
|
||
if (value != null)
|
||
{
|
||
_ErrorMsg.Append(value);
|
||
}
|
||
}
|
||
}
|
||
|
||
|
||
#region 库层面操作
|
||
/// <summary>
|
||
/// 获取数据库链接的数据库类型
|
||
/// </summary>
|
||
/// <param name="conn">链接配置Key或数据库链接语句</param>
|
||
/// <returns></returns>
|
||
public static DataBaseType GetDataBaseType(string conn)
|
||
{
|
||
return ConnBean.Create(conn).ConnDataBaseType;
|
||
}
|
||
/// <summary>
|
||
/// 获取指定数据库的数据类型
|
||
/// </summary>
|
||
/// <param name="ms">单元格结构</param>
|
||
/// <param name="dalType">数据库类型</param>
|
||
/// <param name="version">数据库版本号</param>
|
||
/// <returns></returns>
|
||
public static string GetDataType(MCellStruct ms, DataBaseType dalType, string version)
|
||
{
|
||
return DataType.GetDataType(ms, dalType, version);
|
||
}
|
||
/// <summary>
|
||
/// 测试数据库链接语句
|
||
/// </summary>
|
||
/// <param name="conn">链接配置Key或数据库链接语句</param>
|
||
/// <returns></returns>
|
||
public static bool TestConn(string conn)
|
||
{
|
||
string msg;
|
||
return TestConn(conn, out msg);
|
||
}
|
||
public static bool TestConn(string conn, out string msg)
|
||
{
|
||
bool result = false;
|
||
try
|
||
{
|
||
DalBase helper = DalCreate.CreateDal(conn);
|
||
result = helper.TestConn(AllowConnLevel.Master);
|
||
if (result)
|
||
{
|
||
msg = helper.Version;
|
||
}
|
||
else
|
||
{
|
||
msg = helper.DebugInfo.ToString();
|
||
}
|
||
helper.Dispose();
|
||
}
|
||
catch (Exception err)
|
||
{
|
||
msg = err.Message;
|
||
}
|
||
return result;
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 表的相关操作
|
||
|
||
#region 表是否存在
|
||
/// <summary>
|
||
/// 是否存在（表 U、视图 V 存储过程 P）
|
||
/// </summary>
|
||
/// <param name="name">名称</param>
|
||
public static bool Exists(string name)
|
||
{
|
||
return Exists(name, null);
|
||
}
|
||
/// <summary>
|
||
/// 是否存在（表 U、视图 V 存储过程 P）
|
||
/// </summary>
|
||
/// <param name="name">名称</param>
|
||
/// <param name="type">表 U、视图 V 存储过程 P</param>
|
||
public static bool Exists(string name, string type)
|
||
{
|
||
return Exists(name, type, AppConfig.DB.DefaultConn);
|
||
}
|
||
/// <summary>
|
||
/// 是否存在（表 U、视图 V 存储过程 P）
|
||
/// </summary>
|
||
/// <param name="name">名称</param>
|
||
/// <param name="type">表 U、视图 V 存储过程 P</param>
|
||
/// <param name="conn">指定链接</param>
|
||
/// <returns></returns>
|
||
public static bool Exists(string name, string type, string conn)
|
||
{
|
||
return CrossDB.Exists(name, type, conn);
|
||
|
||
}
|
||
|
||
#endregion
|
||
|
||
#region 创建表语句
|
||
/// <summary>
|
||
/// 为指定的表架构生成SQL(Create Table)语句
|
||
/// </summary>
|
||
public static bool CreateTable(string tableName, MDataColumn columns)
|
||
{
|
||
return CreateTable(tableName, columns, AppConfig.DB.DefaultConn);
|
||
}
|
||
/// <summary>
|
||
/// 为指定的表架构生成SQL(Create Table)语句
|
||
/// </summary>
|
||
public static bool CreateTable(string tableName, MDataColumn columns, string conn)
|
||
{
|
||
if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")"))
|
||
{
|
||
return false;
|
||
}
|
||
bool result = false;
|
||
DataBaseType dalType = GetDataBaseType(conn);
|
||
string dataBase = string.Empty;
|
||
switch (dalType)
|
||
{
|
||
case DataBaseType.Txt:
|
||
case DataBaseType.Xml:
|
||
// string a, b, c;
|
||
conn = AppConfig.GetConn(conn);// CYQ.Data.DAL.DalCreate.GetConnString(conn, out a, out b, out c);
|
||
if (conn.ToLower().Contains(";ts=0"))//不写入表架构。
|
||
{
|
||
//增加缓存
|
||
|
||
result = true;
|
||
}
|
||
else
|
||
{
|
||
tableName = Path.GetFileNameWithoutExtension(tableName);
|
||
string fileName = NoSqlConnection.GetFilePath(conn) + tableName + ".ts";
|
||
result = columns.WriteSchema(fileName);
|
||
dataBase = GetDBInfo(conn).DataBaseName;
|
||
}
|
||
break;
|
||
default:
|
||
#region MyRegion
|
||
|
||
|
||
using (MProc proc = new MProc(null, conn))
|
||
{
|
||
dataBase = proc.DataBaseName;
|
||
try
|
||
{
|
||
proc.dalHelper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;
|
||
proc.SetAopState(Aop.AopOp.CloseAll);
|
||
proc.ResetProc(GetCreateTableSql(tableName, columns, proc.DataBaseType, proc.DataBaseVersion));//.Replace("\n", string.Empty)
|
||
result = proc.ExeNonQuery() > -2;
|
||
if (result)
|
||
{
|
||
//获取扩展说明
|
||
string descriptionSql = GetCreateTableDescriptionSql(tableName, columns, proc.DataBaseType).Replace("\r\n", " ").Trim(' ', ';');
|
||
if (!string.IsNullOrEmpty(descriptionSql))
|
||
{
|
||
if (proc.DataBaseType == DataBaseType.Oracle)
|
||
{
|
||
foreach (string sql in descriptionSql.Split(';'))
|
||
{
|
||
proc.ResetProc(sql);
|
||
if (proc.ExeNonQuery() == -2)
|
||
{
|
||
break;
|
||
}
|
||
|
||
|
||
}
|
||
}
|
||
else
|
||
{
|
||
proc.ResetProc(descriptionSql);
|
||
proc.ExeNonQuery();
|
||
}
|
||
}
|
||
}
|
||
|
||
}
|
||
catch (Exception err)
|
||
{
|
||
Log.Write(err, LogType.DataBase);
|
||
}
|
||
finally
|
||
{
|
||
if (proc.RecordsAffected == -2)
|
||
{
|
||
_ErrorMsg.AppendLine("CreateTable:" + proc.DebugInfo);
|
||
}
|
||
}
|
||
}
|
||
#endregion
|
||
break;
|
||
|
||
|
||
}
|
||
if (result)
|
||
{
|
||
CrossDB.Add(tableName, "U", conn);//修改缓存。
|
||
}
|
||
return result;
|
||
}
|
||
/// <summary>
|
||
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
|
||
/// </summary>
|
||
public static string GetCreateTableDescriptionSql(string tableName)
|
||
{
|
||
return GetCreateTableDescriptionSql(tableName, AppConfig.DB.DefaultConn);
|
||
}
|
||
public static string GetCreateTableDescriptionSql(string tableName, string conn)
|
||
{
|
||
MDataColumn mdc = GetColumns(tableName, conn);
|
||
return GetCreateTableDescriptionSql(tableName, mdc, mdc.DataBaseType);
|
||
}
|
||
/// <summary>
|
||
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
|
||
/// </summary>
|
||
public static string GetCreateTableDescriptionSql(string tableName, MDataColumn columns, DataBaseType dalType)
|
||
{
|
||
return SqlCreateForSchema.CreateTableDescriptionSql(tableName, columns, dalType);
|
||
}
|
||
/// <summary>
|
||
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
|
||
/// </summary>
|
||
public static string GetCreateTableSql(string tableName)
|
||
{
|
||
return GetCreateTableSql(tableName, AppConfig.DB.DefaultConn);
|
||
}
|
||
public static string GetCreateTableSql(string tableName,string conn)
|
||
{
|
||
MDataColumn mdc = GetColumns(tableName, conn);
|
||
return GetCreateTableSql(tableName, mdc, mdc.DataBaseType, mdc.DataBaseVersion);
|
||
}
|
||
/// <summary>
|
||
/// 获取指定的表架构生成的SQL(Create Table)的说明语句
|
||
/// </summary>
|
||
public static string GetCreateTableSql(string tableName, MDataColumn columns, DataBaseType dalType, string version)
|
||
{
|
||
return SqlCreateForSchema.CreateTableSql(tableName, columns, dalType, version);
|
||
}
|
||
internal static void CheckAndCreateOracleSequence(string seqName, string conn, string primaryKey, string tableName)
|
||
{
|
||
seqName = seqName.ToUpper();
|
||
using (DalBase db = DalCreate.CreateDal(conn))
|
||
{
|
||
object o = db.ExeScalar(string.Format(ExistOracleSequence, seqName), false);
|
||
if (db.RecordsAffected != -2 && (o == null || Convert.ToString(o) == "0"))
|
||
{
|
||
int startWith = 1;
|
||
if (!string.IsNullOrEmpty(primaryKey))
|
||
{
|
||
o = db.ExeScalar(string.Format(GetOracleMaxID, primaryKey, tableName), false);
|
||
if (db.RecordsAffected != -2)
|
||
{
|
||
if (!int.TryParse(Convert.ToString(o), out startWith) || startWith < 1)
|
||
{
|
||
startWith = 1;
|
||
}
|
||
else
|
||
{
|
||
startWith++;
|
||
}
|
||
}
|
||
}
|
||
db.ExeNonQuery(string.Format(CreateOracleSequence, seqName, startWith), false);
|
||
}
|
||
if (db.RecordsAffected == -2)
|
||
{
|
||
_ErrorMsg.AppendLine("CheckAndCreateOracleSequence:" + db.DebugInfo.ToString());
|
||
}
|
||
}
|
||
|
||
}
|
||
#endregion
|
||
|
||
#region 修改表语句
|
||
/// <summary>
|
||
/// 获取指定的表架构生成的SQL(Alter Table)的说明语句
|
||
/// </summary>
|
||
public static string GetAlterTableSql(string tableName, MDataColumn columns)
|
||
{
|
||
return GetAlterTableSql(tableName, columns, AppConfig.DB.DefaultConn);
|
||
}
|
||
/// <summary>
|
||
/// 获取指定的表架构生成的SQL(Alter Table)的说明语句
|
||
/// </summary>
|
||
public static string GetAlterTableSql(string tableName, MDataColumn columns, string conn)
|
||
{
|
||
List<string> sqlItems = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
|
||
if (sqlItems.Count > 0)
|
||
{
|
||
StringBuilder sb = new StringBuilder();
|
||
foreach (string sql in sqlItems)
|
||
{
|
||
sb.AppendLine(sql);
|
||
}
|
||
sqlItems = null;
|
||
return sb.ToString();
|
||
}
|
||
return string.Empty;
|
||
}
|
||
/// <summary>
|
||
/// 修改表的列结构
|
||
/// </summary>
|
||
public static bool AlterTable(string tableName, MDataColumn columns)
|
||
{
|
||
return AlterTable(tableName, columns, AppConfig.DB.DefaultConn);
|
||
}
|
||
/// <summary>
|
||
/// 修改表的列结构
|
||
/// </summary>
|
||
/// <param name="tableName">表名</param>
|
||
/// <param name="columns">列结构</param>
|
||
/// <param name="conn">数据库链接</param>
|
||
/// <returns></returns>
|
||
public static bool AlterTable(string tableName, MDataColumn columns, string conn)
|
||
{
|
||
if (columns == null) { return false; }
|
||
List<string> sqls = SqlCreateForSchema.AlterTableSql(tableName, columns, conn);
|
||
if (sqls.Count > 0)
|
||
{
|
||
DataBaseType dalType = DataBaseType.None;
|
||
string database = string.Empty;
|
||
|
||
using (MProc proc = new MProc(null, conn))
|
||
{
|
||
dalType = proc.DataBaseType;
|
||
database = proc.dalHelper.DataBaseName;
|
||
proc.SetAopState(Aop.AopOp.CloseAll);
|
||
if (proc.DataBaseType == DataBaseType.MsSql)
|
||
{
|
||
proc.BeginTransation();//仅对mssql有效。
|
||
}
|
||
foreach (string sql in sqls)
|
||
{
|
||
proc.ResetProc(sql);
|
||
if (proc.ExeNonQuery() == -2)
|
||
{
|
||
proc.RollBack();
|
||
_ErrorMsg.AppendLine("AlterTable:" + proc.DebugInfo);
|
||
Log.Write(proc.DebugInfo, LogType.DataBase);
|
||
|
||
return false;
|
||
}
|
||
}
|
||
proc.EndTransation();
|
||
}
|
||
RemoveCache(tableName, conn);
|
||
return true;
|
||
}
|
||
return false;
|
||
}
|
||
#endregion
|
||
|
||
#region 删除表语句
|
||
/// <summary>
|
||
/// 移除一张表
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public static bool DropTable(string tableName)
|
||
{
|
||
return DropTable(tableName, AppConfig.DB.DefaultConn);
|
||
}
|
||
/// <summary>
|
||
/// 移除一张表
|
||
/// <param name="conn">数据库链接</param>
|
||
/// </summary>
|
||
public static bool DropTable(string tableName, string conn)
|
||
{
|
||
bool result = false;
|
||
string key = string.Empty;
|
||
using (DalBase helper = DalCreate.CreateDal(conn))
|
||
{
|
||
DataBaseType dalType = helper.DataBaseType;
|
||
switch (dalType)
|
||
{
|
||
case DataBaseType.Txt:
|
||
case DataBaseType.Xml:
|
||
string folder = helper.Con.DataSource + Path.GetFileNameWithoutExtension(tableName);
|
||
string path = folder + ".ts";
|
||
try
|
||
{
|
||
if (File.Exists(path))
|
||
{
|
||
result = IOHelper.Delete(path);
|
||
}
|
||
path = folder + (dalType == DataBaseType.Txt ? ".txt" : ".xml");
|
||
if (File.Exists(path))
|
||
{
|
||
result = IOHelper.Delete(path);
|
||
}
|
||
}
|
||
catch
|
||
{
|
||
|
||
}
|
||
break;
|
||
default:
|
||
result = helper.ExeNonQuery("drop table " + Keyword(tableName, dalType), false) != -2;
|
||
if (result)
|
||
{
|
||
//处理表相关的元数据和数据缓存。
|
||
RemoveCache(tableName, conn);
|
||
}
|
||
break;
|
||
}
|
||
if (helper.RecordsAffected == -2)
|
||
{
|
||
_ErrorMsg.AppendLine(helper.DebugInfo.ToString());
|
||
}
|
||
}
|
||
if (result)
|
||
{
|
||
//处理数据库表字典缓存
|
||
CrossDB.Remove(tableName, "U", conn);
|
||
}
|
||
return result;
|
||
}
|
||
|
||
#endregion
|
||
|
||
|
||
#endregion
|
||
|
||
#region 获取结构
|
||
|
||
/// <summary>
|
||
/// 获取表列架构
|
||
/// </summary>
|
||
public static MDataColumn GetColumns(Type typeInfo)
|
||
{
|
||
return TableSchema.GetColumnByType(typeInfo);
|
||
}
|
||
/// <summary>
|
||
/// 获取表列架构
|
||
/// </summary>
|
||
/// <param name="tableName">表名</param>
|
||
public static MDataColumn GetColumns(object tableNameObj)
|
||
{
|
||
string conn = string.Empty;
|
||
if (tableNameObj is Enum)
|
||
{
|
||
conn = CrossDB.GetConnByEnum(tableNameObj as Enum);
|
||
}
|
||
return GetColumns(Convert.ToString(tableNameObj), conn);
|
||
}
|
||
/// <summary>
|
||
/// 获取表列架构（链接错误时，抛异常）
|
||
/// </summary>
|
||
/// <param name="tableName">表名</param>
|
||
/// <param name="conn">数据库链接</param>
|
||
/// <param name="errInfo">出错时的错误信息</param>
|
||
/// <returns></returns>
|
||
public static MDataColumn GetColumns(string tableName, string conn, out string errInfo)
|
||
{
|
||
errInfo = string.Empty;
|
||
try
|
||
{
|
||
return TableSchema.GetColumns(tableName, conn);
|
||
|
||
}
|
||
catch (Exception err)
|
||
{
|
||
errInfo = err.Message;
|
||
return null;
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 获取表列架构
|
||
/// </summary>
|
||
/// <param name="tableName">表名</param>
|
||
/// <param name="conn">数据库链接</param>
|
||
/// <returns></returns>
|
||
public static MDataColumn GetColumns(string tableName, string conn)
|
||
{
|
||
string err;
|
||
return GetColumns(tableName, conn, out err);
|
||
}
|
||
#endregion
|
||
|
||
|
||
#region 其它操作
|
||
//private static List<string> flag = new List<string>(2);
|
||
//internal static void CreateSelectBaseProc(DalType dal, string conn)
|
||
//{
|
||
// try
|
||
// {
|
||
// switch (dal)
|
||
// {
|
||
// //case DalType.Oracle:
|
||
// // if (!flag.Contains("oracle"))
|
||
// // {
|
||
// // flag.Add("oracle");
|
||
// // using (DbBase db = DalCreate.CreateDal(conn))
|
||
// // {
|
||
// // db.AllowRecordSql = false;
|
||
// // object o = db.ExeScalar(string.Format(ExistOracle.Replace("TABLE", "PROCEDURE"), "MyPackage.SelectBase"), false);
|
||
// // if (o != null && Convert.ToInt32(o) < 1)
|
||
// // {
|
||
// // db.ExeNonQuery(SqlPager.GetPackageHeadForOracle(), false);
|
||
// // db.ExeNonQuery(SqlPager.GetPackageBodyForOracle(), false);
|
||
// // }
|
||
// // }
|
||
// // }
|
||
// // break;
|
||
// case DalType.MsSql:
|
||
// if (!flag.Contains("sql"))
|
||
// {
|
||
// flag.Add("sql");//考虑到一个应用不太可能同时使用mssql的不同版本，只使用一个标识。
|
||
// using (DbBase db = DalCreate.CreateDal(conn))
|
||
// {
|
||
// db.IsRecordDebugInfo = false;
|
||
// object o = null;
|
||
// if (!db.Version.StartsWith("08"))
|
||
// {
|
||
// // o = db.ExeScalar(string.Format(Exist2000.Replace("U", "P"), "SelectBase"), false);
|
||
// // if (o != null && Convert.ToInt32(o) < 1)
|
||
// // {
|
||
// // db.ExeNonQuery(SqlPager.GetSelectBaseForSql2000(), false);
|
||
// // }
|
||
// //}
|
||
// //else
|
||
// //{
|
||
// o = db.ExeScalar(string.Format(TableSchema.Exist2005, "SelectBase", "P"), false);
|
||
// if (o != null && Convert.ToInt32(o) < 1)
|
||
// {
|
||
// db.ExeNonQuery(SqlCreateForPager.GetSelectBaseForSql2005(), false);
|
||
// }
|
||
// }
|
||
// }
|
||
// }
|
||
// break;
|
||
// }
|
||
// }
|
||
// catch (Exception err)
|
||
// {
|
||
// Log.Write(err, LogType.DataBase);
|
||
// }
|
||
//}
|
||
|
||
/// <summary>
|
||
/// 为字段或表名添加关键字标签：如[],''等符号
|
||
/// </summary>
|
||
/// <param name="name">表名或字段名</param>
|
||
/// <param name="dalType">数据类型</param>
|
||
/// <returns></returns>
|
||
public static string Keyword(string name, DataBaseType dalType)
|
||
{
|
||
return SqlFormat.Keyword(name, dalType);
|
||
}
|
||
/// <summary>
|
||
/// 取消字段或表名添加关键字标签：如[],''等符号
|
||
/// </summary>
|
||
/// <param name="name">表名或字段名</param>
|
||
public static string NotKeyword(string name)
|
||
{
|
||
return SqlFormat.NotKeyword(name);
|
||
}
|
||
|
||
|
||
/// <summary>
|
||
/// 将各数据库默认值格式化成标准值，将标准值还原成各数据库默认值
|
||
/// </summary>
|
||
/// <param name="flag">[0:转成标准值],[1:转成各数据库值]</param>
|
||
/// <returns></returns>
|
||
public static string FormatDefaultValue(DataBaseType dalType, object value, int flag, SqlDbType sqlDbType)
|
||
{
|
||
return SqlFormat.FormatDefaultValue(dalType, value, flag, sqlDbType);
|
||
}
|
||
|
||
#endregion
|
||
|
||
private static void RemoveCache(string tableName, string conn)
|
||
{
|
||
//清缓存
|
||
string key = Cache.CacheManage.GetKey(Cache.CacheKeyType.Schema, tableName, conn);
|
||
Cache.CacheManage.LocalInstance.Remove(key);
|
||
key = Cache.CacheManage.GetKey(Cache.CacheKeyType.AutoCache, tableName, conn);
|
||
Cache.AutoCache.ReadyForRemove(key);
|
||
}
|
||
}
|
||
public static partial class DBTool
|
||
{
|
||
/// <summary>
|
||
/// 获取配置项中所有的数据库列表
|
||
/// </summary>
|
||
public static Dictionary<int, DBInfo> DataBases
|
||
{
|
||
get
|
||
{
|
||
return DBSchema.DBScheams;
|
||
|
||
}
|
||
}
|
||
/// <summary>
|
||
/// 获取单个数据库信息
|
||
/// </summary>
|
||
/// <returns></returns>
|
||
public static DBInfo GetDBInfo(string conn)
|
||
{
|
||
return DBSchema.GetSchema(conn);
|
||
}
|
||
public static TableInfo GetTableInfo(string tableName)
|
||
{
|
||
return CrossDB.GetTableInfoByName(tableName);
|
||
}
|
||
public static TableInfo GetTableInfo(string tableName, string conn)
|
||
{
|
||
return CrossDB.GetTableInfoByName(tableName, conn);
|
||
}
|
||
}
|
||
public static partial class DBTool
|
||
{
|
||
internal const string ExistOracleSequence = "SELECT count(*) FROM All_Sequences where Sequence_name='{0}'";
|
||
internal const string CreateOracleSequence = "create sequence {0} start with {1} increment by 1";
|
||
internal const string GetOracleMaxID = "select max({0}) from {1}";
|
||
}
|
||
}
|