private static void LoadFunctions(Database database, string connectionString)
{
try
{
//Add the Functions
var dsFunction = DatabaseHelper.ExecuteDataset(connectionString, SchemaModelHelper.GetSqlForFunctions());
if (dsFunction.Tables.Count > 0)
{
foreach (DataRow rowFunction in dsFunction.Tables[0].Rows)
{
var name = (string)rowFunction["name"];
var schema = (string)rowFunction["schemaname"];
var sql = SchemaModelHelper.GetFunctionBody(schema, name, connectionString);
var function = database.FunctionList.FirstOrDefault(x => x.Name == name);
if (function == null)
{
function = new Function();
function.Name = name;
function.Schema = schema;
function.SQL = sql;
database.FunctionList.Add(function);
}
}
}
foreach (var function in database.FunctionList)
{
var dsFunctionAux = DatabaseHelper.ExecuteDataset(connectionString, "sp_help '[" + function.Schema + "].[" + function.Name + "]'");
DataTable dtColumn = null;
DataTable dtParameter = null;
foreach (DataTable dt in dsFunctionAux.Tables)
{
if (dt.Columns.Contains("column_name"))
dtColumn = dt;
else if (dt.Columns.Contains("parameter_name"))
dtParameter = dt;
}
//Add the columns
if (dtColumn != null)
{
foreach (DataRow row in dtColumn.Rows)
{
var field = new Field();
field.Name = (string)row["column_name"];
var dataType = DatabaseHelper.GetSQLDataType((string)row["type"], database.UserDefinedTypes);
var length = int.Parse(row["length"].ToString());
//The length is half the bytes for these types
if ((dataType == SqlDbType.NChar) ||
(dataType == SqlDbType.NVarChar))
{
length = length / 2;
}
field.DataType = dataType;
field.Nullable = row["column_name"].ToString() == "yes" ? true : false;
field.Length = length;
if (row["scale"] != System.DBNull.Value && !string.IsNullOrEmpty((string)row["scale"]) && ((string)row["scale"]).Trim() != string.Empty)
field.Scale = int.Parse(row["scale"].ToString());
function.FieldList.Add(field);
}
}
function.IsTable = (dtColumn != null);
//Add the parameters
if (dtParameter != null)
{
var sortOrder = 1;
foreach (DataRow row in dtParameter.Rows)
{
var name = ((string)row["parameter_name"]).Replace("@", string.Empty);
if (string.IsNullOrEmpty(name))
{
//This is a return value for a scalar function
//If there is no name then this is the return
var field = new Field();
field.Name = "Value";
field.Nullable = true;
var dataType = DatabaseHelper.GetSQLDataType((string)row["type"], database.UserDefinedTypes);
var length = int.Parse(row["length"].ToString());
//The length is half the bytes for these types
if ((dataType == SqlDbType.NChar) ||
(dataType == SqlDbType.NVarChar))
{
length = length / 2;
}
field.DataType = dataType;
field.Length = length;
if (row["scale"] != System.DBNull.Value)
field.Scale = int.Parse(row["scale"].ToString());
function.FieldList.Add(field);
}
else
{
//This is a parameter
var parameter = new Parameter();
parameter.Name = name;
parameter.SortOrder = sortOrder;
sortOrder++;
var dataType = DatabaseHelper.GetSQLDataType((string)row["type"], database.UserDefinedTypes);
parameter.DataType = dataType;
var length = int.Parse(row["length"].ToString());
//The length is half the bytes for these types
if ((dataType == SqlDbType.NChar) ||
(dataType == SqlDbType.NVarChar))
{
length = length / 2;
}
parameter.Length = length;
if (row["scale"] != System.DBNull.Value)
parameter.Scale = int.Parse(row["scale"].ToString());
function.ParameterList.Add(parameter);
}
}
}
}
}
catch (Exception /*ignored*/)
{
throw;
}
}