Using stored procedures with web service in Xamarin

Question

I am working in Xamarin Form ... I create web service ASMX and SQL Server Database, and it works properly. But when I replaced the query by stored procedures there is an error.

what I do :

First of all,

I create class its name DBConnection.cs for connection string

  public class DBConnection
    {
        public string ConnectionString
        {
            get
            {
                return "Data Source=...........;Initial Catalog=..........;User Id=......; Password=......;";
            }
        }
    }

After that, I create the stored procedure Login

CREATE PROCEDURE [dbo].[Login] (
   @UserNameLogin NCHAR (20), 
   @UserPasswordLogin NCHAR (30)
   )
AS
   SELECT * FROM [Us] WHERE UserName=@UserNameLogin AND UserPassword=@UserPasswordLogin
RETURN 0

Finally, I used the code-behind to connect

public Result Login(string UserName, string UserPassword)
       {

           SqlConnection con = new SqlConnection(new DBConnection().ConnectionString);

           /// Class for return bool vaulable 
           Result result = new Result();
           try
           {

               SqlCommand com = new SqlCommand("Login", con);

               com.Parameters.AddWithValue("@UserNameLogin", UserName);
               com.Parameters.AddWithValue("@UserPasswordLogin", UserPassword);
               com.Connection = con;
               if (con.State == System.Data.ConnectionState.Closed)
                   con.Open();
               SqlDataReader rd = com.ExecuteReader();
               if (rd.HasRows)
               {
                   rd.Read();
                   result.ValidUser = true;
                   return result;
               }

Where is the problem that System.Data.SqlClient.SqlException (0x80131904): Procedure or function 'Login' expects parameter '@UserNameLogin', which was not supplied.

and how to fix it? did I need configurationManager ? and if it yes how to write the correct code

Does this answer your question? [Call a stored procedure with parameter in c#](https://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp) — Martheen, May 09 '20 at 16:23
I don't think that, I'm not using asp.net. My case is Xamarin Form X — Hello World, May 09 '20 at 16:27
You get that error when you pass a parameter that has a null value and there is no default on the stored procedure. Have you stepped through with the debugger to look at the value of `UserName`? — Crowcoder, May 09 '20 at 16:38
Taking a closer look it shouldn't work at all because you haven't set the `CommandType` to stored procedure. Is that really the exact code you are executing? — Crowcoder, May 09 '20 at 16:40
this has nothing to do with Xamarin, this is purely a c#/sql issue. The web service doesn't know what kind of client is calling it — Jason, May 09 '20 at 16:47
And as usual, let's add [don't use addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/). — SMor, May 09 '20 at 18:33

score 1 · Accepted Answer · answered May 09 '20 at 16:57

1

I think you have to specify com.CommandType = CommandType.StoredProcedure;

answered May 09 '20 at 16:57

acinace

96
4

That's only part of the problem, you would not get the reported error if the command tried to execute that as CommandType.Text which is the default. – Crowcoder May 09 '20 at 17:07

Using stored procedures with web service in Xamarin

1 Answers1