Usage of LINQ and Datasets in Compact Framework 3.5

Screenshot - demo.jpg

Introduction

In this article, I will speak about LINQ and Compact Framework 3.5. I've written a simple example to demonstrate how we can use LINQ and Dataset to manipulate data.

Background

What is LINQ? LINQ is a codename for the set of extensions to the .NET Framework that encompass language-integrated data query, set, and transform operations. It includes extensions to the C# and Visual Basic languages with native language syntax for queries. It also provides class libraries to take advantage of these capabilities. With the release of Compact Framework 3.5 and Visual Studio "Orcas" Beta 1, LINQ extensions are now also available for mobile development.

About the sample

In this example, I will demonstrate a simple application that has one form. It displays and searches countries' information in a grid. For an illustration of how to work with LINQ and Dataset, the application will retrieve data from the SQL Server Compact Edition file demo.sdf. The application uses Typed Data Adapter as the Data Access Layer and DataTable as the data type.

Screenshot - ds.jpg

LINQ in action

In the code below, I am retrieving data from a database and filling in the Countries DataTable. After that, just for illustration, I am using LINQ to sort and display the data in a grid through the bindingSource object.

///Loading data from demo.sdf to Countries table

private void LoadData()
{
    ///Get Data

    Countries = dal.GetData();

    ///Building LINQ Query

    var query = from r in Countries.AsEnumerable()
        orderby r.Field<string>("Name") ascending
        select new 
    { 
        Code = r.Field<string>("Code"), Name = r.Field<string>("Name") 
    };

    ///Binding data

    bindingSource1.DataSource = query;
}

In the code below, I am using LINQ to search data in DataTable.

///Building LINQ Query

var query = from r in Countries.AsEnumerable()
    where r.Field<string>("Name").ToLower().Contains(txtSearch.Text.ToLower())
    orderby r.Field<string>("Name") ascending
    select new 
    { 
        Code = r.Field<string>("Code"), Name = r.Field<string>("Name") 
    };

///Binding data

bindingSource1.DataSource = query;

Conclusion

As you can see, LINQ is very easy and intuitive to use. Its syntax is quite similar to that of SQL and it is a part of the C# and Visual Basic languages. We can use it to manipulate data in many scenarios.

History

14 June, 2007 -- Original version posted

You must Sign In to use this message board.


Per page

How about the update dataRow in LINQ?
agskid

18:21 1 Jul '09

it is nice example!! quite enjoy after reading ?

How about the update? in compact framework using LINQ?

normal LINQ with datacontext to perform update/insert
as

NorthwindDataContext db = new NorthwindDataContext();
var newCustomer = new Customer
{
    CustomerID = "MCSFT",
    CompanyName = "Microsoft",
    ContactName = "John Doe",
    ContactTitle = "Sales Manager",
    Address = "1 Microsoft Way",
    City = "Redmond",
    Region = "WA",
    PostalCode = "98052",
    Country = "USA",
    Phone = "(425) 555-1234",
    Fax = null
};
db.Customers.InsertOnSubmit(newCustomer);
db.SubmitChanges();

but in compact framework 3.5, datacontext for database is not applicable.

Any good way to implement "update/insert" using linq over dataset on windows mobile?

Thank you

Re: How about the update dataRow in LINQ?
Oleg Levin

19:13 1 Jul '09

Hi What do you mean? Update DB? something Like Linq to SQL or in memory updates? Oleg Levin .NET Development Manager
Sign In·View Thread·PermaLink

Re: How about the update dataRow in LINQ?
agskid

4:19 2 Jul '09

update Database ,yes something like linq to SQL Thank you
Sign In·View Thread·PermaLink

Re: How about the update dataRow in LINQ?
Oleg Levin

5:41 2 Jul '09

Hi Please read this: http://www.pluralsight.com/community/blogs/jimw/archive/2008/04/18/50753.aspx[^] Oleg Levin .NET Development Manager
Sign In·View Thread·PermaLink

Hi there
Sebulba_se

6:29 10 Sep '08

Hope you still get mail from this article.

I have a question for you? Hope its all right.

I have made a similar solution but i have problem formatting the columns.
I read data from an xml file into a dataset and then i do a search from my ds with linq.
Then i set the result from the search as the datasourc on my grid. Then i want to resize the columns in the grid.
But thats when i run out of luck.

So when i searched for that i found your code.

I need to set the column width in the grid. I know im supposed to use the DataGridTableStyle but i guess i do something wrong with the mappingname against the linq result.

this is my code:
Dim x As List(Of Books)
Dim s As String = "*" & UCase(txtSearch.Text) & "*"

x = (From b In ds.Book Join w In ds.Writer On w.idWriter Equals b.idWriter _
Where UCase(b.Title) Like s Or UCase(w.LastName) Like s Or UCase(w.FirstName) Like s _
Order By b.Title, w.LastName _
Select New Books(b.Title, w.FirstName, w.LastName, b.Rating, b.Year, b.Price)).ToList

Dim arrColumnNames(4) As String

arrColumnNames(0) = "Titel"
arrColumnNames(1) = "Författare"
arrColumnNames(2) = "Betyg"
arrColumnNames(3) = "Pris"
arrColumnNames(4) = "Utg.År"

Dim BookStyle As New DataGridTableStyle
BookStyle.MappingName = "books"

Dim i As Integer = 0
For i = 0 To UBound(arrColumnNames)
Dim myDataCol As New DataGridTextBoxColumn
myDataCol.HeaderText = arrColumnNames(i)
myDataCol.MappingName = _result.Tables("books").Columns(i).ColumnName
myDataCol.Width = Int(200)
BookStyle.GridColumnStyles.Add(myDataCol)
myDataCol = Nothing
Next
dgBooks.TableStyles.Clear()
dgBooks.TableStyles.Add(BookStyle)
dgBooks.datasource = x

Any idea what im doing wrong?

Thanks
/Magnus

Re: Hi there
Oleg Levin

8:00 10 Sep '08

Hi It's difficult to see problem in this piece of code but my first remark: you write:BookStyle.MappingName == "books" but your entity is "Books" try change to BookStyle.MappingName == "Books" If you can, give me please your source code i will try to help you (c# my 1st priority) Oleg Levin .NET Development Manager
Sign In·View Thread·PermaLink

AsEnumerable() does not exist.
zhenyulu2003

13:04 17 Aug '07

Hi, I am trying to compile your project using Orcas beta2, but got following errors:

Error	1	'LinqDemo.EntitiesDS.CountriesDataTable' does not contain a definition for 'AsEnumerable' and no extension method 'AsEnumerable' accepting a first argument of type 'LinqDemo.EntitiesDS.CountriesDataTable' could be found (are you missing a using directive or an assembly reference?)	D:\Codes\LinqDemo_src\LinqDemo\MainForm.cs	61	35	LinqDemo

Do I need to configure something in Orcas beta2 to make it work? Thanks.

Re: AsEnumerable() does not exist.
Oleg Levin

9:37 18 Aug '07

Hi. To solve this problem please add Reference to your project: system.data.DataSetExtensions Oleg Levin .NET Developer/Group Leader
Sign In·View Thread·PermaLink	2.00/5

Re: AsEnumerable() does not exist.
zhenyulu2003

3:56 20 Aug '07

That works . Thanks. -Kevin
Sign In·View Thread·PermaLink	1.00/5

Compact Framework
Pamela1984

22:38 12 Jul '07

Hi Oleg, I read your article and I would like your help I am having trouble writing my first mobile application with Compact Framework and SQL server. Can you give me some sample mobile code for a small application so that I can see how it works. Many thanks, Pamela
Sign In·View Thread·PermaLink

Re: Compact Framework
Oleg Levin

3:56 13 Jul '07

Hi. Did you mean that you started to develop with Sql Server Compact Edition and Compact Framework 2.0? You can see here good sample: http://netcf2.blogspot.com Oleg Levin .NET Developer/Group Leader
Sign In·View Thread·PermaLink

Re: Compact Framework
Pamela1984

4:17 15 Jul '07

Hi Oleg, Thank you for replying and the answer is yes to your question. Over the weekend I wrote my first web service and I would love if you could help me solve some problems. 1. How can I make the mouse cursor look busy ? 2. How can I resize the datagrid fields ? 3. Can I transfer files via web service ? thanks again, Pam
Sign In·View Thread·PermaLink

Re: Compact Framework
Oleg Levin

9:32 15 Jul '07

I am answering to your questions:

1. Cursor.Current = Cursors.Wait 2.Resize datagrid columns VB.NET sample:
'Create new Table Style.

Dim ts As New DataGridTableStyle()
ts.MappingName = "Employees"
DataGrid1.TableStyles.Clear()
DataGrid1.TableStyles.Add(ts)

' Assign New Width to DataGrid column.
DataGrid1.TableStyles("Employees").GridColumnStyles("Title").Width = newwidth 3.Transfer files via web service C#:

[WebMethod]
    public byte[] GetFile(string filename) {
        BinaryReader binReader = new
        BinaryReader(File.Open(Server.MapPath(filename), FileMode.Open,
        FileAccess.Read));
        binReader.BaseStream.Position = 0;
        byte[] binFile =
        binReader.ReadBytes(Convert.ToInt32(binReader.BaseStream.Length));
        binReader.Close();
        return binFile;
    }

    [WebMethod]
    public void PutFile(byte[] buffer, string filename) {
        BinaryWriter binWriter = new
        BinaryWriter(File.Open(Server.MapPath(filename), FileMode.CreateNew,
        FileAccess.ReadWrite));
        binWriter.Write(buffer);
        binWriter.Close();
    }

Some useful samples here:
http://samples.gotdotnet.com/quickstart/CompactFramework/[^]

Oleg Levin
.NET Developer/Group Leader

Re: Compact Framework [modified]
Pamela1984

20:43 17 Jul '07

Wow, thanks Oleg

I feel that at last I am getting somewhere

I hope that you don't mind me asking you so many questions Smile

Here are some more,

1. Is it possible to connect the Emulator (Pocket PC / Windows CE) to the activesync?

2. concerning the webservice

I have a folder c:\webTest which contains my webservice and a subfolder

c:\webTest\images containing some images. When I try to transfer files from the images folder to the PDA I get the following error:
" server was unable to process request.---> c:\webTest\images\Jenny.jpg is not a valid virtual path "

Thanks again

Pamela

-- modified at 8:12 Wednesday 18th July, 2007

LINQ
gagliapas

14:43 19 Jun '07

Besides improvements of code readability, is there any improvement in terms of query performances? I am evaluating the porting of a solution developed with ADO, do you suggest it? Thanks, Pas
Sign In·View Thread·PermaLink

Re: LINQ
Oleg Levin

21:02 19 Jun '07

Hi. May be better to wait for final release and after that make discussions about performance. Thanks. Oleg Levin .NET Developer
Sign In·View Thread·PermaLink

nice
Sacha Barber

23:37 14 Jun '07

good job....you may also like my 3 part series starting with this one Sacha Barber A Modern Geek - I cook, I clean, I drink, I Program. Modern or what? My Blog : sachabarber.net
Sign In·View Thread·PermaLink

Re: nice
Oleg Levin

7:14 15 Jun '07

Thank you. I will see it. Oleg Levin .NET Developer
Sign In·View Thread·PermaLink

Thanks
merlin981

6:21 14 Jun '07

Thanks for posting this. It's amazing the number of uses Linq will have for us all ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ I theme/skin my controls and apps with Themer I get my developer tools from Merlin A.I. Soft ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Sign In·View Thread·PermaLink