using System;
using System.Data;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace datasynchronization
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
string connectString = @"Data Source=MOON\SQL2005;Initial Catalog=databaseA;Integrated Security=True";
using (var srcCon = new SqlConnection(connectString)) //connection to source table
{
srcCon.Open();//source table connection open
SqlCommand cmd = new SqlCommand();// sqlobject for source table
cmd.Connection = srcCon;
string connectionString = @"Data Source=MOON\SQL2005;Initial Catalog=databaseB;Integrated Security=True";
using (var tgtCon = new SqlConnection(connectionString)) //connection to target table
{
tgtCon.Open(); //target table connection open
SqlCommand objcmd1 = new SqlCommand();//sqlobject for target table
objcmd1.Connection = tgtCon;
objcmd1.CommandText = "SELECT MAX(date) FROM Table_2"; //query to findout the max date from target table
var maxdate = objcmd1.ExecuteScalar(); // store the value of max date into the variable maxdate
cmd.CommandText = string.Format("SELECT id,date,name,city,salary,region FROM Table_1 where date >'{0}'", maxdate); //select query to fetch rows from source table
using (var reader = cmd.ExecuteReader())
{
SqlCommand objcmd = new SqlCommand();
objcmd.Connection = tgtCon;
objcmd.CommandText = "INSERT INTO Table_2(id,date,name,city,salary,region)VALUES(@id,@date,@name,@city,@salary,@region)";
objcmd.Parameters.Add("@id", SqlDbType.Int);
objcmd.Parameters.Add("@date", SqlDbType.DateTime);
objcmd.Parameters.Add("@name", SqlDbType.NVarChar);
objcmd.Parameters.Add("@city", SqlDbType.NVarChar);
objcmd.Parameters.Add("@salary", SqlDbType.Int);
objcmd.Parameters.Add("@region", SqlDbType.Char);
while (reader.Read())
{
var order1 = reader[0].ToString();
var order2 = reader[1].ToString();
var order3 = reader[2].ToString();
var order4 = reader[3].ToString();
var order5 = reader[4].ToString();
var order6 = reader[5].ToString();
objcmd.Parameters["@id"].Value = order1;
objcmd.Parameters["@date"].Value = order2;
objcmd.Parameters["@name"].Value = order3;
objcmd.Parameters["@city"].Value = order4;
objcmd.Parameters["@salary"].Value = order5;
objcmd.Parameters["@region"].Value = order6;
objcmd.ExecuteNonQuery();
}
}
tgtCon.Close();
}
srcCon.Close();
}
}
}
}
how can i organize the above written code in an efficient way?
