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