Solved

GetStoredProcCommand need to create in class DBAccess namespace Namespace CustomerList.DAL

Posted on 2007-03-27
1
1,347 Views
Last Modified: 2008-01-09
GetStoredProcCommand not working.

C:\SourceControl_mcupryk\InHouseDev\APPS\MKT0003 - CustomerList\src\CustomerList\App_Code\BOL\Division.vb(261,0): error BC30456: 'GetStoredProcCommand' is not a member of 'CustomerList.DAL.DBAccess'.

C:\SourceControl_mcupryk\InHouseDev\APPS\MKT0003 - CustomerList\src\CustomerList\App_Code\BOL\Division.vb(264,0): error BC30311: Value of type 'System.Data.Common.DbCommand' cannot be converted to 'String'.

Dim db As DAL.DBAccess = New DAL.DBAccess


Try

'We get the divisions

cmd = db.GetStoredProcCommand("PARAM_TWD.dbo.usp_GetDivisions")

'Temporarilly put the results in a dataset so we can check for valid data

tmpds = db.ExecuteDataSet(cmd)

'We must ensure that only one table was returned in the execution of the query

If tmpds.Tables.Count.Equals(1) Then



My module



Namespace CustomerList.DAL

Public Class DBAccess

Private cmd As IDbCommand = New SqlCommand()

Private handleErrors As Boolean = False

Private strLastError As String = ""

Private strConnectionString As String = ""

Public Sub New()

Dim objConnectionStringSettings As ConnectionStringSettings = ConfigurationManager.ConnectionStrings("connectionstring")

strConnectionString = objConnectionStringSettings.ConnectionString

Dim cnn As New SqlConnection()

cnn.ConnectionString = strConnectionString

cmd.Connection = cnn

cmd.CommandType = CommandType.StoredProcedure

End Sub

Public Function ExecuteReader() As IDataReader

Dim reader As IDataReader = Nothing

Try

Me.Open()

reader = cmd.ExecuteReader(CommandBehavior.CloseConnection)

Catch ex As Exception

If handleErrors Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return reader

End Function

Public Function ExecuteReader(ByVal commandtext As String) As IDataReader

Dim reader As IDataReader = Nothing

Try

cmd.CommandText = commandtext

reader = Me.ExecuteReader()

Catch ex As Exception

If (handleErrors) Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return reader

End Function

Public Function ExecuteScalar() As Object

Dim obj As Object = Nothing

Try

Me.Open()

obj = cmd.ExecuteScalar()

Me.Close()

Catch ex As Exception

If handleErrors Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return obj

End Function

Public Function ExecuteScalar(ByVal commandtext As String) As Object

Dim obj As Object = Nothing

Try

cmd.CommandText = commandtext

obj = Me.ExecuteScalar()

Catch ex As Exception

If (handleErrors) Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return obj

End Function

Public Function ExecuteNonQuery() As Integer

Dim i As Integer = -1

Try

Me.Open()

i = cmd.ExecuteNonQuery()

Me.Close()

Catch ex As Exception

If handleErrors Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return i

End Function





Public Function ExecuteNonQuery(ByVal commandtext As String) As Integer

Dim i As Integer = -1

Try

cmd.CommandText = commandtext

i = Me.ExecuteNonQuery()

Catch ex As Exception

If handleErrors Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return i

End Function

Public Function ExecuteDataSet() As DataSet

Dim da As SqlDataAdapter = Nothing

Dim ds As DataSet = Nothing

Try

da = New SqlDataAdapter()

da.SelectCommand = CType(cmd, SqlCommand)

ds = New DataSet()

da.Fill(ds)

Catch ex As Exception

If (handleErrors) Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return ds

End Function

Public Function ExecuteDataSet(ByVal commandtext As String) As DataSet

Dim ds As DataSet = Nothing

Try

cmd.CommandText = commandtext

ds = Me.ExecuteDataSet()

Catch ex As Exception

If handleErrors Then

strLastError = ex.Message

Else

Throw

End If

End Try

Return ds

End Function

Public Property CommandText() As String

Get

Return cmd.CommandText

End Get

Set(ByVal value As String)

cmd.CommandText = value

cmd.Parameters.Clear()

End Set

End Property

Public ReadOnly Property Parameters() As IDataParameterCollection

Get

Return cmd.Parameters

End Get

End Property

Public Sub AddParameter(ByVal paramname As String, ByVal paramvalue As Object)

Dim param As SqlParameter = New SqlParameter(paramname, paramvalue)

cmd.Parameters.Add(param)

End Sub

Public Sub AddParameter(ByVal param As IDataParameter)

cmd.Parameters.Add(param)

End Sub

Public Property ConnectionString() As String

Get

Return strConnectionString

End Get

Set(ByVal value As String)

strConnectionString = value

End Set

End Property

Private Sub Open()

cmd.Connection.Open()

End Sub

Private Sub Close()

cmd.Connection.Close()

End Sub

Public Property HandleExceptions() As Boolean

Get

Return handleErrors

End Get

Set(ByVal value As Boolean)

handleErrors = value

End Set

End Property

Public ReadOnly Property LastError() As String

Get

Return strLastError

End Get

End Property

Public Sub Dispose()

cmd.Dispose()

End Sub

End Class

End Namespace

I have a class that does all my sql stuff.
I need command GetStoredProcCommand(sqlCommand)
in my class dbaccess above
0
Comment
Question by:mathieu_cupryk
1 Comment
 
LVL 2

Accepted Solution

by:
jonshern earned 500 total points
ID: 18805678
It seems like it would be easier just to use the Data Access Application Block in the Enterprise Library.
The execute stored proc, is already defined.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Parse the OuterHTML Data in the txtfile using c# 4 48
Not showing page correctly 3 30
Connection String 16 43
Web Form VB.Net  import CSV 4 24
Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
Introduction This article series is supposed to shed some light on the use of IDisposable and objects that inherit from it. In essence, a more apt title for this article would be: using (IDisposable) {}. I’m just not sure how many people would ge…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now