In ActivexDataObject we use Sql classes to perform multiple operations related to the database and to the datasources.

We write SQL statements directly into the Sql class object or we can write the stored procedure and just invoke it in the Sql class object.

We first write the stored procedure for passing it into the Sql object.

Stored procedure are defined in the database and inside code we just invoke it by its name.

Stored procedure can be parametrized or non-parameterized according to the need.

CREATE PROC [dbo].[uspRegisterUser]         
	@FirstName VARCHAR(50),    
	@LastName VARCHAR(50),
	@JobRoleID BIGINT,
	@Email VARCHAR(50),
	@CompanyID BIGINT,
	@Password VARCHAR(50),
	@PasswordSalt VARCHAR(50), 
	@AccessToken VARCHAR(50), 
	@DeviceUUID VARCHAR(500), 
	@DeviceType VARCHAR(150), 
	@CreatedDate DATETIME,
	@IsDeleted BIT,
	@DeletedDate DATETIME
  
AS      
IF NOT EXISTS (SELECT 1 FROM [dbo].[User] WHERE Email = @Email)      
BEGIN      
	DECLARE @EncryptedPassword as VARBINARY(MAX);    
	SELECT @EncryptedPassword = ENCRYPTBYPASSPHRASE (@PasswordSalt, @Password);    
     
	INSERT INTO [dbo].[User]
        ([FirstName]
        ,[LastName]
        ,[JobRoleID]
        ,[Email]
        ,[CompanyID]
        ,[Password]
        ,[PasswordSalt]
        ,[AccessToken]
		,[DeviceUUID]
		,[DeviceType]
        ,[CreatedDate]
        ,[IsDeleted]
		,[DeletedDate])
    VALUES
        (@FirstName
        ,@LastName
        ,@JobRoleID
        ,@Email
        ,@CompanyID
        ,@EncryptedPassword
        ,@PasswordSalt
        ,@AccessToken
		,@DeviceUUID
		,@DeviceType
        ,@CreatedDate
		,@IsDeleted
        ,@DeletedDate)

		SELECT 1
END      
ELSE      
BEGIN      
		SELECT 2      
END 

After that we will pass that stored procedure into our application code by just passing the stored procedure name.

try
            {
                int result = 0;
                string accessToken = Guid.NewGuid().ToString();
                empResponse = new EmployeeResponse();
                using (SqlConnection sqlConnection = new SqlConnection(ConnectionString))
                {
                    if (sqlConnection.State == ConnectionState.Closed)
                    {
                        sqlConnection.Open();
                    }
                    SqlCommand cmd = new SqlCommand("uspRegisterUser", sqlConnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 50).Value = empobj.FirstName;
                    cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = empobj.LastName;
                    cmd.Parameters.Add("@ProfileImage", SqlDbType.VarChar, 500).Value = ConfigurationManager.AppSettings["ProfileImageBaseAddress"].ToString();
                    cmd.Parameters.Add("@JobRoleID", SqlDbType.BigInt).Value = empobj.JobRoleID;
                    cmd.Parameters.Add("@Email", SqlDbType.VarChar, 200).Value = empobj.Email;
                    cmd.Parameters.Add("@CompanyID", SqlDbType.Int).Value = empobj.CompanyId;
                    cmd.Parameters.Add("@Password", SqlDbType.VarChar, 50).Value = empobj.Password;
                    cmd.Parameters.Add("@PasswordSalt", SqlDbType.VarChar, 100).Value = Guid.NewGuid().ToString();
                    cmd.Parameters.Add("@AccessToken", SqlDbType.VarChar, 100).Value = accessToken;
                    cmd.Parameters.Add("@DeviceUUID", SqlDbType.VarChar, 50).Value = empobj.DeviceUUID;
                    cmd.Parameters.Add("@DeviceType", SqlDbType.VarChar, 50).Value = empobj.DeviceType;
                    cmd.Parameters.Add("@CreatedDate", SqlDbType.VarChar, 50).Value = System.DateTime.Now;
                    cmd.Parameters.Add("@IsDeleted", SqlDbType.VarChar, 50).Value = empobj.IsDeleted;
                    cmd.Parameters.Add("@DeletedDate", SqlDbType.Date).Value = System.DateTime.Now;
                    result = Convert.ToInt32(cmd.ExecuteNonQuery());
                    switch (result)
                    {
                        case 1:
                            empResponse.Status = true;
                            empResponse.Message = "User successfully registered.";
                            empobj.AccessToken = accessToken;
                            break;
                        case 2:
                            empResponse.Status = false;
                            empResponse.Message = "Email already exists.";
                            break;
                        default:
                            empResponse.Status = false;
                            empResponse.Message = "Registration failed.";
                            break;
                    }
                }
            }
            catch (SqlException ex)
            {
                Logger.LogException(ex);
                empResponse.Status = false;
                empResponse.Message = ex.Message;
            }