Imports Microsoft.VisualBasic
Imports System
Imports System.IO
Imports System.Net
Imports System.Text
Imports System.Web
Imports System.Threading
Imports System.ComponentModel
Imports System.IO.Ports
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
Imports vb = Microsoft.VisualBasic
Imports System.Data.OleDb

Public Module Starter
    Dim con1, con2 As SqlConnection
    Dim dr, dr2 As SqlDataReader
    Dim da As SqlDataAdapter
    Dim cmd, cmd1 As New SqlCommand

    Public Sub sqlcon1()
        Dim str1 As String
        con1 = New SqlConnection
        str1 = ConfigurationManager.ConnectionStrings("dbnm").ConnectionString.ToString
        con1.ConnectionString = str1
    End Sub

    Public Sub start1()
        Call sqlcon1()
        If con1.State = 1 Then
            con1.Close()
        End If
        con1.Open()
    End Sub

    Public Sub close1()
        If con1.State = 1 Then
            con1.Close()
        End If
    End Sub

    Public Sub SQLSelect(ByVal qry As String)
        Dim cmd1 As SqlCommand
        cmd1 = New SqlCommand(qry, con1)
        cmd1.CommandType = CommandType.Text
        dr = cmd1.ExecuteReader
    End Sub

    Public Sub SQLInsert(ByVal qry As String)
        Dim cmd1 As SqlCommand
        cmd1 = New SqlCommand(qry, con1)
        cmd1.CommandType = CommandType.Text
        cmd1.ExecuteNonQuery()
    End Sub

    Public Function get_dataset(ByVal qry As String) As DataSet
        Dim strweb As String
        Dim cn As SqlConnection
        Dim cmd As SqlCommand
        Dim da As New SqlDataAdapter
        Dim ds As New DataSet
        strweb = ConfigurationManager.ConnectionStrings("dbnm").ConnectionString.ToString
        cn = New SqlConnection(strweb)
        Try
            cn.Open()
            cmd = New SqlCommand(qry, cn)
            da = New SqlDataAdapter(cmd)
            da.Fill(ds)
            Return ds
        Catch ex As Exception
            Throw ex
        Finally
            cn.Close()
            da.Dispose()
        End Try
    End Function

    Public Function stringtodate(ByVal str As String) As Date
        Dim dob As Date
        Dim dt As String = vb.Left(str, 2)
        Dim mon As String = vb.Mid(str, 4, 2)
        Dim yr As String = vb.Right(str, 4)
        dob = CDate(Format(Val(mon), "00") & "/" & Format(Val(dt), "00") & "/" & Format(Val(yr), "0000"))
        Return dob
    End Function

    Public Function excel_dataset(ByVal str As String, ByVal cn_str As String) As DataSet
        Dim a_cn As OleDbConnection
        Dim a_cmd As OleDbCommand
        Dim a_da As OleDbDataAdapter
        Dim A_DS As New DataSet
        a_cn = New OleDbConnection(cn_str)
        Try
            a_cn.Open()
            a_cmd = New OleDbCommand(str, a_cn)
            a_da = New OleDbDataAdapter(a_cmd)
            a_da.Fill(A_DS)
            Return A_DS
        Catch ex As Exception
            Throw ex
        Finally
            a_cn.Close()
            a_da.Dispose()
        End Try

    End Function
End Module
