/// <summary>
/// 获取建表模板
/// </summary>
/// <param name="keyid">表主键</param>
/// <returns>结果</returns>
public Result GetTemplateSqlCreate(string keyid)
{
var con = ConnectionHelper.GetSqlSugarClient();
var tableInfo = con.Queryable <TableInfo>().Where(p => p.TabelKeyID == keyid).First();
if (string.IsNullOrWhiteSpace(tableInfo?.TabelKeyID))
{
return(new Result()
{
Msg = "表信息获取失败"
});
}
var conInfo = new BConString().GetConString(tableInfo.ConStringKeyID);
if (string.IsNullOrWhiteSpace(conInfo?.ConnectionString))
{
return(new Result()
{
Msg = "获取数据库连接失败"
});
}
var conCon = ConnectionHelper.GetSqlSugarClientByConString(conInfo.ConnectionString);
string sql = $"show create table {tableInfo.MaxTableName};";
var res = conCon.Queryable <dynamic>(sql).First();
return(new Result <Table>()
{
Success = true, Msg = res.CreateTable
});
}
/// <summary>
/// 获取所有数据库
/// </summary>
/// <param name="connectionString">数据库连接</param>
/// <returns>数据库列表</returns>
public List <string> GetAllDataBase(string connectionString, ref SqlSugarClient client)
{
client = ConnectionHelper.GetSqlSugarClientByConString(connectionString);
string sql = "show databases;";
return(client.Ado.SqlQuery <string>(sql));
}
/// <summary>
/// 执行表的创建
/// </summary>
/// <param name="tableKeyID">表主键</param>
/// <param name="sql">待执行的sql语句</param>
/// <returns>执行结果</returns>
public Result ExecuteCreateTable(string tableKeyID, string sql)
{
try
{
if (string.IsNullOrWhiteSpace(sql))
{
return(new BTask().AddDBTask(new DBTask()
{
BusinessKeyID = tableKeyID,
BusinessType = BusinessType.表建表,
BusinessContent = sql
}));
}
////当sql为空，获取表模板的sql语句执行建表，不为空执行入参中的sql建表
var tableInfo = this.GetTableByKeyId(tableKeyID)?.Data;
if (string.IsNullOrWhiteSpace(tableInfo?.ConStringKeyID))
{
return(new Result()
{
Msg = "表不存在"
});
}
var conInfo = new BConString().GetConString(tableInfo.ConStringKeyID);
if (string.IsNullOrWhiteSpace(conInfo?.KeyID))
{
return(new Result()
{
Msg = "数据连接不存在"
});
}
var con = ConnectionHelper.GetSqlSugarClientByConString(conInfo.ConnectionString);
var rt = con.Ado.ExecuteCommand(sql);
return(new Result()
{
Success = rt > 0
});
}
catch (Exception e)
{
NLog.LogManager.GetCurrentClassLogger().Error($"{e}");
return(new Result()
{
Msg = e.Message
});
}
}
/// <summary>
/// 根据数据库链接执行连接下所有库进行创建表操作
/// </summary>
/// <param name="keyID">数据库链接主键</param>
/// <param name="sql">sql语句</param>
/// <returns>执行结果</returns>
public Result CreateTableByConKeyID(string keyID, string sql)
{
try
{
var data = this.GetConString(keyID);
if (string.IsNullOrWhiteSpace(data?.ConnectionString))
{
return(new Result()
{
Msg = "查无此数据库配置"
});
}
////整库时间较久，下发任务,做异步执行
if (string.IsNullOrWhiteSpace(sql) == false)
{
return(new BTask().AddDBTask(new DBTask()
{
BusinessKeyID = keyID,
BusinessType = BusinessType.整库建表,
}));
}
var con = ConnectionHelper.GetSqlSugarClientByConString(data.ConnectionString);
int runRt = con.Ado.ExecuteCommand(sql);
return(new Result()
{
Success = runRt > 0
});
}
catch (Exception e)
{
NLog.LogManager.GetCurrentClassLogger().Error(e);
return(new Result()
{
Msg = $"根据数据库执行sql出错,{e.Message}"
});
}
}