ASP——为实现前台和数据库分离写的数据库操作函数

<%Public Sub CkeckSqlErr() '检查sql是否有错误 If Err.Number <> 0 Then ' 'ShowErr("参数错误") response.Write ("<font color=red>抱歉你在访问本页时出现一个错误以下是错误的详细信息。<br>") response.Write ("错误号:" & CStr(Hex(Err.Number)) & "<br>") response.Write ("错误对象:" & Err.Source & "<br>") response.Write ("有关错误的详细描述:" & Err.Description) ' Response.End() End IfEnd SubPublic Function SafeSql(ParaName) '--- 传入参数 --- '防sql注入 'ParaName:参数名称-字符型，惊梦 84881411 2004/09/13 Dim Paravalue Paravalue = LCase(Trim(ParaName)) Paravalue = Replace(Paravalue, "select", "") Paravalue = Replace(Paravalue, "insert", "") Paravalue = Replace(Paravalue, "updata", "") Paravalue = Replace(Paravalue, "addnew", "") Paravalue = Replace(Paravalue, "delete", "") Paravalue = Replace(Paravalue, "order", "") Paravalue = Replace(Paravalue, "and", "") Paravalue = Replace(Paravalue, "or", "") Paravalue = Replace(Paravalue, "exec", "") Paravalue = Replace(Paravalue, "--", "") Paravalue = Replace(Paravalue, "-", "") Paravalue = Replace(Paravalue, ";", "") Paravalue = Replace(Paravalue, "%", "") Paravalue = Replace(Paravalue, "<", "") Paravalue = Replace(Paravalue, ">", "") Paravalue = Replace(Paravalue, "(", "") Paravalue = Replace(Paravalue, ")", "") Paravalue = Replace(Paravalue, "window.open", "") Paravalue = Replace(Paravalue, "window.close", "") Paravalue = Replace(Paravalue, "while(1)", "") Paravalue = Replace(Paravalue, "script", "") Paravalue = Replace(Paravalue, "'", "") Paravalue = Replace(Paravalue, Chr(34), "") Paravalue = Replace(Paravalue, Chr(39), "") SafeSql = ParavalueEnd FunctionPublic Function ListValue(bh, str1)Dim strstr = str1'为了方便字符串列表性字段的处理，列表的格式必须采用如下格式“,1,2,3,4,14,” '参数：type： ' 0：给列表加上前导逗号，用于写入ong ' 1：去除字符串的前后“，”以便进行分割用于读取' if isnull(str) then' ListValue=Null If IsNull(str) Or str = "," Then ListValue = Null Else str = CStr(str) If bh = 0 Then If Left(str, 1) <> "," Then str = "," + str End If If Right(str, 1) <> "," Then str = str + "," End If End If If bh = 1 Then If Left(str, 1) = "," Then str = Right(str, Len(str) - 1) End If If Right(str, 1) = "," Then str = Left(str, Len(str) - 1) End If End If ListValue = str End IfEnd FunctionPublic Function Execute_SQL(conn, sql)' 用于进行复杂查询' 参数：str_sql：查询语句'Sql语法检查'必须以select 开始包含 /from/ ' Response.Write(sql+"<br />") Dim Rs_Execute_SQL Set Rs_Execute_SQL = CreateObject("ADODB.Recordset") Rs_Execute_SQL.ActiveConnection = conn Rs_Execute_SQL.Source = sql Rs_Execute_SQL.open 'Call CkeckSqlErr '检查错误 If Rs_Execute_SQL.EOF Or Rs_Execute_SQL.bof Then Execute_SQL = 0 Else Execute_SQL = Rs_Execute_SQL.GetRows() End If'次只能用于查询一般的字段，大字段不行 Rs_Execute_SQL.Close Set Rs_Execute_SQL = NothingEnd FunctionPublic Function GetRecords(conn, Table_Name, Str_Fields, Str_Filter) '参数：Table_Name：表名；Str_Fields：查询字段,str_sql：查询条件 '返回记录数组 Dim Tmp_Filter, Tmp_Field Dim Rs_GetRecords, GetRecords_sql' Str_Filter=SafeSql(Str_Filter) Tmp_Filter = "" '根据情况生成过滤条 Tmp_Field = "*" If (Table_Name = "") Then ''ShowErr ("参数错误") Exit Function End If If (Str_Fields <> "") Then Tmp_Field = Str_Fields End If If Str_Filter <> "" Then Tmp_Filter = " WHERE " & Str_Filter + " " End If GetRecords_sql = "select " & Tmp_Field & " from " & Table_Name & " " & Tmp_Filter ' Response.Write(GetRecords_sql & "<br />") Set Rs_GetRecords = CreateObject("ADODB.Recordset") Rs_GetRecords.ActiveConnection = conn Rs_GetRecords.Source = GetRecords_sql Rs_GetRecords.open 'Call CkeckSqlErr '检查错误 If Rs_GetRecords.EOF Or Rs_GetRecords.bof Then GetRecords = 0 Else GetRecords = Rs_GetRecords.GetRows() End If'次只能用于查询一般的字段，大字段不行 Rs_GetRecords.Close Set Rs_GetRecords = NothingEnd FunctionPublic Function GetRecordCount(conn, Table_Name, Str_Filter) '测试通过 '获取符合条件的记录数 '参数 '语句： DELETE [FROM] {table_name|view_name} [WHERE clause] Dim GetRecordCount_sql Tmp_Filter = "" '根据情况生成过滤条件 If (Table_Name = "") Then ''ShowErr ("参数错误") Exit Function End If If Str_Filter <> "" Then Tmp_Filter = " WHERE " & Str_Filter + " " End If GetRecordCount_sql = "SELECT COUNT(*) as num from " & Table_Name & " " & Tmp_Filter Set Rs_GetRecordCount = CreateObject("ADODB.Recordset") Rs_GetRecordCount.ActiveConnection = conn Rs_GetRecordCount.Source = GetRecordCount_sql Rs_GetRecordCount.CursorType = 2 Rs_GetRecordCount.CursorLocation = 3 Rs_GetRecordCount.LockType = 3' Response.Write(GetRecordCount_sql) Rs_GetRecordCount.open 'Call CkeckSqlErr '检查错误 GetRecordCount = Rs_GetRecordCount("num") Rs_GetRecordCount.Close Set Rs_GetRecordCount = NothingEnd Function'从数据库表中的第M条记录开始取N条记录，利用Top关键字：注意如果Select语句中既有top，又有order by，则是从排序好的结果集中选择：''SELECT * FROM ( SELECT Top N *FROM (SELECT Top (M + N - 1) * FROM 表名 Order by ID desc) t1 ) t2'Order by ID descPublic Sub UpdateReplace(conn, Table_Name, Str_Field, OldValue, NewValue, Str_Filter) Dim rs_replace, UpdateReplace_sql Dim Tmp If Str_Filter = "" Then UpdateReplace_sql = "select * from " & Table_Name Else UpdateReplace_sql = "select * from " & Table_Name & " where " & Str_Filter End If'' Response.Write(Str_Filter) Set rs_replace = CreateObject("ADODB.Recordset") rs_replace.open UpdateReplace_sql, conn, 1, 3 'Call CkeckSqlErr '检查错误 While Not rs_replace.EOF If Not IsBlank(rs_replace(Str_Field)) Then '如果字段不为空则替换更新 Tmp = Replace(rs_replace(Str_Field), OldValue, NewValue) If Tmp = "," Then Tmp = "" End If rs_replace(Str_Field) = Tmp rs_replace.Update End If rs_replace.movenext Wend rs_replace.Close Set rs_replace = NothingEnd SubPublic Function UpdateRecord(conn, Table_Name, Str_Fields, Str_Values, Str_Filter, IsAll)'用于更新数据'参数:'Table_Name:数据表名'Str_Fields:更新的字段列表, Str_Values:更新字段的新数据 此两参数的个数必须一致'Str_Filter:过滤条件'IsAll:是否全部更新 默认为False 过滤参数为空时,IsAll必须为True,防止数据被误更新 'UPDATE MyDatagrid SET first_column=’Updated!’ Second_column=’Updated!’ Third_column=’Updated!’ WHERE first_column=’Update Me1’ Dim Tmp_Filter, Tmp_Fields, Tmp_Values, tmp_set Dim UpdateRecord_sql Dim Tmp_num, Tmp_num0, i Tmp_Filter = "" '根据情况生成过滤条件 If (Str_Filter = "" And Not IsAll) Then 'ShowErr ("update参数错误1") Exit Function End If If (Table_Name = "" Or Str_Fields = "") Then 'ShowErr ("update参数错误") Exit Function End If If Str_Filter <> "" Then Tmp_Filter = " WHERE " & Str_Filter + " " End If Tmp_Fields = Split(Str_Fields, ",") '把字段名拆分为数组 Tmp_num = UBound(Tmp_Fields)' Response.Write("'" & Str_Fields & "'")' 字段名可以拆分再拆分值If Tmp_num > 0 Then Tmp_Values = Split(Str_Values, ",") '把字段名拆分为数组 Tmp_num0 = UBound(Tmp_Values) If (Tmp_num <> Tmp_num0) Then 'ShowErr ("参数错误3") Exit Function End If i = 0 For i = 0 To Tmp_num '数据类型的问题 '日期型：#01/01/1922#的形式（采用#标志头尾） '逻辑型：Ture，False 'if IsDate(Tmp_Values(i)) then ' ' end if Select Case UCase(Trim(Tmp_Values(i))) Case "TRUE" tmp_set = tmp_set + " " + Tmp_Fields(i) + "=1 " Case "FALSE" tmp_set = tmp_set + " " + Tmp_Fields(i) + "=0 " Case Else tmp_set = tmp_set + " " + Tmp_Fields(i) + "=" + Tmp_Values(i) + " " End Select If i < Tmp_num Then tmp_set = tmp_set + "," End If Next Else '单个字段的替换 Select Case UCase(Trim(Str_Values)) Case "TRUE" tmp_set = " " + Str_Fields + "=1 " Case "FALSE" tmp_set = " " + Str_Fields + "=0 " Case Else tmp_set = " " + Str_Fields + "=" + Str_Values + " " End Select End If UpdateRecord_sql = "update " & Table_Name & " set " & tmp_set & " " & Tmp_Filter ' & Str_Value ' Response.Write("<br />"+UpdateRecord_sql+"<br />") Dim cmd Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = UpdateRecord_sql cmd.CommandType = 1 cmd.CommandTimeout = 0 cmd.Prepared = True cmd.Execute 'Call CkeckSqlErr '检查错误End FunctionPublic Sub InsertRecord(conn, Table_Name, Str_Fields, Str_Value)'参数:Str_Fields:字段列表,多个字段名用逗号隔开',Str_Value:值列表,字符型必须加单引号如:Str_Value="'信息技术',true,##"'Table_Name="b_course"'Str_Fields="coursename"'Str_Value=""'Str_Filter=""Dim Tmp_Filter, cmdDim InsertRecord_sql Tmp_Filter = "" '根据情况生成过滤条件 If (Table_Name = "" Or Str_Fields = "" Or Str_Value = "") Then ''ShowErr ("参数错误") Exit Sub End If InsertRecord_sql = "Insert into " & Table_Name & " (" & Str_Fields & ") values (" & Str_Value & ")"' Response.Write(InsertRecord_sql & "<br>") Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = InsertRecord_sql cmd.CommandType = 1 cmd.CommandTimeout = 0 cmd.Prepared = True cmd.Execute 'Call CkeckSqlErr '检查错误End SubPublic Sub DeleteRecord(conn, Table_Name, Str_Filter) '参数:Str_Filter: 过滤条件必须填写，如果全部删除时使用“all” '语句： DELETE [FROM] {table_name|view_name} [WHERE clause] Dim DeleteRecord_sql, cmd If (Table_Name = "" Or Str_Filter = "") Then ''ShowErr ("参数错误") Exit Sub End If If Str_Filter <> "" Then If LCase(Str_Filter) = "all" Then DeleteRecord_sql = "Delete * from " & Table_Name Else DeleteRecord_sql = "Delete * from " & Table_Name & " " & " WHERE " & Str_Filter End If End If' Response.Write(DeleteRecord_sql+"<br>") If DeleteRecord_sql <> "" Then Set cmd = CreateObject("ADODB.Command") cmd.ActiveConnection = conn cmd.CommandText = DeleteRecord_sql cmd.CommandType = 1 cmd.CommandTimeout = 0 cmd.Prepared = True cmd.Execute 'Call CkeckSqlErr '检查错误 Else ''ShowErr ("参数错误") End IfEnd Sub'如果你需要拷贝整个表，你可以使用SELECT INTO 语句。例如，下面的语句创建了一个名为newtable的新表，该表包含表MyDatagrid的所有数据：''SELECT * INTO newtable FROM MyDatagrid'INSERT MyDatagrid (first_column,second_column)''SELECT another_first,another_second''FROM anothertable''WHERE another_first=’Copy Me!’''这个语句从anothertable拷贝记录到MyDatagrid.只有表anothertable中字段another_first的值为’Copy Me！’的记录才被拷贝。''当为一个表中的记录建立备份时，这种形式的INSERT 语句是非常有用的。在删除一个表中的记录之前，你可以先用这种方法把它们拷贝到另一个表中。'函数ID：20'函数作用：数据库事务处理'建立时间：2006-3-5 3:25'修改时间：'传人参数：'返回值：true or false'============================================================================================================================Public Function ConnManage(Conn_object) If Conn_object.Errors.Count <> 0 Then Conn_object.rollbacktrans Err.Clear ConnManage = False Else Conn_object.committrans ConnManage = True End IfEnd Function'------------------------------'----------字段数据及格式处理部分---'------------------------------'============================================================================================================================'分页读取数据库记录sql语句生成代码Public Function GetPageSQL(TableName, FieldName, OrderField, PageSize, CurrentPage, OrderType, strWhere)Dim strTemp, strSQL, strOrder '根据排序方式生成相关代码 If OrderType = 0 Then strTemp = ">(select max([" & OrderField & "])" strOrder = " order by [" & OrderField & "] asc" Else strTemp = "<(select min([" & OrderField & "])" strOrder = " order by [" & OrderField & "] desc" End If If CurrentPage = 0 Then CurrentPage = 1 End If If FieldName = "" Then FieldName = "*" End If '若是第1页则无须复杂的语句 If CurrentPage = 1 Then strTemp = "" If strWhere <> "" Then strTmp = " where " + strWhere End If strSQL = "select top " & PageSize & " " & FieldName & " from [" & TableName & "]" & strTmp & strOrder Else '若不是第1页，构造SQL语句 strSQL = "select top " & PageSize & " " & FieldName & " from [" & TableName & "] where [" & OrderField & "]" & strTemp & _ " from (select top " & (CurrentPage - 1) * PageSize & " [" & OrderField & "] from [" & TableName & "]" If strWhere <> "" Then strSQL = strSQL & " where " & strWhere End If strSQL = strSQL & strOrder & ") as tblTemp)" If strWhere <> "" Then strSQL = strSQL & " And " & strWhere End If strSQL = strSQL & strOrder End If GetPageSQL = strSQL '返回SQL语句End FunctionPublic Function getPageCount(Rec_Count, Rec_Page)'获取总页码 If Rec_Count Mod Rec_Page = 0 Then getPageCount = Int(Rec_Count / Rec_Page) Else getPageCount = Int(Rec_Count / Rec_Page) + 1 End If End FunctionPublic Function getCurrentPage(conn, sql, CurrentPage, Rec_Page)'Response.Write(sql)'获取当前页的记录集'Response.Write(sql) Set Rs_getCurrentPage = CreateObject("ADODB.Recordset") Rs_getCurrentPage.open sql, conn, 1, 3 'Call CkeckSqlErr '检查错误 If Rs_getCurrentPage.EOF Or Rs_getCurrentPage.bof Then getCurrentPage = 0 Else' Response.Write(CurrentPage) Rs_getCurrentPage.PageSize = Rec_Page Rs_getCurrentPage.AbsolutePage = CurrentPage getCurrentPage = Rs_getCurrentPage.GetRows() End If Rs_getCurrentPage.Close Set Rs_getCurrentPage = Nothing End FunctionFunction IsBlank(ByRef TempVar)'判断数据库字段是否为空'参数：值 IsBlank = False 'now check by variable type Select Case VarType(TempVar) Case 0, 1 'Empty & Null IsBlank = True Case 8 '字符 If Len(TempVar) = 0 Then IsBlank = True End If 'Object Case 9 '对象 tmpType = TypeName(TempVar) If (tmpType = "Nothing") Or (tmpType = "Empty") Then IsBlank = True End If 'Array Case 8192, 8204, 8209 '是否含有一个元素? If UBound(TempVar) = -1 Then IsBlank = True End If End SelectEnd Function %>