Split a stored procedure or a large batch into individual sql statements using Microsoft.Data.Schema.ScriptDom
-
Thursday, March 29, 2012 3:22 AM
What I want to do is scan all our stored procedure text and find procedures updating/deleting/inserting into a set of tables that we want to track changes of. If the procedure is not doing already find the batch
let's say the statement it something like
UPDATE SchemaA.TableA SET ColA = 5 WHERE id = 9
and change it to
UPDATE SchemaA.TableA SET ColA = 5 OUTPUT deleted.primarykeya INTO AuditSchema.AuditTableForA WHERE id = 9and so for merge, delete insert, update statements affecting some tables. I currently can token a stored procedure or any sql code using Microsoft.Data.Schema.ScriptDom library, the token is at keyword level like UPDATE or AS or Literal etc. What I could not figure out is how to split the larger sql code into smaller statements so that I can modify only that statement.
Any ideas on how to split the sql batches into individual statements ?
Thank you
private void button1_Click(object sender, EventArgs e) { string sql = File.ReadAllText(@"C:\Temp\Test.sql"); TSql100Parser mParser = new TSql100Parser(false); Sql100ScriptGenerator gen = new Sql100ScriptGenerator(); System.Collections.Generic.IList<ParseError> Errors = null; IScriptFragment result = mParser.Parse(new StringReader(sql), out Errors); System.Collections.Generic.IList<TSqlParserToken> tokens = gen.GenerateTokens((TSqlFragment)result); StringBuilder mstr = new StringBuilder(); TokenStore[] mstore = new TokenStore[3]; for (int i = 0; i < 3; i ++) mstore[i] = new TokenStore(); mstr.Append("<font face=\"Courier\">"); int iTokenCount = 0; foreach (TSqlParserToken token in tokens) { } }
Gokhan Varol
All Replies
-
Thursday, March 29, 2012 3:41 AMI noticed TSql100Parser has a ParseStatementList method but it does not work for stored procedures.
Gokhan Varol
-
Thursday, March 29, 2012 3:43 PM
Check this link this will help you
-
Thursday, March 29, 2012 4:55 PMI cannot open the link
Gokhan Varol
-
Thursday, April 05, 2012 3:01 PM
The following article deals with object definition:
http://www.sqlusa.com/bestpractices2008/objectdefinition/
The likelyhood is high what you are trying to do requires Artificial Intelligence.
Related article:
http://stackoverflow.com/questions/1779737/using-the-tsqlparser
Kalman Toth SQL SERVER 2012 & BI TRAINING
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012- Edited by Kalman TothMicrosoft Community Contributor Tuesday, October 02, 2012 8:08 PM
-
Thursday, April 05, 2012 10:47 PM
CREATE FUNCTION [dbo].[ProcessSQLforAudit] (@CreateProcSQL NVARCHAR (MAX), @DeclareParamSQL NVARCHAR (MAX)) RETURNS TABLE (AlterSQL nvarchar(MAX), ModCount int, ModSQL nvarchar(MAX), ErrorCount int, ErrorList nvarchar(max), CTECount int, CTEList nvarchar(MAX), DurationMS int) AS EXTERNAL NAME [ScriptDomUtilClr].[ScriptDomUtilClr.ProcessSQLforAuditClass].[ProcessSQLforAudit]
using System; using System.Collections; using System.Collections.Generic; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; using Microsoft.Data.Schema.ScriptDom; using Microsoft.Data.Schema.ScriptDom.Sql; using System.Text; using System.IO; namespace ScriptDomUtilClr { public class ProcessSQLforAuditClass { public class VarStruct { public Int16 ParameterId; public string ParameterName; public bool HasDefault; public string Value; public string ValueParsed; } [SqlFunction(FillRowMethodName = "FillProcessSQLforAudit", TableDefinition = "AlterSQL nvarchar(MAX), ModCount int, ModSQL nvarchar(MAX), ErrorCount int, ErrorList nvarchar(max), CTECount int, CTEList nvarchar(MAX), DurationMS int")] public static IEnumerable ProcessSQLforAudit(SqlString CreateProcSQL, SqlString DeclareParamSQL) { List<DiabloAudit.DiabloOutParam.OutputClass> mout = new List<DiabloAudit.DiabloOutParam.OutputClass>(); if (CreateProcSQL.IsNull || DeclareParamSQL.IsNull) return mout; DiabloAudit mAud = new DiabloAudit(); DiabloAudit.DiabloOutParam retval = new DiabloAudit.DiabloOutParam(CreateProcSQL.ToString(), DeclareParamSQL.ToString()); mAud.ProcessSQLforAudit(ref retval); DiabloAudit.DiabloOutParam.OutputClass moutput = null; if (retval.GetReturnParameters(ref moutput)) mout.Add(moutput); return mout; } public static void FillProcessSQLforAudit(Object obj, out SqlString AlterSQL, out SqlInt32 ModCount, out SqlString ModSQL, out SqlInt32 ErrorCount, out SqlString ErrorList, out SqlInt32 CTECount, out SqlString CTEList, out int DurationMS) { DiabloAudit.DiabloOutParam.OutputClass sobj = (DiabloAudit.DiabloOutParam.OutputClass)obj; if (string.IsNullOrEmpty(sobj.m_AlterSQL)) AlterSQL = SqlString.Null; else AlterSQL = sobj.m_AlterSQL; ModCount = sobj.m_ModCount; if (string.IsNullOrEmpty(sobj.m_ModSQL)) ModSQL = SqlString.Null; else ModSQL = sobj.m_ModSQL; ErrorCount = sobj.m_ErrorCount; if (string.IsNullOrEmpty(sobj.m_Error)) ErrorList = SqlString.Null; else ErrorList = sobj.m_Error; CTECount = sobj.m_CTECount; if (string.IsNullOrEmpty(sobj.m_CTEList)) CTEList = SqlString.Null; else CTEList = sobj.m_CTEList; DurationMS = sobj.m_DurationMS; } } }
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Linq; using System.Text; using System.IO; using Microsoft.Data.Schema.ScriptDom; using Microsoft.Data.Schema.ScriptDom.Sql; using System.Text.RegularExpressions; using System.Diagnostics; namespace ScriptDomUtilClr { public partial class DiabloAudit { static readonly Regex _SpaceBeforeNewLine = new Regex(@"[ \t]*\r\n", RegexOptions.Multiline); static readonly Regex _LeadingAndTrailingWhiteSpace = new Regex(@"^[\s]+|[\s]+$"); static readonly string NewLine = "\r\n"; public eStatementType GetStmtType(TSqlStatement stmt) { if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.UpdateStatement)) return eStatementType.sUpd; if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.DeleteStatement)) return eStatementType.sDel; if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.MergeStatement)) return eStatementType.sMrg; if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.InsertStatement)) return eStatementType.sIns; return eStatementType.sInvalid; } public int GetTokenStart(IList<TSqlParserToken> inList, int iStartToken, TSqlTokenType searchToken, string searchTokenText, bool ReverseSearch, out TSqlParserToken FoundToken) { if (ReverseSearch) { for (; iStartToken > 0; iStartToken--) { if (inList[iStartToken].TokenType == searchToken) { if (searchTokenText.Length > 0) { if (string.Compare(inList[iStartToken].Text, searchTokenText, true) != 0) continue; } FoundToken = inList[iStartToken]; return iStartToken; } } } else { for (; iStartToken < inList.Count; iStartToken++) { if (inList[iStartToken].TokenType == searchToken) { if (searchTokenText.Length > 0) { if (string.Compare(inList[iStartToken].Text, searchTokenText, true) != 0) continue; } FoundToken = inList[iStartToken]; return iStartToken; } } } FoundToken = new TSqlParserToken(); return 0; } public class ProcCodeSplitByAS { public string mProcHeader; public string mProcBody; public ProcCodeSplitByAS(string ProcHeader, string ProcBody) { mProcHeader = ProcHeader; mProcBody = ProcBody; } } public class DiabloOutParam { static string TrimMultilineLocal(string Input) { return _LeadingAndTrailingWhiteSpace.Replace(_SpaceBeforeNewLine.Replace(Input, NewLine), ""); } public bool GetProcCodeSplitByAS(string SQL, ref ProcCodeSplitByAS refProcCodeSplitByAS) { var ml_ProcCodeSplitByAS = new List<ProcCodeSplitByAS>(); bool bAlterApplied = false; string ProcHeader; string ProcBody; try { TSql100Parser mParser = new TSql100Parser(true); Sql100ScriptGenerator gen = new Sql100ScriptGenerator(); System.Collections.Generic.IList<ParseError> Errors = null; System.Collections.Generic.IList<TSqlParserToken> tokens = mParser.GetTokenStream(new StringReader(SQL), Errors); int CreateOffset = -1; foreach (TSqlParserToken token in tokens) { if (token.TokenType == TSqlTokenType.Create) { CreateOffset = token.Offset; } else if (token.TokenType == TSqlTokenType.As) { if (CreateOffset > -1) { ProcHeader = SQL.Substring(0, CreateOffset) + "ALTER" + SQL.Substring(CreateOffset + 6, token.Offset - CreateOffset - 4); bAlterApplied = true; } else ProcHeader = SQL.Substring(0, token.Offset + 2); ProcBody = TrimMultilineLocal(SQL.Substring(token.Offset + 2)); refProcCodeSplitByAS = new ProcCodeSplitByAS(ProcHeader, ProcBody); return bAlterApplied; } } } catch { } refProcCodeSplitByAS = new ProcCodeSplitByAS("", ""); return false; } private class errorClass { public string strError; public int iOffset; public int iLine; public errorClass(int Line, int Offset, string Error) { iLine = Line; iOffset = Offset; strError = Error; } } public class CTEClass { public string strName; public int strStatementOffset; public int strStatementLength; public CTEClass(string Name, int StatementOffset, int StatementLength) { strName = Name; strStatementOffset = StatementOffset; strStatementLength = StatementLength; } } public string origSQL; public string SQL() { return fakeSQL; } private string fakeSQL; public string declareParamSQL; private int OrigSQLOffsetDiff; private int OrigSQLLineDiff; private List<errorClass> Errorlist; public ProcCodeSplitByAS cProcCodeSplitByAS; public List<CTEClass> CTEList; public int CurrentStatementId; private StringBuilder NewSQL; private int ModifiedCount; public TSqlStatement PreviousStatement; public int LastStatementStringArrayLength; private int LastStmtEnd; StatementList sStatementList; Stopwatch mstopwatch; public void IncrementModifiedCount() { ModifiedCount++; } public int GetNewSQLLen() { return NewSQL.Length; } public void AppendSQL(int StartOffset, int FragmentLength, string SQL) { if (StartOffset > LastStmtEnd) NewSQL.Append(fakeSQL.Substring(LastStmtEnd, StartOffset - LastStmtEnd)); LastStmtEnd = StartOffset + FragmentLength; NewSQL.Append(SQL); } public void RemoveSQLFromEnd(int Length) { NewSQL.Remove(NewSQL.Length - Length, Length); LastStmtEnd = LastStmtEnd - Length; } public void ErrorlistAdd(int Line, int Offset, string strError) { Line = Line - OrigSQLLineDiff; Offset = Offset - OrigSQLOffsetDiff; Errorlist.Add(new errorClass(Line, Offset, strError)); } public bool SplitAtAS() { if (GetProcCodeSplitByAS(origSQL, ref cProcCodeSplitByAS)) { fakeSQL = declareParamSQL + cProcCodeSplitByAS.mProcBody; int iNewLineCount = 0; int iIndex = -1; do { if (cProcCodeSplitByAS.mProcHeader.Length > iIndex + 1) iIndex = cProcCodeSplitByAS.mProcHeader.IndexOf(NewLine, iIndex + 1); if (iIndex > -1) iNewLineCount++; } while (iIndex > 0); OrigSQLOffsetDiff = cProcCodeSplitByAS.mProcHeader.Length + 2 - declareParamSQL.Length; OrigSQLLineDiff = iNewLineCount - 2; return true; } return false; } public DiabloOutParam(string strSQL, string strdeclareParamSQL) { Reset(); declareParamSQL = strdeclareParamSQL.Replace(NewLine, " ") + NewLine; origSQL = TrimMultilineLocal(strSQL); } public void SetStatementList(ref StatementList mStatementList) { sStatementList = mStatementList; } public void Reset() { Errorlist = new List<errorClass>(); CTEList = new List<CTEClass>(); NewSQL = new StringBuilder(); cProcCodeSplitByAS = new ProcCodeSplitByAS("", ""); ModifiedCount = 0; LastStatementStringArrayLength = 0; CurrentStatementId = 0; OrigSQLOffsetDiff = 0; OrigSQLLineDiff = 0; LastStmtEnd = 0; sStatementList = null; mstopwatch = new Stopwatch(); mstopwatch.Start(); } public class OutputClass { public string m_AlterSQL; public int m_ModCount; public string m_ModSQL; public int m_ErrorCount; public string m_Error; public int m_CTECount; public string m_CTEList; public int m_DurationMS; public OutputClass(string AlterSQL, int ModCount, string ModSQL, int ErrorCount, string Error, int CTECount, string CTEList, int DurationMS) { m_AlterSQL = AlterSQL; m_ModCount = ModCount; m_ModSQL = ModSQL; m_ErrorCount = ErrorCount; m_Error = Error; m_CTECount = CTECount; m_CTEList = CTEList; m_DurationMS = DurationMS; } } public bool GetReturnParameters(ref OutputClass outClass) { if (cProcCodeSplitByAS.mProcHeader == null || cProcCodeSplitByAS.mProcBody == null) return false; string strCTEList; if (CTEList.Count > 0) { StringBuilder ctesa = new StringBuilder(); foreach (CTEClass es in CTEList) { ctesa.AppendFormat("Offset: {0}|Length: {1}|Name: {2}\r\n", es.strStatementOffset, es.strStatementLength, es.strName); } strCTEList = ctesa.ToString(); } else strCTEList = null; outClass = new OutputClass(cProcCodeSplitByAS.mProcHeader + NewLine + cProcCodeSplitByAS.mProcBody, ModifiedCount, ModifiedSQL(), Errorlist.Count, ErrorOutput(), CTEList.Count, strCTEList, (int)mstopwatch.ElapsedMilliseconds); return true; } private string ModifiedSQL() { if (ModifiedCount > 0) { if (fakeSQL.Length > LastStmtEnd) { NewSQL.Append(fakeSQL.Substring(LastStmtEnd, fakeSQL.Length - LastStmtEnd)); LastStmtEnd = fakeSQL.Length; } string strTemp = NewSQL.ToString(); return cProcCodeSplitByAS.mProcHeader + NewLine + strTemp.Substring(declareParamSQL.Length, strTemp.Length - declareParamSQL.Length); } else return (string)null; } private string ErrorOutput() { if (Errorlist != null) { StringBuilder ms = new StringBuilder(); foreach (errorClass es in Errorlist) { ms.AppendFormat("Line: {0}|Offset: {1}|Error: {2}\r\n", es.iLine, es.iOffset, es.strError); } return ms.ToString(); } return (string)null; } } public bool ProcessSQLforAudit(ref DiabloOutParam retval) { retval.Reset(); TSql100Parser mParser = new TSql100Parser(false); Sql100ScriptGenerator gen = new Sql100ScriptGenerator(); System.Collections.Generic.IList<ParseError> Errors = null; try { if (!retval.SplitAtAS()) { retval.ErrorlistAdd(0, 0, "Failed to parse stored procedure into sections"); return false; } if (retval.SQL().Length == 0) { retval.ErrorlistAdd(0, 0, "Empty SQL"); return false; } StatementList sList = mParser.ParseStatementList(new StringReader(retval.SQL()), out Errors); if (Errors != null && Errors.Count > 0) { foreach (ParseError pe in Errors) { retval.ErrorlistAdd(pe.Line, pe.Offset, pe.Message); } } if (sList == null) return false; retval.SetStatementList(ref sList); return ParseStatements(ref retval, ref sList); } catch (Exception ex) { retval.ErrorlistAdd(0, 0, ex.Message); return false; } } public bool ParseDMLStatement(ref DiabloOutParam retval, ref StatementList sList, ref TSqlStatement stmt) { if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.UpdateStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.UpdateStatement upd = (Microsoft.Data.Schema.ScriptDom.Sql.UpdateStatement)stmt; if (!CheckIfCTEExists(ref retval, stmt, upd.OutputClause)) { return ScanUpdateStatement(ref retval, ref upd, ref stmt, ref sList); } } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.MergeStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.MergeStatement mMrg = (Microsoft.Data.Schema.ScriptDom.Sql.MergeStatement)stmt; if (CheckIfCTEExists(ref retval, stmt, mMrg.OutputClause)) { } else return ScanMergeStatement(ref retval, ref mMrg, ref stmt, ref sList); } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.InsertStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.InsertStatement mIns = (Microsoft.Data.Schema.ScriptDom.Sql.InsertStatement)stmt; if (!CheckIfCTEExists(ref retval, stmt, mIns.OutputClause)) { return ScanInsertStatement(ref retval, ref mIns, ref stmt, ref sList); } } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.DeleteStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.DeleteStatement mDel = (Microsoft.Data.Schema.ScriptDom.Sql.DeleteStatement)stmt; if (!CheckIfCTEExists(ref retval, stmt, mDel.OutputClause)) { return ScanDeleteStatement(ref retval, ref mDel, ref stmt, ref sList); } } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.TruncateTableStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.TruncateTableStatement mTrn = (Microsoft.Data.Schema.ScriptDom.Sql.TruncateTableStatement)stmt; TableClass mTableClass = GetObjectStruct((mTrn.TableName.SchemaIdentifier == null ? "" : mTrn.TableName.SchemaIdentifier.Value), (mTrn.TableName.BaseIdentifier == null ? "" : mTrn.TableName.BaseIdentifier.Value), eStatementType.sUpd); if (mTableClass != null) retval.ErrorlistAdd(mTrn.StartLine, mTrn.StartOffset, string.Format("TRUNCATE TABLE {0}.{1} detected", mTrn.TableName.SchemaIdentifier.Value, mTrn.TableName.BaseIdentifier.Value)); } //else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.DropTableStatement)) //{ // Microsoft.Data.Schema.ScriptDom.Sql.DropTableStatement mTrn = (Microsoft.Data.Schema.ScriptDom.Sql.DropTableStatement)stmt; //} return false; } public bool ParseStatements(ref DiabloOutParam retval, ref StatementList sList) { bool bModified = false; for (int sind = 0; sind < sList.Statements.Count; sind++) { TSqlStatement stmt = sList.Statements[sind]; retval.CurrentStatementId++; //Console.WriteLine("----------------- Statement {0}\n{1}\n-----------------\n", retval.CurrentStatementId, retval.SQL().Substring(stmt.StartOffset, stmt.FragmentLength)); if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.UpdateStatement) || stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.MergeStatement) || stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.InsertStatement) || stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.DeleteStatement) || stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.TruncateTableStatement)) { bModified = ParseDMLStatement(ref retval, ref sList, ref stmt); } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.IfStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.IfStatement mst = (Microsoft.Data.Schema.ScriptDom.Sql.IfStatement)stmt; int i = 0; if (mst.ThenStatement != null) { TSqlStatement ts = mst.ThenStatement; if (ParseDMLStatement(ref retval, ref sList, ref ts)) i++; } if (mst.ElseStatement != null) { TSqlStatement ts = mst.ElseStatement; if (ParseDMLStatement(ref retval, ref sList, ref ts)) i++; } if (i > 0) bModified = true; } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.BeginEndBlockStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.BeginEndBlockStatement mst = (Microsoft.Data.Schema.ScriptDom.Sql.BeginEndBlockStatement)stmt; if (mst.StatementList != null) { StatementList slist = mst.StatementList; ParseStatements(ref retval, ref slist); } } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.TryCatchStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.TryCatchStatement mst = (Microsoft.Data.Schema.ScriptDom.Sql.TryCatchStatement)stmt; if (mst.TryStatements != null) { StatementList slist = mst.TryStatements; ParseStatements(ref retval, ref slist); } if (mst.CatchStatements != null) { StatementList slist = mst.CatchStatements; ParseStatements(ref retval, ref slist); } } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.CreateSchemaStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.CreateSchemaStatement mst = (Microsoft.Data.Schema.ScriptDom.Sql.CreateSchemaStatement)stmt; if (mst.StatementList != null) { StatementList slist = mst.StatementList; ParseStatements(ref retval, ref slist); } } else if (stmt.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.ProcedureStatementBodyBase)) { Microsoft.Data.Schema.ScriptDom.Sql.ProcedureStatementBodyBase mst = (Microsoft.Data.Schema.ScriptDom.Sql.ProcedureStatementBodyBase)stmt; if (mst.StatementList != null) { StatementList slist = mst.StatementList; ParseStatements(ref retval, ref slist); } } retval.LastStatementStringArrayLength = retval.GetNewSQLLen(); if (bModified) retval.IncrementModifiedCount(); retval.PreviousStatement = stmt; } return true; } public bool CheckIfCTEExists(ref DiabloOutParam retval, TSqlStatement stmt, OutputClause ocl) { if (ocl != null) return true; Microsoft.Data.Schema.ScriptDom.Sql.StatementWithCommonTableExpressionsAndXmlNamespaces cte = (Microsoft.Data.Schema.ScriptDom.Sql.StatementWithCommonTableExpressionsAndXmlNamespaces)stmt; if (cte.WithCommonTableExpressionsAndXmlNamespaces != null && cte.WithCommonTableExpressionsAndXmlNamespaces.CommonTableExpressions.Count > 0) { foreach (CommonTableExpression mcte in cte.WithCommonTableExpressionsAndXmlNamespaces.CommonTableExpressions) { retval.CTEList.Add(new DiabloOutParam.CTEClass(mcte.ExpressionName.Value, mcte.StartOffset, mcte.FragmentLength)); } return true; } return false; } public bool BuildNewStatement(ref DiabloOutParam retval, TSqlStatement stmt, string header, string AppendStr, int iStartIndex, int iEndIndex) { if (header.Length > 0) { if (retval.GetNewSQLLen() > retval.LastStatementStringArrayLength && retval.CurrentStatementId > 1 && retval.PreviousStatement.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.SetCommandStatement)) { Microsoft.Data.Schema.ScriptDom.Sql.SetCommandStatement sc = (Microsoft.Data.Schema.ScriptDom.Sql.SetCommandStatement)retval.PreviousStatement; foreach (SetCommand cmd in sc.Commands) { if (((Microsoft.Data.Schema.ScriptDom.Sql.GeneralSetCommand)cmd).CommandType == GeneralSetCommandType.ContextInfo) { retval.RemoveSQLFromEnd(retval.GetNewSQLLen() - retval.LastStatementStringArrayLength); //retval.NewSQL.Remove(retval.LastStatementStringArrayLength, retval.NewSQL.Length - retval.LastStatementStringArrayLength); break; } } } header = NewLine + header + NewLine; retval.AppendSQL(stmt.StartOffset, 0, header); } retval.AppendSQL(stmt.StartOffset, iStartIndex - stmt.StartOffset, retval.SQL().Substring(stmt.StartOffset, iStartIndex - stmt.StartOffset)); if (AppendStr.Length > 0) { retval.AppendSQL(iStartIndex, 0, AppendStr); retval.AppendSQL(iStartIndex, 0, NewLine); } retval.AppendSQL(iEndIndex, stmt.StartOffset + stmt.FragmentLength - iEndIndex, retval.SQL().Substring(iEndIndex, stmt.StartOffset + stmt.FragmentLength - iEndIndex)); return true; } public bool ScanUpdateStatement(ref DiabloOutParam retval, ref Microsoft.Data.Schema.ScriptDom.Sql.UpdateStatement mUpd, ref Microsoft.Data.Schema.ScriptDom.Sql.TSqlStatement stmt, ref StatementList sList) { int OutputStartOffset = 0; int OutputEndOffset = 0; Microsoft.Data.Schema.ScriptDom.Sql.OutputClause mOut = (Microsoft.Data.Schema.ScriptDom.Sql.OutputClause)mUpd.OutputClause; if (mOut != null) { OutputStartOffset = mOut.StartOffset; OutputEndOffset = mOut.StartOffset + mOut.FragmentLength; } else if (mUpd.SetClauses.Count > 0) { for (int i = mUpd.SetClauses[mUpd.SetClauses.Count - 1].LastTokenIndex + 1; i < mUpd.LastTokenIndex; i++) { if (sList.ScriptTokenStream[i].TokenType != TSqlTokenType.WhiteSpace) { OutputStartOffset = sList.ScriptTokenStream[i].Offset; break; } } if (OutputStartOffset == 0) OutputStartOffset = mUpd.StartOffset + mUpd.FragmentLength; OutputEndOffset = OutputStartOffset; } Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget mUpdTargt = (Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget)mUpd.Target; string strBaseIdentifier = (mUpdTargt.SchemaObject.BaseIdentifier == null ? "" : mUpdTargt.SchemaObject.BaseIdentifier.Value); //string strDatabaseIdentifier = (mUpdTargt.SchemaObject.DatabaseIdentifier == null ? "" : mUpdTargt.SchemaObject.DatabaseIdentifier.Value); string strSchemaIdentifier = (mUpdTargt.SchemaObject.SchemaIdentifier == null ? "" : mUpdTargt.SchemaObject.SchemaIdentifier.Value); //string strServerIdentifier = (mUpdTargt.SchemaObject.ServerIdentifier == null ? "" : mUpdTargt.SchemaObject.ServerIdentifier.Value); TableClass mTableClass = GetObjectStruct(strSchemaIdentifier, strBaseIdentifier, eStatementType.sUpd); if (mTableClass != null) { if (strSchemaIdentifier.Length > 0) return BuildNewStatement(ref retval, (TSqlStatement)mUpd, mTableClass.PreClause, NewLine + mTableClass.OutClause, OutputStartOffset, OutputEndOffset); } foreach (TableSource mTableSource in mUpd.FromClauses) { if (AddOutputClause(mTableSource, ref retval, eStatementType.sUpd, ref stmt, OutputStartOffset, OutputEndOffset, ref strBaseIdentifier)) return true; } return false; } public int FindLastNonWhiteNonSemiIndex(TSqlStatement mstmt) { int iLastIndex = 0; for (int il = mstmt.LastTokenIndex; il > 0 && iLastIndex == 0; il--) { switch (mstmt.ScriptTokenStream[il].TokenType) { case TSqlTokenType.Semicolon: case TSqlTokenType.WhiteSpace: break; default: iLastIndex = il; break; } } return iLastIndex; } public bool ScanInsertStatement(ref DiabloOutParam retval, ref Microsoft.Data.Schema.ScriptDom.Sql.InsertStatement mIns, ref Microsoft.Data.Schema.ScriptDom.Sql.TSqlStatement stmt, ref StatementList sList) { int OutputStartOffset = 0; int OutputEndOffset = 0; Microsoft.Data.Schema.ScriptDom.Sql.OutputClause mOut = (Microsoft.Data.Schema.ScriptDom.Sql.OutputClause)mIns.OutputClause; if (mOut != null) { OutputStartOffset = mOut.StartOffset; OutputEndOffset = mOut.StartOffset + mOut.FragmentLength; } else { OutputStartOffset = mIns.InsertSource.StartOffset; OutputEndOffset = OutputStartOffset; } Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget mInsTargt = (Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget)mIns.Target; string strBaseIdentifier = (mInsTargt.SchemaObject.BaseIdentifier == null ? "" : mInsTargt.SchemaObject.BaseIdentifier.Value); //string strDatabaseIdentifier = (mInsTargt.SchemaObject.DatabaseIdentifier == null ? "" : mInsTargt.SchemaObject.DatabaseIdentifier.Value); string strSchemaIdentifier = (mInsTargt.SchemaObject.SchemaIdentifier == null ? "" : mInsTargt.SchemaObject.SchemaIdentifier.Value); //string strServerIdentifier = (mInsTargt.SchemaObject.ServerIdentifier == null ? "" : mInsTargt.SchemaObject.ServerIdentifier.Value); TableClass mTableClass = GetObjectStruct(strSchemaIdentifier, strBaseIdentifier, eStatementType.sUpd); if (mTableClass != null) { if (strSchemaIdentifier.Length > 0) return BuildNewStatement(ref retval, (TSqlStatement)mIns, mTableClass.PreClause, mTableClass.OutClause, OutputStartOffset, OutputEndOffset); } return false; } public bool ScanDeleteStatement(ref DiabloOutParam retval, ref Microsoft.Data.Schema.ScriptDom.Sql.DeleteStatement mDel, ref Microsoft.Data.Schema.ScriptDom.Sql.TSqlStatement stmt, ref StatementList sList) { int OutputStartOffset = 0; int OutputEndOffset = 0; Microsoft.Data.Schema.ScriptDom.Sql.OutputClause mOut = (Microsoft.Data.Schema.ScriptDom.Sql.OutputClause)mDel.OutputClause; if (mOut == null) { if (mDel.FromClauses.Count > 0) { TSqlParserToken FoundToken; int iFrom = GetTokenStart(sList.ScriptTokenStream, mDel.FromClauses[0].FirstTokenIndex, TSqlTokenType.From, "", true, out FoundToken); OutputStartOffset = sList.ScriptTokenStream[iFrom].Offset; } else if (mDel.WhereClause != null) { TSqlParserToken FoundToken; int iFrom = GetTokenStart(sList.ScriptTokenStream, mDel.WhereClause.FirstTokenIndex, TSqlTokenType.Where, "", true, out FoundToken); OutputStartOffset = sList.ScriptTokenStream[iFrom].Offset; } else { int iLastIndex = FindLastNonWhiteNonSemiIndex(mDel); OutputStartOffset = mDel.ScriptTokenStream[iLastIndex].Offset + mDel.ScriptTokenStream[iLastIndex].Text.Length; } OutputEndOffset = OutputStartOffset; } else { OutputStartOffset = mOut.StartOffset; OutputEndOffset = mOut.StartOffset + mOut.FragmentLength; } Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget mDelTargt = (Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget)mDel.Target; string strBaseIdentifier = (mDelTargt.SchemaObject.BaseIdentifier == null ? "" : mDelTargt.SchemaObject.BaseIdentifier.Value); //string strDatabaseIdentifier = (mDelTargt.SchemaObject.DatabaseIdentifier == null ? "" : mDelTargt.SchemaObject.DatabaseIdentifier.Value); string strSchemaIdentifier = (mDelTargt.SchemaObject.SchemaIdentifier == null ? "" : mDelTargt.SchemaObject.SchemaIdentifier.Value); //string strServerIdentifier = (mDelTargt.SchemaObject.ServerIdentifier == null ? "" : mDelTargt.SchemaObject.ServerIdentifier.Value); TableClass mTableClass = GetObjectStruct(strSchemaIdentifier, strBaseIdentifier, eStatementType.sUpd); if (mTableClass != null) { if (strSchemaIdentifier.Length > 0) return BuildNewStatement(ref retval, (TSqlStatement)mDel, mTableClass.PreClause, NewLine + mTableClass.OutClause, OutputStartOffset, OutputEndOffset); } foreach (TableSource mTableSource in mDel.FromClauses) { if (AddOutputClause(mTableSource, ref retval, eStatementType.sUpd, ref stmt, OutputStartOffset, OutputEndOffset, ref strBaseIdentifier)) return true; } return false; } public bool ScanMergeStatement(ref DiabloOutParam retval, ref Microsoft.Data.Schema.ScriptDom.Sql.MergeStatement mMrg, ref Microsoft.Data.Schema.ScriptDom.Sql.TSqlStatement stmt, ref StatementList sList) { int OutputStartOffset = 0; int OutputEndOffset = 0; Microsoft.Data.Schema.ScriptDom.Sql.OutputClause mOut = (Microsoft.Data.Schema.ScriptDom.Sql.OutputClause)mMrg.OutputClause; if (mOut != null) { OutputStartOffset = mOut.StartOffset; OutputEndOffset = mOut.StartOffset + mOut.FragmentLength; } else { int iLastIndex = FindLastNonWhiteNonSemiIndex(mMrg); OutputStartOffset = mMrg.ScriptTokenStream[iLastIndex].Offset + mMrg.ScriptTokenStream[iLastIndex].Text.Length; OutputEndOffset = OutputStartOffset; } Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget mMrgTargt = (Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectDataModificationTarget)mMrg.Target; string strBaseIdentifier = (mMrgTargt.SchemaObject.BaseIdentifier == null ? "" : mMrgTargt.SchemaObject.BaseIdentifier.Value); //string strDatabaseIdentifier = (mMrgTargt.SchemaObject.DatabaseIdentifier == null ? "" : mMrgTargt.SchemaObject.DatabaseIdentifier.Value); string strSchemaIdentifier = (mMrgTargt.SchemaObject.SchemaIdentifier == null ? "" : mMrgTargt.SchemaObject.SchemaIdentifier.Value); //string strServerIdentifier = (mMrgTargt.SchemaObject.ServerIdentifier == null ? "" : mMrgTargt.SchemaObject.ServerIdentifier.Value); TableClass mTableClass = GetObjectStruct(strSchemaIdentifier, strBaseIdentifier, eStatementType.sUpd); if (mTableClass != null) { if (strSchemaIdentifier.Length > 0) return BuildNewStatement(ref retval, (TSqlStatement)mMrg, mTableClass.PreClause, NewLine + mTableClass.OutClause, OutputStartOffset, OutputEndOffset); } return false; } public bool AddOutputClause(TableSource iTableSource, ref DiabloOutParam retval, eStatementType StatementType, ref TSqlStatement stmt, int OutputStartOffset, int OutputEndOffset, ref string UpdatedAlias) { /* SET NOCOUNT ON declare @sql varchar(max) = ' Microsoft.Data.Schema.ScriptDom.Sql.AdhocTableSource Microsoft.Data.Schema.ScriptDom.Sql.BuiltInFunctionTableSource Microsoft.Data.Schema.ScriptDom.Sql.FullTextTableSource Microsoft.Data.Schema.ScriptDom.Sql.InternalOpenRowset Microsoft.Data.Schema.ScriptDom.Sql.JoinParenthesis Microsoft.Data.Schema.ScriptDom.Sql.OdbcQualifiedJoin Microsoft.Data.Schema.ScriptDom.Sql.OpenQueryTableSource Microsoft.Data.Schema.ScriptDom.Sql.OpenRowsetTableSource Microsoft.Data.Schema.ScriptDom.Sql.OpenXmlTableSource Microsoft.Data.Schema.ScriptDom.Sql.PivotedTableSource Microsoft.Data.Schema.ScriptDom.Sql.QualifiedJoin Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectTableSource Microsoft.Data.Schema.ScriptDom.Sql.TableSourceWithAlias Microsoft.Data.Schema.ScriptDom.Sql.TableSourceWithAliasAndColumns Microsoft.Data.Schema.ScriptDom.Sql.UnpivotedTableSource Microsoft.Data.Schema.ScriptDom.Sql.UnqualifiedJoin ' SELECT ' ' + CASE WHEN ROW = 1 THEN '' ELSE 'else ' END + 'if(iTableSource.GetType() == typeof(' + CLASSNAME + ')) { ' + CLASSNAME + ' m' + SMALLNAME + ' = (' + CLASSNAME + ') iTableSource; }' FROM (SELECT LTRIM(RTRIM(FIELD)) AS CLASSNAME, B.SMALLNAME, ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS ROW FROM Util.DBO.PARSEDELIMITED(@SQL, ' ') A CROSS APPLY (SELECT TOP 1 FIELD AS SMALLNAME FROM UTIL.DBO.PARSEDELIMITED(FIELD, '.') WHERE FIELD <> '' ORDER BY FIELDNUM DESC) B WHERE LTRIM(RTRIM(FIELD)) <> '') K */ Console.WriteLine("------------- TableSource {0}\n{1}\n---------------------", iTableSource.GetType(), retval.SQL().Substring(iTableSource.StartOffset, iTableSource.FragmentLength)); if (iTableSource.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.JoinParenthesis)) { Microsoft.Data.Schema.ScriptDom.Sql.JoinParenthesis mJoinParenthesis = (Microsoft.Data.Schema.ScriptDom.Sql.JoinParenthesis)iTableSource; return AddOutputClause(mJoinParenthesis.Join, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias); } else if (iTableSource.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.OdbcQualifiedJoin)) { Microsoft.Data.Schema.ScriptDom.Sql.OdbcQualifiedJoin mOdbcQualifiedJoin = (Microsoft.Data.Schema.ScriptDom.Sql.OdbcQualifiedJoin)iTableSource; return AddOutputClause(mOdbcQualifiedJoin.TableSource, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias); } else if (iTableSource.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.PivotedTableSource)) { Microsoft.Data.Schema.ScriptDom.Sql.PivotedTableSource mPivotedTableSource = (Microsoft.Data.Schema.ScriptDom.Sql.PivotedTableSource)iTableSource; return AddOutputClause(mPivotedTableSource.TableSource, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias); } else if (iTableSource.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.QualifiedJoin)) { Microsoft.Data.Schema.ScriptDom.Sql.QualifiedJoin mQualifiedJoin = (Microsoft.Data.Schema.ScriptDom.Sql.QualifiedJoin)iTableSource; if (!AddOutputClause(mQualifiedJoin.FirstTableSource, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias)) return AddOutputClause(mQualifiedJoin.SecondTableSource, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias); else return true; } else if (iTableSource.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectTableSource)) { Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectTableSource mSchemaObjectTableSource = (Microsoft.Data.Schema.ScriptDom.Sql.SchemaObjectTableSource)iTableSource; string strAlias = (mSchemaObjectTableSource.Alias == null ? "" : mSchemaObjectTableSource.Alias.Value); string strBaseIdentifier = (mSchemaObjectTableSource.SchemaObject.BaseIdentifier == null ? "" : mSchemaObjectTableSource.SchemaObject.BaseIdentifier.Value); //strDatabaseIdentifier = (Src.SchemaObject.DatabaseIdentifier == null ? "" : Src.SchemaObject.DatabaseIdentifier.Value); string strSchemaIdentifier = (mSchemaObjectTableSource.SchemaObject.SchemaIdentifier == null ? "" : mSchemaObjectTableSource.SchemaObject.SchemaIdentifier.Value); //strServerIdentifier = (Src.SchemaObject.ServerIdentifier == null ? "" : Src.SchemaObject.ServerIdentifier.Value); if (string.Compare(strAlias, UpdatedAlias, true) == 0) { TableClass mTableClass = GetObjectStruct(strSchemaIdentifier, strBaseIdentifier, StatementType); if (mTableClass != null) return BuildNewStatement(ref retval, stmt, mTableClass.PreClause, mTableClass.OutClause, OutputStartOffset, OutputEndOffset); } return false; } else if (iTableSource.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.UnpivotedTableSource)) { Microsoft.Data.Schema.ScriptDom.Sql.UnpivotedTableSource mUnpivotedTableSource = (Microsoft.Data.Schema.ScriptDom.Sql.UnpivotedTableSource)iTableSource; return AddOutputClause(mUnpivotedTableSource.TableSource, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias); } else if (iTableSource.GetType() == typeof(Microsoft.Data.Schema.ScriptDom.Sql.UnqualifiedJoin)) { Microsoft.Data.Schema.ScriptDom.Sql.UnqualifiedJoin mUnqualifiedJoin = (Microsoft.Data.Schema.ScriptDom.Sql.UnqualifiedJoin)iTableSource; if (!AddOutputClause(mUnqualifiedJoin.FirstTableSource, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias)) return AddOutputClause(mUnqualifiedJoin.SecondTableSource, ref retval, StatementType, ref stmt, OutputStartOffset, OutputEndOffset, ref UpdatedAlias); else return true; } return false; } } }
Gokhan Varol
-
Thursday, April 05, 2012 11:03 PM
I pasted here a clr function that you can pass the stored procedure text from below query and the clr does the magic of injecting output to the queries that modify the desired tables. I also used context_info variable so that before the dml statement I modified i modify context_info and all the tables have triggers which will rollback the transaction if the context_info does not match and if it does match they clear the context info so that the context info will only let the process allow a single update or delete or merge or insert transaction, to make sure there is no queries that does not insert into our audit tables via output can run. Parsing complex cte's is difficult for me for now, for now I returned a unparsable cte count out as a variable and I go through that code manually.
From the C# code you will notice I am not a C# programmer but this code works for us like a charm :-)
; WITH params AS ( SELECT object_id, p.parameter_id, p.NAME AS ParameterName, CAST(CASE WHEN t.NAME = 'timestamp' THEN 'rowversion' WHEN t.NAME IN ('char', 'varchar') THEN t.NAME + '(' + CASE WHEN p.max_length = - 1 THEN 'MAX' ELSE CAST(p.max_length AS VARCHAR) END + ')' WHEN t.NAME IN ('nchar', 'nvarchar') THEN t.NAME + '(' + CASE WHEN p.max_length = - 1 THEN 'MAX' ELSE CAST(p.max_length / 2 AS VARCHAR) END + ')' WHEN t.NAME IN ('binary', 'varbinary') THEN t.NAME + '(' + CASE WHEN p.max_length = - 1 THEN 'MAX' ELSE CAST(p.max_length AS VARCHAR) END + ')' WHEN t.NAME IN ('bigint', 'int', 'smallint', 'tinyint') THEN t.NAME WHEN t.NAME IN ('datetime2', 'time', 'datetimeoffset') THEN t.NAME + '(' + CAST(p.scale AS VARCHAR) + ')' WHEN t.NAME IN ('numeric', 'decimal') THEN t.NAME + '(' + CAST(p.precision AS VARCHAR) + ', ' + CAST(p.scale AS VARCHAR) + ')' ELSE t.NAME END AS VARCHAR(256)) AS ParameterType FROM sys.parameters p INNER JOIN sys.types t ON p.user_type_id = t.user_type_id ) SELECT o.object_id, s.NAME AS SchemaName, o.NAME AS ObjectName, m.DEFINITION, 'DECLARE ' + ISNULL(substring(ParamList, 3, len(ParamList)), '@xoxoxoxo INT') FROM sys.objects o INNER JOIN sys.schemas s ON s.schema_id = o.schema_Id INNER JOIN sys.sql_modules m ON m.object_id = o.object_id CROSS APPLY ( SELECT ( SELECT ', ' + ParameterName + ' ' + ParameterType FROM params WHERE object_id = o.object_id FOR XML PATH('') ) AS ParamList ) pl WHERE o.type = 'P' AND (charindex('UPDATE', m.DEFINITION) > 0 OR charindex('INSERT', m.DEFINITION) > 0 OR charindex('DELETE', m.DEFINITION) > 0 OR charindex('MERGE', m.DEFINITION) > 0)
using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Linq; using System.Text; using System.IO; using Microsoft.Data.Schema.ScriptDom; using Microsoft.Data.Schema.ScriptDom.Sql; namespace ScriptDomUtilClr { public partial class DiabloAudit { public class TableClass { public string PreClause { get; set; } public string PreCompare { get; set; } public string OutClause { get; set; } public string OutCompare { get; set; } public bool IsTable { get; set; } } public enum eStatementType { sDel, sUpd, sMrg, sIns, sInvalid }; public TableClass GetObjectStruct(string SchemaName, string ObjectName, eStatementType StatementType) { switch (SchemaName.ToUpper() + "." + ObjectName.ToUpper()) { case "TAX.VBUILDING": { switch (StatementType) { case eStatementType.sDel: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3334442C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3334442C", OutClause = @"OUTPUT 34, 'D', deleted.CntyCd, deleted.PclId, deleted.PclSeqNbr, deleted.BldgSeqNbr, deleted.UpdateTimestamp INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr, UpdateTimestamp)", OutCompare = @"OUTPUT34,'D',deleted.CntyCd,deleted.PclId,deleted.PclSeqNbr,deleted.BldgSeqNbr,deleted.UpdateTimestampINTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr,UpdateTimestamp)", IsTable = false }; case eStatementType.sUpd: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3334552C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3334552C", OutClause = @"OUTPUT 34, 'U', inserted.CntyCd, inserted.PclId, inserted.PclSeqNbr, inserted.BldgSeqNbr INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr)", OutCompare = @"OUTPUT34,'U',inserted.CntyCd,inserted.PclId,inserted.PclSeqNbr,inserted.BldgSeqNbrINTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr)", IsTable = false }; case eStatementType.sIns: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3334492C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3334492C", OutClause = @"OUTPUT 34, 'I', inserted.CntyCd, inserted.PclId, inserted.PclSeqNbr, inserted.BldgSeqNbr INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr)", OutCompare = @"OUTPUT34,'I',inserted.CntyCd,inserted.PclId,inserted.PclSeqNbr,inserted.BldgSeqNbrINTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr)", IsTable = false }; case eStatementType.sMrg: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3334552C2C3334492C2C3334442C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3334552C2C3334492C2C3334442C", OutClause = @"OUTPUT 34, LEFT($action,1), ISNULL(inserted.CntyCd, deleted.CntyCd), ISNULL(inserted.PclId, deleted.PclId), ISNULL(inserted.PclSeqNbr, deleted.PclSeqNbr), ISNULL(inserted.BldgSeqNbr, deleted.BldgSeqNbr), CASE WHEN $action = 'DELETE' THEN deleted.UpdateTimestamp END INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr, UpdateTimestamp)", OutCompare = @"OUTPUT34,LEFT($action,1),ISNULL(inserted.CntyCd,deleted.CntyCd),ISNULL(inserted.PclId,deleted.PclId),ISNULL(inserted.PclSeqNbr,deleted.PclSeqNbr),ISNULL(inserted.BldgSeqNbr,deleted.BldgSeqNbr),CASEWHEN$action='DELETE'THENdeleted.UpdateTimestampENDINTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr,UpdateTimestamp)", IsTable = false }; } } break; case "TAX.VBUILDINGAMENITY": { switch (StatementType) { case eStatementType.sDel: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3335442C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3335442C", OutClause = @"OUTPUT 35, 'U', deleted.CntyCd, deleted.PclId, deleted.PclSeqNbr, deleted.BldgSeqNbr, deleted.UpdateTimestamp INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr, UpdateTimestamp)", OutCompare = @"OUTPUT35,'U',deleted.CntyCd,deleted.PclId,deleted.PclSeqNbr,deleted.BldgSeqNbr,deleted.UpdateTimestampINTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr,UpdateTimestamp)", IsTable = false }; case eStatementType.sUpd: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3335552C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3335552C", OutClause = @"OUTPUT 35, 'U', inserted.CntyCd, inserted.PclId, inserted.PclSeqNbr, inserted.BldgSeqNbr INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr)", OutCompare = @"OUTPUT35,'U',inserted.CntyCd,inserted.PclId,inserted.PclSeqNbr,inserted.BldgSeqNbrINTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr)", IsTable = false }; case eStatementType.sIns: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3335492C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3335492C", OutClause = @"OUTPUT 35, 'U', inserted.CntyCd, inserted.PclId, inserted.PclSeqNbr, inserted.BldgSeqNbr INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr)", OutCompare = @"OUTPUT35,'U',inserted.CntyCd,inserted.PclId,inserted.PclSeqNbr,inserted.BldgSeqNbrINTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr)", IsTable = false }; case eStatementType.sMrg: return new TableClass { PreClause = @"SET CONTEXT_INFO /*Audit Prepare !!! DO NOT COPY OR MODIFY !!!*/ 0x2C3335552C2C3335492C2C3335442C", PreCompare = @"SETCONTEXT_INFO/*AuditPrepare!!!DONOTCOPYORMODIFY!!!*/0x2C3335552C2C3335492C2C3335442C", OutClause = @"OUTPUT 35, 'U', ISNULL(inserted.CntyCd, deleted.CntyCd), ISNULL(inserted.PclId, deleted.PclId), ISNULL(inserted.PclSeqNbr, deleted.PclSeqNbr), ISNULL(inserted.BldgSeqNbr, deleted.BldgSeqNbr), ISNULL(inserted.UpdateTimestamp, deleted.UpdateTimestamp) INTO tAudit.Building(TableId, DMLType, CntyCd, PclId, PclSeqNbr, BldgSeqNbr, UpdateTimestamp)", OutCompare = @"OUTPUT35,'U',ISNULL(inserted.CntyCd,deleted.CntyCd),ISNULL(inserted.PclId,deleted.PclId),ISNULL(inserted.PclSeqNbr,deleted.PclSeqNbr),ISNULL(inserted.BldgSeqNbr,deleted.BldgSeqNbr),ISNULL(inserted.UpdateTimestamp,deleted.UpdateTimestamp)INTOtAudit.Building(TableId,DMLType,CntyCd,PclId,PclSeqNbr,BldgSeqNbr,UpdateTimestamp)", IsTable = false }; } } break; /// etc etc etc etc, in our case there are over 150 cases here for our tables } return (TableClass)null; } } }
Gokhan Varol
