How to refer to a datatable column in a web service call?

Question

First off, I dont know much about .net or web service calls. So my apologies if this question has been answered before.

I have the following piece of code that iterates over a dataset populated by an Execute SQL Task in SSIS. I need to take these values and send them to the web service so that the web service can do whatever it wants. I have go to the part where I can iterate over the records, but I am not sure how to refer to the columns in the web service call. Meaning, if the service call was xyz(val1,val2,val3,val4), how do I associate val values to the datatable that I have created?

        public void Main()
        {
            // TODO: Add your code here
            var caseObj = Dts.Variables["User::QCase"].Value;
            var serviceURI = Dts.Variables["User::WebServiceURI"].Value.ToString();
            var oleDA = new OleDbDataAdapter();
            var httpBinding = new BasicHttpBinding();

            var dt = new DataTable();
            string sMsg;

            oleDA.Fill(dt, Dts.Variables["User::QCases"].Value);

            foreach (DataRow row in dt.Rows)
                foreach (DataColumn col in dt.Columns)
                {
                    var caseProxy = new CaseService.CServicesProxyClient(httpBinding, new EndpointAddress(serviceURI));
                    sMsg = "";
                    sMsg = sMsg + col.ColumnName + ": " + (col.Ordinal).ToString() + vbCrLf;
                    MessageBox.Show(sMsg);
                    var results = caseProxy.SaveCaseStatusChange(dt, 0, false, null); //This is the service call which will push the values to the web service. dt is the data table.

                }
          /*  if (results)
            {
                Dts.TaskResult = (int)ScriptResults.Success;
            }
            else
            {
                Dts.TaskResult = (int)ScriptResults.Failure;
            }*/
        }

        #region ScriptResults declaration
        /// <summary>
        /// This enum provides a convenient shorthand within the scope of this class for setting the
        /// result of the script.
        /// 
        /// This code was generated automatically.
        /// </summary>
        enum ScriptResults
        {
            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
        };
        #endregion


        public object vbCrLf { get; set; }
    }
}

I hope I have provided sufficient details, but if not, I will be more than happy to provide. Thank you for taking the time to read the question.

You have a web method that expects N parameters. You have a datatable filled with data for the service. You need to enumerate through all the rows of data and shove columns N through Z into the web call and then actually call it, yeah? — billinkc, Sep 18 '14 at 21:07
Is SaveCaseStatusChange expecting a datatable for the first parameter or is going to be the data table's row zero, column zero that goes in there? — billinkc, Sep 18 '14 at 21:21
It wont take the data table, but instead it will take the 1st column and 1st row of the data table. The 2nd parameter of the SaveCaseStatusChange would take Column2 of Row1 and so on. — rvphx, Sep 18 '14 at 21:22

score 1 · Accepted Answer · edited May 23 '17 at 12:29

Unless there's some nuance I'm missing, this is pretty straight forward. The only thing you have to keep in your head is that the first element in an array is going to be in position zero

Your current approach, iterating through the Columns collection, might be useful for something that allows for generic data sets but I'm assuming the data from User::QCases is always going to return N columns of data. Depth, I don't care about, width I do.

You'll need to look at your webservice method to see what data types it expects and coerce your data from the row into that type. Also, you'll need to deal with NULLs (or not, based on your data) but .NET primitive types don't allow for NULL values. You can "cheat" by using the ? types but that makes this a longer response than I can free type.

foreach (DataRow row in dt.Rows)
{
    // row is going to contain the current row worth of data
    // All you need to do is access the values.

    var results = caseProxy.SaveCaseStatusChange(row[0].ToString(), row[1].ToString(), row[2].ToString(), row[3].ToString());

}

There are mechanisms for addressing columns by name and not their ordinal position which would allow you always access the Id column whether it was the first or the last column.

How to 'foreach' a column in a DataTable using C#?

The first element that the web service expects is a long data type. As well as the 2nd one. The third is type bool and 4th is of type string. When I changed the code to what you suggested, it starts complaining, that the "Best Overloaded method match for 'servicename' has some errors. Trying to see where its going wrong. — rvphx, Sep 18 '14 at 21:39
It's the data type mismatch. I had said I was assuming strings. Moment while I refresh my brain aka look at msdn — billinkc, Sep 18 '14 at 21:44
I think it was trying to convert from object to long. I explicitly converted to type long and the errors are gone! Thanks Bill. — rvphx, Sep 18 '14 at 21:45

How to refer to a datatable column in a web service call?

1 Answers1

Linked