advanced web statistics

LINQ to SQL and Using Multiple Connection Strings

So I found something pretty interesting today in regards to LINQ and LINQ-to-SQL classes.  I was setting up a staging server to deploy a new 3.5 web application.  I then realized, "Oh crap, different connection string!".  This should be easy as all I should have to do is modify the app.config and update the connectionString accordingly. WRONG!

The connection string is actually compiled into the code. That sucks. Here's what I see in Reflector:

[SpecialSetting(SpecialSetting.ConnectionString), ApplicationScopedSetting, DebuggerNonUserCode, DefaultSettingValue("Data Source=(local);Initial Catalog=FooDB;User ID=;Password=")]
public string FooDBConnectionString
{
   get
   {
      return (string) this["FooDBConnectionString"];
   }


Gross.  I thought of a work-around that could work.  This code is in the Settings.Designer.cs class so I could do something like:

public string FooDBConnectionString
{
#if DEBUG
   get { return ((string)(this["FooDBConnectionStringDev"])); }
#else
   
get { return ((string)(this["FooDBConnectionStringProduction"])); }
#endif
}

This way the appropriate connection string could be compiled into the code depending on whether or not the configuration for the project is set to Debug or Release.  The thing about this solution is that you would have to write this code each and every time you regenerate your LINQ-to-SQL class file(s).  Very inefficient. *NOTE* For this to even work you have to add another connection string in your Settings file. No thanks.

The REAL way to solve this:

  1. Create a LINQ-to-SQL dbml file with an initial connection string
  2. Right-click dbml surface
  3. Click 'Properties'
  4. Expand the 'Connection' node
  5. Select 'None' for Connection String
  6. Select 'True' for Application Settings (don't know if this is completely necessary)
  7. Create a partial class for 'YourDataContextHere'
  8. Within this partial class create a parameterless constructor for 'YourDataContextHere'
  9. Set your base connection string accordingly a la:

public FooDB() : base(ConfigurationManager.ConnectionStrings["FooDB"].ConnectionString, mappingSource)
{
   // anything else

Just make sure you have ConnectionStrings node in your app/web configuration files and that you have the specified key in there or this won't work :D

Easy. Now you can throw a different connection string in your Business Layer's configuration file and be good to go.

Thanks to Jon Gallant and David Klein for the initial posts.

Tags: LINQ

Elegant Way of Executing a Stored Procedure using LINQ?

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); 

Tags: C#, LINQ

LINQ and Generic GetRecord Method

Lately I've been abstracting some LINQ functionality into a Generic Data Access Class so that it can be reused across multiple projects without having to write out functionality over-and-over (i.e. creating a RetrieveProductsByProductId method, AddNewProduct, DeleteProduct, etc...).

CRUD'ing is a braindead process using LINQ & Generics.  I've found that Update is / can be a huge pain in the ass.  I currently have 2 versions of an update method (working on a 3rd) but just thought I'd kick-off the next couple of posts by posing the following question:

Question:  How well do you think the following would scale?

public T GetRecord<T>(int primaryKey) where T : class
{
   using (var database = new TData())
      return database.ExecuteQuery<T>(string.Format("SELECT * FROM {0} WHERE ID={1}", typeof(T).Name, primaryKey)).Single();

I found this little beauty during my travels to find examples of generic update methods.  This is obviously not an update method but definitely worthy of posting by me.  This seems a little too specific to the database implemented so it begs the question of why one would even try to make it generic.

Here's how I (and Dwight) would implement:

public static T GetRecord<T>(Expression<Func<T, bool>> predicate)
{
   if (predicate == null)
      throw new ArgumentNullException("predicate", "lulz");

   return new TData().GetTable<T>().Where(predicate).SingleOrDefault();
}

Not Shakespeare but it sure is a lot more practical than the previous example IMHO.

var foo = DataAccess<FooDB>.GetRecord<Foo>(f => f.Name == "Stihl 066");

Easy.  I'll be prepping some more entries related to Generic updating of SQL data soon.  I could really use some feedback from some more seasoned LINQ'ers.

Tags: C#, LINQ

LINQ and Reflecting Object Properties

In working on a recent project over the weekend I decided to switch to .NET 3.5.  I haven't implemented OR/M with LINQ yet so I decided to re-write some of the class library functionality (and existing Data Access Layer) using LINQ, lambda's, and other syntactical sugar.  Keep in mind this is a personal project so I can spend some time wasting time!

One of my original methods in the DAL is called Create Parameters. This simply takes an object as a parameter, iterates the properties and creates a SqlParameter and adds it to a List<T>.

public static List<SqlParameter> CreateParameters(object input)
{
   List<SqlParameter> parameters = new List<SqlParameter>();

   foreach (PropertyInfo p in input.GetType().GetProperties())
      parameters.Add(new SqlParameter(p.Name, p.GetValue(input, null)));

   return parameters;

Pretty basic.  With new C# syntax this can be reduced to one line a la:

public static IEnumerable<SqlParameter> CreateParameters(object input)
{
   return input.GetType().GetProperties().Select(p => new SqlParameter(p.Name, p.GetValue(input, null)));

You will noticed that I changed to IEnumerable<SqlParameter>. It can just as easily be converted to List<SqlParameter> by using the ToList extension method.

public static List<SqlParameter> CreateParameters(object input)
{
   return input.GetType().GetProperties().Select(p => new SqlParameter(p.Name, p.GetValue(input, null))).ToList();
}

It should also be worth mentioning that I prefer the above method's syntax over that of the traditional LINQ syntax:

public static List<SqlParameter> CreateParameters(object input)
{
   
return (from p in input.GetType().GetProperties() select new SqlParameter(p.Name, p.GetValue(input, null))).ToList();
}
 

I probably won't have much use for these methods after implementing LINQ-to-SQL as it provides a pretty braindead process to CRUD data. 

Tags: C#, LINQ

Add & Remove GetTable Extension Methods Gone in 2008?

Moving from Beta 2 to the full version of Visual Studio 2008 recently coupled with Charlie Calvert's presentation last night motivated me to revisit some sandbox 3.5 projects.

One of the problems I first saw with LINQ was using multiple databases in one project.  This would mean that for each Database you wanted to use, you would have to generate LINQ to SQL classes for each and instantiate the appropriate DataContext class when wanting to CRUD, etc..  Well, you will still have to generate the LINQ to SQL classes but can now have access to the data from one Generic class (DataAccess<TDatabase> where TDatabase has contraints of DataContext and new()).  My first sandbox project with 2008 was a very incomplete DataAccess class library (hey it was the first week Beta 2 was released!). Here are two methods in question from that codebase which are slightly altered.

public static void Insert<T>(T instance) where T : class
{
   using (TDatabase database = new TDatabase())
   {
      database.GetTable<T>().Add(instance);
      database.SubmitChanges();
   }
}

public static void Delete<T>(Expression<Func<T, bool>> predicate)
where T : class
{
   using (TDatabase database = new TDatabase())
   {
      T t = (T)database.GetTable<T>().Where<T>(predicate).Single();
      database.GetTable<T>().Remove(t);
      database.SubmitChanges();
   }
}

IRL these are bools but I just pasted this for concept.  Anyway, when I compile this now I get compile-time errors tellng me that System.Data.Ling.Table<T> does not contain a definition for Add or Remove. I thought that these were native extension methods? Apparently things started being done differently between Beta 2 and the release. Unfortunately I wasn't able to keep up with all of this as I, like most, still had 2.0 work to do!

I do know that from CTP to Beta 2 Generic methods like above needed to have parameter-type constraints in order to compile.  I clearly have that!  At this point I am open to suggestions.  If I find anything I'll be sure to update this.

UPDATE
That didn't take long! Apparently the RTM bits consisted of refactoring (amongst other things) Add and Remove to InsertOnSubmit and DeleteOnSubmit respectively.  Also, apparently Add and Remove will still compile in VS 2008 Visual Web Developer Express. Interesting...

NEWLY-MODIFIED CODE

public static void Insert<T>(T instance) where T : class
{
   using (TDatabase database = new TDatabase())
   {
      database.GetTable<T>().InsertOnSubmit(instance);
      database.SubmitChanges();
   }
}

public static void Delete<T>(Expression<Func<T, bool>> predicate)
where T : class
{
   using (TDatabase database = new TDatabase())
   {
      T t = (T)database.GetTable<T>().Where<T>(predicate).Single();
      database.GetTable<T>().DeleteOnSubmit(t);
      database.SubmitChanges();
   }
}

Tags: C#, LINQ

LINQ to SQL Debug Visualizer

At the Scott Guthrie presentation last week he showed the SQL query that the ORM will execute at run-time with a tool called LINQ to SQL Debug Visualizer.  The tool isn't included in Visual Studio 2008 due to time-constraints but the source code and binary are available on Scott's site. Here is the link.

I should've installed this sooner but I've been busy lately.

Tags: LINQ, Orcas, Tools

Update Method for LINQ

Again, quick little piece of code I have been working on for updating data using LINQ.  This method needs to be cleaned up so as to not allow setting of the Primary Key value (or DbGenerated field).  That will come at a later time. This is simply shooting from the hip.

public static void Update<T>(Expression<Func<T, bool>> predicate, T entity)
where T : class
{
   if (!EntityExists<T>(predicate))
      using (TDatabase database = new TDatabase())
      {
         T t = (T) database.GetTable<T>().Where<T>(predicate).Single();

         foreach (PropertyInfo p in t.GetType().GetProperties())
            p.SetValue(t, p.GetValue(entity, null), null);

         database.SubmitChanges();
      }

Read this entry for reference.

Tags: C#, Code, LINQ

More Generic LinqHelper Class

I've implemented a LinqHelper class as kind of a way to test out more Generic Data Access with Linq to Sql.  It's been working out quite well but there is at least 1 major gotcha... multiple databases (DataContext).

After Andy recommended reading Rick Strahl's blog post "Linq to Sql and Dynamic Queries" I figured I should repost the code that I wrote for my LinqHelper.  I just posted a couple of methods so you could see where I was going with this.  Stay tuned to Rick's post.  I'm wondering how this is going to play out.

The thought of creating a class for each database is unacceptable so the following code should do the trick (for now!).

public abstract class LinqHelper<TDatabase>
where TDatabase : DataContext, new()
{
   public static List<T> ReturnAllRows<T>() where T: class
   
{
      return new TDatabase().GetTable<T>().ToList<T>();
   }

   public static bool EntityExists<T>(Expression<Func<T, bool>> predicate)
   where T : class
   
{
      return new TDatabase().GetTable<T>().Where<T>(predicate).Count() > 0;
   }

   public static List<T> Filter<T>(Expression<Func<T, bool>> predicate)
   where
T: class
   
{
      return new TDatabase().GetTable<T>().Where(predicate).ToList<T>();
   }

   public static void Insert<T>(T entity) where T : class
   
{
      using (TDatabase database = new TDatabase())
      {
         database.GetTable<T>().Add(entity);
         database.SubmitChanges();
      }
   }

   public static void Delete<T>(Expression<Func<T, bool>> predicate)
   where T : class
   {
      if (!EntityExists<T>(predicate))
         using (TDatabase database = new TDatabase())
         {
            T t = (T) database.GetTable<T>().Where<T>(predicate).Single();
            database.GetTable<T>().Remove(t);
            database.SubmitChanges();
         }
   }
}

Tags: C#, Code, LINQ

LINQ, Lambda, and Generics: Insert and Delete

I think I'm addicted.  Aside from my first LINQ application ("Hello AccountTypeId") I haven't written one SQL-like query.  From that point on everything has been done with Generics, Lambda expressions (Expression<> trees), and starting today: Reflection.  In the past I tried very hard not to have any SQL or SQL-like syntax in code so why should I start now?  Maybe I'll come around.  Personally I think using Generics coupled with Expression<> trees and Reflection makes for much more elegant code but that is just me. :-)

I, like others I'm sure; am just waiting to see how LINQ is going to play into this whole Presentation / Business / DataAccess layer scheme.  I can't find it right now but I do have a great link (2) to post discussing developer speculations on what is to become of a 3-tier architecture.

Anyway, some more code that I've been working on.

public static void Insert<T>(T entity) where T : class
{
   using (OrcasDB database = new OrcasDB())
   {
      database.GetTable<T>().Add(entity);
      database.SubmitChanges();
   }
}

public static void Delete<T>(Expression<Func<T, bool>> predicate)
where T : class
{
   using (OrcasDB database = new OrcasDB())
   {

      T instance = (T) database.GetTable<T>().Where<T>(predicate).Single();
      database.GetTable<T>().Remove(instance);
      database.SubmitChanges();
   }
}

How to Use
// insert
Employee will = new Employee
{
   Username = "will.asrari",
   EmailAddress = "me@willasrari.com",
   CanCode = true
};

LinqHelper.Insert<Employee>(will); 

// delete
LinqHelper.Delete(emp => emp.EmployeeId.Equals(3));

Easy.  My Delete<T> method could probably be made more elegant but this code does work.

UPDATE
I found the links that I wanted to post.  Pretty lengthy, but some interesting reading.

Creating a DAL with Linq to Sql Part 1
Creating a DAL with Linq to Sql Part 2

Tags: LINQ, Orcas

LINQ and Lack of SQL Primary Keys

Word to the wise.  Not having a primary key in your SQL table will result in a readonly O/R  mapping. You won't be able to CRUD anything!  I normally always have Primary / Foreign keys set but I created a new database (Orcas) specifically for sandboxing.

Lesson learned. Thanks Ben Hall.

System.InvalidOperationException: Can't perform Create, Update or Delete operations on 'Table(Employee)' because it is read-only.

Tags: LINQ
<< Newer Entries Older Entries >>