C# DataGridView插入DB

        public static bool ContrastColumns(DataColumnCollection co1, DataGridViewColumnCollection co2)
        {
            bool aa = false;
            if (co1.Count == co2.Count)
            {
                for (int i = 0; i < co1.Count; i++)
                {
                    if (co1[i].Caption != co2[i].Name)
                    {
                        aa = false;
                        break;
                    }
                    else
                    {
                        aa = true;
                    }
                }
                return aa;
            }
            else
                return false;
        }

        public static string DataGridViewToDB(DataGridView DGV, string connstr, string Tablestr)
        {
            int aa = 0;
            string mycmd = "select * from " + Tablestr;
            DataTable dt = SqlHelper.SQLCommand<DataTable>(mycmd, connstr);

            if (ContrastColumns(dt.Columns, DGV.Columns))
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("insert into " + Tablestr + "(");

                for (int i = 0; i < DGV.ColumnCount; i++)
                {
                    if (i == DGV.ColumnCount - 1)
                    {
                        sb.Append(DGV.Columns[i].Name + ") values (");
                    }
                    else
                    {
                        sb.Append(DGV.Columns[i].Name + ",");
                    }
                }

                foreach (DataGridViewRow dgvRow in DGV.Rows)
                {
                    StringBuilder mysb = new StringBuilder();
                    mysb.Append("select * from " + Tablestr + " where ");

                    StringBuilder sb2 = new StringBuilder();
                    for (int i = 0; i < dgvRow.Cells.Count; i++)
                    {
                        if (i == 0)
                        {
                            mysb.Append(DGV.Columns[i].Name + "='" + dgvRow.Cells[i].Value + "'");
                        }
                        else
                        {
                            mysb.Append(" and " + DGV.Columns[i].Name + "='" + dgvRow.Cells[i].Value + "'");
                        }

                        if (i == dgvRow.Cells.Count - 1)
                        {
                            sb2.Append("'" + dgvRow.Cells[i].Value + "')");
                        }
                        else
                        {
                            sb2.Append("'" + dgvRow.Cells[i].Value + "',");
                        }
                    }
                    string sqlcmd = sb.ToString() + sb2.ToString();

                    try
                    {
                        DataTable mydt = SqlHelper.SQLCommand<DataTable>(mysb.ToString(), connstr);
                        if (mydt.Rows.Count == 0)
                        {
                            SqlHelper.SQLCommand<int>(sqlcmd, connstr);
                            aa++;
                        }
                    }
                    catch (Exception ee)
                    {
                        ee.ToString();
                    }
                }
                return "迁移成功:共" + aa.ToString() + "条记录";
            }
            else
                return "迁移失败:表头不匹配";
        }

 

posted @ 2014-12-10 15:54  bobo-bobo  阅读(317)  评论(0编辑  收藏  举报