sqldb.vb

ASP_NET程序设计教程 源代码 冶金工业出版社 肖金秀 冯沃辉 陈少涌

Imports System
Imports System.Configuration
Imports System.Data
Imports System.Data.OleDb
Imports Microsoft.VisualBasic.Strings
Imports Microsoft.VisualBasic
Imports System.Math

Namespace Packaging

	' SQLDB 用来处理数据库的处理
	' 数据库的连接串请放到web.config中,名字是ConnectionString
	' strSQL 为SQL串（注意要求已经过滤）
	' ExecuteSQL() 方法适用于 SQL 语句是 UPDATE, INSERT, DELETE 的命令
	'	执行strSQL串，无返回值
	' SelectFromDB 适用于 SQL 语句是 SELECT 的查询
	'	从数据库读出查询数据，以DataSet形式返回
	'	该DataSet的DataTable名字是"My"，引用时注意。
	Public Class SQLDB
		Public strSQL As String
		
		Public Sub ExecuteSQL()
			Dim MyConnection As OleDbConnection
			Dim ConnStr As String
			ConnStr=ConfigurationSettings.AppSettings("ConnectionString")
			MyConnection = New OleDbConnection(ConnStr)

			Dim MyCommand As OleDbCommand
			MyConnection.Open()
			MyCommand = New OleDbCommand(strSQL, MyConnection)
			MyCommand.ExecuteNonQuery()
			MyConnection.Close()
		End Sub

		Public Function SelectFromDB() As DataSet
			Dim MyConnection As OleDbConnection
			Dim ConnStr As String
			ConnStr=ConfigurationSettings.AppSettings("ConnectionString")
			MyConnection = New OleDbConnection(ConnStr)

			Dim MyCommand As OleDbDataAdapter
			MyCommand = New OleDbDataAdapter(strSQL, MyConnection)
			Dim DS As DataSet
			DS=New DataSet()
			MyCommand.Fill(DS, "My")

			Return DS
		End Function
        
	End Class
	

	' PageDS 用来处理分页
	' MyDS 属性是分页的数据源
	' PageSize 属性是每页的页码
	' PageSelect方法用来得到第CurPage页的数据，返回DataView可以直接绑定
	' GetTotalPage方法用来得到总页数
	Public Class PageDS
		Public DataSource As DataSet
		Public PageSize As Integer
		Public TotalPage As Integer

		Public Function PageSelect(ByVal CurPage As Integer) As DataView
			Dim TotalItem As Integer=DataSource.Tables("My").Rows.Count

			TotalPage=TotalItem\PageSize+1
			If TotalItem Mod PageSize=0 Then
				TotalPage=TotalPage-1
			End If

			Dim i,j As Integer
			Dim StartItem As Integer= (CurPage-1) * PageSize + 1
			Dim EndItem As Integer= CurPage * PageSize

			Dim DV As DataView=New DataView()

			If EndItem>TotalItem Then
				EndItem=TotalItem
			End If
			
			Dim DT As DataTable=New DataTable("My")

			Dim keyDC As DataColumn
			Dim DR As DataRow
			
			For Each keyDC In DataSource.Tables("My").Columns
				DT.Columns.Add(keyDC.ColumnName, keyDC.DataType)
			Next
			
			For i=StartItem To EndItem
				DR=DT.NewRow()
				For j=0 To DT.Columns.Count-1
					DR(j)=DataSource.Tables("My").Rows(i-1)(j)
				Next
				DT.Rows.Add(DR)
			Next
			DV.Table=DT
			DT=Nothing
			Return DV
		End Function
		
	End Class

	Public Class StringFilter
		Public filterDegree As Integer

		Public Function Change4Saving(ByVal originalString As String) As String
			Dim resultString As String=OriginalString
			resultString=Replace(resultString, "'", "''")
			Return resultString
		End Function

		Public Function Change4Showing(ByVal originalString As String) As String
			Dim resultString As String=OriginalString

			Select Case filterDegree
				Case 0:
					' HTML模式，不做改变
				Case 1:
					' UBB+IMG+FLASH+HTML标签模式，
					resultString=rHtml(resultString)
					resultString=UBB(resultString)
					resultString=replace(resultString,"[img]","<table border=0 cellspacing=0 cellspadding=0 width=100%><tr><td align=center><img src='",1,-1,CompareMethod.Text)
					resultString=replace(resultString,"[/img]","' border=0></td></tr></table>",1,-1,CompareMethod.Text)
					resultString=replace(resultString,"[flash]","<embed src='",1,-1,CompareMethod.Text)
					resultString=replace(resultString,"[/flash]","' quality=high pluginspage='http://www.macromedia.com/shockwave/download/index.cgi?P1_Prod_Version=ShockwaveFlash' type='application/x-shockwave-flash'></embed>",1,-1,CompareMethod.Text)
					resultString=uHtml(resultString)
					resultString=replace(resultString,"\]","]")
				Case 2:
					' UBB模式，作UBB变化
					resultString=gl(resultString)
					resultString=UBB(resultString)
					resultString=replace(resultString,"\]","]")
				Case 3:
					' 全部关闭时
					resultString=gl(resultString)
					resultString=cHTML(resultString)
			End Select

			Return resultString
		End Function

		Private Function gl(ByVal OriginalString As String) As String
			Dim resultString As String=OriginalString
			resultString=replace(resultString,"法轮功","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"Fa Lun Gong","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"誣蔑","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"法輪","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"大法","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"轉法輪","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"修煉","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"真善忍","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"江澤民","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"自殺","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"自殘","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"自焚","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"肏","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"做爱","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"法轮","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"口交","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"射精","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"阴道","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"阴茎","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"台独","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"鸡巴","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"你妈","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"达赖","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"民运","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"李宏志","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"李洪志","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"六四","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"中华民国","",1,-1,CompareMethod.Text)
			Return resultString
		End Function

		Private Function uHTML(ByVal OriginalString As String) As String
			Dim resultString As String=OriginalString
			resultString=replace(resultString,"pb_&lt;","<")
			resultString=replace(resultString,"pb_&gt;",">")
			resultString=replace(resultString,"pb_&quot;",chr(34))
			resultString=replace(resultString,"pb_chr10",chr(10))
			resultString=replace(resultString,"pb_chr32",chr(32))
			resultString=replace(resultString,"[html]","",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/html]","",1,-1,CompareMethod.Text)
			Return resultString
		End Function

		Private Function rHtml(ByVal OriginalString As String) As String
			Dim resultString As String=OriginalString
			Dim startp As Integer=1
			Dim Endp As Integer
			Dim innertext As String
			Do
				startp=Instr(startp,resultString,"[html]",1)
				If startp=0 Then Exit Do
				endp=instr(startp,resultString,"[/html]",1)
				If endp=0 Then Exit Do
				startp=startp+6
				innertext=Mid(resultString,startp,endp-startp)
				Dim innertext2 As String=innertext
				innertext2=Replace(innertext2,"<","pb_&lt;")
				innertext2=Replace(innertext2,">","pb_&gt;")
				innertext2=Replace(innertext2,chr(34),"pb_&quot;")
				innertext2=Replace(innertext2,chr(10),"pb_chr10")
				innertext2=Replace(innertext2,chr(32),"pb_chr32")
				resultString=replace(resultString, innertext, innertext2)
			Loop
			Return resultString
		End Function

		Private Function UBB(ByVal OriginalString As String) As String
			Dim resultString As String=OriginalString
			resultString=cHTML(resultString)
			resultString=replace(resultString,"[b]","<b>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/b]","</b>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[i]","<i>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/i]","</i>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[u]","<u>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/u]","</u>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[blue]","<font color='#2F74B3'>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/blue]","</font>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[red]","<font color='red'>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/red]","</font>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[green]","<font color='green'>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/green]","</font>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[quote]","<br><CENTER>—— 以下是引用 ——<TABLE cellSpacing=0 cellPadding=10 width=80% border=1><TR><TD>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/quote]","</TD></TR></TBODY></TABLE></CENTER><BR>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[code]","<CENTER><TABLE cellSpacing=0 cellPadding=10 width=80% border=1 bgcolor=#F0FFD0><TR><TD>",1,-1,CompareMethod.Text)
			resultString=replace(resultString,"[/code]","</TD></TR></TBODY></TABLE></CENTER><BR>",1,-1,CompareMethod.Text)
			resultString=rConvert(resultString,"url")
			resultString=rConvert(resultString,"email")
			resultString=rConvert(resultString,"color")
			resultString=rConvert(resultString,"face")

			Return resultString
		End Function

		Private Function cHTML(ByVal OriginalString As String) As String
			Dim resultString As String=OriginalString
			resultString=Replace(resultString,"<","&lt;")
			resultString=Replace(resultString,">","&gt;")
			resultString=Replace(resultString,chr(34),"&quot;")
			resultString=Replace(resultString,chr(10),"<br>")
			resultString=Replace(resultString,chr(32),"&nbsp;")
			Return resultString
		End Function

		Private Function rConvert(OriginalString, labelString) As String
			Dim resultString As String=OriginalString
			Dim startp As Integer=1
			Dim Endp As Integer
			Dim innertext As String
			Do while labelString="url" Or labelString="email" Or labelString="color" Or labelString="face"
				startp=Instr(startp,resultString,"[" + labelString + "=",1)
				If startp=0 Then Exit Do
				endp=instr(startp,resultString,"]",1)
				If endp=0 Then Exit Do
				startp=startp+Len(LabelString)+2
				innertext=Mid(resultString,startp,endp-startp)
				innertext=replace(innertext,"script:","")
				innertext=replace(innertext,"script>","")
				Select Case labelString
					Case "url"
						resultString=replace(resultString,"[url=" + innertext + "]","<a href=" + innertext + " target=_blank>",1,1,CompareMethod.Text)
						resultString=replace(resultString,"[/url]","</a>",1,1,CompareMethod.Text)
					Case "email"
						resultString=replace(resultString,"[email=" + innertext + "]","<a href=mailto:" + innertext + ">",1,1,CompareMethod.Text)
						resultString=replace(resultString,"[/email]","</a>",1,1,CompareMethod.Text)
					Case "color"
						resultString=replace(resultString,"[color=" + innertext + "]","<font color='" + innertext + "'>",1,1,CompareMethod.Text)
						resultString=replace(resultString,"[/color]","</font>",1,1,CompareMethod.Text)
					Case "face"
						resultString=replace(resultString,"[face=" + innertext + "]","<font face='" + innertext + "'>",1,1,CompareMethod.Text)
						resultString=replace(resultString,"[/color]","</font>",1,1,CompareMethod.Text)
				End Select
			Loop

			startp=1
			Do
				startp=Instr(startp,resultString,"[" + labelString + "]",1)
				If startp=0 Then Exit Do
				endp=Instr(startp,resultString,"[/" + labelString + "]",1)
				If endp=0 Then Exit Do
				startp=startp+Len(labelString)+2
				innertext=Mid(resultString,startp,endp-startp)
				innertext=replace(innertext,"script:","")
				innertext=replace(innertext,"script>","")
				Select Case labelString
					Case "url"
						resultString=replace(resultString,"[url]" + innertext,"<a href=" + innertext + " target=_blank>" + innertext,1,1,CompareMethod.Text)
						resultString=replace(resultString,"[/" + labelString + "]","</a>",1,1,CompareMethod.Text)
					Case "email"
						resultString=replace(resultString,"[email]" + innertext,"<a href=mailto:" + innertext + ">" + innertext,1,1,CompareMethod.Text)
						resultString=replace(resultString,"[/email]","</a>",1,1,CompareMethod.Text)
				End Select
			Loop

			Return ResultString
		End Function

	End Class
    
End Namespace