SqlDataReader 3

[NaiDanac]

Is there a way to "clone" a sql data reader.

I have a lot of calls to the database to execute stored procedures. Everytime I need to execute a procedure I have to create a connection, open it, create a reader, create an sqlcommand, execute the command, save the result in the reader, then close the connection.

That's a lot of work everytime, so I've build a function to do that for me. All I have to do is to pass it the stored procedure name, and it does all of the above. Now my problem has to do with the reader. As you probably know, once the connection is closed, the reader is no longer available. My function is closing the connection, then returning the reader. If I don't close the connection (it stays open forever, which is not good!) Before closing the connection, can I "clone" (as was the case with recordsets in ASP) the reader (e.g. NewReader = Reader.Clone), then close the connection?

Is there a way I could do what I'm trying to do?

Thanks for your help
NaiDanac

 

[jmeckley]

why not dump your results to a data table, or array list and return that. Then you could close your connection after you transfer the data.

I built a class that sound very similar to yours. I pass a SP and an array list of input parameters. My functions return either a data table or an array list depending on whether it's a queried result, or non-queried result.

I also setup my function to allow me to control when to close the connection. There is a noticable difference between continually opening and closing the connection, and opening and closing once for a set of operations.

Jason Meckley
Database Analyst
WITF

 

[NaiDanac]

Jason,
That's exactly what I'm trying to build.

I created a structure of two strings, parameter and value, and I instantiate an array of that structure, and fill it with the par/value pairs. I pass the function the SP name, and the array.

Now, I don't know how what a data table is used for (forgive me, I'm new to .net) How can one read/write from a data table? Currently I read from the data reader using: myvariable = myReader("COLUMN_NAME")
Is reading from a data table similar?

Also, what the diff. between queried result, or non-queried result?

Thanks a lot for your help

 

[jmeckley]

Queried Result: Select statements
Non-Queried Results: Insert, Update, Delete statements, they modifiy information.

Here is the logic to my code
1. Set the connection string, conneciton object and timeout limit.
2. create the command object for the SP I want to execute
3. If select statement then create a data table
a. loop through column names of data read to create table columns
b. loop through records and append each row to the table
c. close reader
d. close connection (if necessary)
e. return table
4. If non query then loop through output values and append to array list.

This requires a SP in the database to get the parameters:

/*select all the parameters, data types, length and direction for a specific SP*/
ALTER Proc sp_GetParameters_s
 @SPName nvarchar(50) --Name of Stored Procedure
as
 SELECT	c.name as Parameter,
	t.name as DataType,
	c.prec as Length,
	c.isoutparam as Direction
 FROM	sysobjects o INNER JOIN syscolumns c
	ON o.id = c.id INNER JOIN systypes t
	ON c.xusertype = t.xusertype
 WHERE	o.name = @SPName
 ORDER BY c.colorder

Here is the code for .Net:

Imports System.Data.SqlClient
Imports System.Data.ConnectionState

Public Class SQLStoredProcedure
#Region "Properties"
    Private _CnnStr As String 'connection string
    Private _Cnn As New SqlConnection 'connection object
    Private _TimeOut As Integer 'seconds before a timeout occurs

    Public Property ConnectionString() As String
        Get
            Return _CnnStr
        End Get

        Set(ByVal Value As String)
            _CnnStr = Value
        End Set
    End Property

    Public ReadOnly Property ConnectionState() As String
        Get
            Return _Cnn.State.ToString
        End Get
    End Property

    Public Property TimeOut() As Integer
        Get
            Return _TimeOut
        End Get
        Set(ByVal Value As Integer)
            _TimeOut = Value
        End Set
    End Property
#End Region

#Region "Private Functions"
    Private Sub OpenConnection()
        If _Cnn.State = Closed Then _Cnn.Open() 'if the connect is closed, open it
    End Sub

    Private Sub CloseConnection(ByVal Close As Boolean)
        If Not _Cnn.State = Closed And Close Then _Cnn.Close() 'if the connection is not closed and you want to close it, then do so
    End Sub

    Private Function CreateCommand(ByVal SP As String, ByVal Data As ArrayList) As SqlCommand
        Dim CmdParameters As New SqlCommand
        Dim NewCommand As New SqlCommand
        Dim Reader As SqlDataReader
        Dim X As Integer

        With CmdParameters 'define SP to get parameters
            .CommandType = CommandType.StoredProcedure
            .CommandText = "sp_GetParameters_s"
            .Parameters.Add("@SPName", SqlDbType.NVarChar, 50).Value = SP
            .CommandTimeout = _TimeOut
            .Connection = _Cnn 'Connect
        End With

        With NewCommand 'define command object to return
            .CommandType = CommandType.StoredProcedure
            .CommandText = SP
            .CommandTimeout = _TimeOut
        End With

        Reader = CmdParameters.ExecuteReader
        While Reader.Read 'for each parameter
            If Reader.Item("Direction") = 0 Then 'create and assign if it has a non-null value
                If IsDBNull(Data.Item(X)) = False Then NewCommand.Parameters.Add(Reader.Item("Parameter"), ConvertString(Reader.Item("DataType")), Reader.Item("Length")).Value = Data.Item(X)
                X = X + 1 'go to the next item in the array of data
            Else 'create output parameter
                NewCommand.Parameters.Add(Reader.Item("Parameter"), ConvertString(Reader.Item("DataType")), Reader.Item("Length")).Direction = ParameterDirection.Output
            End If
        End While
        Reader.Close()

        Return NewCommand
    End Function

    Private Function ConvertString(ByVal DataType As String) As SqlDbType
        Select Case DataType 'convert sql data type to a object of type SqlDbType
            Case "bigint"
                ConvertString = SqlDbType.BigInt
            Case "binary"
                ConvertString = SqlDbType.Binary
            Case "bit"
                ConvertString = SqlDbType.Bit
            Case "char"
                ConvertString = SqlDbType.Char
            Case "datetime"
                ConvertString = SqlDbType.DateTime
            Case "decimal"
                ConvertString = SqlDbType.Decimal
            Case "float"
                ConvertString = SqlDbType.Float
            Case "image"
                ConvertString = SqlDbType.Image
            Case "int"
                ConvertString = SqlDbType.Int
            Case "money"
                ConvertString = SqlDbType.Money
            Case "nchar"
                ConvertString = SqlDbType.NChar
            Case "ntext"
                ConvertString = SqlDbType.NText
            Case "numberic"
                ConvertString = SqlDbType.Decimal
            Case "nvarchar"
                ConvertString = SqlDbType.NVarChar
            Case "real"
                ConvertString = SqlDbType.Real
            Case "smalldatetime"
                ConvertString = SqlDbType.SmallDateTime
            Case "samllint"
                ConvertString = SqlDbType.SmallInt
            Case "smallmoney"
                ConvertString = SqlDbType.SmallMoney
            Case "sql_variant"
                ConvertString = SqlDbType.Variant
            Case "sysname"
                ConvertString = SqlDbType.NVarChar
            Case "text"
                ConvertString = SqlDbType.Text
            Case "timestamp"
                ConvertString = SqlDbType.Timestamp
            Case "tinyint"
                ConvertString = SqlDbType.TinyInt
            Case "uniqueidentifier"
                ConvertString = SqlDbType.UniqueIdentifier
            Case "varbinary"
                ConvertString = SqlDbType.VarBinary
            Case "varchar"
                ConvertString = SqlDbType.VarChar
            Case "variant"
                ConvertString = SqlDbType.Variant
        End Select
        Return ConvertString
    End Function
#End Region

#Region "Public Functions"
    Public Sub New()

    End Sub

    Public Sub New(ByVal ConnectString As String, Optional ByVal TimeOut As Integer = 60)
        _CnnStr = ConnectString
        _Cnn.ConnectionString = _CnnStr
        _TimeOut = TimeOut
    End Sub

    Public Sub Close()
        CloseConnection(True) 'close connection
    End Sub

    Public Function GetData(ByVal StoredProcedure As String, ByVal Values As ArrayList, Optional ByVal Close As Boolean = True) As DataTable
        Dim Cmd As SqlCommand
        Dim Cnn As New SqlConnection(_CnnStr)
        Dim NewTable As New DataTable
        Dim Rdr As SqlDataReader
        Dim Row As DataRow
        Dim HeaderLoop, RowLoop As Integer 'count loops

        OpenConnection()
        Cmd = CreateCommand(StoredProcedure, Values) 'create command object
        Cmd.Connection = _Cnn
        NewTable.TableName = "tbl_" & StoredProcedure

        Rdr = Cmd.ExecuteReader
        For HeaderLoop = 0 To Rdr.FieldCount - 1 'create cloumns (name, data type)
            NewTable.Columns.Add(Rdr.GetName(HeaderLoop), Type.GetType(Rdr.GetFieldType(HeaderLoop).ToString))
        Next
        While Rdr.Read 'populate table
            Row = NewTable.NewRow 'create a new row to be added
            For RowLoop = 0 To Rdr.FieldCount - 1
                Row(RowLoop) = Rdr.Item(RowLoop) 'populate columns in the row
            Next
            NewTable.Rows.Add(Row) 'add the row
        End While
        Rdr.Close()
        CloseConnection(Close)

        Return NewTable
    End Function

    Public Function SetData(ByVal StoredProcedure As String, ByVal Values As ArrayList, Optional ByVal Close As Boolean = True) As ArrayList
        Dim OutputValues As New ArrayList
        Dim Cmd As SqlCommand
        Dim P As SqlParameter
        Dim X As Integer

        OpenConnection()
        Cmd = CreateCommand(StoredProcedure, Values) 'create command object
        Cmd.Connection = _Cnn

        Cmd.ExecuteNonQuery()
        For Each P In Cmd.Parameters 'for each output parameter add output value to array
            If P.Direction = ParameterDirection.Output Then
                OutputValues.Add(P.Value)
            End If
        Next
        CloseConnection(Close)

        Return OutputValues
    End Function
#End Region
End Class

Jason Meckley
Database Analyst
WITF

 

[NaiDanac]

Jason,
That was very helpful. Thank you very much. That deserves a star!

A few questions:
Why is SetData retuning an arraylist (OutputValues)? Is it just to ensure that you have written the correct values to the db?

Also, I'm assuming the arraylist would containg the "values" of the parameters in the same order as they are in the SP.


Thanks for you help
NaiDanac
P.S. I wish I could give you more stars!

 

[jmeckley]

yes the values in the output arrary list are in the order they appear in the SP.

You can have multiple Output parameters, so I append each value in the array list to capture them all.

Jason Meckley
Database Analyst
WITF

 

[Veep]

Nice.