Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Dan 1274 posts 3885 karma points c-trib
    Nov 25, 2013 @ 15:10
    Dan
    0

    Connect and insert to custom database table in V6 MVC

    Hi,

    I need to add a simple record to a custom database table from within a WebApiController in Umbraco 6.1.6 MVC. What's the best current approach to doing this? I've seen suggestions of using PetaPoco but with my limited .NET skills this is over my head.

    Is this the best approach nowadays, and if so are there any starter guides as to how to do a simple SQL insert in the context of a WebApiController?

    Thanks

  • Comment author was deleted

    Nov 25, 2013 @ 15:13

    is the table in the same db as the umbraco db?

  • Dan 1274 posts 3885 karma points c-trib
    Nov 25, 2013 @ 15:19
    Dan
    0

    Yes - same database just a custom table.

  • Dan Lister 416 posts 1973 karma points c-trib
    Nov 25, 2013 @ 15:32
    Dan Lister
    0

    You could probably just use Entity Framework and have your data context use the Umbraco database connection string. EF's Code First approach will help you create the custom table if it doesn't exist. I'd personally use a different connection string though (just in case you wanted to spilt your database). There are lots of simple tutorials.

    using System.Data.Entity;
    
    namespace Application.Entities
    {
        public class CustomDbContext : DbContext
        {
            public BuildContext() : base("Name=umbracoDbDSN”)
            {
            }
        }
    }
    
  • Comment author was deleted

    Nov 25, 2013 @ 15:35

    Yeah petapoco is an option check http://creativewebspecialist.co.uk/2013/07/16/umbraco-petapoco-to-store-blog-comments/ to get started

    but you can still insert it in the way that you are used to if you have a preference... like entity framework

  • Comment author was deleted

    Nov 25, 2013 @ 15:36

    But that post should get you going, create the poco

    like

    [TableName("BlogComments")]
    [PrimaryKey("BlogCommentId", autoIncrement = true)]
    [ExplicitColumns]
    public class BlogComment
    {
        [Column("id")]
        [PrimaryKeyColumn(AutoIncrement = true)]
        public int BlogCommentId { get; set; }
    
        [Column("BlogPostUmbracoId")]
        public int BlogPostUmbracoId { get; set; }
    
        [Column("Name")]
        public string Name { get; set; }
    
        [Column("Email")]
        public string Email { get; set; }
    
        [Column("Website")]
        public string Website { get; set; }
    
        [Column("Message")]
        [SpecialDbType(SpecialDbTypes.NTEXT)]
        public string Message { get; set; }
    }
    

    then insert

            var blogPostToAdd = new BlogComment();
    
            //Set values from view model & grab the current node ID
            blogPostToAdd.UmbracoNodeId = UmbracoContext.PageId.Value;
            blogPostToAdd.Name          = model.Name;
            blogPostToAdd.Email         = model.Email;
            blogPostToAdd.Website       = model.Website;
            blogPostToAdd.Message       = model.Message;
    
            //Get the Umbraco db
            var db = ApplicationContext.DatabaseContext.Database;
    
            //Add the object to the DB
            db.Insert(blogPostToAdd);
    
  • Comment author was deleted

    Nov 25, 2013 @ 15:44

    If you share how your db table looks we can try to make the poco on the forum :)

  • Dan 1274 posts 3885 karma points c-trib
    Nov 25, 2013 @ 15:47
    Dan
    0

    Thanks Tim, I'll have a go at this. The first thing that strikes me is that the article says "create a folder in your Umbraco website called pocos and add a class file". This literally just means creating a folder in the root, right? Is this a convention?

  • Comment author was deleted

    Nov 25, 2013 @ 15:51

    Nah not a convention

    You just need to create your poco, you can choose where to create it :)

  • Dan 1274 posts 3885 karma points c-trib
    Nov 25, 2013 @ 16:02
    Dan
    0

    Thanks for your help on this Tim. So I've created the folder and created a class to reflect my database structure. Then it says:

    Next create a class file called RegisterEvents.cs this is used to hook into the Application Startup event of Umbraco, which will allow us to check if the custom database table exists or not and if it doesn’t then to create it for us.

    Where do I create this class? Can/should it go inside the WebApiController or somewhere else? Or... is this literally only required to generate the table on app start-up? In which case I probably don't need it as the table will be there.

  • Comment author was deleted

    Nov 25, 2013 @ 16:13

    You don't need that, since you already have the table :) that step is to auto create the table if it doesn't exist

    you only need to insert so this snippet

        var blogPostToAdd = new BlogComment();
    
        //Set values from view model & grab the current node ID
        blogPostToAdd.UmbracoNodeId = UmbracoContext.PageId.Value;
        blogPostToAdd.Name          = model.Name;
        blogPostToAdd.Email         = model.Email;
        blogPostToAdd.Website       = model.Website;
        blogPostToAdd.Message       = model.Message;
    
        //Get the Umbraco db
        var db = ApplicationContext.DatabaseContext.Database;
    
        //Add the object to the DB
        db.Insert(blogPostToAdd);
    
  • Comment author was deleted

    Nov 25, 2013 @ 16:17

    var myobject = new myobjectofpocotype()

    myobject.myprop = "value" .. repeat

    var db = ApplicationContext.DatabaseContext.Database; db.Insert(myobject);

  • Natt 5 posts 96 karma points
    Feb 19, 2018 @ 10:21
    Natt
    0

    don't forget .Current

    var db = ApplicationContext.Current.DatabaseContext.Database;

  • Dan 1274 posts 3885 karma points c-trib
    Nov 25, 2013 @ 16:31
    Dan
    0

    Wow, that worked first time! NICE! That was much easier than expected :)

    I have a couple of other questions to help tidy this up though and understand a bit more, if that's okay?

    So now, just for reference, I have a class in my 'pocos' folder like this:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Web;
    
    using Umbraco.Core.Persistence;
    using Umbraco.Core.Persistence.DatabaseAnnotations;
    
    namespace MyProject.pocos
    {
        [TableName("rating")]
        [PrimaryKey("id", autoIncrement = true)]
        [ExplicitColumns]
        public class ArticleRating
        {
            [Column("id")]
            [PrimaryKeyColumn(AutoIncrement = true)]
            public int id { get; set; }
    
            [Column("memberId")]
            public int memberId { get; set; }
    
            [Column("nodeId")]
            public int nodeId { get; set; }
    
            [Column("rating")]
            public int rating { get; set; }
        }
    }
    

    And I have a WebApiController like this:

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Net;
    using System.Web;
    using System.Web.Http;
    using System.Web.Mvc;
    using Umbraco.Web.WebApi;
    using umbraco.cms.businesslogic.member;
    using System.Data.SqlClient;
    using System.Configuration;
    using umbraco;
    using Umbraco.Core.Persistence;
    using MyProject.pocos;
    
    namespace MyProject.Controllers
    {
        public class ArticleRatingApiController : UmbracoApiController
        {
            public string GetArticleRating()
            {
                if (umbraco.library.IsLoggedOn())
                {
                    Member currentMember = Member.GetCurrentMember();
                    int memberId = currentMember.Id;
                    int nodeId = 1078;
                    int rating = 3;
    
                    var articleRatingToAdd = new ArticleRating();
    
                    //Set values to be saved
                    articleRatingToAdd.memberId = memberId;
                    articleRatingToAdd.nodeId = nodeId;
                    articleRatingToAdd.rating = rating;
    
                    //Get the Umbraco db
                    var db = ApplicationContext.DatabaseContext.Database;
    
                    //Add the object to the DB
                    db.Insert(articleRatingToAdd);
    
                    return "Rating submitted";
                }
                else
                {
                    return "Error - you are not logged in or do not have permission to perform the requested task.";
                }
            }
        }
    }
    

    Here are my questions....

    1. This controller is going to be called via AJAX. Currently I've hard-coded the rating and nodeId values, but these will be passed in through the AJAX call. Is the best way to get these just from a standard HTTP Context GET?

    2. Really, this method shouldn't be called 'GetArticleRating' it should be called 'SetArticleRating' but when I change the name I get an error message "The requested resource does not support http method 'GET'.". Presumably this is because I'm breaking some kind of MVC convention, but is there a way to rename the method something more appropriate?

    3. I need to allow the member only to rate the article once. So before I do the insert I need to ensure there are no records in the DB with the current memberId and nodeId. Would I do something like this?:

      int ratingId = db.Query("SELECT * FROM rating WHERE [email protected] AND [email protected]", memberId, nodeId);

    And check that ratingId hasn't got a value before doing the insert?

    Thanks again, much appreciated.

  • Comment author was deleted

    Nov 25, 2013 @ 16:34

    1) add params to your method and then just pass these as query string params 2) mark it with [httpGet] attribute 3) yeah that an option or use the relations api

  • Comment author was deleted

    Nov 25, 2013 @ 16:36

    Or maybe db.Query("SELECT * FROM rating WHERE [email protected] AND [email protected]", memberId, nodeId).Any() if it returns anything then it should't insert I guess

  • Dan 1274 posts 3885 karma points c-trib
    Nov 25, 2013 @ 16:59
    Dan
    0

    Excellent, very nearly there. Just getting an error with the pre-insert db check:

    The type arguments for method 'Umbraco.Core.Persistence.Database.Query<T>(string, params object[])' cannot be inferred from the usage. Try specifying the type arguments explicitly.
    

    Even just a hard-coded T-SQL string without the dynamic params does this. Any ideas? (Sorry if this is a really simple .NET question!)

    Thanks

  • Dan 1274 posts 3885 karma points c-trib
    Nov 25, 2013 @ 18:00
    Dan
    0

    On the last point, this seems to work:

    var check = db.Query<string>("SELECT TOP 1 * FROM rating WHERE [email protected] AND [email protected]",memberId,nodeId).Any();
    
    if (check){
        return "Error: You have already rated this article.";
    }else{
        db.Insert(articleRatingToAdd);
        return "Rating submitted";
    }
    
  • Comment author was deleted

    Nov 25, 2013 @ 18:03

    Sweet, glad you have it working!

  • Dan 1274 posts 3885 karma points c-trib
    Nov 29, 2013 @ 15:46
    Dan
    0

    Just in case anyone is doing this stuff, I've found this resource really useful for seeing how to perform basic database operations through PetaPoco: http://www.toptensoftware.com/petapoco/. For example, for my scenario where I'm working on a 'rate this article' feature, it's possible to grab the average rating from a node, via PetaPoco, like this:

    var averageRating = db.ExecuteScalar<long>("SELECT AVG(rating) FROM rating WHERE [email protected]", nodeId);
    

    Hope this helps someone at some point.

  • thanhnhu86 4 posts 53 karma points
    Dec 04, 2014 @ 16:10
    thanhnhu86
    0

    Hi everyone!

    I have a problem.

    I have a controller:

     public class CommentSurfaceController : SurfaceController

        {

            [HttpPost]

            public ActionResult AddComment(ArticleComment comment)

            {

                if (!ModelState.IsValid)

                {

                    return CurrentUmbracoPage();

                }

                var repo = new ModelRepository();

                comment.ArticleId = int.Parse(TempData["ArtId"].ToString());

                comment.CreatedTime = DateTime.Parse(string.Format("yyyy/MM/dd", DateTime.Now.Date));

     

                repo.InsertComment(comment);

                return RedirectToCurrentUmbracoPage();

            }

        }

    And model:

     [TableName("ARTICLECOMMENT")]

        [PrimaryKey("ID_ARTICLECOMMENT", autoIncrement = true)]

        public class ArticleComment

        {

     

            public int Id_ArticleComment { get; set; }

     

            [Required]

            public string CommentContent { get; set; }

     

            public int ArticleId { get; set; }

     

     

            public DateTime CreatedTime { get; set; }

     

            [Required]

            public string AddedName { get; set; }

     

            [Required]

            [EmailAddress]

            public string AddedEmail { get; set; }

        }

    And partial view:

    @using (Html.BeginUmbracoForm<CommentSurfaceController>("AddComment"))

    {

        <p>

            <label><small>Name (required)</small></label>

            @Html.TextBoxFor(x => x.AddedName)

           

        </p>

        <p>

            <label><small>Mail (required)</small></label>

            @Html.TextBoxFor(x => x.AddedEmail)

           

        </p>

        <p>

            <label><small>Comment (required)</small></label>

            @Html.TextAreaFor(x => x.CommentContent)

           

        </p>

        <p>

            <input type="submit" value="Send" />

            &nbsp;

            <input type="reset" id="reset" tabindex="5" value="Reset" />

        </p>

     

     

    }

    When I implement the partial view in a template :

       @Html.Partial("AddArticleComment", new ArticleComment())

    I cannot insert model Comment into database of umbraco, and it also do not throw error.........

    How can I result in the problem?????

    Appreciating any support!!!!!!!!!!!

     

  • Urvish 252 posts 775 karma points
    Jul 03, 2015 @ 06:59
    Urvish
    0

    Hi,

    I am trying to insert list of POCO class objects into DB.insert method.

    But it gives me error of "Parameter value mismatch".

    Below is my code.

                            var db = ApplicationContext.Current.DatabaseContext.Database;
    
    
                            List<DomainModel> lstDomain = new List<DomainModel>();
                            foreach (DataRow item in dt.Rows)
                            {
                                DomainModel objDomainList = new DomainModel();
                                objDomainList.DomainName = item.ItemArray[0].ToString();
                                lstDomain.Add(objDomainList);
                            }
                            //Add the object to the DB
                            db.Insert(lstDomain);
    

    POCO:

        [TableName("DomainList")]
        [PrimaryKey("Id", autoIncrement = true)]
        [ExplicitColumns]
        public class DomainModel
        { 
            [Column("Id")]
            [PrimaryKeyColumn(AutoIncrement = true)]
            public int Id { get; set; }
            [Column("DomainName")]
            public string DomainName { get; set; }
    
        }
    

    Can anyone help me how to add multiple values?

    And what to do to truncate table data before inserting the new data.

    Thanks in advance.

    Regards,

    Urvish Mandaliya

Please Sign in or register to post replies

Write your reply to:

Draft