Synchronize Data from serverDB to Local DB

Question

I would like to get all the details of a single table from Remote server DB to my local DB, during
page load event which should happen as a back end process can any one help me over this issue.

NOTE:
Server DB table Columns may slightly differ from local DB.
Each time when a new user is added in the server, it should update the local DB when the UserPage.aspx page is loaded.

Tools using: ASP.NET,SQL SERVER 2008.

Eg:
Let the DB name be sample and the table name is customer

**Table Header in Server DB:** Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob 
**Table Headers in Local DB:** Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob,Cus_password

Once the page gets loaded all the data in Customer table from serve DB should be synchronized to localDB

[There is a Bulk Copy feature in ADO.NET](http://msdn.microsoft.com/en-us/library/s4s223c6.aspx) Or you may try this [example](http://stackoverflow.com/a/18209455/3089494) — pravprab, Mar 12 '14 at 08:01
if i may ask, why the page load before copy to local DB and why asp.net? you can schedule a job that will help move data from one db to another since they are on the same server. — demo.b, Mar 12 '14 at 08:02
application is developed in two mode Desktop and web application, so when users are added using Desktop Application their details are to be synchronized in Web Application,so that user can access both.. — faroke moahmed, Mar 12 '14 at 08:05
Ok, why not use the same database for desktop and web application — demo.b, Mar 12 '14 at 08:14
there are some different details are maintained.. so each one have seperate DB. @demo.b — faroke moahmed, Mar 12 '14 at 08:16
Agreed - just use a trigger to synchronise it. Page Load event is probably the worst way to do this. — Nick.Mc, Mar 12 '14 at 10:41

Archlight · Answer 1 · 2014-03-12T08:48:13.880

0

Asuming that the database login has access to both db's you can execute the following string as one command through your database connections.

--empty local table
truncate table [sample]..customer;

--fill local table
insert into [sample]..customer 
    (Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob) 
    select Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob  from serverDb..Customer;

This will get you startet, but this is not good architecture. You might want to put a trigger on the table in the server database that will insert the new row in your local database everytime there is a new row in the serverDb.

edit

showing the whole process

protected void Page_Load(object sender, EventArgs e){
      string sql = @"--empty local table
            truncate table [sample]..customer;

            --fill local table
            insert into [sample]..customer 
                (Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob) 
                select Cus_id,Cus_name,Cus_address,Cus_email,Cus_mob  from serverDb..Customer;";

    var conn = new SqlConnection("Server=localhost;Database=sample;User Id=myUserName;Password = myPassword");
    var cmd = conn.CreateCommand();
    cmd.CommandText = sql;
    conn.Open();
    cmd.ExecuteNonQuery();
 }

edited Mar 12 '14 at 08:48

answered Mar 12 '14 at 07:59

Archlight

2,019
2
21
34

i need to done this process during page load.. i had mentioned in my post please review it.. – faroke moahmed Mar 12 '14 at 08:06
yes you can do this in your page load. Just execute this text in your connection object. Do you need help with that too ? – Archlight Mar 12 '14 at 08:23
Warning.. this will empty the localdb on every page load – Archlight Mar 12 '14 at 08:48
it will be tedious for me.. it should add the new entry alone..there will be more number of data if it loads each time.. time data will be changed.. – faroke moahmed Mar 12 '14 at 09:36
1

I recomend that you use a trigger in your database. Do you have the rights to do that ? – Archlight Mar 12 '14 at 09:51
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/49550/discussion-between-archlight-and-faroke-moahmed) – Archlight Mar 12 '14 at 09:51

demo.b · Answer 2 · 2014-03-12T09:28:45.163

0

I will advice you SCHEDULE a job to load record to localdb from the backend every 15mins interval. You can use management studios to achieve that.

edited Mar 12 '14 at 09:28

answered Mar 12 '14 at 08:25

demo.b

3,299
2
29
29

can you pleas say how can i ahcive it..? – faroke moahmed Mar 12 '14 at 09:38
Please read more here http://stackoverflow.com/questions/187770/copy-tables-from-one-database-to-another-in-sql-server – demo.b Mar 12 '14 at 10:02

Synchronize Data from serverDB to Local DB

2 Answers2