Home > View Post

Part II. Storing the Feedback Data

Yesterday I introduced a series of posts that would show the interesting parts of a (very basic) feedback service implementation. This is part II, here are the other bits:

Storing the feedback data

We had a glimpse of the FeedbackDal's signature (Dal = Data Access Layer) yesterday with this snippet (note, we don't really need any logic here so we go straight for the Dals. Grrrr!).

public class FeedbackService : ISendFeedbackContract
{
    public void SendFeedback(Feedback feedback)
    {
        try
        {
            FeedbackDal.InsertFeedback(feedback);
        }
        catch (Exception exc)
        {
            // TODO Don't forget your logging and exception shielding etc here
        }
    }
}

Given that we know the data structure (also revealed in yesterday post) we can easily infer the database schema. If it's fair to call a one table database a 'schema'. Anyway.

CREATE TABLE Feedback
(
    [FeedbackId] [int] NOT NULL IDENTITY(1, 1),
    [FeedbackType] [varchar] (250) NULL,
    [EmailAddress] [varchar] (250) NULL,
    [Comments] [varchar] (max) NULL,
    [ScreenGrab] [varbinary] (max) NULL,
    [Created] [datetime] NOT NULL CONSTRAINT [DF_Feedback_Created] DEFAULT (getdate())
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

ALTER TABLE [dbo].[Feedback] ADD CONSTRAINT [PK_Feedback] PRIMARY KEY CLUSTERED ([FeedbackId]) ON [PRIMARY]

Note that we've gone for a varbinary(max) datatype for our ScreenGrab. That's the recommended practice for SQL 2005, had we been using SQL 2000 we'd have opted for an image type.

Injecting the data is surprisingly easy. To keep the ADO.NET code to a minimum we're going to use Patterns & Practices Data Access Application Block (DAAB) that ships with Enterprise Library 3.1.

I admit that I used to hate the first DAAB, but things really have come a long way in the Application Blocks since their first versions. If you've abandoned Enterprise Library because of any earlier disappointments, I'd encourage you to take a second look.


public static class FeedbackDal
{
    public static void InsertFeedback(Feedback feedback)
    {
        Database db = DatabaseFactory.CreateDatabase("FeedbackService");

        DbCommand cmd = db.GetSqlStringCommand(
@"INSERT INTO Feedback (FeedbackType, EmailAddress, Comments, ScreenGrab)
VALUES (@FeedbackType, @EmailAddress, @Comments, @ScreenGrab)"
);

        db.AddInParameter(cmd, "@FeedbackType", DbType.String, feedback.FeedbackType);
        db.AddInParameter(cmd, "@EmailAddress", DbType.String, feedback.EmailAddress);
        db.AddInParameter(cmd, "@Comments", DbType.String, feedback.Comments);
        db.AddInParameter(cmd, "@ScreenGrab", DbType.Binary, feedback.ScreenGrab);

        db.ExecuteNonQuery(cmd);
    }
}

And we're done. Don't forget to add the connectionString "FeedbackService" to your web.config, but otherwise, that really is it!

Next we'll look at grabbing the screenshot on the client and posting it up to our service.

Part III: Grab the ScreenShot

Tags: .NET

 
Josh Post By Josh Twist
12:38 AM
07 Aug 2007

» Next Post: WPF Image element locks my local file
« Previous Post: Part I. Designing the Feedback Contract

Comments are closed for this post.

© 2005 - 2017 Josh Twist - All Rights Reserved.