Thursday, June 30, 2011

Windows Phone 7 (Mango) Tutorial - 25 - Learn about Database Connection String

Last three days we learnt about the new feature of Windows Phone 7 (Mango) called "Local Database Support". Yesterday we discussed it in depth by building a small demo application. While working with the demo you might noticed that, we used a connection string to connect with the local database present in the Windows Phone storage.   In this post, we will discuss more on the connection string. After reading this post, you will be able to know what are the various types and parameters available for connection string.   Index - Windows Phone 7 (Mango) Tutorial   What is Connection String? A connection string is a settings to connect with the database file using a Windows Phone 7 application. Using this string parameter you ask your app to connect to an existing database or create a new one to do SQL operations.   By using the connection string you can specify the connection parameters like data source, password, buffer size, database...

Executing SQL Query using LINQ to SQL

In this post, I will discuss how we can execute a SQL Query directly from LINQ.


To execute SQL query there is a method in DataContext class called ExecuteQuery


ExecuteQuery takes two input parameter

1. SQL Query as string

2. Parameters used in SQL query


And it returns an IEnumerable.


Below code will execute a simple select statement and it will return IEnumerable<Person>


If you want to pass some parameter in the query, you can pass that as second parameter.


If you want pass input parameter as hardcoded value you can very much do that as below


For your reference source code is as below,

01 using System;
02 using System.Collections.Generic;
03 using System.Linq;
04 using System.Text;
05 using System.IO;
06 using System.Data.Linq;
08 namespace Relatedtable
09 {
10     class Program
11     {
13         static DataClasses1DataContext context;
14         static void Main(string[] args)
15         {
16             context = new DataClasses1DataContext();
18             var result = context.ExecuteQuery<Person>("select * from Person");
19             foreach (var r in result)
20             {
21                 Console.WriteLine(r.PersonID + r.FirstName);
22             }
24             int idToPass = 1;
25             var result1 = context.ExecuteQuery<Person>
26                           ("select * from Person where PersonID={0}", idToPass);
27             foreach (var r in result1)
28             {
29                 Console.WriteLine(r.PersonID + r.FirstName);
30             }
32             Console.ReadKey(true);
34             var result2 = context.ExecuteQuery<Person>
35                           ("select * from Person where PersonID='1'");
36             foreach (var r in result2)
37             {
38                 Console.WriteLine(r.PersonID + r.FirstName);
39             }
41             Console.ReadKey(true);
42 }
43 }
44 }

On pressing F5 you should get output as below,


I hope this post was useful. Thanks for reading Smile