EntityFramework

EntityFramework 4.0 and SQL 2000

Leave a comment

If you are like me, some customer just can’t upgrade from SQL 2000, so how do you use the EntityFramework 4.0+ with SQL Server 2000?

Here is one solution that has worked for me:

Here are the steps to add a new Entity Framework 4.0+ entity context to a Visual Studio 2010 project:

  • Download the base entity data model for SQL Server 2000 file and save it to the project folder to which you wish to add the SQL 2000 entity context. Do NOT add it to your project yet.
  • Rename the EDMX file to the name of the data context.
  • Open the EDMX file in a text editor.
  • Find/replace all references to %DB_NAME% with your own value.
  • Add this new connection string section to your App or Web.config and find/replace with your own values:
<connectionStrings>
  <add name="%DB_NAME%Entities" connectionString="metadata=res://*/%DB_NAME%.csdl|res://*/%DB_NAME%.ssdl|res://*/%DB_NAME%.msl;provider=System.Data.SqlClient;provider connection string="Data Source=%DB_HOST_NAME%;Initial Catalog=%DB_NAME%;Persist Security Info=True;User ID=%DB_USER%;Password=%DB_USER_PWD%;MultipleActiveResultSets=False"" providerName="System.Data.EntityClient" />
</connectionStrings>

Please note: the connection string name MUST MATCH the EntityContainer section name you specified in the EDMX file.

  • Find/replace all references to %DB_NAME% in the App or Web.config file with your own value as above.
  • After the .edmx and web.config files configured, add them to the project and verify the new entity data source appears after refreshing the Data Source manager window.
  • If you can’t double click on your *.edmx file you’ll need to right click on it and select “Open with”, and choose “ADO.NET Entity Data Model Designer”
  • If the new entity data source appears, open the .edmx file in design view and refresh the design model.

Now you have your Entity XML file next you can generate your Code by using the T4 templete code generation.  To do this just right click in the EDMX model and select “Add Code Generation Item” this will give you the installed Generators (you can get more from NUGET, like dbcontext”

I’ve found this very useful and gives you a head start on old databases.