Using SMO and ASP.NET, I am trying to add Highlighting and line formatting to the script method of SMO objects ( table,view,etc..). Currently I am using the String.Replace method to match the Keywords, but that doesn't seem to catch all the Keywords. I have the Keywords in an Array, and then loop thru to match against the TSQL script. I am not sure of the characters that I need to match to get all the Keywords and to get the Line formatting that resembles SQL Manager.
Dim searchArr As Array = {"ADD", "EXTERNAL", "PROCEDURE", "ALL", "FETCH", "PUBLIC", "ALTER", "FILE", "RAISERROR", "AND", "FILLFACTOR", "READ", "ANY", "FOR", "READTEXT", "AS", "FOREIGN", "RECONFIGURE",
"ASC", "FREETEXT", "REFERENCES", "AUTHORIZATION", "FREETEXTTABLE", "REPLICATION", "BACKUP", "FROM", "RESTORE", "BEGIN", "FULL", "RESTRICT", "BETWEEN", "FUNCTION", "RETURN",
"BREAK", "GOTO", "REVERT", "BROWSE", "GRANT", "REVOKE", "BULK", "GROUP", "RIGHT", "BY", "HAVING", "ROLLBACK", "CASCADE", "HOLDLOCK", "ROWCOUNT", "CASE", "IDENTITY", "ROWGUIDCOL",
"CHECK", "IDENTITY_INSERT", "RULE", "CHECKPOINT", "IDENTITYCOL", "SAVE", "CLOSE", "IF", "SCHEMA", "CLUSTERED", "IN", "SECURITYAUDIT", "COALESCE", "INDEX", "SELECT",
"COLLATE", "INNER", "SEMANTICKEYPHRASETABLE", "COLUMN", "INSERT", "SEMANTICSIMILARITYDETAILSTABLE", "COMMIT", "INTERSECT", "SEMANTICSIMILARITYTABLE",
"COMPUTE", "INTO", "SESSION_USER", "CONSTRAINT", "IS", "SET", "CONTAINS", "JOIN", "SETUSER", "CONTAINSTABLE", "KEY", "SHUTDOWN", "CONTINUE", "KILL", "SOME",
"CONVERT", "LEFT", "STATISTICS", "CREATE", "LIKE", "SYSTEM_USER", "CROSS", "LINENO", "TABLE", "CURRENT", "LOAD", "TABLESAMPLE", "CURRENT_DATE", "MERGE", "TEXTSIZE",
"CURRENT_TIME", "NATIONAL", "THEN", "CURRENT_TIMESTAMP", "NOCHECK", "TO", "CURRENT_USER", "NONCLUSTERED", "TOP", "CURSOR", "NOT", "TRAN", "DATABASE", "NULL", "TRANSACTION",
"DBCC", "NULLIF", "TRIGGER", "DEALLOCATE", "OF", "TRUNCATE", "DECLARE", "OFF", "TRY_CONVERT", "DEFAULT", "OFFSETS", "TSEQUAL", "DELETE", "ON", "UNION", "DENY", "OPEN", "UNIQUE",
"DESC", "OPENDATASOURCE", "UNPIVOT", "DISK", "OPENQUERY", "UPDATE", "DISTINCT", "OPENROWSET", "UPDATETEXT", "DISTRIBUTED", "OPENXML", "USE", "DOUBLE", "OPTION", "USER",
"DROP", "Or", "VALUES", "DUMP", "ORDER", "VARYING", "ELSE", "OUTER", "VIEW", "END", "OVER", "WAITFOR", "ERRLVL", "PERCENT", "WHEN", "ESCAPE", "PIVOT", "WHERE", "EXCEPT", "PLAN", "WHILE",
"EXEC", "PRECISION", "WITH", "EXECUTE", "PRIMARY", "WITHIN GROUP", "EXISTS", "PRINT", "WRITETEXT", "EXIT", "PROC", "CREATE PROCEDURE", "NOCOUNT", "COUNT"}
Dim srv As Server
Dim name As String
Dim db As Database
Dim sp As StoredProcedure
Private Sub Admin_Props_Load(sender As Object, e As EventArgs) Handles Me.Load
openDB()
db = srv.Databases(databaseName)
If Len(Request.QueryString("name")) = 0 Then
lblData.Text = "Not a valid StoredProcedure"
Else
sp = db.StoredProcedures(Request.QueryString("name").ToString)
lblData.Text = addHighlight(sp.TextBody)
End If
End Sub
Sub openDB()
Dim sqlConn As New SqlConnection("Data Source=xxxx;Integrated Security=True;")
Dim SerCon As New Microsoft.SqlServer.Management.Common.ServerConnection(sqlConn)
srv = New Server(SerCon)
End Sub
Function addHighlight(ByVal strIn As String) As String
Dim keyWords As Array = searchArr
Dim keyInd As Integer
Dim str2() As String
str2 = strIn.Split(" ")
If Len(Trim(strIn)) > 0 And IsArray(keyWords) Then
For keyInd = LBound(keyWords) To UBound(keyWords)
For i As Integer = LBound(str2) To UBound(str2)
str2(i) = Replace(str2(i), UCase(keyWords(keyInd)), "*|*" & UCase(keyWords(keyInd)) & "*||*", 1, -1, 1)
'strIn = Replace(strIn, UCase(keyWords(keyInd)), "*|*" & UCase(keyWords(keyInd)) & "*||*", 1, -1, 1)
Next
Next
End If
strIn = Join(str2, " ")
strIn = Replace(strIn, Chr(13), "<br>", 1, -1, 1)
strIn = Replace(strIn, "*|*", "<span class=""Highlight"">")
strIn = Replace(strIn, "*||*", "</span>")
addHighlight = strIn
End Function
When I run this I get mixed results with the color formatting, as the replace will not only get the whole words, but also the substrings of bigger words; which should not happen. The results should end up like this;
CREATE PROCEDURE AddCityToList
-- Add the parameters for the stored procedure here
@idCity int,
@idProduct int
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
INSERT INTO CityProductExclude (idCity,idProduct)
VALUES (@idCity,@idProduct)
END
The keywords not in remarks should be highlighted and capitalized, how ever I get the following back;
-- =============================================
-- AuthOR: OR,,Name>
-- CREATE date: <CREATE Date,,>
-- DESCriptiON: <DESCriptiON,,>
-- =============================================
CREATE PROCEDURE ADDCityTOLISt
-- ADD the parameters FOR the sTOred PROCEDURE here
@idCity INt,
@idProduct INt
AS
BEGIN
-- SET NOCOUNT ON ADDed TO prevent extra result SETs FROM
-- INterferINg WITH SELECT statements.
SET NOCOUNT ON;
-- INsert statements FOR PROCEDURE here
INSERT INTO CityProductExclude (idCity,idProduct)
VALUES (@idCity,@idProduct)
END
What am I missing? Any help will be much appreciated
Consider sp.TextBody is a long string containing stored procedure commands with newlines, splitting by whitespaces possibly isn't a good way to distinguish SQL keywords and comment parts there, hence better to split based from newline occurrences:
str2 = strIn.Split(New String() { Environment.NewLine }, StringSplitOptions.None)
By using assignment above, it is able to detect any strings begin with -- as SQL comments and leaving them intact, so that using Regex.Replace method can uppercase any exact SQL keywords which not marked as comment:
' Notes:
' (1) Using System.Text.RegularExpressions required to call Regex.Replace
' (2) StartsWith used to detect comment section in stored procedure body
' (3) "\b" + keyWords(keyInd) + "\b" used to replace by whole keywords only
If Len(Trim(strIn)) > 0 And IsArray(keyWords) Then
For keyInd = LBound(keyWords) To UBound(keyWords)
For i As Integer = LBound(str2) To UBound(str2)
If Not str2(i).StartsWith("--")
str2(i) = Regex.Replace(str2(i), "\b" + keyWords(keyInd) + "\b", "*|*" & UCase(keyWords(keyInd)) & "*||*", RegexOptions.IgnoreCase)
End If
Next
Next
End If
Then, putting all together in addHighlight function:
Function addHighlight(ByVal strIn As String) As String
Dim keyWords As Array = searchArr
Dim keyInd As Integer
Dim str2() As String
str2 = strIn.Split(New String() { Environment.NewLine }, StringSplitOptions.None)
If Len(Trim(strIn)) > 0 And IsArray(keyWords) Then
For keyInd = LBound(keyWords) To UBound(keyWords)
For i As Integer = LBound(str2) To UBound(str2)
If Not str2(i).StartsWith("--")
str2(i) = Regex.Replace(str2(i), "\b" + keyWords(keyInd) + "\b", "*|*" & UCase(keyWords(keyInd)) & "*||*", RegexOptions.IgnoreCase)
End If
Next
Next
End If
strIn = Join(str2, Environment.NewLine)
strIn = Replace(strIn, Chr(13), "<br />", 1, -1, 1)
strIn = Replace(strIn, "*|*", "<span class=""Highlight"">")
strIn = Replace(strIn, "*||*", "</span>")
addHighlight = strIn
End Function
NB: To make this trick work, set strIn content to include newlines before comment signs like this (only used if T-SQL string doesn't contain newlines before comments):
If Not strIn.Contains(Environment.NewLine) Then
strIn = Replace(strIn, "--", Environment.NewLine & "--")
End If
Demo: .NET Fiddle Example
Related: