This is a migrated thread and some comments may be shown as answers.
How to execute SQL
5 Answers 77 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
ayumi hinako
Top achievements
Rank 1
ayumi hinako asked on 03 Dec 2009, 05:17 AM
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.

5 Answers, 1 is accepted

Sort by
0
PetarP
Telerik team
answered on 03 Dec 2009, 08:08 AM
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.
0
ayumi hinako
Top achievements
Rank 1
answered on 04 Dec 2009, 02:19 AM
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
0
ayumi hinako
Top achievements
Rank 1
answered on 04 Dec 2009, 03:00 AM
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.
0
Accepted
Damyan Bogoev
Telerik team
answered on 04 Dec 2009, 03:44 PM
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.
0
ayumi hinako
Top achievements
Rank 1
answered on 06 Dec 2009, 03:20 AM
Now I understand that. Thank you :)
Tags
General Discussions
Asked by
ayumi hinako
Top achievements
Rank 1
Answers by
PetarP
Telerik team
ayumi hinako
Top achievements
Rank 1
Damyan Bogoev
Telerik team
Share this question
or