ScriptDom parsing of SELECT with alias
up vote
2
down vote
favorite
I'm looking to use ScriptDom to parse T-SQL statement and ensure that at least two columns are specified. I found a way to extract the column names and can properly do my validation from that. However I also want to validate if the column was aliased and am not sure how to check that.
I want to ensure that the select statement specifies both MCA and MOC columns.
// this should pass
var sql = "SELECT MCA, MOC
FROM myTable";
// this should also pass
var sql = "SELECT some_col as MCA, some_other_col as MOC
FROM myTable";
_parser = new TSql120Parser(false);
var reader = new StringReader(sql);
var statements = parser.ParseStatementList(reader, out _errors);
var firstStatement = statements[0];
var select = firstStatement as SelectStatement;
var querySpec = statement.QueryExpression as QuerySpecification;
var columns = querySpec.SelectElements;
foreach(var column in columns) {
if (column is SelectScalarExpression) {
var expression = (column as SelectScalarExpression).Expression;
if (expression is ColumnReferenceExpression) {
var columnExpression = expression as ColumnReferenceExpression;
if (identifier.Identifiers.Count == 1)
{
var columnName = identifier.Identifiers.First().Value;
// I can validate from this extracted column name
}
}
}
}
c# sql .net scriptdom
edited Nov 13 at 5:30
Nghia Bui
1,443812
asked Nov 12 at 20:16
Newton
525
add a comment |
up vote
2
down vote
favorite
I'm looking to use ScriptDom to parse T-SQL statement and ensure that at least two columns are specified. I found a way to extract the column names and can properly do my validation from that. However I also want to validate if the column was aliased and am not sure how to check that.
I want to ensure that the select statement specifies both MCA and MOC columns.
// this should pass
var sql = "SELECT MCA, MOC
FROM myTable";
// this should also pass
var sql = "SELECT some_col as MCA, some_other_col as MOC
FROM myTable";
_parser = new TSql120Parser(false);
var reader = new StringReader(sql);
var statements = parser.ParseStatementList(reader, out _errors);
var firstStatement = statements[0];
var select = firstStatement as SelectStatement;
var querySpec = statement.QueryExpression as QuerySpecification;
var columns = querySpec.SelectElements;
foreach(var column in columns) {
if (column is SelectScalarExpression) {
var expression = (column as SelectScalarExpression).Expression;
if (expression is ColumnReferenceExpression) {
var columnExpression = expression as ColumnReferenceExpression;
if (identifier.Identifiers.Count == 1)
{
var columnName = identifier.Identifiers.First().Value;
// I can validate from this extracted column name
}
}
}
}
c# sql .net scriptdom
edited Nov 13 at 5:30
Nghia Bui
1,443812
asked Nov 12 at 20:16
Newton
525
add a comment |
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I'm looking to use ScriptDom to parse T-SQL statement and ensure that at least two columns are specified. I found a way to extract the column names and can properly do my validation from that. However I also want to validate if the column was aliased and am not sure how to check that.
I want to ensure that the select statement specifies both MCA and MOC columns.
// this should pass
var sql = "SELECT MCA, MOC
FROM myTable";
// this should also pass
var sql = "SELECT some_col as MCA, some_other_col as MOC
FROM myTable";
_parser = new TSql120Parser(false);
var reader = new StringReader(sql);
var statements = parser.ParseStatementList(reader, out _errors);
var firstStatement = statements[0];
var select = firstStatement as SelectStatement;
var querySpec = statement.QueryExpression as QuerySpecification;
var columns = querySpec.SelectElements;
foreach(var column in columns) {
if (column is SelectScalarExpression) {
var expression = (column as SelectScalarExpression).Expression;
if (expression is ColumnReferenceExpression) {
var columnExpression = expression as ColumnReferenceExpression;
if (identifier.Identifiers.Count == 1)
{
var columnName = identifier.Identifiers.First().Value;
// I can validate from this extracted column name
}
}
}
}
c# sql .net scriptdom
edited Nov 13 at 5:30
Nghia Bui
1,443812
asked Nov 12 at 20:16
Newton
525
I'm looking to use ScriptDom to parse T-SQL statement and ensure that at least two columns are specified. I found a way to extract the column names and can properly do my validation from that. However I also want to validate if the column was aliased and am not sure how to check that.
I want to ensure that the select statement specifies both MCA and MOC columns.
// this should pass
var sql = "SELECT MCA, MOC
FROM myTable";
// this should also pass
var sql = "SELECT some_col as MCA, some_other_col as MOC
FROM myTable";
_parser = new TSql120Parser(false);
var reader = new StringReader(sql);
var statements = parser.ParseStatementList(reader, out _errors);
var firstStatement = statements[0];
var select = firstStatement as SelectStatement;
var querySpec = statement.QueryExpression as QuerySpecification;
var columns = querySpec.SelectElements;
foreach(var column in columns) {
if (column is SelectScalarExpression) {
var expression = (column as SelectScalarExpression).Expression;
if (expression is ColumnReferenceExpression) {
var columnExpression = expression as ColumnReferenceExpression;
if (identifier.Identifiers.Count == 1)
{
var columnName = identifier.Identifiers.First().Value;
// I can validate from this extracted column name
}
}
}
}
c# sql .net scriptdom
c# sql .net scriptdom
edited Nov 13 at 5:30
Nghia Bui
1,443812
asked Nov 12 at 20:16
Newton
525
edited Nov 13 at 5:30
Nghia Bui
1,443812
asked Nov 12 at 20:16
Newton
525
edited Nov 13 at 5:30
Nghia Bui
1,443812
edited Nov 13 at 5:30
Nghia Bui
1,443812
edited Nov 13 at 5:30
Nghia Bui
1,443812
1,443812
asked Nov 12 at 20:16
Newton
525
asked Nov 12 at 20:16
Newton
525
asked Nov 12 at 20:16
Newton
525
525
add a comment |
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53269460%2fscriptdom-parsing-of-select-with-alias%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53269460%2fscriptdom-parsing-of-select-with-alias%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
