将DataGridview中的数据存入数据库

在机房收费的时候我们都会用到DataGridView控件，用来显示从数据库中返回的数据。如何用DataGridView将数据存入到数据库，而且是存入到字段与数据库中不一致的表中呢？

前提介绍

为了将如下的11张表中数据录入到数据库中

这是我们设计的数据库，将部门、公司、表类型、行字段、列字段分别抽象成不同的表，然后用他们的主键作为联合主键形成T_dataTable表

这是显示出的DataGridView形式，开始默认为0

Load事件

将DataGridview的标题整体右移一个单位格，将空出的第一个格显示本表名称，列显示：将每列的第一个单位格中数据显示为列值。

     Private Sub frmGovernmentTable1_Load(sender As Object, e As EventArgs) Handles MyBase.Load        '增加行数        dgvData.Rows.Add(8)        lineNo = 9        rowsNo = 18        listNo = 1        '初始化行表        dgvData.Columns(0).HeaderText = "党政人才类别统计表1"  '表头名        dgvData.Columns(1).HeaderText = "男"        dgvData.Columns(2).HeaderText = "女"        dgvData.Columns(3).HeaderText = "少数民族"        dgvData.Columns(4).HeaderText = "中共党员"        dgvData.Columns(5).HeaderText = "研究生"        dgvData.Columns(6).HeaderText = "大学本科"        dgvData.Columns(7).HeaderText = "大学专科"        dgvData.Columns(8).HeaderText = "中专"        dgvData.Columns(9).HeaderText = "高中"        dgvData.Columns(10).HeaderText = "初中"        dgvData.Columns(11).HeaderText = "小学"        dgvData.Columns(12).HeaderText = "35岁及以下"        dgvData.Columns(13).HeaderText = "36岁至40岁"        dgvData.Columns(14).HeaderText = "41岁至45岁"        dgvData.Columns(15).HeaderText = "46岁至50岁"        dgvData.Columns(16).HeaderText = "51岁至54岁"        dgvData.Columns(17).HeaderText = "55岁及以上"        '初始化列表        dgvData.Rows(0).Cells(0).Value = "中国共产党各级机关"        dgvData.Rows(1).Cells(0).Value = "各级人民代表大会及其常务委员会机关"        dgvData.Rows(2).Cells(0).Value = "各级行政机关"        dgvData.Rows(3).Cells(0).Value = "中国人民政治协商会议各级委员会机关"        dgvData.Rows(4).Cells(0).Value = "各级审判机关"        dgvData.Rows(5).Cells(0).Value = "各级检察机关"        dgvData.Rows(6).Cells(0).Value = "各民主党派和工商联的各级机关"        dgvData.Rows(7).Cells(0).Value = "参公管理人民团体和群众团体机关"        dgvData.Rows(8).Cells(0).Value = "参公管理的其它事业单位"        Call ShowInfo(listNo, rowsNo, lineNo, dgvData)    End Sub

显示数据模块

U层：循环嵌套遍历所有数据格，将单位格对应的行Id，列Id，以及部门Id、公司Id，表Id赋值给实体传递三层。

Imports BLLImports EntityModule ModuleTable    Public rowsNo As Integer    Public lineNo As Integer    Public listNo As Integer    Property intno As Integer = 0  '定义一个初始变量，用来判断是否已经录入    ''' <summary>    '''显示数据信息    ''' </summary>    ''' <remarks></remarks>    Public Sub ShowInfo(listNo As Integer, rowsNo As Integer, lineNo As Integer, dgvData As DataGridView)        Dim simpleQuerybll As New BLL.BLLInfoInpute        Dim table As DataTable        '定义部门ID、公司ID、表ID、行ID、列ID、UserID        Dim departmentInfo As New Entity.DepartMentEntity        Dim companyInfo As New Entity.ComPanyEntity        Dim listInfo As New Entity.ListEntity        Dim rowInfo As New Entity.RowEntity        Dim lineInfo As New Entity.LineEntity        departmentInfo.id = _companytypeid        companyInfo.id = _companyNameid        listInfo.id = listNo        For s = 1 To rowsNo - 1  '18            For h = 0 To lineNo - 1  '9                '获得行列名称                rowInfo.name = dgvData.Columns(s).HeaderText.ToString                lineInfo.name = dgvData.Rows(h).Cells(0).Value                '走三层传递                table = simpleQuerybll.ShowData(departmentInfo, companyInfo, listInfo, rowInfo, lineInfo)  '走三层                '数据填充                If table.Rows.Count = 0 Then                    dgvData.Rows(h).Cells(s).Value = 0                Else                    dgvData.Rows(h).Cells(s).Value = table.Rows(0).Item(0)                    'btnInput.Enabled = False                End If            Next        Next    End Sub

D层：在SQL语句中查询单位格所对应的data数据，公司行Id和列Id为进行跨表查询name得到。

    ''' <summary>    ''' 根据表单显示表的数据    ''' </summary>    ''' <param name="departmentInfo"></param>    ''' <param name="companyInfo"></param>    ''' <param name="listInfo"></param>    ''' <param name="rowInfo"></param>    ''' <param name="lineInfo"></param>    ''' <returns></returns>    ''' <remarks></remarks>    Public Function ShowData(ByVal departmentInfo As Entity.DepartMentEntity, ByVal companyInfo As Entity.ComPanyEntity, ByVal listInfo As Entity.ListEntity, ByVal rowInfo As Entity.RowEntity, ByVal lineInfo As Entity.LineEntity) As DataTable        Dim sql As String        Dim table As DataTable        Dim helper As New SqlHelper        '定义参数        Dim sqlparam As SqlParameter() = {New SqlParameter("@departmentId", departmentInfo.id),                                          New SqlParameter("@unitId", companyInfo.id),                                          New SqlParameter("@listId", listInfo.id),                                          New SqlParameter("@rowName", rowInfo.name),                                          New SqlParameter("@lineName", lineInfo.name)}        'Sql 查询语句        sql = "select data from T_dataTable where departmentId=@departmentId and unitId=@unitId and listId=@listId and rowId=(select id from T_rowTable where name=@rowName) and lineId=(select id from T_lineTable  where name=@lineName)"        '传入SQLHelper        table = helper.ExecSelect(sql, CommandType.Text, sqlparam)        Return table    End Function

录入模块

U层：循环嵌套遍历所有数据格，将单位格对应的行Id，列Id，以及部门Id、公司Id，表Id赋值给实体传递三层。

Private Sub btnInput_Click(sender As Object, e As EventArgs) Handles btnInput.Click        listNo = 1        Call ShowInsert(listNo, rowsNo, lineNo, dgvData)End Sub

    ''' <summary>    ''' 插入功能    ''' </summary>    ''' <remarks></remarks>    Public Sub ShowInsert(listNo As Integer, rowsNo As Integer, lineNo As Integer, dgvData As DataGridView)        Dim simpleQuerybll As New BLL.BLLInfoInpute        '定义部门ID、公司ID、表ID、行ID、列ID、UserID        Dim departmentInfo As New Entity.DepartMentEntity        Dim companyInfo As New Entity.ComPanyEntity        Dim listInfo As New Entity.ListEntity        Dim rowInfo As New Entity.RowEntity        Dim lineInfo As New Entity.LineEntity        Dim dataInfo As New Entity.DataEntity        If intno = 1 Then            MsgBox("该条数据已经录入，如需修改请更新！", , "提示")            Exit Sub        End If        '传递部门ID、公司ID、表ID、行ID、列ID、UserID        departmentInfo.id = _companytypeid        companyInfo.id = _companyNameid        listInfo.id = listNo        Dim s As Integer        Dim h As Integer        Dim i As Integer        For s = 1 To dgvData.Columns(s).HeaderText.Count - 1 's行号            For h = 0 To dgvData.Rows.Count - 1 '列号                rowInfo.name = dgvData.Columns(s).HeaderText.ToString                lineInfo.name = dgvData.Rows(h).Cells(0).Value                dataInfo.data = dgvData.Rows(h).Cells(s).Value                i = simpleQuerybll.InsertData(departmentInfo, companyInfo, listInfo, rowInfo, lineInfo, dataInfo)            Next        Next        MsgBox("数据录入成功！", , "提示")        intno = 1    End Sub

D层：在SQL语句中插入data数据到所对应的单位格，公司行Id和列Id为进行跨表查询name得到。

    ''' <summary>    ''' 根据表插入数据    ''' </summary>    ''' <param name="departmentInfo"></param>    ''' <param name="companyInfo"></param>    ''' <param name="listInfo"></param>    ''' <param name="rowInfo"></param>    ''' <param name="lineInfo"></param>    ''' <param name="dataInfo"></param>    ''' <returns></returns>    ''' <remarks></remarks>    Public Function InsertData(ByVal departmentInfo As Entity.DepartMentEntity, ByVal companyInfo As Entity.ComPanyEntity, ByVal listInfo As Entity.ListEntity, ByVal rowInfo As Entity.RowEntity, ByVal lineInfo As Entity.LineEntity, ByVal dataInfo As Entity.DataEntity) As Integer        Dim sql As String        Dim i, j As Integer        Dim helper As New SqlHelper        '定义参数集合        Dim sqlparam As SqlParameter() = {New SqlParameter("@departmentId", departmentInfo.id),                                          New SqlParameter("@unitId", companyInfo.id),                                          New SqlParameter("@listId", listInfo.id),                                          New SqlParameter("@rowName", rowInfo.name),                                          New SqlParameter("@lineName", lineInfo.name),                                          New SqlParameter("@data", dataInfo.data)}        'SQL语句插入T_dataTable表        sql = "insert into T_dataTable (departmentId,unitId,listId,rowId,lineId,data,isDelete) values (@departmentId,@unitId,@listId,(select id from T_rowTable where name=@rowName),(select id from T_lineTable where name=@lineName),@data,1)"        i = helper.ExecAddDelUpdate(sql, CommandType.Text, sqlparam)        Dim sqlparam1 As SqlParameter() = {New SqlParameter("@departmentId", departmentInfo.id),                                          New SqlParameter("@unitId", companyInfo.id),                                          New SqlParameter("@listId", listInfo.id)}        'SQL语句插入已经录入表T_alreadyinputTable        sql = "Insert into T_alreadyinputTable (departmentId,unitId,listId) values (@departmentId,@unitId,@listId)"        j = helper.ExecAddDelUpdate(sql, CommandType.Text, sqlparam1)        Return i    End Function

（这里面连个语句的连写最好使用存储过程）

更新功能同插入功能类似，只是D层稍有不同。

数据库结果显示

小结

这个功能重要的是理清思路，开始我们做的时候都很迷茫，也许有思路但是实现起来却是没有头脑，但是慢慢地去做，一点点理清，找到结果，当数据显示出来的那一刻很开心。团队合作是一个大的趋势，感谢小伙伴们的支持和帮助~~