How to execute SQL

Thread is closed for posting
6 posts, 1 answers
  1. ayumi hinako
    ayumi hinako avatar
    17 posts
    Member since:
    Oct 2009

    Posted 02 Dec 2009 Link to this post

    Sometimes I want use OpenAccess to execute normal sql, like 'select a,b,c from table1'. I don't know how to use the method GetSqlQuery. Can someone show me a example. Thank you very much.
  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 03 Dec 2009 Link to this post

    Hi ayumi hinako,

    Basically the GetSqlQuery method accepts 3 parameters.
    1. The sqlExpression one is the exact sql query that you would like to execute
    2. The result parameter represent the result type of your query. For example if your query for instances of type Customer you can do a typeof(Customer) in the GetSqlQuery method to explicitly specify the type that will be returned.
    3. The third parameter represents the parameter declaration of your sql query. For example if you would need to pass one int value to your query you will need to pass the following for this parameter:
    "INTEGER param".
    To summarize let me give you an example:

    string queryString = "Select * from Customers where CompanyName like ?";            IQuery query = scope.GetSqlQuery(queryString, typeof(Customer), "varchar CompanyName");           
    IQueryResult result = query.Execute("Ana%");
    Additional information can be found here.

    Regards,
    Petar
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  3. ayumi hinako
    ayumi hinako avatar
    17 posts
    Member since:
    Oct 2009

    Posted 03 Dec 2009 Link to this post

    Thank you for reply.
    I tested it and it worked. And I have another problem, when the sql contains many tables, like "select t1.field1,t2.field2 from table1 t1 left join table2 t2 on t1.id = t2.id",  what's the type should I use?


    Best regards
  4. ayumi hinako
    ayumi hinako avatar
    17 posts
    Member since:
    Oct 2009

    Posted 03 Dec 2009 Link to this post

    And, linq like : var result = (from p in scope.Extent<OpenAccessData.TBusiness>()
                              where p.FBusinessType == 2
                              select new
                              {
                                  p.FBusinessID,
                                  p.FBusinessLevel,
                                  p.FBusinessName,
                                  p.FBusinessLicenceNo,
                                  p.FLegalRepresentative,
                                  p.FOrganizationCode,
                                  p.FEnterpriseType,
                                  p.FTaxNo,
                                  p.FAddress,
                                  p.FContact,
                                  p.FTel
                              }).ToList();
    p.FBusinessLevel is type of int,  when I bind the result to RadDataGridView I want to change the FBusinessLevel to string value, for example, 0 to "A", 1 to "B". How to write the linq and how to bind? Thank you.
  5. Answer
    Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 04 Dec 2009 Link to this post

    Hello ayumi hinako,

    Regarding your first question, you could pass null for the result type. In this case the result will be an array of objects (representing the rows) and each element of this array is an object array itself (containing the value of each column):
    IQuery query = scope.GetSqlQuery(queryString, null, null); 
    IQueryResult result = query.Execute();
    foreach (object[] item in result)
    {
        
    }

    About your last post, you could change the FBusinessLevel by using a method which will be applied on this property inside the select statement:
    var result = (from p in scope.Extent<OpenAccessData.TBusiness>().ToList()
                  where p.FBusinessType == 2
                  select new
                  {
                      p.FBusinessID,
                      FBusinessLevel = Some_Method(p.FBusinessLevel),
                      p.FBusinessName,
                      p.FBusinessLicenceNo,
                      p.FLegalRepresentative,
                      p.FOrganizationCode,
                      p.FEnterpriseType,
                      p.FTaxNo,
                      p.FAddress,
                      p.FContact,
                      p.FTel
                  }).ToList();
    Note that you should first call ToList() on the scope.Extent<>() method.
    Hope that helps.

    All the best,
    Damyan Bogoev
    the Telerik team

    Instantly find answers to your questions on the new Telerik Support Portal.
    Watch a video on how to optimize your support resource searches and check out more tips on the blogs.
  6. ayumi hinako
    ayumi hinako avatar
    17 posts
    Member since:
    Oct 2009

    Posted 05 Dec 2009 Link to this post

    Now I understand that. Thank you :)
Back to Top