How to call a stored procedure thru webservice using c# ?

Question

I am new to web service,

The database access should be through Web Services using ADO.NET to access stored procedures.

any ideas ?

Answer 1

Please see The C# Station ADO.NET Tutorial - Lesson 07: Using Stored Procedures:

This lesson shows how to use stored procedures in your data access code. Here are the objectives of this lesson:

• Learn how to modify the SqlCommand object to use a stored procedure.
• Understand how to use parameters with stored procedures.

Answer 2

A simple one

Data Access through Web Services, Stored Procedures and SQL Query

Answer 3

Um... just have your page pass the stored proc values to the web service via a GET, the web service then feeds those values into the stored proc, and then the web service outputs the result back to the requesting page via JSON or XML?

Answer 4

If you start fresh, I would strongly recommend you start using WCF (instead of the old-style ASMX web services).

In this case, you'll need:

1) a Service Contract (an interface defining the operation(s) on your web service):

[ServiceContract]
public interface IMyDataService
{
    [OperationContract]
    YourDataType GetData(int idValue);
}

2) A Data Contract which will define the data structures for your calls (here: the return type YourDataType):

[DataContract]
public class YourDataType
{
    bool boolValue = true;
    string stringValue = "Hello ";

    [DataMember]
    public bool BoolValue
    {
        get { return boolValue; }
        set { boolValue = value; }
    }

    [DataMember]
    public string StringValue
    {
        get { return stringValue; }
        set { stringValue = value; }
    }
}

3) A Service Class that will actually implement your web service method and make the call to the database using the stored procedure - something like this:

public class YourDataService : IMyDataService
{
    public YourDataType GetData(int idValue)
    {
        YourDataType result = new YourDataType();

        using(SqlConnection _con = new SqlConnection("server=(local);database=test;integrated security=SSPI;"))
        {
            using(SqlCommand _cmd = new SqlCommand("YourStoredProcName", _con))
            {
                _cmd.Parameters.AddWithValue("@ID", idValue);

                _cmd.Parameters.Add("@OutStringValue", SqlDbType.VarChar, 20)
                               .Direction = ParameterDirection.Output;
                _cmd.Parameters.Add("@OutBoolValue", SqlDbType.Bit)
                               .Direction = ParameterDirection.Output;

                _cmd.ExecuteNonQuery();

                result.StringValue = _cmd.Parameters["@OutStringValue"].Value.ToString();
                result.BoolValue = Convert.ToBoolean(_cmd.Parameters["@OutBoolValue"].Value);

            }
        }

        return result;
    }
}

Here, be aware I am totally assuming what your stored procedure looks like (in my case, something like:

CREATE PROCEDURE dbo.YourStoredProcName
   (@ID INT, @OutStringValue VARCHAR(20) OUTPUT, @OutBoolValue BIT OUTPUT)

This might be totally different for you! You need to make sure to adapt this to your actual case!

4) Ultimately, you'll need a way to host your WCF service (in IIS, or yourself in a console app or NT Service) - that's just standard WCF work

5) Last but not least, your client app will need to "Add Service Reference" to that WCF service in order to be able to call it - again: this is totally standard WCF stuff, no special steps needed here.

So there you have it - a WCF service calling your stored proc in the database, returning some values in a custom data type back to the caller.

Marc