Title: Delphi.NET - Getting stored procedures parameters automatically
Question: We Delphians are very used to have a bunch of methods that make life very easy, such as Parameters.Refresh that will just get the whole list of parameters with their types, direction, etc for us and we don't have to add each individual parameter
in .NET seems like is not that easy... we'll see
Answer:
you see examples like this all over the web:
sqlCmd.CommandText = "booksSearchBooks";
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.Parameters.Clear();
sqlCmd.Parameters.Add("@SearchBy", SqlDbType.VarChar, 1);
sqlCmd.Parameters.Add("@SearchCriteria", SqlDbType.VarChar, 50);
(excuse the C#... is just you don't see that many Delphi examples)
I tried and searched for a method something like
sqlCmd.Parameters.Refresh with no luck, but I found this nice function that does the job for you
SqlCommandBuilder.DeriveParameters(aStoredProcedure);
all you have to do is pass your stored procedure variable to it and it returns all the parameters and types, etc, just like in delphi =o)
here's a code snippet from one of my projects:
procedure TWebForm1.Page_Load(sender: System.Object; e: System.EventArgs);
var
SQLConn:SqlConnection;
aStoredProcedure:SqlCommand;
dr:SqlDataReader;
const
CONN_STR = 'workstation id="localhost";packet size=4096;integrated security=SSPI;data source="localhost";persist security info=False;initial catalog=books';
begin
// TODO: Put user code to initialize the page here
SQLConn:=SqlConnection.Create(CONN_STR);
aStoredProcedure:=SqlCommand.Create('booksGetLatest', SQLConn);
aStoredProcedure.CommandType:=CommandType.StoredProcedure;
SQLConn.Open;
try
SqlCommandBuilder.DeriveParameters(aStoredProcedure);
aStoredProcedure.Parameters.Item['@ReturnCount'].Value:=TObject(20);
dr:=aStoredProcedure.ExecuteReader;
try
DataGrid1.DataSource:=dr;
DataGrid1.DataBind;
finally
dr.Close
end
finally
SQLConn.Close
end
end;
as you can see all I do is call that function, then after that I can assign values to any of the parameters
don't forget to add
System.Data, System.Data.SqlClient
to your uses clause
Another approach to this situation would probably be to write a class helper and we could actually call the stored procedure .Refresh method, but I'll leave that for my next article, there's a few more thing that I want to write about now that I've been playing with my trial version of Delphi 2005
(wich in case you don't know is available for download from Borland now)
one strange thing, I couldn't do this:
aStoredProcedure.Parameters.Item['@ReturnCount'].Value:=20;
the compiler complains about incompatible types, TObject and Integer, so I just had to make it happy by typecasting the value...
if anyone knows a better way of doing this please comment
side note for all of us Delphi 8 "users", finally there's a fix coming for Delphi 8 issues:
Danny Thorpe from Borland has said in his blog that there's a Delphi 8 Update 3 coming soon (expected early December) that will update this issue:
I quote from his blog:
"The Delphi 8 update 3 to resolve versioning issues with the .NET 1.1 SP1 release has been going through internal testing for the past week. We're working toward a release target of early December."
keep checking here for more articles about delphi.NET
best regards
EberSys