VB.net連接SQL Server

 

  <DBINFO>

    <DBType>1</DBType> <!--連接的資料庫類型(Oracle=0/MSSQL=1)-->

    <DBServer>12.18.1.23</DBServer>

    <DBPort>1433</DBPort>

    <DBName>DBName</DBName>

    <UID>id</UID>

    <PWD>pw</PWD>

  </DBINFO>

Imports System.Data.SqlClient

Imports System.Xml

Public Enum Posting

    deduct

    debit

End Enum

Class DBTool

    Public Shared connectionString As String = ""

    Public Shared RamFactory As Hashtable = New Hashtable

    Public Shared Sub Init()

        Dim xmlPath As String = "D:\config.xml"

        Dim xmldoc As New XmlDocument()

        ' XML ファイルを読み込む

        xmldoc.Load(xmlPath)

        ' 属性 id='00002' を持つ person の name ノードリストを取得する

        Dim personList As XmlNodeList = xmldoc.SelectNodes("//DBINFO/DBServer")

        Dim ip = xmldoc.SelectSingleNode("//DBINFO/DBServer").InnerText

        Dim DBName = xmldoc.SelectSingleNode("//DBINFO/DBName").InnerText

        Dim UID = xmldoc.SelectSingleNode("//DBINFO/UID").InnerText

        Dim PWD = xmldoc.SelectSingleNode("//DBINFO/PWD").InnerText

        Dim DBPort = xmldoc.SelectSingleNode("//DBINFO/DBPort").InnerText

        Dim DBType = xmldoc.SelectSingleNode("//DBINFO/DBType").InnerText

        connectionString = String.Format("Data Source={0};Initial Catalog={1};User ID={2};Password={3}", ip, DBName, UID, PWD)

    End Sub

    Public Shared Sub SqlToDgv(ByRef ret_dgvFactory As DataGridView)

        '

        '4.在程式中篩選出啟用的Factory並以FACTORY_NO排序，以Grid方式顯示

        Try

            Using conn As New SqlConnection(connectionString)

                Dim ada As SqlDataAdapter = New SqlDataAdapter("SELECT FACTORY_NO, FACTORY_ID, FACTORY_ALIS, FACTORY_DESC, ENABLE FROM WMS_M_FACTORY where ENABLE =0", conn)

                Dim ds As DataSet = New DataSet()

                Dim ds_count As Integer = ada.Fill(ds)

                ret_dgvFactory.DataSource = ds.Tables(0).DefaultView

                ada.Dispose()

            End Using

        Catch ex As Exception

            MessageBox.Show(ex.Message.ToString(), "Error Message")

        End Try

        ret_dgvFactory.Columns(0).HeaderText = "FACTORY_NO"

        ret_dgvFactory.Columns(1).HeaderText = "FACTORY_ID"

        ret_dgvFactory.Columns(2).HeaderText = "FACTORY_ALIS"

        ret_dgvFactory.Columns(3).HeaderText = "FACTORY_DESC"

        ret_dgvFactory.Columns(4).HeaderText = "ENABLE"

    End Sub

    Public Shared Function GetData(ByVal sqlCommand As String) As DataTable

        Dim northwindConnection As SqlConnection =

            New SqlConnection(connectionString)

        Dim command As New SqlCommand(sqlCommand, northwindConnection)

        Dim adapter As SqlDataAdapter = New SqlDataAdapter()

        adapter.SelectCommand = command

        Dim table As New DataTable

        table.Locale = System.Globalization.CultureInfo.InvariantCulture

        adapter.Fill(table)

        Return table

    End Function

    Public Shared Function ReadFactory() As Hashtable

        'Dim conn As SqlConnection = Nothing

        RamFactory = New Hashtable

        Try

            Using conn As New SqlConnection(connectionString)

                conn.Open()

                Dim sql As String = "Select * FROM WMS_M_FACTORY order by FACTORY_NO"

                Dim command As SqlCommand = New SqlCommand(sql, conn)

                Using reader As SqlDataReader = command.ExecuteReader()

                    While reader.Read

                        Try

                            Dim bean = New BeanFactory(reader("FACTORY_NO").ToString(), reader("FACTORY_ID").ToString(), reader("FACTORY_ALIS").ToString()， reader("FACTORY_DESC").ToString()， Decimal.Parse(reader("ENABLE").ToString()))

                            RamFactory.Add(bean.FACTORY_NO, bean)

                        Catch ex As Exception

                            MessageBox.Show(ex.Message.ToString(), "Error Message")

                        End Try

                    End While

                End Using

            End Using

        Catch ex As Exception

            MessageBox.Show(ex.Message.ToString(), "Error Message")

        End Try

        Return RamFactory

    End Function

    Public Shared Sub NewFactory(objBeanFactory As BeanFactory)

        Try

            Using conn As New SqlConnection(connectionString)

                conn.Open()

                Dim sql As String = "INSERT INTO WMS_M_FACTORY (FACTORY_NO, FACTORY_ID, FACTORY_ALIS, FACTORY_DESC, ENABLE) VALUES(@FACTORY_NO, @FACTORY_ID, @FACTORY_ALIS, @FACTORY_DESC, @ENABLE)"

                'sql = "insert into MasbrgSpring (KODEC, WIRE, DIMENSION, STD, NOMLOT, STAT) VALUES (@kodec, @wire,@dimension,@std,@nomlot,@stat)"

                '"INSERT INTO WMS.dbo.WMS_M_FACTORY (FACTORY_NO, FACTORY_ID, FACTORY_ALIS, FACTORY_DESC, ENABLE) VALUES

                ''(@FACTORY_NO, @, @, @, @)"

                Dim command As SqlCommand = New SqlCommand(sql, conn)

                command.Parameters.AddWithValue("@FACTORY_NO", objBeanFactory.FACTORY_NO)

                command.Parameters.AddWithValue("@FACTORY_ID", objBeanFactory.FACTORY_ID)

                command.Parameters.AddWithValue("@FACTORY_ALIS", objBeanFactory.FACTORY_ALIS)

                command.Parameters.AddWithValue("@FACTORY_DESC", objBeanFactory.FACTORY_DESC)

                command.Parameters.AddWithValue("@ENABLE", objBeanFactory.ENABLE)

                command.ExecuteNonQuery()

            End Using

        Catch ex As Exception

            MessageBox.Show(ex.Message.ToString(), "Error Message")

        End Try

    End Sub

    Public Shared Function UpdateFactory(objBeanFactory As BeanFactory) As Boolean

        Dim result = True

        Try

            Using conn As New SqlConnection(connectionString)

                conn.Open()

                Dim sql As String = "UPDATE WMS_M_FACTORY Set FACTORY_ID=@FACTORY_ID, FACTORY_ALIS =@FACTORY_ALIS, FACTORY_DESC =@FACTORY_ALIS, ENABLE =@ENABLE WHERE FACTORY_NO =@FACTORY_NO"

                'UPDATE THIRDPARTY.dbo.WMS_M_FACTORY Set FACTORY_ID=@FACTORY_ID, FACTORY_ALIS =@FACTORY_ALIS, FACTORY_DESC =@FACTORY_ALIS, ENABLE =@ENABLE WHERE FACTORY_NO =@FACTORY_NO

                Dim command As SqlCommand = New SqlCommand(sql, conn)

                command.Parameters.AddWithValue("@FACTORY_NO", objBeanFactory.FACTORY_NO)

                command.Parameters.AddWithValue("@FACTORY_ID", objBeanFactory.FACTORY_ID)

                command.Parameters.AddWithValue("@FACTORY_ALIS", objBeanFactory.FACTORY_ALIS)

                command.Parameters.AddWithValue("@FACTORY_DESC", objBeanFactory.FACTORY_DESC)

                command.Parameters.AddWithValue("@ENABLE", objBeanFactory.ENABLE)

                command.ExecuteNonQuery()

            End Using

        Catch ex As Exception

            result = False

            MessageBox.Show(ex.Message.ToString(), "Error Message")

        End Try

        Return result

    End Function

    Public Shared Function DeleteFactory(objBeanFactory As BeanFactory) As Boolean

        Dim result = True

        Try

            Using conn As New SqlConnection(connectionString)

                conn.Open()

                Dim sql As String = "DELETE From WMS_M_FACTORY Where FACTORY_NO =@FACTORY_NO"

                Dim command As SqlCommand = New SqlCommand(sql, conn)

                command.Parameters.AddWithValue("@FACTORY_NO", objBeanFactory.FACTORY_NO)

                command.ExecuteNonQuery()

            End Using

        Catch ex As Exception

            result = False

            MessageBox.Show(ex.Message.ToString(), "Error Message")

        End Try

        Return result

    End Function

End Class