Quick data retrieval

[Miha Markic]

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);

 

[Paw Boel Nielsen]

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 Boel Nielsen]

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]

Hi Paw,

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?