DBPage

Imports Microsoft.VisualBasic

Imports System.Data.SqlClient

Imports System.Data

Imports System.Exception

Imports System.Transactions

PublicClassGlovalFunction

Public CNN AsNewSqlConnection

Dim str AsString = System.Configuration.ConfigurationSettings.AppSettings("connectionstr")

PublicSubNew()

SqlConnection.ClearPool(CNN)

SqlConnection.ClearAllPools()

        CNN.ConnectionString = str'ConnectionString

EndSub

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

#Region"---------------------------One page to anotherpage-----------------------"

PublicFunctionAddNewEmployee2(ByVal Dtmain AsDataTable) AsLong

Dim Berror AsBoolean, DA AsNewSqlDataAdapter, SQLT AsSqlTransaction

'Dim Myparam1 As SqlParameter

Dim CMD AsNewSqlCommand

Dim RecIDCust AsLong

Try

If CNN.State = ConnectionState.Closed ThenCNN.Open()

Catch ex AsException

Throw ex

Exit Function

EndTry

        Berror = False

Try

Dim sqlpp AsNewSqlParameter

'Dtmain = dsmain.Tables(0).Copy

            SQLT = CNN.BeginTransaction

            CMD.Transaction = SQLT

            CMD.Connection = CNN

            CMD.CommandType = CommandType.StoredProcedure

            CMD.CommandText = "saveregform11"

CMD.Parameters.AddWithValue("@Firstname", Dtmain.Rows(0).Item("Firstname"))

CMD.Parameters.AddWithValue("@Address", Dtmain.Rows(0).Item("Address"))

CMD.Parameters.AddWithValue("@Emailaddress", Dtmain.Rows(0).Item("Emailaddress"))

CMD.Parameters.AddWithValue("@Phone", Dtmain.Rows(0).Item("Phone"))

CMD.Parameters.AddWithValue("@Zipcode", Dtmain.Rows(0).Item("Zipcode"))

CMD.Parameters.AddWithValue("@Mobile", Dtmain.Rows(0).Item("Mobile"))

CMD.Parameters.AddWithValue("@Country", Dtmain.Rows(0).Item("Country"))

'CMD.Parameters.AddWithValue("@ID", Id)

            RecIDCust = CMD.ExecuteNonQuery()

If RecIDCust = 0 Then

                Berror = True

EndIf

If Berror = FalseThen

CMD.Transaction.Commit()

CNN.Close()

Return RecIDCust

Else

CMD.Transaction.Rollback()

CNN.Close()

Return 0

EndIf

Catch ex AsException

Throw ex

CMD.Transaction.Rollback()

Return 0

Finally

If CNN.State <>ConnectionState.Closed ThenCNN.Close()

EndTry

EndFunction

#EndRegion

#Region"-----------------------------Purchase Master--------------------------"

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

PublicFunctionGetAllproductRecord()

Dim cmd AsNewSqlCommand()

        cmd.Connection = CNN

        cmd.CommandText = "GetAllProductRecord"

        cmd.CommandType = CommandType.StoredProcedure

Dim da AsNewSqlDataAdapter

Dim ds AsNewDataSet

Try

da = NewSqlDataAdapter(cmd)

da.Fill(ds)

IfNot ds IsNothingThen

Return ds

EndIf

Catch ex AsException

EndTry

EndFunction

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

#Region"--------------------------CustomerAdrressmaster----------------------"

PublicFunctionAddcustVend(ByVal Dsmain AsDataSet) AsLong

Dim dtAddress AsDataTable

Dim dtContact AsDataTable

Dim Berror AsBoolean, DA AsNewSqlDataAdapter, SQLT AsSqlTransaction

Dim i AsInteger, j AsInteger

Dim CMD AsNewSqlCommand

Dim Dtmain AsNewDataTable

Dim RecIDCust AsLong, AddressIdCust AsLong

Try

If CNN.State = ConnectionState.Closed ThenCNN.Open()

Catch ex AsException

Throw ex

Exit Function

EndTry

        Berror = False

        RecIDCust = 0

Try

Dim sqlpp AsNewSqlParameter

            Dtmain = Dsmain.Tables(0).Copy

            SQLT = CNN.BeginTransaction

            CMD.Transaction = SQLT

            CMD.Connection = CNN

            CMD.CommandType = CommandType.StoredProcedure

            CMD.CommandText = "inscustvendmaster"

CMD.Parameters.AddWithValue("@Custname", Dtmain.Rows(0).Item("Custname"))

CMD.Parameters.AddWithValue("@Dob", Dtmain.Rows(0).Item("DOB"))

CMD.Parameters.AddWithValue("@type", Dtmain.Rows(0).Item("Type"))

Dim Myparam AsSqlParameter = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)

            Myparam.Direction = ParameterDirection.ReturnValue

CMD.ExecuteNonQuery()

            RecIDCust = CMD.Parameters("RETURN_VALUE").Value

DimdtAsNewDataTable

If RecIDCust > 0 Then

dtAddress = NewDataTable

IfNotDsmain.Tables(1) IsNothingThen dtAddress = Dsmain.Tables(1).Copy

IfNot dtAddress IsNothingThen

If dtAddress.Rows.Count > 0 Then

For i = 0 To dtAddress.Rows.Count - 1

                            CMD.CommandType = CommandType.StoredProcedure

                            CMD.CommandText = "InsAddressMaster"

CMD.Parameters.Clear()

CMD.Parameters.AddWithValue("@CustId", RecIDCust)

CMD.Parameters.AddWithValue("@add1", dtAddress.Rows(i).Item("Address1"))

CMD.Parameters.AddWithValue("@add2", dtAddress.Rows(i).Item("Address2"))

                            Myparam = NewSqlParameter

                            Myparam = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)

                            Myparam.Direction = ParameterDirection.ReturnValue

CMD.ExecuteNonQuery()

                            AddressIdCust = CMD.Parameters("RETURN_VALUE").Value

If Myparam.Value = 0 Then

                                Berror = True

Exit For

Else

dtContact = NewDataTable

IfNotDsmain.Tables(2) IsNothingThen dtContact = Dsmain.Tables(2).Copy

IfNot dtContact IsNothingThen

If dtContact.Rows.Count > 0 Then

                                        CMD.CommandType = CommandType.StoredProcedure

                                        CMD.CommandText = "InscontactMaster"

For j = 0 To dtContact.Rows.Count - 1

CMD.Parameters.Clear()

CMD.Parameters.AddWithValue("@addressId", AddressIdCust)

CMD.Parameters.AddWithValue("@Contactname", dtContact.Rows(j).Item("Contactname"))

CMD.Parameters.AddWithValue("@Mobileno", dtContact.Rows(j).Item("Mobileno"))

CMD.Parameters.AddWithValue("@ZipNo", dtContact.Rows(j).Item("ZipNo"))

                                            Myparam = NewSqlParameter

                                            Myparam = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)

                                            Myparam.Direction = ParameterDirection.ReturnValue

CMD.ExecuteNonQuery()

If Myparam.Value = 0 Then

                                                Berror = True

Exit For

EndIf

Next

EndIf

EndIf

EndIf

Next

EndIf

EndIf

EndIf

If Berror = FalseThen

CMD.Transaction.Commit()

CNN.Close()

Return RecIDCust

Else

CMD.Transaction.Rollback()

CNN.Close()

Return 0

EndIf

Catch ex AsException

Throw ex

CMD.Transaction.Rollback()

Finally

If CNN.State <>ConnectionState.Closed ThenCNN.Close()

EndTry

EndFunction

PublicFunctionGetCustlist()

Dim cmd AsNewSqlCommand()

        cmd.Connection = CNN

        cmd.CommandText = "getcustlist"

        cmd.CommandType = CommandType.StoredProcedure

Dim da AsNewSqlDataAdapter

Dim ds AsNewDataSet

Try

da = NewSqlDataAdapter(cmd)

da.Fill(ds)

IfNot ds IsNothingThen

Return ds

EndIf

Catch ex AsException

EndTry

EndFunction

#EndRegion

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

PublicFunctionUpdateUserLogInID(ByVal DTable AsDataTable) AsLong

Dim Berror AsBoolean

Dim DA AsNewSqlDataAdapter

Dim SQLT AsSqlTransaction

'Dim Myparam1 As SqlParameter

Dim CMD AsNewSqlCommand

Dim RecIDCust AsLong

Try

If CNN.State = ConnectionState.Closed ThenCNN.Open()

Catch ex AsException

Throw ex

Exit Function

EndTry

        Berror = False

Try

Dim sqlpp AsNewSqlParameter

            SQLT = CNN.BeginTransaction

            CMD.Transaction = SQLT

            CMD.Connection = CNN

            CMD.CommandType = CommandType.StoredProcedure

            CMD.CommandText = "savedata"

'CMD.Parameters.AddWithValue("@MasterRecID", DTable.Rows(0).Item("RecID"))

CMD.Parameters.AddWithValue("@DOB", DTable.Rows(0).Item("DOB"))

CMD.Parameters.AddWithValue("@Address", DTable.Rows(0).Item("Address"))

CMD.Parameters.AddWithValue("@City", DTable.Rows(0).Item("City"))

CMD.Parameters.AddWithValue("@State", DTable.Rows(0).Item("State"))

'CMD.Parameters.AddWithValue("@PCode", DTable.Rows(0).Item("PostalCode"))

CMD.Parameters.AddWithValue("@Country", DTable.Rows(0).Item("Country"))

CMD.Parameters.AddWithValue("@Email", DTable.Rows(0).Item("Email"))

CMD.Parameters.AddWithValue("@AlternateEmail", DTable.Rows(0).Item("AlternateEmail"))

CMD.Parameters.AddWithValue("@Name", DTable.Rows(0).Item("@Name"))

CMD.Parameters.AddWithValue("@MobileNo", DTable.Rows(0).Item("MobileNo"))

CMD.Parameters.AddWithValue("@Title", DTable.Rows(0).Item("Title"))

CMD.Parameters.AddWithValue("@PhoneNo", DTable.Rows(0).Item("PhoneNo"))

CMD.Parameters.AddWithValue("@NewUserID", DTable.Rows(0).Item("NewUserID"))

CMD.Parameters.AddWithValue("@OldUserID", DTable.Rows(0).Item("OldUserID"))

CMD.Parameters.AddWithValue("@OldPassword", DTable.Rows(0).Item("OldPassword"))

CMD.Parameters.AddWithValue("@NewPassword", DTable.Rows(0).Item("NewPassword"))

CMD.Parameters.AddWithValue("@Gender", DTable.Rows(0).Item("Gender"))

Dim Myparam AsSqlParameter = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.Int)

            Myparam.Direction = ParameterDirection.ReturnValue

            RecIDCust = CMD.ExecuteNonQuery()

            RecIDCust = CMD.Parameters("RETURN_VALUE").Value

'AccountIDCust = Myparam1.Value

If RecIDCust = 0 Then

                Berror = True

EndIf

If Berror = FalseThen

CMD.Transaction.Commit()

CNN.Close()

Return RecIDCust

Else

CMD.Transaction.Rollback()

CNN.Close()

Return 0

EndIf

Catch ex AsException

Throw ex

CMD.Transaction.Rollback()

Return 0

Finally

If CNN.State <>ConnectionState.Closed ThenCNN.Close()

EndTry

EndFunction

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

PublicFunctionupdateaddressmaster(ByVal Dtmain AsDataTable, ByVal Id AsInteger) AsLong

Dim Berror AsBoolean, DA AsNewSqlDataAdapter, SQLT AsSqlTransaction

'Dim Myparam1 As SqlParameter

Dim CMD AsNewSqlCommand

Dim RecIDCust AsLong

Try

If CNN.State = ConnectionState.Closed ThenCNN.Open()

Catch ex AsException

Throw ex

Exit Function

EndTry

        Berror = False

Try

Dim sqlpp AsNewSqlParameter

'Dtmain = dsmain.Tables(0).Copy

            SQLT = CNN.BeginTransaction

            CMD.Transaction = SQLT

            CMD.Connection = CNN

            CMD.CommandType = CommandType.StoredProcedure

            CMD.CommandText = "updateaddressmaster"

CMD.Parameters.AddWithValue("@Firstname", Dtmain.Rows(0).Item("Firstname"))

CMD.Parameters.AddWithValue("@Middlename", Dtmain.Rows(0).Item("Middlename"))

CMD.Parameters.AddWithValue("@Lastname", Dtmain.Rows(0).Item("Lastname"))

CMD.Parameters.AddWithValue("@ID", Id)

            RecIDCust = CMD.ExecuteNonQuery()

If RecIDCust = 0 Then

                Berror = True

EndIf

If Berror = FalseThen

CMD.Transaction.Commit()

CNN.Close()

Return RecIDCust

Else

CMD.Transaction.Rollback()

CNN.Close()

Return 0

EndIf

Catch ex AsException

Throw ex

CMD.Transaction.Rollback()

Return 0

Finally

If CNN.State <>ConnectionState.Closed ThenCNN.Close()

EndTry

EndFunction

PublicFunctiondelateddata(ByVal Dtmain AsDataTable, ByVal Id AsInteger) AsLong

Dim Berror AsBoolean, DA AsNewSqlDataAdapter, SQLT AsSqlTransaction

Dim CMD AsNewSqlCommand

Dim RecIDCust AsLong

Try

If CNN.State = ConnectionState.Closed ThenCNN.Open()

Catch ex AsException

Throw ex

Exit Function

EndTry

        Berror = False

Try

Dim sqlpp AsNewSqlParameter

            SQLT = CNN.BeginTransaction

            CMD.Transaction = SQLT

            CMD.Connection = CNN

            CMD.CommandType = CommandType.StoredProcedure

CMD.CommandText = "dehudata"

            CMD.Parameters.AddWithValue("@ID", Id)

RecIDCust = CMD.ExecuteNonQuery()

If RecIDCust = 0 Then

                Berror = True

EndIf

If Berror = FalseThen

CMD.Transaction.Commit()

CNN.Close()

Return RecIDCust

Else

CMD.Transaction.Rollback()

CNN.Close()

Return 0

EndIf

Catch ex AsException

Throw ex

CMD.Transaction.Rollback()

Return 0

Finally

If CNN.State <>ConnectionState.Closed ThenCNN.Close()

EndTry

EndFunction

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

PublicFunctionLogIn(ByVal UserID AsString, ByVal pass AsString) AsDataSet

Dim DS AsNewDataSet, CMD AsNewSqlCommand, DA AsNewSqlDataAdapter

Try

If CNN.State = ConnectionState.Closed ThenCNN.Open()

Catch ex AsException

Throw ex

Finally

EndTry

        CMD.Connection = CNN

        CMD.CommandType = CommandType.StoredProcedure

        CMD.CommandText = "loginview"

CMD.Parameters.AddWithValue("@UserId", UserID)

CMD.Parameters.AddWithValue("@pass", pass)

Dim SQLP AsSqlParameter = CMD.Parameters.Add("RETURN_VALUE", SqlDbType.BigInt)

SQLP.Direction = ParameterDirection.ReturnValue

Try

            DA = NewSqlDataAdapter(CMD)

DA.Fill(DS)

IfNot DS IsNothingThenReturn DS

Catch ex AsException

Throw ex

Finally

If CNN.State <>ConnectionState.Closed ThenCNN.Close()

EndTry

EndFunction

PublicFunctionCalculateTotalPages(ByVal intTotalRows AsInteger) AsInteger

Try

Dim intPageCount AsInteger = 1

Dim dblPageCount AsDouble = CDbl((Convert.ToDecimal(intTotalRows)))

intPageCount = Convert.ToInt32(Math.Ceiling(dblPageCount))

Return intPageCount

Catch ex AsException

EndTry

EndFunction

'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

PublicFunctionGetUserId(ByVal UserId AsString) AsDataSet

Dim SqlCmd AsNewSqlCommand

Dim SqlDa AsNewSqlDataAdapter

Dim SqlDs AsNewDataSet

Try

            SqlCmd.Connection = CNN

            SqlCmd.CommandText = "uspGetExistUser"

            SqlCmd.CommandType = CommandType.StoredProcedure

With SqlCmd.Parameters

                .Clear()

                SqlDa.SelectCommand = SqlCmd

                .AddWithValue("@UserId", UserId)

SqlDa.Fill(SqlDs)

EndWith

Catch ex AsException

Throw ex

EndTry

Return SqlDs

EndFunction