CODEWITHSUNDEEP
c#sql-server
Rohit
Rohit

Reputation: 300

Need to do in single hit to db

I am saving list of user preferences in db one by one which i want to save in a single hit.

This code is working fine but i am looking for optimized way of doing that.

What is the best optimized way to cater the same.

public void SavePreference(List<UserPreferences> userPreference, string tokenId, string clientIp, string endUserIp)
    {
        var tokenPersister = new TokenPersister();
        var loginMemberId = tokenPersister.GetMemberId(tokenId, clientIp, endUserIp);

        if (loginMemberId <= 0)
        {
            Audit.Add(AuditEventType.SaveUserPreference, Severity.High, loginMemberId, tokenId,
                     "SaveUserPreference Failed|InValid Session", clientIp, endUserIp);
            throw new BusinessServiceException(ErrorType.InValidSession, "User Session has been Expired, please login again");
        }
        foreach (var userPreferences in userPreference)
        {
            var flag = -1;
            var paramPreference = new DbParameter[6];
            paramPreference[0] = DataAccess.ParameterFactory.Create("@memberId", DbType.Int32, userPreferences.MemberId);
            paramPreference[1] = DataAccess.ParameterFactory.Create("@preferenceKey", DbType.String, userPreferences.Key);
            paramPreference[2] = DataAccess.ParameterFactory.Create("@value", DbType.String, userPreferences.PreferenceValue);
            paramPreference[3] = DataAccess.ParameterFactory.Create("@category", DbType.String, userPreferences.Category);
            paramPreference[4] = DataAccess.ParameterFactory.Create("@itemTypeId", DbType.Int32, (int)userPreferences.ItemTypeId);
            paramPreference[5] = DataAccess.ParameterFactory.Create("@flag", DbType.Int32, flag);
            paramPreference[5].Direction = ParameterDirection.Output;

            try
            {
                DataAccess.ExecuteNonQuery(SpNames.SavePreference, paramPreference);
                flag = (int)paramPreference[5].Value;
                Audit.Add(AuditEventType.SaveUserPreference, Severity.Low, loginMemberId, tokenId, "SaveUserPreferences: SavePreference Successfully",
                    clientIp, endUserIp);
                if (flag == -1)
                {
                    throw new BusinessServiceException(ErrorType.InvalidRequest, "Site name " + userPreferences.PreferenceValue + " already exists");
                }

            }

            catch (Exception ex)
            {

            }
        }

    }

Upvotes: 3

Views: 101

Answers (1)

usr
usr

Reputation: 171206

Use Table-Valued Parameters (TVP) to send the entire list at once as a single parameter. Insert all of them using INSERT ... SELECT syntax. You'll find examples of this on the web. It is a common technique.

Using a stored procedure will not gain you any performance here. Make sure to use parameterized SQL, though.

Upvotes: 2

Related Questions