Could not bind a SQL Server 2005 Web Service to DataGridView when creating Web Service Clien

Hello,

I would like to develop a minimal Web Service Client in Visual Studio (C#) which call a Web Service created in SQL Server 2005. I have definied a simple stored procedure and a HTTP endpoint which are both working.

In Visual Studio 2005 Pro I proceed following steps:

New application (C#)

Add web reference to the project (http://localhost:8084/Produktdaten?wsdl)

Add DataGridView to the form1 in the designer window

Configure DataGridView Data Source by selecting "other data sources" - "project data sources" - "localhost" and "produktdaten". In the Form1 (Designer View) the columns "EnableDecompress" ... appears. I could not select my expected web method "GetProduct" definied in the stored procedure.

Afterwards I tried to solve the problem manually with the following code (form1.cs). I could compile this windows application succesfully. When I started the corresponding.exe file I got an error. In the debugging mode problems were reported with the line "dataGridView1.DataMember = "Product";". In the SQL Server database (AdventureWorks) the product table belongs to the schema production. Also with production.product the code listed below did not work.

Could you please help to get this simple web service client running.

Best regards,
Wolfgang

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

namespace AWProductWebServiceClient

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

localhost.Produktdaten ws = new localhost.Produktdaten();

dataGridView1.DataSource = ws.GetProduct();

dataGridView1.DataMember = "Product";

}

private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)

{

}

}

}

Wolfgang,

In general, you can not directly assign the result from the SQL Server 2005 Native Web Services web method as the data source to a dataGridView.
BTW, you do not need to configure the DataGridView data source at design time.

If the stored procedure was mapped to the endpoint WITHOUT the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is an array of objects. The array can contain SqlMessages, DataSet, RowCount, etc. I believe the DataGridView.DataSource requires a DataTable, but I can't be sure. What has worked for me, as a quick sample code, is:
object[] resp = proxy.method(myParam);
foreach (object o in resp)
{
if (o.GetType().ToString().Equals("System.Data.DataSet"))
{
this.dataGridView1.DataSource = ((DataSet)o).Tables[0];
}
}

If the stored procedure was mapped to the endpoint WITH the keyword "FORMAT = ROWSETS_ONLY", then the result of the stored procedure is directly a DataSet. The DataSet object can contain multiple DataTables (depending on how many SELECT statements are in the stored procedure). You can then assign the DataTable as the data source of the DataGridView. So, something like:
DataSet ds = proxy.method(myParam);
this.dataGridView1.DataSource = ds.Tables[0];

Jimmy

|||

Jimmy,

sorry for my late response (vacation).

The provided solution works fine. Also the explanation of the "FORMAT" keyword was very helpful.

Thank you very much.

Best regards,
Wolfgang