Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > Execute Stored Procedure

Reply
Thread Tools

Execute Stored Procedure

 
 
Dikkuuhh
Guest
Posts: n/a
 
      08-23-2006
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

 
Reply With Quote
 
 
 
 
Vadivel Kumar
Guest
Posts: n/a
 
      08-23-2006
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/de...gn.4.366645.13
http://www.codeproject.com/cs/databa...ion_Blocks.asp
http://msdn.microsoft.com/practices/

--
Vadivel Kumar
http://www.vadivelk.net
http://www.velocityreviews.com/forums/(E-Mail Removed) (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
>

 
Reply With Quote
 
 
 
 
Dikkuuhh
Guest
Posts: n/a
 
      08-23-2006
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/de...gn.4.366645.13
> http://www.codeproject.com/cs/databa...ion_Blocks.asp
> http://msdn.microsoft.com/practices/
>
> --
> Vadivel Kumar
> http://www.vadivelk.net
> (E-Mail Removed) (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
> >


 
Reply With Quote
 
Vadivel Kumar
Guest
Posts: n/a
 
      08-23-2006
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
(E-Mail Removed) (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/de...gn.4.366645.13
>> http://www.codeproject.com/cs/databa...ion_Blocks.asp
>> http://msdn.microsoft.com/practices/
>>
>> --
>> Vadivel Kumar
>> http://www.vadivelk.net
>> (E-Mail Removed) (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
>>>

>

 
Reply With Quote
 
Dikkuuhh
Guest
Posts: n/a
 
      08-24-2006
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
> (E-Mail Removed) (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/de...gn.4.366645.13
> >> http://www.codeproject.com/cs/databa...ion_Blocks.asp
> >> http://msdn.microsoft.com/practices/
> >>
> >> --
> >> Vadivel Kumar
> >> http://www.vadivelk.net
> >> (E-Mail Removed) (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
> >>>

> >


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P ASP .Net 0 06-19-2006 01:19 PM
stored procedure takes too much time to execute nicholas ASP .Net 7 08-03-2005 07:20 PM
Stored Procedure vs direct execute SQL =?Utf-8?B?SklNLkgu?= ASP .Net 7 05-27-2005 10:12 PM
Help with Code to Execute a Stored Procedure Jeff Thur ASP .Net 2 02-07-2005 08:58 PM
How can I execute a stored procedure in .net? FireListen ASP .Net 1 06-27-2003 01:39 PM



Advertisments