1  private void Form1_Load(object sender, EventArgs e)
  2         {
  3             BindDataBase(combDataBaseNew, 0, "");
  4             BindDataBase(combDataBaseOld, 0, "");
  5             
  6         }
  7         //获取新数据库 0 是数据库 1是 表
  8         private void BindDataBase(ComboBox combDataBase, int style, string database)
  9         {
 10             switch (style)
 11             {
 12                 case 0:
 13                     {
 14                         string strSql = "select name from sysdatabases order by name";
 15                         combDataBase.DataSource = this.GetDataBases(strSql).Tables[0];
 16                         combDataBase.DisplayMember = "name";
 17                         combDataBase.ValueMember = "name";
 18                         break;
 19                     }
 20                 case 1:
 21                     {
 22                         StringBuilder sb = new StringBuilder();
 23                         sb.AppendFormat("use {0}", database);
 24                         sb.AppendFormat(" SELECT Name from sysobjects Where Type=‘U‘ ORDER BY Name");
 25                         combDataBase.DataSource = this.GetDataBases(sb.ToString()).Tables[0];
 26                         combDataBase.ValueMember = "name";
 27                         combDataBase.DisplayMember = "name";
 28                         break;
 29                     }
 30             }
 31         }
 32         //获取数据库连接
 33         private SqlConnection GetConnections()
 34         {
 35             SqlConnectionStringBuilder sqlsb = new SqlConnectionStringBuilder();
 36             sqlsb.DataSource = "localhost";
 37             sqlsb.IntegratedSecurity = true;
 38             SqlConnection conn = new SqlConnection();
 39             conn.ConnectionString = sqlsb.ConnectionString;
 40             return conn;
 41         }
 42         //根据查询语句，获取对应的数据集
 43         private DataSet GetDataBases(string strSql)
 44         {
 45             SqlDataAdapter sda = new SqlDataAdapter();
 46             SqlCommand cmd = new SqlCommand();
 47             DataSet ds = new DataSet();
 48             using (SqlConnection conn = this.GetConnections())
 49             {
 50                 conn.Open();
 51                 cmd.CommandText = strSql;
 52                 cmd.CommandType = CommandType.Text;
 53                 cmd.Connection = conn;
 54                 sda.SelectCommand = cmd;
 55                 sda.Fill(ds, "databases");
 56                 conn.Close()        ;
 57                
 58             }
 59             return ds;
 60         }
 61         private int GetDoIt(string strSql)
 62         {
 63             SqlCommand cmd = new SqlCommand();
 64             object b = null;
 65             using (SqlConnection conn = this.GetConnections())
 66             {
 67                 conn.Open();
 68                 cmd.CommandText = strSql;
 69                 cmd.CommandType = CommandType.Text;
 70                 cmd.Connection = conn;
 71                 b= cmd.ExecuteNonQuery();
 72                 conn.Close();
 73             }
 74            return Convert.ToInt32(b);
 75         }
 76         private void btn_biaoOld_Click(object sender, EventArgs e)
 77         {
 78             BindDataBase(comOldtable, 1, combDataBaseOld.SelectedValue.ToString());
 79 
 80         }
 81 
 82         private void btn_biaoNew_Click(object sender, EventArgs e)
 83         {
 84             BindDataBase(comNewtable, 1, combDataBaseNew.SelectedValue.ToString());
 85         }
 86         //绑定grid控件
 87         public void GetTableZiDuan()
 88         {
 89             string strSql = string.Format("use {0}  Select Name FROM SysColumns Where id=Object_Id(‘{1}‘)", combDataBaseOld.SelectedValue, comOldtable.SelectedValue);
 90             this.dataGridView1.DataSource = GetDataBases(strSql).Tables[0];
 91 
 92         }
 93         //绑定combobox控件
 94         public void GetNewTableZiDuan()
 95         {
 96             string strSql = string.Format("use {0}  Select Name FROM SysColumns Where id=Object_Id(‘{1}‘)", combDataBaseNew.SelectedValue, comNewtable.SelectedValue);
 97             this.NewZiDuan.DataSource = GetDataBases(strSql).Tables[0];
 98             this.NewZiDuan.ValueMember = "name";
 99             this.NewZiDuan.DisplayMember = "name";
100         }
101         private void btn_OldZiDuan_Click(object sender, EventArgs e)
102         {
103             GetTableZiDuan();
104         }
105 
106         private void btn_NewZiDuan_Click(object sender, EventArgs e)
107         {
108             GetNewTableZiDuan();
109         }
110         private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
111         {
112             if (this.dataGridView1.Rows.Count != 0)
113             {
114                 for (int i = 0; i < this.dataGridView1.Rows.Count; )
115                 {
116                     this.dataGridView1.Rows[i].DefaultCellStyle.BackColor = System.Drawing.Color.Pink;
117                     i += 2;
118                 }
119             }
120         }
121 
122         private void btn_StartZhuanLu_Click(object sender, EventArgs e)
123         {
124             List<string> oldList = new List<string>();
125             List<string> newList = new List<string>();
126             for (int i = 0; i < this.dataGridView1.Rows.Count; i++)
127             {
128                 if ((bool)(((DataGridViewCheckBoxCell)this.dataGridView1.Rows[i].Cells["isTrue"]).EditedFormattedValue) == true)
129                 {
130                     oldList.Add(dataGridView1.Rows[i].Cells["OldZiDuan"].Value.ToString());
131                     newList.Add(((DataGridViewComboBoxCell)dataGridView1.Rows[i].Cells["NewZiDuan"]).Value.ToString());
132                 }
133             }
134            OldToNew(oldList,newList,"");
135         }
136         private void OldToNew(List<string> oldList, List<string> newList, string where)
137         {
138             StringBuilder sb = new StringBuilder();
139             sb.AppendFormat("use {0}",combDataBaseOld.SelectedValue);
140             sb.AppendFormat(" select ");
141             for (int i = 0; i < oldList.Count; i++)
142             {
143                 sb.AppendFormat(oldList[i] + ",");
144             }
145             sb.Remove(sb.ToString().LastIndexOf(‘,‘), 1);
146             sb.AppendFormat(" from {0}", comOldtable.SelectedValue);
147             if (!string.IsNullOrEmpty(where))
148             {
149                 sb.AppendFormat(" where {0}", where);
150             }
151             MessageBox.Show(sb.ToString());
152            DataTable dt= GetDataBases(sb.ToString()).Tables[0];
153            if (dt.Rows.Count > 0)
154            {
155                int sum = 0;
156                for (int i = 0; i < dt.Rows.Count; i++)
157                {
158                  sum+=InsertNewDataBase(dt,i,newList,oldList);
159                }
160                if (sum == dt.Rows.Count)
161                {
162                    MessageBox.Show("数据转录成功");
163                }
164                else
165                {
166                    if (sum != 0)
167                    {
168                        MessageBox.Show("理论转录信息条数：" + dt.Rows.Count + "；实践转录信息条数：" + sum + ";实际转录条数与理论条数不符");
169                    }
170                    
171                }
172            }
173            else 
174            {
175                MessageBox.Show("要转录的旧数据库，没有数据信息");
176            }
177         }
178         private int  InsertNewDataBase(DataTable dt,int a,List<string> newList,List<string> oldList)
179         {
180             int sum = 0;
181             try
182             {
183                 StringBuilder sb = new StringBuilder();
184                 sb.AppendFormat("use {0}", combDataBaseNew.SelectedValue);
185                 sb.AppendFormat(" insert into {0} (", comNewtable.SelectedValue);
186                 for (int i = 0; i < newList.Count; i++)
187                 {
188                     sb.AppendFormat(newList[i] + ",");
189                 }
190                 sb.Remove(sb.ToString().LastIndexOf(‘,‘), 1);
191                 sb.AppendFormat(")values(");
192                 for (int i = 0; i < oldList.Count; i++)
193                 {
194                     sb.AppendFormat("‘" + dt.Rows[a][oldList[i]] + "‘" + ",");
195                 }
196                 sb.Remove(sb.ToString().LastIndexOf(‘,‘), 1);
197                 sb.AppendFormat(")");
198                 MessageBox.Show(sb.ToString());
199                 sum = GetDoIt(sb.ToString());
200                 return sum;
201             }
202             catch (Exception e)
203             {
204                 MessageBox.Show(e.Message);
205                 return sum;
206             }
207            
208         }
209     }