mydb

Imports System.Data
Imports System.Data.OleDb
Imports System.Web
Imports System.Web.UI.Page
Imports System.Web.HttpContext
Imports System.Web.Mail

Public Class mydb
    '过程说明:
    'GETTABLE 输入一个SQL得到一个TABLE
    'record_del 删除记录号,可重载,输入记录号,表名,无返回
    'SQLExecuteNonQuery 执行SQL,可重载,输入SQL,库名,无返回

    '在WEB.CONFGI中的配置如下,加在<configuration>下面
    '<appSettings>
    '<add key="mydatabasepath" value="~/data/database.mdb"></add>
    '<add key="MYdatabaseconn" value="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="></add>
    '</appSettings>

    '作用:打开一个DATASET表
    '参数:传一个SQL语句
    'Dim myconnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("data/database.mdb"))
    'Dim mysql As String = "select * from articles"
    'Dim myconnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("data/database.mdb"))

    '***********************************************************
    'find a record,return Boolean value
    '本过程依敕GETTABLE过程
    '***********************************************************
    Public Overloads Shared Function findrecord(ByVal mysql As String) As Boolean
        Dim mytable As DataTable = gettable(mysql)
        If mytable.Rows.Count = 0 Then
            Return False
        Else
            Return True
        End If
    End Function
    '***********************************************************
    'GET TABLE
    '***********************************************************
    Public Overloads Shared Function gettable(ByVal mysql As String) As DataTable
        Try
            Dim myconnection As New OleDbConnection(CommunityGlobals.ConnectionString)
            '        Dim myconnection As New OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings.Item("MYdatabaseconn") & System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings.Item("mydatabasepath")))
            Dim mydataadapter As New OleDbDataAdapter(mysql, myconnection)
            Dim mydataset As New DataSet
            mydataadapter.Fill(mydataset, "table0")
            HttpContext.Current.Cache("mycache") = mydataset.Tables(0)
            '删除:Cache.Remove("mycache")
            If mydataset.Tables(0).Rows.Count = 0 Then
                'System.Web.HttpContext.Current.Response.Write("<script>alert('error:no data record'');</script>")
            End If
            myconnection.Close()
            Return mydataset.Tables(0)
        Catch ex As Exception
        End Try
    End Function
    Public Overloads Shared Function gettable(ByVal myint As Integer) As DataTable
        Dim myconnection As New OleDbConnection(CommunityGlobals.ConnectionString)
        '        Dim myconnection As New OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings.Item("MYdatabaseconn") & System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings.Item("mydatabasepath")))
        Dim mydataadapter As New OleDbDataAdapter("select * from product where boardid=" & myint.ToString & " order by id ", myconnection)
        Dim mydataset As New DataSet
        mydataadapter.Fill(mydataset, "table0")
        HttpContext.Current.Cache("mycache") = mydataset.Tables(0)
        '删除:Cache.Remove("mycache")
        myconnection.Close()
        Return mydataset.Tables(0)
    End Function

    '***********************************************************
    'DELETE ROW
    '***********************************************************
    Public Shared Sub rowsdelete(ByVal mysql As String)
        Dim myconnection As New OleDbConnection(CommunityGlobals.ConnectionString)
        '  Dim myconnection As New OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings.Item("MYdatabaseconn") & System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings.Item("mydatabasepath")))
        Dim mydataadapter As New OleDbDataAdapter(mysql, myconnection)
        Dim mydataset As New DataSet
        mydataadapter.Fill(mydataset, "table0")
        Dim mybuild As New OleDbCommandBuilder(mydataadapter)
        mydataset.Tables(0).Rows(0).Delete()
        mydataadapter.Update(mydataset, "table0")
        myconnection.Close()
    End Sub
    '***********************************************************
    'GET dataset
    '***********************************************************
    Public Overloads Shared Function getdataset(ByVal mysql As String) As DataSet
        Dim myconnection As New OleDbConnection(CommunityGlobals.ConnectionString)
        '        Dim myconnection As New OleDbConnection(System.Configuration.ConfigurationSettings.AppSettings.Item("MYdatabaseconn") & System.Web.HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationSettings.AppSettings.Item("mydatabasepath")))
        Dim mydataadapter As New OleDbDataAdapter(mysql, myconnection)
        Dim mydataset As New DataSet
        mydataadapter.Fill(mydataset, "table0")
        HttpContext.Current.Cache("mycache") = mydataset.Tables(0)
        '删除:Cache.Remove("mycache")
        If mydataset.Tables(0).Rows.Count = 0 Then
            'System.Web.HttpContext.Current.Response.Write("<script>alert('error:no data record'');</script>")
        End If
        myconnection.Close()
        Return mydataset
    End Function
    '***********************************************************
    'DELETE RECORD
    '***********************************************************
    Public Overloads Shared Sub record_del(ByVal myid As Integer)
        Dim myconnection As New OleDbConnection(CommunityGlobals.ConnectionString)
        Dim mycommand As OleDbCommand = New OleDbCommand("delete from articles where id=" & myid, myconnection)
        myconnection.Open()
        mycommand.ExecuteNonQuery()
        myconnection.Close()
    End Sub
    Public Overloads Shared Sub record_del(ByVal myid As Integer, ByVal mytablename As String)
        Dim myconnection As New OleDbConnection(CommunityGlobals.ConnectionString)
        Dim mysql As String = "delete from " & mytablename
        mysql += " where id="
        mysql += myid.ToString
        Dim mycommand As OleDbCommand = New OleDbCommand(mysql, myconnection)
        myconnection.Open()
        mycommand.ExecuteNonQuery()
        myconnection.Close()
    End Sub
    Public Overloads Shared Function record_delete(ByVal myid As Integer, ByVal mytablename As String) As Boolean
        Try
            Dim mysql As String = "delete from " & mytablename & " where id=" & myid.ToString
            Dim mysql2 As String = "select * from " & mytablename & " where id=" & myid.ToString

            If mydb.findrecord(mysql2) = False Then
                Return False
            Else
                Dim myconnection As New OleDbConnection(CommunityGlobals.ConnectionString)
                Dim mycommand As OleDbCommand = New OleDbCommand(mysql, myconnection)
                myconnection.Open()
                mycommand.ExecuteNonQuery()
                myconnection.Close()
            End If
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

    '***********************************************************
    'MY TEST
    '***********************************************************
    Public Shared Function test() As Boolean
        System.Web.HttpContext.Current.Response.Write("<script>alert('ERROR:DATABASE CONNECTION IS ERROR!'');</script>")
        Return True
    End Function

    '***********************************************************
    '作用:路径的后期处理
    '输入如下()
    'http://localhost/nankang/Controls/WebForm9.aspx?a=dd&bb=fasdfsad&cc=43
    '得到
    'a  dd
    '   bb  fasdfsad
    '  cc 43
    '***********************************************************
    Public Shared Function FORMAT_QUERYSTRING()
        ' Dim k As Integer
        'Dim cool As System.Collections.Specialized.NameValueCollection = Request.QueryString
        '  Dim arr1() As String = cool.AllKeys
        ' For k = 0 To arr1.GetUpperBound(0)
        ' Response.Write(arr1(k))
        ' Response.Write(cool.GetValues(k)(0))
        ' Response.Write("<br>")
        ' Next
    End Function
    '***********************************************************
    'MY FORMAT CHAR
    '***********************************************************
    Public Shared Function FORMAT_MORE(ByVal obj As Object)
        If obj Is Nothing Then
            Return ""
        Else
            Dim text As String = obj.ToString()
            If Len(text) > 8 Then
                Return text.Substring(0, 8) & "......."
            Else
                Return text
            End If
        End If
    End Function
    Public Shared Function FORMAT_MORE(ByVal obj As Object, ByVal N As Integer)
        If obj Is Nothing Then
            Return ""
        Else
            Dim text As String = obj.ToString()
            If Len(text) > N Then
                Return text.Substring(0, N) & "......"
            Else
                Return text
            End If
        End If
    End Function 'FORMAT CHAR
    Public Shared Function FORMAT_MORE2(ByVal obj As Object, ByVal N As Integer)
        If obj Is Nothing Then
            Return ""
        Else
            Dim text As String = obj.ToString()
            If Len(text) > N Then
                Return text.Substring(0, N) & "......"
            Else
                Return text
            End If
        End If
    End Function 'FORMAT CHAR
    Public Shared Function FORMAT_datetime_ymd(ByVal my_datetime As String) As String
        Dim mydatetime As Date = CDate(my_datetime)
        Return mydatetime.Year.ToString + "." + Right("0" & mydatetime.Month.ToString, 2) + "." + Right("0" & mydatetime.Day.ToString, 2)
    End Function
    '***********************************************************
    'MY FORMAT URL
    '例: WWW.21CN.COM?AA=ABC&PAGE=1   返回值WWW.21CN.COM?AA=ABC&
    '作用:解决分页显示的问题
    '***********************************************************
    Public Shared Function format_URL() As String
        If System.Web.HttpContext.Current.Request.ServerVariables("QUERY_STRING") = "" Then
            Return System.Web.HttpContext.Current.Request.ServerVariables("URL") & "?"
        Else
            Dim MYURL As String = (System.Web.HttpContext.Current.Request.ServerVariables("URL") & "?" & System.Web.HttpContext.Current.Request.ServerVariables("QUERY_STRING")).ToUpper
            Dim l As Integer = MYURL.IndexOf("PAGE=".ToUpper)
            If l > 0 Then
                Return (MYURL.Substring(0, l))
            Else
                Return MYURL & "&"
            End If
        End If
    End Function
    '***********************************************************
    'MY FORMAT memory field
    '例: a b   返回值a&nbsp;b
    '作用:解决备注字段的转换
    '***********************************************************
    Public Shared Function format_memory(ByVal memory_field As String) As String
        '   memory_field = Replace(System.Web.HttpContext.Current.Server.HtmlEncode(memory_field), Chr(13), "<BR>", 1)  'enter
        '  memory_field = Replace(System.Web.HttpContext.Current.Server.HtmlEncode(memory_field), Chr(32), "&nbsp;", 1)  'sPACEBAR
        memory_field = Replace(memory_field, Chr(13), "<BR>", 1)  'enter
        memory_field = Replace(memory_field, Chr(32), "&nbsp;", 1)  'sPACEBAR
        Return memory_field
    End Function
    '***********************************************************
    'MY FORMAT memory field
    '例: a b   返回值a&nbsp;b
    '作用:解决备注字段的转换
    '***********************************************************
    Public Shared Function format_memory_rest(ByVal memory_field As String) As String
        memory_field = Replace(System.Web.HttpContext.Current.Server.HtmlDecode(memory_field), "<BR>", Chr(13), 1)
        memory_field = Replace(memory_field, "&nbsp;", Chr(32), 1) 'sPACEBAR
        Return memory_field
    End Function
    '***********************************************************
    'MY FORMAT file and date
    '例:
    '作用:得到现在的日期加时间作为文件名如:20041112142013
    '***********************************************************    '
    Public Shared ReadOnly Property FORMAT_file_date() As String
        Get
            Return DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString + DateTime.Now.Day.ToString + DateTime.Now.Hour.ToString + DateTime.Now.Minute.ToString + DateTime.Now.Second.ToString
        End Get
    End Property
    '***********************************************************
    'MY FORMAT file and date
    '例:
    '作用:得到现在的日期加时间名如:20041112142013再加上扩展名
    '*********************************************************** 
    Public Overloads Shared Function FORMAT_new_name(ByVal name As String) As String
        Dim newfilename As String = name.Substring(name.LastIndexOf("."))
        newfilename = DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString + DateTime.Now.Day.ToString + DateTime.Now.Hour.ToString + DateTime.Now.Minute.ToString + DateTime.Now.Second.ToString + newfilename '时间作为文件名
        Return newfilename
    End Function
    Public Shared Property md5(ByVal password As String) As String
        Get
            Return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(password, "md5")
        End Get
        Set(ByVal Value As String)
            Value = Trim(Value)
        End Set
    End Property
    '保存SESSION   用户,权限数,权限
    Public Overloads Shared Sub session_login(ByVal username As String, ByVal legalpower As Integer, ByVal LEGALpower_text As String)
        System.Web.HttpContext.Current.Session.Add("login", True)
        System.Web.HttpContext.Current.Session.Add("username", username)
        System.Web.HttpContext.Current.Session.Add("legalpower", legalpower)
        System.Web.HttpContext.Current.Session.Add("legalpower_text", LEGALpower_text)
    End Sub
    Public Overloads Shared Sub session_login(ByVal username As String, ByVal legalpower As Integer)
        System.Web.HttpContext.Current.Session.Add("login", True)
        System.Web.HttpContext.Current.Session.Add("username", username)
        System.Web.HttpContext.Current.Session.Add("legalpower", legalpower)
        Select Case legalpower
            Case 0
                System.Web.HttpContext.Current.Session.Add("legalpower_text", "会员")
            Case 1
                System.Web.HttpContext.Current.Session.Add("legalpower_text", "供货商")
            Case 9
                System.Web.HttpContext.Current.Session.Add("legalpower_text", "管理员")
        End Select
    End Sub
    Public Overloads Shared Sub session_logout(ByVal username As String, ByVal legalpower As Integer)
        System.Web.HttpContext.Current.Session.Clear()
    End Sub
    '检查是不是管理员
    Public Overloads Shared Function check_admin() As Boolean
        If System.Web.HttpContext.Current.Session("legalpower") = 9 Then
            Return True
        Else
            Return False
        End If
    End Function

    Public Overloads Shared Sub send_mail(ByVal mailto As String, ByVal subject As String, ByVal body As String)
        Dim mail As New MailMessage
        mail.To = mailto
        mail.From = "info@nketd.com"
        mail.Subject = subject
        mail.Body = body
        mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", "1") 'basic authentication
        mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", "info") 'set your username here
        mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", "123456") 'set your password here
        SmtpMail.SmtpServer = "smtp.nketd.com" 'your real server goes here
        SmtpMail.Send(mail)
    End Sub
    Public Overloads Shared Sub send_mail2(ByVal mailto As String, ByVal subject As String, ByVal body As String)
        Dim mail As New MailMessage
        mail.To = mailto
        mail.From = "xuandhu@21cn.com"
        mail.Subject = subject
        mail.Body = body
        mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate", "1") 'basic authentication
        mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendusername", "xuandhu") 'set your username here
        mail.Fields.Add("http://schemas.microsoft.com/cdo/configuration/sendpassword", "791221") 'set your password here
        SmtpMail.SmtpServer = "SMTP.21cn.com" 'your real server goes here
        SmtpMail.Send(mail)
    End Sub
    Public Shared Property message(ByVal True_False As Boolean) As String
        Get
            If True_False = True Then
                Return "操作成功"
            Else
                Return "操作失败"
            End If
        End Get
        Set(ByVal Value As String)
        End Set
    End Property
End Class