Public Sub MultipleTablesInSingleDataSet()
Dim _ds As New DataSet("AdventureWorks")
Try
Using _cn As New SqlConnection("Data Source=DBServer;Initial Catalog=AdventureWorks;User ID=dbuserid;Password=dbpasswd")
_cn.Open()
Using _adapterDepartment As New SqlDataAdapter("SELECT * FROM HumanResources.Department", _cn)
_ds.Tables.Add(New DataTable("Department"))
_adapterDepartment.Fill(_ds.Tables(0))
End Using
Using _adapterEmployee As New SqlDataAdapter("SELECT * FROM HumanResources.Employee", _cn)
_ds.Tables.Add(New DataTable("Employee"))
_adapterEmployee.Fill(_ds.Tables(1))
End Using
Using _adapterEmployeeAddress As New SqlDataAdapter("SELECT * FROM HumanResources.EmployeeAddress", _cn)
_ds.Tables.Add(New DataTable("EmployeeAddress"))
_adapterEmployeeAddress.Fill(_ds.Tables(2))
End Using
Using _adapterEmployeeDepartmentHistory As New SqlDataAdapter("SELECT * FROM HumanResources.EmployeeDepartmentHistory", _cn)
_ds.Tables.Add(New DataTable("EmployeeDepartmentHistory"))
_adapterEmployeeDepartmentHistory.Fill(_ds.Tables(3))
End Using
_cn.Close()
End Using
Catch ex As SqlException
MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "App Error")
End Try
End Sub
Public Sub MultipleDataSets()
Dim _dsDepartment As New DataSet("Department")
Dim _dsEmployee As New DataSet("Employee")
Dim _dsEmployeeAddress As New DataSet("EmployeeAddress")
Dim _dsEmployeeDepartmentHistory As New DataSet("EmployeeDepartmentHistory")
Try
Using _cn As New SqlConnection("Data Source=DBServer;Initial Catalog=AdventureWorks;User ID=dbuserid;Password=dbpasswd")
_cn.Open()
Using _adapter As New SqlDataAdapter()
Using _cmd As New SqlCommand("SELECT * FROM HumanResources.Department", _cn)
_adapter.SelectCommand = _cmd
_adapter.Fill(_dsDepartment)
End Using
Using _cmd As New SqlCommand("SELECT * FROM HumanResources.Employee", _cn)
_adapter.SelectCommand = _cmd
_adapter.Fill(_dsEmployee)
End Using
Using _cmd As New SqlCommand("SELECT * FROM HumanResources.EmployeeAddress", _cn)
_adapter.SelectCommand = _cmd
_adapter.Fill(_dsEmployeeAddress)
End Using
Using _cmd As New SqlCommand("SELECT * FROM HumanResources.EmployeeDepartmentHistory", _cn)
_adapter.SelectCommand = _cmd
_adapter.Fill(_dsEmployeeDepartmentHistory)
End Using
End Using
_cn.Close()
End Using
Catch ex As SqlException
MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL Error")
Catch ex As Exception
MsgBox(ex.Message, MsgBoxStyle.Critical, "App Error")
End Try
End Sub
Public Function GetDataSet(query As String) As DataSet
Dim _return As DataSet = Nothing
Try
Using _cn As New SqlConnection("Connection_String")
_cn.Open()
Using _cmd As New SqlCommand(query, _cn)
Using _adapter As New SqlDataAdapter(_cmd)
_return = New DataSet()
_adapter.Fill(_return)
End Using
End Using
_cn.Close()
End Using
Catch ex As SqlException
' log sql specific error
Catch ex As Exception
' log geenral error
End Try
Return _return
End Function
Using ds = GetDataSet(String.Format("SELECT * FROM termsummarycontrol WHERE (product_code='{0}'", txtProductCode.Text))
If ds IsNot Nothing Then
' Do your work here because you know the DataSet is not empty
If ds.Tables.Count() > 0 And ds.Tables(0).Rows.Count() > 0 Then
' Now you know you have at least table and it has at least one row
End If
End If
End Using