Data Import from MSAccess To Sql Server Assumptions: 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
Introduction 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
Select "Generate From Database" on the "Choose
Model Contents" page.
Click Next
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
Click on Test Connection, if the popup says "Test
connection succeeded" then click on the "OK"
button
Click on the radio button labeled "Yes, include the
sensitive data in the connection string" (this can be encrypted later
if you wish)
Click the checkbox labeled "Save entity connection settings in App.Config
as" and then give prpper connection a name
Click Next
Select all the database objects that you want to include in the Entity Framework
(Mostly database tables)
Again give name for the Model Namespace as per naming convention
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: "FModel.Context.tt" and "FModel.tt".
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 FModel.tt to cut and paste the name (FModel.tt). 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(FModel.tt) that is now in the
FaithModel project. Just open FModel.tt 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:
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
