CODEWITHSUNDEEP
c#sql-serverdesign-patternsstored-proceduressql-server-2008
DGibbs
DGibbs

Reputation: 14618

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

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

I have two classes:

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

Upvotes: 0

Views: 361

Answers (1)

Yatrix
Yatrix

Reputation: 13775

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

Upvotes: 1

Related Questions