Insert into SQL Table Using EnterpriseLibrary?

Question

I have a WCF service which needs to insert into a SQL 2005 log table after successfully executing one of its methods.

Using EnterpriseLibrary, I found some sample code which resembles this:

public static void SaveActivity(string sSomeString)
{
    Database db = DatabaseFactory.CreateDatabase();

    string sInsert = @"INSERT INTO ActivityTable (SomeString) VALUES @SomeString";

    DbCommand dbInsert = (DbCommand)db.ExecuteScalar(sInsert);

    db.AddInParameter(dbInsert, "SomeString", DbType.String, sSomeString;
}

With this code won't the input parameter be ignored, since it's defined after the ExecuteScalar method?!

Finally, with best practices in mind, instead of doing an INSERT, should I create a stored proc and pass parameters to it?

Answer 1

If you plan on putting a bunch of logic in the stored procedure then yes a stored proc would be the way to go.

From the security side of things, stored procedures are always better because the application doesn't have direct access to the table, so if there is a way to get past the parametrization then you still can't get access to the table data.

In other words, yes use a stored proc.

There are also execution plan cache issues with EF, Link, etc because they don't declare the parameter to be the same size as the column, so for each possible string length you end up with a separate execution plan.

Keep in mind I'm a DBA and I'll probably be flogged by some developers for saying that you should use a stored proc.