ASP.Net查询SQL Server + DataGrid 显示

ConnectSQL.aspx代码如下:

<%@ Page Language="VB" AutoEventWireup="false" CodeFile="ConnectSQL.aspx.vb" Inherits="ConnectSQL" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>Event Log Page</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
    <center><font size="6"><i>Event Log</i></font></center>
    <asp:Label ID="la1" runat="server"></asp:Label><br />

    <table id="Table2" width="100%" >
        <tr>
            <td>
                <asp:datagrid id="dgSQL" runat="server" Width="100%" PageSize="15" CellSpacing="2" CellPadding="2"
                                AutoGenerateColumns="False" AllowPaging="True">
                    <Columns>                                   
                        <asp:BoundColumn DataField="user_name" HeaderText="User Name">
                            <HeaderStyle Width="15%"></HeaderStyle>
                        </asp:BoundColumn>
                        <asp:BoundColumn DataField="action_type" HeaderText="Action Type">
                            <HeaderStyle Width="15%"></HeaderStyle>
                        </asp:BoundColumn>                                   
                    </Columns>
                    <PagerStyle PageButtonCount="5" Mode="NumericPages"></PagerStyle>
                </asp:datagrid>
            </td>
        </tr>
    </table>
    </div>
    </form>
</body>
</html>

        页面设计中用到了一个用来提示数据库连接是否成功的label、一个显示数据的DataGrid。

DataGrid中有两列,第一列用来显示用户名(User Name),另一列用来显示操作类型(Action Type)。前者绑定了数据库中user_name字段,后者绑定了action_type字段。

DataGrid的属性PageSize用来设置行数;

CellSpacing用来设置表格线宽;

CellPadding用来设置表格内容与分隔线的距离;

AutoGenerateColumns用来设置列的宽度是否根据数据内容可变;

AllowPaging用来设置是否允许翻页;

PagerStyle用来设置翻页时显示的页码个数、格式。

 

ConnectSQL.aspx.vb代码如下:

Imports System.Data
Imports System.Data.SqlClient

Partial Class ConnectSQL Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Data_Load()
    End Sub

    Protected Sub dgSQL_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles dgSQL.PageIndexChanged
        Me.dgSQL.CurrentPageIndex = e.NewPageIndex
        Data_Load()

    End Sub

    Private Sub Data_Load()
        Dim myConnection As SqlConnection
        Dim myCommand As SqlCommand
        Dim ds As New DataSet
        Dim tmpDA As SqlDataAdapter

        myConnection = New SqlConnection("Data Source=192.168.0.64;Initial Catalog=event;Persist Security Info=True;User ID=sa;Password=123456")
        myConnection.Open()
        la1.Text = "Connection Opened!"

        myCommand = New SqlCommand("select user_name,action_type from [event_log]", myConnection)
        tmpDA = New SqlDataAdapter(myCommand)
        tmpDA.Fill(ds, "TEMP")

        dgSQL.DataSource = ds.Tables("TEMP").DefaultView
        dgSQL.DataBind()

End Sub
End Class

显示查询结果的流程:连接—>查询—>填充DataSet—>DataGrid绑定数据
‘参考文章宋氏电脑 技术无忧:http://www.pc51.net/data/MS_SQL/2007-01-04/2621.html

运行结果如下图:

image

This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s