GetSqlForFunctions() static private method

static private GetSqlForFunctions ( ) : string
return string
Example #1
0
        public IEnumerable <string> GetFunctionList(string connectionString)
        {
            var retval = new List <string>();

            using (var tableReader = DatabaseHelper.ExecuteReader(connectionString, CommandType.Text, SchemaModelHelper.GetSqlForFunctions()))
            {
                while (tableReader.Read())
                {
                    var newEntity = new Function();
                    newEntity.Name = tableReader["name"].ToString();
                    retval.Add(newEntity.Name);
                    //newEntity.Schema = tableReader["schema"].ToString();
                }
            }
            return(retval);
        }
Example #2
0
        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;
            }
        }