Wednesday, 7 August 2013

Data Import from MSAccess To Sql Server

Data Import from MSAccess To Sql Server
1. Basic Knowledge of Databases
2. Knowldge .Net Basics with C#

Introduction: If Data migration or Data transfer from MsAccess To SqlServer is required ,You can fallow below steps

Separating Model from  ADO.NET Entity Data Model

Separating Model from  ADO.NET Entity Data Model

1..NET4 and vs2010/above is installed on system.
Code Download: Click Here to download entire solution

I wrote a post a last weeks about Understanding MVC Pattern . If you have read that post first (it might be a good idea) you can click here to read it now.Since it was posted, I have noticed that a few people visit the post every day and they might be waiting for next post.At, the end of my article i have clearily mentioned that i will be cleaning this application in future posts . So, as  promised, I am going to fallow some steps to achieve this. So,this post will be  useful as well as helpful i hope.

Create the Solution in Visual Studio2010

I will modify existing solution or you can create new one in a same way for Article Gallary Application which is developed to keep track of  members/authors and their respective articles . if you have not gone throught it just click to see here Article Gallary Application . So to begin with, let’s create a modify existing solution and then add projects to it that will make the different layers of our application. Using Visual Studio,add in the following projects like shown below

Here you see list of Projects(layers) to the solution:

FaithModel– This layer will contain the POCO objects
Data Access Layer – This layer will interact with the database and will contain our Entity Framework file FaithDataLayer in my case
Business Access Layer – This layer calls the data layer and implements business rules(FaithBusinessLayer) in my case
Service Layer – This layer contains the actual WCF or Other service that can be accessed by external websites  (FaithServiceLayer) in my case
Web Layer – This is a MVC website that will be accessed from browser (Faith.MVC.Sample) in my case
For Solution layers, you can name your projects whatever you want as long as you know what each layer is for. Model, Datalayer, BusinessLayer .... are just naming conventions that I have choosen.
This is what it looks like in my solution explorer:

Entity Framework Model and POCO’s

Now right click on the FaithDataLayer project in your solution and select Add > New Item … When the dialog box appears, click on " FaithDataLayer" in the "Installed Templates" section like below

Once that has been done, click on the "ADO.NET Entity Data Model" name the file as per your naming convention and then save it by clicking "Add".
At this point, the "Entity Data Model Wizard" appears. Fallow below steps
  1.  Select "Generate From Database" on the "Choose Model Contents" page.
  2.  Click Next
  3. Click on "New Connection" and then use the "Connection Properties" dialog to build the connection string to your database. I recommend that you use "SQL Server Authentication" with an account that is set up with only the necessary permissions for your applications
  4. Click on Test Connection, if the popup says "Test connection succeeded" then click on the "OK" button
  5. Click on the radio button labeled "Yes, include the sensitive data in the connection string" (this can be encrypted later if you wish)
  6. Click the checkbox labeled "Save entity connection settings in App.Config as" and then give prpper connection a name
  7. Click Next
  8. Select all the database objects that you want to include in the Entity Framework (Mostly database tables)
  9. Again give name for the Model Namespace  as per naming convention
  10. Click "Finish" to Wrap up
Finally our Entity Framework Model is created, But if you will observe whole model is in FaithDataLayer.Now our job is to take the Model out of this layer and push it to seperate/independent layer namely FaithModel. Once you are done, your Entity Framework Model should look something like this:
So now that you are looking at the Entity Framework file that you just created, right click on the canvas of the Entity Framework Model. Just select "Add Code Generation Item .

This will bring up another window showing the "Installed Templates" as shown in the screenshot below. Once the "Add New Item" window pops us, we want to select the template called "ADO.NET POCO Entity Generator". Again name it as per your naming convention . I prefer to keep the name the same as what I chose for the Entity Framework itself.

if you cannot find this template installed in your copy of Visual Studio, you can find instructions on searching for and installing templates in Google. Once you save the "ADO.NET POCO Entity Generator", two new files will be created in your data project as shown in below figure. In my project, they are: "" and "". At this point if you will try to compile solution you will not get any problem but there are certain things which need to be observed. First of all, underneath the new template files (with .tt extension), you will see that several new classes based on Model have been added to your DataAccessLayer project, as shown in the graphic below

Each of these new classes maps to an object in your Entity Framework model, which, in turn, map to a table in your database. These files are auto generated each time that the template file is saved, but if you open one up you will see that it is just a  standard class with properties defining each column in the table. You will also notice that they are partial classes. In Future series we will also see how to modify template to customize our classes.
Final Step to Separate your Model(POCO Classes):
Now that the Entity Framework Model is created, we are ready to seperate model classes from FaithDataLayer.
To move our POCO’s from the FaithDataLayer to the FaithModel is just Cut and Paste process. Right click on the FaithDataLayer Project  and select to cut and paste the name ( Select "Cut" from the context menu, then right click on the FaithModel project and select paste. All of its associated classes should now have moved to the FaithModel project. Our only problem now is that by moving the template we have broken the link between it and the Entity Framework model upon which it depends to create the POCO classes.  To do so, open the template file( that is now in the FaithModel project. Just open try to find  line of code that reads something like this:
string inputFile = @"Fmodel.edmx";
And change it to a path where it can find the Entity Framework model over in the FaithDataLayer  project, like so:
string inputFile = @"..\FaithDataLayer\FModel.edmx"; 
When you click save, you will know immediately if this worked. If it did, the class files for your database objects will be regenerated as they should be. If the path that you entered was incorrect or some typing mistake is there, however, you will receive a error . a big errorr.... If you are on Happy Path i mean if solution builds successfully then just remove System.Data.Entity refrence from FaithModel projects if exists then try to build you model project successfully without refrence of System.Data.Entity.
Project References Now we are can start adding  project references. Just set refrences like shown below:

FaithDataAccessLayer Project should Refer FaithModel Project
FaithBusinessLayer Project should Refer FaithDataAccessLayer Project & FaithModel Project
FaithServiceLayer Project should Refer FaithBusinessLayer
FaithModel Project –  No Project References
Note: Please,don't touch or think about Service layer,BusinessLayer  yet because we will use them at right time,i mean in future posts