How to add Column/Row Validation Using Typed DataTable

In this post we will see how we can add the Validation Rule in Typed DataTable of a Typed DataSet (using Partial Class) and then how can we use this Validation on our Form in DataGridView or Details.

Level: Intermediate

Knowledge Required:

• Typed DataSet
• DataTable
• Partial Class
• DataGridView
• ErrorProvider

Description:
This post is based on Beth Massi's Video Tutorial.

As we have used the DataGridView control for entering the data in a DataTable. If we have set one column of our DataTable AllowDBNull = False, then in DataGridView we encounter an exception when we try to enter a Null in this column. This exception can be handled in DataError event of DataGridView control. But the row will be removed from list anyway. So to make it more user friendly, we do the following things,

1) Set the AllowDBNull = True for the column
2) Add Validation Rule in the DataTable

Example:
I have created a Typed DataSet StudentDataSet in which I have added 1 Table

• Student_ID (primary key, AllowDBNull=False, AutoIncreament=True)
• Student_Name (AllowDBNull=True)
• Father_Name (AllowDBNull=True)
• Age (AllowDBNull=True)

All the columns (except the 1st one) are Allowed to have NULL values. Now we will add the Validation Business Rule in our DataTable by using Partial Class as,

Partial Class StudentDataSet
    Partial Class StudentDataTable
        Private Sub CheckStudentName(ByVal rowStudent As StudentDataSet.StudentRow)
            If rowStudent.IsStudent_NameNull() OrElse rowStudent.Student_Name = "" Then
                ' set error
                rowStudent.SetColumnError(Me.Student_NameColumn, "Student Name is Required")
            Else
                ' ok clear the error
                rowStudent.SetColumnError(Me.Student_NameColumn, "")
            End If
        End Sub

        Private Sub CheckAge(ByVal rowStudent As StudentDataSet.StudentRow)
            If rowStudent.IsAgeNull OrElse _
                rowStudent.Age < 3 OrElse _
                rowStudent.Age > 10 Then
                rowStudent.SetColumnError(Me.AgeColumn, "Age must be between 3 to 10")
            Else
                rowStudent.SetColumnError(Me.AgeColumn, "")
            End If
        End Sub

        Private Sub StudentDataTable_ColumnChanged(ByVal sender As Object, ByVal e As System.Data.DataColumnChangeEventArgs) Handles Me.ColumnChanged
            If e.Column Is Me.Student_NameColumn Then
                Call Me.CheckStudentName(CType(e.Row, StudentRow))
            ElseIf e.Column Is Me.AgeColumn Then
                Call Me.CheckAge(CType(e.Row, StudentRow))
            End If
        End Sub

        Private Sub StudentDataTable_TableNewRow(ByVal sender As Object, ByVal e As System.Data.DataTableNewRowEventArgs) Handles Me.TableNewRow
            Call Me.CheckStudentName(CType(e.Row, StudentRow))
            Call Me.CheckAge(CType(e.Row, StudentRow))
        End Sub
    End Class
End Class

First we have created 2 private methods

1) CheckStudentName() - validates the Student Name column
2) CheckAge() - validates the Age column

Then we have used 2 DataTable Events

1) ColumnChanged - in this event handler we check which column has been changed then we call the method according to that column
2) TableNewRow - in this event handler we execute both methods, since whole row is chagned

Here we have done with our Custom Validation. Now we just simply put the DataGridView control on Form, bind it with this DataTable and the rest of the things will be handled by DataGridView itself.

DataGridView:

As you can see the DataGridView is Displaying a Red Icon in the Age column and it displays a ToolTip of Error that we have set in our Validation.

And finally when we are about to Save this Table, then we will first check the Error as,

If Me.StudentDataSet.Student.HasErrors() Then
    MsgBox("Table contains Error, cannot save", MsgBoxStyle.Exclamation, "Save")
Else
    MsgBox("Table does NOT contain any Error, can Save now", MsgBoxStyle.Information, "Save")
End If

Detail View:


Now we can implement the same thing in our Detail View. As you can see the above Form I have put the TextBox Controls and have Bind them with the same Table. But to show the Error Icon we will use the ErrorProvider Component.

1) Add the ErrorProvider Component
2) Set its BindingSource to the StudentBindingSource

That is it, ErrorProvider will display the Error Icon with TextBox that violates the Business Rule. Note that StudentBindingSource is the same BindingSource which is Bound with the TextBox Controls.

Project Details:
Custom Validation on Typed DataTable

Project Download:
CustomValidation.zip

Try...Catch and Transaction in SQL Server 2005

Level: Intermediate

Knowledge Required:

• T-SQL
• SQL Server 2005
• Stored Procedure

Description:
In Stored Procedure (having lots of INSERTs, UPDATESs and DELETEs) we use Transaction (Begin Tran, Commit Tran, RollBack Tran). We usually begin with Begin Tran and on some error we do RollBack Tran and if NO error occurred we Commit Tran.

A better way to do this task is to use the Try...Catch in SQL Server 2005. For example:

    Begin Try
        BEGIN TRAN;

        INSERT INTO ....

        SELECT ...

        INSERT INTO ...

        DELETE ...
  
        DELETE ...

        INSERT ...

        COMMIT TRAN;
    End Try
    Begin Catch
        ROLLBACK TRAN;

        DECLARE @ErrorMsg varchar(max);
        DECLARE @ErrorSeverity int;
        DECLARE @ErrorState int;

        SET @ErrorMsg = ERROR_Message();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();

        RAISERROR(@ErrorMsg, @ErrorSeverity, @ErrorState);
    End Catch

As you can see,

1) We start the Transaction in Try...Catch block
2) Next we move forward and try to do insert, update and delete
3) If any error occurred during these statements the Exception will be caught in the Begin Catch...End Catch block
4) Then in this block we have first Rolled Back the Transaction
5) Then again throw the exception so the caller should know that some error was occurred during processing
6) And if NO exception occured then finally before the End Try we have Committed the Transaction

Paste Text in DataGridView Control (Bound with BindingSource)

DataGridView control does NOT have the built-in Paste functionality. Therefore we are going to create a Generic Function which will Paste Text in DataGridView control.

Level: Intermediate

Knowledge Required:

• DataGridView
• Clipboard
• String
• BindingSource
• DataTable

Description:
In the my earlier post:

DataGridView Control FAQs and Quick Tips

I have discussed how to Copy Data from DataGridView control to Clipboard. Now we will see how can we Paste Data from Clipboard to DataGridView control. The following function is Generic for DataGridView control which is bound with some BindingSource. Optionally user can provide the DataTable (which is DataGridView source). This parameter is used in the function to check a Column whether it is Read-Only or NOT.

Features:
1) Paste Data at the End: Set the current position at the last row (which creates the new row) and execute the function, new rows will be added
2) Paste Data in the middle rows: Set the current position anywhere in middle (not at end) and execute the function, middle rows will be overwritten by new data
3) Paste Data in any column: Set the current position in any column and execute function, function will start pasting data from that particular column

Public Sub PasteInBoundDataGridView(ByVal dgvToUse As DataGridView, ByVal bsDataGridView As BindingSource, ByVal tblSource As DataTable)
    Dim iCurrentRow As Integer = -1
    Dim bFullRowSelected As Boolean = False

    ' if some row is selected
    If dgvToUse.CurrentRow IsNot Nothing Then
        ' if it is NOT a new row
        If Not dgvToUse.CurrentRow.IsNewRow Then
        ' get the index of that row
            iCurrentRow = dgvToUse.CurrentRow.Index
        End If

        ' if current row is selected
        If dgvToUse.CurrentRow.Selected Then
            ' it means full row is selected
            bFullRowSelected = True
        End If
    End If

    ' cancel the current edit
    bsDataGridView.CancelEdit()

    Dim sText As String
    Dim sLines() As String
    Dim iCurCol As Integer

    ' if full row is selected
    If bFullRowSelected Then
        ' then set the initial column = 0
        iCurCol = 0
    Else ' else if full row is NOT selected
        ' set the initial column = current column
        iCurCol = dgvToUse.CurrentCell.ColumnIndex
    End If

    ' get the text from clipboard
    sText = My.Computer.Clipboard.GetText()
    ' split the text into lines
    sLines = sText.Split(New String() {vbCrLf}, StringSplitOptions.RemoveEmptyEntries)
    ' for each line in extracted lines
    For Each sLine As String In sLines
        Dim sColValues() As String
        ' split the line into columns
        sColValues = sLine.Split(vbTab)

        Dim c As Integer = iCurCol
        Dim rowEdit As DataRowView

        ' if currently some middle rows are selected and also
        ' selected row is NOT the last row
        If iCurrentRow >= 0 AndAlso iCurrentRow < dgvToUse.Rows.Count - 1 Then
            ' row is selected row
            rowEdit = CType(dgvToUse.Rows(iCurrentRow).DataBoundItem, DataRowView)
            ' now move to next row
            iCurrentRow += 1
        Else ' else it means we are at end then
            ' we will add the row
            rowEdit = bsDataGridView.AddNew
        End If

        ' for each column in extracted columns
        For Each sColValue As String In sColValues
            ' if this column is bound
            If dgvToUse.Columns(c).DataPropertyName <> "" Then
                ' if some table is mentioned and also
                ' the column in which we are going to paste is NOT read-only
                If tblSource Is Nothing OrElse _
                   Not tblSource.Columns(dgvToUse.Columns(c).DataPropertyName).ReadOnly Then
                    ' if extracted value is empty string
                    If sColValue = "" Then
                        ' then paste the DBNULL.value
                        rowEdit(dgvToUse.Columns(c).DataPropertyName) = DBNull.Value
                    Else ' else it means some value is mentioned
                        ' then paste that value
                        rowEdit(dgvToUse.Columns(c).DataPropertyName) = sColValue
                    End If
                End If
            End If
            ' increase the column count
            c += 1
            ' if reached at last column then stop setting values in columns
            If c >= dgvToUse.Columns.Count Then Exit For
        Next ' next column
    
        ' ok row edit is complete so end it
        bsDataGridView.EndEdit()
    Next 'next line
End Sub

Usage:

Private Sub Paste()
    Try
        If Not Me.DataGridView1.IsCurrentCellInEditMode Then
            Call PasteInBoundDataGridView(Me.DataGridView1, Me.BindingSource1, Me.DataSet1.DataTable1)
        End If
    Catch ex As Exception
        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Paste")
    End Try
End Sub

See Also:
DataGridView Control FAQs and Quick Tips

Performing Aggregate Functions (MAX, MIN, SUM, COUNT) on DataTable

In this article we will discuss:

• How to get Maximum (MAX) or Minimum (MIN) value from DataTable
• How to SUM all the values of a column in DataTable
• How to Count all the values of a column in DataTable

Level: Beginner

Knowledge Required:
DataTable

Description:
We have used DataTable Class of .net framework lots of time to load the data from Physical Database. DataTable NOT only stores the Data but can also perform other things like performing calculation on a column, e.g. getting Max./Min. item, counting, etc. So in this article we will discuss how can we perform this type of calculation on DataTable.

DataTable has a public method called Compute. We can use the aggregate functions in this method as,

Dim iMaxRow_ID As Integer
iMaxRow_ID = StudentTable.Compute("MAX(Row_ID)", "")

The above example will get the Maximum Row_ID (Row_ID is a column in StudentTable) from StudentTable.

Dim iTotalOrders As Integer
iTotalOrders = OrderTable.Compute("Count(Order_ID)", "Create_Date > #15-Jan-2008#")

Above example will count number of Orders that were created after 15-Jan-2008

For detailed information:

MSDN DataTable.Compute Method