Hi, all,
I wrote the following 3 functions in VB.NET. I think it
might be useful for programmers new to ADO.NET.
You just include this in a VB module. Then it can be
accessed in whole project.
These functions are for sql server. You can easily modify
them for oledb.
Bin Song, MCP
'Definition of CONNECTION_STRING
Public Const CONNECTION_STRING = "data
source=MYSERVENAME;initial catalog=MYDATABASENAME;user
id=MYUSERNAME;password=MYPASSWORD;"
'Function to return a dataset
'Syntax: Dim ds as DataSet = GetDataSet("Select * From
tblCustomer")
Public Function GetDataSet(ByVal sqlSelect As String)
As DataSet
Dim sqlCnn As SqlConnection
Dim sqlAdp As SqlDataAdapter
Dim sqlDst As DataSet
Try
sqlCnn = New SqlConnection(CONNECTION_STRING)
sqlCnn.Open()
sqlAdp = New SqlDataAdapter(sqlSelect, sqlCnn)
sqlDst = New DataSet()
sqlAdp.Fill(sqlDst)
GetDataSet = sqlDst
sqlAdp.Dispose()
sqlCnn.Close()
sqlCnn.Dispose()
Catch eGetDataSet As Exception
Throw eGetDataSet
End Try
End Function
'Function to return a data reader
'Syntax: Dim ds as sqlDataReader = GetDataReader("Select *
From tblCustomer")
Public Function GetDataReader(ByVal sqlSelect As
String) As SqlDataReader
'Close the datareader after Using this function to
get a datareader
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Try
sqlCnn = New SqlConnection(CONNECTION_STRING)
sqlCnn.Open()
sqlCmd = New SqlCommand(sqlSelect, sqlCnn)
GetDataReader = sqlCmd.ExecuteReader
(CommandBehavior.CloseConnection)
Catch eGetDataReader As Exception
Throw eGetDataReader
End Try
End Function
'sub to execute a data process query
'Syntax: ExecNonQuery("Delete * From tblCustomer")
Public Sub ExecNonQuery(ByVal sqlNonQuery As String)
Dim sqlCnn As SqlConnection
Dim sqlCmd As SqlCommand
Try
sqlCnn = New SqlConnection(CONNECTION_STRING)
sqlCnn.Open()
sqlCmd = New SqlCommand(sqlNonQuery, sqlCnn)
sqlCmd.ExecuteNonQuery()
sqlCmd.Dispose()
sqlCnn.Close()
sqlCnn.Dispose()
Catch eExecNonQuery As Exception
Throw eExecNonQuery
End Try
End Sub