Quick data retrieval

Using 'old' ado and VB6 I could use following two lines to quickly retrieve
data from a MSSQL server into a fire hose cursor:

Dim rst As ADODB.Recordset
Set rst = cnn.Execute("{call a_stored_procedure}")

Can I do something similarly in ADO.NET using VB.NET or C# to get results
from a stored procedure into a data reader (using as little code as
possible)?

Paw

Miha
Sun Nov 23 08:37:45 CST 2003

Hi Paw,

Here you go:
SqlConnection conn = new SqlConnection("workstation id=YourComputer;packet
size=4096;integrated security=SSPI;initial catalog=Northwind;persist
security info=False");

DataTable table = new DataTable("Results");

SqlCommand command = new SqlCommand("Ten Most Expensive Products", conn);

command.CommandType = CommandType.StoredProcedure;

SqlDataAdapter adapter = new SqlDataAdapter(command);

adapter.Fill(table);


--
Miha Markic - RightHand .NET consulting & development
miha at rthand com

"Paw Boel Nielsen" <not@important.com> wrote in message
news:eIMbBxcsDHA.540@tk2msftngp13.phx.gbl...
> Using 'old' ado and VB6 I could use following two lines to quickly
retrieve
> data from a MSSQL server into a fire hose cursor:
>
> Dim rst As ADODB.Recordset
> Set rst = cnn.Execute("{call a_stored_procedure}")
>
> Can I do something similarly in ADO.NET using VB.NET or C# to get results
> from a stored procedure into a data reader (using as little code as
> possible)?
>
> Paw
>
>
>

Paw
Sun Nov 23 10:41:36 CST 2003

Thanks for your reply Miha,

after experimenting I found that this would required less code:

SqlConnection conn = new SqlConnection("workstation id=YourComputer;packet
size=4096;integrated security=SSPI;initial catalog=Northwind;persist
security info=False");
conn.Open();
SqlDataReader reader = (new SqlCommand("[Ten Most Expensive
Products]",conn)).ExecuteReader();

1) Why does your code work without using conn.Open(); to open the
connection???
2) With old ADO following syntax could be used to call a stored procedure
without having to add parameters manually:
{[?=]call procedure_name[([parameter][,[parameter]]...)]} (snippet from
BOL)
This doesn't seem to work in ADO.NET does anybody have an idea why?

Paw


"Miha Markic" <miha at rthand com> wrote in message
news:erWhd9csDHA.2244@TK2MSFTNGP09.phx.gbl...
> Hi Paw,
>
> Here you go:
> SqlConnection conn = new SqlConnection("workstation id=YourComputer;packet
> size=4096;integrated security=SSPI;initial catalog=Northwind;persist
> security info=False");
>
> DataTable table = new DataTable("Results");
>
> SqlCommand command = new SqlCommand("Ten Most Expensive Products", conn);
>
> command.CommandType = CommandType.StoredProcedure;
>
> SqlDataAdapter adapter = new SqlDataAdapter(command);
>
> adapter.Fill(table);
>
>
> --
> Miha Markic - RightHand .NET consulting & development
> miha at rthand com
>
> "Paw Boel Nielsen" <not@important.com> wrote in message
> news:eIMbBxcsDHA.540@tk2msftngp13.phx.gbl...
> > Using 'old' ado and VB6 I could use following two lines to quickly
> retrieve
> > data from a MSSQL server into a fire hose cursor:
> >
> > Dim rst As ADODB.Recordset
> > Set rst = cnn.Execute("{call a_stored_procedure}")
> >
> > Can I do something similarly in ADO.NET using VB.NET or C# to get
results
> > from a stored procedure into a data reader (using as little code as
> > possible)?
> >
> > Paw
> >
> >
> >
>
>

Miha
Sun Nov 23 11:39:38 CST 2003

Hi Paw,

"Paw Boel Nielsen" <not@important.com> wrote in message
news:ebFHAhdsDHA.556@TK2MSFTNGP11.phx.gbl...
> Thanks for your reply Miha,
>
> after experimenting I found that this would required less code:
>
> SqlConnection conn = new SqlConnection("workstation id=YourComputer;packet
> size=4096;integrated security=SSPI;initial catalog=Northwind;persist
> security info=False");
> conn.Open();
> SqlDataReader reader = (new SqlCommand("[Ten Most Expensive
> Products]",conn)).ExecuteReader();

Yes, it is shorter. But - it won't fill you a DataTable.

> 1) Why does your code work without using conn.Open(); to open the
> connection???

Fill calls Open implicitly if connection is Closed and it also closes it
afterwards in this case.

> 2) With old ADO following syntax could be used to call a stored procedure
> without having to add parameters manually:
> {[?=]call procedure_name[([parameter][,[parameter]]...)]} (snippet from
> BOL)
> This doesn't seem to work in ADO.NET does anybody have an idea why?

By design?

--
Miha Markic - RightHand .NET consulting & development
miha at rthand com