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

Yes - same database just a custom table.

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”)
        {
        }
    }
}

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

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

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?

Nah not a convention

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

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.

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);

var myobject = new myobjectofpocotype()

myobject.myprop = "value" .. repeat

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

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 memberId=@0 AND nodeId=@1", memberId, nodeId);

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

Thanks again, much appreciated.

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

Or maybe db.Query("SELECT * FROM rating WHERE memberId=@0 AND nodeId=@1", memberId, nodeId).Any() if it returns anything then it should't insert I guess

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

On the last point, this seems to work:

var check = db.Query<string>("SELECT TOP 1 * FROM rating WHERE memberId=@0 AND nodeId=@1",memberId,nodeId).Any();

if (check){
    return "Error: You have already rated this article.";
}else{
    db.Insert(articleRatingToAdd);
    return "Rating submitted";
}

Sweet, glad you have it working!

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 nodeId=@0", nodeId);

Hope this helps someone at some point.

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!!!!!!!!!!!

 

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

is working on a reply...