VB2010(26)SqlDataAdapter 类

SqlDataAdapter 类

https://docs.microsoft.com/zh-cn/dotnet/api/system.data.sqlclient.sqldataadapter?view=netframework-4.8

Imports System.Data
Imports System.Data.SqlClient
Public Class Form1
    Dim objConnection As New SqlConnection _
        ("Data Source=.\SQLEXPRESS;AttachDbFilename='K:\VB练习\Visual Basic 2010入门经典\029DatasetExample\029DatasetExample\PUBS.MDF';Integrated Security=True;Connect Timeout=30;User Instance=True")
    Dim objDataAdapter As New SqlDataAdapter
    Dim objDataSet As New DataSet

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        objDataAdapter.SelectCommand = New SqlCommand
        objDataAdapter.SelectCommand.Connection = objConnection
        objDataAdapter.SelectCommand.CommandText = _
            "select au_lname,au_fname,title,price " & _
            "from authors " & _
            "join titleauthor on authors.au_id=titleauthor.au_id " & _
            "join titles on titleauthor.title_id = titles.title_id " & _
            "order by au_lname,au_fname"
        objDataAdapter.SelectCommand.CommandType = CommandType.Text

        objConnection.Open()

        objDataAdapter.Fill(objDataSet, "authors")

        objConnection.Close()

        grdAuthorTitles.Dock = DockStyle.Fill
        '设置datagridview属性，1自成所有列，2数据源，3数据源中表名
        grdAuthorTitles.AutoGenerateColumns = True
        grdAuthorTitles.DataSource = objDataSet
        grdAuthorTitles.DataMember = "authors"
        
        Dim objAlignRightCellsStyle As New DataGridViewCellStyle
        objAlignRightCellsStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        '设置行风格
        Dim objAlternatingCellsStyle As New DataGridViewCellStyle
        objAlternatingCellsStyle.BackColor = Color.WhiteSmoke
        grdAuthorTitles.AlternatingRowsDefaultCellStyle = objAlternatingCellsStyle

        '设置单元格风格
        Dim objCurrentcyCellsStyle As New DataGridViewCellStyle
        objCurrentcyCellsStyle.Format = "c"
        objCurrentcyCellsStyle.Alignment = DataGridViewContentAlignment.MiddleRight

        '自定义列名
        grdAuthorTitles.Columns(0).HeaderText = "Last Name"
        grdAuthorTitles.Columns(1).HeaderText = "First Name"
        grdAuthorTitles.Columns(2).HeaderText = "Book Title"
        '自定义列宽
        'grdAuthorTitles.Columns(2).Width = 225
        grdAuthorTitles.Columns(2).AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        '自定义列名和风格
        grdAuthorTitles.Columns("price").HeaderCell.Value = "Retail Price"
        grdAuthorTitles.Columns("price").HeaderCell.Style = objAlignRightCellsStyle
        grdAuthorTitles.Columns("price").DefaultCellStyle = objCurrentcyCellsStyle

        '设置datagridview样式
        grdAuthorTitles.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill

        objDataAdapter = Nothing
        objConnection = Nothing
        objCurrentcyCellsStyle = Nothing
        objAlignRightCellsStyle = Nothing
        objAlternatingCellsStyle = Nothing
    End Sub
End Class