public void CleanUpOldToken(string token, int recordId, DownloadTokenType tokenType)
{
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
// Alle nicht mehr gültigen Tokens aus der DB entfernen
var query = new StringBuilder();
query.Append(
"DELETE FROM DownloadToken WHERE ExpiryTime < @expiryTime OR (Token LIKE @token AND recordId = @recordId AND tokenType = @tokenType)");
cmd.CommandText = query.ToString();
cmd.Parameters.Add(new SqlParameter
{
Value = DateTime.Now,
ParameterName = "expiryTime",
SqlDbType = SqlDbType.DateTime2
});
cmd.Parameters.Add(new SqlParameter
{
Value = token,
ParameterName = "token",
SqlDbType = SqlDbType.NVarChar
});
cmd.Parameters.Add(new SqlParameter
{
Value = recordId,
ParameterName = "recordId",
SqlDbType = SqlDbType.Int
});
cmd.Parameters.Add(new SqlParameter
{
Value = tokenType.ToString(),
ParameterName = "tokenType",
SqlDbType = SqlDbType.NVarChar
});
cmd.ExecuteNonQuery();
}
}
}
public bool CheckTokenIsValidAndClean(string token, int recordId, DownloadTokenType tokenType, string ipAdress)
{
if (string.IsNullOrEmpty(token))
{
return(false);
}
if (recordId <= 0)
{
return(false);
}
if (string.IsNullOrEmpty(ipAdress))
{
return(false);
}
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
var timeNow = DateTime.Now;
int foundToken;
using (var cmd = cn.CreateCommand())
{
var query = new StringBuilder();
query.Append("SELECT COUNT(*) ");
query.Append("FROM DownloadToken ");
query.Append(
"WHERE Token LIKE @token AND IpAdress LIKE @ipAddress AND ExpiryTime >= @expiryTime AND recordId = @recordId AND tokenType = @tokenType");
cmd.CommandText = query.ToString();
cmd.Parameters.Add(new SqlParameter
{
Value = token,
ParameterName = "token",
SqlDbType = SqlDbType.NVarChar
});
cmd.Parameters.Add(new SqlParameter
{
Value = ipAdress,
ParameterName = "ipAddress",
SqlDbType = SqlDbType.NVarChar
});
cmd.Parameters.Add(new SqlParameter
{
Value = timeNow,
ParameterName = "expiryTime",
SqlDbType = SqlDbType.DateTime2
});
cmd.Parameters.Add(new SqlParameter
{
Value = recordId,
ParameterName = "recordId",
SqlDbType = SqlDbType.Int
});
cmd.Parameters.Add(new SqlParameter
{
Value = tokenType.ToString(),
ParameterName = "tokenType",
SqlDbType = SqlDbType.NVarChar
});
foundToken = Convert.ToInt32(cmd.ExecuteScalar());
}
return(foundToken > 0);
}
}
bool IDownloadTokenDataAccess.CreateToken(string token, int recordId, DownloadTokenType tokenType, DateTime tokenExpiryTime, string ipAdress,
string userId)
{
if (string.IsNullOrEmpty(token))
{
return(false);
}
if (recordId <= 0)
{
return(false);
}
if (string.IsNullOrEmpty(ipAdress))
{
return(false);
}
const string query =
"INSERT INTO DownloadToken (Token, ExpiryTime, IpAdress, recordId, UserId, TokenType) VALUES (@token, @expiryTime, @ipAddress, @recordId, @userId, @tokenType)";
var parameterList = new List <SqlParameter>
{
new SqlParameter
{
Value = token,
ParameterName = "token",
SqlDbType = SqlDbType.NVarChar
},
new SqlParameter
{
Value = tokenExpiryTime,
ParameterName = "expiryTime",
SqlDbType = SqlDbType.DateTime2
},
new SqlParameter
{
Value = ipAdress,
ParameterName = "ipAddress",
SqlDbType = SqlDbType.NVarChar
},
new SqlParameter
{
Value = recordId,
ParameterName = "recordId",
SqlDbType = SqlDbType.Int
},
new SqlParameter
{
Value = userId,
ParameterName = "userId",
SqlDbType = SqlDbType.NVarChar
},
new SqlParameter
{
Value = tokenType.ToString(),
ParameterName = "tokenType",
SqlDbType = SqlDbType.NVarChar
}
};
return(DataAccessExtensions.CreateNewItem(connectionString, query, parameterList) != null);
}
public string GetUserIdByToken(string token, int recordId, DownloadTokenType tokenType, string ipAdress)
{
if (string.IsNullOrEmpty(token))
{
return("");
}
if (recordId <= 0)
{
return("");
}
if (string.IsNullOrEmpty(ipAdress))
{
return("");
}
string foundUserId;
using (var cn = new SqlConnection(connectionString))
{
cn.Open();
using (var cmd = cn.CreateCommand())
{
var query = new StringBuilder();
query.Append("SELECT UserId ");
query.Append("FROM DownloadToken ");
query.Append("WHERE Token = @token AND IpAdress = @ipAddress AND recordId = @recordId AND tokenType = @tokenType");
cmd.CommandText = query.ToString();
cmd.Parameters.Add(new SqlParameter
{
Value = token,
ParameterName = "token",
SqlDbType = SqlDbType.NVarChar
});
cmd.Parameters.Add(new SqlParameter
{
Value = ipAdress,
ParameterName = "ipAddress",
SqlDbType = SqlDbType.NVarChar
});
cmd.Parameters.Add(new SqlParameter
{
Value = recordId,
ParameterName = "recordId",
SqlDbType = SqlDbType.Int
});
cmd.Parameters.Add(new SqlParameter
{
Value = tokenType.ToString(),
ParameterName = "tokenType",
SqlDbType = SqlDbType.NVarChar
});
foundUserId = cmd.ExecuteScalar() as string;
}
}
return(foundUserId);
}
