Convert .NET Data Provider for Teradata C# samples to VB or C++

Blog
The best minds from Teradata, our partners, and customers blog about whatever takes their fancy.
Teradata Employee

This blog describes how to convert .NET Data Provider for Teradata Learning Examples from C# to VB or C++.

.NET Data Provider for Teradata product installs Learning Examples in "<.NET Data Provider Installation Directory>\samples\LearningExamples" directory (e.g. "C:\Program Files\Teradata\NET Data Provider for Teradata\13.01.00\samples\LearningExamples"). These samples are written in C# language and we will ship VB samples some time in future.

But there is an easy way to convert the C# samples to VB as outlined below:

  1. Download and install ".NET Reflector" from http://www.red-gate.com/products/dotnet-development/reflector/.

  2. Compile one C# sample; see "<.NET Data Provider Installation Directory>\ samples\LearningExamples\Readme.doc" for details.

  3. Use ".NET Reflector" to open the resulting executable.

  4. Find the "Main(String[]): Void" method; right click and select Disassemble.

  5. Select "Visual Basic", "C#" or "MC++" from the toolbar.

For example I used the above procedure to convert:

static void Main(string[] args)
        {
            try
            {
                System.Console.WriteLine("Sample T20303TD: \n");
                System.Console.WriteLine(" Looking for .NET Data Provider for Teradata...");

                // Attempting to connect to Teradata
                System.Console.WriteLine(" Attempting to open a connection to Teradata via " +
                                         "the .NET Data Provider... \n");

                // Create a TdConnection object. A Connection
                // represents a session with a specific database. Within the context
                // of a Connection, SQL commands are executed and results are returned.
                // Creating and opening a database connection with the given connection string.               

                TdConnection con = new TdConnection(Constants.sConnection);
                con.Open();

                System.Console.WriteLine(" User " + Constants.sUser + " connected.");
                System.Console.WriteLine(" Connection to Teradata established. \n");

                try
                {
                    // Create a TdCommand object.  A command object executes
                    // SQL commands on a specified Connection.  In order to specify which
                    // connection, set its connection property.  Command.ExecuteReader will return
                    // a TdDataReader which is used to read the result of a SQL command.
                    // Command.ExecuteNonQuery will perform the specified SQL command but
                    // will not return a reader.

                    TdCommand cmd = con.CreateCommand();
                   
                    try
                    {
                        // The following code will perform a SELECT query
                        // on the table.
                        cmd.CommandText = Constants.sSelect;

                        TdParameter par1 = cmd.CreateParameter();

                        par1.Value = Constants.sDept;
                        par1.ParameterName = "Dept";
                        par1.DbType = DbType.String;
                        par1.Size = Constants.sDept.Length;
                        cmd.Parameters.Add(par1);

                        // ExecuteReader method is being used without passing the SchemaOnly
                        // behavior because access to the ResultSet data is required
                        TdDataReader reader = cmd.ExecuteReader();

                        try
                        {
                            // Display result set table column meta data
                            displayRSMetaData(reader);

                            System.Console.WriteLine("\n\n Using ExecuteReader() to execute the following" +
                                                " SQL command: \n\n");
                            System.Console.WriteLine(Constants.sSelect + '\n');

                            // Extract and display result set table data
                            System.Console.WriteLine();
                            System.Console.WriteLine(" DISPLAYING RESULT SET DATA:");
                            System.Console.WriteLine(" ---------------------------");

                            int rowCount = 0;   // result set row counter

                            // If there are rows iterate through all returned values and display them                       
                            if (reader.HasRows)
                            {
                                // While there is more to read
                                while (reader.Read())
                                {
                                    rowCount++;   // increment retrieved row counter

                                    // Extract column values
                                    String name = reader.GetString(0);
                                    int id = reader.GetInt32(1);

                                    // Geting BLOB
                                    String bFilename = Environment.CurrentDirectory + @"\output\" + id + ".jpg";

                                    // Create a filestream to output BLOB to
                                    FileStream fs = new FileStream(bFilename, FileMode.Create, FileAccess.Write);

                                    try
                                    {
                                        // Create a buffer
                                        Byte[] bBuffer = new byte[100];
                                       
                                        // Call reader.GetTdBlob which will return a TdBlob.
                                        // TdBlobs support chunking so bytes can be read incrementally.
                                        TdBlob blob = reader.GetTdBlob(3);

                                        long numOfBytes;

                                        do
                                        {
                                            // Fill the buffer with bytes from the reader
                                            numOfBytes = blob.Read(bBuffer, 0, Constants.BUFFERSIZE);

                                            // Write bytes to the filestream
                                            fs.Write(bBuffer, 0, Constants.BUFFERSIZE);

                                        } while (numOfBytes == Constants.BUFFERSIZE);
                                        // The last segment of bytes will not fill the entire buffer
                                        // so we will continue while the number of bytes read fill the
                                        // entire buffer.
                                    }
                                    finally
                                    {
                                        fs.Close();
                                    }

                                    String cFilename = Environment.CurrentDirectory + @"\output\" + id + ".txt";

                                    // Create a filestream to output CLOB to
                                    StreamWriter sw = File.CreateText(cFilename);

                                    // Geting CLOB
                                    try
                                    {

                                        // Create a buffer
                                        Char[] cBuffer = new char[100];

                                        // Call reader.GetTdClob which will return a TdClob.
                                        // TdClobs support chunking so chars can be read incrementally.
                                        TdClob clob = reader.GetTdClob(2);

                                        long numOfChars;

                                        do
                                        {
                                            // Fill the buffer with chars from the reader
                                            numOfChars = clob.Read(cBuffer, 0, Constants.BUFFERSIZE);

                                            // Write bytes to the filestream
                                            sw.Write(cBuffer, 0, Constants.BUFFERSIZE);

                                        } while (numOfChars == Constants.BUFFERSIZE);
                                        // The last segment of chars will not fill the entire buffer
                                        // so we will continue while the number of chars read fill the
                                        // entire buffer.
                                    }
                                    finally
                                    {
                                        sw.Close();
                                    }

                                    // get the interval data
                                    TdIntervalYear service = TdIntervalYear.Parse(reader.GetString(4));
                                    TdIntervalDay workdays = TdIntervalDay.Parse(reader.GetString(5));

                                    System.Console.WriteLine("\n ROW " + rowCount);
                                    System.Console.WriteLine(" ----------");
                                    System.Console.WriteLine(" COLUMN empName : " + name);
                                    System.Console.WriteLine(" COLUMN empID : " + id);
                                    System.Console.WriteLine(" COLUMN empResume : Resume saved as " + cFilename);
                                    System.Console.WriteLine(" COLUMN empPhoto : Photo saved as " + bFilename);
                                    System.Console.WriteLine(" COLUMN empService : " + service.ToString());
                                    System.Console.WriteLine(" COLUMN empWorkDays : " + workdays.ToString());
                                }
                            }
                            System.Console.WriteLine("\n " + reader.RecordsReturned + " Row(s) retrieved. \n");
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                    finally
                    {
                        cmd.Dispose();
                        System.Console.WriteLine(" Command Disposed. \n");
                    }
                }
                finally
                {
                    // Close the connection
                    System.Console.WriteLine(" Closing connection to Teradata...");
                    con.Close();
                    System.Console.WriteLine(" Connection to Teradata closed. \n");
                }

                System.Console.WriteLine(" Sample T20303TD finished. \n");
            }
            catch (DbException ex)
            {
                // A DbException was generated.  Catch it and display
                // the error information.
                // Note that there could be multiple error objects chained
                // together.                 
                if (ex is TdException)
                {
                    foreach (TdError err in ((TdException)ex).Errors)
                    {
                        System.Console.WriteLine(err.Message);
                    }
                }
                System.Console.WriteLine(ex.Message);
                System.Console.WriteLine("*** DbException caught ***");
            }
            catch (Exception ex)
            {
                // An exception of some other type has been generated.
                // Display the error information.
                System.Console.WriteLine(ex.Message);
                System.Console.WriteLine(ex.StackTrace);               
            }
        }

to

Private Shared Sub Main(ByVal args As String())
    Try
        Console.WriteLine("Sample T20303TD: " & ChrW(10))
        Console.WriteLine(" Looking for .NET Data Provider for Teradata...")
        Console.WriteLine(" Attempting to open a connection to Teradata via the .NET Data Provider... " & ChrW(10))
        Dim con As New TdConnection(Constants.sConnection)
        con.Open
        Console.WriteLine((" User " & Constants.sUser & " connected."))
        Console.WriteLine(" Connection to Teradata established. " & ChrW(10))
        Try
            Dim cmd As TdCommand = con.CreateCommand
            Try
                cmd.CommandText = Constants.sSelect
                Dim par1 As TdParameter = cmd.CreateParameter
                par1.Value = Constants.sDept
                par1.ParameterName = "Dept"
                par1.DbType = DbType.String
                par1.Size = Constants.sDept.Length
                cmd.Parameters.Add(par1)
                Dim reader As TdDataReader = cmd.ExecuteReader
                Try
                    T20303TD.displayRSMetaData(reader)
                    Console.WriteLine(ChrW(10) & ChrW(10) & " Using ExecuteReader() to execute the following SQL command: " & ChrW(10) & ChrW(10))
                    Console.WriteLine((Constants.sSelect & ChrW(10)))
                    Console.WriteLine
                    Console.WriteLine(" DISPLAYING RESULT SET DATA:")
                    Console.WriteLine(" ---------------------------")
                    Dim rowCount As Integer = 0
                    If reader.HasRows Then
                        Do While reader.Read
                            rowCount += 1
                            Dim name As String = reader.GetString(0)
                            Dim id As Integer = reader.GetInt32(1)
                            Dim bFilename As String = String.Concat(New Object() { Environment.CurrentDirectory, "\output\", id, ".jpg" })
                            Dim fs As New FileStream(bFilename, FileMode.Create, FileAccess.Write)
                            Try
                                Dim numOfBytes As Long
                                Dim bBuffer As Byte() = New Byte(100  - 1) {}
                                Dim blob As TdBlob = reader.GetTdBlob(3)
                                Do
                                    numOfBytes = blob.Read(bBuffer, 0, Constants.BUFFERSIZE)
                                    fs.Write(bBuffer, 0, Constants.BUFFERSIZE)
                                Loop While (numOfBytes = Constants.BUFFERSIZE)
                            Finally
                                fs.Close
                            End Try
                            Dim cFilename As String = String.Concat(New Object() { Environment.CurrentDirectory, "\output\", id, ".txt" })
                            Dim sw As StreamWriter = File.CreateText(cFilename)
                            Try
                                Dim numOfChars As Long
                                Dim cBuffer As Char() = New Char(100  - 1) {}
                                Dim clob As TdClob = reader.GetTdClob(2)
                                Do
                                    numOfChars = clob.Read(cBuffer, 0, Constants.BUFFERSIZE)
                                    sw.Write(cBuffer, 0, Constants.BUFFERSIZE)
                                Loop While (numOfChars = Constants.BUFFERSIZE)
                            Finally
                                sw.Close
                            End Try
                            Dim service As TdIntervalYear = TdIntervalYear.Parse(reader.GetString(4))
                            Dim workdays As TdIntervalDay = TdIntervalDay.Parse(reader.GetString(5))
                            Console.WriteLine((ChrW(10) & " ROW " & rowCount))
                            Console.WriteLine(" ----------")
                            Console.WriteLine((" COLUMN empName : " & name))
                            Console.WriteLine((" COLUMN empID : " & id))
                            Console.WriteLine((" COLUMN empResume : Resume saved as " & cFilename))
                            Console.WriteLine((" COLUMN empPhoto : Photo saved as " & bFilename))
                            Console.WriteLine((" COLUMN empService : " & service.ToString))
                            Console.WriteLine((" COLUMN empWorkDays : " & workdays.ToString))
                        Loop
                    End If
                    Console.WriteLine((ChrW(10) & " " & reader.RecordsReturned & " Row(s) retrieved. " & ChrW(10)))
                Finally
                    reader.Close
                End Try
            Finally
                cmd.Dispose
                Console.WriteLine(" Command Disposed. " & ChrW(10))
            End Try
        Finally
            Console.WriteLine(" Closing connection to Teradata...")
            con.Close
            Console.WriteLine(" Connection to Teradata closed. " & ChrW(10))
        End Try
        Console.WriteLine(" Sample T20303TD finished. " & ChrW(10))
    Catch ex As DbException
        If TypeOf ex Is TdException Then
            Dim err As TdError
            For Each err In DirectCast(ex, TdException).Errors
                Console.WriteLine(err.Message)
            Next
        End If
        Console.WriteLine(ex.Message)
        Console.WriteLine("*** DbException caught ***")
    Catch ex As Exception
        Console.WriteLine(ex.Message)
        Console.WriteLine(ex.StackTrace)
    End Try
End Sub

 

There are alternative methods of converting C# to VB; for example: http://msdn.microsoft.com/en-us/magazine/cc163652.aspx

2 Comments
Teradata Employee
Is it possible to log in to the "MONITOR" partition of the Teradata DB using the .NET Driver? And then use the PMPC sub-system commands like: "MONITOR PHYSICAL RESOURCE"

I know it is possible with the JDBC Driver...
Teradata Employee
The .NET Data Provider for Teradata does not support MONITOR partition sessions.
I suggest requesting changes to the Open API (documented in Performance Management Manual) if Open API does not meet your needs.