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.Data.SqlClient;
using Microsoft.Win32;
namespace DataMigration
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
string dbName, tblName;
int cLeft = 1;
string conString = string.Empty;
private void
comboBox1_SelectedIndexChanged_1(object sender,
EventArgs e)
{
try
{
//comboBox1.Items.Clear();
dbName = (string)comboBox1.SelectedItem;
comboBox2.DataSource = GetTableList(dbName);
}
catch (Exception
ex)
{
MessageBox.Show(ex.Message);
}
}
private void
Form1_Load(object sender, EventArgs e)
{
try
{
comboBox3.DataSource = GetServerList();
//string compName = Environment.MachineName;
//string server =
(string)comboBox3.SelectedItem;
//conString = "server=" + compName +
"\\" + server + ";integrated security=true";
//comboBox1.DataSource = GetDatabaseList();
}
catch (Exception
ex)
{
MessageBox.Show(ex.Message);
}
}
public List<string> GetServerList()
{
List<string>
list = new List<string>();
RegistryKey baseKey = RegistryKey.OpenBaseKey(RegistryHive.LocalMachine, RegistryView.Registry64);
RegistryKey key = baseKey.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server\Instance
Names\SQL");
foreach (string s in key.GetValueNames())
{
list.Add(s);
}
key.Close();
baseKey.Close();
return list;
}
public List<string>
GetDatabaseList()
{
List<string>
list = new List<string>();
// Open connection to the database
//string conString =
"server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";
using (SqlConnection
con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and
associate
// this with the current connection.
using (SqlCommand
cmd = new SqlCommand("select * from sys.databases where database_id not
in (1,2,3,4)", con))
{
using (IDataReader
dr = cmd.ExecuteReader())
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
}
}
return list;
}
private void
comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
tblName = (string)comboBox2.SelectedItem;
// comboBox3.DataSource =
GetColumnList(tblName);
AddNewTextBox(GetColumnList(tblName));
}
catch (Exception
ex)
{
MessageBox.Show(ex.Message);
}
}
public List<string> GetTableList(string
databasename)
{
List<string>
list = new List<string>();
// Open connection to the database
// string conString =
"server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";
using (SqlConnection
con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and
associate
// this with the current connection.
using (SqlCommand
cmd = new SqlCommand("select table_name from " + databasename
+ ".INFORMATION_SCHEMA.TABLES where TABLE_TYPE
= 'BASE TABLE'", con))
{
using (IDataReader
dr = cmd.ExecuteReader())
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
}
}
return list;
}
public List<string> GetColumnList(string
tblName)
{
List<string>
list = new List<string>();
// Open connection to the database
// string conString =
"server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";
using (SqlConnection
con = new SqlConnection(conString))
{
con.Open();
// Set up a command with the given query and
associate
// this with the current connection.
using (SqlCommand
cmd = new SqlCommand("USE " + dbName + " select name from " + dbName + ".sys.columns WHERE is_identity = 0 and object_id=object_id('"
+ tblName + "')", con))
{
using (IDataReader
dr = cmd.ExecuteReader())
{
while (dr.Read())
{
list.Add(dr[0].ToString());
}
}
}
}
return list;
}
public void
AddNewTextBox(List<string> Columns)
{
cLeft = 1;
panel1.Controls.Clear();
List<string>
col = Columns;
int count = col.Count;
for (int i = 0; i
< count; i++)
{
System.Windows.Forms.TextBox txt = new System.Windows.Forms.TextBox();
panel1.Controls.Add(txt);
txt.Top = cLeft + 25;
txt.Left = 150;
txt.Name = col[i];
System.Windows.Forms.Label lbl = new System.Windows.Forms.Label();
panel1.Controls.Add(lbl);
lbl.Top = cLeft + 25;
lbl.Left = 40;
lbl.Text = col[i];
cLeft = cLeft + 25;
}
}
private void
button1_Click(object sender, EventArgs e)
{
//AddNewTextBox();
}
private void
panel1_Paint(object sender, PaintEventArgs e)
{
}
private void
button1_Click_1(object sender, EventArgs e)
{
List<PairValue>
obj = new List<PairValue>();
foreach
(Control ctr in
panel1.Controls)
{
if (ctr is
TextBox)
{
PairValue p = new PairValue();
p.value = ((TextBox)ctr).Text;
p.name = ((TextBox)ctr).Name;
obj.Add(p);
}
}
string query1 = string.Empty;
string query2 = string.Empty;
foreach (PairValue
p in obj)
{
query1 += p.name + ",";
query2 += "'" + p.value + "'" + ",";
}
query1 = query1.Substring(0, query1.LastIndexOf(","));
query2 = query2.Substring(0, query2.LastIndexOf(","));
string sql = "INSERT
INTO " + dbName + ".dbo."
+ tblName + "(" + query1 + ") VALUES(" + query2 + ")";
//string conString =
"server=ICECUBES\\SQLSERVER2008;uid=sa;pwd=sa123";
SqlConnection con = new
SqlConnection(conString);
try
{
SqlCommand cmd = new SqlCommand(sql,
con);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
int temp = cmd.ExecuteNonQuery();
if (temp > 0)
{
MessageBox.Show("Data Are Added Successfully");
}
else
{
MessageBox.Show("Try Again");
}
}
catch (Exception
ex)
{
MessageBox.Show(ex.Message);
}
con.Close();
}
private void
comboBox3_SelectedIndexChanged(object sender, EventArgs e)
{
try
{
string compName = Environment.MachineName;
string server = (string)comboBox3.SelectedItem;
conString = "server=" +
compName + "\\" + server + ";integrated security=true";
comboBox1.DataSource = GetDatabaseList();
}
catch (Exception
ex)
{
MessageBox.Show(ex.Message);
}
}
}
public class PairValue
{
public string name { get; set; }
public string value {
get; set; }
}