Named vs Numbered Parameters (ie @name instead of ?) in Data Access, formerly OpenAccess ORM General Discussions

JOEL GERARD

5 posts

Member since:
Feb 2009

Posted 25 Feb 2009 Link to this post

Does anyone know how to use named parameters instead of numbered? I can see quite a few examples that use numbered parameters using ?. e.g.,

    
            string query = "SELECT * FROM users where email=? or username=?";

            Query<User> command = scope.GetSqlQuery<User>(query, "varchar email, varchar username");

            QueryResultList<User> result = command.ExecuteList(emailUser, emailUser);

This is hell on my maintenance and really error prone. Is there any way to use named parameters? Like this:

    
            SqlCommand mySqlCommand = mySqlConnection.CreateCommand(); 
        
                mySqlCommand.CommandText = 
        
                  "INSERT INTO Employee (" + 
        
                  "  ID, FirstName, LastName" + 
        
                  ") VALUES (" + 
        
                  "  @ID, @FirstName, @LastName" + 
        
                  ")"; 
        
                mySqlCommand.Parameters.Add("@ID", SqlDbType.NChar, 2); 
        
                mySqlCommand.Parameters.Add("@FirstName", SqlDbType.NVarChar, 10); 
        
                mySqlCommand.Parameters.Add("@LastName", SqlDbType.NVarChar, 10);

I know you can use LINQ, but is there any SQL/OQL support for named parameters?

Thanks,
Joel

PetarP

Admin

754 posts

Posted 27 Feb 2009 Link to this post

Hello JOEL GERARD,
we are currently not supporting named parameters. You will have to use the "?" mark in the sql querries or
$(number) in Oql queries. Here is an example of Oql query:

    
            IObjectScope scope = ObjectScopeProvider1.GetNewObjectScope(); 
        
                        string query = "select * from EmployeeExtent as x where x.EmployeeID = $1 and x.Country= $2"; 
        
                        scope.Transaction.Begin(); 
        
                        var result = scope.GetOqlQuery(query).Execute(1,"USA"); 
        
                        foreach (Employee emp in result) 
        
                        { 
        
                            Console.WriteLine( emp.Country); 
        
                        } 
        
                        scope.Transaction.Commit(); 
        
                        scope.Dispose();

Regards,
PetarP
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

Kareem

7 posts

Member since:
Nov 2008

Posted 06 Mar 2009 Link to this post

Hello Petar,

Why do you need the scope.Transaction.Begin(); to read? I thought you only need the transaction to add, update and delete the data.

Thanks,
Kareem

Zoran

Admin

534 posts

Posted 09 Mar 2009 Link to this post

Hello Kareem,

Select operations are not necessary to be done in a transaction but only in a case where you do not do any database modification operation e.g. yo do not do any insert/update/delete. Otherwise it is good to have your select statements inside a transaction because Transaction.Begin() ensures that the latest version of the objects are read from the database so following modifications do not fail.

Regards,
Zoran
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Check out the tips for optimizing your support resource searches.

Kareem

7 posts

Member since:
Nov 2008

Posted 09 Mar 2009 Link to this post

Zoran,

Thanks for your reply. This means that when data changes outside the ORM scope the data returned would not be accurate without the scope.transaction.begin when reading or a scope.refresh or something.

Thanks,
Kareem