ExecuteSPtoDT.cs This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode characters Show hidden characters //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 ;