update queries

1 files changed, 81 insertions, 40 deletions

diff --git a/Emby.Server.Implementations/Security/AuthenticationRepository.cs b/Emby.Server.Implementations/Security/AuthenticationRepository.cs
index f6163b80a..160e0f5d2 100644
--- a/Emby.Server.Implementations/Security/AuthenticationRepository.cs
+++ b/Emby.Server.Implementations/Security/AuthenticationRepository.cs
@@ -69,19 +69,30 @@ namespace Emby.Server.Implementations.Security
                 {
                     connection.RunInTransaction(db =>
                     {
-                        var commandText = "replace into AccessTokens (Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked) values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
-
-                        db.Execute(commandText,
-                            info.Id.ToGuidParamValue(),
-                            info.AccessToken,
-                            info.DeviceId,
-                            info.AppName,
-                            info.AppVersion,
-                            info.DeviceName,
-                            info.UserId,
-                            info.IsActive,
-                            info.DateCreated.ToDateTimeParamValue(),
-                            info.DateRevoked.HasValue ? info.DateRevoked.Value.ToDateTimeParamValue() : null);
+                        using (var statement = db.PrepareStatement("replace into AccessTokens (Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked) values (@Id, @AccessToken, @DeviceId, @AppName, @AppVersion, @DeviceName, @UserId, @IsActive, @DateCreated, @DateRevoked)"))
+                        {
+                            statement.BindParameters.TryBind("@Id", info.Id.ToGuidParamValue());
+                            statement.BindParameters.TryBind("@AccessToken", info.AccessToken);
+
+                            statement.BindParameters.TryBind("@DeviceId", info.DeviceId);
+                            statement.BindParameters.TryBind("@AppName", info.AppName);
+                            statement.BindParameters.TryBind("@AppVersion", info.AppVersion);
+                            statement.BindParameters.TryBind("@DeviceName", info.DeviceName);
+                            statement.BindParameters.TryBind("@UserId", info.UserId);
+                            statement.BindParameters.TryBind("@IsActive", info.IsActive);
+                            statement.BindParameters.TryBind("@DateCreated", info.DateCreated.ToDateTimeParamValue());
+
+                            if (info.DateRevoked.HasValue)
+                            {
+                                statement.BindParameters.TryBind("@DateRevoked", info.DateRevoked.Value.ToDateTimeParamValue());
+                            }
+                            else
+                            {
+                                statement.BindParameters.TryBindNull("@DateRevoked");
+                            }
+
+                            statement.MoveNext();
+                        }
                     });
                 }
             }
@@ -89,6 +100,29 @@ namespace Emby.Server.Implementations.Security
 
         private const string BaseSelectText = "select Id, AccessToken, DeviceId, AppName, AppVersion, DeviceName, UserId, IsActive, DateCreated, DateRevoked from AccessTokens";
 
+        private void BindAuthenticationQueryParams(AuthenticationInfoQuery query, IStatement statement)
+        {
+            if (!string.IsNullOrWhiteSpace(query.AccessToken))
+            {
+                statement.BindParameters.TryBind("@AccessToken", query.AccessToken);
+            }
+
+            if (!string.IsNullOrWhiteSpace(query.UserId))
+            {
+                statement.BindParameters.TryBind("@UserId", query.UserId);
+            }
+
+            if (!string.IsNullOrWhiteSpace(query.DeviceId))
+            {
+                statement.BindParameters.TryBind("@DeviceId", query.DeviceId);
+            }
+
+            if (query.IsActive.HasValue)
+            {
+                statement.BindParameters.TryBind("@IsActive", query.IsActive.Value);
+            }
+        }
+
         public QueryResult<AuthenticationInfo> Get(AuthenticationInfoQuery query)
         {
             if (query == null)
@@ -99,7 +133,6 @@ namespace Emby.Server.Implementations.Security
             using (var connection = CreateConnection(true))
             {
                 var commandText = BaseSelectText;
-                var paramList = new List<object>();
 
                 var whereClauses = new List<string>();
 
@@ -107,26 +140,22 @@ namespace Emby.Server.Implementations.Security
 
                 if (!string.IsNullOrWhiteSpace(query.AccessToken))
                 {
-                    whereClauses.Add("AccessToken=?");
-                    paramList.Add(query.AccessToken);
+                    whereClauses.Add("AccessToken=@AccessToken");
                 }
 
                 if (!string.IsNullOrWhiteSpace(query.UserId))
                 {
-                    whereClauses.Add("UserId=?");
-                    paramList.Add(query.UserId);
+                    whereClauses.Add("UserId=@UserId");
                 }
 
                 if (!string.IsNullOrWhiteSpace(query.DeviceId))
                 {
-                    whereClauses.Add("DeviceId=?");
-                    paramList.Add(query.DeviceId);
+                    whereClauses.Add("DeviceId=@DeviceId");
                 }
 
                 if (query.IsActive.HasValue)
                 {
-                    whereClauses.Add("IsActive=?");
-                    paramList.Add(query.IsActive.Value);
+                    whereClauses.Add("IsActive=@IsActive");
                 }
 
                 if (query.HasUser.HasValue)
@@ -171,20 +200,30 @@ namespace Emby.Server.Implementations.Security
 
                 var list = new List<AuthenticationInfo>();
 
-                foreach (var row in connection.Query(commandText, paramList.ToArray()))
+                using (var statement = connection.PrepareStatement(commandText))
                 {
-                    list.Add(Get(row));
-                }
+                    BindAuthenticationQueryParams(query, statement);
 
-                var count = connection.Query("select count (Id) from AccessTokens" + whereTextWithoutPaging, paramList.ToArray())
-                    .SelectScalarInt()
-                    .First();
+                    foreach (var row in statement.ExecuteQuery())
+                    {
+                        list.Add(Get(row));
+                    }
 
-                return new QueryResult<AuthenticationInfo>()
-                {
-                    Items = list.ToArray(),
-                    TotalRecordCount = count
-                };
+                    using (var totalCountStatement = connection.PrepareStatement("select count (Id) from AccessTokens" + whereTextWithoutPaging))
+                    {
+                        BindAuthenticationQueryParams(query, totalCountStatement);
+
+                        var count = totalCountStatement.ExecuteQuery()
+                            .SelectScalarInt()
+                            .First();
+
+                        return new QueryResult<AuthenticationInfo>()
+                        {
+                            Items = list.ToArray(),
+                            TotalRecordCount = count
+                        };
+                    }
+                }
             }
         }
 
@@ -199,16 +238,18 @@ namespace Emby.Server.Implementations.Security
             {
                 using (var connection = CreateConnection(true))
                 {
-                    var commandText = BaseSelectText + " where Id=?";
-                    var paramList = new List<object>();
-
-                    paramList.Add(id.ToGuidParamValue());
+                    var commandText = BaseSelectText + " where Id=@Id";
 
-                    foreach (var row in connection.Query(commandText, paramList.ToArray()))
+                    using (var statement = connection.PrepareStatement(commandText))
                     {
-                        return Get(row);
+                        statement.BindParameters["@Id"].Bind(id.ToGuidParamValue());
+
+                        foreach (var row in statement.ExecuteQuery())
+                        {
+                            return Get(row);
+                        }
+                        return null;
                     }
-                    return null;
                 }
             }
         }