public DatabaseFunctionDTO ToDatabaseFunctionDTO(DatabaseFunction cItem, bool withColumns)
{
BizColumn bizColumn = new BizColumn();
var result = new DatabaseFunctionDTO();
result.ID = cItem.ID;
result.Name = cItem.FunctionName;
//result.Catalog = cItem.Catalog;
result.SchemaID = cItem.DBSchemaID;
result.DatabaseID = cItem.DBSchema.DatabaseInformationID;
result.RelatedSchema = cItem.DBSchema.Name;
//result.ReturnType = cItem.ReturnType;
//if (cItem.ValueCustomType != null)
// result.ValueCustomType = (ValueCustomType)cItem.ValueCustomType;
result.Type = (Enum_DatabaseFunctionType)cItem.Type;
result.Title = cItem.Title;
result.Enable = cItem.Enable;
//if (result.Title == null)
//{
// result.Title = cItem.FunctionName;
//}
if (withColumns)
{
result.DatabaseFunctionParameter = ToDatabaseFunctionParameterDTO(cItem);
}
// if (result.Type == Enum_DatabaseFunctionType.Function)
result.ReturnDotNetType = GetReturnType(cItem);
return(result);
}
private RadTreeViewItem AddNode(ItemCollection collection, DatabaseFunctionDTO DatabaseFunction)
{
var node = new RadTreeViewItem();
node.DataContext = DatabaseFunction;
node.Header = GetNodeHeader(string.IsNullOrEmpty(DatabaseFunction.Title) ? DatabaseFunction.Name : DatabaseFunction.Title, "function");
node.ToolTip = DatabaseFunction.Title;
collection.Add(node);
return(node);
}
private FunctionResult GetDBFunctionValue(DatabaseFunctionDTO databaseFunction, List <IDataParameter> paramList)
{
try
{
string stringParamList = "";
foreach (var column in paramList)
{
stringParamList += (stringParamList == "" ? "" : ",") + column.ParameterName;
}
if (stringParamList != "")
{
stringParamList = "(" + stringParamList + ")";
}
FunctionResult result = new FunctionResult();
var dbHelper = ConnectionManager.GetDBHelper(databaseFunction.DatabaseID);
result.Result = dbHelper.ExecuteScalar("select " + (string.IsNullOrEmpty(databaseFunction.RelatedSchema) ? "" : databaseFunction.RelatedSchema + ".") + databaseFunction.Name + stringParamList, paramList);
if (result.Result is string)
{
if (Convert.ToString(result.Result) != null)
{
if (Convert.ToString(result.Result).Contains("@"))
{
var splt = Convert.ToString(result.Result).Split("@".ToCharArray());
var res = splt[0];
if (res != null && res.ToLower() == "ExceptionWithMessage".ToLower())
{
result.Exception = new Exception(splt[1]);
}
else
{
result.Result = res;
}
}
}
}
return(result);
}
catch (Exception ex)
{
FunctionResult result = new FunctionResult();
result.Exception = ex;
return(result);
}
}
private string EntityIsModified(DatabaseFunctionDTO dbItem, DatabaseFunctionDTO existingEntity)
{
string result = "";
if (dbItem.RelatedSchema != existingEntity.RelatedSchema)
{
result += (result == "" ? "" : Environment.NewLine) + "شمای موجودیت تغییر کرده است";
}
//if (dbItem.ReturnType != existingEntity.ReturnType)
// result += (result == "" ? "" : Environment.NewLine) + "خروجی موجودیت تغییر کرده است";
if (dbItem.DatabaseFunctionParameter.Any(x => !existingEntity.DatabaseFunctionParameter.Any(y => y.ParameterName == x.ParameterName)))
{
foreach (var column in dbItem.DatabaseFunctionParameter.Where(x => !existingEntity.DatabaseFunctionParameter.Any(y => y.ParameterName == x.ParameterName)))
{
result += (result == "" ? "" : Environment.NewLine) + "ستون" + " " + column.ParameterName + " " + "اضافه شده است";
}
}
if (existingEntity.DatabaseFunctionParameter.Where(x => x.Enable).Any(x => !dbItem.DatabaseFunctionParameter.Any(y => y.ParameterName == x.ParameterName)))
{
foreach (var column in existingEntity.DatabaseFunctionParameter.Where(x => !dbItem.DatabaseFunctionParameter.Any(y => y.ParameterName == x.ParameterName)))
{
result += (result == "" ? "" : Environment.NewLine) + "ستون" + " " + column.ParameterName + " " + "حذف شده است";
}
}
foreach (var column in existingEntity.DatabaseFunctionParameter.Where(x => x.Enable && dbItem.DatabaseFunctionParameter.Any(y => y.ParameterName == x.ParameterName)))
{
var dbColumn = dbItem.DatabaseFunctionParameter.First(y => y.ParameterName == column.ParameterName);
if (column.DataType != dbColumn.DataType)
{
result += (result == "" ? "" : Environment.NewLine) + "نوع داده ستون" + " " + column.ParameterName + " " + "تغییر کرده است";
}
if (column.InputOutput != dbColumn.InputOutput)
{
result += (result == "" ? "" : Environment.NewLine) + "نوع ورودی/خروجی ستون" + " " + column.ParameterName + " " + "تغییر کرده است";
}
}
return(result);
}
private FunctionResult GetDBSPValue(DatabaseFunctionDTO databaseFunction, List <IDataParameter> paramList, string outputParameterName)
{
try
{
FunctionResult result = new FunctionResult();
var dbHelper = ConnectionManager.GetDBHelper(databaseFunction.DatabaseID);
result.Result = dbHelper.ExecuteStoredProcedure((string.IsNullOrEmpty(databaseFunction.RelatedSchema) ? "" : databaseFunction.RelatedSchema + ".") + databaseFunction.Name, paramList, outputParameterName);
if (result.Result is string)
{
if (Convert.ToString(result.Result) != null)
{
if (Convert.ToString(result.Result).Contains("@"))
{
var splt = Convert.ToString(result.Result).Split("@".ToCharArray());
var res = splt[0];
if (res != null && res.ToLower() == "ExceptionWithMessage".ToLower())
{
result.Exception = new Exception(splt[1]);
}
else
{
result.Result = res;
}
}
}
}
return(result);
}
catch (Exception ex)
{
FunctionResult result = new FunctionResult();
result.Exception = ex;
return(result);
}
}
//public FunctionResult GetDatabaseFunctionValue(DR_Requester resuester, int functionID, params object[] parameters)
//{
// FunctionResult result = new FunctionResult();
// var databaseFunction = bizDatabaseFunction.GetDatabaseFunction(functionID);
// return GetDatabaseFunctionValue(resuester, databaseFunction, parameters);
//}
private FunctionResult GetDatabaseFunctionValue(DR_Requester resuester, DatabaseFunctionDTO databaseFunction, List <Tuple <string, object> > parameters = null)// params object[] parameters)
{
FunctionResult result = new FunctionResult();
//if (parameters.Count() != databaseFunction.DatabaseFunctionParameter.Count(x => x.InputOutput != Enum_DatabaseFunctionParameterType.ReturnValue))
//{
// result.Exception = new Exception("تعداد پارامترهای ارسالی و تابع یکسان نمیباشد");
// result.Result = null;
// return result;
//}
//else
//{
//if (databaseFunction.Type == Enum_DatabaseFunctionType.Function)
//{
//}
//else
//{
//}
int paramCount = 0;
if (parameters != null)
{
paramCount = parameters.Count();
}
List <IDataParameter> paramList = new List <IDataParameter>();
if (databaseFunction.Type == Enum_DatabaseFunctionType.Function)
{
if (paramCount < databaseFunction.DatabaseFunctionParameter.Count(x => x.InputOutput == Enum_DatabaseFunctionParameterType.Input))
{
result.Exception = new Exception("تعداد پارامترهای ارسالی و تابع یکسان نمیباشد");
result.Result = null;
return(result);
}
// var indexer = 0;
foreach (var column in databaseFunction.DatabaseFunctionParameter.Where(x => x.InputOutput == Enum_DatabaseFunctionParameterType.Input).OrderBy(x => x.Order))
{
if (parameters != null && parameters.Any(x => x.Item1 == column.ParameterName))
{
paramList.Add(ToDbParameter(column, parameters.First(x => x.Item1 == column.ParameterName).Item2));
}
else
{
result.Exception = new Exception("پارامتر" + " " + column.ParameterName + " " + "مقداردهی نشده است");
result.Result = null;
return(result);
}
}
return(GetDBFunctionValue(databaseFunction, paramList));
}
else
{
if (paramCount < databaseFunction.DatabaseFunctionParameter.Count(x => x.InputOutput == Enum_DatabaseFunctionParameterType.Input))
{
result.Exception = new Exception("تعداد پارامترهای ارسالی و تابع یکسان نمیباشد");
result.Result = null;
return(result);
}
DatabaseFunctionColumnDTO outputParameter = null;
DatabaseFunctionColumnDTO returnValueParameter = databaseFunction.DatabaseFunctionParameter.FirstOrDefault(x => x.InputOutput == Enum_DatabaseFunctionParameterType.ReturnValue);
outputParameter = databaseFunction.DatabaseFunctionParameter.FirstOrDefault(x => x.InputOutput == Enum_DatabaseFunctionParameterType.InputOutput || x.InputOutput == Enum_DatabaseFunctionParameterType.Output);
if (outputParameter == null)
{
if (returnValueParameter != null)
{
outputParameter = returnValueParameter;
}
else
{
result.Exception = new Exception("این تابع خروجی ندارد!");
result.Result = null;
return(result);
}
}
//List<Tuple<DatabaseFunctionColumnDTO, object>> columnsAndValues = new List<Tuple<DatabaseFunctionColumnDTO, object>>();
//var indexer = 0;
foreach (var column in databaseFunction.DatabaseFunctionParameter.Where(x => x.InputOutput != Enum_DatabaseFunctionParameterType.ReturnValue).OrderBy(x => x.Order))
{
if (column.InputOutput != Enum_DatabaseFunctionParameterType.Output)
{
if (parameters != null && parameters.Any(x => x.Item1 == column.ParameterName))
{
paramList.Add(ToDbParameter(column, parameters.First(x => x.Item1 == column.ParameterName).Item2));
}
else
{
if (column.InputOutput == Enum_DatabaseFunctionParameterType.Input)
{
result.Exception = new Exception("پارامتر" + " " + column.ParameterName + " " + "مقداردهی نشده است");
result.Result = null;
return(result);
}
else
{
paramList.Add(ToDbParameter(column, GetPropertyDefaultValue(column.DotNetType)));
}
}
}
else
{
//تاحالا پارامتری که output خالی باشه ندیدم پس این تیکه تست نشده
paramList.Add(ToDbParameter(column, DBNull.Value));
}
}
if (returnValueParameter != null)
{
paramList.Add(ToDbParameter(returnValueParameter, DBNull.Value));
}
return(GetDBSPValue(databaseFunction, paramList, outputParameter.ParameterName));
}
//}
}
private void UpdateEntityInModel(MyProjectEntities projectContext, int databaseID, DatabaseFunctionDTO function, List <DBSchema> listAddedSchema)
{
DBSchema dbSchema = null;
var schema = function.RelatedSchema;
dbSchema = listAddedSchema.FirstOrDefault(x => x.DatabaseInformationID == databaseID && x.Name == function.RelatedSchema);
if (dbSchema == null)
{
dbSchema = new DBSchema()
{
DatabaseInformationID = databaseID, Name = schema
};
dbSchema.SecurityObject = new SecurityObject();
dbSchema.SecurityObject.Type = (int)DatabaseObjectCategory.Schema;
projectContext.DBSchema.Add(dbSchema);
listAddedSchema.Add(dbSchema);
}
var dbFunction = projectContext.DatabaseFunction.Include("DatabaseFunctionParameter").FirstOrDefault(x => x.FunctionName == function.Name && x.DBSchema.DatabaseInformationID == databaseID);
if (dbFunction == null)
{
dbFunction = new DatabaseFunction();
projectContext.DatabaseFunction.Add(dbFunction);
dbFunction.FunctionName = function.Name;
dbFunction.Enable = true;
}
dbFunction.DBSchema = dbSchema;
dbFunction.Type = (short)function.Type;
foreach (var column in function.DatabaseFunctionParameter)
{
var dbColumn = dbFunction.DatabaseFunctionParameter.Where(x => x.ParamName == column.ParameterName).FirstOrDefault();
if (dbColumn == null)
{
dbColumn = new DatabaseFunctionParameter();
dbColumn.ParamName = column.ParameterName;
dbColumn.Enable = true;
dbFunction.DatabaseFunctionParameter.Add(dbColumn);
}
dbColumn.InputOutput = (short)column.InputOutput;
dbColumn.DataType = column.DataType;
dbColumn.ParamName = column.ParameterName;
}
var columnNames = function.DatabaseFunctionParameter.Select(x => x.ParameterName).ToList();
foreach (var dbColumn in dbFunction.DatabaseFunctionParameter.Where(x => !columnNames.Contains(x.ParamName)))
{
dbColumn.Enable = false;
}
}