public ExecViewModel Exec(string code, Language language)
{
if (language != Language.TSQL)
{
throw new NotSupportedException();
}
string result = String.Empty;
ResultType type = ResultType.Data;
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDynamicRepertory db = new SQLServerDynamicRepertory();
result = SerializerHelper.SerializerObjectByJsonConvert(db.SQLQuery(code));
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the exec sql, error:{e.ToString()}");
Exception exception = e.InnerException == null ? e : e.InnerException;
result = exception.Message;
type = ResultType.Error;
}
return(new ExecViewModel()
{
Result = result, ResultType = type
});
}
public List <MemoryViewModel> GetMemoryInfos()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
List <RingBuffer> buffers = db.Filter <RingBuffer, DateTime>(d => d.Type == "RING_BUFFER_SCHEDULER_MONITOR" && d.Record.Contains("<SystemHealth>"), d => d.EventTime);
SystemInfo system = db.Find <SystemInfo>(d => d.PhysicalMemory > 0);
List <MemoryViewModel> memoryInfos = new List <MemoryViewModel>();
foreach (RingBuffer buffer in buffers)
{
MemoryViewModel info = RingBufferHelper.ParseXMLToMemoryInfo(buffer.Record);
info.TotalMemory = system.PhysicalMemory;
info.UseMemory = Convert.ToInt32(system.PhysicalMemory * ((double)info.MemoryUtilization / 100));
info.EventTime = buffer.EventTime.ToLocalTime().ToString();
memoryInfos.Add(info);
}
return(memoryInfos);
}
}
catch (Exception e)
{
throw;
}
}
/// <summary>
/// TODO
/// </summary>
/// <returns></returns>
public List <QueryProportionViewModel> GetQueryProportionForTable()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
List <QueryProportionViewModel> infos = new List <QueryProportionViewModel>();
SQLServerDBRepertory db = new SQLServerDBRepertory();
int total = 0;
List <QueryHistory> queries = db.CrossJoin <QueryHistory, DmExecSQLText, byte[], DateTime>(d => d.SQLHandle, d => d.LastReturnRows > -1, d => d.LastExecutionTime, out total, 1, int.MaxValue, true);
object syncRoot = new object();
Parallel.ForEach(queries, query =>
{
List <SQLNode> nodes = SQLParse.Parse(query.Text);
if (nodes != null && nodes.Any())
{
nodes.FirstOrDefault(d => d.Type == SQLNodeType.FORM);
//TODO
}
});
//return info;
//TODO
return(null);
}
}
catch (Exception e)
{
throw;
}
}
public List <ExceptionViewModel> GetExceptionInfos()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
return(new List <ExceptionViewModel>());
}
}
catch (Exception e)
{
throw;
}
}
public bool Connect()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
return(db.Connect());
}
}
catch (Exception e)
{
mLog.Warn($"An error has occurred in the connect mongodb,error:{e.ToString()}");
throw;
}
}
public List <TableViewModel> GetTables(string tableName)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
return(db.Use(tableName).Filter <Table, string>(d => d.Type == "U", d => d.Name).ToViewModel(tableName));
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the get tables,error:{e.ToString()}");
throw;
}
}
public List <ConnectedViewModel> GetConnectedInfos()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
List <RingBuffer> buffers = db.Filter <RingBuffer>(d => d.Type == "RING_BUFFER_CONNECTIVITY");
return(new List <ConnectedViewModel>());
}
}
catch (Exception e)
{
throw;
}
}
public List <FieldTypeViewModel> GetFieldTypes()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
return(db.All <Domain.FieldType>().ToViewModel());
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the get field types,error:{e.ToString()}");
throw;
}
}
public bool DeleteDatabase(string databaseName)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
return(db.Use(databaseName).Delete <Database>(d => d.Name == databaseName));
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the create table,error:{e.ToString()}");
throw;
}
}
public bool CreateTable(TableViewModel table)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
return(false);
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the create table,error:{e.ToString()}");
throw;
}
}
public List <DiskViewModel> GetDiskInfos()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
int total = 0;
List <Disk> disks = db.CrossJoin <Disk, DmOSVolumeStats, dynamic, string>(d => new { d.DatabaseId, d.FileId }, d => d.DatabaseId > 0, d => d.DatabaseName, out total, 1, int.MaxValue, true);
return(disks.ToViewModel());
}
}
catch (Exception e)
{
throw;
}
}
public DatabaseTreeViewModel GetDatabases()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
List <Product> products = db.Exec <Product>();
return(db.All <Database>().ToViewModel(this.mDBConnect, products));
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the get databases,error:{e.ToString()}");
throw;
}
}
public bool Delete(RemoveFilter filter)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDynamicRepertory db = new SQLServerDynamicRepertory();
List <IndexViewModel> indexs = ServiceFactory.GetInstance().DatabaseService.GetTableIndexs(filter.TableName);
string key = indexs.FirstOrDefault(d => d.Type == IndexType.Primary).ColumnName;
return(db.DbSet(filter.TableName).Delete($"{key} in ('{String.Join("','", filter.Selected)}')"));
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the delete,error:{e.ToString()}");
throw;
}
}
public List <IndexViewModel> GetTableIndexs(string tableName)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
var dbName = tableName.Split('.').First();
var tbName = tableName.Split('.').Last().Remove(0, 1);
tbName = tbName.Remove(tbName.Length - 1, 1);
return(db.SQLQuery <Index>($"select _index.id as id,_index.indid as indid,_index.name as indname,_col.name as colname from {dbName}..SysColumns as _col join (select t_key.id, t_key.indid,t_key.colid,t_index.name from {dbName}..sysindexkeys as t_key inner join {dbName}..sysindexes as t_index on t_key.indid = t_index.indid where t_key.id = t_index.id and t_key.id=(select id from {dbName}..sysobjects where Name = '{tbName}')) as _index on _index.colid = _col.colid where _col.id = _index.id").ToViewModel());
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the get table indexs,error:{e.ToString()}");
throw;
}
}
public List <FieldViewModel> GetTableFields(string tableName)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
var dbName = tableName.Split('.').First();
var tbName = tableName.Split('.').Last().Remove(0, 1);
tbName = tbName.Remove(tbName.Length - 1, 1);
SQLServerDBRepertory db = new SQLServerDBRepertory();
Table table = db.Use(dbName).Find <Table>(d => d.Type == "U" && d.Name == tbName);
long id = table.Id;
return(db.Use(dbName).Filter <Field, string>(d => d.Id == id, d => d.Name).ToViewModel());
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the get table fields,error:{e.ToString()}");
throw;
}
}
//[DBScopeInterecpor]
public TableDataViewMdoel Select(DataFilter filter)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
//SQLServerDBRepertory db = new SQLServerDBRepertory();
//var count = db.Count(filter.TableName);
//var datas = db.Filter(filter).To();
//return new TableDataViewMdoel()
//{
// Datas = datas,
// PageIndex = filter.PageIndex,
// PageSize = filter.PageSize,
// PageCount = Convert.ToInt64(Math.Ceiling(((double)count / filter.PageSize))),
// Total = count
//};
SQLServerDynamicRepertory db = new SQLServerDynamicRepertory();
var dbName = filter.TableName.Split('.').First();
var tbName = filter.TableName.Split('.').Last().Remove(0, 1);
tbName = tbName.Remove(tbName.Length - 1, 1);
int total = 1;
string search = String.IsNullOrEmpty(filter.Search.Key) ? "1=1" : $"{filter.Search.Key} like '%{filter.Search.Value}%'";
string select = filter.Selected == null || !filter.Selected.Any() ? "*" : String.Join(",", filter.Selected);
string orderBy = String.IsNullOrEmpty(filter.SortColumn) ? "1" : filter.SortColumn;
List <FieldViewModel> fileds = ServiceFactory.GetInstance().DatabaseService.GetTableFields(filter.TableName);
var vm = db.Use(dbName).DbSet(filter.TableName).Filter(select, search, orderBy, out total, filter.PageIndex, filter.PageSize, filter.IsAsc).ToViewModel(fileds);
vm.Total = total;
vm.PageIndex = filter.PageIndex;
vm.PageSize = filter.PageSize;
vm.PageCount = Convert.ToInt32(Math.Ceiling((double)total / filter.PageSize));
return(vm);
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the select data,error:{e.ToString()}");
throw;
}
}
public QueryProportionViewModel GetAllQueryProportionInfo()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
QueryProportionViewModel info = new QueryProportionViewModel();
Dictionary <string, dynamic> queryConfig = new Dictionary <string, dynamic>()
{
{ "Select", new ExpandoObject() }, { "Delete", new ExpandoObject() }, { "Update", new ExpandoObject() }, { "Insert", new ExpandoObject() }
};
SQLServerDBRepertory db = new SQLServerDBRepertory();
int total = 0;
List <QueryHistory> queries = db.CrossJoin <QueryHistory, DmExecSQLText, byte[], DateTime>(d => d.SQLHandle, d => d.LastReturnRows > -1, d => d.LastExecutionTime, out total, 1, int.MaxValue, true);
foreach (var queryIndex in queryConfig)
{
queryIndex.Value.Count = 0;
}
Parallel.ForEach(queries, query =>
{
string sql = query.Text;
foreach (var queryIndex in queryConfig)
{
queryIndex.Value.Index = sql.IndexOf(queryIndex.Key, StringComparison.OrdinalIgnoreCase);
}
queryConfig.OrderByDescending(d => d.Value.Index).First().Value.Count += 1;
});
foreach (var queryIndex in queryConfig)
{
typeof(QueryProportionViewModel).GetProperty($"{queryIndex.Key}Count").SetValue(info, Convert.ToInt32(queryIndex.Value.Count));
}
return(info);
}
}
catch (Exception e)
{
throw;
}
}
public List <CPUViewModel> GetCPUInfos()
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
List <RingBuffer> buffers = db.Filter <RingBuffer, DateTime>(d => d.Type == "RING_BUFFER_SCHEDULER_MONITOR" && d.Record.Contains("<SystemHealth>"), d => d.EventTime);
List <CPUViewModel> cpuInfos = new List <CPUViewModel>();
foreach (RingBuffer buffer in buffers)
{
CPUViewModel info = RingBufferHelper.ParseXMLToCPUnfo(buffer.Record);
info.EventTime = buffer.EventTime.ToLocalTime().ToString();
cpuInfos.Add(info);
}
return(cpuInfos);
}
}
catch (Exception e)
{
throw;
}
}
public QueryHistoryViewModel GetQueryHistories(DataFilter filter)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDBRepertory db = new SQLServerDBRepertory();
int total = 0;
List <QueryHistoryInfoViewModel> queryHistories = db.CrossJoin <QueryHistory, DmExecSQLText, byte[], DateTime>(d => d.SQLHandle, d => d.LastReturnRows > -1, d => d.LastExecutionTime, out total, filter.PageIndex, filter.PageSize, filter.IsAsc).ToViewModel();
return(new QueryHistoryViewModel()
{
QueryHistories = queryHistories,
PageIndex = filter.PageIndex,
PageSize = filter.PageSize,
PageCount = Convert.ToInt32(Math.Ceiling((double)total / filter.PageSize))
});
}
}
catch (Exception e)
{
throw;
}
}
public bool Update(UpdateFilter filter)
{
try
{
using (var scope = new SQLServerDBContextScope(this.mDBConnect))
{
SQLServerDynamicRepertory db = new SQLServerDynamicRepertory();
var dbName = filter.TableName.Split('.').First();
var tbName = filter.TableName.Split('.').Last().Remove(0, 1);
tbName = tbName.Remove(tbName.Length - 1, 1);
List <IndexViewModel> indexs = ServiceFactory.GetInstance().DatabaseService.GetTableIndexs(filter.TableName);
foreach (var data in filter.Datas)
{
db.DbSet(filter.TableName).Update(data, indexs.FirstOrDefault(d => d.Type == IndexType.Primary).ColumnName);
}
return(true);
}
}
catch (Exception e)
{
mLog.Error($"An error has occurred in the update data,error:{e.ToString()}");
throw;
}
}
