Nathan Yorke

LINQ using stored procedures without implicitly typed variables

Yorkenw06 — Wed, 30 Jan 2008 21:38:00 GMT

Problem:

You want (need to because of the stubborn dba!) to use stored procedures in your next project and would also like to take advantage of the new features in .Net 3.5 like LINQ and extension methods, implicitly typed variables etc. I particularly don't like to use the implicitly typed variables (the new var keyword). I like strong typed variables better since it makes the code easier to read. Don't let the name fool you Implicitly Typed Variables in C# 3.5 are still strongly typed because you can't change the type of the variable once it has been initialized.

LINQ makes it super easy to work with stored procedures with dragging and dropping the procedure into the data context designer (I am not going to get into the details here, so if you do not know click here). It will automatically generate a method for you with parameters and everything. The issue is if you are joining two or more tables together inside your procedure LINQ does not understand this and it therefore will not return a hierarchal data object (like ad hoc LINQ calls do). It is not even an object that consists of the types that are inside your data context representing your tables. Instead it returns an object of type ISingeResult. This is where the var keyword comes in as you normally would just use this and move on. I find this difficult (the ISingeResult) when I am creating my data access layer as I would like to know what type of object to expect to get back before I invoke the method.

Solution:

When calling LINQ stored procedures from your data context object you can build and return an object whose type already exists in the data context, like Topic, Reply, Subject or User in the above. This way you can return familiar types that have meaning and are easy to read.

   1:          public Topic Get(int TopicId)

   2:          {

   3:              Topic Topic =

   4:              (

   5:                  from T in _DB.GetTopicById(TopicId)

   6:                  select new Topic

   7:                  {

   8:                      TopicId = TopicId,

   9:                      TeamId = T.TeamId,

  10:                      UserId = T.UserId,

  11:                      User = new User

  12:                      {

  13:                          UserId = T.UserId,

  14:                          UserName = T.UserName

  15:                      },

  16:                      SubjectId = T.SubjectId,

  17:                      Subject = new Subject

  18:                      {

  19:                          SubjectId = T.SubjectId,

  20:                          Subject1 = T.Subject

  21:                      },

  22:                      Description = T.Description,

  23:                      Topic1 = T.Topic,

  24:                      Folder = T.Folder,

  25:                      Link = T.Link,

  26:                      GameDateTime = T.GameDateTime,

  27:                      CreatedDateTime = T.CreatedDateTime,

  28:                      PromotedDateTime = T.PromotedDateTime,

  29:                      Total = 1,

  30:                      TotalReplies = T.TotalReplies.Value

  31:                  }

  32:              ).FirstOrDefault();

  33:              if (Topic != null)

  34:              {

  35:                  Topic.Replies.AddRange(GetReplies(Topic.TopicId));

  36:              }

  37:              return Topic;

  38:          }

This method is returning an single Topic object. It is also joining in a User object and Subject object both of which are child tables to Topic in the database. The results from the stored procedure is one row of data from multiple tables, but the code is building a hierarchal object. The "select" statement in the above code (lines 6-31) is rebuilding the types of objects that currently exists in our data context instead of returning an object of type ISingleResult. This provides more of a user friendly approach to our data layer. The last part of this method (line 35) is getting all of the Replies for a Topic. This is a separate step, but this way we can return one hierarchal Topic object to the caller with out the need to enumerate via a foreach statement over the data.

   1:          private List<Reply> GetReplies(int TopicId)

   2:          {

   3:              return

   4:              (

   5:                  from R in _DB.GetRepliesByTopicId(TopicId)

   6:                  select new Reply

   7:                  {

   8:                      ReplyId = R.ReplyId,

   9:                      UserId = R.UserId,

  10:                      User = new User

  11:                      {

  12:                          UserId = R.UserId,

  13:                          UserName = R.UserName

  14:                      },

  15:                      ParentReplyId = R.ParentReplyId,

  16:                      Reply1 = R.Reply,

  17:                      CreatedDateTime = R.CreatedDateTime,

  18:                      TotalUps = R.TotalUps

  19:                  }

  20:              ).ToList<Reply>();

  21:          }

This method does the same thing as the example above. It is running a query that selects 1 to n Reply records from the database. It then casts the results in to a generic list of type Reply (this class exists in our data context) instead of a ISingleResult which gets added to the topic object above in line 35.

This exposes some of the power behind .Net 3.5 and LINQ, although, most people will not have a need for this solution, but I worked nicely for me.

InfoPath form deployed to Sharepoint that reads from config files

Yorkenw06 — Thu, 20 Dec 2007 00:25:00 GMT

Problem:

If you do not use InfoPath and or Sharepoint you can skip this post. When developing InfoPath forms in Visual Studio 2005 that are deployed on Sharepoint you can add an app.config file and one would think that you could use it as in any other project, heck you even get access to the System.Configuration namespace. Everything even compiles gracefully, however, when you run your InfoPath form you will quickly realize that it does not work. This is because all the InfoPath template info is built into a single .xsn template file that will be downloaded to a users local machine when they open an InfoPath form from Sharepoint. No other file, like an app.config, will be downloaded. Everything from here is ran off the client machine (remember a user needs InfoPath installed in order to run non web compliant forms).

Solution:

Instead of hard coding different values for development and production then forgetting to switch the values depending on the deployed environment a solution is to create a custom config file. We can read a custom xml file (or any file of you choice for that matter) that exists back on the Sharepoint server where your InfoPath template was downloaded from. In .Net we can simply make a Web Request back to the server and set our configs settings programmatically in the code.

Tip: If you open IE and go to File --> Open (check "Open as web folder") then paste the root path of the document library where you InfoPath template is deployed we can see all the files from the database. This is where you will want to store your custom config file.

In the FormEvents_Loading method you will first want to call your method to set the configuration settings. The first "if" statement determines if the form was downloaded and ran from you Sharepoint server or is from you local machine when developing. If true, it will have the extra string above in the uri. We will then have to slice the uri a bit and make a web request to pull in the contents of the config file into an Xml document object. If the template is not from Sharepoint (this is here for development since the template is then being executed locally) will need to read the file from our local disk and not the web. Then it is possible to just XPath our configuration settings and set the proper values for our variables. You most likey will have to modify the code above a lot to fit you needs as it is specific to my situation, but it should be good enough to get your juices flowing.

Uploading multiple files logically via the web

Yorkenw06 — Thu, 06 Dec 2007 00:32:00 GMT

Trying to upload multiple files easily on the web is not a very intuitive thing. I am not sure why you can only select one file at a time though a <input id="fileToUplad" type="file" /> tag even though you can add multiple files to a request. Hopeful somebody has an answer for me, but it is what it is. I know that you can dynamically add file input boxes though the DOM, but this is still tedious because you still have to select each file one at a time.

If you have ever used Sharepoint you might have noticed (if you were using IE) that there is an option to upload multiple files and it add a folder explorer view in your page! At the time I was working on a project where the users wanted to use this type of file picker to upload numerous photos at one time. Upon further investigation I discovered that it was the STSUPLD.DLL that came with Office 2003. Below is some code that will allow you to use this control in an Asp.Net page and have the collection of files on the server side, so you can programmatically decide what to do with them. This solution will work for users with IE and office 2003 or above.

*Notice the object tag:

The only thing is when using the multiple file upload control it causes the page to load again and not a postback. However it can be tricked by adding a value to the query string and then checking the value in the code. To see it in action you can download the attached code.