none
Can't do CRUD operation on Oracle database using EF 5.0 in asp.net mvc 4 RRS feed

  • Question

  • Actually I'm using Entity-Framework 5.0 with Oracle 11g in an Asp.Net MVC4 application. For some reason when I run the Entity Data Model wizard and select tables and store procedures from the wizard to generate entities and object context classes. The automatic generated code doesn't give me any dataobject class or any property or methods in the generated classes by using which I can write my own C# code in MVC to use Store Procedures for CRUD operations. I can't do direct insert,delete,edit or update in to Oracle tables, I have to have store procedures to perform CRUD operations on data tables. And one more thing all these store procedures are not standalone they are packaged in to difference packages. And Entity Data Wizard is showing all these store procedures as individual store procedures with in a given package.I could be wrong they way i explained the package thing, because I'm very new to Oracle and never dealt with packages as such.

    And when I use EF to Sql Server to generate code for Tables and Store Procedures, it gives me thousands lines of code just for 5 to 10 tables and 4-5 store procedures, which is not case for Oracle database.

    I get exactly the same automatically generated code for Oracle tables whether I only select tables from Entity Data Model wizard or I select both the tables and store procedures from the same oracle database.

    Why no code is getting generated by EF for oracle store procedures, I'm highly shocked after seeing all this. When I can select store procedures from Entity Data Model wizard why is no code is generated to access these store procedures to perform CRUD operatiions.

    Thanks

    Saturday, March 8, 2014 4:04 PM

Answers

  • The blow is a little project I rolled my own from the EF tutorial log ago.

    <http://www.codeguru.com/csharp/csharp/net30/article.php/c15489/ADONET-Entity-Framework-Tutorial-and-Basics.htm>

    <http://www.vbforums.com/showthread.php?540421-Tutorial-An-Introduction-to-the-ADO-NET-Entity-Framework>

    The Entities classlib project holds all DTO(s), which is being referenced by the MVC controller, the BLL Business Logic layer and the DAL
    Data Access Layer. You don't see the BLL you just see the DAL. So think about how you are going to use the DTO(s) in the DAL with your sprocs
    and how you are going to use your DTO at the MVC level like it's being done with Article, the MVC Model object.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web.Mvc;
    using BLL;
    using Castle.Core.Logging;
    using Common;
    using Entities;
    using MvcPub.Models;

    namespace MvcPub.Controllers
    {
        public class ArticleController : Controller
        {

            #region "Depenencies"
            public IArticleBusMgmt articleBusMgmt { get; set; }
            public IAuthorBusMgmt authorBusMgmt { get; set; }

            // this is Castle.Core.Logging.ILogger, not log4net.Core.ILogger
            public ILogger Logger { get; set; }
            #endregion

           
            //
            // GET: /Article/

            #region "Public Methods"
            public ActionResult Index()
            {
                return View();
            }
           
            public ActionResult List()
            {
                return View("List", MapDTOToAuthors(authorBusMgmt.GetAuthors()));
            }

            public ActionResult Add()
            {
                var article = new Article();

                ViewBag.Message = "Add Article";

                // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                if (HttpContext != null)
                   article = new Article {AuthorID = (int) Session["authorid"]};
       
                return View("Add", article);
            }

            [HttpPost]
            public ActionResult Add(Article addmodel, FormCollection formCollection)
            {
                ViewBag.Message = "Add Article";

                if (Helper.IsCancel(formCollection))
                    return RedirectToAction("ArticleList", new { id = addmodel.AuthorID });

                try
                {
                    articleBusMgmt.SaveArticle(MapArticleToDTO(addmodel));
                }
                catch (ValiadationException ex)
                {
                    // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                    if (HttpContext == null)
                        throw;

                    ViewBag.Message = ex.Message;
                    return View(addmodel);
                }
                catch (Exception ex)
                {
                    Logger.Fatal(ex.ToString());
                    ViewBag.Message = "Fatal error has occured.";
                    return View("Add", addmodel);
                }

                return RedirectToAction("ArticleList", new { id = addmodel.AuthorID });
            }

            public ActionResult Edit(Int32 id)
            {
                ViewBag.Message = "Edit Article";
            
                return View("Add", MapDTOTOArticle(articleBusMgmt.GetAticleByID(id)));
            }

            [HttpPost]
            public ActionResult Edit(Article editmodel, FormCollection formCollection)
            {
                ViewBag.Message = "Edit Article";

                if (Helper.IsCancel(formCollection))
                    return RedirectToAction("ArticleList", new { id = editmodel.AuthorID });

                try
                {
                    articleBusMgmt.SaveArticle(MapArticleToDTO(editmodel));
                }
                catch (ValiadationException ex)
                {
                    // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                    if (HttpContext == null)
                        throw;

                    ViewBag.Message = ex.Message;
                    return View("Add", editmodel);
                }
                catch (Exception ex)
                {
                    Logger.Fatal(ex.ToString());
                    ViewBag.Message = "Fatal error has occured.";
                    return View("Add", editmodel);
                }

                return RedirectToAction("ArticleList", new { id = editmodel.AuthorID });
               
            }

            public ActionResult Select(Int32 id)
            {
                var theid = id;

                return RedirectToAction("ArticleList", new { id = theid});
            }

            public ActionResult ArticleList(Int32 id)
            {
                // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                if (HttpContext != null)
                    Session["authorid"] = id;

                return View("ArticleList", MapDTOTOArticles(authorBusMgmt.GetAuthorByID(id)));
            }

            public ActionResult Delete(Int32 id, Int32 authid)
            {
                articleBusMgmt.DeleteArticle(new DTOArticle { ArticleID = id });

                return RedirectToAction("ArticleList", new { id = authid });
            }

            #endregion

            #region "Private Methods"
            private List<Author> MapDTOToAuthors(IEnumerable<DTOAuthor> dtos)
            {
                return dtos.Select(dto => new Author { AuthorID = dto.AuthorID, Firstname = dto.FirstName, Lastname = dto.LastName }).ToList();
            }

            private List<Article> MapDTOTOArticles(DTOAuthor dto)
            {
                return dto.Articles.Select(dtoart => new Article
                                                         {
                                                             ArticleID = dtoart.ArticleID, AuthorID = (int) dtoart.AuthorID, Title = dtoart.Title, Body = dtoart.Body
                                                         }).ToList();
            }

            private Article MapDTOTOArticle(DTOArticle dto)
            {
                var article = new Article
                                  {
                                      ArticleID = dto.ArticleID,
                                      AuthorID = (int) dto.AuthorID,
                                      Title = dto.Title,
                                      Body = dto.Body
                                  };

                return article;
            }

            private DTOArticle MapArticleToDTO(Article article)
            {
                var dto = new DTOArticle
                              {
                                  ArticleID = article.ArticleID,
                                  AuthorID = article.AuthorID,
                                  Title = article.Title.Trim(),
                                  Body = article.Body.Trim()
                              };

                return dto;
            }
          
            #endregion
        }
    }


    using System;
    using System.Collections.Generic;
    using Entities;

    namespace DAL
    {
        public interface IArticleDalMgr
        {
            DTOArticle GetArticleByID(Int32 id);
            List<DTOArticle> GetArticlesByAuthorID(int id);
            void SaveArticle(DTOArticle dto);
            void DeleteArticle(DTOArticle article);
        }
    }


    using System.Collections.Generic;
    using System.Data.EntityClient;
    using System.Linq;
    using Common;
    using DAL.Model;
    using Entities;

    namespace DAL
    {
        public class ArticleDalMgr : IArticleDalMgr
        {

            #region "Public Methods"
            public DTOArticle GetArticleByID(int id)
            {
                var dto = new DTOArticle();

                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var result = (from a in db.Article.Where(a => a.ArticleID== id) select a).Single();

                        dto.ArticleID = result.ArticleID;
                        dto.AuthorID = result.AuthorID;
                        dto.Title = result.Title.Trim();
                        dto.Body = string.IsNullOrEmpty(result.Body) ? "" : result.Body.Trim();
                       
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

                return dto;
            }

            public List<DTOArticle> GetArticlesByAuthorID(int id)
            {
                 var dtos = new List<DTOArticle>();

                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var results = (from a in db.Article.Where(a => a.AuthorID == id) select a).ToList();

                        dtos.AddRange(results.Select(item => new DTOArticle()
                        {
                            ArticleID = item.ArticleID,
                            AuthorID = item.AuthorID,
                            Title = item.Title,
                            Body =  item.Body
                        }).ToList());
                       
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

                return dtos;
            }

            public void SaveArticle(DTOArticle dto)
            {
                if (dto.ArticleID == 0)
                {
                    AddArticle(dto);
                }
                else
                {
                    UpdateArticle(dto);
                }
            }

            public void DeleteArticle(DTOArticle article)
            {
                var art = new Article { ArticleID = article.ArticleID };

                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        db.AttachTo("Article", art);
                        db.DeleteObject(art);
                        db.SaveChanges();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            #endregion
       
            #region "Private Methods"
            private void UpdateArticle(DTOArticle article)
            {
                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var art = new Article() { ArticleID = (int) article.ArticleID };

                        db.AttachTo("Article", art);
                        art.AuthorID = article.AuthorID;
                        art.Title = article.Title.Trim();
                        art.Body = article.Body.Trim();

                        db.SaveChanges();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            private void AddArticle(DTOArticle article)
            {
                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var art = new Article()
                        {
                            AuthorID = article.AuthorID,
                            Title = article.Title.Trim(),
                            Body = article.Body.Trim()
                        };


                        db.AddToArticle(art);
                        db.SaveChanges();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            #endregion 
        }
    }

    Sunday, March 9, 2014 6:28 PM

All replies

  • You are talking EF Code First  most likely and MVC that EF crutch with MVC. Why don't you try the Model First approach  with EF and using a Data Access Layer or Repository design pattern?

    Let the MVC controllers make calls to the DAL or Repository where the Model First model resides and do CRUD operations. Your sprocs should be based on doing CRUD for a single table on the Model and not some sprocs that have been used in the past with sprocs  calling other sprocs, which most likely is not going to work for you. It is not working for you.

    If all you are doing is calling sprocs, then why even use EF? EF is buying you nothing in this situation,  and may be you need to be using straight up ADO.NET with ADO.NET Oracle Command objects with a Datareader,  List<t> of DTO(s) Data Transfer Objects or a single DTO sending the DTO(s) or DTO to the MVC controller and the MVC controller sending the DTO(s) or DTO back to the DAL or Repository, and using your sprocs, which would be implemented in the DAL or Repository. 

     
    Saturday, March 8, 2014 7:18 PM
  • Thanks a lot for your reply, that's what my apprehension was. That I need to use Ado.net with all those datareaders, datasets, oracle commands etc.

    Because, sprocs are not at all useful in this situation and you are right the EF is buying me nothing.

    Thanks

    Yogesh Sharma

    Saturday, March 8, 2014 8:51 PM
  • You need to learn how to make a DTO and use a List<T> of DTO(s) or send a single DTO to the MVC and send them back to database to be persisted.

    http://en.wikipedia.org/wiki/Data_transfer_object

     public partial class DTOArticle

      {
            public Int32 ArticleID { get; set; }

            public String Title { get; set; }

            public String Body { get; set; }

      }

    Use DTO(s) a List<DTOArticle> send the collection back or send an individual DTOArticle to the MVC Controller and forget about Datatables and Datasets.

    Saturday, March 8, 2014 9:35 PM
  • Can anyone give me an example in Asp.Net MVC  to perform crud using DTO's , let it be a little Model having just one class and mapping to a data table in oracle database and performing crud operations through sprocs.

    Thanks

    Sunday, March 9, 2014 3:13 PM
  • The blow is a little project I rolled my own from the EF tutorial log ago.

    <http://www.codeguru.com/csharp/csharp/net30/article.php/c15489/ADONET-Entity-Framework-Tutorial-and-Basics.htm>

    <http://www.vbforums.com/showthread.php?540421-Tutorial-An-Introduction-to-the-ADO-NET-Entity-Framework>

    The Entities classlib project holds all DTO(s), which is being referenced by the MVC controller, the BLL Business Logic layer and the DAL
    Data Access Layer. You don't see the BLL you just see the DAL. So think about how you are going to use the DTO(s) in the DAL with your sprocs
    and how you are going to use your DTO at the MVC level like it's being done with Article, the MVC Model object.

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web.Mvc;
    using BLL;
    using Castle.Core.Logging;
    using Common;
    using Entities;
    using MvcPub.Models;

    namespace MvcPub.Controllers
    {
        public class ArticleController : Controller
        {

            #region "Depenencies"
            public IArticleBusMgmt articleBusMgmt { get; set; }
            public IAuthorBusMgmt authorBusMgmt { get; set; }

            // this is Castle.Core.Logging.ILogger, not log4net.Core.ILogger
            public ILogger Logger { get; set; }
            #endregion

           
            //
            // GET: /Article/

            #region "Public Methods"
            public ActionResult Index()
            {
                return View();
            }
           
            public ActionResult List()
            {
                return View("List", MapDTOToAuthors(authorBusMgmt.GetAuthors()));
            }

            public ActionResult Add()
            {
                var article = new Article();

                ViewBag.Message = "Add Article";

                // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                if (HttpContext != null)
                   article = new Article {AuthorID = (int) Session["authorid"]};
       
                return View("Add", article);
            }

            [HttpPost]
            public ActionResult Add(Article addmodel, FormCollection formCollection)
            {
                ViewBag.Message = "Add Article";

                if (Helper.IsCancel(formCollection))
                    return RedirectToAction("ArticleList", new { id = addmodel.AuthorID });

                try
                {
                    articleBusMgmt.SaveArticle(MapArticleToDTO(addmodel));
                }
                catch (ValiadationException ex)
                {
                    // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                    if (HttpContext == null)
                        throw;

                    ViewBag.Message = ex.Message;
                    return View(addmodel);
                }
                catch (Exception ex)
                {
                    Logger.Fatal(ex.ToString());
                    ViewBag.Message = "Fatal error has occured.";
                    return View("Add", addmodel);
                }

                return RedirectToAction("ArticleList", new { id = addmodel.AuthorID });
            }

            public ActionResult Edit(Int32 id)
            {
                ViewBag.Message = "Edit Article";
            
                return View("Add", MapDTOTOArticle(articleBusMgmt.GetAticleByID(id)));
            }

            [HttpPost]
            public ActionResult Edit(Article editmodel, FormCollection formCollection)
            {
                ViewBag.Message = "Edit Article";

                if (Helper.IsCancel(formCollection))
                    return RedirectToAction("ArticleList", new { id = editmodel.AuthorID });

                try
                {
                    articleBusMgmt.SaveArticle(MapArticleToDTO(editmodel));
                }
                catch (ValiadationException ex)
                {
                    // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                    if (HttpContext == null)
                        throw;

                    ViewBag.Message = ex.Message;
                    return View("Add", editmodel);
                }
                catch (Exception ex)
                {
                    Logger.Fatal(ex.ToString());
                    ViewBag.Message = "Fatal error has occured.";
                    return View("Add", editmodel);
                }

                return RedirectToAction("ArticleList", new { id = editmodel.AuthorID });
               
            }

            public ActionResult Select(Int32 id)
            {
                var theid = id;

                return RedirectToAction("ArticleList", new { id = theid});
            }

            public ActionResult ArticleList(Int32 id)
            {
                // The below HTTP check is for Controller Unit Test to pass test, becuase it's not in an HTTP session.

                if (HttpContext != null)
                    Session["authorid"] = id;

                return View("ArticleList", MapDTOTOArticles(authorBusMgmt.GetAuthorByID(id)));
            }

            public ActionResult Delete(Int32 id, Int32 authid)
            {
                articleBusMgmt.DeleteArticle(new DTOArticle { ArticleID = id });

                return RedirectToAction("ArticleList", new { id = authid });
            }

            #endregion

            #region "Private Methods"
            private List<Author> MapDTOToAuthors(IEnumerable<DTOAuthor> dtos)
            {
                return dtos.Select(dto => new Author { AuthorID = dto.AuthorID, Firstname = dto.FirstName, Lastname = dto.LastName }).ToList();
            }

            private List<Article> MapDTOTOArticles(DTOAuthor dto)
            {
                return dto.Articles.Select(dtoart => new Article
                                                         {
                                                             ArticleID = dtoart.ArticleID, AuthorID = (int) dtoart.AuthorID, Title = dtoart.Title, Body = dtoart.Body
                                                         }).ToList();
            }

            private Article MapDTOTOArticle(DTOArticle dto)
            {
                var article = new Article
                                  {
                                      ArticleID = dto.ArticleID,
                                      AuthorID = (int) dto.AuthorID,
                                      Title = dto.Title,
                                      Body = dto.Body
                                  };

                return article;
            }

            private DTOArticle MapArticleToDTO(Article article)
            {
                var dto = new DTOArticle
                              {
                                  ArticleID = article.ArticleID,
                                  AuthorID = article.AuthorID,
                                  Title = article.Title.Trim(),
                                  Body = article.Body.Trim()
                              };

                return dto;
            }
          
            #endregion
        }
    }


    using System;
    using System.Collections.Generic;
    using Entities;

    namespace DAL
    {
        public interface IArticleDalMgr
        {
            DTOArticle GetArticleByID(Int32 id);
            List<DTOArticle> GetArticlesByAuthorID(int id);
            void SaveArticle(DTOArticle dto);
            void DeleteArticle(DTOArticle article);
        }
    }


    using System.Collections.Generic;
    using System.Data.EntityClient;
    using System.Linq;
    using Common;
    using DAL.Model;
    using Entities;

    namespace DAL
    {
        public class ArticleDalMgr : IArticleDalMgr
        {

            #region "Public Methods"
            public DTOArticle GetArticleByID(int id)
            {
                var dto = new DTOArticle();

                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var result = (from a in db.Article.Where(a => a.ArticleID== id) select a).Single();

                        dto.ArticleID = result.ArticleID;
                        dto.AuthorID = result.AuthorID;
                        dto.Title = result.Title.Trim();
                        dto.Body = string.IsNullOrEmpty(result.Body) ? "" : result.Body.Trim();
                       
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

                return dto;
            }

            public List<DTOArticle> GetArticlesByAuthorID(int id)
            {
                 var dtos = new List<DTOArticle>();

                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var results = (from a in db.Article.Where(a => a.AuthorID == id) select a).ToList();

                        dtos.AddRange(results.Select(item => new DTOArticle()
                        {
                            ArticleID = item.ArticleID,
                            AuthorID = item.AuthorID,
                            Title = item.Title,
                            Body =  item.Body
                        }).ToList());
                       
                    }
                    finally
                    {
                        conn.Close();
                    }
                }

                return dtos;
            }

            public void SaveArticle(DTOArticle dto)
            {
                if (dto.ArticleID == 0)
                {
                    AddArticle(dto);
                }
                else
                {
                    UpdateArticle(dto);
                }
            }

            public void DeleteArticle(DTOArticle article)
            {
                var art = new Article { ArticleID = article.ArticleID };

                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        db.AttachTo("Article", art);
                        db.DeleteObject(art);
                        db.SaveChanges();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }
            #endregion
       
            #region "Private Methods"
            private void UpdateArticle(DTOArticle article)
            {
                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var art = new Article() { ArticleID = (int) article.ArticleID };

                        db.AttachTo("Article", art);
                        art.AuthorID = article.AuthorID;
                        art.Title = article.Title.Trim();
                        art.Body = article.Body.Trim();

                        db.SaveChanges();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            private void AddArticle(DTOArticle article)
            {
                using (var conn = new EntityConnection(Constants.pcdb))
                using (var db = new PublishingCompanyEntities(conn))
                {
                    try
                    {
                        var art = new Article()
                        {
                            AuthorID = article.AuthorID,
                            Title = article.Title.Trim(),
                            Body = article.Body.Trim()
                        };


                        db.AddToArticle(art);
                        db.SaveChanges();
                    }
                    finally
                    {
                        conn.Close();
                    }
                }
            }

            #endregion 
        }
    }

    Sunday, March 9, 2014 6:28 PM
  • Thanks a lot, let me try to work on it and if I come across any problem/issue will let you know.

    God bless you all.

    Very Helpful and Kind People are out there in the world.

    Thanks.

    Sunday, March 9, 2014 6:50 PM
  • Please on reply posts that answered your questions mark them as "Marked as Answered".
    Sunday, March 9, 2014 9:08 PM