Linq to SQl Stored Procedure Problem( it can't figure out the return type)

Question

Hi

I have this SP

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UsersInsert](@UpdatedProdData XML)
AS 
 INSERT INTO 
      dbo.UserTable(UserId,UserName,LicenseId,Password,PasswordSalt,Email,IsApproved,IsLockedOut,CreateDate,
      LastLoginDate,LastLockOutDate,FailedPasswordAttempts,RoleId)
      SELECT
         @UpdatedProdData.value('(/ArrayOfUsers/Users/UserId)[1]', 'uniqueidentifier'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/UserName)[1]', 'varchar(20)'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/LicenseId)[1]', 'varchar(50)'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/Password)[1]', 'varchar(128)'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/PasswordSalt)[1]', 'varchar(128)'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/Email)[1]', 'varchar(50)'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/IsApproved)[1]', 'bit'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/IsLockedOut)[1]', 'bit'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/CreateDate)[1]', 'datetime'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/LastLoginDate)[1]', 'datetime'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/LastLockOutDate)[1]', 'datetime'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/FailedPasswordAttempts)[1]', 'int'),
         @UpdatedProdData.value('(/ArrayOfUsers/Users/RoleId)[1]', 'int')

Now this SP creates just fine. It's when I go to VS2010 and try to drag this SP in my method panel of my linq to sql file in design view.

It tells me that it can't figure out the return type. I try to go to the properties but it does not have "none" as a choice and I can't type it in. It should be "none" so how do I set it to "none"?

Answer 1

You can edit the file .designer.cs of your .dbml , just edit the return of your stor. proc.

and you should also click in the .dbml file with the right button, and then, open as xml to edit it too.

Answer 2

Is there any particular reason you absolutely have to call this from your Linq-to-SQL model??

I would take the pragmatic approach:

• Linq-to-SQL is great for retrieving, handling, updating single records or small sets of data
• for bulk loads and/or stuff like this - why bother squeezing that into Linq-to-SQL?? Just new up a SqlConnection, a SqlCommand, and execute that stored proc....

UPDATE: to call the stored proc from straight ADO.NET, you'd use the standard ADO.NET stuff as any programming book on .NET data access or ADO.NET tutorial (just Google for that!) will teach you:

using(SqlConnection con = new SqlConnection(your-connection-string-here))
{
     string sprocName = "UsersInsert";

     using(SqlCommand cmd = new SqlCommand(sprocName, con))
     {
         cmd.CommandType = CommandType.StoredProcedure;

         cmd.CommandType = System.Data.CommandType.StoredProcedure;

         SqlParameter param1 = new SqlParameter("@UpdateXml", SqlDbType.VarChar, int.MaxValue);
         param1.Value = YourXmlValueHere;
         cmd.Parameters.Add(param1);

         con.Open();
         int result = cmd.ExecuteNonQuery();
         con.Close();
     }    
}

Of course, you might want to wrap this into a try...catch block for exception handling and so forth - but that's basically the code you'd need to call that stored proc using straight ADO.NET.