Imports System.Data.SqlClient
Imports Microsoft.VisualBasic

Public Class code

    Public Shared customSetting As String = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString")

    Public Shared Function Data_label(ByVal SQLstr As String, ByVal LabelStr As String) As String

        Dim Conn As SqlConnection = New SqlConnection(customSetting)
        Dim SQL As String
        Dim da As New SqlDataAdapter(SQL, Conn)
        Dim ds As New Data.DataSet
        SQL = SQLstr
        da = New SqlDataAdapter(SQL, Conn)
        ds = New Data.DataSet
        da.Fill(ds)
        Dim tb As New Data.DataTable
        tb = ds.Tables(0)
        Dim str As String
        If tb.Rows.Count = 0 Then
            str = ""
        Else
            str = Trim(tb.Rows.Item(0).Item(LabelStr))
        End If
        Return str
    End Function

    Public Shared Function NoHTML(ByVal Htmlstring As String) As String
        If String.IsNullOrEmpty(Htmlstring) = True Then
            Return String.Empty
        End If

        Htmlstring = Regex.Replace(Htmlstring, "<script[^>]*?>.*?</script>", "", RegexOptions.IgnoreCase)
        Htmlstring = Regex.Replace(Htmlstring, "<style[^>]*?>.*?</style>", "", RegexOptions.IgnoreCase)
        Htmlstring = Regex.Replace(Htmlstring, "-->", "", RegexOptions.IgnoreCase)
        Htmlstring = Regex.Replace(Htmlstring, "<!--.*", "", RegexOptions.IgnoreCase)
        Htmlstring = Regex.Replace(Htmlstring, "<[^>]*>", "", RegexOptions.Compiled)
        Htmlstring = Regex.Replace(Htmlstring, "([\r\n])[\s]+", " ", RegexOptions.Compiled)
        Return Htmlstring.Replace("&nbsp;", " ")
    End Function

    Public Shared Function Data_count(ByVal SQLstr As String) As String

            Dim intrs As String
            Dim Conn As SqlConnection
            Conn = New SqlConnection(customSetting)
            Dim Cmd As New SqlCommand(SQLstr, Conn)
            Conn.Open()
            If Cmd.ExecuteScalar() Is DBNull.Value Then
                intrs = "0"
            Else
                intrs = Cmd.ExecuteScalar()
            End If
            Conn.Close()
            Return intrs

    End Function

    Public Shared Function Data_adlink(ByVal SQLstr As String, ByVal w As String, ByVal h As String) As String

        Dim Conn As SqlConnection = New SqlConnection(customSetting)
        Dim SQL As String
        Dim da As New SqlDataAdapter(SQL, Conn)
        Dim ds As New Data.DataSet
        SQL = SQLstr
        da = New SqlDataAdapter(SQL, Conn)
        ds = New Data.DataSet
        da.Fill(ds)
        Dim tb As New Data.DataTable
        tb = ds.Tables(0)
        Dim str As String = "<a href=" & Trim(tb.Rows.Item(0).Item("A_Url")) & " rel=""nofollow"" target=""_blank""><img src=" & Trim(tb.Rows.Item(0).Item("A_Img")) & " width=" & w & " height=" & h & "></a>"
        Return str

    End Function

    Public Shared Function Data_Repeater(ByVal SQLstr As String) As ICollection

        Dim Conn As SqlConnection = New SqlConnection(customSetting)
        Dim SQL As String
        Dim da As New SqlDataAdapter(SQL, Conn)
        Dim ds As New Data.DataSet
        SQL = SQLstr
        da = New SqlDataAdapter(SQL, Conn)
        ds = New Data.DataSet
        da.Fill(ds)
        Dim tb As New Data.DataTable
        tb = ds.Tables(0)
        Return tb.DefaultView

    End Function

    Public Shared Function Data_DataTable(ByVal SQLstr As String) As Data.DataSet

        Dim Conn As SqlConnection = New SqlConnection(customSetting)
        Dim SQL As String
        Dim da As New SqlDataAdapter(SQL, Conn)
        Dim ds As New Data.DataSet
        SQL = SQLstr
        da = New SqlDataAdapter(SQL, Conn)
        ds = New Data.DataSet
        da.Fill(ds)
        Return ds
    End Function

    Public Shared Function Data_updatefw(ByVal SQLstr As String) As String

        Dim Cmd As SqlCommand
        Dim Conn As SqlConnection
        Conn = New SqlConnection(customSetting)
        Cmd = New SqlCommand(SQLstr, Conn)
        Conn.Open()
        Cmd.ExecuteNonQuery()
        Conn.Close()
        Return True
    End Function

    Public Shared Function Data_User_count(ByVal SQLstr As String, ByVal userName As String) As String

        Dim intrs As String
        Dim Conn As SqlConnection
        Conn = New SqlConnection(customSetting)
        Dim Cmd As New SqlCommand(SQLstr, Conn)
        Conn.Open()
        Dim myParameter As SqlParameter = New SqlParameter
        myParameter = Cmd.Parameters.Add("@user", Data.SqlDbType.NVarChar, 255)
        Cmd.Parameters("@user").Value = userName
        If Cmd.ExecuteScalar() Is DBNull.Value Then
            intrs = "0"
        Else
            intrs = Cmd.ExecuteScalar()
        End If
        Conn.Close()
        Return intrs

    End Function

    Public Shared Function Data_userName_upDate(ByVal SQLstr As String, ByVal userName As String) As String

        Dim Cmd As SqlCommand
        Dim Conn As SqlConnection
        Conn = New SqlConnection(customSetting)
        Cmd = New SqlCommand(SQLstr, Conn)
        Dim myParameter As SqlParameter = New SqlParameter
        myParameter = Cmd.Parameters.Add("@user", Data.SqlDbType.NVarChar, 255)
        Cmd.Parameters("@user").Value = userName

        Conn.Open()
        Cmd.ExecuteNonQuery()
        Conn.Close()
        Return True
    End Function
    Public Shared Function Data_userName(ByVal SQLstr As String, ByVal userName As String) As Data.DataSet

        Dim Conn As SqlConnection = New SqlConnection(customSetting)
        Dim SQL As String
        Dim da As SqlDataAdapter = New SqlDataAdapter()
        Dim ds As Data.DataSet = New Data.DataSet()
        SQL = SQLstr
        Dim Cmd As SqlCommand = New SqlCommand
        Cmd.CommandText = SQL
        Cmd.Parameters.Add("@user", Data.SqlDbType.NVarChar, 255)
        Cmd.Parameters("@user").Value = userName
        Cmd.Connection = Conn
        da.SelectCommand = Cmd
        da.Fill(ds)

        Return ds

    End Function

End Class