Print Page | Close Window

ParameterizedSql in PassthruRdbQuery

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=394
Printed Date: 11-Jun-2026 at 1:37am


Topic: ParameterizedSql in PassthruRdbQuery
Posted By: -ook
Subject: ParameterizedSql in PassthruRdbQuery
Date Posted: 29-Aug-2007 at 12:23am
Hello

I want to create a simple PassthruRdbQuery that contains a few parameters (yes,yes I know DevForce don't necessarily advocate the use of this - but too bad I'm using it!). I want to include some parameters within the SQL Statement. However the supplied documentation and tutorials neglect to show how the ParameterizedSql is created - one simple example in the Help system would have been enough for me.

So, can someone please provide me with an example of a Passthru query with parameters?

Thanks


Replies:
Posted By: owais,zahid
Date Posted: 29-Aug-2007 at 5:53am
I am not sure about the performance penalty of PassthruRdbQuery, but i have used in like
 
PassthruRdbQuery aQuery = new PassthruRdbQuery(typeof(Payment),
                "select * from dbo.APPayment as vp where " +
                "vp.IsUnposted = 'False' and vp.VendorId = '" + pvendorId.ToString() + "' and vp.PacketId IN (select Id from dbo.APPacket as ap where " +
                "ap.IsPosted = 'True')");
 
Hope this works for u.
 
-> Can any one tell me about the performance issues with it?

Posted By: kimj
Date Posted: 29-Aug-2007 at 10:22am
Here's one example of how you can pass parameterized SQL through the PassthruRdbQuery:
 
// We're retrieving the RdbKey here so that the parameter name can be
//formatted correctly. If you already know your data provider and
//parameter format you can skip this step and hardcode the parameter name.
 
RdbKey key = (RdbKey) PM.GetDataSourceKey(typeof(Employee));
string parmName = key.AdoHelper.FormatParameterName("id");
 
// Build select statement
string passThruSelect = string.Format("select * from Employee where id = {0}", parmName);
 
// Build the parameters - be sure to use RdbParameter (esp. if using a BOS)
RdbParameter parm = new RdbParameter(parmName, DbType.Int32);
parm.Value = 1;
 
// Now create a ParameterizedSql instance
ParameterizedSql sql = new ParameterizedSql(passThruSelect, parm);
 
// And finally setup the passthru query and execute it
PassthruRdbQuery passThruQuery = new PassthruRdbQuery(typeof(Employee), sql);
Employee emp = PM.GetEntity<Employee>(passThruQuery);

Posted By: -ook
Date Posted: 29-Aug-2007 at 2:42pm
zahid

According to the documentation the PassThruQuery can be just as efficient as an Entity Query depending on the way you have written the statement. There are disadvantages in disconnected applications. Thanks for the string append method, but I did need the parameters due to SQL Server formatting my datetime variable to the US date format instead of Aussie.

Kimj

Thanks for the example, my parameters are not keys but dates (that I am using in a SQL user defined function in order to keep the same logic between SQL Server Reports and this IdeaBlade application).  However you have given me the correct syntax and advice I need to make it work, again thanks.

Hmmmm ... but maybe there is an IdeaBlade way of doing what I want. Perhaps it should be in a different thread. Below is the SQL Code I'm using in Reporting Services for my reports. I want the same statement in IdeaBlade without having to redo the UDF as a class (because then I have to maintain two different sets of code that do the same thing). Possible or is a Passthru the only option?

SELECT *
FROM vwPallets
WHERE DateTimeStacked between dbo.fnShiftStart(@StartDate,@StartType) AND dbo.fnShiftEnd(@EndDate,@EndType)

The SQL UDF's work out the correct date and times that should be included for a shift range.

Thanks

Posted By: kimj
Date Posted: 29-Aug-2007 at 3:45pm
I think the Passthru query is the IdeaBlade way of doing what you want here.  The only other alternative would be to write a wrapper stored proc, and then use a StoredProcRdbQuery.  I don't really know what the benefits vs. drawbacks would be for the different approaches, but it really comes down to a matter of choice and which you find fits in best with your application.

Posted By: jeffdoolittle
Date Posted: 24-Jun-2008 at 3:38pm
Kim,

I'm trying to follow the example above in order to create a parameterized query, but I get a persistence server exception.

When I call this method:

string parmName = key.AdoHelper.FormatParameterName("pc");

it doesn't seem to do anything to the parameter name.  When I inspect the parameterized sql, I see a clause that says "PostalCode=pc" but apparent the the variable "pc" isn't being replaced properly with the parameter value when the query is executed.

The exception detail follows:

IdeaBlade.Persistence.PersistenceServerException : Invalid column name 'pc'.
  ----> System.Data.OleDb.OleDbException : Invalid column name 'pc'.
    IdeaBlade.Persistence.PersistenceManager.HandlePersistenceServerException(Exception pException, Boolean pTryToHandle, PersistenceOperation pOperation)
    IdeaBlade.Persistence.PersistenceManager.XFetchDataSet(IEntityQuery pEntityQuery)
    IdeaBlade.Persistence.PersistenceManager.XFetch(IEntityFinder pEntityFinder, WorkState pWorkState)
    IdeaBlade.Persistence.PersistenceManager.XFetch(IEntityQuery pEntityQuery, QueryStrategy pQueryStrategy, WorkState pWorkState)
    IdeaBlade.Persistence.PersistenceManager.XGetEntities(IEntityQuery pEntityQuery, QueryStrategy pQueryStrategy, WorkState pWorkState)
    IdeaBlade.Persistence.PersistenceManager.GetEntities[T](IEntityQuery pEntityQuery, QueryStrategy pQueryStrategy)
    IdeaBlade.Persistence.PersistenceManager.GetEntities[T](IEntityQuery pEntityQuery)
    C:\Projects\Lotpath\Source\Lotpath.Model\Search\SearchHelper.cs(37,0): at Lotpath.Model.SearchHelper.GetSearchResult()
    C:\Projects\Lotpath\Source\Lotpath.Model\Search\SearchHelper.cs(24,0): at Lotpath.Model.SearchHelper.GetResult(SearchFilter searchFilter)
    C:\Projects\Lotpath\Tests\Lotpath.Model.Tests\SearchHelperTest.cs(125,0): at Lotpath.Model.Tests.SearchHelperTest.GS1AttributeFilteredSearchWithLocationFilterTest()

    IdeaBlade.Persistence.RemotingPersistenceServerProxy.CheckConnection(Exception pException)
    IdeaBlade.Persistence.PersistenceServerProxy.Fetch(SessionBundle pBundle, IEntityQuery pQuery)
    IdeaBlade.Persistence.PersistenceManager.XFetchDataSet(IEntityQuery pEntityQuery)

Posted By: jeffdoolittle
Date Posted: 24-Jun-2008 at 3:51pm
Nevermind, I figured it out.


Print Page | Close Window