How to connect to Progress Database from DotNet

There are two very different answers to your question.  One is to use the ABL GUI for .NET, i.e., .NET UI controls and ABL code to create a client which then talks to the OE database.  The other is to use OpenClient with AppServer, i.e., a "pure" .NET client interfacing with AppServer.  Both cases should use AppServer, although it is possible to connect directly with the ABL GUI for .NET, but don't go there.  Both are discussed at length in the documentation.

This is the first time I try to use Dotnet to talk to Progress Database.

ODBC is the third option when you want to communicate with the database (and not application logic). I'm afraid, there is no native .NET data provider for the OpenEdge database.

Hi,

We are using Progress OpenEdge 10.2A driver right now.
It works with DSN but I want to see the connectring not using DSN.
Do you  example of connection string look like not using DSN property?

JDang

Hi everyone,

I think here is the correct conntionstring:

DRIVER={Progress OpenEdge 10.2A Driver};HOST=MyServer; DB=myDB; UID=myUser;PWD=myPassword;PORT=myPort#;

regards,

JDang

// Sample C# program to insert a record into customer table in sports2000 Progress database Version 10.2a
// Author : Rajesh Kayakkal
// Email : kayakkal@hotmail.com //

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.Odbc;
using System.Data;
using Microsoft.Win32;
using System.Diagnostics;
using System.Threading;
using System.IO;

namespace SampleODBC
{
    class Program
    {
        static void Main(string[] args)
        {
            Stopwatch stopWatch = new Stopwatch();
            // Console.WriteLine(GetODBCDriverName("Progress","10.2A"));

            stopWatch.Start();
            int InsertSuccess = Insert(20007, "Bizken" , "bizken@bizken.com");
            stopWatch.Stop();

            Console.WriteLine("ElapsedMilliseconds=" + stopWatch.ElapsedMilliseconds);

            Console.WriteLine(InsertSuccess > 0 ? "Inserted Successfully" : "Insert Failed");

            Console.Read();

        }

        public static string GetODBCDriverName(string Database, string Version)
        {
           
            string ODBCDriverName = "";

            RegistryKey registryKey = Registry.LocalMachine;
            RegistryKey registrySubKey = registryKey.OpenSubKey(@"SOFTWARE\ODBC\ODBCINST.INI\");

            String[] SubKeyNames = registrySubKey.GetSubKeyNames();

            foreach (String KeyName in SubKeyNames)
            {
                if (KeyName.Contains(Database) && KeyName.Contains(Version))
                {
                    ODBCDriverName = KeyName;
                    break;
                }
            }

            registrySubKey.Close();
            registryKey.Close();
           
            return ODBCDriverName;
        }

        public static int Insert(int custNum, String customerName, String emailAddress)
        {

            OdbcConnectionStringBuilder odbcConnectionStringBuilder = new OdbcConnectionStringBuilder();
            OdbcCommand odbcCommand;           
           
            int InsertSuccess = 0, RecordFound = 0;

            odbcConnectionStringBuilder.Driver = GetODBCDriverName("Progress", "10.2A");

            if (odbcConnectionStringBuilder.Driver == "")
            {
                Console.WriteLine(" ODBC Driver is not installed");
                return -1;
            }
          
            odbcConnectionStringBuilder.Add("DSN", "sports2000");  
            odbcConnectionStringBuilder.Add("UID", "dddd");
            odbcConnectionStringBuilder.Add("PWD", "ddd34");
            odbcConnectionStringBuilder.Add("DB", "test1");
            odbcConnectionStringBuilder.Add("HOST", "dda");
            odbcConnectionStringBuilder.Add("PORT", "9230");

            //builder.Add("Integrated Security","SSPI");

            // Console.WriteLine("Connecttion String = " + odbcConnectionStringBuilder.ConnectionString);

            using (OdbcConnection connection =
               new OdbcConnection(odbcConnectionStringBuilder.ConnectionString))
            {

                connection.Open();
                // Console.WriteLine(connection.State + "," + connection.ConnectionTimeout + "," + connection.ServerVersion + "," + connection.Database);

                try
                {

                    odbcCommand = new OdbcCommand("SELECT COUNT(*) FROM pub.customer WHERE customer.custnum =" + custNum.ToString() + "", connection);
                    odbcCommand.CommandTimeout = 1;
                    object executeScalarResult = odbcCommand.ExecuteScalar();

                    RecordFound = Convert.ToInt32(executeScalarResult);

                    if (RecordFound == 0)
                    {

                        odbcCommand = new OdbcCommand("Insert into pub.customer (custnum,name,EmailAddress) Values(" + custNum.ToString() + ",'" + customerName + "','" + emailAddress + "')", connection);
                        odbcCommand.CommandTimeout = 1;

                        InsertSuccess = odbcCommand.ExecuteNonQuery();

                    }
                    else
                    {
                        return -2;
                    }
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                    return -3;
                }
            
            
            }

            return InsertSuccess;
        }

    }
}

Recognize that ODBC is *not* the preferred approach except for casual use.  If you are just doing a little fiddling for some special purpose, it might be fine, but if you are contemplating a large number of active clients, then don't even start down that road.

Thomas,

What is the other option to talk to Progress database beside the to use this ODBC with this driver ?

DRIVER={Progress OpenEdge 10.2A Driver};HOST=MyServer; DB=myDB; UID=myUser;PWD=myPassword;PORT=myPort#;

Regards,

Jdang

As I said above, the preferred method for a .NET client is to use AppServer with OpenClient .... certainly if you are planning any substantial give and take interaction with the database instead of just some reporting.

Or, skip the .NET programming altogether and use ABL GUI for .NET ... .NET controls, but ABL code.  That also should use AppServer.

But, if you told us a bit about what you were hoping to accomplish, we could give a more informed opinion.

tamhas schrieb:
As I said above, the preferred method for a .NET client is to use AppServer with OpenClient .... certainly if you are planning any substantial give and take interaction with the database instead of just some reporting.
Or, skip the .NET programming altogether and use ABL GUI for .NET ... .NET controls, but ABL code.  That also should use AppServer.
But, if you told us a bit about what you were hoping to accomplish, we could give a more informed opinion.

I wouldn't completely ban ODBC! It all depends on the use case, as Thomas properly stated. For transactional processing the AppBuilder should be superior, but that will certainly require ABL programming knowledge.

PSC hasn't ignored SQL ... been available since 1988 (SQL 92 since 2000).  But, ABL and the OE database were designed primarily to support OLTP and are thus record oriented whereas SQL is set oriented.  SQL works well against an OE database for reporting purposes, e.g., with third party tools, but it wouldn't be my first choice for update, the ABL shines.  The idea that hordes of Java and .NET programmers would flock to OE if there was a better SQL just has no basis in reality.

.......................................................

PSC hasn't ignored SQL ... been available since 1988 (SQL 92 since 2000).  But, ABL and the OE database were designed primarily to support OLTP and are thus record oriented whereas SQL is set oriented.  SQL works well against an OE database for reporting purposes, e.g., with third party tools, but it wouldn't be my first choice for update, the ABL shines.

.......................................................

.......................................................

The idea that hordes of Java and .NET programmers would flock to OE if there was a better SQL just has no basis in reality.

.......................................................

We seem not to be communicating very well.  Recent versions of PSC SQL92, the one external to ABL, do perform very well for reporting.  We have had type 4 drivers for a number of releases now and the SQL engine even has some advantages such as no-index reads, which are not in ABL, and which are a help in full table scans.  And, the query based optimization is good for running arbitrary complex queries on large amounts of data, such as one often does with data warehouse type reports. It could use a little enhancement in the form of some additional extensions (Oracle and MS SQL having a lot of such extensions, but not the same ones), but the way it is it provides a very appropriate foundation for third party reporting tools.  I.e., this is not a problem which needs fixing.  At most it needs a little tinkering and enhancement like everything always does.

If that is what you want to do with Java and .NET clients, then dive in.

But, I'm not going to recommend it for a general purpose replacement for ABL.  For starters, what's the point?  If you are going to write a Java or .NET client and you aren't going to use AppServer, why in the world would you use an OpenEdge database?  The only reason you are going to have the OE database in the first place is because you have an ABL application.

SQL works for update, but I haven't heard of anyone who is using it for a large number of clients on OE.  I know there are some real obstacles.  Some of them might be issues with the PSC SQL and some of them might just be things SQL does on any database.  Casual, limited use for some special purpose, give it a go, but if you are thinking of something like running the whole application this way, then you ought to move one of three different ways.

If you want to do everything n Java or .NET and SQL, then pick another database.  You aren't going to get the benefit from an OE database that way.

If you want your server-side logic in ABL, a choice which I would recommend, then go with AppServer and OpenClient.

If you want .NET UI components, but the ease of writing everything in ABL, then use .NET GUI for ABL.

PSC has a motivation to provide a SQL which provides highly performant reporting capabilities because a lot of people using ABL applications want to use third-party reporting tools that are based on SQL.  PSC does not have much motivation to provide hignly performant wrte capabilities since anybody who wants a lot of clients or needs to write a lot of data to an OE database is going to use ABL to do it.

hi john dang,

by using ODBC is a very straight forward way to connect to  the Progress Database.  If you use for only read only purpose, it should be ok. but if you want to do update record as well.

sometime you might need to do the "record has been changed by another user" checking.

Thank you

Regards,

Tan