public DBMapper(string tablename, T2 defaultvalue, string nameparameter = "name")
{
sqlbase = new SQLHandlerBase();
NameParameter = nameparameter;
this.DefaultValue = defaultvalue;
InstanceQueries = (from q in xquerys select Clean(q, tablename, nameparameter)).ToList();
}
public DBTableMaker(string table)
{
var b = new SQLHandlerBase();
this.tableName = $"[{b.Schema}].[{b.TablePrefix}_{table}]";
ForTable();
}
const char delim = (char)31; // tab character = 9 // unit separator = 31
public T2 ID(T0 Name0, T1 Name)
{
var compoundName = $"{Name0}{delim}{Name}"; // different type of KeyPair dictionary
if (xIDs.TryGetValue(compoundName, out T2 idCache))
{
return(idCache);
}
else
{
var sqlbase = new SQLHandlerBase();
foreach (var query in InstanceQueries)
{
T2 id = sqlbase.DoQueryScaler <T0, T1, T2>(query, DefaultValue,
NameParameter0, Name0,
NameParameter, Name);
if (id != null && !id.Equals(DefaultValue))
{
xIDs.TryAdd(compoundName, id); // save value to id for next round of lookups, then return it
return(id);
}
}
}
return(DefaultValue);
}
public CimDirectoryToDB(CIMDirectoryCollection newFiles, ACLSet newAclset)
{
Files = newFiles;
aclset = newAclset;
sqlHandler = new SQLHandler();
sQLHandlerBase = new SQLHandlerBase();
ConnectionString = sQLHandlerBase.ConnectionString;
}
//private string Clean(string cleanthis, string table, string nameparameter, string datatype)
//{
// return new StringBuilder(cleanthis)
// .Replace("%TABLE%", table)
// .Replace("%PRIME%", nameparameter)
// .Replace("%TYPE%", datatype)
// .ToString();
//}
public void ForTable()
{
if (!IfExists(tableName))
{
var sql = $@"CREATE TABLE {tableName} ([Id] INT IDENTITY (1, 1) NOT NULL, PRIMARY KEY CLUSTERED ([Id] ASC));";
var handlerBase = new SQLHandlerBase();
handlerBase.DoQueryNonScaler(sql);
}
}
public void ForIndex(string indexColumn)
{
if (!IfIndexExists(tableName, indexColumn))
{
var sql = $"CREATE UNIQUE NONCLUSTERED INDEX [ix{tableName}_{indexColumn}] " +
" ON [dbo].[CIM_{tableName}]([{indexColumn}] ASC);";
var handlerBase = new SQLHandlerBase();
handlerBase.DoQueryNonScaler(sql);
}
}
public void ForColumn(string columnName, string datatype, bool nullable, bool indexed)
{
if (!IfColumnExists(tableName, columnName))
{
var nullvalue = nullable ? "NULL" : "";
var sql = $"Alter Table {tableName} Add Column {columnName} {nullvalue}";
var handlerBase = new SQLHandlerBase();
handlerBase.DoQueryNonScaler(sql);
if (indexed)
{
ForIndex(columnName);
}
}
}
//"Select IIF( Object_id('%objectname%') is null, 0, 1)";
// IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexId')
private bool IfIndexExists(string objectname, string objectname2)
{
//const string IfObjectIndexExists =
//"Select IIF(IndexProperty(Object_Id('%object%'), '%index%', 'IndexId') is null, 0, 1)";
//var qry = IfObjectIndexExists
// .Replace("%object%", objectname)
// .Replace("%index%", objectname2);
bool result = false;
var sql = $"Select IIF(IndexProperty(Object_Id('{objectname}'), '{objectname2}', 'IndexId') is null, 0, 1)";
var handlerBase = new SQLHandlerBase();
result = handlerBase.DoQueryScaler <bool>(sql, false);
return(result);
}
//public bool ValidateTable(string tableName, string valueName, string valueType)
//{
// bool result = true;
// NewTable = Clean(SampleTable, tableName, valueName, valueType);
// NewIndex = Clean(SampleIndex, tableName, valueName, valueType);
// var table = Clean("[dbo].[CIM_%TABLE%]", tableName, valueName, valueType);
// var index = Clean("[ix%TABLE%_%PRIME%]", tableName, valueName, valueType);
// var tableOk = IfExists(table);
// if (!tableOk)
// {
// var handlerBase = new SQLHandlerBase();
// var newTable = handlerBase.DoQueryNonScaler(NewTable);
// //result = false;
// }
// var indexOk = IfIndexExists(table, index);
// if (!indexOk)
// {
// var handlerBase = new SQLHandlerBase();
// var newIndex = handlerBase.DoQueryNonScaler(NewIndex);
// }
// return result;
//}
private bool IfExists(string objectname)
{
//const string IfObjectExists = "select IIF(Object_id('%objectname%') is null, 0, 1)";
//var qry = IfObjectExists.Replace("%objectname%", objectname);
bool result = false;
var sql = $"select IIF(Object_id('{objectname}') is null, 0, 1)";
var handlerBase = new SQLHandlerBase();
var rc = handlerBase.DoQueryScaler <int>(sql, -2);
if (rc == 1)
{
result = true;
}
return(result);
}
public void ForIndex(string indexColumn)
{
var ixName = ("ix" + tableName + '_' + indexColumn).Replace('[', '_').Replace(']', '_');
//if (!IfIndexExists(tableName, indexColumn))
if (!IfIndexExists(tableName, ixName))
{
//var sql = $"CREATE UNIQUE NONCLUSTERED INDEX [ix{tableName}_{indexColumn}] " +
// $" ON [dbo].[CIM_{tableName}]([{indexColumn}] ASC);";
var sql = $"CREATE UNIQUE NONCLUSTERED INDEX [{ixName}] " +
$" ON {tableName} ({indexColumn} ASC);";
var handlerBase = new SQLHandlerBase();
handlerBase.DoQueryNonScaler(sql);
}
}
// COL_LENGTH ( 'table' , 'column' )
private bool IfColumnExists(string tablename, string columnname)
{
//const string IfObjectIndexExists =
//"Select Coalesce(COL_LENGTH('%object%', '%column%'), -1)";
//var qry = IfObjectIndexExists
// .Replace("%object%", tablename)
// .Replace("%column%", columnname);
bool result = false;
var sql = $"Select Coalesce(COL_LENGTH('{tablename}', '{columnname}'), -1)";
var handlerBase = new SQLHandlerBase();
var rc = handlerBase.DoQueryScaler <int>(sql, -2);
if (rc > -1)
{
result = true;
}
return(result);
}
public T2 ID(T1 Name)
{
if (xIDs.TryGetValue(Name, out T2 idCache))
{
return(idCache);
}
var sqlbase = new SQLHandlerBase();
foreach (var query in InstanceQueries)
{
T2 id = sqlbase.DoQueryScaler <T1, T2>(query, DefaultValue, NameParameter, Name);
if (id != null && !id.Equals(DefaultValue))
{
xIDs.TryAdd(Name, id); // save value to id for next round of lookups, then return it
return(id);
}
}
return(DefaultValue);
}
public int ID(string serverName)
{
if (xServerIDs.TryGetValue(serverName, out int idCache))
{
return idCache;
}
int factor = 1;
foreach (var query in xqueryServers)
{
var sqlbase = new SQLHandlerBase();
var serverID = sqlbase.DoQuery<int>(query, -1, ServerParameter, serverName);
if (serverID > 0)
{
var vServerId = factor * serverID;
xServerIDs.TryAdd(serverName, vServerId); // save serverid, then return it
return vServerId;
}
factor = -1;
}
return -1;
}
public SQLHandler()
{
sQLbase = new SQLHandlerBase();
_connectionString = sQLbase.ConnectionString ?? throw new ArgumentNullException(nameof(_connectionString));
}