WebMethod failure

Question

I have the following webmethod on a asmx page that will not connect to my database. Can anyone shed any light on where I have gone wrong.

[WebMethod]
    public int[] getEmployeeIDs()
    {
        Connection conn = new Connection();
        Recordset rs = new Recordset();
        conn.ConnectionString = "Data Source=MyWebBasedServer;Initial Catalog=MyDatabase;Persist Security Info=False;User ID=MyLogin;Password=MyPassword";
        rs.Open("SELECT ID from MyTable", conn, CursorTypeEnum.adOpenStatic);

        int[] ID = new int[rs.RecordCount];
        int i = 0;
        while (!rs.EOF)
        {
            ID[i++] = rs.Fields["ID"].Value;
            rs.MoveNext();
        }
        rs.Close();
        conn.Close();
        return ID;
    }

Error message I get is

The connection cannot be used to perform this operation. It is either closed or invalid in this context. (Pointing to "int[] ID = new int[rs.RecordCount];")

Thanks.

Looks like you need to call .open() on the Connection. Also: What database are you connecting to? Recordset looks like the old DAO classes to me. I haven't seen this syntax in many years. — Moby Disk, Oct 06 '15 at 17:08
Isn't the connection opened by the rs.Open when it uses the "conn" connection. I'm connecting to a database hosted by smarterasp.net its a mssql database. I've taken this off a youtube video. Maybe you could help me. All i'm trying to do it bring the list of ID's from MyTable from my database and going to take it into an android app. Any suggestions? — Chris Music, Oct 06 '15 at 17:28
The connection string looks suspicious as you're using ADO Connection class the connection string should have format like this : `"Provider=MySQLProv;server=MyWebBasedServer;uid=MyUserName;pwd=MyPassword;database=MyDatabase"` — vendettamit, Oct 06 '15 at 17:30
It's a MS Sql not MY Sql. Does that make a difference to the connection string? — Chris Music, Oct 06 '15 at 17:47
ohh.. my bad!! I read it wrong. yes it does make a difference. — vendettamit, Oct 06 '15 at 17:51
Why not use a DataReader or DataSet instead? The old Recordset objects are holdovers from code long long ago when ADODB was the current technology. And you should move your connection string to your config file so it can be accessed from one location. — Sean Lange, Oct 06 '15 at 18:35
Sounds like a good idea, but I'm still learning. Any hints of what I should put instead? — Chris Music, Oct 06 '15 at 18:42

Chris R. Timmons · Accepted Answer · 2015-10-06T22:05:29.303

The code below shows two common ways to retrieve a result set from a SELECT statement in ADO.Net.

There's a website called ConnectionStrings.com that shows all the various ways to connect to SQL Server, along with many other kinds of databases.

If you're new to C# programming, the using statement is a great way to avoid resource leaks when handling objects that implement IDisposable.

Returning complex types from a WebMethod might result in an error. The method's underlying XML serializer might not know how to handle certain types. In that case, XmlIncludeAttribute can be used to provide explicit type information. Here's an MSDN thread discussing how to go about that.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web.Services;

namespace ConsoleApplication19
{
  public class Program
  {
    public static void Main(String[] args)
    {
      var connectionString = "Data Source=MyWebBasedServer;Initial Catalog=MyDatabase;Persist Security Info=False;User ID=MyLogin;Password=MyPassword;";
      var a = GetEmployeeIDs_Version1(connectionString);
      var b = GetEmployeeIDs_Version2(connectionString);
    }

    /* Version 1

       Use a "while" loop to fill a WebData list and return it as an array. */

    [WebMethod]
    private static WebData[] GetEmployeeIDs_Version1(String connectionString)
    {
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();

        var commandText = "SELECT ID, SurName from MyTable";

        using (var command = new SqlCommand() { Connection = connection, CommandType = CommandType.Text, CommandText = commandText })
        {
          using (var reader = command.ExecuteReader(CommandBehavior.CloseConnection))
          {
            var result = new List<WebData>();

            while (reader.Read())
              result.Add(new WebData() { ID = Convert.ToInt32(reader["ID"]), Surname = reader["Surname"].ToString() });

            return result.ToArray();
          }
        }
      }
    }

    /* Version 2

       Fill a DataSet with the result set.

       Because there's only one SELECT statement, ADO.Net will
       populate a DataTable with that result set and put the
       DataTable in the dataset's Tables collection.

       Use LINQ to convert that table into a WebData array. */

    [WebMethod]
    private static WebData[] GetEmployeeIDs_Version2(String connectionString)
    {
      using (var connection = new SqlConnection(connectionString))
      {
        connection.Open();

        var commandText = "SELECT ID, SurName from MyTable";

        using (var command = new SqlCommand() { Connection = connection, CommandType = CommandType.Text, CommandText = commandText })
        {
          using (var adapter = new SqlDataAdapter())
          {
            var dataSet = new DataSet();
            adapter.SelectCommand = command;
            adapter.Fill(dataSet);

            return
              dataSet
              // There should only be one table in the dataSet's Table's collection.
              .Tables[0]
              .Rows
              // DataTable isn't LINQ-aware.  An explicit cast is needed
              // to allow the use of LINQ methods on the DataTable.Rows collection.
              .Cast<DataRow>()
              // The rows in a DataTable filled by an SqlDataAdapter
              // aren't strongly typed.  All of a row's columns are
              // just plain old System.Object.  Explicit casts are necessary.
              .Select(row => new WebData() { ID = Convert.ToInt32(row["ID"]), Surname = row["Surname"].ToString() })
              // Use LINQ to convert the IEnumerable<WebData> returned by
              // the .Select() method to an WebData[].
              .ToArray();
          }
        }
      }
    }
  }

  public class WebData
  {
    public Int32 ID { get; set; }
    public String Surname { get; set; }
  }
}

Thank you for your suggestions. As I'm taking this data into an Android app, I've been told that I should use webmethod as this keeps the connection string private. I've put both your suggestions in and both do exactly what I need them to, Could I trouble you further and ask how I go about bringing multiple columns into the array, e.g. ID and Surname — Chris Music, Oct 06 '15 at 20:59
Chris, I updated the example code, and added a couple of links about XmlIncludeAttribute if [WebMethod] has problems serializing a composite type like the WebData class. — Chris R. Timmons, Oct 06 '15 at 22:07

WebMethod failure

1 Answers1