Alternative to EXOLEDB provider?

6 Jul 2005 4:55 PM

Ken Wigle

All,

I am in dire need of a solution. I used to have my .net application on my
exchange server but have since moved it to a separate server (which is
running SQL 2000). The application basically grabs leave form requests and
stores them on the sql server. It uses Exchange 2003 to route e-mails and to
store the time off on a public calendar. Everything still works fine except
for the public calendar. As I found via google, you cannot use exoledb as
your provider unless the code is on the exchange server. Does anyone have an
alternative (maybe with WebDAV) to the small piece of code below that does
not use exoledb?

---
    ' Reference to Microsoft ActiveX Data Objects 2.5 Library
    ' Reference to Microsoft CDO for Exchange 2000 Library
    ' Reference to Active DS Type Library
    Function CreateAppointment(ByVal StartDate As Date, _
                               ByVal EndDate As Date, ByVal stime As String, _
                               ByVal etime As String, ByVal Subject As
String, _
                               ByVal Location As String, _
                               ByVal TextBody As String) As CDO.Appointment
        Try

            Dim iAppt As New CDO.Appointment
            Dim Conn As New ADODB.Connection
            Conn.Provider = "ExOLEDB.DataSource"

            With iAppt

                .StartTime = "#" & StartDate & "#"
                .EndTime = "#" & EndDate & "#"
                .Subject = Subject
                .Location = Location
                .TextBody = TextBody
                .BusyStatus = "OOF"
                .MeetingStatus = "Tentative"

                'Save the appointment in a public folder calendar
                Conn.Open("http://myserver/public/leaveform/")

..DataSource.SaveToContainer("http://myserver/public/leaveform/", Conn)

            End With

            CreateAppointment = iAppt
            Conn.Close()

        Catch err As Exception
            Console.WriteLine(err.ToString())
            CreateAppointment = Nothing
        End Try
    End Function
---

I would greatly appreciate any help and would be glad to share any knowledge
I have gained from .net apps with SQL and exchange.

Thank you,
Ken Wigle
wigl***@uc.edu

7 Jul 2005 4:20 AM

Glen Scales [MVP]

There are some samples of creating an appointment via WebDAV in the Exchange
SDK eg
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/_esdk_creating_an_appointment_webdav.asp .
The only thing to be carefull of is with these sample is that they create
meeting (with attendees) rather then just a simple appointment. If you just
want an appointment all you need to do is drop the header information out
and make sure you add in the apptstateflags property and set it to 0 eg
(moded code from the above link)

Module Module1

    Sub Main()
        ' Variables
        Dim strExchSvrName As String
        Dim strMailbox As String
        Dim strCalendarUri As String
        Dim strApptItem As String
        Dim strDomain As String
        Dim strUserName As String
        Dim strPassword As String
        Dim strApptRequest As String
        Dim strMailInfo As String
        Dim strCalInfo As String
        Dim strXMLNSInfo As String
        Dim strHeaderInfo As String
        Dim PROPPATCHRequest As System.Net.HttpWebRequest
        Dim PROPPATCHResponse As System.Net.WebResponse
        Dim MyCredentialCache As System.Net.CredentialCache
        Dim bytes() As Byte
        Dim PROPPATCHRequestStream As System.IO.Stream

        Try
            ' Exchange server name
            strExchSvrName = "servername"

            ' Mailbox folder name.
            strMailbox = "mailbox"

            ' Appointment item.
            strApptItem = "testappointment.eml"

            ' URI of the user's calendar folder.
            strCalendarUri = "http://" & strExchSvrName & "/exchange/" & _
            strMailbox & "/Calendar/"

            ' User name and password of appointment creator.
            strUserName = "username"
            strDomain = "domain"
            strPassword = "password"

            ' XML namespace info for the WebDAV request.
            strXMLNSInfo = "xmlns:g=""DAV:"" " & _
               "xmlns:e=""http://schemas.microsoft.com/exchange/"" " & _
               "xmlns:mapi=""http://schemas.microsoft.com/mapi/"" " & _
               "xmlns:mapit=""http://schemas.microsoft.com/mapi/proptag/"" "
& _
               "xmlns:x=""xml:"" xmlns:cal=""urn:schemas:calendar:"" " & _
               "xmlns:dt=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/""
" & _
               "xmlns:header=""urn:schemas:mailheader:"" " & _
               "xmlns:mail=""urn:schemas:httpmail:"""

            ' Set the appointment item properties. The reminder time is set
in seconds.
            ' To create an all-day meeting, set the dtstart/dtend range for
24 hours
            ' or more and set the alldayevent property to 1. See the
documentation
            ' on the properties in the urn:schemas:calendar: namespace for
more information.
            strCalInfo = "<cal:location>meetappt Location</cal:location>" &
_
               "<cal:dtstart
dt:dt=""dateTime.tz"">2005-07-18T23:00:00.000Z</cal:dtstart>" & _
               "<cal:dtend
dt:dt=""dateTime.tz"">2005-07-18T23:30:00.000Z</cal:dtend>" & _
               "<cal:instancetype dt:dt=""int"">0</cal:instancetype>" & _
               "<cal:busystatus>OOF</cal:busystatus>" & _
               "<cal:meetingstatus>Tentative</cal:meetingstatus>" & _
               "<cal:alldayevent dt:dt=""boolean"">0</cal:alldayevent>" & _
               "<cal:responserequested
dt:dt=""boolean"">1</cal:responserequested>" & _
               "<cal:reminderoffset dt:dt=""int"">900</cal:reminderoffset>"

            ' Set the required attendee of the appointment.
            strHeaderInfo = ""

            ' Set the subject of the appointment.
            strMailInfo = "<mail:subject>Test Appointment
Subject</mail:subject>" & _
               "<mail:htmldescription>Let's meet
here</mail:htmldescription>"

            ' Build the XML body of the PROPPATCH request.
            strApptRequest = "<?xml version=""1.0""?>" & _
               "<g:propertyupdate " & strXMLNSInfo & ">" & _
               "<g:set><g:prop>" & _
               "<g:contentclass>urn:content-classes:appointment</g:contentclass>"
& _
               "<e:outlookmessageclass>IPM.Appointment</e:outlookmessageclass>"
& _
               strMailInfo & _
               strCalInfo & _
               strHeaderInfo & _
               "<mapi:finvited dt:dt=""boolean"">0</mapi:finvited>" & _
               "<mapi:apptstateflags dt:dt=""int"">0</mapi:apptstateflags>"
& _
               "</g:prop></g:set>" & _
               "</g:propertyupdate>"

            ' Create a new CredentialCache object and fill it with the
network
            ' credentials required to access the server.
            MyCredentialCache = New System.Net.CredentialCache
            MyCredentialCache.Add(New System.Uri(strCalendarUri), _
                                  "NTLM", _
                                  New
System.Net.NetworkCredential(strUserName, strPassword, strDomain) _
                                  )

            ' Create the HttpWebRequest object.
            PROPPATCHRequest =
CType(System.Net.HttpWebRequest.Create(strCalendarUri & strApptItem), _
                                     System.Net.HttpWebRequest)

            ' Add the network credentials to the request.
            PROPPATCHRequest.Credentials = MyCredentialCache

            ' Specify the PROPPATCH method.
            PROPPATCHRequest.Method = "PROPPATCH"

            ' Set the content type header.
            PROPPATCHRequest.ContentType = "text/xml"

            ' Encode the body using UTF-8.
            bytes = System.Text.Encoding.UTF8.GetBytes(strApptRequest)

            ' Set the content header length. This must be
            ' done before writing data to the request stream.
            PROPPATCHRequest.ContentLength = bytes.Length

            ' Get a reference to the request stream.
            PROPPATCHRequestStream = PROPPATCHRequest.GetRequestStream()

            ' Write the message body to the request stream.
            PROPPATCHRequestStream.Write(bytes, 0, bytes.Length)

            ' Close the Stream object to release the connection
            ' for further use.
            PROPPATCHRequestStream.Close()

            ' Create the appointment in the Calendar folder of the
            ' user's mailbox.
            PROPPATCHResponse = CType(PROPPATCHRequest.GetResponse(),
System.Net.HttpWebResponse)

            Console.WriteLine("Appointment successfully created.")

            ' Clean up.
            PROPPATCHResponse.Close()

        Catch ex As Exception
            ' Catch any exceptions. Any error codes from the PROPPATCH
            ' or MOVE method requests on the server will be caught
            ' here, also.
            Console.WriteLine(ex.Message)

        End Try

    End Sub

End Module

cheers
Glen
Show quote

"Ken Wigle" <KenWi***@discussions.microsoft.com> wrote in message
news:39916028-7B3F-44FA-8CE6-7ECE17FAAB8F@microsoft.com...
> All,
>
> I am in dire need of a solution. I used to have my .net application on
> my
> exchange server but have since moved it to a separate server (which is
> running SQL 2000). The application basically grabs leave form requests
> and
> stores them on the sql server. It uses Exchange 2003 to route e-mails and
> to
> store the time off on a public calendar. Everything still works fine
> except
> for the public calendar. As I found via google, you cannot use exoledb as
> your provider unless the code is on the exchange server. Does anyone have
> an
> alternative (maybe with WebDAV) to the small piece of code below that does
> not use exoledb?
>
> ---
>    ' Reference to Microsoft ActiveX Data Objects 2.5 Library
>    ' Reference to Microsoft CDO for Exchange 2000 Library
>    ' Reference to Active DS Type Library
>    Function CreateAppointment(ByVal StartDate As Date, _
>                               ByVal EndDate As Date, ByVal stime As
> String, _
>                               ByVal etime As String, ByVal Subject As
> String, _
>                               ByVal Location As String, _
>                               ByVal TextBody As String) As CDO.Appointment
>        Try
>
>            Dim iAppt As New CDO.Appointment
>            Dim Conn As New ADODB.Connection
>            Conn.Provider = "ExOLEDB.DataSource"
>
>            With iAppt
>
>                .StartTime = "#" & StartDate & "#"
>                .EndTime = "#" & EndDate & "#"
>                .Subject = Subject
>                .Location = Location
>                .TextBody = TextBody
>                .BusyStatus = "OOF"
>                .MeetingStatus = "Tentative"
>
>                'Save the appointment in a public folder calendar
>                Conn.Open("http://myserver/public/leaveform/")
>
> .DataSource.SaveToContainer("http://myserver/public/leaveform/", Conn)
>
>            End With
>
>            CreateAppointment = iAppt
>            Conn.Close()
>
>        Catch err As Exception
>            Console.WriteLine(err.ToString())
>            CreateAppointment = Nothing
>        End Try
>    End Function
> ---
>
> I would greatly appreciate any help and would be glad to share any
> knowledge
> I have gained from .net apps with SQL and exchange.
>
> Thank you,
> Ken Wigle
> wigl***@uc.edu

7 Jul 2005 1:55 PM

Ken Wigle

Thank you - I will try it. Even though it looks more complicated than using
exoledb, it may also solve another question I had which is how to
progammatically set the category (the same category field you see in an
outlook appointment screen).

I really appreciate the feedback.

Ken

Show quote

"Glen Scales [MVP]" wrote:

> There are some samples of creating an appointment via WebDAV in the Exchange
> SDK eg
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/wss/wss/_esdk_creating_an_appointment_webdav.asp .
> The only thing to be carefull of is with these sample is that they create
> meeting (with attendees) rather then just a simple appointment. If you just
> want an appointment all you need to do is drop the header information out
> and make sure you add in the apptstateflags property and set it to 0 eg
> (moded code from the above link)
>
> Module Module1
>
>     Sub Main()
>         ' Variables
>         Dim strExchSvrName As String
>         Dim strMailbox As String
>         Dim strCalendarUri As String
>         Dim strApptItem As String
>         Dim strDomain As String
>         Dim strUserName As String
>         Dim strPassword As String
>         Dim strApptRequest As String
>         Dim strMailInfo As String
>         Dim strCalInfo As String
>         Dim strXMLNSInfo As String
>         Dim strHeaderInfo As String
>         Dim PROPPATCHRequest As System.Net.HttpWebRequest
>         Dim PROPPATCHResponse As System.Net.WebResponse
>         Dim MyCredentialCache As System.Net.CredentialCache
>         Dim bytes() As Byte
>         Dim PROPPATCHRequestStream As System.IO.Stream
>
>         Try
>             ' Exchange server name
>             strExchSvrName = "servername"
>
>             ' Mailbox folder name.
>             strMailbox = "mailbox"
>
>             ' Appointment item.
>             strApptItem = "testappointment.eml"
>
>             ' URI of the user's calendar folder.
>             strCalendarUri = "http://" & strExchSvrName & "/exchange/" & _
>             strMailbox & "/Calendar/"
>
>             ' User name and password of appointment creator.
>             strUserName = "username"
>             strDomain = "domain"
>             strPassword = "password"
>
>             ' XML namespace info for the WebDAV request.
>             strXMLNSInfo = "xmlns:g=""DAV:"" " & _
>                "xmlns:e=""http://schemas.microsoft.com/exchange/"" " & _
>                "xmlns:mapi=""http://schemas.microsoft.com/mapi/"" " & _
>                "xmlns:mapit=""http://schemas.microsoft.com/mapi/proptag/"" "
> & _
>                "xmlns:x=""xml:"" xmlns:cal=""urn:schemas:calendar:"" " & _
>                "xmlns:dt=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/""
> " & _
>                "xmlns:header=""urn:schemas:mailheader:"" " & _
>                "xmlns:mail=""urn:schemas:httpmail:"""
>
>             ' Set the appointment item properties. The reminder time is set
> in seconds.
>             ' To create an all-day meeting, set the dtstart/dtend range for
> 24 hours
>             ' or more and set the alldayevent property to 1. See the
> documentation
>             ' on the properties in the urn:schemas:calendar: namespace for
> more information.
>             strCalInfo = "<cal:location>meetappt Location</cal:location>" &
> _
>                "<cal:dtstart
> dt:dt=""dateTime.tz"">2005-07-18T23:00:00.000Z</cal:dtstart>" & _
>                "<cal:dtend
> dt:dt=""dateTime.tz"">2005-07-18T23:30:00.000Z</cal:dtend>" & _
>                "<cal:instancetype dt:dt=""int"">0</cal:instancetype>" & _
>                "<cal:busystatus>OOF</cal:busystatus>" & _
>                "<cal:meetingstatus>Tentative</cal:meetingstatus>" & _
>                "<cal:alldayevent dt:dt=""boolean"">0</cal:alldayevent>" & _
>                "<cal:responserequested
> dt:dt=""boolean"">1</cal:responserequested>" & _
>                "<cal:reminderoffset dt:dt=""int"">900</cal:reminderoffset>"
>
>             ' Set the required attendee of the appointment.
>             strHeaderInfo = ""
>
>             ' Set the subject of the appointment.
>             strMailInfo = "<mail:subject>Test Appointment
> Subject</mail:subject>" & _
>                "<mail:htmldescription>Let's meet
> here</mail:htmldescription>"
>
>             ' Build the XML body of the PROPPATCH request.
>             strApptRequest = "<?xml version=""1.0""?>" & _
>                "<g:propertyupdate " & strXMLNSInfo & ">" & _
>                "<g:set><g:prop>" & _
>                "<g:contentclass>urn:content-classes:appointment</g:contentclass>"
> & _
>                "<e:outlookmessageclass>IPM.Appointment</e:outlookmessageclass>"
> & _
>                strMailInfo & _
>                strCalInfo & _
>                strHeaderInfo & _
>                "<mapi:finvited dt:dt=""boolean"">0</mapi:finvited>" & _
>                "<mapi:apptstateflags dt:dt=""int"">0</mapi:apptstateflags>"
> & _
>                "</g:prop></g:set>" & _
>                "</g:propertyupdate>"
>
>             ' Create a new CredentialCache object and fill it with the
> network
>             ' credentials required to access the server.
>             MyCredentialCache = New System.Net.CredentialCache
>             MyCredentialCache.Add(New System.Uri(strCalendarUri), _
>                                   "NTLM", _
>                                   New
> System.Net.NetworkCredential(strUserName, strPassword, strDomain) _
>                                   )
>
>             ' Create the HttpWebRequest object.
>             PROPPATCHRequest =
> CType(System.Net.HttpWebRequest.Create(strCalendarUri & strApptItem), _
>                                      System.Net.HttpWebRequest)
>
>             ' Add the network credentials to the request.
>             PROPPATCHRequest.Credentials = MyCredentialCache
>
>             ' Specify the PROPPATCH method.
>             PROPPATCHRequest.Method = "PROPPATCH"
>
>             ' Set the content type header.
>             PROPPATCHRequest.ContentType = "text/xml"
>
>             ' Encode the body using UTF-8.
>             bytes = System.Text.Encoding.UTF8.GetBytes(strApptRequest)
>
>             ' Set the content header length. This must be
>             ' done before writing data to the request stream.
>             PROPPATCHRequest.ContentLength = bytes.Length
>
>             ' Get a reference to the request stream.
>             PROPPATCHRequestStream = PROPPATCHRequest.GetRequestStream()
>
>             ' Write the message body to the request stream.
>             PROPPATCHRequestStream.Write(bytes, 0, bytes.Length)
>
>             ' Close the Stream object to release the connection
>             ' for further use.
>             PROPPATCHRequestStream.Close()
>
>             ' Create the appointment in the Calendar folder of the
>             ' user's mailbox.
>             PROPPATCHResponse = CType(PROPPATCHRequest.GetResponse(),
> System.Net.HttpWebResponse)
>
>             Console.WriteLine("Appointment successfully created.")
>
>             ' Clean up.
>             PROPPATCHResponse.Close()
>
>         Catch ex As Exception
>             ' Catch any exceptions. Any error codes from the PROPPATCH
>             ' or MOVE method requests on the server will be caught
>             ' here, also.
>             Console.WriteLine(ex.Message)
>
>         End Try
>
>     End Sub
>
> End Module
>
> cheers
> Glen
> "Ken Wigle" <KenWi***@discussions.microsoft.com> wrote in message
> news:39916028-7B3F-44FA-8CE6-7ECE17FAAB8F@microsoft.com...
> > All,
> >
> > I am in dire need of a solution. I used to have my .net application on
> > my
> > exchange server but have since moved it to a separate server (which is
> > running SQL 2000). The application basically grabs leave form requests
> > and
> > stores them on the sql server. It uses Exchange 2003 to route e-mails and
> > to
> > store the time off on a public calendar. Everything still works fine
> > except
> > for the public calendar. As I found via google, you cannot use exoledb as
> > your provider unless the code is on the exchange server. Does anyone have
> > an
> > alternative (maybe with WebDAV) to the small piece of code below that does
> > not use exoledb?
> >
> > ---
> >    ' Reference to Microsoft ActiveX Data Objects 2.5 Library
> >    ' Reference to Microsoft CDO for Exchange 2000 Library
> >    ' Reference to Active DS Type Library
> >    Function CreateAppointment(ByVal StartDate As Date, _
> >                               ByVal EndDate As Date, ByVal stime As
> > String, _
> >                               ByVal etime As String, ByVal Subject As
> > String, _
> >                               ByVal Location As String, _
> >                               ByVal TextBody As String) As CDO.Appointment
> >        Try
> >
> >            Dim iAppt As New CDO.Appointment
> >            Dim Conn As New ADODB.Connection
> >            Conn.Provider = "ExOLEDB.DataSource"
> >
> >            With iAppt
> >
> >                .StartTime = "#" & StartDate & "#"
> >                .EndTime = "#" & EndDate & "#"
> >                .Subject = Subject
> >                .Location = Location
> >                .TextBody = TextBody
> >                .BusyStatus = "OOF"
> >                .MeetingStatus = "Tentative"
> >
> >                'Save the appointment in a public folder calendar
> >                Conn.Open("http://myserver/public/leaveform/")
> >
> > .DataSource.SaveToContainer("http://myserver/public/leaveform/", Conn)
> >
> >            End With
> >
> >            CreateAppointment = iAppt
> >            Conn.Close()
> >
> >        Catch err As Exception
> >            Console.WriteLine(err.ToString())
> >            CreateAppointment = Nothing
> >        End Try
> >    End Function
> > ---
> >
> > I would greatly appreciate any help and would be glad to share any
> > knowledge
> > I have gained from .net apps with SQL and exchange.
> >
> > Thank you,
> > Ken Wigle
> > wigl***@uc.edu
>
>
>