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 }
