一个关于datagridview保存新增数据到数据库的问题,高手请进!

Question

我的代码是下面这样的写的,现在不知道如何用更好的办法把DataGridView1中所输入的每行数据给保存到sqlserver数据库中(我用的是简单三层架构框架)         private void w_store_in_Load(object sender, EventArgs e)
        {
            DataSet dsDetail = new DataSet();
            DataTable dt = dsDetail.Tables.Add("newTables");
            DataColumn CurrentState = dt.Columns.Add("CurrentState", typeof(string));
            DataColumn NewState = dt.Columns.Add("NewState", typeof(string));
            DataColumn GoodsNo = dt.Columns.Add("GoodsNo", typeof(string));
            DataColumn KindNo = dt.Columns.Add("KindNo", typeof(string));
            DataColumn ShopsignNo = dt.Columns.Add("ShopsignNo", typeof(string));
            DataColumn PackNum = dt.Columns.Add("PackNum", typeof(string));
            DataColumn ProareaNo = dt.Columns.Add("ProareaNo", typeof(string));
            DataColumn GoodsOwnerPackNum = dt.Columns.Add("GoodsOwnerPackNum", typeof(string));
            DataColumn GradeNum = dt.Columns.Add("GradeNum", typeof(string));
            DataColumn InNetweight = dt.Columns.Add("InNetweight", typeof(decimal));
            DataColumn InGrossWeight = dt.Columns.Add("InGrossWeight", typeof(decimal));
            DataColumn SheetNum = dt.Columns.Add("SheetNum", typeof(decimal));
            DataColumn OwnerNo = dt.Columns.Add("OwnerNo", typeof(string));
            DataColumn GoodsLoc = dt.Columns.Add("GoodsLoc", typeof(string));
            CurrentState.MaxLength = 2;
            //设置表主键
              GoodsNo.Unique = true;
            GoodsNo.AllowDBNull = false;            dt.NewRow();
            dsDetail.Tables[0].Rows.Add(dsDetail.Tables[0].NewRow());
          
            dataGridView1.DataSource = dsDetail.Tables["newTables"];
            dataGridView1.Columns["GoodsNo"].Visible = false;
            dataGridView1.Columns["CurrentState"].HeaderText = "当前状态";
            dataGridView1.Columns["NewState"].HeaderText = "新状态";
            dataGridView1.Columns["GoodsNo"].HeaderText = "系统编号";
            dataGridView1.Columns["KindNo"].HeaderText = "品种";
            dataGridView1.Columns["ShopsignNo"].HeaderText = "钢种";
            dataGridView1.Columns["PackNum"].HeaderText = "捆包";
            dataGridView1.Columns["ProareaNo"].HeaderText = "产地";
            dataGridView1.Columns["GoodsOwnerPackNum"].HeaderText = "货主捆包号";
            dataGridView1.Columns["GradeNum"].HeaderText = "规格";
            dataGridView1.Columns["InNetWeight"].HeaderText = "净重";
            dataGridView1.Columns["InGrossWeight"].HeaderText = "毛重";
            dataGridView1.Columns["OwnerNo"].HeaderText = "货主";
            dataGridView1.Columns["GoodsLoc"].HeaderText = "库位";
            dataGridView1.Columns["SheetNum"].HeaderText = "张数";
            //字段格式化
              dataGridView1.Columns["InNetWeight"].DefaultCellStyle.Format = "N3";
            dataGridView1.Columns["InNetWeight"].DefaultCellStyle.Format = "N3";
}

Answer 1

private   DataTable   DataGridToDataTable(DataGridView   dgList)
                {
                        DataTable   newdt   =   new   DataTable();
                        try
                        {
                                DataTable   dt2   =   (DataTable)dgList.DataSource;
                                int   colCount   =   dgList.Columns.GetColumnCount(DataGridViewElementStates.Visible);
                                string[]   ColumnNames   =   new   string[colCount];
                                int   j   =   0;
                                for   (int   i   =   0;   i   <   dgList.Columns.Count;   i++)
                                {
                                        if   (dgList.Columns[i].Visible)
                                        {
                                                ColumnNames[j]   =   dgList.Columns[i].Name.ToString();
                                                j++;
                                        }
                                }
                               
                                foreach   (string   ColumnName   in   ColumnNames)
                                {
                                        newdt.Columns.Add(ColumnName);
                                }                                for   (int   i   =   0;   i   <   dt2.Rows.Count;   i++)
                                {
                                        DataRow   drNew   =   newdt.NewRow();
                                        foreach   (string   ColumnName   in   ColumnNames)
                                        {
                                                drNew[ColumnName]   =   dt2.Rows[i][ColumnName];
                                        }
                                        newdt.Rows.Add(drNew);
                                        newdt.AcceptChanges();
                                }
                        }
                        catch   (Exception   ex)
                        {
                                MessageBox.Show(ex.Message);
                        }
                        return   newdt;
 
                }  DataTable   dt   =   this.DataGridToDataTable(dgList);
SqlDataAdapter   +   SqlCommandBuilder   ..调用SqlDataAdapter.Update(DataTable); 

Answer 2

DataTable和datagridview1已经是关联的了,所以不用再用代码获取!关键是这个sqldataAdapter该如何创建呢?如何通过sqldataadapter把datatable中的数据保存到数据库中呢??因为我不想在当前代码中创建数据库连接(因为是三层架构,有专用的数据库处理类),有没有好的办法实现???

Answer 3

void updateTable(DataTable dt)
{
string select = new BLL.CenterInfo().PublicSelect;//关键使这里select 必须和你datatable的字段完全一致就可以
 OleDbConnection conn = new OleDbConnection(DataAccess.SqlHelper.CONN_MAIN); OleDbDataAdapter _da = new OleDbDataAdapter("SELECT " + select + " FROM " + dt.TableName, conn);
 OleDbCommandBuilder cmdb = new OleDbCommandBuilder(_da);
try
{
     _da.Update(dt);
     conn.Close();
  }
 catch (Exception ex)
 {
    conn.Close();
  }
}

Answer 4

把datagridview每行的值赋给一个insert的sql语句,然后保存入数据库

Answer 5

便利GRIDVIEW,把每条信息都取出来赋给一个对象的成员。
用一个ARRAYLIST来承载这个对象（Gridview每条看做一个对象），将这个Arraylist作为参数传递到你的DB层，再便利这个对象取出值  

Answer 6

http://www.wangchao.net.cn/bbsdetail_482043.html