New Posts New Posts RSS Feed: ParameterizedSql in PassthruRdbQuery
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

ParameterizedSql in PassthruRdbQuery
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
-ook View Drop Down
Groupie
Groupie
Avatar

Joined: 26-Aug-2007
Location: Australia
Posts: 41 		Post Options Post Options   Quote -ook Quote  Post ReplyReply Direct Link To This Post Topic: ParameterizedSql in PassthruRdbQuery
    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
Back to Top
owais,zahid View Drop Down
Newbie
Newbie
Avatar

Joined: 22-Aug-2007
Location: Pakistan
Posts: 8 		Post Options Post Options   Quote owais,zahid Quote  Post ReplyReply Direct Link To This Post 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?
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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);
Back to Top
-ook View Drop Down
Groupie
Groupie
Avatar

Joined: 26-Aug-2007
Location: Australia
Posts: 41 		Post Options Post Options   Quote -ook Quote  Post ReplyReply Direct Link To This Post 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



Edited by -ook - 29-Aug-2007 at 2:42pm
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post 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.
Back to Top
jeffdoolittle View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 146 		Post Options Post Options   Quote jeffdoolittle Quote  Post ReplyReply Direct Link To This Post 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)
Back to Top
jeffdoolittle View Drop Down
Senior Member
Senior Member
Avatar

Joined: 14-Jun-2007
Location: United States
Posts: 146 		Post Options Post Options   Quote jeffdoolittle Quote  Post ReplyReply Direct Link To This Post Posted: 24-Jun-2008 at 3:51pm
Nevermind, I figured it out.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down