Assuming that you want to save into Payments and PaymentsDetails tables for a Client who has an account and ensuring that the balance in Accounts table is also updated.
This example shows how you can save, update and delete different objects of different types (or object lists) as a transaction in your DAL. It’s related to my previous post that you can find at. Using List(Of T) Generic in your data access layer (DAL)- Improved However, this could only process a list of the same type.
Explanation
- Create an enumeration (say Action) that you’ll use to specify an action to be performed on the list
- Create an interface (say ISQLData) for objects that are going to participate in a transaction to implement.
- Create a base class say DBConnect that implements ISQLData for other classes that will participate in the transaction to derive from.
- Create a helper class (say TransactionList) that will help to store object lists with action to be performed on the list
- Create a method (say DoTransactions) that processes the transactions
- Create objects such as Payments, PaymentDetails and Accounts that derives from DBConnect
- Define a list of the above objects and call DoTransactions to process them as a transaction.
Enumeration
Public Enum Action
Save
Update
Delete
End Enum
Interface
''' <summary>
''' Objects that will participate in transaction
''' must implement this interface
''' </summary>
''' <remarks></remarks>
Public Interface ISQLData
Property ConString() As String
Function SaveCommand() As SqlCommand
Function UpdateCommand() As SqlCommand
Function DeleteCommand() As SqlCommand
End Interface
Helper Objects and Methods
''' <summary>
''' Helper class that stores a list of objects
''' that implement ISQLData interface
''' </summary>
''' <typeparam name="T"></typeparam>
''' <remarks></remarks>
Public Class TransactionList(Of T As ISQLData)
#Region " Fields "
Private m_list As List(Of T)
Private m_Action As Action
#End Region
#Region " Properties "
Public Property List() As List(Of T)
Get
Return m_list
End Get
Set(ByVal value As List(Of T))
m_list = value
End Set
End Property
Public Property Action() As Action
Get
Return m_Action
End Get
Set(ByVal Value As Action)
m_Action = Value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _list As List(Of T), ByVal _Action As Action)
MyClass.New()
Me.List = _list
Me.Action = _Action
End Sub
#End Region
#Region " Methods "
#End Region
End Class
''' <summary>
''' Base class that implements ISQLData
''' </summary>
''' <remarks></remarks>
Public MustInherit Class DBConnect : Implements ISQLData
#Region " Fields "
' Objects that will participate in transactions
' will use the same connection string, lets define it here
Private Shared m_ConString As String
#End Region
#Region " Properties "
Public Property ConString() As String Implements ISQLData.ConString
Get
Return m_ConString
End Get
Set(ByVal Value As String)
m_ConString = Value
End Set
End Property
#End Region
#Region " Methods "
Public Overridable Function SaveCommand() As SqlCommand Implements ISQLData.SaveCommand
Return Nothing
End Function
Public Overridable Function UpdateCommand() As SqlCommand Implements ISQLData.UpdateCommand
Return Nothing
End Function
Public Overridable Function DeleteCommand() As SqlCommand Implements ISQLData.DeleteCommand
Return Nothing
End Function
#End Region
End Class
''' <summary>
''' Performs all specified actions such as Save,
''' Update and Delete on a list of objects of type
''' TransactionList having a list of objects of type
''' IDBConnect and returns number of records affected.
''' </summary>
''' <typeparam name="T"></typeparam>
''' <param name="_List"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function DoTransactions(Of T As ISQLData)(ByVal _List As List(Of TransactionList(Of T))) As Integer
Dim records As Integer
Using conn As New SqlConnection()
Try
If _List Is Nothing OrElse _List.Count < 1 Then Throw New ArgumentException("List is not set or empty!")
'exit the loop as soon as an item with set connection string is found
For Each items As TransactionList(Of T) In _List
For Each list As T In items.List
If list IsNot Nothing Then
conn.ConnectionString = list.ConString()
Exit For
End If
Next
If Not String.IsNullOrEmpty(conn.ConnectionString) Then Exit For
Next
conn.Open()
Using tran As SqlTransaction = conn.BeginTransaction()
For Each items As TransactionList(Of T) In _List
Select Case items.Action
Case Action.Save
For Each list As T In items.List
Try
Using comm As SqlCommand = list.SaveCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while saving, transaction canceled!" & vbCrLf & ex.Message)
End Try
Next
Case Action.Update
For Each list As T In items.List
Try
Using comm As SqlCommand = list.UpdateCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while updating, transaction was canceled!" & vbCrLf & ex.Message)
End Try
Next
Case Action.Delete
For Each list As T In items.List
Try
Using comm As SqlCommand = list.DeleteCommand()
comm.Connection = conn
comm.Transaction = tran
If comm.ExecuteNonQuery > 0 Then records += 1
End Using
Catch ex As Exception
tran.Rollback()
Throw New ArgumentException("Error occured while deleting, transaction was canceled!" & vbCrLf & ex.Message)
End Try
Next
End Select
Next
tran.Commit()
End Using
Return records
Catch eX As SqlException
Throw eX
Catch eX As Exception
Throw eX
Finally
conn.Close()
End Try
End Using
End Function
''' <summary>
''' Construct a connection string in a way that eliminates SQL injection
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString() As String
Dim conBuilder As New SqlConnectionStringBuilder()
Dim serverName As String = "(local)"
Dim databaseName As String = "Accounting"
conBuilder.Clear()
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder("Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo = False
Return conBuilder.ConnectionString
End Function
Participating Objects (Payments, PaymentDetails, Accounts)
Public Class Payments : Inherits DBConnect
#Region " Fields "
Private m_ReceiptNo As Integer
Private m_AccountNo As Integer
Private m_PayDate As Date
#End Region
#Region " Properties "
Public Property ReceiptNo() As Integer
Get
Return m_ReceiptNo
End Get
Set(ByVal value As Integer)
m_ReceiptNo = value
End Set
End Property
Public Property AccountNo() As Integer
Get
Return m_AccountNo
End Get
Set(ByVal value As Integer)
m_AccountNo = value
End Set
End Property
Public Property PayDate() As Date
Get
Return m_PayDate
End Get
Set(ByVal value As Date)
m_PayDate = value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _ConString As String)
MyClass.New()
Me.ConString = _ConString
End Sub
#End Region
#Region " Methods "
Public Overrides Function SaveCommand() As SqlCommand
Using comm As SqlCommand = New SqlCommand("uspInsertPayments")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@ReceiptNo", Me.ReceiptNo)
.AddWithValue("@AccountNo", Me.AccountNo)
.AddWithValue("@PayDate", Me.PayDate)
End With
Return comm
End Using
End Function
#End Region
End Class
Public Class PaymentDetails : Inherits DBConnect
#Region " Fields "
Private m_ReceiptNo As Integer
Private m_ProductNo As Integer
Private m_Price As Decimal
#End Region
#Region " Properties "
Public Property ReceiptNo() As Integer
Get
Return m_ReceiptNo
End Get
Set(ByVal value As Integer)
m_ReceiptNo = value
End Set
End Property
Public Property ProductNo() As Integer
Get
Return m_ProductNo
End Get
Set(ByVal value As Integer)
m_ProductNo = value
End Set
End Property
Public Property Price() As Decimal
Get
Return m_Price
End Get
Set(ByVal value As Decimal)
m_Price = value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _ConString As String)
MyClass.New()
Me.ConString = _ConString
End Sub
#End Region
#Region " Methods "
Public Overrides Function SaveCommand() As SqlCommand
Using comm As SqlCommand = New SqlCommand("uspInsertPaymentDetails")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@ReceiptNo", Me.ReceiptNo)
.AddWithValue("@ProductNo", Me.ProductNo)
.AddWithValue("@Price", Me.Price)
End With
Return comm
End Using
End Function
#End Region
End Class
Public Class Accounts : Inherits DBConnect
#Region " Fields "
Private m_TranID As Integer
Private m_AccountNo As Integer
Private m_Amount As Decimal
Private m_Balance As Decimal
#End Region
#Region " Properties "
Public Property TranID() As Integer
Get
Return m_TranID
End Get
Set(ByVal value As Integer)
m_TranID = value
End Set
End Property
Public Property AccountNo() As Integer
Get
Return m_AccountNo
End Get
Set(ByVal value As Integer)
m_AccountNo = value
End Set
End Property
Public Property Amount() As Decimal
Get
Return m_Amount
End Get
Set(ByVal value As Decimal)
m_Amount = value
End Set
End Property
Public Property Balance() As Decimal
Get
Return m_Balance
End Get
Set(ByVal value As Decimal)
m_Balance = value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
Public Sub New(ByVal _ConString As String)
MyClass.New()
Me.ConString = _ConString
End Sub
#End Region
#Region " Methods "
Public Overrides Function UpdateCommand() As SqlCommand
Using comm As SqlCommand = New SqlCommand("uspUpdateAccounts")
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@TranID", Me.TranID)
.AddWithValue("@AccountNo", Me.AccountNo)
.AddWithValue("@Amount", Me.Amount)
.AddWithValue("@Balance", Me.Balance)
End With
Return comm
End Using
End Function
#End Region
End Class
User Interface Code
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
Try
Me.Cursor = Cursors.WaitCursor
Dim transactions As New List(Of TransactionList(Of DBConnect))
' Make sure that atleast one object(Payments in this case)
' sets the connection string
Dim oPayments As New Payments(ConString)
Dim lPayments As New List(Of DBConnect)
Dim oPaymentDetails As New PaymentDetails()
Dim lPaymentDetails As New List(Of DBConnect)
Dim oAccounts As New Accounts()
Dim lAccounts As New List(Of DBConnect)
' Set Payments list to save
With lPayments
.Add(New Payments() With {.ReceiptNo = 101, .AccountNo = 1001, .PayDate = Today()})
End With
' Set PaymentDetails list to save
With lPaymentDetails
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 1, .Price = 500})
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 2, .Price = 550})
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 3, .Price = 200})
.Add(New PaymentDetails() With {.ReceiptNo = 101, .ProductNo = 4, .Price = 180})
End With
' Set Accounts list to update
With lAccounts
.Add(New Accounts() With {.TranID = 2, .AccountNo = 1001, .Amount = 800, .Balance = 8900})
End With
' Populate the transactions list
With transactions
.Add(New TransactionList(Of DBConnect)(lPayments, Action.Save))
.Add(New TransactionList(Of DBConnect)(lPaymentDetails, Action.Save))
.Add(New TransactionList(Of DBConnect)(lAccounts, Action.Update))
End With
' Process transactions
Dim records As Integer = Common.DoTransactions(transactions)
MessageBox.Show(String.Format("{0} record(s) processed!", records))
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
Me.Cursor = Cursors.Default
End Try
End Sub
Source Code (DoTransactions.zip) attached
Wilson Kutegeka | Microsoft MVP - VB
Developer | Promoter | ClinicMaster Software
Cel: +256 772 609113 | Web: www.clinicmaster.net
Stored procedures offer powerful security control over the database and importantly, provide an abstraction layer between the physical structure of the database and the logical way in which it’s used.
Thus, whenever possible, database access should be performed through the use of stored procedures. If this is the case, you’ll quickly realize that delete stored procedure is identical for most delete operations with an exception of table name to delete from and the where clause.
Below is an example of a delete stored procedure that you can use to delete data from different tables
if exists (select * from sysobjects where name = 'uspDeleteObject')
drop proc uspDeleteObject
go
create proc uspDeleteObject(
@ObjectName varchar(40) ,
@Where varchar(200) ,
@ErrorPart varchar(100) = null
) as
exec('declare @Records int declare @errorMSG varchar(200)
select @Records = count(*) from ' + @ObjectName + ' where ' + @Where + '
if @Records <= 0
begin
set @errorMSG = ''The record ' + @ErrorPart + ', you are trying to delete does not exist in the registered ' + @ObjectName + '.''
raiserror(@errorMSG,16, 1)
return
end
delete from ' + @ObjectName + ' where ' + @Where + '')
go
-- exec uspDeleteObject 'Logins', 'LoginID = ''Admin''', 'Login ID: Admin'
You can now call such a stored procedure from your VB code as follows
''' <summary>
''' Use uspDeleteObject stored procedure to delete data
''' from different tables. This deletes from Clients table
''' whose primary key is ClientID
''' </summary>
''' <returns></returns>
''' <remarks></remarks>
Public Function Delete() As Boolean
Dim where As String = "ClientID = '" & Me.ClientID & "'"
Dim errorPart As String = "Client ID: " & Me.ClientID
' Assuming Clients table had a composite key (ClientID and Version),
' the where and errorPart would be as follows
' where = "ClientID = '" & Me.ClientID & "' and Version = '" & Me.Version & "'"
' errorPart = "ClientID: " & Me.ClientID & " and Version: " & Me.Version
Using conn As New SqlConnection()
conn.ConnectionString = ConString()
Using comm As SqlCommand = New SqlCommand("uspDeleteObject", conn)
comm.CommandType = CommandType.StoredProcedure
With comm.Parameters
.AddWithValue("@ObjectName", "Clients")
.AddWithValue("@Where", where)
.AddWithValue("@ErrorPart", errorPart)
End With
Try
conn.Open()
Return comm.ExecuteNonQuery() > 0
Catch eX As SqlException
Throw eX
Catch eX As Exception
Throw eX
Finally
conn.Close()
End Try
End Using
End Using
End Function
''' <summary>
''' This construct of connection string eliminates SQL injection
''' </summary>
''' <param name="_ConString"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Function ConString() As String
Dim conBuilder As New SqlConnectionStringBuilder()
Dim serverName As String = "(local)"
Dim databaseName As String = "Accounting"
conBuilder.Clear()
conBuilder("Server") = serverName
conBuilder.InitialCatalog = databaseName
conBuilder("Integrated Security") = "SSPI"
conBuilder.PersistSecurityInfo = False
Return conBuilder.ConnectionString
End Function
Note:
You can modify the delete function such that the errorPart, where clause and tableName are supplied as its parameters.
Well I’ve not encountered many scenarios where I really needed to overload an operator. However operator overloading can save you a lot of coding time like in the double data entered application example I worked on and applied the concept.
This is how it goes.
Two users (first and second entry user) will enter two entries of the same record, it doesn’t matter who enters the first though. If both users enter correctly, the two entries will be merged into a final verified entry. Otherwise both entries will be saved and compared for error(s) before merging them.
This case needed retrieving a previously saved entry by the first user, compare it with one about to be saved, and if they’re the same, update the saved copy to verified otherwise display fields that are different for both users for editing.
Operator equal to (=) Overloading was used as a short cut to this implementation, a simplified code sample is as follows
Public Class Customer
#Region " Fields "
Private m_CustID As Integer
Private m_FirstName As String
Private m_LastName As String
Private m_Address As String
Private m_Telephone As String
Private m_Email As String
#End Region
#Region " Properties "
Public Property CustID() As Integer
Get
Return m_CustID
End Get
Set(ByVal Value As Integer)
m_CustID = Value
End Set
End Property
Public Property FirstName() As String
Get
Return m_FirstName
End Get
Set(ByVal Value As String)
m_FirstName = Value
End Set
End Property
Public Property LastName() As String
Get
Return m_LastName
End Get
Set(ByVal Value As String)
m_LastName = Value
End Set
End Property
Public Property Address() As String
Get
Return m_Address
End Get
Set(ByVal Value As String)
m_Address = Value
End Set
End Property
Public Property Telephone() As String
Get
Return m_Telephone
End Get
Set(ByVal Value As String)
m_Telephone = Value
End Set
End Property
Public Property Email() As String
Get
Return m_Email
End Get
Set(ByVal Value As String)
m_Email = Value
End Set
End Property
#End Region
#Region " Constructors "
Public Sub New()
MyBase.New()
End Sub
#End Region
#Region " Operator = Overloading "
''' <summary>
''' Overloading = operator is as simple as creating a method.
''' In fact, operator overloads are really just methods created with the Operator keyword
''' Note that the comaprison excludes CustID field, which is the primary key
''' </summary>
''' <param name="lhs"></param>
''' <param name="rhs"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Operator =(ByVal lhs As Customer, ByVal rhs As Customer) As Boolean
If lhs Is Nothing OrElse rhs Is Nothing Then Return False
If lhs.FirstName.ToUpper() <> rhs.FirstName.ToUpper() Then Return False
If lhs.LastName.ToUpper() <> rhs.LastName.ToUpper() Then Return False
If lhs.Address.ToUpper() <> rhs.Address.ToUpper() Then Return False
If lhs.Telephone <> rhs.Telephone Then Return False
If lhs.Email <> rhs.Email Then Return False
Return True
End Operator
''' <summary>
''' When you define = operator, VB requires that you also
''' define the operator for the inverse operation (not equal to) operator.
''' Simply reverse the = operand and use the not equal to operator
''' </summary>
''' <param name="lhs"></param>
''' <param name="rhs"></param>
''' <returns></returns>
''' <remarks></remarks>
Public Shared Operator <>(ByVal lhs As Customer, ByVal rhs As Customer) As Boolean
Return Not lhs = rhs
End Operator
#End Region
#Region " Methods "
#End Region
End Class
You can now use equal to (=) as shown in the following code
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
'Get the previously saved customer 101, by user one.
'For simplicity, we will just assign this customer as follows
Dim oCustByUser1 As New Customer() _
With {.CustID = 101, .FirstName = "Wilson", .LastName = "Kutegeka", _
.Address = "Kampala", .Telephone = "+256 772 609113"}
'Pick the new customer record 101, by user two from your interface.
'For simplicity, we will assign this customer as well
Dim oCustByUser2 As New Customer() _
With {.CustID = 101, .FirstName = "Wilson", .LastName = "Kutegeka", _
.Address = "Kampala-Uganda", .Telephone = "+256 772 609113"}
'This will not verify because adress is not the same
If oCustByUser1 = oCustByUser2 Then
'Update the first entry to verified
MessageBox.Show("Verified!")
Else
'Check to see which fields are not the same and report to user
MessageBox.Show("Some Fields are not the same!")
End If
End Sub
Introduction
This code will allow you to Export data from different data sources such as DataTable, DataGridView, and ListView. to Excel. One key thing about it is that it will not convert strings with leading zero to numeric. i.e. 0747 will export as is and not as 747 as it’s usually the case when you send data to excel.
It will also not export data from columns of DataGridView that are not visible.
''' <summary>
''' Exports the supplied data to excel, displaying supplied caption for caption
''' Allows mainly DataTable, DataGridView, and ListView
''' </summary>
''' <param name="data"></param>
''' <param name="caption"></param>
''' <remarks></remarks>
Public Sub ExportToExcel(ByVal data As Object, ByVal caption As String)
Dim excelApp As New Excel.Application
Dim excelBook As Excel.Workbook = excelApp.Workbooks.Add
Dim excelWorksheet As Excel.Worksheet = CType(excelBook.Worksheets(1), Excel.Worksheet)
Dim excelRange As Excel.Range
Dim cellColumnIndex As Integer
Try
If data Is Nothing Then Return
If String.IsNullOrEmpty(caption) Then caption = "Data"
' Start Excel and get application object
excelApp.Visible = True
excelApp.Caption = caption
excelWorksheet.Name = caption
excelApp.Cursor = Excel.XlMousePointer.xlWait
'excelApp.WindowState = Excel.XlWindowState.xlMinimized
If TypeOf data Is DataTable Then
Dim _data As DataTable = CType(data, DataTable)
If _data.Columns.Count < 1 Then Return
' Set the table headers from the column names
For columnIndex As Integer = 0 To _data.Columns.Count - 1
excelWorksheet.Cells(1, (columnIndex + 1)) = _data.Columns(columnIndex).ColumnName
Next
' Assign the headers as bold
excelRange = excelWorksheet.Range(excelWorksheet.Cells(1, 1), excelWorksheet.Cells(1, _data.Columns.Count))
excelRange.Font.Bold = True
For rowIndex As Integer = 0 To _data.Rows.Count - 1
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If IsDBNull(_data.Rows(rowIndex).Item(columnIndex)) Then
excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = String.Empty
ElseIf _data.Columns(columnIndex).DataType.FullName.Equals(GetType(String).FullName) Then
excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = "'" & CStr(_data.Rows(rowIndex).Item(columnIndex))
Else : excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = _data.Rows(rowIndex).Item(columnIndex)
End If
Next
Next
' Autofit the columns
excelRange.EntireColumn.AutoFit()
ElseIf TypeOf data Is DataGridView Then
Dim _data As DataGridView = CType(data, DataGridView)
If _data.Columns.Count < 1 Then Return
' Set the table headers from the column names
cellColumnIndex = 0
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If _data.Columns(columnIndex).Visible = False Then Continue For
cellColumnIndex += 1
excelWorksheet.Cells(1, (cellColumnIndex)) = _data.Columns(columnIndex).HeaderText
Next
' set the header row bold
excelRange = excelWorksheet.Range(excelWorksheet.Cells(1, 1), excelWorksheet.Cells(1, _data.Columns.Count))
excelRange.Font.Bold = True
'Export the base data
For rowIndex As Integer = 0 To _data.Rows.Count - 1
cellColumnIndex = 0
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If _data.Columns(columnIndex).Visible = False Then Continue For
cellColumnIndex += 1
If IsDBNull(_data.Item(columnIndex, rowIndex).Value) Then
excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = String.Empty
ElseIf Not _data.Columns(columnIndex).ValueType Is Nothing _
AndAlso _data.Columns(columnIndex).ValueType.FullName.Equals(GetType(String).FullName) Then
excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = "'" & CStr(_data.Item(columnIndex, rowIndex).Value)
Else : excelWorksheet.Cells(rowIndex + 2, cellColumnIndex) = _data.Item(columnIndex, rowIndex).Value
End If
Next
Next
' Autofit the columns
excelRange.EntireColumn.AutoFit()
ElseIf TypeOf data Is ListView Then
Dim _data As ListView = CType(data, ListView)
If _data.Columns.Count < 1 Then Return
' Set the table headers from the column names
For columnIndex As Integer = 0 To _data.Columns.Count - 1
excelWorksheet.Cells(1, (columnIndex + 1)) = _data.Columns(columnIndex).Text
Next
' Assign the headers as bold
excelRange = excelWorksheet.Range(excelWorksheet.Cells(1, 1), excelWorksheet.Cells(1, _data.Columns.Count))
excelRange.Font.Bold = True
For rowIndex As Integer = 0 To _data.Items.Count - 1
If IsDBNull(_data.Items(rowIndex).Text) Then
excelWorksheet.Cells(rowIndex + 2, 1) = String.Empty
Else : excelWorksheet.Cells(rowIndex + 2, 1) = "'" & _data.Items(rowIndex).Text
End If
For columnIndex As Integer = 0 To _data.Columns.Count - 1
If IsDBNull(_data.Items(rowIndex).SubItems(columnIndex).Text) Then
excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = String.Empty
Else : excelWorksheet.Cells(rowIndex + 2, columnIndex + 1) = "'" & _data.Items(rowIndex).SubItems(columnIndex).Text
End If
Next
Next
' Autofit the columns
excelRange.EntireColumn.AutoFit()
ElseIf TypeOf data Is DataGrid Then
Dim _data As DataGrid = CType(data, DataGrid)
' add other data sources
End If
' Make sure Excel is visible and give the user control
excelApp.Visible = True
excelApp.UserControl = True
Catch ex As Exception
Throw ex
Finally
excelApp.Cursor = Excel.XlMousePointer.xlDefault
End Try
End Sub
Wilson Kutegeka | Microsoft MVP - VB
Developer | Promoter | ClinicMaster Software
Cel: +256 772 609113 | Web: www.clinicmaster.net
