Need some help with C# and MS SQL

[crzyone9584]

So here is my login stored procedure. I want this stored procedure to return 2 things after the user logs in. 1 to be the user id. and 2 to be the user rank. both are ints.

Code: Select all

-- =============================================
-- Author: JR Padfield		
-- Create date: 8/21/2011 
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[Account_Authenticate] 
	@Username nvarchar(50), 
	@Password nvarchar(50) 
AS
BEGIN
	SET NOCOUNT ON;

    DECLARE @ID bigint
	DECLARE @RoleName varchar(30)
	DECLARE @LastLogin datetime
	
	SELECT @ID = dbo.Users.ID, @RoleName =  dbo.Roles.Role, @LastLogin = LastLoginDate
	FROM dbo.Users INNER JOIN dbo.Roles ON dbo.Users.RoleID = dbo.Roles.RoleID 
	WHERE UserName = @UserName AND Password = @Password
	
	IF @ID IS NOT NULL
	BEGIN
		UPDATE Users SET LastLoginDate = GetDate() WHERE ID = @ID
		
		SELECT  dbo.Users.ID, dbo.Roles.Role, dbo.Users.IsLockedOut, dbo.Users.LockOutReason, 
				@LastLogin As 'LastLoginDate', dbo.Users.LastLockoutDate, dbo.Users.LastPasswordChangeDate
		FROM    dbo.Users 
				INNER JOIN dbo.Roles ON dbo.Users.RoleID = dbo.Roles.RoleID
		WHERE	ID = @ID

	END
	
END

How would i get that procedure to return the ID and the RoleID using c#? I'm not very good with ms sql but once i have it once. I can always refer to it later and figure out how it works and practice it more.

[Baseball435]

I dont think many people here will know. Try on gamedev.

[Xaleph]

Actually I do, but you should create a new procedure for that. There`s a difference in authenticating a user and retrieving some data. Split it up, that`s my advice. Stored procedures btw, should do grind work, authentication could be considered grind, but it`s still a "delicate" operation to execute. Anyway, stored procedures are mostly for the bulk.

So 1: seperate retrieval and authentication
2. Use proc. for bulk operations.
3. Use proc. wisely. If T-SQL is still the same as it was 3 years ago, procs. will claim memory resources. So it might be wise to just query it without using the proc.

[crzyone9584]

Thanks for the info. I'll query the id and role into my list. Thanks a lot.

[Jackolantern]

Sorry for not seeing this earlier and chiming in! Every time I looked I kept reading it as "MySQL" for some reason and thinking "well I never used MySQL with C#".

3. Use proc. wisely. If T-SQL is still the same as it was 3 years ago, procs. will claim memory resources. So it might be wise to just query it without using the proc.

Seconded. It is still that way, at least as of 2008, which I think is still the most current if you don't count Azure.