RSS

Elegant Way of Executing a Stored Procedure using LINQ?

22 Nov

I was working in a project today and I was trying to create a generic way of using a DataContext class to execute a stored procedure.  Of course there’s the way of dragging a stored procedure onto the designer, etc… but I’m not interested in this at the moment.  TBQH, I haven’t even been doing the ORM mapping in this manner lately after using the System.Data.Linq.Mapping namespace to map a POCO (Plain ‘Ole CLR Object) to an actual SQL table (as well as using ColumnAttribute‘s to map the properties to columns.  This is extremely helpful for larger 2.0 projects that were recently converted to 3.5.   Some might argue that this isn’t how LINQ was supposed to be used or that it makes it more complicated.   First of all, I don’t think that there is any right or wrong way to use LINQ.  Secondly, I find it can be a pain to have to remove the table from the designer, reconnect via Server Explorer, refresh the database, re-drag onto designer over-and-over when making changes to tables, views, procedures, etc…  This is also problematic if you don’t have access to a production database from your dev environment.  Most web servers I experience don’t have Visual Studio 2008 installed so it’s not like I can quickly generate a newer set of LINQ-to-SQL classes.  If a table column name changes, I simply refactor the corresponding property in my library and I’m good to go.

[Table(Name = “dbo.FooTable”)]
public class Foo
{
#region Table Columns / Properties

[Column]
public long FooId { get; set; }

[Column]
public string FooName { get; set; }

[Column]
public string FooDescription { get; set; }

#endregion Table Columns / Properties
}

Easy.

public class Database<TDataContext, TEntity>
{
// …
}

Take the previous data access helper class.  Let’s say I want to create just a generic function to execute a stored procedure against my database.  Let’s call it “ExecuteCommand”.

public static void ExecuteCommand(string command, object[] parameters)
{
if (string.IsNullOrEmpty(command))
throw new ArgumentNullException(“command”);

using (var database = new TDataContext())
database.ExecuteCommand(command, parameters);
}

Basic.  The implementation is pretty mindless as well.

Database<MyDataContext, TFoo>.ExecuteCommand(“CommandName”, null);

This sucks when you have parameters because I haven’t found any documentation on how to execute a procedure elegantly using this approach.  It appears that the DataContext.ExecuteCommand approach is best-suited for on-the-fly dynamic SQL…. gross!  I also read (need to find the link again) that some super-sleuthing uncovered that parameter substitution is silently ignored.  (I’ll have to check back on that)

I did find a way to implement using a stored procedure….

string command = string.Format(“exec Command ‘{0}’, {1}”, “string”, 1);
Database<MyDataContext, TFoo>.ExecuteCommand(command, null);

That is disgusting.  Not a huge fan.  What if you have a procedure that has 5, 10, 20 parameters? Ouch.  What if your input parameters contain ‘ ‘s? You would then have to replace all ‘ with a ”.

Any suggestions?  I’m hoping that there is a much more elegant / succinct way of doing this that I haven’t found yet.  I’ve tried to “RTFM” as they say but a couple thousand pages of LINQ reference books and I’ve got nothing.

Here’s a little 2.0 throwback of what I’m trying to accomplish.  Like I said before, there has to bge a more elegant way of doing this with the 3.5 Framework / LINQ / DataContext that doesn’t involve dragging-and-dropping procedures onto the designer.

public static int ExecuteProcedure(List<SqlParameter> parameters, string command)
{
try
{
using (DBManager manager = …)
{
manager.Open();
manager.CreateParameters(parameters.Count);

for (int i = 0; i < parameters.Count; ++i)
manager.AddParameters(i, parameters[i].ParameterName, parameters[i].Value);

return (int) manager.ExecuteScalar(CommandType.StoredProcedure, command);
}
}
catch (SqlException)
{
return 0;
}

}

Help me from the following please!!

StringBuilder query = new StringBuilder(“exec Command “);
query.AppendFormat(“{0},”, foo.FooProperty1);
query.AppendFormat(“‘{0}’,”, foo.FooProperty2.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty3.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty4.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty5.Replace(“‘”, “””));
query.AppendFormat(“‘{0}’,”, foo.FooProperty6.Replace(“‘”, “””));
query.AppendFormat(“‘{0}'”, foo.FooProperty7.Replace(“‘”, “””));

Database<MyDataContext, Foo>.ExecuteCommand(query.ToString(), null);

C#, LINQ

Referenced by: http://www.willasrari.com/blog/elegant-way-of-executing-a-stored-procedure-using-linq/000312.aspx

Advertisements
 
Leave a comment

Posted by on November 22, 2010 in C#, LinQ

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: