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);
}
//"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);
}
// 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);
}