Execute Stored Procedure

Discussion in 'ASP .Net' started by Dikkuuhh, Aug 23, 2006.

  1. Dikkuuhh

    Dikkuuhh Guest

    Hi, i have a function that can execute stored procedures, but i don`t
    know how i can give it back to the other classes. I did it in an
    ArrayList now, but how can i do it better, i can`t make it variable how
    much values i get....

    This is my code:

    Public Function ExSP(ByVal StoredProcedure As String, ByVal
    Parameters As ArrayList, ByVal ParamName As ArrayList) As ArrayList

    m_strSQLConnection = New SqlConnection("User ID=oriflame02;" &
    _
    "Password=Ujn87-yt#;" & _
    "Server=kemal.interconnect.nl,1433;" & _
    "Database=oriflame02;" & _
    "Connection Lifetime=0")
    Dim cmdAuthors As New SqlCommand(StoredProcedure,
    m_strSQLConnection)
    cmdAuthors.CommandType = CommandType.StoredProcedure

    Dim i As Integer
    If Parameters.Count = ParamName.Count Then
    For i = 0 To Parameters.Count - 1
    cmdAuthors.Parameters.AddWithValue(ParamName.Item(i),
    Parameters.Item(i))
    Next i
    Else

    End If

    Dim drAuthors As SqlDataReader
    m_strSQLConnection.Open()
    drAuthors = cmdAuthors.ExecuteReader()
    i = 0
    Do While drAuthors.Read()

    ArList.Add(drAuthors.GetValue(0) & "<br/>")
    ArList.Add(drAuthors.GetValue(1) & "<br/>")
    ArList.Add(drAuthors.GetValue(2) & "<br/>")
    ArList.Add(drAuthors.GetValue(3) & "<br/>")
    ArList.Add(drAuthors.GetValue(4) & "<br/>")
    ArList.Add(drAuthors.GetValue(5) & "<br>")

    Loop
    drAuthors.Close()
    m_strSQLConnection.Close()

    Return ArList
    End Function





    Can somebody help me, please??

    Thnx

    Koen
    Dikkuuhh, Aug 23, 2006
    #1
    1. Advertising

  2. I think the better way would be creating an container class for the
    Author table data.

    And, you should learn about how to use DAL layer and BL layer in your
    application, i can give you few links where you can learn about it
    before writing some code.

    http://www.15seconds.com/issue/030317.htm
    http://forums.asp.net/thread/1355418.aspx
    http://discuss.joelonsoftware.com/default.asp?design.4.366645.13
    http://www.codeproject.com/cs/database/MS_Application_Blocks.asp
    http://msdn.microsoft.com/practices/

    --
    Vadivel Kumar
    http://www.vadivelk.net
    (remove "online.")

    Dikkuuhh wrote:
    > Hi, i have a function that can execute stored procedures, but i don`t
    > know how i can give it back to the other classes. I did it in an
    > ArrayList now, but how can i do it better, i can`t make it variable how
    > much values i get....
    >
    > This is my code:
    >
    > Public Function ExSP(ByVal StoredProcedure As String, ByVal
    > Parameters As ArrayList, ByVal ParamName As ArrayList) As ArrayList
    >
    > m_strSQLConnection = New SqlConnection("User ID=oriflame02;" &
    > _
    > "Password=Ujn87-yt#;" & _
    > "Server=kemal.interconnect.nl,1433;" & _
    > "Database=oriflame02;" & _
    > "Connection Lifetime=0")
    > Dim cmdAuthors As New SqlCommand(StoredProcedure,
    > m_strSQLConnection)
    > cmdAuthors.CommandType = CommandType.StoredProcedure
    >
    > Dim i As Integer
    > If Parameters.Count = ParamName.Count Then
    > For i = 0 To Parameters.Count - 1
    > cmdAuthors.Parameters.AddWithValue(ParamName.Item(i),
    > Parameters.Item(i))
    > Next i
    > Else
    >
    > End If
    >
    > Dim drAuthors As SqlDataReader
    > m_strSQLConnection.Open()
    > drAuthors = cmdAuthors.ExecuteReader()
    > i = 0
    > Do While drAuthors.Read()
    >
    > ArList.Add(drAuthors.GetValue(0) & "<br/>")
    > ArList.Add(drAuthors.GetValue(1) & "<br/>")
    > ArList.Add(drAuthors.GetValue(2) & "<br/>")
    > ArList.Add(drAuthors.GetValue(3) & "<br/>")
    > ArList.Add(drAuthors.GetValue(4) & "<br/>")
    > ArList.Add(drAuthors.GetValue(5) & "<br>")
    >
    > Loop
    > drAuthors.Close()
    > m_strSQLConnection.Close()
    >
    > Return ArList
    > End Function
    >
    >
    >
    >
    >
    > Can somebody help me, please??
    >
    > Thnx
    >
    > Koen
    >
    Vadivel Kumar, Aug 23, 2006
    #2
    1. Advertising

  3. Dikkuuhh

    Dikkuuhh Guest

    The part of DAL and BLL is what i understand...

    I made a lot of multitier applications, this is my first in asp.net
    2.0, so thats why i can`t do it.....

    Koen


    Vadivel Kumar wrote:
    > I think the better way would be creating an container class for the
    > Author table data.
    >
    > And, you should learn about how to use DAL layer and BL layer in your
    > application, i can give you few links where you can learn about it
    > before writing some code.
    >
    > http://www.15seconds.com/issue/030317.htm
    > http://forums.asp.net/thread/1355418.aspx
    > http://discuss.joelonsoftware.com/default.asp?design.4.366645.13
    > http://www.codeproject.com/cs/database/MS_Application_Blocks.asp
    > http://msdn.microsoft.com/practices/
    >
    > --
    > Vadivel Kumar
    > http://www.vadivelk.net
    > (remove "online.")
    >
    > Dikkuuhh wrote:
    > > Hi, i have a function that can execute stored procedures, but i don`t
    > > know how i can give it back to the other classes. I did it in an
    > > ArrayList now, but how can i do it better, i can`t make it variable how
    > > much values i get....
    > >
    > > This is my code:
    > >
    > > Public Function ExSP(ByVal StoredProcedure As String, ByVal
    > > Parameters As ArrayList, ByVal ParamName As ArrayList) As ArrayList
    > >
    > > m_strSQLConnection = New SqlConnection("User ID=oriflame02;" &
    > > _
    > > "Password=Ujn87-yt#;" & _
    > > "Server=kemal.interconnect.nl,1433;" & _
    > > "Database=oriflame02;" & _
    > > "Connection Lifetime=0")
    > > Dim cmdAuthors As New SqlCommand(StoredProcedure,
    > > m_strSQLConnection)
    > > cmdAuthors.CommandType = CommandType.StoredProcedure
    > >
    > > Dim i As Integer
    > > If Parameters.Count = ParamName.Count Then
    > > For i = 0 To Parameters.Count - 1
    > > cmdAuthors.Parameters.AddWithValue(ParamName.Item(i),
    > > Parameters.Item(i))
    > > Next i
    > > Else
    > >
    > > End If
    > >
    > > Dim drAuthors As SqlDataReader
    > > m_strSQLConnection.Open()
    > > drAuthors = cmdAuthors.ExecuteReader()
    > > i = 0
    > > Do While drAuthors.Read()
    > >
    > > ArList.Add(drAuthors.GetValue(0) & "<br/>")
    > > ArList.Add(drAuthors.GetValue(1) & "<br/>")
    > > ArList.Add(drAuthors.GetValue(2) & "<br/>")
    > > ArList.Add(drAuthors.GetValue(3) & "<br/>")
    > > ArList.Add(drAuthors.GetValue(4) & "<br/>")
    > > ArList.Add(drAuthors.GetValue(5) & "<br>")
    > >
    > > Loop
    > > drAuthors.Close()
    > > m_strSQLConnection.Close()
    > >
    > > Return ArList
    > > End Function
    > >
    > >
    > >
    > >
    > >
    > > Can somebody help me, please??
    > >
    > > Thnx
    > >
    > > Koen
    > >
    Dikkuuhh, Aug 23, 2006
    #3
  4. Only thing I could understand from your code is that you want to return
    the data back in a proper or professional way rather than just an Array
    List.

    What i used to do use,

    1. I will create an Author class which has all the Author related data
    as a properties
    2. And, I will use this Author object in the ExSP function to store the
    data in a manner of ArrayList. I mean, this function will create n
    number of Author objects based on the row count and stores the Author
    object in a Array list and return it back to the caller method.

    So, this way I can have the Author's ArrayList to bind the data to the
    UI Controls.

    Let me know, if i understood propery or missing something.

    Thanks,
    --
    Vadivel Kumar
    http://www.vadivelk.net
    (remove "online.")

    Dikkuuhh wrote:
    > The part of DAL and BLL is what i understand...
    >
    > I made a lot of multitier applications, this is my first in asp.net
    > 2.0, so thats why i can`t do it.....
    >
    > Koen
    >
    >
    > Vadivel Kumar wrote:
    >> I think the better way would be creating an container class for the
    >> Author table data.
    >>
    >> And, you should learn about how to use DAL layer and BL layer in your
    >> application, i can give you few links where you can learn about it
    >> before writing some code.
    >>
    >> http://www.15seconds.com/issue/030317.htm
    >> http://forums.asp.net/thread/1355418.aspx
    >> http://discuss.joelonsoftware.com/default.asp?design.4.366645.13
    >> http://www.codeproject.com/cs/database/MS_Application_Blocks.asp
    >> http://msdn.microsoft.com/practices/
    >>
    >> --
    >> Vadivel Kumar
    >> http://www.vadivelk.net
    >> (remove "online.")
    >>
    >> Dikkuuhh wrote:
    >>> Hi, i have a function that can execute stored procedures, but i don`t
    >>> know how i can give it back to the other classes. I did it in an
    >>> ArrayList now, but how can i do it better, i can`t make it variable how
    >>> much values i get....
    >>>
    >>> This is my code:
    >>>
    >>> Public Function ExSP(ByVal StoredProcedure As String, ByVal
    >>> Parameters As ArrayList, ByVal ParamName As ArrayList) As ArrayList
    >>>
    >>> m_strSQLConnection = New SqlConnection("User ID=oriflame02;" &
    >>> _
    >>> "Password=Ujn87-yt#;" & _
    >>> "Server=kemal.interconnect.nl,1433;" & _
    >>> "Database=oriflame02;" & _
    >>> "Connection Lifetime=0")
    >>> Dim cmdAuthors As New SqlCommand(StoredProcedure,
    >>> m_strSQLConnection)
    >>> cmdAuthors.CommandType = CommandType.StoredProcedure
    >>>
    >>> Dim i As Integer
    >>> If Parameters.Count = ParamName.Count Then
    >>> For i = 0 To Parameters.Count - 1
    >>> cmdAuthors.Parameters.AddWithValue(ParamName.Item(i),
    >>> Parameters.Item(i))
    >>> Next i
    >>> Else
    >>>
    >>> End If
    >>>
    >>> Dim drAuthors As SqlDataReader
    >>> m_strSQLConnection.Open()
    >>> drAuthors = cmdAuthors.ExecuteReader()
    >>> i = 0
    >>> Do While drAuthors.Read()
    >>>
    >>> ArList.Add(drAuthors.GetValue(0) & "<br/>")
    >>> ArList.Add(drAuthors.GetValue(1) & "<br/>")
    >>> ArList.Add(drAuthors.GetValue(2) & "<br/>")
    >>> ArList.Add(drAuthors.GetValue(3) & "<br/>")
    >>> ArList.Add(drAuthors.GetValue(4) & "<br/>")
    >>> ArList.Add(drAuthors.GetValue(5) & "<br>")
    >>>
    >>> Loop
    >>> drAuthors.Close()
    >>> m_strSQLConnection.Close()
    >>>
    >>> Return ArList
    >>> End Function
    >>>
    >>>
    >>>
    >>>
    >>>
    >>> Can somebody help me, please??
    >>>
    >>> Thnx
    >>>
    >>> Koen
    >>>

    >
    Vadivel Kumar, Aug 23, 2006
    #4
  5. Dikkuuhh

    Dikkuuhh Guest

    I found something usefull..

    I`ve got the information into a dataset and give it back like that...
    With a dataset there are more usefull things......

    Thanks anyway

    Koen


    Vadivel Kumar wrote:
    > Only thing I could understand from your code is that you want to return
    > the data back in a proper or professional way rather than just an Array
    > List.
    >
    > What i used to do use,
    >
    > 1. I will create an Author class which has all the Author related data
    > as a properties
    > 2. And, I will use this Author object in the ExSP function to store the
    > data in a manner of ArrayList. I mean, this function will create n
    > number of Author objects based on the row count and stores the Author
    > object in a Array list and return it back to the caller method.
    >
    > So, this way I can have the Author's ArrayList to bind the data to the
    > UI Controls.
    >
    > Let me know, if i understood propery or missing something.
    >
    > Thanks,
    > --
    > Vadivel Kumar
    > http://www.vadivelk.net
    > (remove "online.")
    >
    > Dikkuuhh wrote:
    > > The part of DAL and BLL is what i understand...
    > >
    > > I made a lot of multitier applications, this is my first in asp.net
    > > 2.0, so thats why i can`t do it.....
    > >
    > > Koen
    > >
    > >
    > > Vadivel Kumar wrote:
    > >> I think the better way would be creating an container class for the
    > >> Author table data.
    > >>
    > >> And, you should learn about how to use DAL layer and BL layer in your
    > >> application, i can give you few links where you can learn about it
    > >> before writing some code.
    > >>
    > >> http://www.15seconds.com/issue/030317.htm
    > >> http://forums.asp.net/thread/1355418.aspx
    > >> http://discuss.joelonsoftware.com/default.asp?design.4.366645.13
    > >> http://www.codeproject.com/cs/database/MS_Application_Blocks.asp
    > >> http://msdn.microsoft.com/practices/
    > >>
    > >> --
    > >> Vadivel Kumar
    > >> http://www.vadivelk.net
    > >> (remove "online.")
    > >>
    > >> Dikkuuhh wrote:
    > >>> Hi, i have a function that can execute stored procedures, but i don`t
    > >>> know how i can give it back to the other classes. I did it in an
    > >>> ArrayList now, but how can i do it better, i can`t make it variable how
    > >>> much values i get....
    > >>>
    > >>> This is my code:
    > >>>
    > >>> Public Function ExSP(ByVal StoredProcedure As String, ByVal
    > >>> Parameters As ArrayList, ByVal ParamName As ArrayList) As ArrayList
    > >>>
    > >>> m_strSQLConnection = New SqlConnection("User ID=oriflame02;" &
    > >>> _
    > >>> "Password=Ujn87-yt#;" & _
    > >>> "Server=kemal.interconnect.nl,1433;" & _
    > >>> "Database=oriflame02;" & _
    > >>> "Connection Lifetime=0")
    > >>> Dim cmdAuthors As New SqlCommand(StoredProcedure,
    > >>> m_strSQLConnection)
    > >>> cmdAuthors.CommandType = CommandType.StoredProcedure
    > >>>
    > >>> Dim i As Integer
    > >>> If Parameters.Count = ParamName.Count Then
    > >>> For i = 0 To Parameters.Count - 1
    > >>> cmdAuthors.Parameters.AddWithValue(ParamName.Item(i),
    > >>> Parameters.Item(i))
    > >>> Next i
    > >>> Else
    > >>>
    > >>> End If
    > >>>
    > >>> Dim drAuthors As SqlDataReader
    > >>> m_strSQLConnection.Open()
    > >>> drAuthors = cmdAuthors.ExecuteReader()
    > >>> i = 0
    > >>> Do While drAuthors.Read()
    > >>>
    > >>> ArList.Add(drAuthors.GetValue(0) & "<br/>")
    > >>> ArList.Add(drAuthors.GetValue(1) & "<br/>")
    > >>> ArList.Add(drAuthors.GetValue(2) & "<br/>")
    > >>> ArList.Add(drAuthors.GetValue(3) & "<br/>")
    > >>> ArList.Add(drAuthors.GetValue(4) & "<br/>")
    > >>> ArList.Add(drAuthors.GetValue(5) & "<br>")
    > >>>
    > >>> Loop
    > >>> drAuthors.Close()
    > >>> m_strSQLConnection.Close()
    > >>>
    > >>> Return ArList
    > >>> End Function
    > >>>
    > >>>
    > >>>
    > >>>
    > >>>
    > >>> Can somebody help me, please??
    > >>>
    > >>> Thnx
    > >>>
    > >>> Koen
    > >>>

    > >
    Dikkuuhh, Aug 24, 2006
    #5
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. FireListen
    Replies:
    1
    Views:
    481
    James J. Foster
    Jun 27, 2003
  2. Jeff Thur
    Replies:
    2
    Views:
    1,011
    Guest
    Feb 7, 2005
  3. =?Utf-8?B?SklNLkgu?=

    Stored Procedure vs direct execute SQL

    =?Utf-8?B?SklNLkgu?=, May 27, 2005, in forum: ASP .Net
    Replies:
    7
    Views:
    5,020
    Robbe Morris [C# MVP]
    May 27, 2005
  4. nicholas
    Replies:
    7
    Views:
    593
    John.Net
    Aug 3, 2005
  5. Mike P
    Replies:
    0
    Views:
    3,277
    Mike P
    Jun 19, 2006
Loading...

Share This Page