public SQLDMLStatement GetInsertScriptForTypedEntity(IDatabaseTenant Tenant, object Entity, List <string> ParameterNames, List <object> ParameterValues, DMLStatemtType dMLStatemtType, List <ISQLDMLStatementVariable> uniqueKeys)
{
string DMLStatementPrefix = "";
string paramNames = "";
int expectedParamCount;
object[] ParamNamesArray = ParameterNames.ToArray();
object[] ParameterValuesArray = ParameterValues.ToArray();
expectedParamCount = ParameterNames.Count;
string fieldList = "";
int i = 0;
foreach (var param in ParamNamesArray)
{
fieldList += (i > 0 ? ", " : "Tenant, ") + (string)param;
paramNames += (i > 0 ? "," : "") + "@" + (string)param;
i++;
}
if (dMLStatemtType == DMLStatemtType.Insert)
{
DMLStatementPrefix = $"insert into {Entity.GetType().Name} ({fieldList})";
}
else if (dMLStatemtType == DMLStatemtType.Delete)
{
DMLStatementPrefix = $"delete from {Entity.GetType().Name} ";
}
else if (dMLStatemtType == DMLStatemtType.Update)
{
DMLStatementPrefix = $"update {Entity.GetType().Name} set ";
}
else if (dMLStatemtType == DMLStatemtType.Select)
{
DMLStatementPrefix = $"select * from {Entity.GetType().Name} ";
}
else if (dMLStatemtType == DMLStatemtType.SelectAll)
{
DMLStatementPrefix = $"select * from {Entity.GetType().Name} ";
}
string[] paramaterNames;
if (paramNames.Length > 0)
{
paramaterNames = paramNames.Split(',');
}
else
{
paramaterNames = new string[] { }
};
return(_GetDMLScript(Tenant, DMLStatementPrefix, paramaterNames, ParameterValuesArray, expectedParamCount, dMLStatemtType, uniqueKeys));
}
public DatabaseAccess(IExecuteDML dmlExecutionProvider, IDatabaseTenant BarTenant)
{
_dbaccess = dmlExecutionProvider;
Tenant = BarTenant;
if (Tenant == null)
{
throw new Exception("CODE LOGIC ERROR: Tenant may not be null");
}
}
internal static SQLDMLStatement GetDDLStatementGenericEntityStorageCreation(IDatabaseTenant Tenant, object Entity)
{
var statement = new SQLDMLStatement
{
PreparedStatement = BuildTableCreateForEntitySql(Entity),
StatemtType = DMLStatemtType.Create
};
return(statement);
}
private SQLDMLStatement _GetDMLScript(IDatabaseTenant Tenant, string DMLStatementPrefix, object[] ParameterNames, object[] ParameterValues, int expectedParamCount, DMLStatemtType DMLStatemtType, List <ISQLDMLStatementVariable> uniqueKeys)
{
if (expectedParamCount != ParameterValues.GetUpperBound(0) + 1)
{
throw new Exception("Number of parameters supplied for statement differs from expected");
}
if (DMLStatemtType == DMLStatemtType.SelectAll && false)
{
ParameterNames = AppendValueToBeginningOfArray(new object[0], "@Tenant");
ParameterValues = AppendValueToBeginningOfArray(new object[0], Tenant.Code);
}
else
{
ParameterNames = AppendValueToBeginningOfArray(ParameterNames, "@Tenant");
ParameterValues = AppendValueToBeginningOfArray(ParameterValues, Tenant.Code);
}
if (ParameterNames.GetUpperBound(0) != ParameterValues.GetUpperBound(0))
{
throw new Exception("CODE LOGIC ERROR: Param names and value count mismatch");
}
string paramlist = "";
string paramlist2 = "";
string dmlSuffix = "";
int j = 0;
foreach (var param in ParameterValues)
{
if (ParameterNames[j].ToString().Contains("@@"))
{
if (ParameterValues[j].ToString().Contains("'"))
{
throw new Exception("injection attempt");
}
else
{
DMLStatementPrefix = DMLStatementPrefix.Replace(ParameterNames[j].ToString(), ParameterValues[j].ToString());
}
}
else if (DMLStatementPrefix.Contains(ParameterNames[j].ToString()))
{
}
else if (DMLStatemtType == DMLStatemtType.Insert)
{
paramlist += ParameterNames[j] + ",";
}
else if (DMLStatemtType == DMLStatemtType.Delete)
{
bool considerUniqueKeys = true;
considerUniqueKeys = uniqueKeys != null;
if (considerUniqueKeys)
{
if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0)
{
paramlist += (paramlist.Length > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else if (DMLStatemtType == DMLStatemtType.Select || DMLStatemtType == DMLStatemtType.SelectAll)
{
bool considerUniqueKeys = true;
considerUniqueKeys = uniqueKeys != null;
if (considerUniqueKeys)
{
if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0)
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
else if (DMLStatemtType == DMLStatemtType.Update)
{
bool isUniqueKey = false;
bool considerUniqueKeys = (uniqueKeys != null);
if (considerUniqueKeys)
{
if (uniqueKeys.Where(u => u.Name == ParameterNames[j].ToString()).Count() > 0)
{
isUniqueKey = true;
}
}
if (isUniqueKey && considerUniqueKeys)
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
else if (!isUniqueKey && considerUniqueKeys)
{
paramlist2 += (paramlist2.Length > 0 ? " , " : " ");
paramlist2 += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j].ToString();
}
else if (ParameterNames[j].ToString().Substring(0, 1) == "$")
{
paramlist2 += (paramlist2.Length > 0 ? " , " : " ");
paramlist2 += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j].ToString().Replace("$", "@");
}
else
{
paramlist += (j > 0 ? " AND " : " ");
paramlist += ParameterNames[j].ToString().Substring(1) + " = " + ParameterNames[j];
}
}
j++;
}
if ((paramlist.Length > 0) && (paramlist.EndsWith(",")))
{
paramlist = paramlist.Substring(0, paramlist.Length - 1);
}
if (paramlist.Length > 0)
{
dmlSuffix += (DMLStatemtType == DMLStatemtType.Insert ? $" values ({paramlist})" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.Delete ? $" where {paramlist}" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.Select ? $" where {paramlist}" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.SelectAll ? $" where {paramlist}" : "");
dmlSuffix += (DMLStatemtType == DMLStatemtType.Update ? paramlist2 + $" where {paramlist}" : "");
}
List <ISQLDMLStatementVariable> sqlVars = new List <ISQLDMLStatementVariable>();
int i = 0;
foreach (var val in ParameterValues)
{
string paramNam = "";
paramNam = ParameterNames[i].ToString().Replace("$", "@");
if (DMLStatementPrefix.Contains(paramNam) || dmlSuffix.Contains(paramNam))
{
sqlVars.Add(new SQLDMLStatementVariable {
Name = paramNam, Value = val
});
}
i++;
}
SQLDMLStatement dmlStatement = new SQLDMLStatement
{
PreparedStatement = DMLStatementPrefix + dmlSuffix,
Variables = sqlVars,
StatemtType = DMLStatemtType
};
//if (expectedParamCount != dmlStatement.ParameterCount - 1)
// throw new Exception("CODE LOGIC ERROR : Param Count Mismatch");
return(dmlStatement);
}
public SQLDMLStatement GetScript(IDatabaseTenant Tenant, BarDMLScript ScriptId, object[] ParameterValues)
{
string DMLStatementPrefix;
string paramNames;
int expectedParamCount;
DMLStatemtType dMLStatemtType;
if (ScriptId == BarDMLScript.eCreateUser)
{
DMLStatementPrefix = "insert into BarUser (Tenant, Id, Username, Password, Email, Telephone)";
paramNames = "@Id,@Username,@Password,@Email,@Telephone";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Insert;
}
else if (ScriptId == BarDMLScript.eCreateInstitution)
{
DMLStatementPrefix = "insert into BarInstitution (Tenant, Id, Name, Password, Email, Telephone)";
paramNames = "@Id,@Name,@Password,@Email,@Telephone";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Insert;
}
else if (ScriptId == BarDMLScript.eCreateEntity)
{
DMLStatementPrefix = "insert into BarBill (Tenant, Id, Amount, Tip, DateTime, Comment)";
paramNames = "@Id,@Amount,@Tip,@dateTime,@Comment";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Insert;
}
else if (ScriptId == BarDMLScript.eCreateToken)
{
DMLStatementPrefix = "insert into BarToken (Tenant, Id, IssueTime, OriginatorWalletAddress, CurrentWallet, TokenType)";
paramNames = "@Id,@IssueTime,@OriginatorWalletAddress,@CurrentWallet,@TokenType";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Insert;
}
else if (ScriptId == BarDMLScript.eCreateWallet)
{
DMLStatementPrefix = "insert into BarWallet (Tenant,OwnerId, CreationTime, ReceiveAddress, PublicKey, PrivateKey)";
paramNames = "@OwnerId,@CreationTime,@ReceiveAddress,@PublicKey,@PrivateKey";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Insert;
}
else if (ScriptId == BarDMLScript.eCreateTransaction)
{
DMLStatementPrefix = "insert into BarTransaction (Tenant,SourceWalletAddress, TargetWalletAddress, TokenAmount, TxDate)";
paramNames = "@SourceWalletAddress,@TargetWalletAddress,@TokenAmount,@TxDate";
expectedParamCount = 4;
dMLStatemtType = DMLStatemtType.Insert;
}
else if (ScriptId == BarDMLScript.eDeletUser)
{
DMLStatementPrefix = "delete from BarUser";
paramNames = "@Id";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Delete;
}
else if (ScriptId == BarDMLScript.eDeleteInstitution)
{
DMLStatementPrefix = "delete from BarInstitution";
paramNames = "@Id";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Delete;
}
else if (ScriptId == BarDMLScript.eDeleteToken)
{
DMLStatementPrefix = "delete from BarToken";
paramNames = "@Id";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Delete;
}
else if (ScriptId == BarDMLScript.eDeleteWallet)
{
DMLStatementPrefix = "delete from BarWallet";
paramNames = "@OwnerId";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Delete;
}
else if (ScriptId == BarDMLScript.eDeleteTransaction)
{
DMLStatementPrefix = "delete from BarTransaction";
paramNames = "@SourceWalletAddress";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Delete;
}
else if (ScriptId == BarDMLScript.eUpdateUser)
{
DMLStatementPrefix = "update BarUser set ";
paramNames = "@Id,$Username,$Password,$Email,$Telephone";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Update;
}
else if (ScriptId == BarDMLScript.eUpdateInstitution)
{
DMLStatementPrefix = "update BarInstitution set ";
paramNames = "@Id,$Name,$Password,$Email,$Telephone";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Update;
}
else if (ScriptId == BarDMLScript.eUpdateToken)
{
DMLStatementPrefix = "update BarToken set ";
paramNames = "@Id,$IssueTime,$OriginatorWalletAddress,$CurrentWallet,$TokenType";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Update;
}
else if (ScriptId == BarDMLScript.eUpdateWallet)
{
DMLStatementPrefix = "update BarWallet set ";
paramNames = "@OwnerId,$CreationTime,$ReceiveAddress,$PublicKey,$PrivateKey";
expectedParamCount = 5;
dMLStatemtType = DMLStatemtType.Update;
}
else if (ScriptId == BarDMLScript.eUpdateTransaction)
{
DMLStatementPrefix = "update BarTransaction set ";
paramNames = "@SourceWalletAddress,$TargetWalletAddress,$TokenAmount,$TxDate";
expectedParamCount = 4;
dMLStatemtType = DMLStatemtType.Update;
}
else if (ScriptId == BarDMLScript.eGetUser)
{
DMLStatementPrefix = "select * from BarUser";
paramNames = "@Id";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetUserByLoginDetails)
{
DMLStatementPrefix = "select * from BarUser";
paramNames = "@UserName,@Password";
expectedParamCount = 2;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetAllUsers)
{
DMLStatementPrefix = "select * from BarUser";
paramNames = "";
expectedParamCount = 0;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetInstitution)
{
DMLStatementPrefix = "select * from BarInstitution";
paramNames = "@Id";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetInstitutionByLoginDetails)
{
DMLStatementPrefix = "select * from BarInstitution";
paramNames = "@Name,@Password";
expectedParamCount = 2;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetAllInstitutions)
{
DMLStatementPrefix = "select * from BarInstitution";
paramNames = "";
expectedParamCount = 0;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetToken)
{
DMLStatementPrefix = "select * from BarToken";
paramNames = "@Id";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetWallet)
{
DMLStatementPrefix = "select * from BarWallet";
paramNames = "@OwnerId";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eGetTransaction)
{
DMLStatementPrefix = "select * from BarTransaction";
paramNames = "@SourceWalletAddress";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Select;
}
else if (ScriptId == BarDMLScript.eSendTokens)
{
DMLStatementPrefix = "update BarToken set CurrentWallet = @NewWallet WHERE CurrentWallet = @CurrentWallet AND Tenant = @Tenant AND Id In (select Id from BarToken Where CurrentWallet = @CurrentWallet limit @TokenCount)";
paramNames = "@TokenCount,@CurrentWallet,@NewWallet";
expectedParamCount = 3;
dMLStatemtType = DMLStatemtType.Update;
}
else if (ScriptId == BarDMLScript.eGetBalance)
{
DMLStatementPrefix = "select COUNT(*) AS Balance from BarToken";
paramNames = "@CurrentWallet";
expectedParamCount = 1;
dMLStatemtType = DMLStatemtType.Select;
}
else
{
throw new Exception("CODE LOGIC ERROR: Unknown DML script");
}
string[] paramaterNames;
if (paramNames.Length > 0)
{
paramaterNames = paramNames.Split(',');
}
else
{
paramaterNames = new string[] { }
};
return(_GetDMLScript(Tenant, DMLStatementPrefix, paramaterNames, ParameterValues, expectedParamCount, dMLStatemtType, null));
//scripts.Add("create table (Id varchar(50), datetime, varchar(100), varchar (100), int)");
//scripts.Add("create table BarWallet (OwnerId varchar(50), CreationTime datetime, ReceiveAddress varchar(100), PublicKey varchar (100), PrivateKey varchar(100))");
//scripts.Add("create table BarTransaction ()");
}
internal static SQLDMLStatement GetDMLStatement(IDatabaseTenant Tenant, BarDMLScript ScriptId, object [] Parameters)
{
SQLDMLScripts dmlScripts = new SQLDMLScripts();
if (ScriptId == BarDMLScript.eCreateUser)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eCreateUser, Parameters));
}
if (ScriptId == BarDMLScript.eCreateInstitution)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eCreateInstitution, Parameters));
}
if (ScriptId == BarDMLScript.eCreateToken)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eCreateToken, Parameters));
}
if (ScriptId == BarDMLScript.eCreateWallet)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eCreateWallet, Parameters));
}
if (ScriptId == BarDMLScript.eCreateTransaction)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eCreateTransaction, Parameters));
}
if (ScriptId == BarDMLScript.eDeletUser)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eDeletUser, Parameters));
}
if (ScriptId == BarDMLScript.eDeleteInstitution)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eDeleteInstitution, Parameters));
}
if (ScriptId == BarDMLScript.eDeleteToken)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eDeleteToken, Parameters));
}
if (ScriptId == BarDMLScript.eDeleteWallet)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eDeleteWallet, Parameters));
}
if (ScriptId == BarDMLScript.eDeleteTransaction)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eDeleteTransaction, Parameters));
}
if (ScriptId == BarDMLScript.eGetUser)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetUser, Parameters));
}
if (ScriptId == BarDMLScript.eGetUserByLoginDetails)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetUserByLoginDetails, Parameters));
}
if (ScriptId == BarDMLScript.eGetAllUsers)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetAllUsers, Parameters));
}
if (ScriptId == BarDMLScript.eGetInstitution)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetInstitution, Parameters));
}
if (ScriptId == BarDMLScript.eGetInstitutionByLoginDetails)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetInstitutionByLoginDetails, Parameters));
}
if (ScriptId == BarDMLScript.eGetAllInstitutions)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetAllInstitutions, Parameters));
}
if (ScriptId == BarDMLScript.eGetToken)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetToken, Parameters));
}
if (ScriptId == BarDMLScript.eGetWallet)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetWallet, Parameters));
}
if (ScriptId == BarDMLScript.eGetTransaction)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetTransaction, Parameters));
}
if (ScriptId == BarDMLScript.eUpdateUser)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eUpdateUser, Parameters));
}
if (ScriptId == BarDMLScript.eUpdateInstitution)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eUpdateInstitution, Parameters));
}
if (ScriptId == BarDMLScript.eUpdateToken)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eUpdateToken, Parameters));
}
if (ScriptId == BarDMLScript.eUpdateWallet)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eUpdateWallet, Parameters));
}
if (ScriptId == BarDMLScript.eUpdateTransaction)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eUpdateTransaction, Parameters));
}
if (ScriptId == BarDMLScript.eSendTokens)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eSendTokens, Parameters));
}
if (ScriptId == BarDMLScript.eGetBalance)
{
return(dmlScripts.GetScript(Tenant, BarDMLScript.eGetBalance, Parameters));
}
throw new Exception("CODE LOGIC ERROR : " + ScriptId.ToString() + " not in statement list.");
}
internal static SQLDMLStatement GetDMLStatementForGenericEntity(IDatabaseTenant Tenant, object Entity, DMLStatemtType dMLStatemtType, List <string> querProperytParametersToUse)
{
List <object> propValues = GetObjectPropertyValues(Entity);
List <string> propNames = GetObjectPropertyNames(Entity);
List <ISQLDMLStatementVariable> uniqueKeys = null;
if (dMLStatemtType == DMLStatemtType.SelectAll && querProperytParametersToUse == null)
{
uniqueKeys = new List <ISQLDMLStatementVariable>
{
new SQLDMLStatementVariable {
Name = "@Tenant", Value = "TR1"
}
};
}
else if (querProperytParametersToUse == null)
{
EntityDescriber ed = new EntityDescriber(Entity);
bool primaryKeyIdFieldValueSupplied = ed.PrimaryKeyProvidedOnEntity();
if (primaryKeyIdFieldValueSupplied && (dMLStatemtType == DMLStatemtType.Update || dMLStatemtType == DMLStatemtType.Delete))
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, true, false);
}
else if (primaryKeyIdFieldValueSupplied == false && (dMLStatemtType == DMLStatemtType.Update || dMLStatemtType == DMLStatemtType.Delete))
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, false, true);
}
else
{
uniqueKeys = GetUniqueKeyNameValuePairs(Entity, false, true);
}
}
else
{
uniqueKeys = GetFilterNameValuePairs(Entity);
}
List <ISQLDMLStatementVariable> uniqueKeysFiltered = new List <ISQLDMLStatementVariable>();
if (querProperytParametersToUse != null)
{
querProperytParametersToUse.Add("Tenant");
foreach (var key in uniqueKeys)
{
if (querProperytParametersToUse.Contains(key.Name.Replace("@", "")))
{
uniqueKeysFiltered.Add(key);
}
}
}
else
{
uniqueKeysFiltered = uniqueKeys;
}
SQLDMLScripts dmlScripts = new SQLDMLScripts();
return(dmlScripts.GetInsertScriptForTypedEntity(Tenant, Entity, propNames, propValues, dMLStatemtType, uniqueKeysFiltered));
}
