Stored Procedure, Web Service, Dataset, Argh...

[kleptos]

I have a Web Service that is calling a Stored Procedure in MS SQL 2000, the only problem is i dont remember how to get the returned results from the procedure to show from my web service. I can execute a procedure with no problem as long as i dont get data back, but i am running a procedure to get data back, my return type for the procedure is a DataSet, so i guess i need to get the procedure results into my dataset to return. Help!

Thanks!

[Serge]

Something like this:

[WebMethod]
public DataSet getMyData()
{
    try
    {
        SqlConnection cn = new SqlConnection("YourConnectionStringHere");
        cn.Open();

        SqlCommand cm = new SqlCommand("StoredProcName", cn);
        SqlDataAdapter da;
        DataSet ds = new DataSet();

        cm.CommandType = CommandType.StoredProcedure;

        //if you have parameters, add them here
        //cm.Parameters.Add("@ParameterName", SqlDbType.Int);
        //cm.Parameters["@ParameterName"].Value = YourValueHere;

        da = new SqlDataAdapter(cm);
        da.Fill(ds, "MyData");

        cn.Close();
        return ds;
    }
    catch(Exception ex)
    {
        //here do something with the execption
    }
}

[kleptos]

That worked! Thanks!

[k_glitch]

Dear Serge or any one else can explain these steps.... infact i will be realy thankful

[DNA7433]

He created a method, getMyData, that can be called as a webservice method to return a dataset object. The method simply creates a connection to the database, creates a dataset object, fills that object with data, and returns it. Does that make sense?

[k_glitch]

thanks DNA