Odbc Connection in C# x Odbc Connection in Python

Grasshopper Developer
1

Hi guys,

I was able to write a script in python to connect and query some data in a PostgreSQL database via ODBC. This is the code:

import clr
import System
clr.AddReference("System.Data")

from System.Data import DataSet
from System.Data.Odbc import OdbcConnection, OdbcDataAdapter


if CToggle == True:

    connection = OdbcConnection(CString)
    adaptor = OdbcDataAdapter(Query, connection)
    dataSet = DataSet()
    connection.Open()
    adaptor.Fill(dataSet)
    connection.Close()

    rows = []
    for row in dataSet.Tables[0].Rows:
        rows.append(list(row))

    QResult=[]

    for i in range(0,(len(rows))):
        QResult.append(rows[i][0])
else:
    QResult = 'Turn the CToggle on!!!'

(My input variables are “CString”, “CToggle” and “Query”. My output is “QResult”.)

Recently I have contact with C# and I am trying to “translate” this code. Then I do this:

  private void RunScript(string CString, bool CToggle, string Query, ref object QResult)
  {
    if (CToggle == true)
    {

      System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CString);
      System.Data.Odbc.OdbcDataAdapter adaptor = new System.Data.Odbc.OdbcDataAdapter(Query, connection);
      DataSet dataset = new DataSet();

      connection.Open();
      adaptor.Fill(dataset);
      connection.Close();

      List<string> rows = new List<string>();

      foreach (DataRow row in dataset.Tables[0].Rows)
      {
        rows.Append(row[0]);
        Print(row[0].ToString());
      }

      QResult = rows;

      
      
      //Override component attributes
      Component.Name = "Odbc Query C#";
      Component.NickName = "OdbcQuery.cs";
      Component.Message = "v01_02/20/2020";
      Component.Description = "This component sends a query to a database using ODBC driver installed in your system\n\n------\n\nProvided by EugĂŞnio Moreira @ LED_UFC";


      //Override component input attributes
      Component.Params.Input[0].Name = "Connection String";
      Component.Params.Input[0].NickName = "CString";
      Component.Params.Input[0].Description = "Input the connection string";

      Component.Params.Input[1].Name = "Connection Toggle";
      Component.Params.Input[1].NickName = "CToggle";
      Component.Params.Input[1].Description = "Set 'True' to start the process";

      Component.Params.Input[2].Name = "Query";
      Component.Params.Input[2].NickName = "Query";
      Component.Params.Input[2].Description = "Input the complete query in string format";


      //Override component output attributes
      Component.Params.Output[1].Name = "Query Results";
      Component.Params.Output[1].NickName = "QResult";
      Component.Params.Output[1].Description = "List with the result of the query";


    }

So, here is the thing… When I use the Print function (inside the last foreach), I have the exact result that I want in the out output. But, when I try to build a list (rows) with the results and assing this to another output (QResult), I have a empty list.

image
image1358Ă—735 136 KB

Anyone understand this behavior? Anyone have a clue about pass to my QResult output the same list as out?

Thanks in advance!
:yellow_heart:

2

Screen Shot 078
Screen Shot 0781153Ă—513 226 KB

2 Likes
3

This is it!

Just to clarify. this is the modification that I had to make:

private void RunScript(string CString, bool CToggle, string Query, ref object QResult)
  {
    if (CToggle == true)
    {

      System.Data.Odbc.OdbcConnection connection = new System.Data.Odbc.OdbcConnection(CString);
      System.Data.Odbc.OdbcDataAdapter adaptor = new System.Data.Odbc.OdbcDataAdapter(Query, connection);
      DataSet dataset = new DataSet();

      connection.Open();
      adaptor.Fill(dataset);
      connection.Close();

      List<string> rows = new List<string>();

      foreach (DataRow row in dataset.Tables[0].Rows)
      {

        rows.Add(row[0].ToString());


        Print(row[0].ToString());
      }
      
      QResult = rows;

}
}

The line rows.Append(row[0]); , was replaced for rows.Add(row[0].ToString());.

Many Thaks @PeterFotiadis!

1 Like