C# Code Listing for ExecSP

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

The following code creates ExecSP.

private void ExecSP_Click(System.Object sender, System.EventArgs e)
{
   System.Data.SqlTypes.SqlString inParam = "";
   System.Data.SqlTypes.SqlString outParam = "";

   server.sql_endpoint proxy = new server.sql_endpoint ();

     proxy.Credentials = System.Net.CredentialCache.DefaultCredentials;
   listBox1.Items.Add ("1) Executing SP. Results returned as DataSet objects. Result is ");
   listBox1.Items.Add ("");

   object[] results;

   if (textBox1.Text == "NULL" || textBox1.Text == "null")
     inParam = null;
   else
     inParam = textBox1.Text;

   results = proxy.GetCustomerInfo(inParam, ref outParam);

   for (int j = 0; j < results.Length; j++)
   {
object e1;
server.SqlMessage errorMessage;
System.Xml.XmlElement xmlResult;
System.Data.DataSet resultDS; 

e1 = results[j];

//return value from SP is an int
if (e1.GetType ().IsPrimitive)
{
   listBox1.Items.Add ("Printing Returned Code from SP:");
   listBox1.Items.Add ("The type of the element in obj array is: ");
   listBox1.Items.Add (e1.GetType ());
   listBox1.Items.Add ("Return code = ");
   listBox1.Items.Add (e1);
}

switch (e1.ToString ())
{
   case "System.Data.DataSet":
listBox1.Items.Add ("Printing result of SELECT ");
resultDS = (System.Data.DataSet)results[j];
listBox1.Items.Add (resultDS.GetXml ());
break;

   case "NativeSOAPApp1.server.SqlRowCount":
listBox1.Items.Add ("Printing Sql Row count returned");
listBox1.Items.Add ("The type of the row count element in obj array is: ");
listBox1.Items.Add (e1.ToString ());
listBox1.Items.Add ("Row count =");
listBox1.Items.Add (((NativeSOAPApp1.server.SqlRowCount)results[j]).Count);
break;

   case "System.Xml.XmlElement":
listBox1.Items.Add ("Printing result of SELECT ...FOR XML");
listBox1.Items.Add ("The type of the result in obj array is: ");
listBox1.Items.Add (e1.ToString ());
listBox1.Items.Add ("This is the result :");
xmlResult = (System.Xml.XmlElement)results[j];
listBox1.Items.Add (xmlResult.OuterXml);
break;

   case "NativeSOAPApp1.server.SqlMessage":
listBox1.Items.Add ("Printing error msg, warning or other informational msg:");
listBox1.Items.Add ("The type of the corresponding  obj array element is: ");
listBox1.Items.Add (e1.ToString ());
listBox1.Items.Add ("This is the msg :");
errorMessage = (server.SqlMessage)results[j];
listBox1.Items.Add (errorMessage.Message);
listBox1.Items.Add (errorMessage.Source);
break;

   case "NativeSOAPApp1.server.SqlParameter":
listBox1.Items.Add ("Printing output params:");
listBox1.Items.Add ("The type of the corresponding  obj array element is: ");
listBox1.Items.Add (e1.ToString ());
listBox1.Items.Add ("Outparam name is :");
listBox1.Items.Add (((server.SqlParameter)results[j]).name);
listBox1.Items.Add ("Outparam value is :");
listBox1.Items.Add (((server.SqlParameter)results[j]).Value);
break;
}
   }

   // printing output param value
   listBox1.Items.Add ("Output Param: ");
   listBox1.Items.Add (outParam);

}