ตอนที่ 18 : Visual Basic (VB.Net) เรียกใช้ EXEC SQL Server Stored Procedure

EXEC procedure_name agr1,agr2,...

CREATE PROCEDURE [dbo].[getCustomer]
	@pCountryCode	VARCHAR(2)
AS
BEGIN

	-- set customer
	SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode

END

Imports System.Data.SqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New SqlConnection()
        Dim objCmd As New SqlCommand()
        Dim dtAdapter As New SqlDataAdapter()

        Dim ds As New DataSet()
        Dim dt As DataTable
        Dim strConnString As String, strSQL As String

        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " & _
            " Max Pool Size=400;Connect Timeout=600;"

        strSQL = "EXEC getCustomer 'US' "

        objConn.ConnectionString = strConnString
        objConn.Open()
        objCmd.Connection = objConn
        objCmd.CommandText = strSQL
        objCmd.CommandType = CommandType.Text

        dtAdapter.SelectCommand = objCmd

        dtAdapter.Fill(ds)
        dt = ds.Tables(0)

        For i As Integer = 0 To dt.Rows.Count - 1
            Console.Write(dt.Rows(i)("CUSTOMER_ID"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("NAME"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("EMAIL"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("COUNTRY_CODE"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("BUDGET"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("USED"))
            Console.Write("")
        Next

        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing
    End Sub

End Module

Imports System.Data.SqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New SqlConnection()
        Dim objCmd As New SqlCommand()
        Dim dtAdapter As New SqlDataAdapter()

        Dim ds As New DataSet()
        Dim dt As DataTable
        Dim strConnString As String, strStored As String

        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " & _
            " Max Pool Size=400;Connect Timeout=600;"

        strStored = "getCustomer"
        objCmd.Parameters.Add(New SqlParameter("@pCountryCode", "US"))

        objConn.ConnectionString = strConnString
        objConn.Open()
        objCmd.Connection = objConn
        objCmd.CommandText = strStored
        objCmd.CommandType = CommandType.StoredProcedure

        dtAdapter.SelectCommand = objCmd

        dtAdapter.Fill(ds)
        dt = ds.Tables(0)

        For i As Integer = 0 To dt.Rows.Count - 1
            Console.Write(dt.Rows(i)("CUSTOMER_ID"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("NAME"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("EMAIL"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("COUNTRY_CODE"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("BUDGET"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("USED"))
            Console.Write("")
        Next

        dtAdapter = Nothing
        objConn.Close()
        objConn = Nothing
    End Sub

End Module

CREATE PROCEDURE [dbo].[insertCustomer]
	@pCustomerID	VARCHAR(4),
	@pName			VARCHAR(50),
	@pEmail			VARCHAR(50),
	@pCountryCode	VARCHAR(2),
	@pBudget		DECIMAL(18,2),
	@pUsed			DECIMAL(18,2)
AS
BEGIN

	-- insert customer
	INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);

END

Imports System.Data.SqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New SqlConnection()
        Dim objCmd As New SqlCommand()
        Dim strConnString As String, strSQL As String

        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " & _
                " Max Pool Size=400;Connect Timeout=600;"

        Dim sCustomerID As String = "C005"
        Dim sName As String = "Fun Wipa"
        Dim sEmail As String = "[email protected]"
        Dim sCountryCode As String = "TH"
        Dim dBudget As Decimal = 1000000
        Dim dUsed As Decimal = 0

        strSQL = "EXEC insertCustomer '" & sCustomerID & "','" & sName & "' " & _
            ",'" & sEmail & "','" & sCountryCode & "','" & dBudget & "','" & dUsed & "' "

        objConn.ConnectionString = strConnString
        objConn.Open()
        objCmd.Connection = objConn
        objCmd.CommandText = strSQL
        objCmd.CommandType = CommandType.Text

        objCmd.ExecuteNonQuery()

        objConn.Close()
        objConn = Nothing
    End Sub

End Module

Imports System.Data.SqlClient
Imports System.Data

Module MyModule

    Sub Main()
        Dim objConn As New SqlConnection()
        Dim objCmd As New SqlCommand()
        Dim strConnString As String, strStored As String

        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " & _
                " Max Pool Size=400;Connect Timeout=600;"

        Dim sCustomerID As String = "C005"
        Dim sName As String = "Fun Wipa"
        Dim sEmail As String = "[email protected]"
        Dim sCountryCode As String = "TH"
        Dim dBudget As Decimal = 1000000
        Dim dUsed As Decimal = 0

        strStored = "insertCustomer"
        objCmd.Parameters.Add(New SqlParameter("@pCustomerID", sCustomerID))
        objCmd.Parameters.Add(New SqlParameter("@pName", sName))
        objCmd.Parameters.Add(New SqlParameter("@pEmail", sEmail))
        objCmd.Parameters.Add(New SqlParameter("@pCountryCode", sCountryCode))
        objCmd.Parameters.Add(New SqlParameter("@pBudget", dBudget))
        objCmd.Parameters.Add(New SqlParameter("@pUsed", dUsed))

        objConn.ConnectionString = strConnString
        objConn.Open()
        objCmd.Connection = objConn
        objCmd.CommandText = strStored
        objCmd.CommandType = CommandType.StoredProcedure

        objCmd.ExecuteNonQuery()

        objConn.Close()
        objConn = Nothing
    End Sub

End Module

        objCmd.Parameters.Add("@pCustomerID", SqlDbType.VarChar).Value = sCustomerID
        objCmd.Parameters.Add("@pName", SqlDbType.VarChar).Value = sName
        objCmd.Parameters.Add("@pEmail", SqlDbType.VarChar).Value = sEmail
        objCmd.Parameters.Add("@pCountryCode", SqlDbType.VarChar).Value = sCountryCode
        objCmd.Parameters.Add("@pBudget", SqlDbType.Decimal).Value = dBudget
        objCmd.Parameters.Add("@pUsed", SqlDbType.Decimal).Value = dUsed

CREATE PROCEDURE [dbo].[getCustomer]
	@pCountryCode	VARCHAR(2),
	@pRowFound	INT OUTPUT
AS
BEGIN

	-- set rowcount
	SELECT @pRowFound = COUNT(*) FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode

	-- set customer
	SELECT * FROM CUSTOMER WHERE COUNTRY_CODE = @pCountryCode

END

Imports System.Data.SqlClient
Imports System.Data

Module MyModule

    Sub Main()

        Dim objConn As New SqlConnection()
        Dim objCmd As New SqlCommand()
        Dim dtAdapter As New SqlDataAdapter()

        Dim ds As New DataSet()
        Dim dt As DataTable
        Dim strConnString As String, strStored As String

        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " & _
            " Max Pool Size=400;Connect Timeout=600;"

        strStored = "getCustomer"
        objCmd.Parameters.Add(New SqlParameter("@pCountryCode", SqlDbType.VarChar)).Value = "US" ' IN
        objCmd.Parameters.Add(New SqlParameter("pRowFound", SqlDbType.Int)).Direction = ParameterDirection.Output ' OUT

        objConn.ConnectionString = strConnString
        objConn.Open()
        objCmd.Connection = objConn
        objCmd.CommandText = strStored
        objCmd.CommandType = CommandType.StoredProcedure

        ' Get Select
        dtAdapter.SelectCommand = objCmd
        dtAdapter.Fill(ds)

        ' Get Result 
        ' objCmd.Parameters["pRowFound"].Value
        Console.WriteLine(String.Format("pRowFound = {0}", objCmd.Parameters("pRowFound").Value))
        Console.WriteLine("")

        ' Loop Data Table
        dt = ds.Tables(0)
        ' *** Cursor at 0 , 1 , 2, ...
        For i As Integer = 0 To dt.Rows.Count - 1
            Console.Write(dt.Rows(i)("CUSTOMER_ID"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("NAME"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("EMAIL"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("COUNTRY_CODE"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("BUDGET"))
            Console.Write(" - ")
            Console.Write(dt.Rows(i)("USED"))
            Console.WriteLine("")
        Next

        objConn.Close()
        objConn = Nothing

    End Sub

End Module

CREATE PROCEDURE [dbo].[insertCustomer]
	@pCustomerID	VARCHAR(4),
	@pName			VARCHAR(50),
	@pEmail			VARCHAR(50),
	@pCountryCode	VARCHAR(2),
	@pBudget		DECIMAL(18,2),
	@pUsed			DECIMAL(18,2),
	@pResult		INT OUTPUT,
	@pMessage		VARCHAR(500) OUTPUT
AS
BEGIN

	BEGIN TRY
		-- insert customer
		INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
		SET @pResult = @@ROWCOUNT;
		SET @pMessage = 'Insert Data Successfully';
	END TRY
	BEGIN CATCH
		SET @pResult = 0;
		SELECT @pMessage = ERROR_MESSAGE();
	END CATCH

END

Imports System.Data.SqlClient
Imports System.Data

Module MyModule

    Sub Main()

        Dim objConn As New SqlConnection()
        Dim objCmd As New SqlCommand()

        Dim strConnString As String, strStored As String

        strConnString = "Server=localhost;UID=sa;PASSWORD=;database=mydatabase; " & _
            " Max Pool Size=400;Connect Timeout=600;"
        objConn.ConnectionString = strConnString
        objConn.Open()

        Dim strCustomerID As String = "C005"
        Dim strName As String = "Fun Wipa"
        Dim strEmail As String = "[email protected]"
        Dim strCountryCode As String = "TH"
        Dim dBudget As Decimal = 100000
        Dim dUsed As Decimal = 0

        strStored = "insertCustomer"
        objCmd.Parameters.Add(New SqlParameter("pCustomerID", SqlDbType.VarChar, 4)).Value = strCustomerID ' IN
        objCmd.Parameters.Add(New SqlParameter("pName", SqlDbType.VarChar, 150)).Value = strName ' IN
        objCmd.Parameters.Add(New SqlParameter("pEmail", SqlDbType.VarChar, 150)).Value = strEmail ' IN
        objCmd.Parameters.Add(New SqlParameter("pCountryCode", SqlDbType.VarChar, 2)).Value = strCountryCode ' IN
        objCmd.Parameters.Add(New SqlParameter("pBudget", SqlDbType.VarChar)).Value = dBudget ' IN
        objCmd.Parameters.Add(New SqlParameter("pUsed", SqlDbType.VarChar)).Value = dUsed ' IN

        objCmd.Parameters.Add(New SqlParameter("pResult", SqlDbType.Int)).Direction = ParameterDirection.Output ' OUT
        objCmd.Parameters.Add(New SqlParameter("pMessage", SqlDbType.VarChar, 500)).Direction = ParameterDirection.Output ' OUT

        objCmd.Connection = objConn
        objCmd.CommandText = strStored
        objCmd.CommandType = CommandType.StoredProcedure

        objCmd.ExecuteNonQuery()

        ' Get Result 
        ' objCmd.Parameters["pResult"].Value
        Console.WriteLine(String.Format("pResult = {0}", objCmd.Parameters("pResult").Value))

        ' objCmd.Parameters["pMessage"].Value
        Console.WriteLine(String.Format("pMessage = {0}", objCmd.Parameters("pMessage").Value))

        objConn.Close()
        objConn = Nothing

    End Sub

End Module