Populating a DataTable from a Stored Procedure in DB2 or SQL

ExecuteSPtoDT.cs

//https://stackoverflow.com/questions/13402003/how-to-populate-a-datatable-from-a-stored-procedure

//For SQL
DataTable table = new DataTable();
using(var con = new SqlConnection(ConfigurationManager.ConnectionStrings["DB"].ConnectionString))
using(var cmd = new SqlCommand("usp_GetABCD", con))
using(var da = new SqlDataAdapter(cmd))
{
   cmd.CommandType = CommandType.StoredProcedure;
   da.Fill(table);
}

//Alternative - 
SqlConnection con = new SqlConnection(@"Some Connection String");
SqlDataAdapter da = new SqlDataAdapter("ParaEmp_Select",con);
da.SelectCommand.CommandType = CommandType.StoredProcedure;
da.SelectCommand.Parameters.Add("@Contactid", SqlDbType.Int).Value = 123;
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;


//For DB2
DataTable DTT = new DataTable();
//See if UserID exists check is required here
using (var connection = new DB2Connection(ConnectionString))
{
  //connection.Open(); //open/close connection will be done implicitely by the DataAdapter.
  using (DB2Command cmd = connection.CreateCommand())
  {
      cmd.CommandText = DataBaseObjects.spGetAssignedBranches;
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.Add(new DB2Parameter(DataBaseObjects.ParamVUserID, DB2Type.Integer)).Value = userDo.User_ID;
      cmd.Parameters.Add(new DB2Parameter(DataBaseObjects.ParamBusinessEntityID, DB2Type.Integer)).Value = userDo.BusinessEntity;
      cmd.Parameters.Add(new DB2Parameter(DataBaseObjects.ParamFunctionalAreaID, DB2Type.Integer)).Value = userDo.FunctionalArea;
      cmd.Parameters.Add(new DB2Parameter(DataBaseObjects.ParamBusinessAreaID, DB2Type.Integer)).Value = userDo.BusinessArea;
      //cmd.CommandTimeout = CommandTimeout; //wait time before terminating the attempt to execute a command and generating an error in secs

      using (var da = new DB2DataAdapter(cmd))
      {
          da.Fill(DTT);
      }
      DTT.TableName = TableName;
      return DTT;
  }
}



CREATE OR REPLACE PROCEDURE GETASSIGNEDROLES(IN V_USER_ID INTEGER, IN V_BUSINESS_ENTITY_ID INTEGER) SPECIFIC GETASSIGNEDROLES DYNAMIC RESULT SETS 1 LANGUAGE SQL NOT DETERMINISTIC EXTERNAL ACTION READS SQL DATA CALLED ON NULL INPUT INHERIT SPECIAL REGISTERS 
BEGIN 
	DECLARE TEMP_CURSOR CURSOR WITH HOLD WITH RETURN TO CLIENT FOR 
		SELECT DISTINCT UR.ROLE_ID,R.ROLE_NAME AS ROLE_NAME  FROM ROLE R INNER JOIN USER_ROLE UR ON R.ROLE_ID = UR.ROLE_ID INNER JOIN USER_BUSINESS_ENTITY UBE ON UBE.USER_SGID = UR.USER_SGID WHERE UBE.USER_ID = V_USER_ID AND UBE.BUSINESS_ENTITY_ID = V_BUSINESS_ENTITY_ID AND R.ROLE_TYPE_ID <> (SELECT ROLE_TYPE_ID FROM ROLE_TYPE WHERE UPPER(ROLE_TYPE_NAME) LIKE '%PERSONAL%' WITH UR) UNION SELECT DISTINCT UR.ROLE_ID,R.ROLE_NAME AS ROLE_NAME FROM ROLE R INNER JOIN USER_ROLE UR ON R.ROLE_ID = UR.ROLE_ID INNER JOIN USER_BUSINESS_ENTITY UBE ON UBE.USER_SGID = UR.USER_SGID WHERE UBE.USER_ID = V_USER_ID AND UBE.ISACTIVE = 'Y' AND UBE.BUSINESS_ENTITY_ID = V_BUSINESS_ENTITY_ID AND R.ROLE_NAME = (SELECT U.LAST_NAME || ','|| U.FIRST_NAME || ' (' || U.USER_LAN_CODE ||')' FROM USER U INNER JOIN USER_BUSINESS_ENTITY UBE ON UBE.USER_ID = U.USER_ID WHERE U.USER_ID = V_USER_ID AND UBE.BUSINESS_ENTITY_ID = V_BUSINESS_ENTITY_ID WITH UR) ORDER BY ROLE_NAME FOR READ ONLY WITH UR; 
	IF V_USER_ID IS NOT NULL AND V_USER_ID > 0 THEN 
		OPEN TEMP_CURSOR; 
	END IF; 
END;