protected override List <ModuleResultElement> GetResultElements(SqlConnection connection, string schema, string name)
        {
            var dtResult = new DataTable();
            var result   = new List <ModuleResultElement>();

            var sql = $"exec dbo.sp_describe_first_result_set N'[{schema}].[{name}]';";

            var adapter = new SqlDataAdapter
            {
                SelectCommand = new SqlCommand(sql, connection)
            };

            adapter.Fill(dtResult);

            int rCounter = 0;

            foreach (DataRow res in dtResult.Rows)
            {
                var parameter = new ModuleResultElement()
                {
                    Name      = string.IsNullOrEmpty(res["name"].ToString()) ? $"Col{rCounter}" : res["name"].ToString(),
                    StoreType = string.IsNullOrEmpty(res["system_type_name"].ToString()) ? res["user_type_name"].ToString() : res["system_type_name"].ToString(),
                    Ordinal   = int.Parse(res["column_ordinal"].ToString()),
                    Nullable  = (bool)res["is_nullable"],
                };

                result.Add(parameter);

                rCounter++;
            }

            return(result);
        }
コード例 #2
0
        public static Type ClrType(this ModuleResultElement moduleResultElement)
        {
            if (moduleResultElement is null)
            {
                throw new ArgumentNullException(nameof(moduleResultElement));
            }

            return(GetClrType(moduleResultElement.StoreType, moduleResultElement.Nullable));
        }
コード例 #3
0
        protected override List <List <ModuleResultElement> > GetResultElementLists(SqlConnection connection, Routine module, bool multipleResults, bool useLegacyResultSetDiscovery)
        {
            if (module is null)
            {
                throw new ArgumentNullException(nameof(module));
            }

            using var dtResult = new DataTable();
            var list = new List <ModuleResultElement>();

            var sql = $@"
SELECT 
    c.name,
    COALESCE(type_name(c.system_type_id), type_name(c.user_type_id)) AS type_name,
    c.column_id AS column_ordinal,
    c.is_nullable
FROM sys.columns c
WHERE object_id = OBJECT_ID('{module.Schema}.{module.Name}');";

#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
            using var adapter = new SqlDataAdapter
                  {
                      SelectCommand = new SqlCommand(sql, connection),
                  };
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities

            adapter.Fill(dtResult);

            int rCounter = 0;

            foreach (DataRow res in dtResult.Rows)
            {
                var parameter = new ModuleResultElement()
                {
                    Name      = string.IsNullOrEmpty(res["name"].ToString()) ? $"Col{rCounter}" : res["name"].ToString(),
                    StoreType = res["type_name"].ToString(),
                    Ordinal   = int.Parse(res["column_ordinal"].ToString(), CultureInfo.InvariantCulture),
                    Nullable  = (bool)res["is_nullable"],
                };

                list.Add(parameter);

                rCounter++;
            }

            var result = new List <List <ModuleResultElement> >
            {
                list,
            };

            return(result);
        }
コード例 #4
0
        private static List <List <ModuleResultElement> > GetFirstResultSet(SqlConnection connection, string schema, string moduleName)
        {
            using var dtResult = new DataTable();
            var list = new List <ModuleResultElement>();

            var sql = $"exec dbo.sp_describe_first_result_set N'[{schema}].[{moduleName}]';";

#pragma warning disable CA2100 // Review SQL queries for security vulnerabilities
            using var adapter = new SqlDataAdapter
                  {
                      SelectCommand = new SqlCommand(sql, connection),
                  };
#pragma warning restore CA2100 // Review SQL queries for security vulnerabilities

            adapter.Fill(dtResult);

            int rCounter = 0;

            foreach (DataRow row in dtResult.Rows)
            {
                var name = row["name"].ToString();
                if (string.IsNullOrEmpty(name))
                {
                    continue;
                }

                var parameter = new ModuleResultElement()
                {
                    Name      = name,
                    StoreType = string.IsNullOrEmpty(row["system_type_name"].ToString()) ? row["user_type_name"].ToString() : row["system_type_name"].ToString(),
                    Ordinal   = int.Parse(row["column_ordinal"].ToString(), CultureInfo.InvariantCulture),
                    Nullable  = (bool)row["is_nullable"],
                };

                list.Add(parameter);

                rCounter++;
            }

            var result = new List <List <ModuleResultElement> >
            {
                list,
            };

            return(result);
        }
コード例 #5
0
        protected override List <List <ModuleResultElement> > GetResultElementLists(SqlConnection connection, Routine module, bool multipleResultSets = false)
        {
            var dtResult = new DataTable();
            var list     = new List <ModuleResultElement>();

            var sql = $@"
SELECT 
    c.name,
    COALESCE(type_name(c.system_type_id), type_name(c.user_type_id)) AS type_name,
    c.column_id AS column_ordinal,
    c.is_nullable
FROM sys.columns c
WHERE object_id = OBJECT_ID('{module.Schema}.{module.Name}');";

            var adapter = new SqlDataAdapter
            {
                SelectCommand = new SqlCommand(sql, connection)
            };

            adapter.Fill(dtResult);

            int rCounter = 0;

            foreach (DataRow res in dtResult.Rows)
            {
                var parameter = new ModuleResultElement()
                {
                    Name      = string.IsNullOrEmpty(res["name"].ToString()) ? $"Col{rCounter}" : res["name"].ToString(),
                    StoreType = res["type_name"].ToString(),
                    Ordinal   = int.Parse(res["column_ordinal"].ToString()),
                    Nullable  = (bool)res["is_nullable"],
                };

                list.Add(parameter);

                rCounter++;
            }

            var result = new List <List <ModuleResultElement> >
            {
                list
            };

            return(result);
        }
コード例 #6
0
 public static Type ClrType(this ModuleResultElement moduleResultElement)
 {
     return(GetClrType(moduleResultElement.StoreType, moduleResultElement.Nullable));
 }