数据库开发028使用DataGridView控制外观显示方式

程序员文章站 2022-05-07 14:36:14

...

DataGridView 类

https://docs.microsoft.com/zh-cn/dotnet/api/system.windows.forms.datagridview?view=netcore-3.1#properties

属性

表 2
AllowUserToAddRows	获取或设置一个值，该值指示是否向用户显示添加行的选项。
AllowUserToDeleteRows	获取或设置一个值，该值指示是否允许用户从 DataGridView 中删除行。
AllowUserToOrderColumns	获取或设置一个值，该值指示是否允许通过手动对列重新定位。
AllowUserToResizeColumns	获取或设置一个值，该值指示用户是否可以调整列的大小。
AllowUserToResizeRows	获取或设置一个值，该值指示用户是否可以调整行的大小。
AlternatingRowsDefaultCellStyle	获取或设置应用于 DataGridView 的奇数行的默认单元格样式。
BindingContext	获取或设置控件的 BindingContext。 (继承自 Control)
ColumnHeadersHeight	获取或设置列标题行的高度（以像素为单位）。
ColumnHeadersHeightSizeMode	获取或设置一个值，该值指示是否可以调整列标题的高度，以及它是由用户调整还是根据标题的内容自动调整。
Columns	获取一个集合，它包含控件中的所有列。
CurrentCell	获取或设置当前处于活动状态的单元格。
CurrentRow	获取包含当前单元格的行。
DataBindings	为该控件获取数据绑定。 (继承自 Control)
DataSource	获取或设置 DataGridView 所显示数据的数据源。
DefaultCellStyle	在未设置其他单元格样式属性的情况下，获取或设置应用于 DataGridView 中的单元格的默认单元格样式。
EditMode	获取或设置一个值，该值指示如何开始编辑单元格。
RowCount	获取或设置 DataGridView 中显示的行数。
RowHeadersBorderStyle	获取或设置行标题单元格的边框样式。
RowHeadersDefaultCellStyle	获取或设置应用于行标题单元格的默认样式。
RowHeadersVisible	获取或设置一个值，该值指示是否显示包含行标题的列。
RowHeadersWidth	获取或设置包含行标题的列的宽度（以像素为单位）。
RowHeadersWidthSizeMode	获取或设置一个值，该值指示是否可以调整行标题的宽度，以及它是由用户调整还是根据标题的内容自动调整。
Rows	获取一个集合，该集合包含 DataGridView 控件中的所有行。
RowsDefaultCellStyle	获取或设置应用于 DataGridView 的行单元格的默认样式。
ScrollBars	获取或设置要在 DataGridView 控件中显示的滚动条的类型。
SelectedCells	获取用户选定的单元格的集合。
SelectedColumns	获取用户选定的列的集合。
SelectedRows	获取用户选定的行的集合。
SelectionMode	获取或设置一个值，该值指示如何选择 DataGridView 的单元格。
ShowRowErrors	获取或设置一个值，该值指示行标题是否为包含数据输入错误的每一行显示错误标志符号。
SortedColumn	获取 DataGridView 内容的当前排序所依据的列。
SortOrder	获取一个值，该值指示是按升序或降序对 DataGridView 控件中的项进行排序，还是不排序。

数据库开发028使用DataGridView控制外观显示方式

Imports System.Data.SqlClient

Public Class Form1
    Dim conn As SqlConnection
    Dim da As SqlDataAdapter
    Dim ds As DataSet           '必须加“NEW”关键字
    Dim dt As DataTable

    Private Function GetConnection() As SqlConnection
        Return New SqlConnection(My.Settings.SalesConnectionString)
    End Function

    Private Sub DisplayData1(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataReader对象

        datagridView1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        Dim comm As New SqlCommand(str, conn)
        Dim dr As SqlDataReader = comm.ExecuteReader
        Dim dt As New DataTable
        dt.Load(dr)
        conn.Close()

        DataGridView1.DataSource = dt
    End Sub

    Private Sub DisplayData2(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        DataGridView1.DataSource = Nothing

        conn = GetConnection()
        conn.Open()
        da = New SqlDataAdapter(str, conn)
        da.Fill(ds, "dt")

        conn.Close()

        DataGridView1.DataSource = ds.Tables("dt")
    End Sub

    Private Sub DisplayData3(ByVal str As String, ByVal datagridview1 As DataGridView) '用DataSet对象和Adapter的Fill方法
        DataGridView1.DataSource = Nothing

        conn = GetConnection()
        Dim comm As New SqlCommand(str, conn)
        da = New SqlDataAdapter()
        da.SelectCommand = comm
        conn.Open()

        ds = New DataSet
        da.Fill(ds, "grade")
        conn.Close()

        DataGridView1.DataSource = ds.Tables("grade")

    End Sub

    Private Sub DisplayData4()
        conn = GetConnection()
        conn.Open()

        da = New SqlDataAdapter("Select * from grade", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        BindingSource1.DataSource = customerTable
        DataGridView2.DataSource = BindingSource1

        DataGridView2.ColumnHeadersVisible = True
        DataGridView2.RowHeadersVisible = True

        conn.Close()
    End Sub

    Private Sub DataReaderData(ByVal str As String)
        conn = GetConnection()
        conn.Open()

        Dim comm As New SqlCommand(str, conn)
        Dim dr As SqlDataReader
        dr = comm.ExecuteReader
        Dim strOutput As String = ""
        Try
            While dr.Read
                strOutput += dr("学号").ToString.PadLeft(10) + Space(5)
                strOutput += dr("姓名").ToString.PadLeft(10) + Space(5)
                strOutput += dr("语文").ToString.PadLeft(10) + Space(5)
                strOutput += dr("数学").ToString.PadLeft(10) + Space(5)
                strOutput += dr("英语").ToString.PadLeft(10) + Space(5)
                strOutput += vbCrLf
            End While
        Catch ex As Exception
            MsgBox("出现异常")
        Finally
            dr.Close()
            conn.Close()
        End Try
        'Label2.Text = strOutput
    End Sub

    Private Sub UpdateData()
        conn = GetConnection()
        conn.Open()
        Dim comm As New SqlCommand
        Dim trans As SqlTransaction
        trans = conn.BeginTransaction
        comm.Connection = conn
        comm.Transaction = trans
        Try
            comm.CommandText = "Update grade set 数学=95 Where 姓名 like '%周%'"
            comm.ExecuteNonQuery()
            comm.CommandText = "Update grade set 数学=65 Where 姓名 like '%张%'"
            comm.ExecuteNonQuery()

            trans.Commit()
            'Label1.Text = "事务运行成功"
        Catch ex As Exception
            trans.Rollback()
            'Label1.Text = "有错"
        Finally
            conn.Close()
        End Try
    End Sub

    Private Sub InsertRecord1()
        'conn = GetConnection()
        'conn.Open()
        'Dim strSql As String = "Insert into grade(学号,姓名,语文,数学,英语) " & _
        '"Values('" & txtId.Text & "','" & txtName.Text & "','" & _
        'txtChinese.Text & "','" & txtMaths.Text & "','" & txtEnglish.Text & "')"
        'Dim comm As New SqlCommand(strSql, conn)
        'comm.ExecuteNonQuery()
        'conn.Close()

    End Sub

    Private Sub InserRecord2()
        conn = GetConnection()

        Dim strInsertSql As String = "Insert into grade(学号,姓名,数学) Values(27,'于谦',59)"
        Dim strUpdateSql As String = "Update grade Set 数学=60 where 学号=27"
        Dim strSelectSql As String = "Select * From grade where 学号='27'"

        Dim InsertComm As New SqlCommand(strInsertSql, conn)
        Dim UpdateComm As New SqlCommand(strUpdateSql, conn)
        Dim SelectComm As New SqlCommand(strSelectSql, conn)

        da = New SqlDataAdapter
        conn.Open()

        da.InsertCommand = InsertComm
        da.UpdateCommand = UpdateComm
        da.SelectCommand = SelectComm

        ds = New DataSet

        da.InsertCommand.ExecuteNonQuery()
        da.Fill(ds, "grade1")
        da.UpdateCommand.ExecuteNonQuery()
        da.Fill(ds, "grade2")
        da.Fill(ds, "grade3")

        '显示插入结果
        'DataGridView2.DataSource = ds.Tables("grade1")
        '显示更新结果
        'DataGridView3.DataSource = ds.Tables("grade2")
        '显示查询结果
        'DataGridView4.DataSource = ds.Tables("grade3")

        conn.Close()

    End Sub

    Private Sub RunningCreateDataSet(ByVal dgv As DataGridView)
        Dim myds As New DataSet
        Dim mydt As New DataTable("Squares")
        Dim mydr As DataRow
        Dim i As Integer
        mydt.Columns.Add(New DataColumn("数字", GetType(Integer)))
        mydt.Columns.Add(New DataColumn("平方", GetType(Integer)))
        For i = 0 To 10
            mydr = mydt.NewRow
            mydr(0) = i
            mydr(1) = i * i
            mydt.Rows.Add(mydr)
        Next
        myds.Tables.Add(mydt)
        dgv.DataSource = myds.Tables("Squares")

        myds.WriteXml("Squares.xml")
    End Sub

    Private Sub SortDataView(ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()
        da = New SqlDataAdapter
        ds = New DataSet
        Dim strSql As String = "Select * from grade"
        Dim comm As New SqlCommand(strSql, conn)
        da.SelectCommand = comm
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"))
        dv.RowFilter = "语文>60"
        dv.Sort = "学号 asc,语文 desc"

        Label1.Text = "满足条件的记录有：" + dv.Count.ToString + "条"

        dgv.DataSource = dv

        conn.Close()
    End Sub

    Private Sub SearchfromDataView()
        conn = GetConnection()
        conn.Open()
        Dim strSql As String = "Select * from Grade"
        Dim comm As New SqlCommand(strSql, conn)
        da = New SqlDataAdapter()
        da.SelectCommand = comm
        ds = New DataSet
        da.Fill(ds, "grade")

        Dim dv As DataView
        dv = New DataView(ds.Tables("grade"), "", "姓名", DataViewRowState.CurrentRows)
        'Dim rowIndex As Integer = dv.Find(TextBox1.Text)
        'If rowIndex = -1 Then
        'Label2.Text = "没找到"
        'Else
        'Label2.Text = "姓名=" & dv(rowIndex)("姓名").ToString & Space(5) & _
        '"语文=" & dv(rowIndex)("语文").ToString & Space(5) & _
        ' "数学=" & dv(rowIndex)("数学").ToString & Space(5) & _
        '"英语=" & dv(rowIndex)("英语").ToString

        'End If

        conn.Close()
    End Sub

    Private Sub myFilter(ByVal bds As BindingSource, ByVal dgv As DataGridView)
        conn = GetConnection()
        conn.Open()

        da = New SqlDataAdapter("Select * from grade order by 数学 asc", conn)
        Dim customerTable As New DataTable
        da.Fill(customerTable)
        bds.DataSource = customerTable
        dgv.DataSource = bds

        conn.Close()

    End Sub

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        DisplayData1("Select * from grade", DataGridView1)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DisplayData4()

        Dim i As Integer
        If DataGridView2.Rows.Count <> 0 Then
            For i = 0 To DataGridView2.Rows.Count - 1 Step 2
                DataGridView2.Rows(i).DefaultCellStyle.BackColor = Color.Pink
            Next
        End If
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        DisplayData4()
        DataGridView2.ColumnHeadersVisible = False
        DataGridView2.RowHeadersVisible = False
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        DisplayData4()
        DataGridView2.Columns(2).DisplayIndex = 1
        DataGridView2.Columns(1).DisplayIndex = 2
    End Sub

    Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
        DataGridView2.AllowUserToAddRows = False
    End Sub
End Class