using Sbroenne.ExcelMcp.ComInterop;
using Sbroenne.ExcelMcp.ComInterop.Session;
using Sbroenne.ExcelMcp.Core.PowerQuery;
namespace Sbroenne.ExcelMcp.Core.Commands;
/// <summary>
/// Connection operations (LoadTo, Test)
/// </summary>
public partial class ConnectionCommands
{
/// <summary>
/// Loads connection data to a worksheet
/// </summary>
public void LoadTo(IExcelBatch batch, string connectionName, string sheetName)
{
using var timeoutCts = new CancellationTokenSource(TimeSpan.FromMinutes(5));
batch.Execute((ctx, ct) =>
{
dynamic? conn = null;
dynamic? sheets = null;
dynamic? targetSheet = null;
try
{
conn = ComUtilities.FindConnection(ctx.Book, connectionName);
if (conn == null)
{
throw new InvalidOperationException($"Connection '{connectionName}' not found.");
}
// Check if this is a Power Query connection
if (PowerQueryHelpers.IsPowerQueryConnection(conn))
{
throw new InvalidOperationException($"Connection '{connectionName}' is a Power Query connection. Use 'pq-loadto' command instead.");
}
// Find or create target sheet
sheets = ctx.Book.Worksheets;
for (int i = 1; i <= sheets.Count; i++)
{
dynamic? sheet = null;
try
{
sheet = sheets.Item(i);
if (sheet.Name.ToString().Equals(sheetName, StringComparison.OrdinalIgnoreCase))
{
targetSheet = sheet;
sheet = null; // Don't release in finally since we're keeping reference
break;
}
}
finally
{
ComUtilities.Release(ref sheet);
}
}
if (targetSheet == null)
{
targetSheet = sheets.Add();
targetSheet.Name = sheetName;
}
// Remove existing QueryTables first
PowerQueryHelpers.RemoveQueryTables(ctx.Book, connectionName);
// Create QueryTable to load data
var options = new PowerQueryHelpers.QueryTableOptions
{
Name = connectionName,
RefreshImmediately = true
};
CreateQueryTableForConnection(targetSheet, conn, options);
return 0;
}
finally
{
ComUtilities.Release(ref targetSheet);
ComUtilities.Release(ref sheets);
ComUtilities.Release(ref conn);
}
}, timeoutCts.Token);
}
/// <summary>
/// Gets connection properties
/// </summary>
public void Test(IExcelBatch batch, string connectionName)
{
batch.Execute((ctx, ct) =>
{
dynamic? conn = ComUtilities.FindConnection(ctx.Book, connectionName);
if (conn == null)
{
throw new InvalidOperationException($"Connection '{connectionName}' not found.");
}
// Get connection type
int connType = conn.Type;
// For Text (4) and Web (5) connections, connection string might not be accessible
// until a QueryTable is created. Just verify the connection object exists.
if (connType is 4 or 5)
{
return 0;
}
// For other connection types (OLEDB, ODBC), validate connection string
string? connectionString = GetConnectionString(conn);
if (string.IsNullOrWhiteSpace(connectionString))
{
throw new InvalidOperationException("Connection has no connection string configured");
}
// Connection exists and is accessible
return 0;
});
}
}
