/// <summary>
/// 移除一张表
/// <param name="conn">数据库链接</param>
/// </summary>
public static bool DropTable(string tableName, string conn)
{
bool result = false;
string key = string.Empty;
using (DbBase helper = DalCreate.CreateDal(conn))
{
key = TableSchema.GetTableCacheKey(helper);
DalType dalType = helper.dalType;
switch (dalType)
{
case DalType.Txt:
case DalType.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 == DalType.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, helper.DataBase, dalType);
}
break;
}
if (helper.recordsAffected == -2)
{
_ErrorMsg.AppendLine(helper.debugInfo.ToString());
}
}
if (result)
{
//处理数据库表字典缓存
if (TableSchema.tableCache.ContainsKey(key))
{
Dictionary <string, string> tableDic = TableSchema.tableCache[key];
if (tableDic.ContainsKey(tableName))
{
tableDic.Remove(tableName);
}
}
}
return(result);
}
internal static void CheckAndCreateOracleSequence(string seqName, string conn, string primaryKey, string tableName)
{
seqName = seqName.ToUpper();
using (DbBase db = DalCreate.CreateDal(conn))
{
object o = db.ExeScalar(string.Format(TableSchema.ExistOracleSequence, seqName), false);
if (o == null || Convert.ToString(o) == "0")
{
int startWith = 1;
if (!string.IsNullOrEmpty(primaryKey))
{
o = db.ExeScalar(string.Format(TableSchema.GetOracleMaxID, primaryKey, tableName), false);
if (!int.TryParse(Convert.ToString(o), out startWith) || startWith < 1)
{
startWith = 1;
}
else
{
startWith++;
}
}
db.ExeNonQuery(string.Format(TableSchema.CreateOracleSequence, seqName, startWith), false);
}
}
}
/// <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;
DbBase helper = DalCreate.CreateDal(conn);
helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况
if (!helper.TestConn())
{
errInfo = helper.debugInfo.ToString();
if (string.IsNullOrEmpty(errInfo))
{
errInfo = "Open database fail : " + tableName;
}
helper.Dispose();
Error.Throw(errInfo);
return(null);
}
if (!tableName.Contains(" "))
{
tableName = GetMapTableName(conn, tableName);
}
MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper);
helper.Dispose();
return(mdc);
}
private void GetProcs()
{
if (_Procs.Count == 0)
{
lock (lockProcObj)
{
if (_Procs.Count == 0)
{
using (DalBase dal = DalCreate.CreateDal(ConnString))
{
Dictionary <string, string> procs = dal.GetProcs();
if (procs != null && procs.Count > 0)
{
Dictionary <string, TableInfo> dic = new Dictionary <string, TableInfo>();
foreach (KeyValuePair <string, string> item in procs)
{
string hash = TableInfo.GetHashKey(item.Key);
if (!dic.ContainsKey(hash))
{
dic.Add(hash, new TableInfo(item.Key, "P", item.Value, this));
}
}
_Procs = dic;
}
}
}
}
}
}
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());
}
}
}
private static DBInfo GetSchemaDic(string conn)
{
DBInfo info = new DBInfo();
using (DalBase dal = DalCreate.CreateDal(conn))
{
info.ConnName = dal.ConnObj.Master.ConnName;
info.ConnString = dal.ConnObj.Master.ConnString;
info.DataBaseName = dal.DataBaseName;
info.DataBaseType = dal.DataBaseType;
info.DataBaseVersion = dal.Version;
Dictionary <string, string> tables = dal.GetTables();
if (tables != null && tables.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in tables)
{
int hash = TableInfo.GetHashCode(item.Key);
if (!dic.ContainsKey(hash))
{
dic.Add(hash, new TableInfo(item.Key, "U", item.Value, info));
}
}
info.Tables = dic;
}
Dictionary <string, string> views = dal.GetViews();
if (views != null && views.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in views)
{
int hash = TableInfo.GetHashCode(item.Key);
if (!dic.ContainsKey(hash))
{
dic.Add(hash, new TableInfo(item.Key, "V", item.Value, info));
}
}
info.Views = dic;
}
Dictionary <string, string> procs = dal.GetProcs();
if (procs != null && procs.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in procs)
{
int hash = TableInfo.GetHashCode(item.Key);
if (!dic.ContainsKey(hash))
{
dic.Add(hash, new TableInfo(item.Key, "P", item.Value, info));
}
}
info.Procs = dic;
}
}
return(info);
}
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.IsAllowRecordSql = 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.WriteLogToTxt(err);
}
}
private void GetVersion()
{
if (string.IsNullOrEmpty(_DataBaseVersion))
{
using (DalBase dal = DalCreate.CreateDal(ConnString))
{
_DataBaseVersion = dal.Version;
}
}
}
internal bool LoadDataInsert(DalType dalType, bool keepID)
{
bool fillGUID = CheckGUIDAndDateTime(dalType);
bool isNeedCreateDal = (_dalHelper == null);
if (isNeedCreateDal && dalType != DalType.Oracle)
{
_dalHelper = DalCreate.CreateDal(_Conn);
_dalHelper.isAllowInterWriteLog = false;
}
string path = MDataTableToFile(mdt, fillGUID ? true : keepID, dalType);
string formatSql = dalType == DalType.MySql ? SqlCreate.MySqlBulkCopySql : SqlCreate.OracleBulkCopySql;
string sql = string.Format(formatSql, path, SqlFormat.Keyword(mdt.TableName, dalType),
AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepID, dalType));
if (dalType == DalType.Oracle)
{
string ctlPath = CreateCTL(sql, path);
sql = string.Format(SqlCreate.OracleSqlIDR, "sa/123456@ORCL", ctlPath, ctlPath.Replace(".ctl", ".out"));//只能用进程处理
}
try
{
if (dalType == DalType.Oracle)
{
return(ExeSqlLoader(sql));
}
else
{
if (_dalHelper.ExeNonQuery(sql, false) != -2)
{
return(true);
}
}
}
catch (Exception err)
{
if (err.InnerException != null)
{
err = err.InnerException;
}
sourceTable.DynamicData = err;
Log.WriteLogToTxt(err);
}
finally
{
if (isNeedCreateDal && _dalHelper != null)
{
_dalHelper.Dispose();
_dalHelper = null;
}
// File.Delete(path);
}
return(false);
}
/// <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);
}
internal bool OracleBulkCopyInsert()
{
CheckGUIDAndDateTime(DalType.Oracle);
string conn = DalCreate.FormatConn(DalType.Oracle, AppConfig.GetConn(_Conn));
Assembly ass = OracleDal.GetAssembly();
object sbc = ass.CreateInstance("Oracle.DataAccess.Client.OracleBulkCopy", false, BindingFlags.CreateInstance, null, new object[] { conn }, null, null);
Type sbcType = sbc.GetType();
try
{
sbcType.GetProperty("BatchSize").SetValue(sbc, 100000, null);
sbcType.GetProperty("BulkCopyTimeout").SetValue(sbc, AppConfig.DB.CommandTimeout, null);
sbcType.GetProperty("DestinationTableName").SetValue(sbc, SqlFormat.Keyword(mdt.TableName, DalType.Oracle), null);
PropertyInfo cInfo = sbcType.GetProperty("ColumnMappings");
object cObj = cInfo.GetValue(sbc, null);
MethodInfo addMethod = cInfo.PropertyType.GetMethods()[4];
foreach (MCellStruct column in mdt.Columns)
{
addMethod.Invoke(cObj, new object[] { column.ColumnName, column.ColumnName });
}
sbcType.GetMethods()[4].Invoke(sbc, new object[] { mdt });
return(true);
}
catch (Exception err)
{
if (err.InnerException != null)
{
err = err.InnerException;
}
sourceTable.DynamicData = err;
Log.WriteLogToTxt(err);
return(false);
}
finally
{
sbcType.GetMethod("Dispose").Invoke(sbc, null);
}
//using (Oracle.DataAccess.Client.OracleBulkCopy sbc = new OracleBulkCopy(conn, OracleBulkCopyOptions.Default))
//{
// sbc.BatchSize = 100000;
// sbc.DestinationTableName = mdt.TableName;
// foreach (MCellStruct column in mdt.Columns)
// {
// sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
// }
// sbc.WriteToServer(mdt);
//}
//return true;
}
/// <summary>
/// 事务回滚
/// </summary>
public static bool RollBack(string conn)
{
string key = StaticTool.GetTransationKey(conn);
TransationKeys.Remove(key);
DalBase dal = DalCreate.Get(key);
if (dal != null && dal.RollBack())
{
dal.Dispose();
return(DalCreate.Remove(key));
}
return(false);
}
/// <summary>
/// 提交事务
/// </summary>
public static bool EndTransation(string conn)
{
string key = StaticTool.GetTransationKey(conn);
TransationKeys.Remove(key);
DalBase dal = DalCreate.Get(key);
if (dal != null && dal.EndTransaction())//如果事务回滚了，
{
dal.Dispose();
return(DalCreate.Remove(key));
}
return(false);
}
/// <summary>
/// 获取表架构
/// </summary>
/// <param name="conn">数据库链接</param>
/// <param name="connectionName">指定要返回架构的名称</param>
/// <param name="restrictionValues">为指定的架构返回一组限制值</param>
public static DataTable GetSchema(string conn, string connectionName, string[] restrictionValues)
{
DbBase helper = DalCreate.CreateDal(conn);
if (!helper.TestConn())
{
return(null);
}
helper.Con.Open();
DataTable dt = helper.Con.GetSchema(connectionName, restrictionValues);
helper.Con.Close();
helper.Dispose();
return(dt);
}
/// <summary>
/// 检测表是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="conn">数据库链接</param>
/// <param name="dalType">数据库类型</param>
public static bool ExistsTable(string tableName, string conn, out DalType dalType, out string database)
{
dalType = DalType.None;
database = string.Empty;
if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")"))
{
return(false);
}
using (DalBase helper = DalCreate.CreateDal(conn))
{
dalType = helper.DataBaseType;
database = helper.DataBase;
}
return(TableSchema.Exists(tableName, "U", conn));
}
/// <summary>
/// 获得表的描述
/// </summary>
internal static string GetTableDescription(string conn, string tableName)
{
using (DbBase dbBase = DalCreate.CreateDal(conn))
{
string key = GetTableCacheKey(dbBase);
if (tableCache.ContainsKey(key))
{
if (tableCache[key].ContainsKey(tableName))
{
return(tableCache[key][tableName]);
}
}
return(string.Empty);
}
}
/// <summary>
/// 检测表是否存在
/// </summary>
/// <param name="tableName">表名</param>
/// <param name="conn">数据库链接</param>
/// <param name="dalType">数据库类型</param>
public static bool ExistsTable(string tableName, string conn, out DalType dalType, out string database)
{
dalType = DalType.None;
database = string.Empty;
if (string.IsNullOrEmpty(tableName) || tableName.Contains("(") && tableName.Contains(")"))
{
return(false);
}
DbBase helper = DalCreate.CreateDal(conn);
dalType = helper.dalType;
database = helper.DataBase;
bool result = TableSchema.Exists("U", tableName, ref helper);
helper.Dispose();
return(result);
}
private static DBInfo GetSchemaDic(string conn)
{
DalBase dal = DalCreate.CreateDal(conn);
DBInfo info = new DBInfo();
info.ConnName = dal.ConnObj.Master.ConnName;
info.ConnString = dal.ConnObj.Master.ConnString;
info.DataBaseName = dal.DataBase;
Dictionary <string, string> tables = TableSchema.GetTables(conn);
if (tables != null && tables.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in tables)
{
dic.Add(TableSchema.GetTableHash(item.Key), new TableInfo(item.Key, "U", item.Value, info));
}
info.Tables = dic;
}
Dictionary <string, string> views = TableSchema.GetViews(conn);
if (views != null && views.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in views)
{
dic.Add(TableSchema.GetTableHash(item.Key), new TableInfo(item.Key, "V", item.Value, info));
}
info.Views = dic;
}
Dictionary <string, string> procs = TableSchema.GetProcs(conn);
if (procs != null && procs.Count > 0)
{
Dictionary <int, TableInfo> dic = new Dictionary <int, TableInfo>();
foreach (KeyValuePair <string, string> item in procs)
{
dic.Add(TableSchema.GetTableHash(item.Key), new TableInfo(item.Key, "P", item.Value, info));
}
info.Procs = dic;
}
return(info);
}
/// <summary>
/// 获取所有表（表名+表说明）【链接错误时，抛异常】
/// </summary>
/// <param name="conn">数据库链接</param>
/// <param name="dbName">返回指定链接的数据库名称</param>
/// <param name="errInfo">链接错误时的信息信息</param>
public static Dictionary <string, string> GetTables(string conn, out string dbName, out string errInfo)
{
errInfo = string.Empty;
using (DalBase helper = DalCreate.CreateDal(conn))
{
dbName = helper.DataBase;
if (!helper.TestConn(AllowConnLevel.MaterBackupSlave))
{
errInfo = helper.DebugInfo.ToString();
if (string.IsNullOrEmpty(errInfo))
{
errInfo = helper.DataBaseType + " Open database fail : " + dbName;
}
return(null);
}
}
return(TableSchema.GetTables(conn));//内部有缓存
}
internal bool MySqlBulkCopyInsert(bool keepID)
{
bool fillGUID = CheckGUIDAndDateTime(DalType.MySql);
string conn = DalCreate.FormatConn(DalType.MySql, AppConfig.GetConn(_Conn));
bool isNeedCreateDal = (_dalHelper == null);
if (isNeedCreateDal)
{
_dalHelper = DalCreate.CreateDal(conn);
_dalHelper.isAllowInterWriteLog = false;
}
string path = MDataTableToFile(mdt, fillGUID ? true : keepID);
string sql = string.Format(SqlCreate.MySqlBulkCopySql, path, SqlFormat.Keyword(mdt.TableName, DalType.MySql),
AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepID, DalType.MySql));
try
{
if (_dalHelper.ExeNonQuery(sql, false) != -2)
{
return(true);
}
}
catch (Exception err)
{
if (err.InnerException != null)
{
err = err.InnerException;
}
sourceTable.DynamicData = err;
Log.WriteLogToTxt(err);
}
finally
{
if (isNeedCreateDal)
{
_dalHelper.Dispose();
_dalHelper = null;
}
// File.Delete(path);
}
return(false);
}
/// <summary>
/// 移除一张表
/// <param name="conn">数据库链接</param>
/// </summary>
public static bool DropTable(string tableName, string conn)
{
bool result = false;
using (DbBase helper = DalCreate.CreateDal(conn))
{
DalType dalType = helper.dalType;
switch (dalType)
{
case DalType.Txt:
case DalType.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 == DalType.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, helper.DataBase, dalType);
}
break;
}
}
return(result);
}
/// <summary>
/// 获取所有表（表名+表说明）【链接错误时，抛异常】
/// </summary>
/// <param name="conn">数据库链接</param>
/// <param name="dbName">返回指定链接的数据库名称</param>
/// <param name="errInfo">链接错误时的信息信息</param>
public static Dictionary <string, string> GetTables(string conn, out string dbName, out string errInfo)
{
errInfo = string.Empty;
DbBase helper = DalCreate.CreateDal(conn);
helper.IsAllowRecordSql = false;
dbName = helper.DataBase;
if (!helper.TestConn())
{
errInfo = helper.debugInfo.ToString();
if (string.IsNullOrEmpty(errInfo))
{
errInfo = "Open database fail : " + dbName;
}
helper.Dispose();
return(null);
}
Dictionary <string, string> tables = TableSchema.GetTables(ref helper);//内部有缓存
helper.Dispose();
return(tables);
}
/// <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;
DbBase helper = DalCreate.CreateDal(conn);
DbResetResult result = helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况
switch (result)
{
case DbResetResult.No_DBNoExists:
helper.Dispose();
return(null);
case DbResetResult.No_SaveDbName:
case DbResetResult.Yes:
tableName = SqlFormat.NotKeyword(tableName); //same database no need database.tablename
break;
}
if (!helper.TestConn(AllowConnLevel.MaterBackupSlave))
{
errInfo = helper.debugInfo.ToString();
if (string.IsNullOrEmpty(errInfo))
{
errInfo = "Open database fail : " + tableName;
}
helper.Dispose();
Error.Throw(errInfo);
return(null);
}
if (!tableName.Contains(" "))//
{
tableName = GetMapTableName(conn, tableName);
}
MDataColumn mdc = TableSchema.GetColumns(tableName, ref helper);
helper.Dispose();
return(mdc);
}
public static bool TestConn(string conn, out string msg)
{
bool result = false;
try
{
DbBase helper = DalCreate.CreateDal(conn);
result = helper.TestConn();
if (result)
{
msg = helper.Version;
}
else
{
msg = helper.debugInfo.ToString();
}
helper.Dispose();
}
catch (Exception err)
{
msg = err.Message;
}
return(result);
}
internal bool LoadDataInsert(DataBaseType dalType, bool keepid)
{
bool fillGUID = CheckGUIDAndDateTime(dalType);
bool isNeedCreateDal = (_dalHelper == null);
if (isNeedCreateDal && dalType != DataBaseType.Oracle)
{
_dalHelper = DalCreate.CreateDal(_Conn);
_dalHelper.IsWriteLogOnError = false;
}
string path = MDataTableToFile(mdt, fillGUID ? true : keepid, dalType);
string formatSql = dalType == DataBaseType.MySql ? SqlCreate.MySqlBulkCopySql : SqlCreate.OracleBulkCopySql;
string sql = string.Format(formatSql, path, SqlFormat.Keyword(mdt.TableName, dalType),
AppConst.SplitChar, SqlCreate.GetColumnName(mdt.Columns, keepid, dalType));
if (dalType == DataBaseType.Oracle)
{
string ctlPath = CreateCTL(sql, path);
sql = string.Format(SqlCreate.OracleSqlidR, "sa/123456@ORCL", ctlPath);//只能用进程处理
}
try
{
if (dalType == DataBaseType.Oracle)
{
return(ExeSqlLoader(sql));
}
else
{
bool isGoOn = true;
if (IsTruncate)
{
_dalHelper.IsOpenTrans = true;//开启事务
isGoOn = _dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(mdt.TableName, dalTypeTo)), false) != -2;
}
if (isGoOn && _dalHelper.ExeNonQuery(sql, false) != -2)
{
return(true);
}
}
}
catch (Exception err)
{
if (err.InnerException != null)
{
err = err.InnerException;
}
sourceTable.DynamicData = err;
Log.Write(err, LogType.DataBase);
}
finally
{
if (isNeedCreateDal && _dalHelper != null)
{
_dalHelper.EndTransaction();
_dalHelper.Dispose();
_dalHelper = null;
}
IOHelper.Delete(path);//删除文件。
}
return(false);
}
internal bool MsSqlBulkCopyInsert(bool keepid)
{
SqlTransaction sqlTran = null;
SqlConnection con = null;
bool isCreateDal = false;
try
{
CheckGUIDAndDateTime(DataBaseType.MsSql);
string conn = AppConfig.GetConn(_Conn);
if (_dalHelper == null)
{
if (IsTruncate)
{
isCreateDal = true;
_dalHelper = DalCreate.CreateDal(conn);
}
else
{
con = new SqlConnection(conn);
con.Open();
}
}
bool isGoOn = true;
if (_dalHelper != null)
{
if (IsTruncate)
{
_dalHelper.IsOpenTrans = true;
if (_dalHelper.ExeNonQuery(string.Format(SqlCreate.TruncateTable, SqlFormat.Keyword(mdt.TableName, dalTypeTo)), false) == -2)
{
isGoOn = false;
sourceTable.DynamicData = _dalHelper.DebugInfo;
Log.Write(_dalHelper.DebugInfo.ToString(), LogType.DataBase);
}
}
if (isGoOn)
{
con = _dalHelper.Con as SqlConnection;
_dalHelper.OpenCon(null, AllowConnLevel.MasterBackup);//如果未开启，则开启，打开链接后，如果以前没执行过数据，事务对象为空，这时会产生事务对象
sqlTran = _dalHelper._tran as SqlTransaction;
}
}
if (isGoOn)
{
using (SqlBulkCopy sbc = new SqlBulkCopy(con, (keepid ? SqlBulkCopyOptions.KeepIdentity : SqlBulkCopyOptions.Default) | SqlBulkCopyOptions.FireTriggers, sqlTran))
{
sbc.BatchSize = 100000;
sbc.DestinationTableName = SqlFormat.Keyword(mdt.TableName, DataBaseType.MsSql);
sbc.BulkCopyTimeout = AppConfig.DB.CommandTimeout;
foreach (MCellStruct column in mdt.Columns)
{
sbc.ColumnMappings.Add(column.ColumnName, column.ColumnName);
}
if (AppConfig.IsAspNetCore)
{
sbc.WriteToServer(mdt.ToDataTable());
}
else
{
sbc.WriteToServer(mdt);
}
}
}
return(true);
}
catch (Exception err)
{
sourceTable.DynamicData = err;
Log.Write(err, LogType.DataBase);
}
finally
{
if (_dalHelper == null)
{
con.Close();
con = null;
}
else if (isCreateDal)
{
_dalHelper.EndTransaction();
_dalHelper.Dispose();
}
}
return(false);
}
internal static Dictionary <string, string> GetSchemas(string conn, string type)
{
ConnBean connBean = ConnBean.Create(conn);
int hash = connBean.GetHashCode();
Dictionary <int, Dictionary <string, string> > schemaDic = null;
switch (type)
{
case "U":
schemaDic = _TableCache;
break;
case "V":
schemaDic = _ViewCache;
break;
case "P":
schemaDic = _ProcCache;
break;
}
if (schemaDic.ContainsKey(hash))
{
return(schemaDic[hash]);
}
Dictionary <string, string> tables = null;
#region 查看有没有表架构缓存
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + hash + ".ts";
if (System.IO.File.Exists(fullPath))
{
tables = JsonHelper.Split(IOHelper.ReadAllText(fullPath));
}
}
#endregion
if (tables == null)
{
lock (ot)
{
if (!schemaDic.ContainsKey(hash))
{
#region 从数据库读
using (DalBase helper = DalCreate.CreateDal(connBean.ConnName))
{
helper.IsRecordDebugInfo = false;
switch (type)
{
case "U":
tables = helper.GetTables();
break;
case "V":
tables = helper.GetViews();
break;
case "P":
tables = helper.GetProcs();
break;
}
}
}
else
{
return(schemaDic[hash]);
}
}
#endregion
}
#region 写入表结构映射
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + hash + ".ts";
IOHelper.Save(fullPath, JsonHelper.ToJson(tables), false, true);
}
#endregion
#region 写入缓存
if (!schemaDic.ContainsKey(hash) && tables != null && tables.Count > 0)//读不到表不缓存。
{
schemaDic.Add(hash, tables);
}
#endregion
return(tables);
}
/// <summary>
/// 获取数据库链接的数据库类型
/// </summary>
/// <param name="conn">链接配置Key或数据库链接语句</param>
/// <returns></returns>
public static DalType GetDalType(string conn)
{
return(DalCreate.GetDalTypeByConn(conn));
}
/// <summary>
/// 获取指定的表架构生成的SQL(Alter Table)语句
/// </summary>
public static List <string> AlterTableSql(string tableName, MDataColumn columns, string conn)
{
List <string> sql = new List <string>();
DbBase helper = DalCreate.CreateDal(conn);
helper.ChangeDatabaseWithCheck(tableName);//检测dbname.dbo.tablename的情况
if (!helper.TestConn())
{
helper.Dispose();
return(sql);
}
DalType dalType = helper.dalType;
string version = helper.Version;
MDataColumn dbColumn = TableSchema.GetColumns(tableName, ref helper);//获取数据库的列结构
helper.Dispose();
//开始比较异同
List <MCellStruct> primaryKeyList = new List <MCellStruct>();
string tbName = SqlFormat.Keyword(tableName, dalType);
string alterTable = "alter table " + tbName;
foreach (MCellStruct ms in columns)//遍历新的结构
{
string cName = SqlFormat.Keyword(ms.ColumnName, dalType);
if (ms.AlterOp != AlterOp.None)
{
bool isContains = dbColumn.Contains(ms.ColumnName);
if (ms.AlterOp == AlterOp.Drop)
{
#region MyRegion
if (isContains)
{
switch (dalType)
{
case DalType.MsSql:
case DalType.Access:
case DalType.MySql:
case DalType.Oracle:
if (dalType == DalType.MsSql)
{
sql.Add(@"declare @name varchar(50) select @name =b.name from sysobjects b join syscolumns a on b.id = a.cdefault
where a.id = object_id('" + tableName + "') and a.name ='" + ms.ColumnName + "'if(@name!='') begin EXEC('alter table Base_Button drop constraint '+ @name) end");
}
sql.Add(alterTable + " drop column " + cName);
break;
case DalType.Sybase:
sql.Add(alterTable + " drop " + cName);
break;
}
}
#endregion
}
else if (ms.AlterOp == AlterOp.Rename)
{
#region MyRegion
if (!string.IsNullOrEmpty(ms.OldName) && ms.OldName != ms.ColumnName && !isContains)
{
string oName = SqlFormat.Keyword(ms.OldName, dalType);
switch (dalType)
{
case DalType.MsSql:
sql.Add("exec sp_rename '" + tbName + "." + oName + "', '" + ms.ColumnName + "', 'column'");
break;
case DalType.Sybase:
sql.Add("exec sp_rename \"" + tableName + "." + ms.OldName + "\", " + ms.ColumnName);
break;
case DalType.MySql:
sql.Add(alterTable + " change " + oName + " " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(','));
break;
case DalType.Oracle:
sql.Add(alterTable + " rename column " + oName + " to " + cName);
break;
}
}
#endregion
}
else if (ms.AlterOp == AlterOp.AddOrModify)
{
string alterSql = SqlFormat.Keyword(ms.ColumnName, dalType) + " " + DataType.GetDataType(ms, dalType, version);
//智能判断
if (isContains) // 存在，则修改
{
//检测是否相同
MCellStruct dbStruct = dbColumn[ms.ColumnName];
if (dbStruct.IsCanNull != ms.IsCanNull || dbStruct.SqlType != ms.SqlType || dbStruct.MaxSize != ms.MaxSize || dbStruct.Scale != ms.Scale)
{
string modify = "";
switch (dalType)
{
case DalType.Oracle:
case DalType.Sybase:
modify = " modify ";
break;
case DalType.MySql:
modify = " change " + cName + " ";
break;
case DalType.MsSql:
case DalType.Access:
modify = " alter column ";
break;
}
if (ms.IsCanNull != dbStruct.IsCanNull)
{
alterSql += (ms.IsCanNull ? " NULL" : " NOT NULL");
}
sql.Add(alterTable + modify + alterSql);
}
}
else //不在，则添加
{
sql.Add(alterTable + " add " + GetKey(ms, dalType, ref primaryKeyList, version).TrimEnd(','));
if (!string.IsNullOrEmpty(ms.Description))
{
string description = SqlCreateForSchema.GetTableDescriptionSql(tableName, ms, dalType, true);
if (!string.IsNullOrEmpty(description))
{
sql.Add(description);
}
}
}
}
}
}
return(sql);
}
public static MDataColumn GetColumns(string tableName, string conn)
{
string key = GetSchemaKey(tableName, conn);
#region 缓存检测
if (_ColumnCache.ContainsKey(key))
{
return(_ColumnCache[key].Clone());
}
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string fullPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath + key + ".ts";
if (System.IO.File.Exists(fullPath))
{
MDataColumn columns = MDataColumn.CreateFrom(fullPath);
if (columns.Count > 0)
{
CacheManage.LocalInstance.Set(key, columns.Clone(), 1440);
return(columns);
}
}
}
#endregion
string fixName;
conn = CrossDB.GetConn(tableName, out fixName, conn ?? AppConfig.DB.DefaultConn);
tableName = fixName;
if (conn == null)
{
return(null);
}
MDataColumn mdcs = null;
using (DalBase dbHelper = DalCreate.CreateDal(conn))
{
DataBaseType dalType = dbHelper.DataBaseType;
if (dalType == DataBaseType.Txt || dalType == DataBaseType.Xml)
{
#region 文本数据库处理。
if (!tableName.Contains(" ")) // || tableName.IndexOfAny(Path.GetInvalidPathChars()) == -1
{
tableName = SqlFormat.NotKeyword(tableName); //处理database..tableName;
tableName = Path.GetFileNameWithoutExtension(tableName); //视图表，带“.“的，会出问题
string fileName = dbHelper.Con.DataSource + tableName + (dalType == DataBaseType.Txt ? ".txt" : ".xml");
mdcs = MDataColumn.CreateFrom(fileName);
mdcs.DataBaseType = dalType;
mdcs.Conn = conn;
}
#endregion
}
else
{
#region 其它数据库
mdcs = new MDataColumn();
mdcs.Conn = conn;
mdcs.TableName = tableName;
mdcs.DataBaseType = dalType;
tableName = SqlFormat.Keyword(tableName, dbHelper.DataBaseType);//加上关键字：引号
//如果table和helper不在同一个库
DalBase helper = dbHelper.ResetDalBase(tableName);
helper.IsRecordDebugInfo = false || AppDebug.IsContainSysSql;//内部系统，不记录SQL表结构语句。
try
{
bool isView = tableName.Contains(" ");//是否视图。
if (!isView)
{
isView = CrossDB.Exists(tableName, "V", conn);
}
if (!isView)
{
TableInfo info = CrossDB.GetTableInfoByName(mdcs.TableName, conn);
if (info != null)
{
mdcs.Description = info.Description;
}
}
MCellStruct mStruct = null;
SqlDbType sqlType = SqlDbType.NVarChar;
if (isView)
{
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
mdcs = GetViewColumns(sqlText, ref helper);
}
else
{
mdcs.AddRelateionTableName(SqlFormat.NotKeyword(tableName));
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Oracle:
case DataBaseType.MySql:
case DataBaseType.Sybase:
case DataBaseType.PostgreSQL:
#region Sql
string sql = string.Empty;
if (dalType == DataBaseType.MsSql)
{
#region Mssql
string dbName = null;
if (!helper.Version.StartsWith("08"))
{
//先获取同义词，检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(MSSQL_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
string[] items = realTableName.Split('.');
tableName = realTableName;
if (items.Length > 0) //跨库了
{
dbName = realTableName.Split('.')[0];
}
}
}
sql = GetMSSQLColumns(helper.Version.StartsWith("08"), dbName ?? helper.DataBaseName);
#endregion
}
else if (dalType == DataBaseType.MySql)
{
sql = GetMySqlColumns(helper.DataBaseName);
}
else if (dalType == DataBaseType.Oracle)
{
tableName = tableName.ToUpper(); //Oracle转大写。
//先获取同义词，不检测是否跨库
string realTableName = Convert.ToString(helper.ExeScalar(string.Format(Oracle_SynonymsName, SqlFormat.NotKeyword(tableName)), false));
if (!string.IsNullOrEmpty(realTableName))
{
tableName = realTableName;
}
sql = GetOracleColumns();
}
else if (dalType == DataBaseType.Sybase)
{
tableName = SqlFormat.NotKeyword(tableName);
sql = GetSybaseColumns();
}
else if (dalType == DataBaseType.PostgreSQL)
{
sql = GetPostgreColumns();
}
helper.AddParameters("TableName", SqlFormat.NotKeyword(tableName), DbType.String, 150, ParameterDirection.Input);
DbDataReader sdr = helper.ExeDataReader(sql, false);
if (sdr != null)
{
long maxLength;
bool isAutoIncrement = false;
short scale = 0;
string sqlTypeName = string.Empty;
while (sdr.Read())
{
short.TryParse(Convert.ToString(sdr["Scale"]), out scale);
if (!long.TryParse(Convert.ToString(sdr["MaxSize"]), out maxLength)) //mysql的长度可能大于int.MaxValue
{
maxLength = -1;
}
else if (maxLength > int.MaxValue)
{
maxLength = int.MaxValue;
}
sqlTypeName = Convert.ToString(sdr["SqlType"]);
sqlType = DataType.GetSqlType(sqlTypeName);
isAutoIncrement = Convert.ToBoolean(sdr["IsAutoIncrement"]);
mStruct = new MCellStruct(mdcs.DataBaseType);
mStruct.ColumnName = Convert.ToString(sdr["ColumnName"]).Trim();
mStruct.OldName = mStruct.ColumnName;
mStruct.SqlType = sqlType;
mStruct.IsAutoIncrement = isAutoIncrement;
mStruct.IsCanNull = Convert.ToBoolean(sdr["IsNullable"]);
mStruct.MaxSize = (int)maxLength;
mStruct.Scale = scale;
mStruct.Description = Convert.ToString(sdr["Description"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, sdr["DefaultValue"], 0, sqlType);
mStruct.IsPrimaryKey = Convert.ToString(sdr["IsPrimaryKey"]) == "1";
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.MySql:
case DataBaseType.Oracle:
mStruct.IsUniqueKey = Convert.ToString(sdr["IsUniqueKey"]) == "1";
mStruct.IsForeignKey = Convert.ToString(sdr["IsForeignKey"]) == "1";
mStruct.FKTableName = Convert.ToString(sdr["FKTableName"]);
break;
}
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
sdr.Close();
if (dalType == DataBaseType.Oracle && mdcs.Count > 0) //默认没有自增概念，只能根据情况判断。
{
MCellStruct firstColumn = mdcs[0];
if (firstColumn.IsPrimaryKey && firstColumn.ColumnName.ToLower().Contains("id") && firstColumn.Scale == 0 && DataType.GetGroup(firstColumn.SqlType) == 1 && mdcs.JointPrimary.Count == 1)
{
firstColumn.IsAutoIncrement = true;
}
}
}
#endregion
break;
case DataBaseType.SQLite:
#region SQlite
if (helper.Con.State != ConnectionState.Open)
{
helper.Con.Open();
}
DataTable sqliteDt = helper.Con.GetSchema("Columns", new string[] { null, null, SqlFormat.NotKeyword(tableName) });
if (!helper.IsOpenTrans)
{
helper.Con.Close();
}
int size;
short sizeScale;
string dataTypeName = string.Empty;
foreach (DataRow row in sqliteDt.Rows)
{
object len = row["NUMERIC_PRECISION"];
if (len == null || len == DBNull.Value)
{
len = row["CHARACTER_MAXIMUM_LENGTH"];
}
short.TryParse(Convert.ToString(row["NUMERIC_SCALE"]), out sizeScale);
if (!int.TryParse(Convert.ToString(len), out size)) //mysql的长度可能大于int.MaxValue
{
size = -1;
}
dataTypeName = Convert.ToString(row["DATA_TYPE"]);
if (dataTypeName == "text" && size > 0)
{
sqlType = DataType.GetSqlType("varchar");
}
else
{
sqlType = DataType.GetSqlType(dataTypeName);
}
//COLUMN_NAME,DATA_TYPE,PRIMARY_KEY,IS_NULLABLE,CHARACTER_MAXIMUM_LENGTH AUTOINCREMENT
mStruct = new MCellStruct(row["COLUMN_NAME"].ToString(), sqlType, Convert.ToBoolean(row["AUTOINCREMENT"]), Convert.ToBoolean(row["IS_NULLABLE"]), size);
mStruct.Scale = sizeScale;
mStruct.Description = Convert.ToString(row["DESCRIPTION"]);
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, row["COLUMN_DEFAULT"], 0, sqlType); //"COLUMN_DEFAULT"
mStruct.IsPrimaryKey = Convert.ToBoolean(row["PRIMARY_KEY"]);
mStruct.SqlTypeName = dataTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
mdcs.Add(mStruct);
}
#endregion
break;
case DataBaseType.Access:
#region Access
DataTable keyDt, valueDt;
string sqlText = SqlFormat.BuildSqlWithWhereOneEqualsTow(tableName);// string.Format("select * from {0} where 1=2", tableName);
OleDbConnection con = new OleDbConnection(helper.Con.ConnectionString);
OleDbCommand com = new OleDbCommand(sqlText, con);
con.Open();
keyDt = com.ExecuteReader(CommandBehavior.KeyInfo).GetSchemaTable();
valueDt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, SqlFormat.NotKeyword(tableName) });
con.Close();
con.Dispose();
if (keyDt != null && valueDt != null)
{
string columnName = string.Empty, sqlTypeName = string.Empty;
bool isKey = false, isCanNull = true, isAutoIncrement = false;
int maxSize = -1;
short maxSizeScale = 0;
SqlDbType sqlDbType;
foreach (DataRow row in keyDt.Rows)
{
columnName = row["ColumnName"].ToString();
isKey = Convert.ToBoolean(row["IsKey"]); //IsKey
isCanNull = Convert.ToBoolean(row["AllowDBNull"]); //AllowDBNull
isAutoIncrement = Convert.ToBoolean(row["IsAutoIncrement"]);
sqlTypeName = Convert.ToString(row["DataType"]);
sqlDbType = DataType.GetSqlType(sqlTypeName);
short.TryParse(Convert.ToString(row["NumericScale"]), out maxSizeScale);
if (Convert.ToInt32(row["NumericPrecision"]) > 0) //NumericPrecision
{
maxSize = Convert.ToInt32(row["NumericPrecision"]);
}
else
{
long len = Convert.ToInt64(row["ColumnSize"]);
if (len > int.MaxValue)
{
maxSize = int.MaxValue;
}
else
{
maxSize = (int)len;
}
}
mStruct = new MCellStruct(columnName, sqlDbType, isAutoIncrement, isCanNull, maxSize);
mStruct.Scale = maxSizeScale;
mStruct.IsPrimaryKey = isKey;
mStruct.SqlTypeName = sqlTypeName;
mStruct.TableName = SqlFormat.NotKeyword(tableName);
foreach (DataRow item in valueDt.Rows)
{
if (columnName == item[3].ToString()) //COLUMN_NAME
{
if (item[8].ToString() != "")
{
mStruct.DefaultValue = SqlFormat.FormatDefaultValue(dalType, item[8], 0, sqlDbType); //"COLUMN_DEFAULT"
}
break;
}
}
mdcs.Add(mStruct);
}
}
#endregion
break;
}
}
}
catch (Exception err)
{
Log.Write(err, LogType.DataBase);
//helper.DebugInfo.Append(err.Message);
}
#endregion
}
}
if (mdcs.Count > 0)
{
//移除被标志的列：
string[] fields = AppConfig.DB.HiddenFields.Split(',');
foreach (string item in fields)
{
string field = item.Trim();
if (!string.IsNullOrEmpty(field) & mdcs.Contains(field))
{
mdcs.Remove(field);
}
}
}
#region 缓存设置
if (!_ColumnCache.ContainsKey(key) && mdcs.Count > 0)
{
_ColumnCache.Add(key, mdcs.Clone());
if (!string.IsNullOrEmpty(AppConfig.DB.SchemaMapPath))
{
string folderPath = AppConfig.RunPath + AppConfig.DB.SchemaMapPath;
if (!System.IO.Directory.Exists(folderPath))
{
System.IO.Directory.CreateDirectory(folderPath);
}
mdcs.WriteSchema(folderPath + key + ".ts");
}
}
#endregion
return(mdcs);
}