我正在编写针对SQL Server数据工具的代码生成工具,我需要能够获取数据类型:
- 查看列
- 表上计算的列
这个信息在哪里?对于表(计算列除外),它在这里:
TSqlObject table; //retrieved elsewhere
TSqlObject column = table.GetReferenced(Table.Columns).First(); //first column
TSqlObject dataType = column.GetReferenced(Column.DataType).FirstOrDefault();
对于计算的列,上面的dataType
为null。
供浏览量,我尝试了:
TSqlObject view; //retrieved elsewhere
TSqlObject column = view.GetReferenced(View.Columns).First(); //first column
TSqlObject dataType = column.GetReferenced(Column.DataType).FirstOrDefault();//null
此信息是否在任何地方?除了发布数据库的源DACPAC外,还有其他选择可以获取此信息吗?
编辑:响应下面的ED Elliot(关于使用强大的DACFX模型)
以下代码无法带回视图的类型信息:
TSqlTypedModel model = new TSqlTypedModel(@"path.dacpac");
var view = model.GetObjects<TSqlView>(Microsoft.SqlServer.Dac.Model.DacQueryScopes.UserDefined).FirstOrDefault();
var viewcolumns = view.Columns;
//false
bool viewHasTypeInformation = viewcolumns.Any(c => c.DataType.Count() > 0);
var table = model.GetObjects<TSqlTable>(Microsoft.SqlServer.Dac.Model.DacQueryScopes.UserDefined).FirstOrDefault();
var tablecolumns = table.Columns;
//true
bool tableHasTypeInformation = tablecolumns.Any(c => c.DataType.Count() > 0);
我开始认为这是DAC模型本身的限制。
哦,很棒的主题:)
使用DACFXSTRONGLYTYPEDMODEL对象查询的最简单方法:
https://github.com/microsoft/dacextensions
这有点奇怪,因为它是您构建的样本,然后使您可以轻松地查询DACFX:
https://github.com/microsoft/dacextensions/tree/master/dacfxstronglonglonglonglonglonglonglytypedmodel
要获得一个强大的模型:
var model = new TSqlTypedModel("dacpacPath");
然后,当您查询所有视图(或其他)时,您会收到一个列出的列表,这些对象很" saner"很多。比DACFX。
您可以回到视图的接口:
ISQL120TSQLVIEW(将版本编号更改为您的版本编号)具有列的IEnumerable:
IEnumerable<Microsoft.SqlServer.Dac.Extensions.Prototype.ISql120TSqlColumn> Columns
{
get;
}
列接口具有iEnumerable的数据类型:
IEnumerable<Microsoft.SqlServer.Dac.Extensions.Prototype.ISqlDataType> DataType
{
get;
}
我现在没有Windows机器可以为您提供完整的演示同时)。
要在视图上获取列列表:
var views = model.GetObjects<TSqlView>(DacQueryScopes.UserDefined);
foreach (var v in views)
{
Console.WriteLine(v.Columns.Count());
}
这对我有效,使用了130版的DAC DLL。
要获取计算列的详细信息,您需要查看" expressionDecondencies"。在列上(请参阅v.Columns
),该列与表相同。
编辑
所以我一直在玩游戏,有些事情在运行时您无法确定,因此DACFX无法弄清楚这些类型记录集并检查您的回来,但是如果以此为例,我们可以使用一些计算列的事情:
create table [dbo].[the_table]
(
[Id] INT not null primary key,
[StringCol] varchar(234) not null,
[a] int,
[b] decimal,
[a_and_b] as [a] + [b]
)
对于列,Id
,StringCol
,a
和b
,当我们使用强键入DACFX时,我们可以通过此操作获得列类型:
var tables = model.GetObjects(DacQueryScopes.UserDefined);
foreach (var t in tables)
{
switch (c.ColumnType)
{
case ColumnType.Column:
ShowType(c.DataType);
break;
}
}
ShowType
看起来像这样:
void ShowType(IEnumerable<ISqlDataType> types)
{
var builder = new StringBuilder();
foreach (var type in types)
{
var t = new TSqlDataType(type.Element);
builder.Append($"{t.SqlDataType.ToString()} ");
}
Console.Write(builder);
}
我们要做的是每列的数据类型列表,它可能只是int或类似的东西,但它是列表。
现在,因为我们有一个计算的列,而不仅仅是数据类型,我们参考了基础列,我们可以从:
中获取数据类型void ShowDependencies(IEnumerable<ISqlModelElementReference> dependencies)
{
foreach (var dependency in dependencies)
{
if (dependency is TSqlColumnReference)
{
var column = new TSqlColumn(dependency.Element);
Console.Write(column.Name + " ");
ShowType(column.DataType);
}
}
}
知道何时调用此版本:
var tables = model.GetObjects<TSqlTable>(DacQueryScopes.UserDefined);
foreach (var t in tables)
{
Console.WriteLine($"table - {t.Name}");
foreach (var c in t.Columns)
{
Console.Write("rn" + c.Name.ToString() + " ");
switch (c.ColumnType)
{
case ColumnType.Column:
ShowType(c.DataType);
break;
case ColumnType.ComputedColumn:
Console.Write($"({c.Expression}) ");
ShowDependencies(c.ExpressionDependencies);
break;
对于示例表,我们将获得此输出:
table - [dbo].[the_table]
[dbo].[the_table].[Id] Int
[dbo].[the_table].[StringCol] VarChar
[dbo].[the_table].[a] Int
[dbo].[the_table].[b] Decimal
[dbo].[the_table].[a_and_b] ([a] + [b]) [dbo].[the_table].[a] Int [dbo].[the_table].[b] Decimal view - [dbo].[mutli_type]
然后,我们需要决定什么是类型,因为猜测SQL将作为十进制的运行时间进行隐式演员,但是在编译时我认为这是不知道的(很高兴在这里纠正!)
如果我们以视图为例:
create view the_view
as
select
*,
object_name(4) some_name,
123 as an_int
from
the_table
我们从基表中有列,可以简单地列举,但是使用上面的代码相同的代码,但是对于object_name和123稍难,但是对于视图,我们得到了:
[dbo].[the_view].[Id] [dbo].[the_table].[Id] Int
[dbo].[the_view].[StringCol] [dbo].[the_table].[StringCol] VarChar
[dbo].[the_view].[a] [dbo].[the_table].[a] Int
[dbo].[the_view].[b] [dbo].[the_table].[b] Decimal
[dbo].[the_view].[a_and_b] [dbo].[the_table].[a_and_b]
[dbo].[the_view].[some_name] some_name = an_int =
[dbo].[the_view].[an_int] some_name = an_int =
因此,计算的列不得类型加上a_and_b的值,我们需要再次列举以获取上面的类型。
在这一点您会在数据或数据类型上获得非确定性的视图?
如果我们接受:
create view mutli_type
as
select case datepart(day, getdate())
when 1
then 100
when 2
then 'hello'
else
getdate()
end as multitype
取决于我们返回不同的数据类型的一天 - double ouch。
如果您确实需要知道返回的视图,则可以在视图中获取选择元素,并使用tsqlscript dom将它们分解为部分并尝试推断每个元素,我嘲笑了一个发现GetDate的示例()功能在此视图中使您了解您需要做的事情,但这并不简单,我什至不想考虑可以通过动态SQL传递的存储过程:
完整样本:
create table [dbo].[the_table]
(
[Id] INT not null primary key,
[StringCol] varchar(234) not null,
[a] int,
[b] decimal,
[a_and_b] as [a] + [b]
)
go
create view the_view
as
select *, object_name(4) some_name, 123 as an_int from the_table
go
create view mutli_type
as
select case datepart(day, getdate())
when 1
then 100
when 2
then 'hello'
else
getdate()
end as multitype
go
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Dac.Extensions.Prototype;
using Microsoft.SqlServer.Dac.Model;
using Microsoft.SqlServer.TransactSql.ScriptDom;
using ColumnType = Microsoft.SqlServer.Dac.Model.ColumnType;
namespace ConsoleApplication1
{
class Program
{
static void ShowType(IEnumerable<ISqlDataType> types)
{
var builder = new StringBuilder();
foreach (var type in types)
{
var t = new TSqlDataType(type.Element);
builder.Append($"{t.SqlDataType.ToString()} ");
}
Console.Write(builder);
}
static void ShowDependencies(IEnumerable<ISqlModelElementReference> dependencies)
{
foreach (var dependency in dependencies)
{
if (dependency is TSqlColumnReference)
{
var column = new TSqlColumn(dependency.Element);
Console.Write(column.Name + " ");
ShowType(column.DataType);
}
}
}
static void Main(string[] args)
{
var model = new TSqlTypedModel(@"pathDa.dacpac");
var views = model.GetObjects<TSqlView>(DacQueryScopes.UserDefined);
var tables = model.GetObjects<TSqlTable>(DacQueryScopes.UserDefined);
foreach (var t in tables)
{
Console.WriteLine($"table - {t.Name}");
foreach (var c in t.Columns)
{
Console.Write("rn" + c.Name.ToString() + " ");
switch (c.ColumnType)
{
case ColumnType.Column:
ShowType(c.DataType);
break;
case ColumnType.ComputedColumn:
Console.Write($"({c.Expression}) ");
ShowDependencies(c.ExpressionDependencies);
break;
case ColumnType.ColumnSet:
break;
default:
throw new ArgumentOutOfRangeException();
}
}
}
foreach (var v in views)
{
Console.WriteLine($"view - {v.Name}");
foreach (var c in v.Columns)
{
Console.Write("rn" + c.Name.ToString() + " ");
var needDomParse = false;
switch (c.ColumnType)
{
case ColumnType.Column:
ShowType(c.DataType);
ShowDependencies(c.ExpressionDependencies);
break;
case ColumnType.ComputedColumn:
ShowType(c.DataType);
ShowDependencies(c.ExpressionDependencies);
if (!c.DataType.Any() && !c.ExpressionDependencies.Any())
{
needDomParse = true;
}
break;
case ColumnType.ColumnSet:
break;
default:
throw new ArgumentOutOfRangeException();
}
if (needDomParse)
{
//ouch
var create = new CreateViewStatement();
var parser = new TSql130Parser(false);
IList<ParseError> errors;
var fragment = parser.Parse(new StringReader(v.GetScript()), out errors);
var selectVisitor = new SelectVisitor();
fragment.Accept(selectVisitor);
foreach (var s in selectVisitor.Selects)
{
var spec = s.QueryExpression as QuerySpecification;
foreach (var element in spec.SelectElements)
{
var select = element as SelectScalarExpression;
if (select != null)
{
Console.Write(select.ColumnName.Value + " = ");
var caseExpression = select.Expression as SimpleCaseExpression;
if (caseExpression != null)
{
var func = caseExpression.ElseExpression as FunctionCall;
Console.WriteLine(func.FunctionName.Value);
}
}
}
}
}
}
}
}
}
internal class SelectVisitor : TSqlConcreteFragmentVisitor
{
public List<SelectStatement> Selects = new List<SelectStatement>();
public override void Visit(SelectStatement node)
{
Selects.Add(node);
base.Visit(node);
}
}
}
我希望它能有所帮助,我知道这不是这样做的,但希望可以解释一些挑战:)
ed