private static void RunGeneratedCommand(IConnectToDB _Connect, T thisModel, SQLProcedureModels.BIG_CALL RUN)
{
DataTable _result = ER_Procedure.SQL_PROCEDURE_PARAMS(_Connect, RUN).COMMANDS[0].result._DataTable;
//DataRow[] outputRow = _result.Select("ID = " + outputParam);
string attemptedSQL = _result.Rows.Count > 0 && _result.Rows[0].Field <string>("ChildSQL") != null ? _result.Rows[0].Field <string>("ChildSQL").ToString() : "";
bool isError = _result.Rows.Count > 0 && _result.Rows[0].Field <string>("ChildType") != null && _result.Rows[0].Field <string>("ChildType").ToString() == "Error";
if (!String.IsNullOrWhiteSpace(attemptedSQL))
{
thisModel.GetType().GetProperty("V_ATTEMPTED_SQL")?.SetValue(thisModel, attemptedSQL, null);
}
if (isError)
{
try
{
thisModel.GetType().GetProperty("O_ERR_MESS")?.SetValue(thisModel, _result.Rows[0].Field <string>("ChildValue").ToString(), null);
}
catch (Exception)
{
}
}
else
{
foreach (DataRow row in _result.Rows)
{
var modelProperty = row["ChildItem"].ToString().Replace("@", "");
var thisValue = row["ChildValue"];
if (modelProperty.Substring(0, 2) == "O_")
{
var propertyType = thisModel.GetType().GetProperty(modelProperty)?.PropertyType;
object propertyVal = row["ChildValue"];
if (propertyType != null)
{
//propertyVal = Convert.ChangeType(thisValue, propertyType);
propertyVal = ChangeType(propertyVal, propertyType);
}
if (propertyVal != null)
{
thisModel.GetType().GetProperty(modelProperty)?.SetValue(thisModel, propertyVal, null);
}
}
//if (row.ItemArray[2].ToString() == ReturnValueFor && Int64.TryParse(row.ItemArray[3].ToString(), out outputid))
//{
// thisModel.GetType().GetProperty(outputParam).SetValue(thisModel, outputid, null);
//}
}
}
}
public T GenericInputProcedureParamsAndCall(IConnectToDB _Connect, T thisModel)
{ //START T GenericInputProcedure
SQLProcedureModels.BIG_CALL RUN = GetParams(thisModel);
try
{
RunGeneratedCommand(_Connect, thisModel, RUN);
//thisModel.GetType().GetProperty(outputParam).SetValue(thisModel, Convert.ToInt64(_result.Rows[properties.Length - 2].ItemArray[3]), null);
}
catch (Exception ex)
{
//ER_Tools._WriteEventLog(string.Format("Caught exception: {0} \r\n Stack Trace: {1}", ex.Message, ex.StackTrace), EventLogType.exception);
}
return(thisModel);
}
public static SQLProcedureModels.BIG_CALL GetParams(T thisModel)
{
List <DBParameters> EntryProcedureParameters = new List <DBParameters>();
PropertyInfo[] properties = thisModel.GetType().GetProperties();
Type modelType = properties.GetType();
string procedure = properties[0].Name;
string[] variableList = new string[] { "V_", "I_", "O_" };
foreach (PropertyInfo property in properties.Where(p => variableList.Contains(p.Name.Substring(0, 2))))
{
string name = property.Name;
object value = property.GetValue(thisModel, null);
if (name == "V_PROCEDURE_NAME")
{
procedure = value.ToString();
}
else if (name.Substring(0, 2) == "I_" || name.Substring(0, 2) == "O_")
{
string propertyType = (property.PropertyType == typeof(String) || property.PropertyType == typeof(byte[]) || property.PropertyType == typeof(Decimal)) ? property.PropertyType.Name : property.PropertyType.GenericTypeArguments[0].Name;
ParameterDirection direction = name.Substring(0, 2) == "I_" ? ParameterDirection.Input : ParameterDirection.Output;
SqlDbType dbType = new SqlDbType();
switch (propertyType)
{
default:
//'varchar' then 'string'
case "String":
dbType = SqlDbType.VarChar;
break;
//'char' then 'char?'
case "Char":
dbType = SqlDbType.Char;
break;
//'int' then 'int'
case "Int32":
dbType = SqlDbType.Int;
break;
//'bigint' then 'long?'
case "Int64":
dbType = SqlDbType.BigInt;
break;
//'numeric' then 'Decimal'
//'decimal' then 'Decimal'
case "Decimal":
dbType = SqlDbType.Decimal;
break;
//'date' then 'DateTime?'
//'smalldatetime' then 'DateTime?'
//'datetime' then 'DateTime?'
case "DateTime":
dbType = SqlDbType.DateTime;
break;
//'varbinary' then 'byte[]'
case "Byte[]":
dbType = SqlDbType.VarBinary;
break;
//'bit' then 'bool'
case "Boolean":
dbType = SqlDbType.Bit;
break;
case "Guid":
dbType = SqlDbType.UniqueIdentifier;
break;
}
EntryProcedureParameters.Add(new DBParameters {
ParamName = "@" + name, ParamDirection = direction, MSSqlParamDataType = dbType, ParamSize = -1, ParamValue = value
});
}
}
string ProcedureName = procedure;
string ProcedureReturnType = "value";
string outputParam = properties[properties.Length - 1].Name;
string ReturnValueFor = "@" + outputParam;
// ER_DML _dml = new ER_DML();
SQLProcedureModels.BIG_CALL RUN = new SQLProcedureModels.BIG_CALL();
RUN.COMMANDS = new List <SQLProcedureModels.SQL_PROCEDURE_CALL>();
RUN.COMMANDS.Add(new SQLProcedureModels.SQL_PROCEDURE_CALL {
ProcedureType = ProcedureReturnType, ProcedureName = ProcedureName, _dbParameters = EntryProcedureParameters
});
return(RUN);
}
