sql数据库之间数据的转录
浏览数：28 /
时间：2015年06月12日
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 }
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
