«
ASP整合的一个SQL语句类

时间:2008-5-31    作者:Deri    分类: 分享


   <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>