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