Migrating records from SQL SERVER to MS ACCESS and Vice Versa

Often we come across situation where we have to design an application where we have to copy the data from one database to another for instance you have all your records in MS ACCESS and you have to design an application where you have to transfer all your table records to SQL SERVER or vice versa.

In this article we are going to discuss the above mentioned scenario in a very small scale so that we can have a very brief idea of how we can accomplish this task.

Below is the form:

In my example I have used Sql Server and MS Access database to interchange records, you can also use other combination of database like oracle and Sql server etc.

Note: In my example the tables structure are already created in both the databases, we are only copying the specific record from one table in one database to a table of same structure in another database.

So let's begin with our coding, we first require tables of same structure in both the database (MS ACCESS and SQL SERVER).

I have already discussed creating in SQL SERVER in previous articles so this time I will precede with MS ACCESS:

Below is screen shot of how you could create a database and a table in MS Access:

As you can see I have marked the procedure how can do that- first select the blank database then change the name of the database of your choice I have named it as migrated1 and then hit the create button.

After this a window will open and at the right hand corner you will find All Tables column in that you find your table is mentioned so right click and choose design view.

Below is screen shot:

After choosing design view it will ask you save the name of your table, you can save it with name of your choice and design view will be opened and here you can design your table.

Note: Design of both the tables in SQL SERVER and MS ACCESS should be same.

Now let's look at the most important thing which is C # code.

In this example I have used 2 different data grid for populating data from respective databases to grid and then we are transferring data through grid cells to another database. You can have your own way of doing it for example textboxes.

To populate data to grid click button "load data or refresh data" and to transfer data to different database double click on the cell (data grid view) as we have used CellDoubleClick event of data grid.

In the below screen shot clicking on the circled portion would transfer the records to different database.

Furthermore, we would be requiring 2 different:

1. Connection strings one for SQL Server and another for MS ACCESS.

E.g.

string constr = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=blog;Data Source=MY-PC\SQLEXPRESS";

string constr_ms = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Users\aaa\Desktop\migrated.accdb";

2.Connection objects:

SqlConnection con;

OleDbConnection con1;

 

3. Command objects

SqlCommand cmd;

OleDbCommand cmd1;

 

4. Data Adapter

SqlDataAdapter adpt;

OleDbDataAdapter adpt1;

 

5.Data Grid View one for populating Sql Server data and another for MS ACCESS data.

 

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.IO;

using System.Data.SqlClient;

using System.Data.OleDb;

namespace blog

{

public partial class Form4_migration : Form

{

SqlConnection con;

OleDbConnection con1;

string constr = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=blog;Data Source=My-PC\SQLEXPRESS";

string constr_ms = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Users\aaa\Desktop\migrated.accdb";

 

string sp_e_msg;

SqlCommand cmd;

OleDbCommand cmd1;

SqlDataAdapter adpt;

OleDbDataAdapter adpt1;

DataTable dt;

string notific;

string ntfic;

 

public Form4_migration()

{

InitializeComponent();

}

 

private void Form4_migration_Load(object sender, EventArgs e)

{

 

 

}

 

//*****data will flow from SQL SERVER to MS ACCESS*******************

private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)

{

try

{

textBox1.Text = dataGridView1.SelectedCells[0].Value.ToString();

textBox2.Text = dataGridView1.SelectedCells[1].Value.ToString();

textBox3.Text = dataGridView1.SelectedCells[2].Value.ToString();

textBox4.Text = dataGridView1.SelectedCells[3].Value.ToString();

 

con1 = new OleDbConnection();

con1.ConnectionString = constr_ms;

con1.Open();

//****string- sql insert statment**************************

string str1 = "Insert into migration(empid ,name,age,location) Values (" + textBox1.Text + ",'" + textBox2.Text + "','" + textBox3.Text + "','" + textBox4.Text + "' )";

 

cmd1 = new OleDbCommand(str1, con1);

cmd1.ExecuteNonQuery();

MessageBox.Show("Record Inserted successfully.");

 

con1.Close();

}

catch (Exception ex)

{

label1.Text = ex.ToString();

}

}

 

 

 

//***loading or refreshing data for MS ACCESS******************

private void button1_Click_1(object sender, EventArgs e)

{

try

{

con1 = new OleDbConnection();

con1.ConnectionString = constr_ms;

con1.Open();

//*************string- sql insert statment**************************

string str1 = "select * from migration";

 

cmd1 = new OleDbCommand(str1, con1);

adpt1 = new OleDbDataAdapter(cmd1);

dt = new DataTable();

adpt1.Fill(dt);

dataGridView2.DataSource = dt.DefaultView;

cmd1.ExecuteReader();

MessageBox.Show("Record Populated successfully for MS ACCESS");

 

con1.Close();

}

catch (Exception ex)

{

label1.Text = ex.ToString();

 

//************data will flow from MS ACCESS TO SQL SERVER*******************

private void dataGridView2_CellDoubleClick(object sender, DataGridViewCellEventArgs e)

{

try

{

textBox1.Text = dataGridView2.SelectedCells[0].Value.ToString();

textBox2.Text = dataGridView2.SelectedCells[1].Value.ToString();

textBox3.Text = dataGridView2.SelectedCells[2].Value.ToString();

textBox4.Text = dataGridView2.SelectedCells[3].Value.ToString();

con = new SqlConnection();

con.ConnectionString = constr;

con.Open();

 

cmd = new SqlCommand("blog_insert_migration", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("@empid", SqlDbType.NVarChar).Value = textBox1.Text;

 

cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = textBox2.Text;

cmd.Parameters.Add("@age", SqlDbType.NVarChar).Value = textBox3.Text;

cmd.Parameters.Add("@location", SqlDbType.NVarChar).Value = textBox4.Text;

//**************************************************************************

SqlParameter param = new SqlParameter("@returnval", SqlDbType.Int);

param.Size = 50;

param.Direction = ParameterDirection.ReturnValue;

 

//add to parameter to collection

cmd.Parameters.Add(param);

//***********************************************************************

SqlParameter param1 = new SqlParameter("@msg", SqlDbType.VarChar);

param1.Size = 50;

param1.Direction = ParameterDirection.Output;

 

//add to parameter to collection

cmd.Parameters.Add(param1);

//*************************************************************************

cmd.ExecuteNonQuery();

ntfic = cmd.Parameters["@msg"].Value.ToString();

label1.Text = cmd.Parameters["@msg"].Value.ToString();

ntfic = cmd.Parameters["@msg"].Value.ToString() + "--" + cmd.Parameters["@returnval"].Value.ToString();

 

}

 

catch (Exception ex)

{

label1.Text = ntfic + ex.ToString();

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

}

}

 

//**********load and refresh data for SQL SERVER*********************

private void button2_Click(object sender, EventArgs e)

{

try

{

con = new SqlConnection();

con.ConnectionString = constr;

con.Open();

cmd = new SqlCommand("blog_select2_up", con);

cmd.CommandType = CommandType.StoredProcedure;

//**********************************************************************

SqlParameter parm2 = new SqlParameter("@returnval", SqlDbType.Int);

parm2.Size = 50;

parm2.Direction = ParameterDirection.ReturnValue;

cmd.Parameters.Add(parm2);

//**************************************************************

SqlParameter parm1 = new SqlParameter("@msg", SqlDbType.VarChar);

parm1.Size = 50;

parm1.Direction = ParameterDirection.Output;

cmd.Parameters.Add(parm1);

//***************************************************************

adpt = new SqlDataAdapter(cmd);

dt = new DataTable();

adpt.Fill(dt);

dataGridView1.DataSource = dt.DefaultView;

cmd.ExecuteNonQuery();

notific = cmd.Parameters["@returnval"].Value.ToString();

//*************************************************************************

sp_e_msg = cmd.Parameters["@msg"].Value.ToString();

MessageBox.Show("Record Populated successfully for SQL SERVER");

 

}

catch (Exception ex)

{

if (notific == "1")

{

label1.Text = sp_e_msg + notific;

}

}

finally

{

con.Close();

con.Dispose();

cmd.Dispose();

}

}

 

 

}

}

Tags: , , , ,

Windows Form

Comments (1) -

Sweta India, on 6/24/2011 1:10:32 AM Said:

Sweta

HI Ankit..i was really looking for a link like this. Its really works. good job.