1. Start Excel 2010, select the Data ribbon, and then from the options in the ribbon select Connections, as shown in Figure 16-1.
   

   Figure 16-1. Selecting the Excel Connections option within the workbook

2. This then displays a dialog that details all the connections that this Excel workbook has contained within it. At present there are no connections, and so the list is empty, as shown in Figure 16-2. Click Add.
   

   Figure 16-2. Displaying any existing connections

3. You will now see a new dialog that lists connections that exist on your computer, as well as connections that are known on the network. At this point, I have no known connections, as shown in Figure 16-3. If a connection existed, you could highlight it and click Add, or you click “Browse for more...” to search for it. This scenario will exist after the end of the exercise, when you will have created a connection for the workbook, and therefore you could add this connection in a subsequent worksheet in which you were working with the data. You also need to click “Browse for more…” to create a new connection.
   

   Figure 16-3. Displaying the known local and network connections

4. The next screen you will see is shown in Figure 16-4, where you can select a data source that Excel doesn’t know about, or in this instance you can click the New Source… button. This is what is required, so click that button now.
   

   Figure 16-4. Searching for other connections or creating a new source

5. From the dialog that is displayed, you can select Microsoft SQL Server from the options, as shown in Figure 16-5. Once selected, click Next.
   

   Figure 16-5. Microsoft SQL Server data connection selected

6. The next dialog allows you to enter the database server and instance name. In Figure 16-6, you will see I have entered my server and the APRESS_DEV1 instance name. I have also selected Windows Authentication. Enter your details, and click Next.
   

   Figure 16-6. Defining your SQL Server server and instance and connection method

   Note The main connection issues surround the server name being incorrectly typed. This should be the same value as the connection dialog in SSMS.

7. If your connection is successful, the next dialog in the wizard lists the databases that are found on the server. Select ApressFinancial, as shown in Figure 16-7.
   

   Figure 16-7. Choosing the correct database

8. Once the database is selected, you see a list of the tables and views that the account has access to, as shown in Figure 16-8. As you are connecting to the database with your dbo login, you will see all the objects. For this example, you will display the data from the view vw_SharePrices. Once you have chosen your table or view, click Next.
   

   Figure 16-8. Tables and views that can be used to display data

   Tip Figure 16-8 demonstrates why you must lock down tables and views correctly. The login being used can access tables directly, which is something you may not want. For example, if FAT-BELLY-SONYMSmith was connecting to ApressFinancial via Excel, then he would see only the CustomerDetails.Customers table and no views.

9. Figure 16-9 shows the .odc file that holds your database connection file name, and it allows you to place a description against that file. This is the final dialog in the wizard; therefore, click Finish.
   

   Figure 16-9. The file name and description for the connection

10. The wizard will close, and the connection will be added to the Workbook Connections window, as shown in Figure 16-10. It is possible to have more than one connection in a workbook, and therefore any further connections would also be listed here. Highlight the connection, and click Properties.
    

    Figure 16-10. All the connections defined for the Excel workbook

11. You should now see the screen as shown in Figure 16-11. The bottom half of the properties concerns OLAP (Analysis Services systems). The default options on the Usage tab are acceptable as you will not be refreshing the data on a timed basis or when the workbook is opened.
    

    Figure 16-11. Usage options for the connection

12. The three options of interest are as follows:

    • Enable background refresh: The Excel screen will retrieve the data in the background. You will see a spinning world while the data is being brought back. The Excel workbook can accept input while the refresh is occurring.

    • Refresh every …: The data can be refreshed while the workbook is open at specific periods of time. If you enable this option, then you need to ensure that you are not putting an unnecessary strain on SQL Server resources with a complex, long-running query.

    • Refresh data when opening the file: When the workbook is opened, then a refresh of the data is performed. This is ideal for the start of the day to retrieve any changes completed overnight.

13. Moving to the Definition tab as shown in Figure 16-12, there is some useful information and some warnings that I discussed prior to the exercise with the name of the connection and “Command text” text box. Click OK, and then click Close to close the Connection window.
    

    Figure 16-12. Definition details allowing new command text to be entered

14. The options on the Definition tab are as follows:

    • Connection type: The type of connection Excel will use to connect itself with SQL Server; this is a specific type for Office applications. You will see different types in other sections within the chapter.

    • Connection file: The file name and location storing the connection details

    • Connection string: The file connection string used by Excel to connect to SQL Server

    • Command type: How you are accessing the data; if you were executing a T-SQL command, you would alter this option to Text. You will see these different command types within the .NET try-it-outs.

    • Command text: The command text you will be executing

    Tip The connection string will be useful to be aware of in the remaining examples as a reference point.

15. You should now be back at the Excel workbook. It is possible to get back to the connection dialog shown in Figure 16-3 by clicking the Existing Connections icon, shown in Figure 16-13. Click the icon.
    

    Figure 16-13. The Existing Connections icon

16. Figure 16-14 shows the connections I have and the existing connection. Highlight the top connection, and click Open.
    

    Figure 16-14. Selecting the new connection for use

17. You are now presented with the Import Data screen as shown in Figure 16-15, where you can define how the data is imported and where it is placed. Clicking Properties would take you back to Figure 16-11. Leave the options as they are, and click OK.
    

    Figure 16-15. How to retrieve the data and where to place it

18. Excel will now execute the code in the “Command text” text box and display the information based on the options in Figure 16-15. Excel also adds some filtering and coloring, as shown in Figure 16-16. There are options in the Design ribbon that allow you to modify the look and feel of the data.
    

    Figure 16-16. Displaying some of the returned data within Excel

You can now save the Excel sheet and send it to your users, and as long as they have Windows authentication logins to your server and they are users within your database, then they should be able to refresh your data.

1. First of all, start Microsoft Excel and enter data in to the first sheet within the workbook, as shown in Figure 16-17. Column A details the parameters that the stored procedure will use. This information is within the sheet for your reference only. Column B holds the values that will be used for the insertion.

   Note Don’t forget that this is downloadable from Apress or from my web site, www.fat-belly.com.

   

   Figure 16-17. The values in the Excel sheet to insert a new customer

2. You can get to the Visual Basic Editor (VB Editor) by pressing Alt+F11; however, there is a Developer ribbon within Excel that would be better to have displayed. From Excel click File and then Options, which opens up a dialog. Select Customize Ribbon, and from the list box on the right select Developer, as shown in Figure 16-18. Once complete, click OK.
   

   Figure 16-18. Selecting the Developer ribbon from the Excel options

3. Navigate to the Developer ribbon so that it is the active ribbon. On the left, there is an icon labeled Visual Basic, as shown in Figure 16-19. Click this icon.
   

   Figure 16-19. Visual Basic icon in the Developer ribbon

4. Excel will open up a new application, the VB Editor, and you will see a new application on your Windows toolbar. The editor should look like Figure 16-20.
   

   Figure 16-20. The VB Editor IDE

5. If you have ever used Visual Basic, then the editor is very much like it. On the left in the Project Explorer, you will see the three sheets that exist within the workbook and the workbook itself. These are macro areas for those specific areas. If you have code that is specific to a sheet, then you would place the code in that sheet. This is not what is wanted in the example, as the code you will be creating could come from any sheet.
6. A basic Excel VBA project will have the basic functionality enabled. Connecting to SQL Server is extended functionality and is required to be added. From the menu within the VB Editor, select Tools References.
7. A dialog is displayed with a very long list of references. The references listed are extensions that can be added to a VBA project. Scroll through the list until you find the reference Microsoft ActiveX Data Objects Library, and find the latest version. On my system, this is version 6.0. Select this as shown in Figure 16-21.
   

   Figure 16-21. Microsoft ActiveX Data Objects 6.0 Library

8. There is one last action to perform before you start writing code, and that is to add a module to the VB Editor to write the code into. From the Project Explorer, ensure that it has the focus, right-click, and from the pop-up menu select Insert Module, as shown in Figure 16-22.
   

   Figure 16-22. Inserting a new module into your VBA project

9. Finally you can now enter some code. First of all, you need to define a routine, as it is a routine that will hold your code. You then have two variables defined, one to hold the ADO connection and one for holding any errors returned from executing the T-SQL code:
   Sub RetrieveData()
    Dim dbConnection As New ADODB.Connection
    Dim anError As ADODB.error
10. The next statement is to define the connection string. One of two nonobvious parameters is the Provider, which defines the method of connection to the database, and in this instance you are using the SQL Server–specific OLEDB data provider. The final parameter defines that you are using Windows authentication.
    sConnString = "Provider=SQLOLEDB;" + _
    "server=FAT-BELLY-SonyAPRESS_DEV1;" + _
    "database=ApressFinancial;Integrated Security=sspi"
11. The default for VBA code is that you don’t need to define a variable before using it.
12. Now that you have your connection string defined, open the connection between the VBA code and SQL Server.
    dbConnection.Open (sConnString)
13. The next step builds up the SQL to execute. This is achieved by building up a string containing the stored procedure and each parameter.
        sSQL = "CustomerDetails.apf_InsertCustomer "
    sSQL = sSQL + "@CustTitle=" + IIf(Len(Trim(Str(Range("B1").Value)) > 0) _
        And Range("B1").Value <> Empty, Str(Range("B1").Value), "NULL")
    sSQL = sSQL + ", @FirstName=" + IIf(Len(Trim(Range("B2").Value) > 0) _
        And Range("B2").Value <> Empty, "'" + Range("B2").Value + "'", "NULL")
    sSQL = sSQL + ", @CustInitials=" + IIf(Len(Trim(Range("B3").Value) > 0) _
        And Range("B3").Value <> Empty, "'" + Range("B3").Value + "'", "NULL")
    sSQL = sSQL + ", @LastName=" + IIf(Len(Trim(Range("B4").Value) > 0) _
        And Range("B4").Value <> Empty, "'" + Range("B4").Value + "'", "NULL")
    sSQL = sSQL + ", @AddressLine1=" + IIf(Len(Trim(Range("B5").Value) > 0) _
        And Range("B5").Value <> Empty, "'" + Range("B5").Value + "'", "NULL")
    sSQL = sSQL + ", @AddressLine2=" + IIf(Len(Trim(Range("B6").Value) > 0) _
        And Range("B6").Value <> Empty, "'" + Range("B6").Value + "'", "NULL")
    sSQL = sSQL + ", @AddressLine3=" + IIf(Len(Trim(Range("B7").Value) > 0) _
        And Range("B7").Value <> Empty, "'" + Range("B7").Value + "'", "NULL")
    sSQL = sSQL + ", @TownOrCity=" + IIf(Len(Trim(Str(Range("B8").Value)) > 0) _
        And Range("B8").Value <> Empty, Str(Range("B8").Value), "NULL")
    sSQL = sSQL + ", @ZipCode=" + IIf(Len(Trim(Range("B9").Value) > 0) _
        And Range("B9").Value <> Empty, "'" + Range("B9").Value + "'", "NULL")
    sSQL = sSQL + ", @USState=" + IIf(Len(Trim(Str(Range("B10").Value)) > 0) _
        And Range("B10").Value <> Empty, Str(Range("B10").Value), "NULL")
    sSQL = sSQL + ", @AccountTypeId=" + IIf(Len(Trim(Str(Range("B11").Value)) > 0) _
        And Range("B11").Value <> Empty, Str(Range("B11").Value), "NULL")
14. A new line in VB/VBA is generated by the use of an underscore.
15. It is now possible to execute the SQL to insert the new customer:
       dbConnection.Execute (sSQL)
16. Once the code has been executed, it will either succeed or fail. A failure could be that a parameter is incorrectly named, the stored procedure doesn’t exist, or the account running the query does not have the permissions. If an error has occurred, then the Errors collection will be populated and you can inspect the collection count.
        If dbConnection.Errors.Count > 0 Then
17. Any errors come in a collection, and to navigate a collection, you need to use a For Each. Any errors found will be printed out to the Immediate window for you to inspect. Close the loop, close the If, and close the subroutine.
            For Each anError In dbConnection.Errors
            Debug.Print ("An error has occurred executing the following ")
            Debug.Print (sSQL)
            Debug.Print ("The error is...")
            Debug.Print (anError.Description)
        Next
    Else
        Debug.Print ("Record inserted")
    End If
End Sub
18. To see any output from the routine, whether it is the errors or that the record has been successfully entered, you need to have the Immediate window displayed, which is not displayed by default. This is remedied by selecting from the menu in the VB Editor, View Immediate Window, as shown in Figure 16-23.
    

    Figure 16-23. Displaying the Immediate window within the VB Editor

19. Finally it’s time to run the code. The two requirements that enable you to avoid problems with the code used here are to ensure that the Excel sheet that has the data to insert has the focus and that the routine in the VB Editor has focus. Within the VB Editor, you should have the standard toolbar, and there is a Run icon that looks the same as the Execute button in SSMS. It is highlighted in Figure 16-24. Click Run or press F5.
    

    Figure 16-24. Run button

20. Providing you have no errors, you should see that the record is inserted, as shown in Figure 16-25. Any errors would be displayed in the same window.
    

    Figure 16-25. A successful insertion of a new customer

21. One area where you may have problems is a typing error within the data in Figure 16-17 causing a Type Mismatch error.

1. Start Visual Studio, and from the menu select File New Project, or from Visual Basic Express select New Project from the Start page.
2. From the New Project dialog, ensure the Visual Basic templates node in the Installed Templates explorer is expanded, and select Windows if you are using Visual Studio. From the list of templates in any Visual Studio version, select Console Application, as shown in Figure 16-28 (taken from Visual Studio Professional). You may find your Installed Templates slightly differ depending on the language chosen while installing Visual Studio, and they will differ in the Express edition, but the Console Application will be there.
   

   Figure 16-28. Selecting Visual Basic Console Application

3. With the Express edition, you can create only a name. If using a non-Express edition, give the solution a name, in this instance ReturnAValue, and navigate to a location that you want to hold your code. The solution name will prepopulate with the value in the Name text box; leave it as it is. You should have at the bottom of your New Project dialog the values as shown in Figure 16-29. (In the Express edition, you will see something very close to Figure 16-29, except the Solution combo box does not exist when you save the project at the end of the example.) Click OK.
   

   Figure 16-29. Naming the Visual Basic Application

4. After a moment, Visual Studio should look like Figure 16-30 no matter what edition you use. You will have an explorer on the left called Toolbox, which you can close if you wish. The middle screen is the code window, and the explorer on the right is Solution Explorer, detailing the contents of your Visual Studio solution.
   

   Figure 16-30. Visual Studio detailing the code for the Module1.vb

5. It is time to add the directives, mentioned earlier, to the code to qualify code used for the access. Add the System.Data and System.Data.SqlClient namespaces into the code in the Module1.vb. Both of these namespaces live in the System.Data library. The code at the start of Module1.vb should now look like Figure 16-31.
   

   Figure 16-31. Importing the SqlClient

6. After the SqlClient is imported, within the main subroutine you will test that one and only one parameter is passed to your application. If this is not the case, then an error will be printed to the console.
   Imports System.Data.SqlClient
Module Module1
    Sub Main(ByVal args() As String)
        If args.Length <> 1 Then
            Console.WriteLine("Invalid arguments. You only need 1 argument")
        End If
7. The Sub statement defines a subroutine, which is a set of commands and statements that perform a set of actions. The information between the curved brackets is known as parameters. The Sub Main() in the following code is defined as the entry point for your program and is the first line of code that will be executed. Using args() as the parameter indicates that parameters can be passed into your program.
8. The next line of code will define a variable as a string, and the string will be the connection details to SQL Server. The connection string will contain the name of the server, instance, database, and that you are using Windows authentication.
           Dim connectionString As String = + _
                "Server=FAT-BELLY-SonyAPRESS_DEV1;" + _
                "Database=ApressFinancial;Trusted_Connection=True;"
9. If you want to use SQL Server authentication, then the connection string would be something like the following with your own User and Pwd:
           Dim connectionString As String = __
                "Server=FAT-BELLY-SonyAPRESS_DEV1;" + _
                "Database=ApressFinancial;Trusted_Connection=False;" + _
                "User=MySQLServerLogin;Pwd=T3st!t;"
10. You now need to define a variable that will create and hold the connection object that has the connection details within it. The connection is created but has not been opened at this point.
            Dim databaseConnection As SqlConnection =
                New SqlConnection(connectionString)
11. It is now possible to open the connection between your code and the database so that you can execute some T-SQL.
            databaseConnection.Open()
12. To execute code, you need to create a variable that will hold an SQL command object. The command object will hold the text to execute, which in this case is the parameter passed into your program. The SQL command object also needs to know which connection object to use as you could have multiple connections to different servers open.
            Dim tsqlCommand = New
                SqlCommand(args(0), databaseConnection)
13. A command object could be one of three different types: plain text, the name of a table, or a stored procedure. In this instance, although you are executing a stored procedure, it could also be plain text that is passed in.
                    tsqlCommand.CommandType = CommandType.Text
14. The SQL code can now be executed and the value returned. By using the ExecuteScalar method, you are indicating to the SqlClient ADO.NET driver that you are interested only in the first value returned. If the stored procedure returned more than one column of information, all other columns would be ignored. The returned value is placed into a variable. As you don’t know the type of data returned, even a NULL value, place it into an Object data type, which can be tested and transposed as required.
            Dim returnedValue As Object = tsqlCommand.ExecuteScalar()
15. The first test is to see if the value returned is a NULL value. If it is, then convert the value to a string so that it can be displayed.
            If IsDBNull(returnedValue) Then
            returnedValue = "NULL"
        End If
16. You can now output the value to the command prompt console with instructions on how to finish execution by pressing the Enter key.
            Console.WriteLine("The returned value is " + returnedValue.ToString())
        Console.WriteLine("Press <ENTER> to continue")
        Console.ReadLine()
17. Once you are finished, you close the connection. For efficiency, you can move the following code to be placed immediately after the code in point 12.
            databaseConnection.Close()
18. The final lines of code close the subroutine and the module. The code is now complete.
        End Sub
End Module
19. While testing and debugging the code within Visual Studio, it is possible to define parameters to be passed in at the time of execution. This is achieved by selecting the Project menu and then the name of the project, which in this case is ReturnAValue Properties…, as shown in Figure 16-32.
    

    Figure 16-32. Navigate to the project properties

20. You are now presented with a screen with all of the properties of the project. You need to move to the Debug tab, as shown in Figure 16-33. You will also see in the figure a stored procedure and value, which is the parameter to the stored procedure, in the Command Line Arguments text box. This is the value that will be passed in to the args() parameter in the main subroutine. As there is only one parameter, you need to surround the information with double quotation marks. Once you are happy, click OK.
    

    Figure 16-33. Project properties with the command line entered

21. The command-line arguments would be as follows:
    "CustomerDetails.apf_CustClearedBalance 2"
22. You can now execute your code by pressing F5. A command prompt should be displayed, the stored procedure executed, and the cleared balance returned, as shown in Figure 16-34.
    

    Figure 16-34. Running the code and displaying the returned value

23. What has happened is that Visual Studio has executed your program, and because you are outputting information to the console via the Console.WriteLine statement, Visual Studio will create a command-line console and output the information you have requested. What follows is an alternative execution method.
24. You can also execute your code outside of Visual Studio. You can do with this one of several methods, but I will show you how to run the code from a command prompt. Click the Windows Start button and in the text box that is displayed above the Start button, type cmd and press Enter, as shown in Figure 16-35.
    

    Figure 16-35. Starting the command prompt

25. From the command prompt, you can either run the code or navigate to where the code was compiled by Visual Studio. When building the solution, I gave you the name of the solution, and then the project. The compiled code is then placed in the bin subfolder and then either the Release or Debug subfolder. The following code will change the directory to my Debug folder location.
    Cd C:UsersRobinDewsonDocumentsBegSQL2012Chapter16
vb-netReturnAValueReturnAValueinDebug
26. You can now run the executable with the parameter as before, as shown in the following code.
    ReturnAValue.exe "CustomerDetails.apf_CustClearedBalance 2"
27. Figure 16-36 shows the change of directory, then the code being executed, and finally the result displayed.
    

    Figure 16-36. Running the code external to Visual Studio

28. Press the Enter key to complete the execution.

And that’s your first VB.NET application complete.

1. Start Visual Studio, and from the menu select File New Project.
2. From the New Project dialog, ensure the Visual C# templates node in the Installed Templates explorer is expanded, and select Windows. From the list of Templates, select Console Application, as shown in Figure 16-37. You may find your Installed Templates slightly differ depending on the language chosen while installing Visual Studio.
   

   Figure 16-37. Selecting Visual C# Console Application

3. Give the solution a name, in this instance RunAParameterizedStoredProcedure, and navigate to a location that you want to hold your code. The solution name will prepopulate with the value in the Name text box; leave it as it is. You should have at the bottom of your New Project dialog the values as shown in Figure 16-38. Click OK.
   

   Figure 16-38. Giving the application a name

4. After a moment, Visual Studio should look like Figure 16-39. You will have an explorer on the left, called Toolbox, which you can close if you wish. The middle screen is the code window, and the explorer on the right is Solution Explorer, detailing the contents of your Visual Studio solution.
   

   Figure 16-39. Visual Studio detailing the code for Program.cs

5. To access a SQL Server database, you need to load into the solution a .NET library that has code built that will perform the low-level link between your code and the database. Visual Studio will have completed this automatically with the type of project, and you can check this in the References node in Solution Explorer, which you can expand as shown in Figure 16-40.
   

   Figure 16-40. Visual Studio detailing the code for Program.cs

6. It is time to bring in the directives to the code to qualify code used for the access. Add the System.Data and System.Data.SqlClient namespaces into the code in Program.cs. Both of these namespaces live in the System.Data library. The code at the start of Program.cs should now look like Figure 16-41.
   

   Figure 16-41. The full set of namespaces imported

7. There are three variables that will be used to hold the returned values from the stored procedure. Good practice is to define them at the top of your code. In this instance, they are required only within the Main method, and so they will be defined there. The code will now look like the following:
   using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace RunAParameterizedStoredProcedure
{
    class Program
    {
        static void Main(string[] args)
        {
            string clearedBalance = "";
            string unClearedBalance = "";
            string returnValue = "";
8. The next stage is to define a variable with the connection details to the SQL Server database. The following connection details are for a Windows authentication. For the Server parameter, you have to define the server and the instance.
               try
            {
                string connectionString = @"Server=FAT-BELLY-SonyAPRESS_DEV1;" +
                                "Database=ApressFinancial;Trusted_Connection=True;";
9. The @ sign is a C# indicator that allows to be included in the string. A in C# is an escape character, and without the @ sign then you would need to place double \ for a single to exist in the string.
10. Now that you have the connection string, you can create a new database connection that will link your application to SQL Server using the connection settings. Like VB.NET, the connection is not yet open at this point.
                    SqlConnection databaseConnection = new SqlConnection(connectionString);
11. You can now open the connection between the two.
                    databaseConnection.Open();
12. Once the connection is open, you can execute the T-SQL code. In this instance, it will be the CustomerDetails.apf_CustBalance stored procedure.
                    SqlCommand tsqlCommand = new
SqlCommand("CustomerDetails.apf_CustBalance",databaseConnection);
13. This time, unlike the VB.NET example, you will explicitly define the command as a stored procedure, as you want to inspect the return value parameter, which is the first parameter.
                    tsqlCommand.CommandType = CommandType.StoredProcedure;
                tsqlCommand.Parameters.Add("ReturnValue", SqlDbType.Int);
                tsqlCommand.Parameters[0].Direction = ParameterDirection.ReturnValue;
14. The second parameter is the customer ID, which is an input parameter.
                    tsqlCommand.Parameters.Add("@CustomerId", SqlDbType.Int);
                tsqlCommand.Parameters[1].Direction = ParameterDirection.Input;
                tsqlCommand.Parameters[1].Value = 2;
15. The stored procedure has two output parameters, one for the cleared balance and one for the uncleared balance. These are defined next.
                    tsqlCommand.Parameters.Add("@ClearedBalance", SqlDbType.Money);
                tsqlCommand.Parameters[2].Direction = ParameterDirection.Output;
                tsqlCommand.Parameters.Add("@UnclearedBalance", SqlDbType.Money);
                tsqlCommand.Parameters[3].Direction = ParameterDirection.Output;
16. As no data that form a result set are returned from the query, only parameter values, you would execute the stored procedure as a nonquery.
                    tsqlCommand.ExecuteNonQuery();
17. Once the code is executed, you can inspect the relevant parameters to retrieve the returned values.
                    clearedBalance = tsqlCommand.Parameters[2].Value ==
DBNull.Value ? "NULL" : tsqlCommand.Parameters[2].Value.ToString();
                unClearedBalance = tsqlCommand.Parameters[3].Value ==
DBNull.Value ? "NULL" : tsqlCommand.Parameters[3].Value.ToString();
                returnValue = tsqlCommand.Parameters[0].Value ==
DBNull.Value ? "NULL" : tsqlCommand.Parameters[0].Value.ToString();
18. It is now possible to close the connection as the values are held in local variables.
                    databaseConnection.Close();
19. You then have some error handling code but also the ability to display the returned values.
                }
            catch (Exception exc)
            {
                Console.WriteLine("Problems running the T-SQL" + exc.Message);
            }
            Console.WriteLine("The return values are " +
                "Cleared Balance = " + clearedBalance +
                " Uncleared Balance = " + unClearedBalance +
                " Returned Value = " + returnValue);
20. The final lines of code will hold the command prompt screen open until the Enter key is pressed.
                Console.WriteLine("Press <ENTER> to finish....");
            Console.ReadLine();
21. If you now press F5, this will execute the code. A command prompt should be displayed with the results from Customer ID 2, as shown in Figure 16-42.
    

    Figure 16-42. The code executed and a command prompt displayed

22. You can achieve the same by executing the compiled exe. The location is based on the folder location, the solution, and the project name when you created the project at the start of the exercise.
    C:UsersRobinDewsonDocumentsBegSQL2012Chapter16
c-sharpRunAParameterizedStoredProcedure
RunAParameterizedStoredProcedureinDebug
RunAParameterizedStoredProcedure.exe

1. First of all, the stored procedure to return a scalar value needs to be built, if you have not already built the stored procedure when looking at the examples for other development languages in this chapter. Enter and execute the following code in SQL Server Management Studio.
   CREATE PROCEDURE CustomerDetails.apf_CustClearedBalance
    @CustomerId int
AS
BEGIN
    SELECT ClearedBalance
      FROM CustomerDetails.Customers
     WHERE CustomerId = @CustomerId
END
2. Eclipse should have been downloaded and extracted somewhere on your computer. The location doesn’t really matter as it is self-contained. I have a specific folder for my downloads, and within this I have my Eclipse installation, as shown in the code here. Find your location and start Eclipse.
   C:UsersRobinDewsonDownloadsEclipseeclipseeclipse.exe
3. Eclipse should start and ask for the name of a workspace. This is just somewhere for the projects in your solution to be stored. The location doesn’t matter, but the final folder I have called ReturnAScalarValue. The full path I used follows, and you can see in Figure 16-43 how this looks. Click OK.
   C:UsersRobinDewsonDocumentsBegSQL2012Chapter16JavaReturnAScalarValue
   

   Figure 16-43. Defining the workspace for the Java project

4. You are now presented with Eclipse opened and a Welcome tab, as shown in Figure 16-44. Close this tab by clicking the cross next to Welcome.
   

   Figure 16-44. The Welcome screen that can be closed

5. You are now presented with an empty development environment not too dissimilar to Visual Studio and SQL Server Management Studio. At the moment, you have an empty workspace and therefore you need to create a Java project to build the example. From the menu, select File New Java Project, as demonstrated in Figure 16-45.
   

   Figure 16-45. Creating a new Java project

6. This will start a new Java project wizard, where you need to enter a project name and select the Java Runtime Engine (JRE) to use. I have used the same name as the workspace, but this is not mandatory. The example also uses the 1.6 JRE version. The name and the default options, which are the options you should use, are shown in Figure 16-46. Click Next.
   

   Figure 16-46. Ability to enter details for a new Java project

7. You can develop for earlier and later versions. If you develop for a Java version prior to 1.5, be sure you use the version-appropriate JAR file mentioned in the following paragraph.
8. The next stage of the wizard is to inform Eclipse of other projects you want to use and the order of how those projects would be built, but for this example you need to let Eclipse know of any external libraries to use when compiling the code. If you are using a JRE of 1.5 or above, then you would need to add sqljdbc4.jar in the next step, otherwise it will be sqljdbc.jar. You will have downloaded and extracted these earlier.
9. To add the relevant jar, click Add External JARs…, navigate to where sqljdbc4.jar exists, select it, and click OK. It should now be loaded and visible, as shown in Figure 16-47. Click Finish.
   

   Figure 16-47. Adding the SQL JDBC library

10. You now have an Eclipse IDE with a few items in Package Explorer, which is similar to Visual Studio Solution Explorer. You now need to add a class for your program code. From the project ReturnAScalarValue in Package Explorer, right-click, select New, and then Select Class, as shown in Figure 16-48.
    

    Figure 16-48. Selecting the option to create a new class

11. You are now presented with the New Java Class window. The source folder will remain the same, and the package will remain blank. In the Name field, name the class “main” as this is the main program. You can call it by a different name if you wish, but you should select the first option from the check boxes within “Which method stubs would you like to create?”. The third option should already be selected. Figure 16-49 shows the class I generated. When you are happy, click Finish.
    

    Figure 16-49. Detailing the new Java class and indicating that it is the main point of entry

12. A new file is added to Package Explorer, but before you start entering code, there is one more library that needs to be added, and this library allows for a Windows authentication connection to your SQL Server database. It is not possible to add this prior to this point, as it forms part of the runtime environment and a class is required to exist before you can add the library.
13. From the toolbar in Eclipse, you will see a bug-like icon, which is the Run as Debug option. Click the black down-pointing arrow next to open the drop-down menu shown in Figure 16-50. From the menu, select Debug Configuration…
    

    Figure 16-50. Creating the debug configuration

14. You will now see a screen where you can define configuration details for your package. As you are building a Java application, either highlight Java Application from the options on the left and click the New Icon from the toolbar above the options, as shown in Figure 16-51, or just double-click the Java Application option. You will now have a debug configuration for your package.
    

    Figure 16-51. Defining that the debug configuration is a Java application

15. Within the configuration, you can define runtime arguments. You can also define a runtime change to your CLASSPATH within Windows if you need Eclipse at runtime to find libraries external to Eclipse. For this package, you need to select the Arguments tab and add the following path to the virtual machine (VM) arguments, as shown in Figure 16-52, which is the path where sql_auth.dll resides. This .dll is part of the SQLJDBC download. Click Apply and then click Close.
    -Djava.library.path=C:UsersRobinDewsonDownloads
sql-jdbcsqljdbc_4.0enuauthx64

    Note A virtual machine (VM) is a platform-independent execution environment that is used to run your Java code.

    

    Figure 16-52. Detailing the virtual machine arguments for the Windows authentication .dll

16. You are now ready to start entering the code for the application. Eclipse should look something like Figure 16-53, with the main.java class open, ready for coding.
    

    Figure 16-53. The start of your Java code

17. The first line of code will be inserted as the first line prior to the public class main line. The import statement is used to import a Java package into your class, similar to using an import in .NET. This will import the SQL package that is used to build the SQL code statements.
    import java.sql.*;
18. Within the main class, you will define three variables for the returned data, the database connection, and the SQL to execute. These variables are added to the main Java class and the main() method.
    public class main {
    /**
    * @param args
    */
    public static void main(String[] args) {
        // TODO Auto-generated method stub

        ResultSet returnedData = null;
        Connection databaseConnection = null;
        PreparedStatement prepStatement = null;
19. The main() method is the default way to start a Java application.
20. The code used to access and work with SQL Server is surrounded with a try…catch block. After this is defined, you define your connection string to your SQL Server, the instance, the database, and that you are using Windows authentication. The first option defines that JRE should use the SQL Server JDBC driver.
    try {
String connString = "jdbc:sqlserver://FAT-BELLY-Sony\" +
    "APRESS_DEV1;databaseName=ApressFinancial;" +
    "integratedSecurity=true";
21. Moving to the next line, you are required to inform the JRE to dynamically load the JDBC driver at runtime. This is the sqljdbc4.jar.
    Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
22. The dot notation name is the full package name for the driver. Once you start building larger Java applications, you will develop a package hierarchy, which traditionally but not mandatorily will start with com, then your organization, the top-level package, and then down through the project hierarchy.
23. The next step is to create a database connection using the connection string defined in step 15.
    databaseConnection = DriverManager.getConnection(connString);
24. You now define the stored procedure to execute with a ? at the end for the input parameter. If you had more than one parameter, you would separate each parameter with a comma.
    String sqlToExecute = "CustomerDetails.apf_CustClearedBalance ?";
25. It is possible to execute code against a database using one of two methods. For this example, I will use the prepareStatement method against the connection, which takes the SQL and prepares it for parameters and then to execute against SQL Server.
    prepStatement = databaseConnection.prepareStatement(sqlToExecute);
26. Now that you have the prepared statement built, you can define the values for the parameters. You would use one of several methods, depending on the data type to pass across. As CustomerId is an integer, you need to use setInt and then define the parameter number, in this case 1, and then the value. I have chosen to output the value from CustomerId 2.
    prepStatement.setInt(1, 2);
27. Once the parameters are defined, you can execute the query against the database.
    returnedData = prepStatement.executeQuery();
28. If you recall, Java returns either no data or a data record set of data. The next steps are to loop around the data, output the returned value, and print the result to the console. Any errors will be caught in the catch. The final actions are to clean up and to remove the memory used for the record set, the prepared SQL, and finally the connection.
    while (returnedData.next()) {
    System.out.println("The return value was " + returnedData.getString(1));
}
}
catch (Exception e) {
    e.printStackTrace();
}
finally {
    if (returnedData != null)
    try {
        returnedData.close();
    }
    catch(Exception e) {}
    
    if (prepStatement != null)
    try {
        prepStatement.close();
    }
    catch(Exception e) {}
    if (databaseConnection != null)
    try {
        databaseConnection.close();
    }
    catch(Exception e) {}
}
}
}
29. Your code is now complete, and all the options to execute the code have been defined. You can now step through your code by clicking the Debug icon you saw earlier at the start of the exercise, which will start the main class. A dialog will appear saying you will be switching modes. Click OK. Your code should now run through, and you will see Eclipse now looking like Figure 16-54, with the result of the stored procedure call in the Console window.
    

    Figure 16-54. Eclipse in Debug mode

Eclipse is now in Debug mode, as indicated in the top right of the screenshot, where the Debug toolbar button is highlighted. You can switch back to Java mode by clicking the toolbar button next to Debug. You can also put breakpoints in the bar to the left of the code and step through the code as you can with Visual Studio.