Here is my stored procedure.

ALTER PROCEDURE dbo.GetUsersByRoleId(@RoleId int)
AS
SELECT * FROM Users WHERE RoleId = @RoleId

And here is code snippet which will help to execute this Stored procedure.

public IQueryable<User> GetUsersByRoleId(int roleId)
{
    var parameter = new SqlParameter("RoleId", SqlDbType.Int) { Value = roleId };
    return _timeSheetDataContext.Users.SqlQuery("GetUsersByRoleId @RoleId",
        new[] { parameter }).AsQueryable();
}

You need to create the SqlParameter to use Parameter in stored procedure, otherwise you may get some mapping exception.

Happy Programming

//Role class
public class Role
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int RoleId { get; set; }

    public string RoleName { get; set; }
}

//Here is the Context class.
public class MyDataContext : DbContext
{
    public DbSet<Role> Roles { get; set; }
}

And here is the actual implementation of the custom DbInitializer

public class MyDataContextInitializer
: DropCreateDatabaseIfModelChanges<MyDataContext>
{
    protected override void Seed(MyDataContext context)
    {
        var adminRole = new Role {RoleName = "Administrator"};
        var userRole = new Role { RoleName = "User" };
        context.Roles.Add(adminRole);
        context.Roles.Add(userRole);
        base.Seed(context);
    }
}

Here is the output after running my application.

How it works

The Database Initializer has an internal method, Seed, which it calls whenever it is creating a database. The internal Seed method has no code, but it’s virtual, which means you can override it and supply your own logic. The key to overriding Seed is that you will have access to the DbContext instance and in the method, you can populate the context with data. Seed will pass that context back to the class that is performing the initialization and it, in turn will call SaveChanges, pushing that data into the newly created database. In order to access all of this logic, you must create a class which inherits from the initialization strategy that you want to use. Since the application is using DropCreateDatabaseIfModelChanges, we can inherit from that, then override its Seed method.

Happy Programming

class Program
{
    static void Main(string[] args)
    {
        var baseAddress = new Uri("http://localhost:8080/");
        var host = new DataServiceHost(typeof(DataContextService),
            new[] { baseAddress });
        host.Open();
        Console.WriteLine("Service started. Press  to exit");
        Console.WriteLine("Listening in following endpoints");
        host.Description.Endpoints.
            ToList().ForEach(endpoint =>
                Console.WriteLine(endpoint.Address));
        Console.ReadKey();
        host.Close();
    }
}

[ServiceBehavior(IncludeExceptionDetailInFaults = true)]
public class DataContextService : DataService
{
    public static void InitializeService(DataServiceConfiguration config)
    {
        config.DataServiceBehavior.MaxProtocolVersion =
            DataServiceProtocolVersion.V2;
        config.SetEntitySetAccessRule("*", EntitySetRights.All);
        config.UseVerboseErrors = true;
    }
}

This code requires following references

• System.Data.Services
• System.Data.Services.Client
• System.ServiceModel
• System.ServiceModel.Web

Here is my screen shot of console application.

And here is the sample output while navigating to the url using IE.

You need to turn off the Feed read view to see this output in IE.

You need to run the Application as Administrator, otherwise you may not able to create the host instance.

Next post(s) I will try to cover accessing and updating data from client side.

You can fix this issue by modifying Visual Studio settings

• Go to Tools, Options.
• Under Text Editor open the XAML section and select Miscellaneous.
• Under Default View check the Always open documents in full XAML view box
• Click OK to Apply

Happy Programming

Participate in the K-MUG Idea Challenge and win cool prizes. Do you have a killer Windows Phone Application idea? Do you think you can put Windows Phone Tools and Technologies together? Then “Idea Challenge” is the perfect game for you. Showcase your idea related to Windows Phone and win Cool prizes.

You can find more details here : DevCon 2012 website