一个整合SQL语句的类

类别:Asp 点击:0 评论:0 推荐:

我们在写ASP数据库程序的时候,通常都会用到SQL语句,而在增加数据和更新数据的时候,通常会使用一下方式:insert into message (incept,sender,title,content,sendtime,flag,issend) values ('"&incept(i)&"','"&membername&"','"&title&"','"&message&"',Now(),0,1)当字段比较多的时候,而且更新的表比较多的时候,修改起来会比较麻烦,而且查找错误也比较困难。使用这个SQL类后可以简化修改,而且查错也比较容易。通过类的AddField函数增加字段名和字段值,可轻松的将字段名和字段值插入SQL语句,然后返回该SQL语句。

下面让我们看看这个类的代码:

<%

class SQLString

       '************************************

       '变量定义

       '************************************

       'sTableName ---- 表名

       'iSQLType ----SQL语句类型:0-增加,1-更新,2-删除,3-查询

       'sWhere ---- 条件

       'sOrder ---- 排序方式

       'sSQL ----值

       Private sTableName,iSQLType,sWhere,sOrder,sSQL

      

       '************************************

       '类初始化/结束

       '************************************

      

       Private Sub Class_Initialize()

              sTableName=""

              iSQLType=0

              sWhere=""

              sOrder=""

              sSQL=""

       End Sub

 

       Private Sub Class_Terminate()

       End Sub

 

       '************************************

       '属性

       '************************************

       ‘设置表名的属性

       Public Property Let TableName(value)        

              sTableName=value

       End Property

 

    ‘设置条件

       Public Property Let Where(value)

              sWhere=value

       End Property

 

    ‘设置排序方式

       Public Property Let Order(value)

              sOrder=value

       End Property

      

    ‘设置查询语句的类型

       Public property Let SQLType(value)

              iSQLType=value

              select case iSQLType

              case 0

                     sSQL="insert into #0 (#1) values (#2)"

              case 1

                     sSQL="update #0 set #1=#2"

              case 2

                     sSQL="delete from #0 "

              case 3

                     sSQL="select #1 from #0 "                 

              end select

       End Property

      

       '************************************

       '函数

       '************************************

 

    '增加字段(字段名称,字段值)

       Public Sub AddField(sFieldName,sValue)

              select case iSQLType

              case 0

                     sSQL=replace(sSQL,"#1",sFieldName & ",#1")

                     sSQL=replace(sSQL,"#2","'" & sFieldName & "',#2")

              case 1

                     sSQL=replace(sSQL,"#1",sFieldName)

                     sSQL=replace(sSQL,"#2","'" & sFieldName & "',#1=#2")

              case 3

                     sSQL=replace(sSQL,"#1",sFieldName & ",#1")

              End Select

       End Sub

      

       '返回SQL语句

       Public Function ReturnSQL()

              sSQL=replace(sSQL,"#0",sTableName)

              select case iSQLType

              case 0

                     sSQL=replace(sSQL,",#1","")

                     sSQL=replace(sSQL,",#2","")

              case 1

                     sSQL=replace(sSQL,",#1=#2","")

              case 3

                     sSQL=replace(sSQL,",#1","")

              end Select

              if sWhere<>"" then

                     sSQL=sSQL & " where " & sWhere

              end if

              if sOrder<>"" then

                     sSQL=sSQL & " order by "  & sOrder

              end if

              ReturnSQL=sSQL

       End Function

      

       '清空语句

       Public Sub Clear()

              sTableName=""

              iSQLType=0

              sWhere=""

              sOrder=""

              sSQL=""

       End Sub

end class

%>

 

使用方法:

例句:insert into message (incept,sender,title,content,sendtime,flag,issend) values ('"&incept(i)&"','"&membername&"','"&title&"','"&message&"',Now(),0,1)

set a =new SQLString  ‘创建类对象

a.TableName=" message "  ‘设置表名为message

a.SQLType=0     ‘设置查询类型为增加记录

a.AddField " incept", incept(i)

a.AddField " sender ", membername

a.AddField " title ", membername

a.AddField " sender ", title

a.AddField " content ", message

a.AddField " sendtime ", sendtime()

a.AddField " flag", 0

a.AddField " issend ", 1

Response.Write a.ReturnSQl

set a=nothing

本文地址:http://com.8s8s.com/it/it7750.htm