# 賈斯汀貼的
###### tags: `火箭`

Create FUNCTION [dbo].[GetCaseID]()
RETURNS nvarchar(20)
AS
BEGIN
    DECLARE @OrderID nvarchar(20)
    DECLARE @DT nvarchar(20)
    SELECT @DT = convert(varchar(10),getdate(),112)
    SELECT @OrderID= @DT + right('0000' + ltrim(isnull(max(cast(right(CaseID, 5) as int)),0)+1), 5) from Cases where left(CaseID, 8) = @DT
    RETURN @OrderID
END

Create PROCEDURE [dbo].[CreateMember]
                @Account nvarchar(50),
                @Password nvarchar(100),
                @PasswordSalt nvarchar(100),
                @Name nvarchar(50),
                @Email nvarchar(50),
                @JobTitle nvarchar(50),
                @Permission nvarchar(500),
                @Poster nvarchar(20),
                @UnitId int,
                @Gender int,
                @MyPic nvarchar(50),
                @Id   int OUTPUT
AS
   DECLARE @ReturnCode    int
                 DECLARE @memberCount    int
                 select @memberCount= count(Id) from Members where Account= @Account
                 if @memberCount> 0
                 begin
                                 set  @ReturnCode= 0
                 end
                 else
                 begin
                                 insert into Members (
                                                Account ,Password,PasswordSalt, Name,Email,JobTitle ,Permission, Poster,UnitId ,Gender, MyPic
                                 ) VALUES
        (
                                   @Account,@Password,@PasswordSalt, @Name,@Email ,@JobTitle, @Permission,@Poster ,@UnitId, @Gender,@MyPic
                                 )
                                 set @ReturnCode = @@Identity
                 end
                 Select @Id= @ReturnCode;
SELECT * FROM Members  WHERE Id = @id;

public bool AddMember()
        {
            BackendContext db = new BackendContext();
            var pid = new SqlParameter { ParameterName = "Id", Value = 0, Direction = ParameterDirection.Output };
            var result =
                db.Members.SqlQuery(
                    "dbo.CreateMember @Account,@Password,@PasswordSalt,@Name,@Email,@JobTitle,@Permission,@Poster,@UnitId,@Gender,@MyPic,@Id out",
                    new SqlParameter("Account", this.Account), new SqlParameter("Password", this.Password),
                    new SqlParameter("@PasswordSalt", this.PasswordSalt),
                    new SqlParameter("Name", this.Name),
                    new SqlParameter("Email", this.Email), new SqlParameter("JobTitle", this.JobTitle),
                    new SqlParameter("Permission", this.Permission),
                    new SqlParameter("Poster", "admin"), new SqlParameter("UnitId", this.UnitId),
                    new SqlParameter("Gender", this.Gender),
                    new SqlParameter("MyPic", this.MyPic ?? ""), pid).SingleOrDefault();
            return pid.Value.ToString() != "0";
        }