这几天做.NET版的机房收费系统的时候遇到了一个级别查询的问题关于DAL层我不怎么会写昨晚问了一下正权我终于会了，下面就贴出代码来
UI层：
'用来级别查询
Private SubcboOperatorLevel_SelectedIndexChanged(ByVal sender As System.Object, ByVal e AsSystem.EventArgs) Handles cboOperatorLevel.SelectedIndexChanged
Dim EUser As New Entity.UserInfo'定义一个登录用户实体类
Dim CheckUser As NewBLL.BLLAddOrDeleteUser '定义一个BLL层的BLLLogin类
Dim dt As New DataTable '定义一个DataTable
EUser.OperatorLevel =Trim(cboOperatorLevel.Text) '把级别传到EUser实体类里面
dt = CheckUser.CheckUser(EUser)'调用AddUser的CheckUser函数
gvwViewUserInfo.DataSource = dt'把DataTable的值传给Gvw控件
End Sub
BLL层
‘级别查询
FunctionCheckUser(ByVal User As Entity.UserInfo) As DataTable
Dim DalUser As NewDAL.DALUserInfo'定义一个DalUser为DALUserInfo类
Dim EUser As NewEntity.UserInfo'定义一个实体类
Dim dt As NewDataTable '定义一个DataTable
EUser.OperatorLevel =User.OperatorLevel'把U层的OperatorLevel属性传给实体类EUser
dt =DalUser.SelectLevel(EUser)’调用D层的SelectLevel方法
Return dt
End Function
最关键的是D层，D层由于用不同的方法就可以用同的SQL语句
D层
第一种普通的查询：
'连接数据库
Dim con As String = " data source=.;initial catalog=Charge_System;user id =sa; password=123456"
Dim conn As SqlConnection = New SqlConnection(con)
Public Function SelectLevel(ByVal User As Entity.UserInfo) As System.Data.DataTable
Dim strSql As String = "select OperatorID as 用户名,OperatorNameas 用户姓名,OperatorLevel as 用户级别 fromUser_Info where OperatorLevel =@OperatorLevel"‘SQL语句
Dim cmd As SqlCommand = NewSqlCommand(strSql, conn)
cmd.Parameters.Add(NewSqlParameter("@OperatorLevel", User.OperatorLevel))’把User.OperatorLevel的值传给@OperatorLevel
Dim sqlda As New SqlDataAdapter‘定义一个SqlDataAdapter
Dim ds As New DataSet’定义一个 DataSet
Dim dt As New DataTable’定义一个DataTable
Try
conn.Open()‘打开数据库
‘把查到的内容填充到DataTabel中
sqlda.SelectCommand= cmd
sqlda.Fill(dt)
cmd.ExecuteReader()
Return dt
Catch ex As Exception
Return dt
End Try
End Function
第二种，利用SQL视图
首先在数据库中建立一个视图，如图
然后D层代码为：
Dim strSql As String = "select * from V_User_Info where 用户级别=@OperatorLevel"‘SQL语句
Dim cmd As SqlCommand = NewSqlCommand(strSql, conn)
cmd.Parameters.Add(NewSqlParameter("@OperatorLevel", User.OperatorLevel))’把User.OperatorLevel的值传给@OperatorLevel
Dim sqlda As New SqlDataAdapter‘定义一个SqlDataAdapter
Dim ds As New DataSet’定义一个 DataSet
Dim dt As New DataTable’定义一个DataTable
Try
conn.Open()‘打开数据库
‘把查到的内容填充到DataTabel中
sqlda.SelectCommand= cmd
sqlda.Fill(dt)
cmd.ExecuteReader()
Return dt
Catch ex As Exception
Return dt
End Try
End Function
第三种利用存储过程（正在研究中）