<p> 我们在写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语句。 </p><p> 下面让我们看看这个类的代码:</p><p> <code><%<br />class SQLString<br />'************************************<br />'变量定义<br />'************************************<br />'sTableName ---- 表名<br />'iSQLType ----SQL语句类型:0-增加,1-更新,2-删除,3-查询<br />'sWhere ---- 条件<br />'sOrder ---- 排序方式<br />'sSQL ----值<br />Private sTableName,iSQLType,sWhere,sOrder,sSQL<br />'************************************<br />'类初始化/结束<br />'************************************<br />Private Sub Class_Initialize()<br />
sTableName=""<br />
iSQLType=0<br />
sWhere=""<br />
sOrder=""<br />
sSQL=""<br />End Sub<br />Private Sub Class_Terminate()<br />End Sub<br />'************************************<br />'属性<br />'************************************<br />‘设置表名的属性<br />Public Property Let TableName(value)<br />sTableName=value<br />End Property<br />‘设置条件<br />Public Property Let Where(value)<br />sWhere=value<br />End Property<br />‘设置排序方式<br />Public Property Let Order(value)<br />sOrder=value<br />End Property<br />‘设置查询语句的类型<br />Public property Let SQLType(value)<br />iSQLType=value<br />
select case iSQLType<br />
case 0<br />
sSQL="insert into #0 (#1) values (#2)"<br />
case 1<br />
sSQL="update #0 set #1=#2"<br />
case 2<br />
sSQL="delete from #0 "<br />
case 3<br />
sSQL="select #1 from #0 "<br />
end select<br />End Property<br />'************************************<br />'函数<br />'************************************<br />'增加字段(字段名称,字段值)<br />Public Sub AddField(sFieldName,sValue)<br />
select case iSQLType<br />
case 0<br />
sSQL=replace(sSQL,"#1",sFieldName & ",#1")<br />
sSQL=replace(sSQL,"#2","'" & sFieldName & "',#2")<br />
case 1<br />
sSQL=replace(sSQL,"#1",sFieldName)<br />
sSQL=replace(sSQL,"#2","'" & sFieldName & "',#1=#2")<br />
case 3<br />
sSQL=replace(sSQL,"#1",sFieldName & ",#1")<br />
End Select<br />End Sub<br />'返回SQL语句<br />Public Function ReturnSQL()<br />
sSQL=replace(sSQL,"#0",sTableName)<br />
select case iSQLType<br />
case 0<br />
sSQL=replace(sSQL,",#1","")<br />
sSQL=replace(sSQL,",#2","")<br />
case 1<br />
sSQL=replace(sSQL,",#1=#2","")<br />
case 3<br />
sSQL=replace(sSQL,",#1","")<br />
end Select<br />
if sWhere<>"" then<br />
sSQL=sSQL & " where " & sWhere<br />
end if<br />
if sOrder<>"" then<br />
sSQL=sSQL & " order by " & sOrder<br />
end if<br />
ReturnSQL=sSQL<br />End Function<br />'清空语句<br />Public Sub Clear()<br />
sTableName=""<br />
iSQLType=0<br />
sWhere=""<br />
sOrder=""<br />
sSQL=""<br />End Sub<br />end class<br />%></code></p>
<p> </p>
<p> 使用方法:</p><p> 例句:insert into message (incept,sender,title,content,sendtime,flag,issend) values ('"&incept(i)&"','"&membername&"','"&title&"','"&message&"',Now(),0,1)</p><p> <code>set a =new SQLString ‘创建类对象<br />a.TableName=" message " ‘设置表名为message<br />a.SQLType=0 ‘设置查询类型为增加记录<br />a.AddField " incept", incept(i)<br />a.AddField " sender ", membername<br />a.AddField " title ", membername<br />a.AddField " sender ", title<br />a.AddField " content ", message<br />a.AddField " sendtime ", sendtime()<br />a.AddField " flag", 0<br />a.AddField " issend ", 1<br />Response.Write a.ReturnSQl<br />set a=nothing</code></p></p>