StrataFrame Forum

Geniric Data access

http://forum.strataframe.net/Topic413.aspx

By Scott - 1/6/2006

I noticed that there is a CreateBlankDbCommand method on the DbDataSourceItem object.  Is this the recommended way to create a command object to pass to the various BO methods that accept command objects so that it is generic based on the currect connection?

Using this method would it simply be:

DbDataSourceItem dsItem = DataLayer.DataSources[0];
DbCommand cmd = dsItem.CreateBlankDbCommand();
cmd.CommandText = "SELECT COUNT(*) FROM QDetails WHERE ItemID = @itemID";
cmd.Parameters.Add("@itemID");
cmd.Parameters["@itemID"].Value = ItemID;

Does not specifying the type for the parameter affect the speed? I know you have to give up something for the flexability but I guess what I am asking is it slower by a factor of 1-5ms or 20 - 30 time longer. I just want to know for future reference if there is a process that needs to be "as fast as posible". Thanks for the feedback.

By StrataFrame Team - 1/6/2006

Scott,



The CreateBlankDbCommand() method on the DbDataSourceItem is generally used internally, but yes, you could use it if necessary. The reason it's there is because the DbDataSourceItem does not know what database it is connecting to. However, when you're creating a custom Fill method like this, you do know the database type, so I would avoid the use of the CreateBlankDbCommand() method. For instance, if you pass a string statement such as "SELECT * FROM Customers", the DbDataSourceItem will use the CreateBlankDbCommand() to obtain a command object that can be used with the data source.



Your best bet is going to be to use the constructor for your database type (i.e., create an SqlCommand or an OracleCommand) and work with that. I would change your code to:



SqlCommand cmd = new SqlCommand();

cmd.CommandText = "SELECT COUNT(*) FROM QDetails WHERE ItemID = @itemID";

cmd.Parameters.Add("@itemID", SqlDbType.Int);

cmd.Parameters["@itemID"].Value = ItemID;



It's always easier to work with the subclass type (in this case SqlCommand) rather than the supertype (DbCommand).



If you're working with more than one data source, in our case SQL Server and VFP, then you'll want to test on the business object's data source key (which from that you will know the type of the data source), like this:



if(this.DataSourceKey == "")

{

   //-- This is the default data source, so create and execute an SqlCommand object

}else if(this.DataSourceKey == "MyVfpKey")

{

   //-- This is the VFP data source, so create and execute an OleDbCommand object 

}



As for the speed on specifing the parameter type, yes, it is faster and more efficient to use the proper data type, however, it probably is not a measurable amount until you execute the query some 100,000 times or more. The biggest benefit is allowing the server to optimize the query since it knows the data type (everthing is left as nvarchar if you don't specify a type). It's all relative, however, as 90% of the latency between calling a small query and receiving the results is due to network latency.



Whenever the DbDataSourceItem creates a dynamic command, it specifies the parameter type by using the FieldDbTypes and FieldNativeDbTypes collections on the business object (which we create in the static constructor in the designer file), so you don't have to worry about the dynamic statements not being optimized.
By Scott - 1/6/2006

Good to know.  Thanks for all the info.