RRI_Template_DataSQL.zip (708.0 KB)
In the attached file, I have 4 external connections. 3 of them are SQL connections, however none of the 3 connections are recognized as type DBConnection. I’m using Aspose.Cells 22.9.0 and the following code:
ExternalConnectionCollection connections = workbook.DataConnections;
ExternalConnection connection = null;
for (int i = 0; i < connections.Count; i++)
{
connection = connections[i];
// Check if the Connection is DBConnection, then retrieve its various properties
if (connection is DBConnection)
{
DBConnection dbConn = (DBConnection)connection;
// Retrieve DB Connection Command
Console.WriteLine("Command: " + dbConn.Command);
// Retrieve DB Connection Command Type
Console.WriteLine("Command Type: " + dbConn.CommandType);
// Retrieve DB Connection Description
Console.WriteLine("Description: " + dbConn.ConnectionDescription);
// Retrieve DB Connection ID
Console.WriteLine("Id: " + dbConn.ConnectionId);
// Retrieve DB Connection Info
Console.WriteLine("Info: " + dbConn.ConnectionInfo);
// Retrieve DB Connection Credentials
Console.WriteLine("Credentials: " + dbConn.Credentials);
// Retrieve DB Connection Name
Console.WriteLine("Name: " + dbConn.Name);
// Retrieve DB Connection ODC File
Console.WriteLine("OdcFile: " + dbConn.OdcFile);
// Retrieve DB Connection Source File
Console.WriteLine("Source file: " + dbConn.SourceFile);
// Retrieve DB Connection Type
Console.WriteLine("Type: " + dbConn.Type);
}
}
@mraduenzel,
In your file, there are 3 OLEDBDataModel connections and 1 other OLEDB based source connection. Please try the following sample code:
.........
// Check all the connections inside the workbook
for (int i = 0; i < workbook.DataConnections.Count; i++)
{
Aspose.Cells.ExternalConnections.ExternalConnection externalConnection = workbook.DataConnections[i];
DBConnection dbConn = externalConnection as DBConnection;
if (dbConn != null)
{
Console.WriteLine("Connection Name: " + dbConn.Name);
Console.WriteLine("Info: " + dbConn.ConnectionInfo);
Console.WriteLine("Command type: " + dbConn.CommandType);
Console.WriteLine("Command: " + dbConn.Command);
Console.WriteLine("Description: " + dbConn.ConnectionDescription);
Console.WriteLine("Id: " + dbConn.ConnectionId);
Console.WriteLine("Credentials: " + dbConn.CredentialsMethodType);
Console.WriteLine("Type: " + dbConn.Type);
Console.WriteLine("########################");
}
else
{
Console.WriteLine("Connection Name: " + workbook.DataConnections[i].Name);
Console.WriteLine("Description info: " + workbook.DataConnections[i].ConnectionDescription);
Console.WriteLine("Connection ID: " + workbook.DataConnections[i].ConnectionId);
Console.WriteLine("Credentials: " + workbook.DataConnections[i].CredentialsMethodType);
Console.WriteLine("Type: " + workbook.DataConnections[i].Type);
Console.WriteLine("########################");
}
}
Hope, this helps a bit.
This is what I’m getting back. I need to be able to change the connection string on the OLEDBDataModel types, but when I try to cast them to a DBConnection it comes up null in your code. What am I missing?
Connection Name: CAC Data
Description info:
Connection ID: 1
Credentials: Integrated
Type: WorksheetDataModel
########################
Connection Name: ContractData
Description info: Data Connection for Renewal
Connection ID: 2
Credentials: Integrated
Type: OLEDBDataModel
########################
Connection Name: RevenueData
Description info: Data Connection for RF and Cohort
Connection ID: 3
Credentials: Integrated
Type: OLEDBDataModel
########################
Connection Name: ThisWorkbookDataModel
Info: Data Model Connection
Command type: CubeName
Command: Model
Description: Data Model
Id: 4
Credentials: Integrated
Type: OLEDBBasedSource
########################
@mraduenzel,
Could you please share your updated sample code on how you are modify connections and other attributes, we will check it soon.
I used the exact same code you sent me so I could be sure it was correct. The output I provided is from your code you sent.
@mraduenzel,
We will evaluate your requirements further and get back to you soon.
@mraduenzel,
We found an issue regarding external data connections in Aspose.Cells APIs. A ticket with an id “CELLSNET-52065” is logged into our database. We will look into it soon. Once we have an update on it, we will let you know.
@mraduenzel,
We have fixed the issue now. The fix will be included in the next release (Aspose.Cells for .NET v22.10) which is scheduled in the first/second week of October 2022.
Please note, there are four data connections in the original file, including three DataModelConnections and one DBConnection DataModelConnection class, which was used to be internal, will be shown from the next release. So, you will judge the conversion according to the type. You will try the following sample code with upcoming version:
e.g.
Sample code:
Workbook workbook = new Workbook("RRI_Template_DataSQL.xlsb");
for (int i = 0; i < workbook.DataConnections.Count; i++)
{
Aspose.Cells.ExternalConnections.ExternalConnection externalConnection = workbook.DataConnections[i];
if (externalConnection is DBConnection)
{
DBConnection dbConn = externalConnection as DBConnection;
Console.WriteLine("Connection Name: " + dbConn.Name);
Console.WriteLine("Info: " + dbConn.ConnectionInfo);
Console.WriteLine("Command type: " + dbConn.CommandType);
Console.WriteLine("Command: " + dbConn.Command);
Console.WriteLine("Description: " + dbConn.ConnectionDescription);
Console.WriteLine("Id: " + dbConn.ConnectionId);
Console.WriteLine("Credentials: " + dbConn.CredentialsMethodType);
Console.WriteLine("Type: " + dbConn.Type);
Console.WriteLine("########################");
}
else if (externalConnection is DataModelConnection)
{
DataModelConnection dataModelConnection = externalConnection as DataModelConnection;
Console.WriteLine("Connection Name: " + dataModelConnection.Name);
Console.WriteLine("Description info: " + dataModelConnection.ConnectionDescription);
Console.WriteLine("Connection ID: " + dataModelConnection.ConnectionId);
Console.WriteLine("Credentials: " + dataModelConnection.CredentialsMethodType);
Console.WriteLine("Type: " + dataModelConnection.Type);
Console.WriteLine("########################");
}
else
{
Console.WriteLine("Connection Name: " + workbook.DataConnections[i].Name);
Console.WriteLine("Description info: " + workbook.DataConnections[i].ConnectionDescription);
Console.WriteLine("Connection ID: " + workbook.DataConnections[i].ConnectionId);
Console.WriteLine("Credentials: " + workbook.DataConnections[i].CredentialsMethodType);
Console.WriteLine("Type: " + workbook.DataConnections[i].Type);
Console.WriteLine("########################");
}
}
Great, thank you! I’ll look forward to the next release.
@mraduenzel,
You are welcome. We will keep you posted with updates on it.
The issues you have found earlier (filed as CELLSNET-52065) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi