Tuesday, February 18, 2014

How to invoke a stored procedure in Web API using Entity Framework

In this tutorial, we will go over sample code to retrieve data with Web API and Entity Framework (invoking a stored procedure to return data).

 Before getting started, I installed Entity Framework 4.3.0. To learn more about installation, click here....

 For this example, I had installed Northwind database and created a sample stored procedure that would return few records, which we will fetch through Web API methods in this illustration...

Create a sample procedure as below...



   To start with, create a folder called Data Model. Right click on Data Model and select add new item...


In the above, I created a new ADO.NET Entity Data Model named DBEntity. I will be using this one for the tutorial.

 I prefer to create an empty data model and add stored procedures as I go. (this works well with just stored procedures approach)...



Once you click finish, this should open up DBEntity.edmx file. Right click on Stored Procedures and click "update model from database"



Once you do that, make sure it has the connection to the DB server it needs to, if not click on New Connection and enter the server details and make sure the connection is working fine by clicking on Test Connection.

Finally we should see a window where it shows up with three tabs, Add, Refresh and Delete... In the Add tab, expand stored procedures and you should see the one created in first step. Check on the TestProc stored procedure and click finish... (refer below..)


Now, our DBEntity.edmx should be like the one below, with new stored procedure added to it...  To include a stored procedure, right click on Function Import  and select Add function import to add stored procedure.




In the window that appears,  select TestProc from stored procedure name and give "TestProc" for Function import name..

Once you click on get column information, this should give the set of columns that will be returned. Now click on the button "Create New Complex type" and this should create a new result-set as "TestProc_Result". Finally the screen should look like the below image.....  If so, then click ok...



Now, its time to get started on invoking the stored procedure and testing the results....  You can find the entity name in web.config and use it for data retrieval... (here the name is DBEntityContainer)

In my WebApiTestController, will add the following code (class and implementation to retrieve data).

 public class CategoryList
        {
            public int CategoryID { get; set; }
            public string CategoryName { get; set; }
            public string CategoryDescription { get; set; }
        }

        public IList<CategoryList> GetSampleData()
        {

            List<CategoryList> catList = new  List<CategoryList>();

            using(WebAPI.DataModel.DBEntityContainer test = new DataModel.DBEntityContainer())
            {
                var result = test.TestProc().ToList();

                foreach (var item in result.ToList())
                {
                    catList.Add(new CategoryList { CategoryID = item.CategoryID, CategoryName = item.CategoryName, CategoryDescription = item.Description});
                }
                return catList;
            }

        }


Now press F5 and you should be able to see the output as below...



Happy coding...



Monday, January 27, 2014

How to pass parameters to Kendo UI control through a data method instead of reading it through read action


Lets review a new way to pass parameter to Kendo Dropdownlist. This option is straightforward, when you have a parameter to invoke and nothing else to be processed while doing this.


Below is a sample code which will load a Kendo DropDownList with a defined parameter.



       The above can be achieved as below too.  Both the methods do the same thing, except in the method below, we can demand to do some extra steps and retrieve value to be passed. This might come in handy, when you want to read a control, which will not be loaded with data, while loading this control. There are few other instances, where this will be helpful. Once you have the parameter, you just have to invoke a force read for this drop down and the results will show up.

The sample code to do the same is given below....


Wednesday, January 22, 2014

A look into Web API - Creating a simple Web API application... - Part 2



Click here to view part 1



Lets create a model class for our sample and use it to test...





I had created a class called TestData.cs and defined it as below.




As said, I am extending the class from ApiController and added the following code...




Above code will make use of my model TestData to generate some sample data and to display them when invoked.

Now we are all set to run this web service and display the data to end users. When you press F5, you should be able to see the result below.. (I am using chrome to run it)



That's just getting started with Web API's... Happy coding...

A look into Web API - Creating a simple Web API application... - Part 1


ASP.Net Web API is a powerful framework to retrieve data and to serve a broad range of clients. Once created, it can be used to access data within mobile applications too.

   Web API's support developers to create RESTful services and the request is controlled by Controllers, which is similar to MVC controllers, but here the class will be derived from APIController when compared to MVC applications where it will be just Controller. This framework is really helpful to build HTTP based services and can very well control the returned data type  to be json or XML.

   If you are developing an application in MVC, it well suites to have Web API, as it is also based on routing concepts. More over, a given set of data can be accessed/modified through GET,PUT,DELETE etc.....

Now lets see how to create a simple Web API and invoke it through its URL....

I will create a new web API, by making the following choice : ASP.NET MVC 4 Web Application



    After making the selection, make sure you select Web API option (with View Engine type being Razor).



  Sample generated file structure below :


 
   Main controller method inherits from APIController with default value being ValuesController. For tutorial purpose, I will create a new controller WebAPITestController  that would generate and return data on invoking the method.





  Now we have the controller ready. Since this is a regular Controller, we should extend the class with  Sytem.Web.Http.ApiController to make it a Web API or we can select the following option when creating a controller, which will load with few empty basic method structures....






Click here to continue Creating a simple Web API....


 









Monday, January 20, 2014

Displaying an ascx control within MVC Razor view (as partial view)

In this blog, I would like to talk about including ascx controls within MVC pages and display them.

    Though, this one is not the preferred approach in general, but still if needed for common scenarios with avoidable runat=Server attribute elements, then yes, we can implement the below...

   Though I was able to successfully do this, but I was not successful in implementing the server side controls within the .ascx controls or .aspx pages. (will be glad to know if  any one did this way)

Lets see an example of how this can be achieved.

   In your MVC project,
  • navigate to Views and right click to select Add new item. 
  • Select a web user control and name it testview.ascx
  • Implement any basic html text and display any items you want to and save it.
  • Go to your MVC view (any .cshtml file ) that needs to display this control.
  •  Add this line, where the control should show up.. @(Html.Partial("testview",null));
Also, make sure the following changes are done, before running the project..

RegisterRoutes method should be defined as below..

 public static void RegisterRoutes(RouteCollection routes)
        {
            routes.IgnoreRoute("{resource}.axd/{*pathInfo}");
            routes.IgnoreRoute("{resource}.aspx/{*pathInfo}");
            routes.MapRoute(
                name: "Default",
                url: "{controller}/{action}/{id}",
                defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
            );
        }

Also delete the code behind files and designer files.  

This should be good enough to get you started.....


Using Install Package commands within Package Manager console

The moment I decided to get started with my new MVC project, NuGet package manager is the best bet to go further and install  Entity Framework, Web Optimization, MVC etc.

   Though there is a UI driven option, but also another way to do it through console is what I had been doing ( and I prefer that way).  Listing few options that I used to install from package manager console.

 For installing Entity framework, you could use the below one :

PM> Install-Package EntityFramework

The above might install a version more later than the one you might need. For example, when you need Entity Framework 4.3.1, but the latest is 6, you can still get a specific version by typing the following...

PM> Install-Package EntityFramework -Version 4.3.1


Using the -Version will help for all the nu-get packages that needs to be installed specifically... In this case, this might not work, since we have a latest version already installed, to fix this, we need to do a force uninstall and then install specific version as below...

PM> Uninstall-Package EntityFramework -force

PM> Install-Package EntityFramework -Version 4.3.1

Similarly, for Web API, you could do the following.

PM> Install-Package Microsoft.AspNet.WebApi

For specific version of Newton-soft Json (using json in our project)

PM> Install-Package Newtonsoft.Json -Version 4.5.6

To use the help option and to view list of commands..

PM> get-help Nuget

To get a list of all available packages

PM> Get-package -ListAvailable

   Running the above is going to take a lot of time, as the number of available packages will be more... You can try a restricted search based on a specific package as below.

PM> Get-Package -ListAvailable -Filter EntityFramework

In the above case, we are listing only EntityFramework related packages, thus narrowing the amount of items we need to go through.



Friday, January 17, 2014

To call a controller method in MVC from UI with callback to read callback data..

In this example lets review the code to invoke a controller method from UI and get JsonResult back. Based on the output data, we can loop through the records to retrieve the values.

Lets start writing the script to invoke it from View.....


In the above, Url.Action method, first parameter is the method to be invoked within HomeController, which is GetDatafromControllertoDisplay and the next one is the controller name. If any parameter needs to be passed, then pass it as above indicated (as it is done for param1).

Below is the callback method that will return data from the controller.


If multiple rows returned, use foreach statement, to loop through the records.