Solved

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

Posted on 2007-03-27
1
1,356 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

This article introduced a TextBox that supports transparent background.   Introduction TextBox is the most widely used control component in GUI design. Most GUI controls do not support transparent background and more or less do not have the…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.

772 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