How can I convert U2 Business Logic Subroutine’s multi-value data into .NET Objects such as DataSet/DataTable using U2 Toolkit for .NET?

Question

I have the following subroutine. It takes INPUT as argument 1 and sends multi-value data OUTPUT as argument 2.

SUBROUTINE MV_TO_DATASET_SELECT_SUBROUTINE(ARG_INPUT,ARG_OUTPUT)

x = ARG_INPUT ARG_OUTPUT = "100":@VM:"101":@VM:"102":@VM:"103":@FM:"Nancy":@VM:"Andrew":@VM:"Janet":@VM:"Margaret":@FM:"01/06/1991":@VM:"06/07/1996":@VM:"11/08/1999":@VM:"12/10/2001"

RETURN

The Schema of above subroutine’s multi-value data is as below.

        DataSet ds = new DataSet();
        DataTable dt = new DataTable("Employee");
        ds.Tables.Add(dt);
        dt.Columns.Add("ID",typeof(Int32));
        dt.Columns.Add("Name", typeof(string));
        dt.Columns.Add("HireDate", typeof(DateTime));

Answer 1

You can achevieve this task one of the following ways:

• Use MV_To_DataTable() and DataTable_To_MV(). You can create schema of subroutine by dragging and dropping Empty DataTable into Empty DataSet Deginer.
• By draagging and dropping Visual Studio Server Explorer’s U2 Subroutine into DataSet Designer. U2 Subrotine returns resultset/dataset by executing API such as ST=SQLExecDirect(@HSTMT, "SELECT F1 AS COL1,F2 AS COL2 ,F3 AS COL3 FROM @TMP SLIST 9 ORDER BY 1")

Use MV_To_DataTable() and DataTable_To_MV()

Create ASP.NET Web Application Project. Type ‘WebApplication_Subroutine’ in Project Name.

Add Reference to U2NETDK Assembly (U2.Data.Client)

Change header ‘Welcome to ASP.NET!’ to ‘Welcome to U2 Toolkit for .NET Demo on Business Logic Subroutine’s multi-value string data to .NET DataSet!’

Open ‘Default.aspx’ file and Go to Design Mode.

Do the following:

• Drag and Drop Button Control. Name it ‘Load’
• Drag and Drop Button Control. Name it ‘Update’

• Drag and Drop GridView Control.

  

Right Click on Solution Explorer. Select Add ->New Item-DataSet. In the Name box, type ‘Employee.xsd’

Drag and Drop DataTable into Designer. Change the name to ‘Employee’ Table.

Create 3 new Columns (U2 subroutine Schema):

• ID – DataType : INT
• Name – DataType : STRING
• HireDate - DataType : DATE

Open ‘Default.aspx’ file in Design Mode. Double Click ‘Load Button’. It will create Event handler Code behind.

Cut and paste the following code.

protected void Button1_Click(object sender, EventArgs e)
        {
            U2ConnectionStringBuilder l = new U2ConnectionStringBuilder();
            l.Server = "127.0.0.1";
            l.UserID = "user";
            l.Password = "pass";
            l.Database = "HS.SALES";
            l.ServerType = "universe";
            string lconnstr = l.ToString();
            U2Connection c = new U2Connection();
            c.ConnectionString = lconnstr;
            c.Open();
            U2Command command = c.CreateCommand();
            command.CommandText = "CALL MV_TO_DATASET_SELECT_SUBROUTINE(?,?)";
            command.CommandType = CommandType.StoredProcedure;
            U2Parameter p1 = new U2Parameter();
            p1.Direction = ParameterDirection.InputOutput;
            p1.Value = "";
            p1.ParameterName = "@arg_input";
            command.Parameters.Add(p1);
            U2Parameter p2 = new U2Parameter();
            p2.Direction = ParameterDirection.InputOutput;
            p2.Value = "";
            p2.ParameterName = "@arg_output";
            command.Parameters.Add(p2);
            command.ExecuteNonQuery();

            Employee.EmployeeDataTable dt = new Employee.EmployeeDataTable();

            command.Parameters[1].MV_To_DataTable(dt);

            Session["GridDataset"] = dt;


            this.GridView1.DataSource = dt;
            this.GridView1.DataBind();
        }

Run the application. Press ‘Load’ Button.

Open ‘Default.aspx’ file in Design Mode. Double click ‘Update Button’. It will create Event Handler in Code behind page. Cut and Paste the following Code.

        protected void Button2_Click(object sender, EventArgs e)
    {
        DataTable dt = (DataTable)Session["GridDataset"];

        //To TEST, change first row
        string s1 = (string)dt.Rows[0]["Name"];
        dt.Rows[0]["Name"] = s1 + "NewValue";


        // get the modified rows
        DataTable dt_changed = dt.GetChanges();

        //call DATASET_TO_MV_UPDATE_SUBROUTINE
        U2ConnectionStringBuilder l = new U2ConnectionStringBuilder();
        l.Server = "127.0.0.1";
        l.UserID = "user";
        l.Password = "pass";
        l.Database = "HS.SALES";
        l.ServerType = "universe";
        string lconnstr = l.ToString();
        U2Connection c = new U2Connection();
        c.ConnectionString = lconnstr;
        c.Open();
        U2Command command = c.CreateCommand();
        command.CommandText = "CALL DATASET_TO_MV_UPDATE_SUBROUTINE(?)";
        command.CommandType = CommandType.StoredProcedure;
        U2Parameter p1 = new U2Parameter();
        p1.Value = "";
        p1.Direction = ParameterDirection.InputOutput;
        p1.ParameterName = "@arg_data";
        command.Parameters.Add(p1);

        command.Parameters[0].DataTable_To_MV(dt_changed);

        // modified data going to subroutine
        string lData = (string)command.Parameters[0].Value;

        command.ExecuteNonQuery();
    }

See the Modified Value in the Debugger when you click Update Button.

U2 Subrotine returns resultset/dataset

Create U2 Data Connection in Visual Studio Server Explorer. Expand Store Procedures Node.

Go to subroutine that returns resultset/dataset. Drag and Drop subroutine into DataSet Designer. It shows INPUT argument and resultset/dataset columns.