Search:  
NamespacesDiscussions.NET v1.1Feedback
SV: Reusable method in C# to return the results of a Stored Procedure query - Help needed.
Messages   Related Types
This message was discovered on ASPFriends.com 'ngfx-sqlclient' list.


=?iso-8859-1?Q?Andr=E9_Colbi=F6rnsen?=
A DataView may well be used. Here is an example in VB.Net but I'm sure u
get the drift:

1.' Publicly dim a DataSet and a Dataview

Public dstClients As DataSet
Public dvwClients As DataView

2. 'The Function

Public Function BindMasterGrid() As DataView
Dim conFamily As SqlConnection = New SqlConnection(strConn)
Dim dadClients As SqlDataAdapter
dstClients = New DataSet()
Try
dadClients = New SqlDataAdapter("mySproc", conFamily)
dadClients.SelectCommand.CommandType CommandType.StoredProcedure
'If you need to use Parameters, do like this

'dadClients.SelectCommand.Parameters.Add("@yrParam",yrValue)
conFamily.Open()
dadClients.Fill(dstClients, "clients")

dvwClients = dstClients.Tables("clients").DefaultView()

Catch exp As Exception
Server.Transfer("error.aspx")
End Try

Return dvwClients
End Function

3. 'Bind the view to a grid

myDataGrid.Source = BindMasterGrid()
myDatagrid.DataBind()

Hth

Regards/Halsningar

Andre Colbiornsen
--------------------------------------
Sonnenburg Communications
Bergsgatan 3,
SE-211 54 Malmö
Sweden
Tel.: +46-(0)40-97 78 80
Fax.: +46-(0)40-97 78 80
Mob.: +46-(0)708-97 78 79
Mail: Click here to reveal e-mail address
Web.: www.sonnenburg.se
--------------------------------------
B2B Web agency - Specializing on .Net
--------------------------------------

-----Ursprungligt meddelande-----
Från: Phil Winstanley [mailto:Click here to reveal e-mail address]
Skickat: den 12 juni 2002 12:58
Till: ngfx-sqlclient
Ämne: [ngfx-sqlclient] Reusable method in C# to return the results of a
Stored Procedure query - Help needed.

Hi,

I’m trying to create a reusable method in C# to return the results of a
Stored Procedure query.

I’m running in to a little difficulty understanding which Data storage
object I should use to pass the returned information from my query back
through the method to whatever is calling it.

So far I have tried to use an SqlDataReader, but this has proved
fruitless as the SqlDataReader is Closed before it is returned through
the method: -

“Invalid attempt to FieldCount when reader is closed.”

Using the below method: -

public static SqlDataReader ReturnProjectDataReader()

{

string connectionstring = "user id=xxx;password=yyy;initial
catalog=db_project;data source=ch-cg-db-01;";

SqlConnection Conn = new SqlConnection(connectionstring);

SqlCommand Cmd = new SqlCommand("listallprojects",Conn);

Cmd.CommandType = CommandType.StoredProcedure;

SqlDataReader DR;

try

{

Conn.Open();

DR Cmd.ExecuteReader(CommandBehavior.CloseConnection);

Conn.Close();

}

finally

{

}

return DR;

}

I’m then calling the above code as so: -

SqlDataReader DR = Acclaim.ProjectManagement.ReturnProjectDataReader();

ProjectList.DataSource = DR;

ProjectList.DataTextField = ("project_name");

ProjectList.DataValueField = ("project_projectID");

ProjectList.DataBind();

What is the best way to achieve what I am trying to do, and why?

All and any help is appreciated.

Plip.

--

Phil Winstanley, Systems Architect.

Acclaim Studios Manchester Limited.

http://www.acclaim.com

--

| [ngfx-sqlclient] member Click here to reveal e-mail address = YOUR ID |
http://www.aspfriends.com/aspfriends/ngfx-sqlclient.asp = JOIN/QUIT
Reply to this message...
 
    
Phil Winstanley

Hello Again,

Ok, what I did was follow the suggestions of Kapil Nanchahal, as that =
meant changing the least amount of code - which works fine.

I have another question though, does me having picked a DataReader over =
a DataView have any implications?=20

(i.e. could choosing one potentially cause problems down the line?)

Plip.
--
Phil Winstanley, Super Hero.
Acclaim Studios Manchester.
http://www.acclaim.com
--

=A0=20
-----Original Message-----
From: Andr=E9 Colbi=F6rnsen [mailto:Click here to reveal e-mail address]=20
Sent: 12 June 2002 12:36
To: ngfx-sqlclient
Subject: [ngfx-sqlclient] SV: Reusable method in C# to return the =
results of a Stored Procedure query - Help needed.

A DataView may well be used. Here is an example in VB.Net but I'm sure u =
get the drift:
=A0
1.' Publicly dim a DataSet and a Dataview
=A0
=A0=A0 Public dstClients As DataSet
=A0=A0=A0Public dvwClients As DataView
=A0
2. 'The Function
=A0
=A0=A0=A0 Public Function BindMasterGrid() As DataView
=A0=A0=A0=A0=A0=A0=A0 Dim conFamily As SqlConnection =3D New =
SqlConnection(strConn)
=A0=A0=A0=A0=A0=A0=A0 Dim dadClients As SqlDataAdapter
=A0=A0=A0=A0=A0=A0=A0 dstClients =3D New DataSet()
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Try
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 dadClients =3D New =
SqlDataAdapter("mySproc", conFamily)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
dadClients.SelectCommand.CommandType =3D CommandType.StoredProcedure
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0'If you need to use =
Parameters, do like this
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0'dadClients.SelectCommand=
.Parameters.Add("@yrParam",yrValue)
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 conFamily.Open()
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
dadClients.Fill(dstClients, "clients")
=A0
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 dvwClients =3D =
dstClients.Tables("clients").DefaultView()
=A0
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Catch exp As Exception
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 =
Server.Transfer("error.aspx")
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 End Try
=A0
=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 Return dvwClients
=A0=A0=A0 End Function
=A0
3. 'Bind the view to a grid
=A0
=A0=A0=A0 myDataGrid.Source =3D BindMasterGrid()
=A0=A0=A0 myDatagrid.DataBind()
=A0
=A0
Hth
Regards/Halsningar

Andre Colbiornsen
Reply to this message...
 
 
System.Data.CommandBehavior
System.Data.CommandType
System.Data.DataSet
System.Data.DataView
System.Data.SqlClient.SqlCommand
System.Data.SqlClient.SqlConnection
System.Data.SqlClient.SqlDataAdapter
System.Data.SqlClient.SqlDataReader
Ad
MBR BootFX
Best-of-breed application framework for .NET projects, developed by Matthew Baxter-Reynolds and MBR IT
 
 Copyright © Matthew Baxter-Reynolds 2001-2008. '.NET 247 Software Development Services' is a trading style of MBR IT Solutions Ltd.
Contact Us - Terms of Use - Privacy Policy - www.dotnet247.com