static private GetFunctionBody ( string schema, string name, string connectionString ) : string | ||
schema | string | |
name | string | |
connectionString | string | |
return | string |
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; } }