LINQ to SQL with dynamically generated Stored Prcedure
Posted by Paul McConnon
I’ve been using LINQ to SQL which is a very nice feature for .NET. It allows you to very quickly model your database objects as objects in code and return them as strongly typed .NET objects.
Seems to have ‘nicked’ (been inspired by?) quite a few ideas from ActiveRecord for Ruby (and Rails). In fact a lot of the new language features (e.g. predicates as anonymous functions for selection, and iterators) seem to borrow from things learnt by being exposed to dynamic languages. All in all it makes .NET much more fun to work in.
Anyway, I was using all this goodness in a project (VS.NET 2008 Orcas beta) and had all my Entities modelled and could use LINQ to query and transform my entities programmatically. I also had a few Stored procedures mapped as methods of the data context. I ran into a problem when I wanted to generate some SQL on the fly so that users could get adhoc filters of their calls.
I didn’t want to drop out into old style ADO.NET using my DBHandler as i would lose the strong typing etc. I decided to use a QueryReport table (as I’ve done numerous times before) to allow me to do ad-hoc queries without being open to SQL Injection. The table consisted of an ID, Name and SQL field. My Stored procedure could take an ID parameter, look up the SQL and use it to filter the calls table
e.g.ID Name SQL 1 All calls Select * from Calls Where 1 = 1 2 Open calls Select * from Calls Where Status = 1 3 Old calls Select * from Calls Where TimeStamp < DateAdd(d, -14,GetDate())My stored procedure looked like this
ALTER PROCEDURE [dbo].[spExecStoredQuery]
@QueryReportID int = 0
AS
BEGIN
SET NOCOUNT ON;
declare @queryText varchar(500)
select
@QueryText = SQL
from
QueryReports
where
id = @QueryReportID
exec (@querytext)
END
The sp always returned All fields from the Calls table, so I should be able to map it to a Call object in the LINQ designer. I created the stored procedure and went about adding it as a method to the LINQ designer.
My problem occurred when I went to set it’s return value to be of type Call. It wouldn’t let me. The option was disabled and greyed out. I immediately assumed that it was because I had an Exec statement in the stored procedure, and assumed it was either a security issue or the designer could not work out it’s return type. I then removed all the exec code from sp and had it return a straight select * from calls.
This still didn’t work. I then renamed the Stored procedure to spStoredQuery. I added it to the designer and I was then able to set its Return Type to Call. This lead me to believe that the designer didn’t like the ‘exec’ in the name of the Stored Procedure. In order to test this I renamed it back to spExecStoredQuery, but, it still worked okay.
I’ve since played around with different versions and names and have discovered the following
- The first time you add the sp as a method to the designer it checks for return values to try and guess the output type.
- If it cannot guess the type, it will not let you set a return type in the designer at all
- If you delete the method then fix the stored procedure so that it returns a constant return type, the designer caches the previous type and will still not let you set a return type.
- If you rename the stored procedure and re-add it, it checks again, sees a return type (doesn’t matter what it is) This allows you to manually select a return type in the designer.
- If you then remove the non-dynamic select, and make the stored procedure dynamic, the method still works, and coerces the return type to be of type you selected.
So, the outcome of all this is: If you are dynamically generating a query in a stored procedure and want to add it as a method with a return type in the LINQ designer, add it first with non-dynamic select (select * from foo), override it’s return type if required, then add the dynamic SQL to stored procedure and you’re laughing.
Photos
Is there any way out for above scenario.Because though we add anything in designer.cs of Linq file, the next time when we drag & drop anything on to it,the custom code get removed.Can we change this file dynamically?
Jayshree, once you have used the designer to auto-generate the code for the stored procedure, you can move the code out of the .designer.vb or .designer.cs code into your own partial class. In Visual Studio, go to Show All Files, expand the .dbml file, you should see a .layout and a code file. Open the code file and find the code that declares your data context (Partial Public Class FooDataContext) Copy the whole datacontext code into a new code file. In the new code file, remove everything except the stored procedure call declarations, and their return types (ie you don't need Public sub New etc as this is a partial class). This means that when your designer is regenerated, your declaration of your stored procedure remains intact! P
Easy way: You can open the .dbml file with the XML editor and edit the return type there - that is the file from which the .designer.cs code is auto generated from. The .designer.cs file will be automagically generated correctly this way. Hard way: Do not use drag-drop to add your SP to the DBML, instead go to codebehind and add the call manually as a new partial method.
I've tried to do this, but I'm getting "Specified cast is not valid." exceptions when I try to call the stored procedure. This solution fit my issue perfectly until I got those casting exceptions. Any ideas?
Even though I got a work around to this problem, it is very frustrating to create dummy sp and then replace it with actual sp. In the development environment, we regenerate the dbml for stored procedure very often and every time fiddling with this issue is very annoying.
auto insurance quotes
Slick Productions-Slick Productionsl
The True Meaning of Pictures There are many more directors who make great movies and look cool doing it I didn't mention for the sake of brevity. It's not glamorous, but it gets the job done. A strong grasp of the entire procedure it takes to make a movie is significant. There are different departments that work together to bring a film together. The camera department, sound department, make-upwardrobe section etc.. Members are invited to share their personal tale on taking photographs, techniques and review. You don't have to be a professional photographer nor with press forward equipments; this is an inspiring share on the skill and gains. Article can be in form of tale, journal, photography technique & skill, equipment & tools characteristic and review. Photos communicate. Good nature photos speak well! Photographic work refers to the arrangement of visual elements in a photo. As a photographer, you use lines, shapes, colors, tones, patterns, textures, balance, symmetry, depth, perspective, scale, and lighting to bring your imagery to life. But to think the interplay of all of these visual elements in every photo is scary. When the photographer's message is garbled, ambiguous, weak or obscured by distracting visual elements in the composition, the photo is not a keeper. Nature photos that convey a influential message compel the viewer to take a second look in order to soak in the beauty and meaning of the image. These look like a set of rules, but at the outset I'd like to place these rules in context. Photography is an skill, with some craft. For me the rules set a framework of what tends to work and what doesn't.They are not there to be blindly followed; in fact breaking them can make a picture. However over the years I have found that appreciative the rules, helps get a better picture. Understanding file formats and knowing the correct one to use will save you a great arrangement of time in the future. Some file formats should only be used for web images, where others are ideal to get the wonderful print. One of the chief advantages of digital photography is that you can see the picture straight after you press the shutter release. This allows you to reshoot if essential or delete pictures as you go to save room on the memory card. Others require you to press the monitor display to see the image, or to switch to playback mode to analysis images. Many of the techniques used in traditional photography also affect to digital photography also apply to digital photography. Three elements that are different in digital photography are white balance, picture resolution, and digital effects. But copying someone else’s style is not what fine art photography is about. It’s about creating your own style, having your own voice. Also in the majority cases, that understanding of self evolves over your entire lifetime and transitions with you. Look up any artist and view their work throughout their lives and you will begin to understand that procedure.Nuvaring Attorney A NuvaRing attorney represents individuals and families harmed by this controversial vaginal-ring contraceptive.
I am happy to find this useful information here in the post. Thanks
Sensation! Thanks for making me aware about this most recent expertise. For more expertise I will be in touch.
Nice job, it’s a great post. The info is good to know!
Central America. File:Central America (orthographic projection).svg | Map of Central America ! style"border-top: solid 1px ccd2d9; padding:
If the organization that is serving the content has control over the network between server and recipients
Dentists in New York, NY, See Reviews and Book Online Instantly. All appointment times are guaranteed by our dentists and doctors.
Well this is very interesting indeed.Would love to read a little more of this. Great post. Thanks for the heads-up...This blog was very informative and knowledgeable.
If you are dynamically generating a query in a stored procedure and want to add it as a method with a return type in the LINQ designer, add it first with non-dynamic select (select * from foo), override it’s return type if required, then add the dynamic SQL to stored procedure and you’re laughing.
Nothing more annoying than someone who prefixes every stored procedure with sp
Wonderful illustrated information. I thank you about that. No doubt it will be very useful for my future projects. Would like to see some other posts on the same subject!
A standard television set comprises multiple internal electronic circuits including those for receiving and decoding broadcast signals.
Foreigners cannot buy land or homes within 50 km of the coast or 100 km from a border unless they hold title in a Mexican Corporation.
Thanks Paul McConnon your article is very helpful i book mark this. If Any blogger want, Learn image marquee on web page by HTML code at web-page-tip.blogspot.com
Thanks Paul McConnon your article is very helpful i book mark this. If Any blogger want, Learn image marquee on web page by HTML code at web-page-tip.blogspot.com
IT services California V&C Solutions, Inc is an IT Support Services provider specializing in IT consulting. Skilled IT consultants will resolve your existing IT-related issues, plus they are quilified enough to assess and reconfigure your systems in order to prevent any future problems.
This is a really good read for me, Must admit that you are one of the best bloggers I ever saw.Thanks for posting this informative article.
Wow thanks for that definitely useful article. I was searching for a solution like that for an ages. SQL is a dark forest for me. I have tried to learn something about it but unsuccessfully. LINQ is absolutely a fresh thing for me too. Thanks for publishing such a useful article here and I will be definitely waiting for more nice ones from you in the nearest future too.
No doubt it will be very useful for my future projects. Would like to see some other posts on the same subject!
I was really ignorant,I am not too clear on this matter.Thank you for sharing the information. christian louboutin shoes ebay
Official balenciaga handbags Online Shop - offering you balenciaga inspired fashion and lifestyle for women balenciaga handbags
Wonderful illustrated information. I thank you about that. No doubt it will be very useful for my future projects. Would like to see some other posts on the same subject! grow taller
very good point, and thank you for sharing! Your forum is so different from us, very nice http://www.guccicn.net Gucci handbags
I am so glad that I have found this your post because I have just been searching for some information on how to use LINQ to SQL with dynamically generated stored procedure. The information which you have shared for us is really useful and I have known so many new things about it which I have not known before. Well, I will definitely bookmark your website and wait for other such great posts from you in the future. Best wishes.
Fascinating, really.
Amazing Site.
I'm loving this website, grat info.
Excellent I agree.
Very good.
Wonderful information. I thank so.
It is very good ,thank you for sharing the article,waits for more articles.
In early 2010, David set up a step-by-step social media business development plan for other risk management advisors at Praxiom.
First of all thanks a lot for the useful and informative article. I have to do a little research in my university about the LINQ to SQL with dynamically generated Stored Prcedure so I am so glad that I have found this your post. The information which you have shared for us is really good explained and I will definitely will use it in my work. Thanks a lot one more time for the useful and detailed post and I will be waiting for more such great articles like this one from you.
This blog is very rich, content, will often come around. correctalittle.com
This blog is very rich, content, will often come around. correctalittle.com
This blog is very rich, content, will often come around. correctalittle.com
Very interesting and informative post indeed. I have to admit that I always follow your blog because it is full of various and useful information about everything. Well, it was quite interesting to read this your article about the generated stored procedures . Actually, I am very interested in this sphere and reading this post I have known many new things, which I have not known before. Thanks for publishing this great article here, without you I would never known about such a thing ever.
I have to admit that I have been searching for this information for a long time. Reading this wonderful entry I have known many new things on how to generate LINQ to SQL, which I have not known before. As I see all your entries are detailed and full of valuable information so I will definitely bookmark your website and wait for more such great posts like this one. So huge thanks for publishing this article here! Best Regards, Greg Matton.
Thanks for such an interesting and useful article here. SQL is one of the hardest topics for me in web programming so it is always nice to read such an articles like that. I have found many new information here and I will definitely read this one few more times and show it to my friends. Thanks one more time and keep publishing them in the nearest future too.
I've tried to do this, but I'm getting "Specified cast is not valid." exceptions when I try to call the stored procedure. This solution fit my issue perfectly until I got those casting exceptions. Any ideas?
Well , the view of the passage is totally correct ,your details is really reasonable and you guy give us valuable informative post, I totally agree the standpoint of upstairs. I often surfing on this forum when I m free and I find there are so much good information we can learn in this forum! the-various.com
Well , the view of the passage is totally correct ,your details is really reasonable and you guy give us valuable informative post, I totally agree the standpoint of upstairs . http://www.precision-mechanical.net I often surfing on this forum when I m free and I find there are so much good information we can learn in this forum!
car hire kerry
host guide
web hosting guide
web hosting news
casino online
car hire france
car hire ireland
car hire italy
car hire portugal
Ephedra: Fast weight Loss with Ephedra diet pills, smacker ephedra hoodia diet supplements, fat burner, ephedrine diet
Nursing bras and maternity bras from leading nursing bra designers. Also maternity pyjamas, maternity nightwear, maternity sleepwear and breastfeeding bras.
Beverly Hills Plastic Surgeon: Improve your looks with Beverly Hills Plastic Surgeon. For Plastic Surgery Los Angeles & expert Beverly Hills Plastic Surgeon, visit Niptucksurgeon.com.
replace green card