How To Use SQL Server 2008 R2 with Scaffolding

Mar 1, 2011 at 5:22 AM

Truly, this is amazing work. However, that was what you did for us last week. This week we want to use a full blown version of SQL Server. Can you advise if this is possible and if so how to do it?

Mar 29, 2011 at 2:32 PM
Edited Mar 29, 2011 at 2:43 PM

Did you get anywhere with this ?

I've changed my web.config connStr to my existing DB, but get "CREATE DATABASE permission denied in database 'master'.", even with SA permissions.

On the basis that my crude understanding is that continued use of scaffolding will make DB amends, some insight into correct SQL permissions would be superb, as after a couple of days handwriting classes and relationships, stumbling on scaffolding seems great, but am finding it a little hard to see what I will lose from doing everything Steve taught us in Prof ASP.NET MVC 2 Framework....



UPDATE : Okay, having stated on Scaffolding a little while ago (today) and reading from Steves blog and trying out with a clean start on project, I've just decided to take the time out to watch the video (always hard to stop working and sit and watch), but just noticed to update the class, the database gets DROPPED !!!!! As Steve says, this is 'pretty catastrophic". Whilst I understand why, and now get the idea of perhaps I should be running SQL locally for development, can anyone give any guidance on how they have worked with this on a live project against a db with live data in ?


Mar 30, 2011 at 4:35 PM

I'm not sure this issue is a scaffolding issue, but rather a EF/Database ALM issue.  I am also seeking to define that process

Mar 30, 2011 at 4:53 PM

Yeah, I have spent quite a few hours on this since, and watched Steves video back a number of times and working through the code against my own models. It feels very good, but for people used to doing things one way (DB First etc) then the move to Scaffolding AND CodeFirst at the same time, some of us miss the concepts that go with EF. 

As an example, whilst I knew of EF, I skipped delving much deeper into it when I started with MVC (too many learning curves at one point)  AND Steves book (great btw) also put it to one side.  As such I am now playing catch up with quite a few concepts on a sparsely documented concept, but what I can see so far is great.

I am having difficulty seeing on how I develop with scaffolding on a production scale. My current project is coming together quite well and I can see the massive speed improvements, but it just scares the hell and confuses me when it comes to me having hundreds of carefully crafted records in the DB and then VS suggests I drop the tables and recreate !

I am also looking into the concept of putting prefilled fake repositories into the build process (can't remember where i read about that now) where during the very early stages you can force sample data in each time the dreaded DropCreateDatabaseIfModelChanges is called, but I still feel like a noob when it comes to understanding how to wire the POCO CF data can be protected once written when you still need to develop the DB.

I am sure we will all get there in the end, and I am loving MVC compared to my bastardised WebForms applications, it just needs a nice guide to the pros and cons of it all.

Will be spending a few hours in front of the TV and some Channel9 videos tommorow I think !


Mar 30, 2011 at 8:15 PM

I see MVCScaffolding as a way to spin up applications very quickly, but then I can see not using after that. The reason is that are always customizations that I want to make to a page after it's been created. I have an application with 115 separate pages. To have a way to generate those in a consistent manner would be beneficial from the start, but then I could see not scaffolding anything after that.

I think it's OK to drop and recreate my Dev DB every time. There are documented ways of providing seed data for your dev db.  For Deployments use SQL Server deployments to generate the scrips via the Difference between production and the Dev DB.  Then manually or automatically apply them to the production DB. IMO I would never trust auto generated sql code to run against a production db since it can't know about what's in it.




Mar 30, 2011 at 9:32 PM

Yeah, after about 30 hours since first finding out about Scaffolding, there is a picture slowly coming into my mind as to how it will be used. Like you say, all my projects start with about 80% common code and then go off from there to their own thing, and looking at the code it is creating, I would be happy living with that ongoing. Reading more and more on the subject, I think most of my hangups are about EFCodeFirst, and coming to terms with that, but again as you say, it is probably coming up with a deployment and db strategy that secures the data that would settle most of my concerns there.

I am also very concious that this is all beta, but has a couple of geniuses behind it, and watching the video, can already see that the power lies in the extensibility (having already made some changes to the T4's and wanting to build my own scaffold), but I guess I just have to weigh up how much time to do I really spend setting up a project, and how much customising it. I guess the latter is the important one, and should let that be the lead to determine how I do the former.


PS : If you have any links to further reading you think may be of use about these issues, I would be very appreciative... 

Apr 2, 2011 at 8:49 PM
Edited Apr 2, 2011 at 8:51 PM

First, I'd apologize for not responding sooner but this last week was crunch time (including a 68 hr stint) and so let me start by saying that shortly after starting this post we resolved our issue and since then we have rescaffolded several dozen times. To date we are having no issues with either SqlExpress (local) or SQL Server 2008 on our internal network and the issue that promted this question turned out to be permissions on the server. On occasion we find EF stumbles and is unable to drop and replace the db but in those cases we simply drop the database manually and re-run. Beyond that this is working flawlessly.

We have since moved the application to a hosted site where we continue to test. Strangely, even though we have deselected the "Include all databases configured in Package/Publish SQL tab" checkbox we continue to see a SQL Error raised when we first attempt to browse the site indicating that the attempt to drop and recreate the Database failed because there are open connections to the database. Once we refresh the page, everything proceeds as per normal.

Still, this is odd behavior because although we have  DbDatabase.SetInitializer(new DropCreateDatabaseIfModelChanges<ProjectNameContext>()); active in our Context, our experience on our local server has been consistent, insofar as no attempts to modify the DB are made when no changes are made to the model. However, even when no changes are made to the model, we are seing this page generated immediately after we publish to the hosted site.  I suspect it is a plumbing issue (as Scott H. says - the magic on the other side of the wall) and will be resolved as we move forward and begin updating the hosted DB through the process.

As far as comments about production scale, I think that the line between development and production is continuing to blur. Being able to test a sytem/application/architecture in the final production environment offers a number of benfits. Not to extend the argument about what is prodution - what is development; however, clearly the one-click publishing method of deployment, combined with EF and Scaffolding, now affords an opportunity to take the iterative development process to a new level. Ultimately, moving a project to production could be reduced to simply changing from debug to release. So far everything we've done is leading us to that conclusion.

I'm not sure that this resolves anything that others are working on but I can say the process is working locally and on our intranet and I suspect we'll soon add internet to the list.