use this as your starting point:
Imports System.Xml
Imports System.IO
Imports System.Data.SqlClient
Imports System.Text
Public NotInheritable Class XmlReaderAdapter
Private Sub New()
End Sub
Public Shared Function GetString(ByVal command As SqlCommand, Optional ByVal rootName As String = "root") As String
Return GetString(command, Encoding.UTF8, rootName)
End Function
Public Shared Function GetString(ByVal command As SqlCommand, ByVal encoding As System.Text.Encoding, Optional ByVal rootName As String = "root") As String
Dim memory As New MemoryStream
Dim dataReader As XmlReader
Dim writer As New XmlTextWriter(memory, encoding)
Dim reader As New StreamReader(memory)
writer.WriteStartDocument(
writer.WriteStartElement(r
Try
command.Connection.Open()
dataReader = command.ExecuteXmlReader()
dataReader.Read()
While dataReader.Read
writer.WriteRaw(dataReader
End While
Catch ex As Exception
Finally
If Not dataReader Is Nothing Then
dataReader.Close()
End If
End Try
writer.WriteEndDocument()
writer.Flush()
memory.Position = 0
Dim value As String = reader.ReadToEnd()
writer.Close()
reader.Close()
Return value
End Function
End Class
Main Topics
Browse All Topics
by: ptakjaPosted on 2005-05-06 at 19:54:26ID: 13949209
Pretty straight forward. Here you go:
Here's a couple routines I use for this sort of thing:
'*************************
' ExecQuery
' ABSTRACT: Executes a stored procedure against the Eisemann database and returns
' a NEW Dataset with the selected data.
'
' INPUT PARMS: ProcedureName Name of Stored Procedure to execute
' Parms Array of SqlParameter objects that will be passed into the
' stored procedure.
'
' RETURNS: DataSet populated with results from stored procedure execution.
'
'*************************
Private Overloads Function ExecQuery(ByVal ProcedureName As String, ByVal Parms As SqlParameter()) As DataSet
Dim dsDataSet As New DataSet
' Configure the SqlCommand object
With _cmdSqlCommand
.CommandType = CommandType.StoredProcedur
.CommandText = ProcedureName 'Specify stored procedure to run
' Clear any previous parameters from the Command object
Call .Parameters.Clear()
' Loop through parmameter collection adding parameters to the command object
If Not (Parms Is Nothing) Then
For Each sqlParm As SqlParameter In Parms
_cmdSqlCommand.Parameters.
Next
End If
End With
' Configure Adapter to use newly created command object and fill the dataset.
_adpAdapter.SelectCommand = _cmdSqlCommand
_adpAdapter.Fill(dsDataSet
Return dsDataSet
End Function
'*************************
' ExecNonQuery
' ABSTRACT: Executeds a non-query without any parameters.
'
' INPUT PARMS: ProcedureName stored procedure to execute.
'
' RETURNS: Integer indicating how many rows were affected by the non-query.
'
' Copyright © 2005 by Corning, Inc.
'*************************
Private Overloads Function ExecNonQuery(ByVal ProcedureName As String) As Integer
Return ExecNonQuery(ProcedureName
End Function
'*************************
' ExecNonQuery
' ABSTRACT: Executes a non-query stored procedure agains the Eisemann database.
'
' INPUT PARMS: ProcedureName Name of stored procedure to execute
' Parms Collection of SqlParameter objects used as arguments for
' the stored procedure.
'
' RETURNS: An integer containing the number of rows affected by the Stored Procedure.
'
' Copyright © 2005 by Corning, Inc.
'*************************
Private Overloads Function ExecNonQuery(ByVal ProcedureName As String, ByVal Parms As SqlParameter()) As Integer
Dim intRowsAffected As Integer
' Configure the _cmdSqlCommand object
With _cmdSqlCommand
.CommandType = CommandType.StoredProcedur
.CommandText = ProcedureName 'Specify procedure to run
' Clear any previous parameters from the command object
Call .Parameters.Clear()
' Loop through parmameter collection, if defined, adding parameters to the command object
If Not (Parms Is Nothing) Then
For Each sqlParm As SqlParameter In Parms
_cmdSqlCommand.Parameters.
Next
End If
End With
If _cnnMyConnection.State <> ConnectionState.Open Then
Call _cnnMyConnection.Open()
End If
' Execute the procedure
intRowsAffected = _cmdSqlCommand.ExecuteNonQ
Return intRowsAffected 'Return the number of rows affected by procedure
End Function
To use these guys you create a sub like this:
Public Function GetDemographics(ClientID as String, FirstName as string, LastName as string, MiddleName as string, SSN as string, Bday as Date, Gender as string) As DataTable
Dim MyDataSet as DataSet
Dim Parms(6) As SqlParameter
Parms(0) = New SqlParameter("@clientid", ClientID)
Parms(1) = New SqlParameter("@firstname",
Parms(2) = New SqlParameter("@lastname", LastName)
Parms(3) = New SqlParameter("@middlename"
Parms(4) = New SqlParameter("@SSN", SSN)
Parms(5) = New SqlParameter("@Birthdate",
Parms(6) = New SqlParameter("@Gender", Gender)
Return ExecuteQuery("SP_OESS_Get_
End function
You would need to create your SqlConnection object and a SqlDataAdapter object named _adpAdapter in order for this code to work. But this should get you started.