Imports System
Imports System.Web
Imports System.Web.UI
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.UI.WebControls
Imports System.Diagnostics
Imports Microsoft.VisualBasic
namespace DissData_vb
	public class DissSql:Inherits Control : Implements INamingContainer
	dim ConnectString as string'连接字符串
		public sub New(ServerName as string,UID as string,Password as string, DBName as string)
			ConnectString="server=" & ServerName & ";uid=" & UID & ";pwd=" & Password & ";database=" & DBName
		end sub
		public function GetDataTable(cate as integer,StartDate as string,EndDate as string) as DataTable
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			Dim Comm as SqlDataAdapter = new SqlDataAdapter("select * from Article where (cate = " & cate & ") and (nowtime between CONVERT(DATETIME, '" & StartDate & " 00:00:00', 102) AND CONVERT(DATETIME, '" & EndDate & " 23:59:59', 102)) and (reply = 0) Order by ID desc",Source)
			Comm.Fill(DS,"Article")
			Comm.Fill(DS,"Article1")
			DS.Tables("Article1").Rows.Clear()
			Dim Row As DataRow
			for each Row in DS.Tables("Article").Rows
				dim Comm1 as SqlDataAdapter = new SqlDataAdapter("select * from Article where reply =" & Row("id") & " Order by ID desc",Source)
				dim p as integer=0
				for p=0 to DS.Tables.Count-1
					if DS.Tables(p).TableName="SubArticle" then
						DS.Tables(p).Rows.Clear()
						exit for
					end if
				next
				Comm1.Fill(DS,"SubArticle")
				dim po as DataRow = DS.Tables("Article1").NewRow()
				dim i as integer
				for i = 0 to DS.Tables("Article").Columns.Count-1
					po(i)=Row(i).ToString()
				next
				DS.Tables("Article1").Rows.Add(po)
				if DS.Tables("SubArticle").Rows.Count > 0 then
					dim ko as DataRow
					for each ko in DS.Tables("SubArticle").Rows
						Dim po1 as DataRow = DS.Tables("Article1").NewRow()
						dim u as integer=0
						for u=0 to DS.Tables("Article").Columns.Count-1
							if DS.Tables("Article").Columns(u).ColumnName="subject" then
								dim LK as string = "　" & ko(u).ToString()
								po1(u)=LK
							else
								po1(u)=ko(u).ToString()
							end if
						next
						DS.Tables("Article1").Rows.Add(po1)
					next
				end if
			next
			return DS.Tables("Article1")
		end function
		public Function GetAllCate() as DataTable
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			Dim Comm as SqlDataAdapter = new SqlDataAdapter("select * from cate Order by ID",Source)
			Comm.Fill(DS,"category")
			return DS.Tables("category")
		end function
		public Function SaveToArticle(NowID as integer,Reply as integer,Subj as string,Cname as string,Body as string,cate as integer,uid as string) as Boolean
		try
			Body=Body.Replace(Chr(10),"<br>")
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			Dim Comm as SqlDataAdapter = new SqlDataAdapter("select * from Article",Source)
			Comm.Fill(DS,"Article")
			Dim Row as DataRow
			Row=DS.Tables("Article").NewRow()
			Row("id")=NowID
			Row("reply")=Reply
			Row("cate")=cate
			Row("subject")=Subj
			Row("send")=Cname
			Row("body")=Body
			Row("UserID")=uid
			Row("nowtime")=DateTime.Now
			DS.Tables("Article").Rows.Add(Row)
			Dim custCB as SqlCommandBuilder = new SqlCommandBuilder(Comm)
			Comm.Update(DS,"Article")
			Dim Comm1 as SqlDataAdapter = new SqlDataAdapter("select * from Cate where id=" + cate,Source)
			Comm1.Fill(DS,"Cate")
			DS.Tables("Cate").Rows(0)("nowtime")=DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
			dim v as integer=Convert.ToInt32(DS.Tables("Cate").Rows(0)("total").ToString())
			DS.Tables("Cate").Rows(0)("total")=v+1
			Dim custCB1 as SqlCommandBuilder = new SqlCommandBuilder(Comm1)
			Comm1.Update(DS,"Cate")
			return true
		catch
			return false
		end try
		end function
		public function GetArticleRow(CID as integer) as DataRow
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			Dim Comm as SqlDataAdapter = new SqlDataAdapter("select * from Article where id =" & CID,Source)
			Comm.Fill(DS,"ArticleRow")
			return DS.Tables("ArticleRow").Rows(0)
		end function
		public function GetReplyID(Replyed as string) as integer
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			Dim Comm as SqlDataAdapter = new SqlDataAdapter("select * from Article where id =" & Convert.ToInt32(Replyed),Source)
			Comm.Fill(DS,"FindReply")
			dim k as string =""
			if DS.Tables("FindReply").Rows(0)("reply").ToString()="0" then
				k=DS.Tables("FindReply").Rows(0)("id").ToString()
			else
				k = DS.Tables("FindReply").Rows(0)("reply").ToString()
			end if
			return Convert.ToInt32(k)
		end function
		public Function ValiUser(IuputID as string,Pass as string) as string
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			Dim Comm as SqlDataAdapter = new SqlDataAdapter("select * from Disuser where id Like '" & IuputID & "'",Source)
			Comm.Fill(DS,"Users")
			if DS.Tables("Users").Rows.Count > 0 then
				if DS.Tables("Users").Rows(0)("pass").ToString()=Pass then
					return DS.Tables("Users").Rows(0)("name").ToString()
				else
					return ""
				end if
			else
				return ""
			end if
		end function
		public Function FullSearch(FindWhat as string,FindCate as string) as DataTable 
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			dim Comm as SqlDataAdapter
			if FindCate="全文" then
				Comm = new SqlDataAdapter("select * from Article where body Like '%" & FindWhat & "%'",Source)
			elseif FindCate="主题" then
				Comm = new SqlDataAdapter("select * from Article where subject Like '%" & FindWhat & "%'",Source)
			end if
			Comm.Fill(DS,"SearchRes")
			return DS.Tables("SearchRes")
		end function
		public Function GetMaxID() as integer
			Dim DS as DataSet = new DataSet()
			Dim Source as SqlConnection = new SqlConnection(ConnectString)
			Dim Comm as SqlDataAdapter = new SqlDataAdapter("SELECT Max(Article.id) AS idMax FROM Article",Source)
			Comm.Fill(DS,"MaxID")
			dim k as string= DS.Tables("MaxID").Rows(0)("idMax").ToString()
			return Convert.ToInt32(k) + 1
		end function
	end class
end namespace