ADO's .Parameters.Refresh isn't in ADO.NET

i'm using ado.net in visual basic 2008 express (.net framework 3.5 sp1).

i'm old (yeah, happens best of us) vb/vba programmer used ado 2.x.

here's i'm used doing:

dim cmd new adodb.command

set cmd.activeconnection = connect.getconnection
cmd.commandtype = adcmdstoredproc
cmd.commandtext = "usp_mystoredprocedure"
cmd.parameters.refresh
cmd.parameters("@parameter1") = foo
cmd.parameters("@parameter2") = bar

it looks there no longer refresh method of parameters collection under .net 3.5 sp1's system.data.sqlclient.sqlcommand object:

dim con new system.data.sqlclient.sqlconnection
dim cmd new system.data.sqlclient.sqlcommand
dim reader system.data.sqlclient.sqldatareader

dim csbuilder new syste.data.sqlclient.sqlconnectionstringbuilder

csbuilder.datasource = "myserver"
csbuilder.integratedsecurity = true
csbuilder.initialcatalog = "mydatabase"

con.connectionstring = csbuilder.connectionstring
con.open()

cmd.commandtype = commandtype.storedprocedure
cmd.commandtext = "usp_mystoredprocedure"

cmd.parameters.add(new system.data.sqlclient.sqlparameter("@parameter1", sqldbtype.int)
cmd.parameters("@parameter1") = foo

cmd.parameters.add(new syste.data.sqlclient.sqlparameter("@parameter2", sqldbtype.nvarchar, 255)
cmd.parameters("@parameter2").value = bar


is there way have parameters collection on sqlcommand object auto-populate way did under ado 2.x???  it's nifty feature saved me bit of typing (and typos).

thanks!

yes, have...

you can use sqlcommandbuilder.deriveparameters() method.
but, whenever execute method, unnecessary (and not light) system stored procedure called sql server
(you can see procedure at following code)
that's why don't recommend use when develop performance

hope helps
thanks

-----------------------------------------------------------------------------
below code tested @ customer site
-----------------------------------------------------------------------------
sqlconnection cnn;
sqlcommand cmd;

cnn = new sqlconnection("server=localhost; database=master;integrated security=true;");
cmd = new sqlcommand();
cmd.connection = cnn;
cmd.commandtext = "sp_who";
cmd.commandtype = commandtype.storedprocedure;

sqlcommandbuilder cmdbuilder = new sqlcommandbuilder();

cnn.open();
/*
it called next code executed:

ado.net 1.x : called 'sp_procedure_params_rowset' sql server
ado.net 2.0 : called 'sp_procedure_params_managed' sql server
*/
sqlcommandbuilder.deriveparameters(cmd);
cnn.close();

messagebox.show("count of parameters: " + cmd.parameters.count.tostring());

-----------------------------------------------------------------------------


 

---

best regards,
jungsun kim

SQL Server  >  SQL Server Data Access