(SQL) SQL (2007 год)

Вариации на тему Notification-сервера

Очередная новая работа началась с привычного дела - планирования системы оповещения клиентов об изменениях в рекордсетах. Такие системы нотификации мне доводилось проектировать и программировать в самых разных вариантах. Например, в 2005-м году я делал такую систему на основе собственного прокси сервера и SQL-сборки, передающей нотификации прокси-серверу, а он в свою очередь по TCP/IP на клиентов.


Но система, необходимая для этого проекта, обладала совсем другими требованиями - абсолютно не требовалась такая реактивность нотификаций, зато требовалась:

Это и определило архитектуру выполненного решения - собственно нотификации будут рассылаться по почте, используя новый компонент DBMAIL (который пришел на смену SQLMail).


Вся нотификация фактически вызывается вот из такого триггера таблицы, где ведется журналирование всех изменений рекордсетов:

00001: ALTER TRIGGER [dbo].[SendNotify]
00002:    ON  [dbo].[CIHistory]
00003:    AFTER INSERT
00004: AS 
00005: BEGIN
00006:     SET NOCOUNT ON
00007: --    IF @@ROWCOUNT=0 RETURN
00008: --  RAISERROR ('Notify', 16, 10)
00009: Declare @profile_name nvarchar(50)
00010: select  @profile_name = 'RusLan' --имя SQL-профиля для отправки почты
00011: 
00012: DECLARE @NotifyXUserID int,@UserEmail Nvarchar(100),@UserName Nvarchar(100), @Notify_NotifyName Nvarchar(100), 
00013: @Notify_Subject Nvarchar(200), @Notify_Msg Nvarchar(max), @Notify_ID int, @UserCode nvarchar(20), @UserLogin nvarchar(100), 
00014: @Customer_ID int, @CodePrefix nvarchar(20), @UserTableName nvarchar(50), @EventsTableName nvarchar(50), @CIType_ID int, 
00015: @CITypeName Nvarchar(50), @CIAttr_ID int, @CIAttr_AttrName Nvarchar(50), @CIAttr_FieldName Nvarchar(50), @LogID int,
00016: @CIAttrType_SQLTypeName Nvarchar(50), @CIHistory_ID int, @CIHistory_CIID int, @CIHistory_Description Nvarchar(100), 
00017: @CIHistory_Comment Nvarchar(max), @CIHistory_Created datetime, @CIHistory_CreatorID int, @Body nvarchar(max), @dbmail_Return int
00018: 
00019: DECLARE SUBSCRIBE CURSOR FOR 
00020: select  MailRequest.ID,UserEmail, UserName, Notify_NotifyName, Notify_Subject, Notify_Msg, Notify_ID, 
00021: UserCode, UserLogin, Customer_ID, CodePrefix, UserTableName, EventsTableName,
00022: CIType_ID, CITypeName, CIAttr_ID, CIAttr_AttrName, CIAttr_FieldName, CIAttrType_SQLTypeName , 
00023: inserted.ID int, inserted.CIID, inserted.Description, inserted.Created, inserted.CreatorID, inserted.Comment 
00024: from MailRequest join inserted ON 
00025: (MailRequest.CIType_ID=inserted.CITypeID and inserted.[Description]='Created' and MailRequest.CIAttr_ID=0) --подписка на новые записи
00026: or (MailRequest.CIType_ID=inserted.CITypeID and Left([Description],6)='Change' and MailRequest.CIAttr_AttrName = Substring([Description],10,100) ) --подписка на измененные атрибуты
00027: or (MailRequest.CIType_ID=inserted.CITypeID and Left([Description],11)='Add Comment' and MailRequest.CIAttr_ID=-1) --добавление комментария
00028: 
00029: OPEN SUBSCRIBE
00030: 
00031: FETCH NEXT FROM SUBSCRIBE
00032: INTO @NotifyXUserID, @UserEmail,@UserName, @Notify_NotifyName, @Notify_Subject, @Notify_Msg, @Notify_ID,
00033: @UserCode, @UserLogin, @Customer_ID, @CodePrefix, @UserTableName, @EventsTableName, @CIType_ID, @CITypeName, 
00034: @CIAttr_ID, @CIAttr_AttrName, @CIAttr_FieldName, @CIAttrType_SQLTypeName , @CIHistory_ID, @CIHistory_CIID, 
00035: @CIHistory_Description, @CIHistory_Created, @CIHistory_CreatorID, @CIHistory_Comment
00036: 
00037: WHILE @@FETCH_STATUS = 0
00038: BEGIN
00039: 
00040: INSERT INTO [NotifyLog]([profile_name],[UserEmail],[UserName],[NotifyName],[NotifySubject],[NotifyMsg],
00041: [NotifyXUserID],[NotifyID],[UserCode],[UserLogin],[CustomerID],[CodePrefix],[UserTableName],[EventsTableName],
00042: [CIType_ID],[CITypeName],[CIAttr_ID],[CIAttr_AttrName],[CIAttr_FieldName],[CIAttrType_SQLTypeName],[CIHistory_ID],
00043: [CIHistory_CIID],[CIHistory_Description],[CIHistory_Comment],[CIHistory_Created],[CIHistory__CreatorID])
00044: VALUES (@profile_name, @UserEmail, @UserName, @Notify_NotifyName, @Notify_Subject, @Notify_Msg, @NotifyXUserID,
00045: @Notify_ID, @UserCode, @UserLogin, @Customer_ID, @CodePrefix, @UserTableName, @EventsTableName,@CIType_ID, 
00046: @CITypeName, @CIAttr_ID, @CIAttr_AttrName, @CIAttr_FieldName, @CIAttrType_SQLTypeName, @CIHistory_ID, 
00047: @CIHistory_CIID, @CIHistory_Description, @CIHistory_Comment, @CIHistory_Created, @CIHistory_CreatorID)
00048: select @LogID=scope_identity()
00049: 
00050: select @Body=dbo.CreateMailBodyForTemplate(@UserEmail, @UserName, @Notify_NotifyName, @Notify_Subject, 
00051: @Notify_Msg, @NotifyXUserID, @Notify_ID, @UserCode, @UserLogin, @Customer_ID, @CodePrefix, @UserTableName, 
00052: @EventsTableName, @CIType_ID,@CITypeName,@CIAttr_AttrName, @CIAttr_FieldName, @CIAttrType_SQLTypeName, 
00053: @CIHistory_ID, @CIHistory_CIID, @CIHistory_Description, @CIHistory_Created, @CIHistory_CreatorID, @CIHistory_Comment)
00054: 
00055: Update NotifyLog SET BODY=@Body where i=@LogID
00056: 
00057: EXEC @dbmail_Return=msdb.dbo.sp_send_dbmail
00058:      @profile_name = @profile_name,
00059:      @recipients = @UserEmail,
00060:      @subject = @Notify_Subject,
00061:      @body = @Body
00062: 
00063: Update NotifyLog SET dbmail_Return=@dbmail_Return where i=@LogID     
00064: 
00065: FETCH NEXT FROM SUBSCRIBE
00066: INTO @NotifyXUserID, @UserEmail,@UserName, @Notify_NotifyName, @Notify_Subject, @Notify_Msg, @Notify_ID,
00067: @UserCode, @UserLogin, @Customer_ID, @CodePrefix, @UserTableName, @EventsTableName, @CIType_ID, @CITypeName, 
00068: @CIAttr_ID, @CIAttr_AttrName, @CIAttr_FieldName, @CIAttrType_SQLTypeName , @CIHistory_ID, @CIHistory_CIID, 
00069: @CIHistory_Description, @CIHistory_Created, @CIHistory_CreatorID, @CIHistory_Comment
00070: 
00071: END 
00072: CLOSE SUBSCRIBE
00073: DEALLOCATE SUBSCRIBE
00074: 
00075: 
00076: END

Как видите, тут все достаточно просто. Есть некая табличная функция MailRequest, которая возвращает результат подписки юзеров на события и строчная функция CreateMailBodyForTemplate, которая формирует тело нотификации. Есть журнал нотификации NotifyLog и стандартная системная процедура msdb.dbo.sp_send_dbmail, отсылающая нотифкации по заданному профилю. Все достаточно примитивно и было бы вообще недостойно упоминания на моем сайте, если бы не две особенности, и строчная функция CreateMailBodyForTemplate и табличная функция MailRequest выполнены мною на основе SQL'сборок.

Зачем же я отказался простых и понятных TSQL-решений и решил все это на сборках?


А вот зачем. Дело в том, что у этой системы весьма крученая структура, не совпадающая с традиционной реляционной структурой. Фактически таблицы содержат ИМЕНА других таблиц и номера строк в этих таблицах, примерно так:




Те фактически надо выполнить не просто JOIN, а как-бы динамический джоин из некоторой строки в T_DEMO_UserAccount. Причем в этой строке вьюшки имя таблицы, которую надо заджойнить - указано просто текстом. Конечно, в TSQL такое в принципе выполнить возможно только в EXEC - но как получить результат EXEC в триггере? Здесь на помощи придет только SQL-сборка:

00001: Imports System
00002: Imports System.Data
00003: Imports System.Data.SqlClient
00004: Imports System.Data.SqlTypes
00005: Imports Microsoft.SqlServer.Server
00006: 
00007: Friend Class OneRow
00008:     Public ID As Integer
00009:     Public UserID As Integer
00010:     Public UserName As String
00011:     Public UserCode As String
00012:     Public UserLogin As String
00013:     Public UserEmail As String
00014:     Public Customer_ID As Integer
00015:     Public Notify_ID As Integer
00016:     Public Notify_NotifyName As String
00017:     Public Notify_Subject As String
00018:     Public Notify_Msg As String
00019:     Public CIType_ID As Integer
00020:     Public CITypeName As String
00021:     Public EventsTableName As String
00022:     Public CodePrefix As String
00023:     Public UserTableName As String
00024:     Public CIAttr_ID As Integer
00025:     Public CIAttr_AttrName As String
00026:     Public CIAttr_FieldName As String
00027:     Public CIAttrType_SQLTypeName As String
00028:     Public MailBody As String
00029:     Public MailSended As Integer
00030:     Public MailSendedDate As DateTime
00031:     Public Sub New(ByVal DR As SqlDataReader)
00032:         If Not IsDBNull(DR(0)) Then ID = DR.GetInt32(0)
00033:         If Not IsDBNull(DR(1)) Then UserID = DR.GetInt32(1)
00034:         '
00035:         If Not IsDBNull(DR(6)) Then Customer_ID = DR.GetInt32(6)
00036:         If Not IsDBNull(DR(7)) Then Notify_ID = DR.GetInt32(7)
00037:         If Not IsDBNull(DR(8)) Then Notify_NotifyName = DR(8).ToString
00038:         If Not IsDBNull(DR(9)) Then Notify_Subject = DR(9).ToString
00039:         If Not IsDBNull(DR(10)) Then Notify_Msg = DR(10).ToString
00040:         If Not IsDBNull(DR(11)) Then CIType_ID = DR.GetInt32(11)
00041:         If Not IsDBNull(DR(12)) Then CITypeName = DR(12).ToString
00042:         If Not IsDBNull(DR(13)) Then EventsTableName = DR(13).ToString
00043:         If Not IsDBNull(DR(14)) Then CodePrefix = DR(14).ToString
00044:         If Not IsDBNull(DR(15)) Then UserTableName = DR(15).ToString
00045:         If Not IsDBNull(DR(16)) Then CIAttr_ID = DR.GetInt32(16)
00046:         If Not IsDBNull(DR(17)) Then CIAttr_AttrName = DR(17).ToString
00047:         If Not IsDBNull(DR(18)) Then CIAttr_FieldName = DR(18).ToString
00048:         If Not IsDBNull(DR(19)) Then CIAttrType_SQLTypeName = DR(19).ToString
00049:     End Sub
00050: End Class
00051: 
00052: Public NotInheritable Class UserDefinedFunctions
00053: 
00054:     <Microsoft.SqlServer.Server.SqlFunction(FillRowMethodName:="FillOneRow", Name:="JoinExtUserTableByName", _
00055:     TableDefinition:="ID int, " & _
00056:     "UserID int," & _
00057:     "UserName nvarchar(100)," & _
00058:     "UserCode Nvarchar(20)," & _
00059:     "UserLogin Nvarchar(100)," & _
00060:     "UserEmail nvarchar(100)," & _
00061:     "Customer_ID int," & _
00062:     "Notify_ID int," & _
00063:     "Notify_NotifyName nvarchar(50)," & _
00064:     "Notify_Subject nvarchar(200)," & _
00065:     "Notify_Msg Nvarchar(max)," & _
00066:     "CIType_ID int," & _
00067:     "CITypeName nvarchar(50)," & _
00068:     "EventsTableName nvarchar(50)," & _
00069:     "CodePrefix nvarchar(10)," & _
00070:     "UserTableName nvarchar(50)," & _
00071:     "CIAttr_ID int," & _
00072:     "CIAttr_AttrName nvarchar(50)," & _
00073:     "CIAttr_FieldName nvarchar(50)," & _
00074:     "CIAttrType_SQLTypeName nvarchar(50)," & _
00075:     "MailBody Nvarchar(max)," & _
00076:     "MailSended int," & _
00077:     "MailSendedDate datetime", DataAccess:=DataAccessKind.Read)> _
00078:     Public Shared Function JoinExtUserTableByName() As IEnumerable
00079:         Dim X As New Collections.Generic.List(Of OneRow)
00080:         Using CN As New SqlConnection("context connection=true")
00081:             Dim CMD As SqlCommand = CN.CreateCommand()
00082:             CMD.CommandText = "select * from dbo.NotifyRequest"
00083:             CN.Open()
00084:             Dim DR = CMD.ExecuteReader
00085:             While DR.Read
00086:                 X.Add(New OneRow(DR))
00087:             End While
00088:             DR.Close()
00089:             DR = Nothing
00090:             'с вложенными командами и ридерами не получается - There is already an open DataReader associated with this Command which must be closed first.
00091:             For each One as OneRow in X 
00092:                 CMD.CommandText = "select Name,Code,Login,Email from " & One.UserTableName & " Where ID=" & One.UserID
00093:                 DR = CMD.ExecuteReader
00094:                 If DR.Read Then
00095:                     One.UserName = DR(0).ToString
00096:                     One.UserCode = DR(1).ToString
00097:                     One.UserLogin = DR(2).ToString
00098:                     One.UserEmail = DR(3).ToString
00099:                 End If
00100:                 DR.Close()
00101:             Next
00102:             DR = Nothing
00103:             CMD = Nothing
00104:             CN.Close()
00105:             Return X
00106:         End Using
00107:     End Function
00108: 
00109:     Public Shared Sub FillOneRow(ByVal row As Object, _
00110:     <Runtime.InteropServices.Out()> ByRef ID As Integer, _
00111:     <Runtime.InteropServices.Out()> ByRef UserID As Integer, _
00112:     <Runtime.InteropServices.Out()> ByRef UserName As String, _
00113:     <Runtime.InteropServices.Out()> ByRef UserCode As String, _
00114:     <Runtime.InteropServices.Out()> ByRef UserLogin As String, _
00115:     <Runtime.InteropServices.Out()> ByRef UserEmail As String, _
00116:     <Runtime.InteropServices.Out()> ByRef Customer_ID As Integer, _
00117:     <Runtime.InteropServices.Out()> ByRef Notify_ID As Integer, _
00118:     <Runtime.InteropServices.Out()> ByRef Notify_NotifyName As String, _
00119:     <Runtime.InteropServices.Out()> ByRef Notify_Subject As String, _
00120:     <Runtime.InteropServices.Out()> ByRef Notify_Msg As String, _
00121:     <Runtime.InteropServices.Out()> ByRef CIType_ID As Integer, _
00122:     <Runtime.InteropServices.Out()> ByRef CITypeName As String, _
00123:     <Runtime.InteropServices.Out()> ByRef EventsTableName As String, _
00124:     <Runtime.InteropServices.Out()> ByRef CodePrefix As String, _
00125:     <Runtime.InteropServices.Out()> ByRef UserTableName As String, _
00126:     <Runtime.InteropServices.Out()> ByRef CIAttr_ID As Integer, _
00127:     <Runtime.InteropServices.Out()> ByRef CIAttr_AttrName As String, _
00128:     <Runtime.InteropServices.Out()> ByRef CIAttr_FieldName As String, _
00129:     <Runtime.InteropServices.Out()> ByRef CIAttrType_SQLTypeName As String, _
00130:     <Runtime.InteropServices.Out()> ByRef MailBody As String, _
00131:     <Runtime.InteropServices.Out()> ByRef MailSended As Integer, _
00132:     <Runtime.InteropServices.Out()> ByRef MailSendedDate As DateTime)
00133:         If row Is Nothing Then Exit Sub
00134:         Dim X As OneRow = CType(row, OneRow)
00135:         '
00136:         ID = X.ID
00137:         UserID = X.UserID
00138:         UserName = x.UserName
00139:         UserCode = x.UserCode
00140:         UserLogin = x.UserLogin
00141:         UserEmail = x.UserEmail
00142:         Customer_ID = X.Customer_ID
00143:         Notify_ID = X.Notify_ID
00144:         Notify_NotifyName = X.Notify_NotifyName
00145:         Notify_Subject = X.Notify_Subject
00146:         Notify_Msg = X.Notify_Msg
00147:         CIType_ID = X.CIType_ID
00148:         CITypeName = X.CITypeName
00149:         EventsTableName = X.EventsTableName
00150:         CodePrefix = X.CodePrefix
00151:         UserTableName = X.UserTableName
00152:         CIAttr_ID = X.CIAttr_ID
00153:         CIAttr_AttrName = X.CIAttr_AttrName
00154:         CIAttr_FieldName = X.CIAttr_FieldName
00155:         CIAttrType_SQLTypeName = X.CIAttrType_SQLTypeName
00156:         'MailBody = 
00157:         'MailSended = 
00158:         MailSendedDate = Now
00159:         '
00160:     End Sub
00161: 
00162: End Class
00163:

Как видите, в этой сборке я вызвал вьюшку NotifyRequest, а потом заполнил каждую строку вьюшки требуемыми данными.

Что касается непосредственно стиля программирования сборки, то как видите, я предпочитаю достаточно широкие вьюшки, ибо заузить их позже не представляет труда - ведь это самая первая девелоперская версия, которая еще буде одвергаться в процессе эксплуатации множеству и множеству модификаций. И зауживать вьюшки всегда на порядок проще чем расширять.

Обратите внимение также на ту особенность, что в FillRowMethod читать из базы не получается. Это проблема. Работать с базой можно только из основной процедуры. Если бы можно бы чиать из FillRowMethod, то вероятно TSQL был бы вообще не нужен.


Теперь посмотрим на вторую сборку, которую я сделал для этого проекта. Она требует также такого же динамического джоина (термин мой - не воспринимайте его всерьез), который принципиально невозможно реализовать на TSQL. Но на этот раз сбобрка у нас получится строчная, а не табличная:

00001: Imports System
00002: Imports System.Data
00003: Imports System.Data.SqlClient
00004: Imports System.Data.SqlTypes
00005: Imports Microsoft.SqlServer.Server
00006: 
00007: 
00008: Partial Public Class UserDefinedFunctions
00009:     <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read)> _
00010:     Public Shared Function CreateMailBodyForTemplate(ByVal UserEmail As SqlString, ByVal UserName As SqlString, _
00011:     ByVal Notify_NotifyName As SqlString, ByVal Notify_Subject As SqlString, ByVal Notify_Msg As SqlString, _
00012:     ByVal NotifyXUserID As SqlInt32, ByVal Notify_ID As SqlInt32, ByVal UserCode As SqlString, _
00013:     ByVal UserLogin As SqlString, ByVal Customer_ID As SqlInt32, ByVal CodePrefix As SqlString, _
00014:     ByVal UserTableName As SqlString, ByVal EventsTableName As SqlString, ByVal CIType_ID As SqlInt32, _
00015:     ByVal CITypeName As SqlString, ByVal CIAttr_AttrName As SqlString, ByVal CIAttr_FieldName As SqlString, _
00016:     ByVal CIAttrType_SQLTypeName As SqlString, ByVal CIHistory_ID As SqlInt32, ByVal CIHistory_CIID As SqlInt32, _
00017:     ByVal CIHistory_Description As SqlString, ByVal CIHistory_Created As SqlDateTime, _
00018:     ByVal CIHistory_CreatorID As SqlString, ByVal CIHistory_Comment As SqlString) As SqlString
00019:         Dim Str2 As New System.Text.StringBuilder
00020:         Using CN As New SqlConnection("context connection=true")
00021:             CN.Open()
00022:             Dim CMD As SqlCommand = CN.CreateCommand()
00023:             Dim Str1 As String = Notify_Msg.ToString, Pos1 As Integer, Pos2 As Integer, FieldName As String
00024:             Dim i As Integer = 1
00025:             While i < Len(Str1)
00026:                 Pos1 = InStr(i, Str1, "#", CompareMethod.Text)  'начало шаблона
00027:                 If Pos1 > 0 Then
00028:                     Str2.Append(Mid(Str1, i, Pos1 - i))
00029:                     Pos2 = InStr(Pos1 + 1, Str1, "#", CompareMethod.Text)   'конец шаблона
00030:                     If Pos2 > 0 Then
00031:                         FieldName = Mid(Str1, Pos1 + 1, Pos2 - Pos1 - 1)
00032:                         Str2.Append(GetValueForTemplate(FieldName, CN, CMD, UserEmail, UserName, _
00033:                         Notify_NotifyName, Notify_Subject, Notify_Msg, NotifyXUserID, Notify_ID, _
00034:                         UserCode, UserLogin, Customer_ID, CodePrefix, UserTableName, EventsTableName, _
00035:                         CIType_ID, CITypeName, CIAttr_AttrName, CIAttr_FieldName, CIAttrType_SQLTypeName, _
00036:                         CIHistory_ID, CIHistory_CIID, CIHistory_Description, CIHistory_Created, _
00037:                         CIHistory_CreatorID, CIHistory_Comment))
00038:                         i = Pos2 + 1
00039:                     Else
00040:                         Str2.Append(Mid(Str1, i)) 'шаблон не закрыт - докопировали до конца
00041:                         Exit While
00042:                     End If
00043:                 Else
00044:                     Str2.Append(Mid(Str1, i)) 'докопировали хвостик без шаблонов
00045:                     Exit While
00046:                 End If
00047:             End While
00048:             CMD = Nothing
00049:             CN.Close()
00050:         End Using
00051:         Return New SqlString(Str2.ToString)
00052:     End Function
00053: 
00054:     Private Shared Function GetValueForTemplate(ByVal FieldName As String, ByVal CN As SqlConnection, _
00055:     ByVal CMD As SqlCommand, ByVal UserEmail As SqlString, ByVal UserName As SqlString, _
00056:     ByVal Notify_NotifyName As SqlString, ByVal Notify_Subject As SqlString, ByVal Notify_Msg As SqlString, _
00057:     ByVal NotifyXUserID As SqlInt32, ByVal Notify_ID As SqlInt32, ByVal UserCode As SqlString, _
00058:     ByVal UserLogin As SqlString, ByVal Customer_ID As SqlInt32, ByVal CodePrefix As SqlString, _
00059:     ByVal UserTableName As SqlString, ByVal EventsTableName As SqlString, ByVal CIType_ID As SqlInt32, _
00060:     ByVal CITypeName As SqlString, ByVal CIAttr_AttrName As SqlString, ByVal CIAttr_FieldName As SqlString, _
00061:     ByVal CIAttrType_SQLTypeName As SqlString, ByVal CIHistory_ID As SqlInt32, ByVal CIHistory_CIID As SqlInt32, _
00062:     ByVal CIHistory_Description As SqlString, ByVal CIHistory_Created As SqlDateTime, _
00063:     ByVal CIHistory_CreatorID As SqlString, ByVal CIHistory_Comment As SqlString) As String
00064:         Select Case FieldName
00065:             Case "UserName": return UserName.ToString
00066:             Case "UserLogin": return UserLogin.ToString
00067:             Case "NewsMakerName":  Return [SELECT]("Name", CN, CMD,  UserTableName.ToString, CInt(CIHistory_CreatorID))
00068:             Case "NewsMakerLogin": Return [SELECT]("Login", CN, CMD,  UserTableName.ToString, CInt(CIHistory_CreatorID))
00069:             Case "Comment" : Return CIHistory_Comment.ToString
00070:             Case "Date" : Return CIHistory_Created.ToString
00071:             Case "TableName": return EventsTableName.ToString
00072:             Case "RecordNum": return  CIHistory_CIID.ToString
00073:             Case "CIAttrField": return CIAttr_FieldName.ToString
00074:             Case "CITypeName": return CITypeName.ToString
00075:             Case "CIAttrName": return CIAttr_AttrName.ToString
00076:             Case Else : Return [SELECT](FieldName, CN, CMD, EventsTableName.ToString, CInt(CIHistory_CIID))
00077:         End Select
00078:     End Function
00079: 
00080:     Private Shared Function [SELECT](ByVal FieldName As String, ByVal CN As SqlConnection, ByVal CMD As SqlCommand, ByVal EventsTableName As String, ByVal CIHistory_CIID As Integer) As String
00081:         CMD.CommandText = "select " & FieldName & " from " & EventsTableName & " where ID=" & CIHistory_CIID.ToString
00082:         Return CMD.ExecuteScalar.ToString
00083:     End Function
00084: End Class

Состоит эта сборка из строчного парсера и собственно динамического джоина нетрадиционной ориентации, смысл которого, надеюсь вы уловили. Как видите, здесь тоже все достаточно широко и с излишествами - но ведь эта система еще будет и будет модифицироваться годами. А уже буквально в момент публикации этой странички я уже расширял текст этой сборки, докручивая поддержку шаблонов в Subject уведомления. Кроме того, в процессе прогонов обнаружились некоторые погрешности, в часности Body нежелательно возвращать из сборки пустым.

В этом проекте применена достаточно нетрадиционная SQL-структура, однако в отдельных случаях она имеет смысл. Я не всегда с восторгом вопринимаю решения, принятые архитекторами пректа или моими предшественниками-программистами, но в данном случае, осознав концепцию системы, я полностью согласен с архитектором системы, что такое отклонение от традиционных реляционных норм и введение таких динамических джоинов было АБСОЛЮТНО НЕОБХОДИМО.


К сказанному остается добавить, что возможности у сборок просто замечательные (и не только в плане применимости к нетрадиционным заджойниваниям), хотя некоторых SQL-ограничений преодолеть не удасться все равно. Одна из универсальных сборок пибликуется ниже. Она позволяет получить ТОЧНЫЕ имена полей, возвращаемых процедурой и их типы. В реальных проектах, когда процедуры возврашают широченные данные из десятков полей - точные их имена, а уж тем более типа - это необычайно актуальная задача. Но IDE раскрывает результирующие поля только для вьюшек, но совершенно не раскрывает их для процедур - что как раз очень даже необходимо для безошибочного написания процедур. Этот пробел восполняет эта сборочка.




00001: Imports System
00002: Imports System.Data
00003: Imports System.Data.SqlClient
00004: Imports System.Data.SqlTypes
00005: Imports Microsoft.SqlServer.Server
00006: 
00007: Partial Public Class UserDefinedFunctions
00008:     <Microsoft.SqlServer.Server.SqlProcedure()> _
00009:     Public Shared Function GetRecordsetFormat(ByVal SQL_BATCH As String) As Integer
00010:         Using CN As New SqlConnection("context connection=true")
00011:             CN.Open()
00012:             Dim CMD As SqlCommand = CN.CreateCommand()
00013:             CMD.CommandText = "set nocount on" & vbCrLf
00014:             CMD.CommandText &= "declare @STR1 nvarchar(4000)" & vbCrLf
00015:             CMD.CommandText &= "select  @STR1='" & SQL_BATCH.ToString & "'" & vbCrLf
00016:             CMD.CommandText &= "exec (@STR1)"
00017:             SqlContext.Pipe.Send(CMD.CommandText)
00018:             Dim RDR As SqlDataReader
00019:             Try
00020:                 RDR = CMD.ExecuteReader
00021:             Catch ex As Exception
00022:                 SqlContext.Pipe.Send("поданная на вход команда некорректна" & vbcrlf & ex.Message)
00023:                 CMD = Nothing
00024:                 CN.Close()
00025:                 Return 2
00026:                 Exit Function
00027:             End Try
00028:             'поданная на вход команда корректна
00029:             If RDR.Read Then
00030:                 Dim Column1 As New Microsoft.SqlServer.Server.SqlMetaData("Name", SqlDbType.NVarChar, 50)
00031:                 Dim Column2 As New Microsoft.SqlServer.Server.SqlMetaData("Type", SqlDbType.NVarChar, 20)
00032:                 Dim Column3 As New Microsoft.SqlServer.Server.SqlMetaData("Len", SqlDbType.NVarChar, 5)
00033:                 Dim RecordInfo As New Microsoft.SqlServer.Server.SqlMetaData() {Column1, Column2, Column3}
00034:                 Dim Struct As DataTable = RDR.GetSchemaTable
00035:                 For Each Row1 As DataRow In Struct.Rows
00036:                     Dim OneRow As New SqlDataRecord(RecordInfo)
00037:                     OneRow.SetString(0, Row1(0).ToString)
00038:                     OneRow.SetString(1, Row1(24).ToString)
00039:                     OneRow.SetString(2, Row1(2).ToString)
00040:                     If Row1 Is Struct.Rows(0) Then
00041:                         SqlContext.Pipe.SendResultsStart(OneRow)
00042:                     End If
00043:                     SqlContext.Pipe.SendResultsRow(OneRow)
00044:                 Next
00045:                 SqlContext.Pipe.SendResultsEnd()
00046:                 RDR.Close()
00047:                 CMD = Nothing
00048:                 CN.Close()
00049:                 Return 0
00050:             Else
00051:                 SqlContext.Pipe.Send("процедура не выдала никакого рекордсета")
00052:                 CMD = Nothing
00053:                 CN.Close()
00054:                 Return 1
00055:                 Exit Function
00056:             End If
00057:         End Using
00058:     End Function
00059: End Class


Comments ( )
Link to this page: //www.vb-net.com/asp2/28/index.htm
< THANKS ME>