How to execute SQL

[ayumi hinako]

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.

[PetarP]

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.

[ayumi hinako]

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

[ayumi hinako]

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.

[Damyan Bogoev]

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.

[ayumi hinako]

Now I understand that. Thank you :)