SQL Create new record in table, take record id and use in second procedure

Question

I have a somewhat tricky question to ask but I shall do my best.

I have two classes:

BlogPost
BlogTag

BlogPost has a public property HashSet<BlogTag> Tags, a collection of blog tags, simple enough. What I'm trying to achieve is to add a new BlogPost to my table dbo.BlogPost, and then iterate over the HashSet of tags and update a separate table dbo.BlogTags for example with each tag in the collection and the ID of the post it belongs to.

I would do this using something like: -

public static void AddPost(BlogPost post)
{
     try
     {
         Database db = DatabaseFactory.CreateDatabase(Constants.Application.DatabaseName);
         DbCommand cmd = db.GetStoredProcCommand("stp_BlogPost_Add");

         db.AddInParameter(cmd, "post_title", DbType.String, post.Title);
         db.AddInParameter(cmd, "author", DbType.String, post.Author);
         db.AddInParameter(cmd, "date_created", DbType.DateTime, DateTime.Now);
         db.AddInParameter(cmd, "post_content", DbType.String, post.Content);

         //Snip...

         //Insert tags
         foreach(BlogTag tag in post.Tags)
         {
             AddNewTags(post.ID, tag.TagText);
         }

         db.ExecuteNonQuery(cmd);
     }
     catch (Exception ex)
     {
         Logging.LogError(ex);
         throw;
     }
 }

However, the problem I can't seem to get around is this:

foreach(BlogTag tag in post.Tags)
{
    AddNewTags(post.ID, tag.TagText);
}

The above would work only when we have the post.ID value, however, since this is run in the AddPost method, the ID will still be the default 0 at this point (id for record is PK in table and is set to auto-increment.

Is there a way of passing in the HashSet<BlogTags> directly as a parameter to the stored procedure (worth mentioning that I'm a total SQL newbie), and then once the stp_BlogPost_Add procedure has run, get the id of the newly created post and insert the values into the BlogTags table?

Alternatively, is there a preferred approach of achieving what I want to do other than the above? I had considered simple storing the tags as a comma separated string in the BlogPost table and then splitting by , when needed but this doesn't seem as clean.

Any suggestions would be greatly appreciated

Is this MSSql ? You need to say. – Hugh Jones May 01 '13 at 14:32 — Hugh Jones, May 01 '13 at 14:32

score 1 · Accepted Answer · answered May 01 '13 at 14:37

1

You can't pass an object to a stored procedure as your SQL Engine will have no idea what that is. You can pass an XML string to it and operate against that. If you set your object up to be serializable, you can serialize it to an xml string and pass that in. It'll basically be an XML representation of your object.

http://msdn.microsoft.com/en-us/library/system.xml.serialization.xmlserializer.aspx

answered May 01 '13 at 14:37

Yatrix

13,361
16
48
78

This seems like an awful amount of work just to update two fields, it may work as a last solution though, thanks. – DGibbs May 01 '13 at 14:41
It depends on if he wants to do it all in one trip. Serialization isn't that much work. You just decorate your class a bit and serialize it. – Yatrix May 01 '13 at 14:46
`you` you mean ;) Yes doing it all in one trip would be nice. I know how to serialize my object and send it in as a string but how would SQL then extract the data it needs from this string? If I do it this way, i still need to get the ID of the post after it's created and then insert the record into the BlogTags table (with the new posts ID). Another poster mentioned (post now deleted) returning the postID after the add procedure completes, and then using that, is this feasible? – DGibbs May 01 '13 at 14:57
@DGibbs here is a pretty simple example: http://stackoverflow.com/questions/5006230/how-do-i-iterate-through-the-nodes-of-a-xml-field-in-t-sql. It's really easy, honestly. Just have to fit it to your needs – Yatrix May 01 '13 at 15:06
Thanks, I'll give this a try when I have more time +1. Could i still use `SCOPE_IDENTITY()` to get the postid (after it's created) within the `stp_BlogPost_Add` proc? As this is what I'm relying on to tie the tags to each post. – DGibbs May 01 '13 at 15:14
@DGibbs Use an OUTPUT return value and set it to your @@SCOPE_IDENTITY(). – Yatrix May 01 '13 at 15:28

SQL Create new record in table, take record id and use in second procedure

1 Answers1