Webservice help

Question

How would I refactor this to get it to return a string not a dataset?

[WebMethod] public DataSet GetPONumber(string Database) { SqlConnection sqlConn = new SqlConnection();

        sqlConn.ConnectionString = GetConnString(Database);

        // build query
        string strSQL = @" A SELECT QUERY!!!!! ";

        SqlDataAdapter da = new SqlDataAdapter(strSQL, sqlConn);

        DataSet ds = new DataSet();

        da.Fill(ds, "NEWPO");

        return (ds);
    }

Answer 1

You could transform the dataset into its JSON string representation. This way it can be easily consumed by basically any client.

Answer 2

You could return ds.GetXml() and change the return type.

That would return the data as XML.

If your results were quite simple (Say a single value), you might just want to return them directly.

Answer 3

//Use an SqlCommand and the ExecuteScalar method.
//Cast returnValue to known object.
SqlCommand command = sqlConn.CreateCommand();
command.CommandType = CommandType.Text;
command.CommandText = @" A SELECT QUERY!!!!! ";
sqlConn.Open();
object returnValue = command.ExecuteScalar();
command.Dispose();
return returnValue.ToString();

Answer 4

I have tryed the above and get the following error:

System.InvalidOperationException: ExecuteScalar requires an open and available Connection. The connection's current state is closed. at System.Data.SqlClient.SqlConnection.GetOpenConnection(String method) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteScalar() at NavisionLink.NavisionLink.GetPONumber(String Database) in D:\MyDev\NavisionLink\NavisionLink.asmx.cs:line 283

Copy of the code now:

    public String GetPONumber(string Database)
    {
        SqlConnection sqlConn = new SqlConnection(GetConnString(Database));

        sqlConn.ConnectionString = GetConnString(Database);

        SqlCommand command = sqlConn.CreateCommand();

        command.CommandType = CommandType.Text;

        command.CommandText = @" Query Here!! ";

        object returnValue = command.ExecuteScalar();

        command.Dispose();

        return returnValue.ToString();
    }

Answer 5

This is what I finnished with and is working, thank you for your input:

[WebMethod]
    public String GetPONumber(string Database)
    {   
        //Create Object ready for Value
        object po = "";

        //Set Connection
        SqlConnection Connection = new SqlConnection(GetConnString(Database));

        //Open Connection
        Connection.Open();

        //Set Query to string
        string Query = @" SQL QUERY GOES HERE!!!! ";

        //Run Query
        SqlCommand Command = new SqlCommand(Query, Connection);

        //Set Value from Query
        try
        {
            po = Command.ExecuteScalar();
        }
        catch
        {
            //Error
        }

        //Clean up sql
        Command.Dispose();
        Command = null;


        //Clean up connection
        Connection.Close();
        Connection.Dispose();
        Connection = null;

        //Return Value
        return po.ToString();
    }

Answer 6

@MartGrif

I have modified your code to include the using statement, this is common usage. It also makes the code more terse and I believe, more readable. The using statement automatically disposes of object at the end of the code block. See documentation on MSDN here

[WebMethod]
public String GetPONumber(string Database)
{   
    //Create Object ready for Value
    object po = "";

    //Set Connection
    using(SqlConnection connection = new SqlConnection(GetConnString(Database)))
    {
        string Query = @" SQL QUERY GOES HERE!!!! ";
        using(SqlCommand command = new SqlCommand(Query, connection))
        {
            try
            {
                connection.Open();
                po = Command.ExecuteScalar();
            }
            catch
            {
                //Error
            }
        }
    }
    return po.ToString();
}