分层体系结构（MVC）二
二、EntityClass
EntityClass中的每一个文件对应数据库中的一张表，对应其字段，以及对表的操作的封装，如查询，增加、删除等。
所有的EntityClass继承了一个基类BaseEntity，基类的代码如下，主要是一些接口
Imports System.Data
Namespace EntityClass
Public Class BaseEntity
Public strSql As String
Public database As Data.DataFactory
Public dt As DataTable
'构造函数
Public Sub New()
Me.database = New Data.DataFactory
Me.dt = New DataTable
End Sub
'应该做成抽象的abstract
Public Function Load() As Integer
End Function
Public Function Creat() As Integer
End Function
Public Function Updata() As Integer
End Function
Public Function Delete() As Integer
End Function
Public Function GetDataOnTable() As DataTable
Me.dt = Me.database.DataTableResult(Me.strSql)
Return dt
End Function
Public Function GetOneObj() As Object
Return Me.database.ReadOneLog(Me.strSql)
End Function
Public Function ExecuteSql() As Integer
Return Me.database.ExecuteNonQuery(Me.strSql)
End Function
End Class
End Namespace
这里以对用户表的操作为例，来说明EntityClass的结构，代码如下：
Imports System.Data
Namespace EntityClass
Public Class S_User
Inherits BaseEntity
Const ARRAYLENGTH = 100
Public Id_I As String
' 用户名
Public UserName_S As String
'用户密码
Public Password_S As String
'add by gb 20050725
Dim array1(ARRAYLENGTH) As String
Dim array2(ARRAYLENGTH, 1) As String
Dim sqlarray(ARRAYLENGTH, 1) As String
Dim len1 As Integer = 0
Dim len2 As Integer = 0
Dim sqllen As Integer = 0
Private mydataset As New DataSet
'对Array2赋初值
Private Sub SetArray2()
array2(0, 0) = "Id_I"
array2(1, 0) = "UserName_S"
array2(2, 0) = "Password_S"
array2(0, 1) = Id_I
array2(1, 1) = UserName_S
array2(2, 1) = Password_S
End Sub
'通过读取数据库中的列名，对array1赋值
Private Sub SetDBColoum()
LoadDynamicDataset(mydataset)
Dim m_i As Integer = 0
Dim m_len As Integer = 0
'm_len = Me.mydataset.Tables.Item(0).Select.Length()
m_len = Me.mydataset.Tables.Item(0).Columns.Count
'For m_i = 0 To ARRAYLENGTH
' If Not Me.mydataset.Tables.Item(0).Columns(m_i) Is Nothing Then
' m_len = m_len + 1
' End If
'Next
'MsgBox("m_len" + m_len.ToString)
For m_i = 0 To m_len - 1
array1(m_i) = Me.mydataset.Tables.Item(0).Columns(m_i).ColumnName.ToString
Next
'array1(0) = Me.mydataset.Tables.Item(0).Columns(0).ColumnName.ToString
'array1(0) = "Id_I"
'array1(1) = "Password_S"
End Sub
'通过array1和array2的处理，对sqlarray赋值；最后的结果是sqlarray中，依次对应数据库中的列的值
Public Function SetDynamicColoum()
SetArray2()
SetDBColoum()
len1 = 0
len2 = 0
sqllen = 0
Dim i As Integer
Dim j As Integer = 0
For i = 0 To ARRAYLENGTH
If Not array1(i) Is Nothing Then
len1 = len1 + 1
Else
Exit For
End If
Next
'MsgBox("len1=" + len1.ToString)
For i = 0 To ARRAYLENGTH
If Not array2(i, 0) Is Nothing Then
len2 = len2 + 1
Else
Exit For
End If
Next
'MsgBox("len2=" + len2.ToString)
For i = 0 To len1 - 1
For j = 0 To len2 - 1
If array1(i) = array2(j, 0) Then
sqlarray(i, 0) = array2(j, 0)
sqlarray(i, 1) = array2(j, 1)
Exit For
End If
Next
Next
For i = 0 To ARRAYLENGTH
If Not sqlarray(i, 0) Is Nothing Then
sqllen = sqllen + 1
Else
Exit For
End If
Next
'For i = 0 To sqllen - 1
' MsgBox(sqlarray(i, 1))
'Next
'MsgBox(sqlarray(0))
'MsgBox(sqlarray(1))
End Function
'得到对应数据库中的表的信息
Public Sub LoadDynamicDataset(ByRef dataset As DataSet)
MyBase.strSql = "select * from S_User"
MyBase.database.SelectSqlSrvRows(dataset, MyBase.strSql)
End Sub
'add end
Public Function Count() As Integer
Return Convert.ToInt32(MyBase.database.ReadOneLog("select count(*) from S_User"))
End Function
Public Sub New()
' TODO: 在此处添加构造函数逻辑
Me.Init()
SetDynamicColoum()
End Sub
Public Function Init()
Me.Id_I = ""
Me.UserName_S = ""
Me.Password_S = ""
End Function
'根据id得到一条用户信息
Public Shadows Function Load() As Integer
MyBase.strSql = "select * from S_User where Id_I=" + Me.Id_I
Dim dt As DataTable = MyBase.database.DataTableResult(strSql)
If (dt.Rows.Count > 0) Then
If FindInSqlarray("UserName_S", sqlarray) > 0 Then
Me.UserName_S = dt.Rows(0)("UserName_S").ToString()
End If
If FindInSqlarray("Password_S", sqlarray) > 0 Then
Password_S = dt.Rows(0)("Password_S").ToString()
End If
'Me.UserName_S = dt.Rows(0)("UserName_S").ToString()
'Me.Password_S = dt.Rows(0)("Password_S").ToString()
Return dt.Rows.Count
Else
Return 0
End If
End Function
Public Function GetUser(ByVal UserName As String, ByVal Password As String) As Integer
'MsgBox(3)
MyBase.strSql = "select * from S_User where UserName_S='" + UserName + "' and Password_S='" + Password + "'"
Dim dt As DataTable = MyBase.database.DataTableResult(strSql)
If (dt.Rows.Count > 0) Then
Me.Id_I = dt.Rows(0)("ID_I").ToString()
Me.UserName_S = dt.Rows(0)("UserName_S").ToString()
Me.Password_S = dt.Rows(0)("Password_S").ToString()
Return dt.Rows.Count
Else
Return 0
End If
End Function
'添加用户
Public Shadows Function CreatUser() As Integer
Dim i As Integer
Dim col As String
Dim colval As String
For i = 1 To sqllen - 2
col = col + sqlarray(i, 0) + ","
colval = colval + sqlarray(i, 1) + "','"
Next
col = col + sqlarray(sqllen - 1, 0)
colval = colval + sqlarray(sqllen - 1, 1)
MyBase.strSql = "insert into S_User (" + col + ")" + "values ('" + colval + "')"
'MyBase.strSql = "insert into S_User (" + _
'"UserName_S,Password_S" + ")" + _
'"values ('" + UserName_S + "','" + Password_S + "')"
'MsgBox("sql2=" + strSql)
Return MyBase.database.ExecuteNonQuery(MyBase.strSql)
End Function
'更新用户
Public Shadows Function Updata() As Integer
Dim i As Integer
Dim str As String
For i = 1 To sqllen - 2
str = str + sqlarray(i, 0) + "='" + sqlarray(i, 1) + "',"
Next
str = str + sqlarray(sqllen - 1, 0) + "='" + sqlarray(sqllen - 1, 1) + "'"
MyBase.strSql = "update S_User set " + str + " where ID_I=" + Me.Id_I
MsgBox(strSql)
'MyBase.strSql = "update S_User set UserName_S='" + Me.UserName_S + "',Password_S='" + Me.Password_S + "' where ID_I=" + Me.Id_I
Return MyBase.database.ExecuteNonQuery(MyBase.strSql)
End Function
'删除用户
Public Shadows Function Delete() As Integer
MyBase.strSql = "delete from S_User where Id_I=" + Me.Id_I.ToString()
Return MyBase.database.ExecuteNonQuery(MyBase.strSql)
End Function
Public Sub loadusers(ByRef dataset As DataSet)
MyBase.strSql = "select * from S_User"
MyBase.database.SelectSqlSrvRows(dataset, MyBase.strSql)
End Sub
Public Function setEntity(ByRef myParameter As business.login.UserParameter) As Integer
Try
Me.Id_I = myParameter.get_ID
Me.UserName_S = myParameter.get_UserName
Me.Password_S = myParameter.get_Password
SetDynamicColoum()
Catch ex As Exception
Return 0
End Try
Return 1
End Function
Public Function FindInSqlarray(ByVal prop As String, ByVal sqlarray(,) As String) As Integer
Dim m As Integer = 0
For m = 0 To ARRAYLENGTH
If prop.Equals(sqlarray(m, 0)) Then
Return 1
End If
Next
Return 0
End Function
'根据sql语句查询
Public Function GetObjByQry(ByVal str As String, ByRef mp() As business.login.UserParameter) As Integer
MyBase.strSql = "select * from " + "S_User " + str
Dim dt As DataTable = MyBase.database.DataTableResult(strSql)
Dim m_i As Integer
ReDim mp(dt.Rows.Count - 1)
If (dt.Rows.Count > 0) Then
For m_i = 0 To dt.Rows.Count() - 1
mp(m_i) = New business.login.UserParameter
If FindInSqlarray("UserName_S", sqlarray) > 0 Then
mp(m_i).set_UserName(dt.Rows(m_i)("UserName_S").ToString())
End If
If FindInSqlarray("Password_S", sqlarray) > 0 Then
mp(m_i).set_Password(dt.Rows(0)("Password_S").ToString())
End If
Next
Else
Return 0
End If
End Function
End Class
End Namespace
可以看到，代码中定义了一系列的属性值，对应于用户表中的各个字段。还有对如添加用户，删除用户等各种操作的封装，调用了Data层中的方法，并且向以后将要涉及到的Business层提供了接口。所有的sql语句都是在这里拼写的。
