Esempio n. 1
0
        public List <RawStoredProcedure> ReadStoredProcs(bool includeSynonyms)
        {
            if (DatabaseReaderPlugin != null)
            {
                return(DatabaseReaderPlugin.ReadStoredProcs());
            }

            var result = new List <RawStoredProcedure>();

            using (var conn = _factory.CreateConnection())
            {
                if (conn == null)
                {
                    return(result);
                }

                conn.ConnectionString = Settings.ConnectionString;
                conn.Open();

                var storedProcedureSQL = StoredProcedureSQL();
                if (string.IsNullOrEmpty(storedProcedureSQL))
                {
                    return(result);
                }

                var cmd = GetCmd(conn);
                if (cmd == null)
                {
                    return(result);
                }

                if (includeSynonyms)
                {
                    cmd.CommandText = SynonymStoredProcedureSQLSetup() + storedProcedureSQL + SynonymStoredProcedureSQL() + SpecialQueryFlags();
                }
                else
                {
                    cmd.CommandText = storedProcedureSQL + SpecialQueryFlags();
                }

                using (var rdr = cmd.ExecuteReader())
                {
                    var lastName         = string.Empty;
                    var emptyParamNumber = 1;
                    while (rdr.Read())
                    {
                        var rawDataType    = rdr["DATA_TYPE"];
                        var schema         = rdr["SPECIFIC_SCHEMA"].ToString().Trim();
                        var name           = rdr["SPECIFIC_NAME"].ToString().Trim();
                        var routineType    = rdr["ROUTINE_TYPE"].ToString().Trim().ToLower();
                        var returnDataType = rdr["RETURN_DATA_TYPE"].ToString().Trim().ToLower();
                        var dataType       = rawDataType.ToString().Trim().ToLower();
                        var parameterMode  = rdr["PARAMETER_MODE"].ToString().Trim().ToLower();

                        if (name != lastName)
                        {
                            lastName         = name;
                            emptyParamNumber = 1;
                        }

                        var isTableValuedFunction  = (routineType == "function" && returnDataType == "table");
                        var isScalarValuedFunction = (routineType == "function" && returnDataType != "table");
                        var isStoredProcedure      = (routineType == "procedure");

                        StoredProcedureParameter parameter = null;
                        if (rawDataType != DBNull.Value)
                        {
                            parameter = new StoredProcedureParameter
                            {
                                Ordinal             = ChangeType <int>(rdr["ORDINAL_POSITION"]),
                                Name                = rdr["PARAMETER_NAME"].ToString().Trim(),
                                SqlDbType           = GetStoredProcedureParameterDbType(dataType),
                                ReturnSqlDbType     = GetStoredProcedureParameterDbType(returnDataType),
                                PropertyType        = GetPropertyType(dataType),
                                ReturnPropertyType  = GetPropertyType(returnDataType),
                                DateTimePrecision   = ChangeType <short>(rdr["DATETIME_PRECISION"]),
                                MaxLength           = ChangeType <int>(rdr["CHARACTER_MAXIMUM_LENGTH"]),
                                Precision           = ChangeType <byte>(rdr["NUMERIC_PRECISION"]),
                                Scale               = ChangeType <int>(rdr["NUMERIC_SCALE"]),
                                UserDefinedTypeName = rdr["USER_DEFINED_TYPE"].ToString().Trim(),
                                IsSpatial           = SpatialTypes.Contains(dataType)
                            };

                            if (string.IsNullOrEmpty(parameter.Name))
                            {
                                parameter.Name = "p" + emptyParamNumber++;
                            }

                            switch (parameterMode)
                            {
                            case "in":
                                parameter.Mode = StoredProcedureParameterMode.In;
                                break;

                            case "out":
                                parameter.Mode = StoredProcedureParameterMode.Out;
                                break;

                            default:
                                parameter.Mode = StoredProcedureParameterMode.InOut;
                                break;
                            }

                            var clean = CleanUp(parameter.Name.Replace("@", string.Empty));
                            if (!string.IsNullOrEmpty(clean))
                            {
                                parameter.NameHumanCase = Inflector.MakeInitialLower((Settings.UsePascalCase ? Inflector.ToTitleCase(clean) : clean).Replace(" ", ""));

                                if (ReservedKeywords.Contains(parameter.NameHumanCase))
                                {
                                    parameter.NameHumanCase = "@" + parameter.NameHumanCase;
                                }
                            }
                        }

                        var rsp = new RawStoredProcedure(schema, name, isTableValuedFunction, isScalarValuedFunction, isStoredProcedure, parameter);
                        result.Add(rsp);
                    }
                }
            }
            return(result);
        }