public object Query(string sql, params object[] parms)
{
object result = null;
SqlConnection con = ConnectionPool.GetConnection();
try
{
parms = GetParamList(parms);
DateTime begtime = DateTime.Now;
SqlCommand com = new SqlCommand(sql, con);
for (int i = 0; i < parms.Length - 1; i += 2)
{
if (parms[i] != null)
{
com.Parameters.Add(new SqlParameter(parms[i].ToString(), PrepareParam(parms[i + 1])));
}
}
using (SqlDataReader rdr = com.ExecuteReader())
{
while (rdr.Read())
{
result = rdr.GetValue(0);
break;
}
}
MainTrace.Add(TraceType.Sql, $"QUERY => {com.CommandText}");
}
finally
{
ConnectionPool.FreeConnection(con);
}
return(result);
}
public override void InitCfg(string filename)
{
MainTrace.Add(TraceType.Cfg, $"file => {Path.GetFileName(filename)} ");
XmlDocument xdoc = XFunc.Load(filename);
foreach (XmlNode cfgNode in xdoc.SelectNodes("descendant::cfg"))
{
foreach (XmlNode typeNode in cfgNode.SelectNodes("*"))
{
Type t = GetClassTypeByClassName(typeNode.Name);
if (t != null)
{
MainTrace.Add(TraceType.Cfg, $"Class => {t.Name}");
foreach (XmlNode itemNode in typeNode.SelectNodes("*"))
{
LockUpdates++;
try
{
MEDMObj obj = MainDic.GetObj(t, itemNode.Name);
obj.SetValues(itemNode);
MainTrace.Add(TraceType.Cfg, $"Item => {itemNode.Name} ({obj})");
}
finally
{
LockUpdates--;
}
}
}
else
{
MainTrace.Add(TraceType.Error, $"Для узла конфигурации {typeNode.Name} класс не найден");
}
}
}
}
public virtual void Init()
{
try
{
MainTrace.Add(TraceType.Init, $"Init Model => {this.GetType().Name}");
InitTables();
InitCfgs();
InitUsers();
}
catch (Exception e)
{
MainTrace.Add(TraceType.Error, $"{e.Message}");
throw new Exception("Init errors");
}
}
public void Exec(string sql, params object[] parms)
{
SqlConnection con = ConnectionPool.GetConnection();
try
{
parms = GetParamList(parms);
DateTime begtime = DateTime.Now;
SqlCommand com = new SqlCommand(sql, con);
for (int i = 0; i < parms.Length - 1; i += 2)
{
if (parms[i] != null)
{
com.Parameters.Add(new SqlParameter(parms[i].ToString(), PrepareParam(parms[i + 1])));
}
}
com.ExecuteNonQuery();
MainTrace.Add(TraceType.Sql, $"EXEC => {com.CommandText}");
}
finally
{
ConnectionPool.FreeConnection(con);
}
}
public virtual void InitTable(Type t)
{
MainTrace.Add(TraceType.Init, $"{t.Name} ");
// Создать таблицу если ее нет
if (!Query <bool>($"select 1 from sysobjects where type='U' and name='{t.Name}'"))
{
if (t.GetTypeInfo().IsSubclassOf(typeof(MEDMCfgObj)))
{
return;
}
else if (t.GetTypeInfo().IsSubclassOf(typeof(MEDMIdObj)))
{
MainTrace.Add(TraceType.Init, $"create table [{t.Name}]");
Exec($"CREATE TABLE [dbo].[{t.Name}]([Id] int identity not null)");
Exec($"ALTER TABLE [dbo].[{t.Name}] ADD CONSTRAINT PK_{t.Name} PRIMARY KEY CLUSTERED (Id) WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]");
}
else if (t.GetTypeInfo().IsSubclassOf(typeof(MEDMNameObj)))
{
MainTrace.Add(TraceType.Init, $"create table [{t.Name}]");
Exec($"CREATE TABLE [dbo].[{t.Name}]([Id] nvarchar(255) not null default(''))");
Exec($"ALTER TABLE [dbo].[{t.Name}] ADD CONSTRAINT PK_{t.Name} PRIMARY KEY CLUSTERED (Id) WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]");
}
else if (t.GetTypeInfo().IsSubclassOf(typeof(MEDMGuidObj)))
{
MainTrace.Add(TraceType.Init, $"create table [{t.Name}]");
Exec($"CREATE TABLE [dbo].[{t.Name}]([Id] uniqueidentifier not null default (newid()) )");
Exec($"ALTER TABLE [dbo].[{t.Name}] ADD CONSTRAINT PK_{t.Name} PRIMARY KEY CLUSTERED (Id) WITH(STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON[PRIMARY]");
}
else
{
return;
}
}
// Создать поля таблицы
Dictionary <string, PropertyInfo> classColumns = GetClassColumns(t);
Dictionary <string, MColumnScheme> schemeColumns = GetSchemeColumns(t);
// Проходим по описателям
foreach (string n in classColumns.Keys)
{
PropertyInfo pi = classColumns[n];
DbColumnAttribute ca = pi.GetCustomAttribute <DbColumnAttribute>();
// Есть в схеме
if (schemeColumns.ContainsKey(n))
{
MColumnScheme cs = schemeColumns[n];
if (ca.Type != cs.Type)
{
MainTrace.Add(TraceType.Init, $"alter column [{pi.Name}] {ca.Type}");
Exec($"ALTER TABLE [dbo].[{t.Name}] ALTER COLUMN [{pi.Name}] {ca.Type} not null");
}
if (ca.Def.Replace("(", "").Replace(")", "") != cs.Def.Replace("(", "").Replace(")", ""))
{
MainTrace.Add(TraceType.Init, $"alter default [{pi.Name}] {ca.Def}");
try
{
Exec($"ALTER TABLE [dbo].[{t.Name}] DROP CONSTRAINT DF_{t.Name}_{pi.Name}");
}
catch { }
Exec($"ALTER TABLE [dbo].[{t.Name}] ADD CONSTRAINT DF_{t.Name}_{pi.Name} DEFAULT {ca.Def} FOR [{pi.Name}]");
}
}
// Нет в схеме
else
{
MainTrace.Add(TraceType.Init, $"create column [{pi.Name}] {ca.Type} default {ca.Def}");
Exec($"ALTER TABLE [dbo].[{t.Name}] ADD [{pi.Name}] {ca.Type}");
Exec($"ALTER TABLE [dbo].[{t.Name}] ADD CONSTRAINT DF_{t.Name}_{pi.Name} DEFAULT {ca.Def} FOR [{pi.Name}]");
}
}
// Проходим по колонкам
foreach (string n in schemeColumns.Keys)
{
// Нет в классах
if (!classColumns.ContainsKey(n))
{
MColumnScheme cs = schemeColumns[n];
MainTrace.Add(TraceType.Init, $"drop column [{cs.column_name}]");
try
{
Exec($"ALTER TABLE [dbo].[{t.Name}] DROP CONSTRAINT DF_{t.Name}_{cs.column_name}");
}
catch { }
Exec($"ALTER TABLE [dbo].[{t.Name}] DROP COLUMN [{cs.column_name}]");
}
}
}
public IList Select(IList list, Type t, string sql, params object[] parms)
{
SqlConnection con = ConnectionPool.GetConnection();
try
{
parms = GetParamList(parms);
int allCount = -1;
IPaginationList plist = null;
if (list is IPaginationList)
{
plist = (IPaginationList)(list);
}
DateTime begtime = DateTime.Now;
SqlCommand com = new SqlCommand(sql, con);
for (int i = 0; i < parms.Length - 1; i += 2)
{
if (parms[i] != null)
{
com.Parameters.Add(new SqlParameter(parms[i].ToString(), PrepareParam(parms[i + 1])));
}
}
// Для PaginationList select выполняем в 2 этапа сначала считаем кол-во а потом сам select c добавленным top
if (plist != null)
{
if (plist.Top != "")
{
string sql1 = com.CommandText;
int i = sql1.IndexOf('*');
int j = sql1.ToLower().IndexOf("order by");
if (i > 0 && (j < 0 || i < j))
{
com.CommandText = sql1.Remove(j).Remove(i, 1).Insert(i, "count(*)");
allCount = Convert.ToInt32(com.ExecuteScalar());
com.CommandText = sql1.Insert(i, plist.Top + " ");
}
}
}
bool f = MainDic.ContainsKey(t);
using (SqlDataReader rdr = com.ExecuteReader())
{
while (rdr.Read())
{
if (plist != null)
{
if ((plist.AllCount >= plist.StartPos || plist.StartPos == -1) && plist.AllCount < plist.StartPos + plist.PageSize || plist.StartPos == -1 || plist.PageSize == -1)
{
MObj obj = null;
if (f)
{
obj = MainDic.CreateObj(t);
}
else
{
obj = Activator.CreateInstance(t) as MObj;
if (obj == null)
{
throw new Exception($@"Тип ""{t}"" не наследует от MObj");
}
obj.Model = this;
}
SetObjValues(obj, rdr);
if (obj is MEDMObj)
{
MainDic.AddObj(obj as MEDMObj);
}
if (list != null)
{
list.Add(obj);
}
}
plist.AllCount++;
}
else
{
MObj obj = null;
if (f)
{
obj = MainDic.CreateObj(t);
}
else
{
obj = Activator.CreateInstance(t) as MObj;
if (obj == null)
{
throw new Exception($@"Тип ""{t}"" не наследует от MObj");
}
obj.Model = this;
}
SetObjValues(obj, rdr);
if (obj is MEDMObj)
{
MainDic.AddObj(obj as MEDMObj);
}
if (list != null)
{
list.Add(obj);
}
}
}
}
if (plist != null && allCount >= 0)
{
plist.AllCount = allCount;
}
MainTrace.Add(TraceType.Sql, $"SELECT => {com.CommandText}");
}
finally
{
ConnectionPool.FreeConnection(con);
}
return(list);
}
