Imports System
Imports System.IO
Imports System.Web
Imports System.Web.UI.WebControls
Imports System.Web.UI.HtmlControls
Imports System.Data
Imports System.Data.SqlClient
Imports System.Xml
Imports System.Diagnostics
Imports Microsoft.VisualBasic
Imports System.Collections
namespace SQLDataProcess_vb
	public class UseSqlData_vb
		dim ConnectString as string'连线字串
		public Source as SqlConnection '使用于本次连接的Connection
		public Sub New(ServerName as string,uid as string,Password as string,DBName as string)
			ConnectString="server=" & ServerName & ";uid=" & uid & ";pwd=" & Password & ";database=" & DBName
			Source=new SqlConnection(ConnectString)
		end sub
		public Function GetDataTable(SQLString as string,TBName as string,Byref DT as DataSet) as DataTable
			try
				dim m as SqlDataAdapter=new SqlDataAdapter(SQLString,Source)
				m.Fill(DT,TBName)
				return DT.Tables(TBName)
			catch
				return nothing
			end try
		end function
		public Function GetDataTable(SQLString as string,TBName as string) as DataTable
			dim DT as DataSet= new DataSet()
			try
				dim m as SqlDataAdapter=new SqlDataAdapter(SQLString,Source)
				m.Fill(DT,TBName)
				return DT.Tables(TBName)
			catch
				return nothing
			end try
		end function
		public function GetDataRow(SQLString as string,TBName as string) as DataRow
			dim DT as DataSet= new DataSet()
			try
				dim m as SqlDataAdapter=new SqlDataAdapter(SQLString,Source)
				m.Fill(DT,TBName)
				if DT.Tables.Count>0 then
					return DT.Tables(TBName).Rows(0)
				else
					return nothing
				end if
			catch
				return nothing
			end try
		end function
		public Function GetRecordCount(SQLString as string) as integer
			dim DT as DataSet= new DataSet()
			try
				dim m as SqlDataAdapter=new SqlDataAdapter(SQLString,Source)
				m.Fill(DT,"test")
				return DT.Tables("test").Rows.Count
			catch
				return -1
			end try
		end function
		public Function GetAllFields(SQLString as string,TBName as string) as Hashtable
			dim DT as DataSet= new DataSet()
			try
				dim m as SqlDataAdapter=new SqlDataAdapter(SQLString,Source)
				m.Fill(DT,TBName)
				dim HT as Hashtable =new Hashtable()
				dim i as integer = 0
				for i=0 to DT.Tables(TBName).Columns.Count-1
					HT.Add(DT.Tables(TBName).Columns(i).ColumnName,DT.Tables(TBName).Columns(i).ColumnName)
				next
				return HT
			catch
				return nothing
			end try
		end function
		public Function ExecAction(SQLString as string) as Boolean
			try
				dim InCmdExec as SqlCommand = new SqlCommand(SQLString,Source)
				InCmdExec.Connection.Open()
				InCmdExec.ExecuteNonQuery()
				InCmdExec.Connection.Close()
				return true
			catch
				return false
			end try
		end function
		public function SaveToDatabase(t as DataTable,STBName as string) as Boolean
			try
				dim m as SqlDataAdapter=new SqlDataAdapter("Select * from " & STBName,Source)
				dim custCB as SqlCommandBuilder = new SqlCommandBuilder(m)
				m.Update(t)
				return true
			catch
				return false
			end try
		end function
		public function DSum(TBName as string,FDName as string,Filter as string) as string
			try
				dim n as string =""
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Sum(" & FDName & ") as fd From " & TBName & " where " & Filter
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetValue(0).ToString()
				loop
				sm.Connection.Close()
				return n
			catch
				return nothing
			end try
		end function
		public Function DSum(TBName as string,FDName as string) as string
			try
				dim n as string =""
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Sum(" & FDName & ") as fd From " & TBName
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetValue(0).ToString()
				loop
				sm.Connection.Close()
				return n
			catch
				return nothing
			end try
		end function
		public function Dlookup(TBName as string,FDName as string,Filter as string) as string
			Dim DS as DataSet = new DataSet()
			dim m as SqlDataAdapter=nothing
			try
				m=new SqlDataAdapter("select * from " & TBName & " where " & Filter,Source)
				m.Fill(DS,TBName)
				if DS.Tables(TBName).Rows.Count>0 then
					return DS.Tables(TBName).Rows(0)(FDName).ToString()
				else
					return ""
				end if
			catch
				return ""
			end try
		end function
		public function Dlookup(TBName as string,FDName as string) as string
			Dim DS as DataSet = new DataSet()
			dim m as SqlDataAdapter=nothing
			try
				m=new SqlDataAdapter("select * from " & TBName,Source)
				m.Fill(DS,TBName)
				if DS.Tables(TBName).Rows.Count>0 then
					return DS.Tables(TBName).Rows(0)(FDName).ToString()
				else
					return ""
				end if
			catch
				return ""
			end try
		end function
		public Function DlookupSQL(SQLString as string,FDName as string) as string
			Dim DS as DataSet = new DataSet()
			dim m as SqlDataAdapter=nothing
			try
				m=new SqlDataAdapter(SQLString,Source)
				m.Fill(DS,"test")
				if DS.Tables("test").Rows.Count>0 then
					return DS.Tables("test").Rows(0)(FDName).ToString()
				else
					return ""
				end if
			catch
				return ""
			end try
		end function
		public Function DCount(TBName as string,FDName as string,Filter as string) as integer
			try
				dim n as integer =0
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Count(" & FDName & ") as fd From " & TBName & " where " & Filter
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetInt32(0)
				loop
				sm.Connection.Close()
				return n
			catch
				return 0
			end try
		end function
		public function DCount(TBName as string,FDName as string) as integer
			try
				dim n as integer =0
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Count(" & FDName & ") as fd From " & TBName
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetInt32(0)
				loop
				sm.Connection.Close()
				return n
			catch
				return 0
			end try
		end function
		public Function DMax(TBName as string,FDName as string,Filter as string) as string 
			try
				dim n as string=""
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Max(" & FDName & ") as fd From " & TBName & " where " & Filter
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetValue(0).ToString()
				loop
				sm.Connection.Close()
				return n
			catch
				return ""
			end try
		end function
		public Function  DMax(TBName as string,FDName as string) as string
			try
				dim n as string=""
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Max(" & FDName & ") as fd From " & TBName
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetValue(0).ToString()
				loop
				sm.Connection.Close()
				return n
			catch
				return ""
			end try
		end function
		public function DMin(TBName as string,FDName as string,Filter as string) as string
			try
				dim n as string=""
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Min(" & FDName & ") as fd From " & TBName & " where " & Filter
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetValue(0).ToString()
				loop
				sm.Connection.Close()
				return n
			catch
				return ""
			end try
		end function
		public function DMin(TBName as string,FDName as string) as string
			try
				dim n as string=""
				dim sm as SqlCommand=new SqlCommand()
				sm.Connection=Source
				sm.CommandText="select Min(" & FDName & ") as fd From " & TBName
				sm.CommandType = CommandType.Text
				dim k as SqlDataReader
				sm.Connection.Open()
				k=sm.ExecuteReader()
				do until k.Read()=false
					n=k.GetValue(0).ToString()
				loop
				sm.Connection.Close()
				return n
			catch
				return ""
			end try
		end function
		public sub ControlBind(SQLString as string,TBName as string, Byref SessionName as object,SourceControl as object,TypeName as string, tag as Boolean)
			dim dv as DataView =nothing
			if tag=true then
				Dim DS as DataSet=new DataSet()
				dim m as SqlDataAdapter=new SqlDataAdapter(SQLString,Source)
				m.Fill(DS,TBName)
		    		dv = DS.Tables(TBName).DefaultView
		    		SessionName = dv
			else
				dv = Ctype(SessionName,DataView)
			end if
			if TypeName ="DataGrid" then
				Dim ctr as DataGrid = Ctype(SourceControl,DataGrid)
		    		ctr.DataSource = dv
	    			ctr.DataBind()
			elseif TypeName = "DataList" then
				Dim ctr as DataList = Ctype(SourceControl,DataList)
		    		ctr.DataSource = dv
    				ctr.DataBind()
	                elseif TypeName = "Repeater" then
				Dim ctr as Repeater = Ctype(SourceControl,Repeater)
		    		ctr.DataSource = dv
	    			ctr.DataBind()
			end if
	    	end sub
		public sub DropDownListBind(SQLString as string,TBName as string, Byref SessionName as object , Byref ddL as DropDownList ,tag as Boolean, DataValueField as string,DataTextField as string)
			dim dv as DataView =nothing
			if tag=true then
				Dim DS as DataSet =new DataSet()
				dim m as SqlDataAdapter=new SqlDataAdapter(SQLString,Source)
				m.Fill(DS,TBName)
		    		dv = DS.Tables(TBName).DefaultView
				SessionName=dv
			else
				dv = Ctype(SessionName,DataView)
			end if
			ddL.DataValueField = DataValueField
			ddL.DataTextField = DataTextField
			ddL.DataSource = dv
			ddL.DataBind()
		end sub
end class
end namespace