Best practice

[Guest]

Hello All,

Best coding strategies for calling stored procedures from ASP.NET. As you
are all aware that accessing a property is expensive than calling a method.
For example in order to call a stored procedure which accepts 2 input
parameters, we are currently following the below standard to set a single
parameter:

objOracleParameterSTATUS = new System.Data.OracleClient.OracleParameter ();
objOracleParameterSTATUS.ParameterName ="STATUS_IN";
objOracleParameterSTATUS.SourceColumn = "STATUS"
objOracleParameterSTATUS.OracleType=System.Data.OracleClient.OracleType.Number;
objOracleParameterSTATUS.Size =1;
objOracleParameterSTATUS.Precision =0;
objOracleParameterSTATUS.Scale =0;
objOracleParameterSTATUS.Direction = System.Data.ParameterDirection.Input;
objOracleParameterSTATUS.SourceVersion=System.Data.DataRowVersion.Current;
objOracleParameterSTATUS.Value =STATUS;
bjOracleCommand.Parameters.Add (objOracleParameterSTATUS);

So on the whole we are setting 9 properties for a single parameter and if
this stored procedure accepts 10 parameters, then this process has to be
repeated for those many parameters.

Can any of the solution developers suggest me an alternative choice for the
above code or how can I make this less expensive and efficient.

Thanks for your help!!!

 

[Marina]

If that object has no method to set all of them, then there is nothing you
can do.

I honestly don't think you are going to see an actual performance problem by
having to set 9 properties instead of calling 1 method.

 

[Scott Allen]

Property getters and setters are generally short enough to be inlined
by the optimizing compiler, so they won't be as expensive as a method
call. No need to worry....

 

[Guest]

Instead of coding these many lines for each and every parameter, is there
anyway that I can reduce the lines of code ? How can I design a resusable
method for setting these properties......so that this method can be called
for each and every parameter?

 

[TE]

Hi,

actually I do not know the OracleClient - but I am sure, the Oracle Provider
provides an overloaded constructor for the OracleParameter Object (this is
available for the SQL-Server Client at last).

This is the way, I solve this for a SQL-Server sp parameter:

(Please note: the SqlParameter has 6 different constructors. One of them can
handle all the properties you are using - like
Direction,Scale,Precision,Size, etc)

myCommand.Parameters.Add(New SqlClient.SqlParameter("@PKClientGuid",
SqlDbType.UniqueIdentifier)).Value = Client.Guid

I think this is much more read- and maintainable.

Hope I could help you!

Regards,

Tom

 

[Steve C. Orr [MVP, MCSD]]

Boilerplate data access code such as this can be dramatically simplified by
using Microsoft's free Data Access Application Block.
Here's more info:
http://aspnet.4guysfromrolla.com/articles/070203-1.aspx

 

[Scott Allen]

Yes - use a code generator. There are code gen tools that you can
point to a database and they can spit out all the code you need for
the tables / sprocs / views. Here is one:
http://www.ericjsmith.net/codesmith/