CODEWITHSUNDEEP
c#openedgeprogress-db
Litation
Litation

Reputation: 37

OpenEdge/Progress DB Parameters in Query

I’m trying to write an small c# app to connect to a ProgressDB but I’m failing at a pretty early stage.... writing a query that takes a parameter. For context I had not even head of progress until last week (I am a mainstream MSSQL monkey)

If I were writing my query in TSQL it would just be

DECLARE @Id nvarchar(15) = 'X1234'
SELECT * from People WHERE Id = @Id

Now I can write a hardcoded select statement for progress using ODBC query tool

Select * from People where "Id" = 'X1234'

But I don’t know how to parameterise it, I’ve had a look at the Progress/OpenEdge KB but its doesn't seem as simple as X = "Y" call X.

I could do something horribly messy (With the added benefit of make my senior Devs cry) in my C# code and do the following :

string sqlstr = "Select * from People where " + "\"" + "Id" + "\" ' = " + id + "'";

but I really want to be doing something like this. Obviously this is using SqlConnection rather than OdbcConnection but that’s a problem for tomorrow me to fix (Tomorrow me doesn’t really like yesterday me much)

public async Task<IEnumerable<Data>>GetMeMyData(string id)
        {
            using (var connection = new SqlConnection(_configuration.GetConnectionString("MyDB")))
            {
                var sqlQuery = "Select * from People where \"ID\" = '@Id'";

                return await connection.QueryAsync<Data>(sqlQuery, new { Id = id });
            }
        }

Any advice or links to a good eli5/babies first progress query would really be appreciated Thanks (and hopefully that all makes sense).

Upvotes: 1

Views: 1055

Answers (2)

Litation
Litation

Reputation: 37

Went back to this and used Dapper to achive what was needed both options worked I just wanted to keep constant with existing code.

using (var connection = new OdbcConnection(_configuration.GetConnectionString("LocalClient")))
            {
                 var FilesSqlQuery = 
                    "Select \"CUSTOMER-ID\" as CId," +
                    "\"CUSTOMER-NAME\" as CName," +
                    " \"CUSTOMER-TYPE\" as CType," +
                    " \"DATE-ADDED\" as DateAdded" +
                    "FROM PUB.People" +
                    " WHERE \"ID\" = ? and \"ORDER-TYPE\" != 'Test'";

                return await connection.QueryAsync<FileData>(FilesSqlQuery, new { Id= id});
        
            }

Upvotes: 0

Stefan Drissen
Stefan Drissen

Reputation: 3374

google site:progress.com sql parameterized query

The first hit P176215 contains an example, which I am just copy / pasting in here.

The following sample C# program shows how to build a parameterized query against an ODBC connection:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            OdbcConnection conn = new OdbcConnection("DSN=S2K;UID=abc;PWD=def;");
            OdbcCommand cmd = conn.CreateCommand();

            conn.Open();

            cmd.CommandText = "SELECT CustNum, Name FROM PUB.Customer WHERE CustNum > ? AND Balance > ?";

            cmd.Parameters.Add("@Num", OdbcType.Int);
            cmd.Parameters.Add("@Bal", OdbcType.Int);

            cmd.Parameters["@Num"].Value = 2;
            cmd.Parameters["@Bal"].Value = 500;

            OdbcDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine("CustNum and Name: {0} {1}", reader.GetValue(0), reader.GetValue(1));
            }

            reader.Close();

            conn.Close();

            Console.ReadLine();
        }
    }
}

It all looks sensible to me.

My only concern is that you are using direct database access, which I would never give you - but your case my provide for this.

I would expose the data I need as a controlled secured REST API which can then be consumed by C# or whatever other client language you want to use.

Upvotes: 1

Related Questions