.NETGURU
  Search:  
NamespacesDiscussions.NET v1.1Dot Net Guru's BlogIT Certification
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




ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
Ad


Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews
.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT
Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project
FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.
Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials
 
ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification
 Copyright © ExamGuru, Inc. 2001-2006
Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification