Tuesday 29 April 2008

Parameterized INSERT with VistaDB

I'm continuing learning ASP.NET MVC by writing a very simple blog application using VistaDB as a backend, and basing my architecture loosely on Rob Conery's MVC Storefront screencasts. Today it was time to insert new posts into the database, and although it turned out to be fairly straightforward, I thought I would share the code anyway, as my first few attempts failed. The code shows how to add parameters and how to get the ID of the newly inserted row.

public void CreatePost(Post post)
{
    using (VistaDBConnection connection = new VistaDBConnection(connectionString))
    {
        connection.Open();
        string sql = "INSERT INTO Posts (BlogId, Title, Body, " + 
            "AuthorName, PublishedDate, LastModifiedDate, PublishState, " +
            "Slug) VALUES (@BlogId,@Title,@Body,@AuthorName," +
            "@PublishedDate,@LastModified,@PublishState,@Slug)";
        using (VistaDBCommand command = new VistaDBCommand(sql, connection))
        {
            command.Parameters.Add("@BlogId",post.BlogId);
            command.Parameters.Add("@Title",post.Title);
            command.Parameters.Add("@Body", post.Body);
            command.Parameters.Add("@AuthorName", post.Author);
            command.Parameters.Add("@PublishedDate", post.Published);
            command.Parameters.Add("@LastModified", post.LastModified);
            command.Parameters.Add("@PublishState", post.Status);
            command.Parameters.Add("@Slug", post.Slug);
            
            int rowsAffected = command.ExecuteNonQuery();
            if (rowsAffected != 1)
            {
                throw new InvalidOperationException("Failed to add post");
            }
        }
        using (VistaDBCommand command = new VistaDBCommand("SELECT @@IDENTITY", connection))
        {
            post.Id = (int)command.ExecuteScalar();                
        }
    }
}

1 comment:

Jason Short - VistaDB said...

Nicely done. Code looks good and is a good best practice example of how to position your using statements to make the code cleaner and simpler at the same time.