[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2842
  • Last Modified:

Read and Unread Lotus Notes mails in asp.net Gridview

Hi all,
have GridView with inbox mails on asp.net form, what I need is to distingush read and unread mails.
Example:
First mail in inbox is unread. I want to see that mail in red color and bold font style in Gridview. Then when I click select button and display mail body and attachment list in text boxes text should be regular, black color and unbold.
Is it possible to get mail status (read/unread) from Lotus Notes database and present it this way in asp.net?

Thanks
Imports Domino 
Imports System.Data.SqlClient 
 
Partial Class Default6 
    Inherits System.Web.UI.Page 
 
Private Function CreateDataTable() As Data.DataTable 
        Try 
            Dim myDataTable As Data.DataTable = New Data.DataTable() 
            Dim myDataColumn As Data.DataColumn 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.Int32") 
            myDataColumn.ColumnName = "ID" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Status" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "From" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "To" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.DateTime") 
            myDataColumn.ColumnName = "Timestamp" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Subject" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Attachment List" 
            myDataTable.Columns.Add(myDataColumn) 
 
            myDataColumn = New Data.DataColumn() 
            myDataColumn.DataType = Type.GetType("System.String") 
            myDataColumn.ColumnName = "Body" 
            myDataTable.Columns.Add(myDataColumn) 
 
            Return myDataTable 
        Catch ex As Exception 
 
        End Try 
 
    End Function 
 
Private Function AddDataToTable(ByVal bdy As String, ByVal fom As String, ByVal sbj As String, ByVal time As String, ByVal attachmentNames As String, ByVal myTable As Data.DataTable) 
        Try 
            Dim row As Data.DataRow = myTable.NewRow() 
 
            row("From") = fom 
            row("Subject") = sbj 
            row("Timestamp") = time 
            row("Attachment List") = attachmentNames 
            row("Body") = bdy 
 
            myTable.Rows.Add(row) 
            myTable.AcceptChanges() 
        Catch ex As Exception 
 
        End Try 
    End Function 
 
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 
 
 
        Dim appNotes As NotesSession 
        appNotes = New NotesSession 
        Dim dbNotes As NotesDatabase 
        appNotes.Initialize("baahdb") 
        dbNotes = appNotes.GetDatabase("abc", "def")
        Dim vwInbox As NotesView 
        vwInbox = dbNotes.GetView("$Inbox") 
        Dim docMessage As NotesDocument 
        Dim fom As Object 
        Dim sbj As Object 
        Dim bdy As Object 
        Dim time As Object 
        Dim attachmentNames As Object 
        Dim prezime As String 
        Dim vrijeme As String 
        On Error Resume Next 
 
        Dim myDt As Data.DataTable 
        myDt = New Data.DataTable() 
        myDt = CreateDataTable() 
 
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString 
        Dim mySqlConnection As New SqlConnection(connectionString) 
        Dim cmd As New SqlCommand 
        Dim dreader As SqlDataReader 
        If mySqlConnection.State = Data.ConnectionState.Open Then mySqlConnection.Close() 
        mySqlConnection.Open() 
 
        With vwInbox 
            docMessage = .GetLastDocument 
            Do 
                fom = appNotes.Evaluate("@Name([CN]; From)", docMessage) 
                'fom = appNotes.Evaluate("@Right(@Left(From; ""/""); ""CN="")", docMessage) 
 
                sbj = appNotes.Evaluate(("Subject"), docMessage) 
 
                bdy = appNotes.Evaluate("@Text(Body)", docMessage) 
                'List3.Items.Add(docMessage.GetItemValue("Body")) 
                'List3.Items.Add(Join(docMessage.GetItemValue("Body"))) 
 
                time = appNotes.Evaluate("@Text(DeliveredDate)", docMessage) 
                'Convert.ToDateTime(time) 
 
                attachmentNames = appNotes.Evaluate("@Implode(@AttachmentNames; "", "")", docMessage) 
                If attachmentNames(0) = "" Then attachmentNames(0) = " " 
                docMessage = .GetPrevDocument(docMessage) 
                AddDataToTable(CStr(bdy(0)), CStr(fom(0)), CStr(sbj(0)), CStr(time(0)), CStr(attachmentNames(0)), myDt) 
 
            Loop Until docMessage Is Nothing 
        End With 
        On Error Resume Next 
 
        docMessage = Nothing 
        vwInbox = Nothing 
        dbNotes = Nothing 
        appNotes = Nothing 
 
        For i As Integer = 0 To myDt.Rows.Count - 1 
            cmd.Parameters.Clear() 
            prezime = myDt.Rows(i).Item("From") 
            vrijeme = Convert.ToDateTime(myDt.Rows(i).Item("Timestamp")) 
            Convert.ToDateTime(vrijeme) 
            cmd.Connection = mySqlConnection 
            cmd.CommandText = "SELECT ID,Status,Uradio from Zahtjev_za_intervenciju with (nolock) where Prezime_ime=@prezime and [Timestamp]=@vrijeme;" 
 
            cmd.Parameters.AddWithValue("@prezime", prezime.ToString) 
            cmd.Parameters.AddWithValue("@vrijeme", vrijeme.ToString) 
            dreader = cmd.ExecuteReader 
            If dreader.HasRows Then 
                dreader.Read() 
                myDt.Rows(i).Item("ID") = dreader.Item("ID") 
                myDt.Rows(i).Item("To") = dreader.Item("Uradio") 
                myDt.Rows(i).Item("Status") = dreader.Item("Status") 
 
            End If 
            dreader.Close() 
        Next 
 
        mySqlConnection.Close() 
 
        Session("myDataTable") = myDt 
        Me.GridView1.DataSource = myDt 
        Me.GridView1.Attributes.Add("style", "table-layout:fixed") 
        Me.GridView1.AutoGenerateColumns = True 
        Me.GridView1.DataBind() 
 
    End Sub 
 
 
    Public Sub GridView1_OnRowCreated(ByVal sender As Object, ByVal e As Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowCreated 
 
        e.Row.Cells(7).Visible = False 
        e.Row.Cells(8).Visible = False 
 
    End Sub 
 
    Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles GridView1.SelectedIndexChanged 
        txtBody.Text = GridView1.SelectedRow.Cells(8).Text 
        txtAttach.Text = GridView1.SelectedRow.Cells(7).Text 
 
    End Sub 
 
    Protected Sub Upisi_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles Upisi.Click 
        Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString 
        Dim mySqlConnection As New SqlConnection(connectionString) 
        Dim vrijeme2 As String 
        vrijeme2 = Convert.ToDateTime(GridView1.SelectedRow.Cells(5).Text) 
 
        If mySqlConnection.State = Data.ConnectionState.Open Then mySqlConnection.Close() 
        mySqlConnection.Open() 
 
        Dim strSqlInsert As String = "INSERT INTO Zahtjev_za_intervenciju (Prezime_ime, Opis_Lotus, Timestamp, Subject_Lotus, Atach_list) VALUES (@Prezime_ime, @Opis_Lotus, @Timestamp, @Subject_Lotus, @Atach_List);" 
        Dim mySqlCommand As SqlCommand = New SqlCommand(strSqlInsert, mySqlConnection) 
 
        mySqlCommand.Parameters.AddWithValue("@Prezime_ime", GridView1.SelectedRow.Cells(3).Text) 
        mySqlCommand.Parameters.AddWithValue("@Subject_Lotus", GridView1.SelectedRow.Cells(6).Text) 
        mySqlCommand.Parameters.AddWithValue("@Opis_Lotus", txtBody.Text) 
        mySqlCommand.Parameters.AddWithValue("@Timestamp", vrijeme2.ToString) 
        mySqlCommand.Parameters.AddWithValue("@Atach_List", txtAttach.Text) 
 
        mySqlCommand.ExecuteNonQuery() 
        mySqlConnection.Close() 
    End Sub

Open in new window

0
kahvedzic
Asked:
kahvedzic
  • 3
1 Solution
 
Bill-HansonCommented:
Making progress, I see.  :)

If you are using Notes 8.5 or higher, you can use the NotesDocument.GetRead([username]) method.  If not, then you are out of luck.  There was no way to determine the status programatically until 8.5.
0
 
kahvedzicAuthor Commented:
Step by step but there is some progress.
Checked, it is Lotus Notes 8.5. Can you give me example for this metod and also where to use it. Thanks.
0
 
Bill-HansonCommented:
The first thing you need to know is that Notes maintains a separate 'unread' table for each user, so you have to pass the user's name in the GetRead method.

To use it, just place a call in your Do loop.  Something like this would work:
 
  dim userName As String = "CN=Some User/O=Acme"
  dim docRead as Boolean = docMessage.GetRead(userName)
0
 
Bill-HansonCommented:
Also, using Evaluate is slow.  Reading the field values directly from the docMessage object should be faster:

  Dim nam As NotesName
  Set nam = session.CreateName(docMessage.GetItemValue("From")(0))
  Dim fom As String = nam.Common
  Dim sbj As String = docMessage.GetItemValue("Subject")(0)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now