frmmain.cs

【原创】很多情况下

        private void comBTable_SelectedIndexChanged(object sender, EventArgs e)
        {
            //根据选择的表名，获取字段名
            string strConB = "SERVER=" + this.comBServe.Text.Trim() + ";" +
               "Initial Catalog=" + this.comBDataBase.Text.Trim() + ";" +
               "User=sa;" +
               "Password=" + this.txtBPwd.Text.Trim().Replace("'", "");
            try
            {
                ds_FileB = DbHelperSQL.Query("SELECT a.Name as Name ,a.Length as Len,b.Name as [Type] FROM  syscolumns as a,systypes b where a.xtype=b.xtype AND b.Name <> 'sysname' AND id=object_id('" + this.comBTable.Text.Trim() + "')", strConB);
                this.listB.Items.Clear();
                
                HasB = new System.Collections.Hashtable();

                for (int i = 0; i < ds_FileB.Tables[0].Rows.Count; i++)
                {
                    Model B = new Model();
                    B.Name = this.ds_FileB.Tables[0].Rows[i]["Name"].ToString().Trim();
                    B.Len = Convert.ToInt32(this.ds_FileB.Tables[0].Rows[i]["Len"].ToString().Trim());
                    B.Type = this.ds_FileB.Tables[0].Rows[i]["Type"].ToString().Trim();

                    HasB.Add(ds_FileB.Tables[0].Rows[i]["Name"].ToString(), B);

                    this.listB.Items.Add(B.Name + " " + B.Type + "(" + B.Len.ToString() + ")");
                }

                if (this.listB.Items.Count > 0)
                {
                    this.listB.SelectedIndex = 0;
                }
            }
            catch (Exception ex)
            {
                Pub.ShowWarning("获取字段名失败！" + "\n" + ex.Message);
            }
        }

        private void btnAuto_Click(object sender, EventArgs e)
        {
            if (ds_FileA == null || ds_FileB == null)
            {
                return;
            }

            DataTable Tb = new DataTable();
            Tb.Columns.Add("目标字段");
            Tb.Columns.Add("数据字段");            

            for (int i = 0; i < this.ds_FileB.Tables [0].Rows.Count; i++)
            {
                bool boo = false;
                int k = 0;
                for (int j = 0; j < this.ds_FileA.Tables[0].Rows.Count; j++)
                {
                    if (this.ds_FileB.Tables[0].Rows[i]["Name"].ToString().Trim() == this.ds_FileA.Tables[0].Rows[j]["Name"].ToString().Trim())
                    {
                        boo = true;
                        k = j;
                        break;
                    }
                }
                if (boo)
                {
                    DataRow rw = Tb.NewRow();
                    rw["目标字段"] = this.ds_FileB.Tables[0].Rows[i]["Name"].ToString().Trim();
                    rw["数据字段"] = this.ds_FileA.Tables[0].Rows[k]["Name"].ToString().Trim();
                    Tb.Rows.Add(rw);
                }
                else
                {
                    DataRow rw = Tb.NewRow();
                    rw["目标字段"] = this.ds_FileB.Tables[0].Rows[i]["Name"].ToString().Trim();
                    rw["数据字段"] = "无匹配字段";
                    Tb.Rows.Add(rw);
                }
            }

            #region 填充列表
            DataGridViewComboBoxColumn Com = new DataGridViewComboBoxColumn();
            Com.Name = "数据字段";
            Com.HeaderText = "数据字段";
            Com.DataPropertyName = "数据字段";
            Com.Items.Add("无匹配字段");
            for (int i = 0; i < ds_FileA.Tables[0].Rows.Count; i++)
            {
                Com.Items.Add(ds_FileA.Tables[0].Rows[i]["Name"].ToString().Trim());
            }            

            if (Com.Items.Count > 0)
            {
                Tb.Columns[1].DefaultValue = Com.Items[0].ToString();
            }
            #endregion

            this.dataGridView1.DataSource = Tb;
            this.dataGridView1.Columns.RemoveAt(1);
            this.dataGridView1.Columns.Insert(1, Com);
            this.dataGridView1.Columns[0].ReadOnly = true;
        }

        private void btnOut_Click(object sender, EventArgs e)
        {
            if (this.dataGridView1.DataSource == null)
            {
                Pub.ShowWarning("没有数据集，请先匹配字段");
                return;
            }

            #region 将DataGridView中的对应数据源为对象值。
            //建立新的哈希键值对，以目标源字段为键，将DataGridView中的对应数据源为对象值。
            DataTable dt = (DataTable)this.dataGridView1.DataSource;
            System.Collections.Hashtable Has = new System.Collections.Hashtable();
            
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Model m = new Model ();
                Model mA = (Model)HasA[dt.Rows[i]["数据字段"].ToString()];
                Model mB = (Model)HasB[dt.Rows[i]["目标字段"].ToString()];
                m.Name = mB.Name;
                m.Len = mB.Len;
                m.Type = mB.Type;
                m.OldName = mA==null?"":mA.Name;
                m.OldLen = mA==null?0:mA.Len;
                m.OldType = mA == null ? "" : mA.Type;
                Has.Add(dt.Rows[i]["目标字段"].ToString().Trim(), m);
            }
            #endregion

            string strCon = "SERVER=" + this.comBServe.Text.Trim() + ";" +
               "Initial Catalog=" + this.comBDataBase.Text.Trim() + ";" +
               "User=sa;" +
               "Password=" + this.txtBPwd.Text.Trim().Replace("'", "");

            string strConOld = "SERVER=" + this.comAServe.Text.Trim() + ";" +
               "Initial Catalog=" + this.comADataBase.Text.Trim() + ";" +
               "User=sa;" +
               "Password=" + this.txtAPwd.Text.Trim().Replace("'", "");

            //获取数据源的表内数据集合 
            string strSQL = "Select * from " + this.comATable .Text .Trim ();
            DataSet ds_Old = DbHelperSQL.Query(strSQL, strConOld);
            System.Collections.ArrayList Array = new System.Collections.ArrayList();

            #region 根据数据源的记录，组合插入语句命名，图片不做处理
            for (int i = 0; i < ds_Old.Tables[0].Rows.Count; i++)
            {
                //将数据从数据源复制到目标源
                StringBuilder strSQLBuilder = new StringBuilder();
                strSQLBuilder.Append("Insert Into " + this.comBTable.Text.Trim() + " (");
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    Model mm = (Model)Has[dt.Rows[j]["目标字段"].ToString()];
                    if (mm.Type.ToLower().Contains("image"))
                    {
                        continue;
                    }
                    strSQLBuilder.Append(dt.Rows[j]["目标字段"].ToString());
                    strSQLBuilder.Append(",");
                }
                strSQLBuilder.Remove(strSQLBuilder.Length - 1, 1);
                strSQLBuilder.Append(") Values(");
                //根据哈希表的匹配字段进行 取值
                for (int j = 0; j < dt.Rows.Count; j++)
                {
                    Model mm = (Model)Has[dt.Rows[j]["目标字段"].ToString()];
                    #region 如果是数字型
                    if (mm.Type.ToLower().Contains("int") || mm.Type.ToLower().Contains("float") || mm.Type.ToLower().Contains("money") || mm.Type.ToLower().Contains("numeric") || mm.Type.ToLower().Contains("decimal"))
                    {
                        if (mm.OldName == "")
                        {
                            strSQLBuilder.Append("0");
                        }
                        else
                        {
                            strSQLBuilder.Append(ds_Old.Tables[0].Rows[i][mm.OldName].ToString());
                        }
                    }
                    #endregion
                    #region 如果是图片类型
                    else if (mm.Type.ToLower().Contains("image"))
                    {
                        continue;
                    }
                    #endregion
                    #region 如果是字符型
                    else
                    {
                        if (mm.OldName == "")
                        {
                            strSQLBuilder.Append("'" + "'");
                        }
                        else
                        {
                            strSQLBuilder.Append("'" + ds_Old.Tables[0].Rows[i][mm.OldName].ToString() + "'");
                        }
                    }
                    #endregion
                    strSQLBuilder.Append(",");
                }
                strSQLBuilder.Remove(strSQLBuilder.Length - 1, 1);
                strSQLBuilder.Append(")");
                //SQL 字符串处理完成，增加到命令列表，准备事务插入数据库
                Array.Add(strSQLBuilder.ToString());
            }
            #endregion

            //执行插入
            int intResult = DBUtility.DbHelperSQL.ExecuteSqlTran(Array, strCon);
            this.labInfo.Text = "数据源记录共 " + ds_Old.Tables [0].Rows .Count .ToString () + " 条，执行结果在目标库中插入记录 " + intResult.ToString() + " 条 ";
        }
    }
}