Interface IConnection
Represent the connection source
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public interface IConnection : IParentApplication
Properties
ConncetionId
Gets the connection id.
Declaration
uint ConncetionId { get; }
Property Value
Type |
---|
System.UInt32 |
Remarks
This API is deprecated. Please use the the ConnectionId instead.
ConnectionId
Gets the connection id.
Declaration
uint ConnectionId { get; }
Property Value
Type |
---|
System.UInt32 |
Examples
The following code illustrates how to access the connection id.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook= application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
//Set connection description
Connection.Description = "Sample Connection";
// Import data to the sheet from the database
table.Refresh();
//Get Connection ID
Console.WriteLine(Connection.ConnectionId);
//Remove Connection
Connection.Delete();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//1
DataBaseType
Gets the connection database type.
Declaration
ExcelConnectionsType DataBaseType { get; }
Property Value
Type |
---|
ExcelConnectionsType |
Examples
The following code illustrates how to get the connection database type.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook= application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
//Set connection description
Connection.Description = "Sample Connection";
// Import data to the sheet from the database
table.Refresh();
//Get DataBase Type
Console.WriteLine(Connection.DataBaseType.ToString());
//Remove Connection
Connection.Delete();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//DataBaseType OLEDB
Description
Gets or sets the connection description.
Declaration
string Description { get; set; }
Property Value
Type |
---|
System.String |
Examples
The following code illustrates how description for connection can be set and accessed.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook= application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
// Import data to the sheet from the database
table.Refresh();
//Get Description
Console.WriteLine(Connection.Description);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//Sample connection with MsAccess
Name
Gets or sets the connection name.
Declaration
string Name { get; set; }
Property Value
Type |
---|
System.String |
Examples
The following code illustrates how the name for the connection can be set and accessed.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook= application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
// Import data to the sheet from the database
table.Refresh();
//Get Name
Console.WriteLine(Connection.Name);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//Connection1
ODBCConnection
Gets the odbc connection.
Declaration
ODBCConnection ODBCConnection { get; }
Property Value
Type |
---|
ODBCConnection |
Remarks
If connection type is ODBC then to customize connection properties ODBCConnection property can be used.
Examples
If the connection type is ODBC we can customize the properties of connection by accessing ODBCConnection property. Here for example, we get the command text and print it to console.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "ODBC;DSN=MS Access;DBQ=C:\\Company\\DB\\Testing.mdb;DefaultDir=C:\\Company\\DB;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
//Set connection description
Connection.Description = "Sample Connection";
// Import data to the sheet from the database
table.Refresh();
//Get Command Text from ODBC connection
Console.WriteLine(Connection.ODBCConnection.CommandText);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//Select * from tbltest
OLEDBConnection
Gets the oledb connection.
Declaration
OLEDBConnection OLEDBConnection { get; }
Property Value
Type |
---|
OLEDBConnection |
Remarks
If connection type is ODBC then to customize connection properties ODBCConnection property can be used.
Examples
If the connection type is OLEDB we can customize the properties of connection by accessing OLEDBConnection property. Here for example, we get the command text and print it to console.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook= application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
//Set connection description
Connection.Description = "Sample Connection";
// Import data to the sheet from the database
table.Refresh();
//Get Command Text from OLEDB connection
Console.WriteLine(Connection.OLEDBConnection.CommandText);
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//Select * from tbltest
Range
Gets or sets the connection range.read only
Declaration
IRange Range { get; }
Property Value
Type |
---|
IRange |
Examples
The following code illustrates how the range of the connection can be accessed.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook= application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
//Set connection description
Connection.Description = "Sample Connection";
// Import data to the sheet from the database
table.Refresh();
//Get Range
Console.WriteLine(Connection.Range.AddressLocal.ToString());
//Remove Connection
Connection.Delete();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
Console.ReadKey();
}
//Output will be
//A1:A2
Methods
add_OnConnectionPassword(ConnectionPasswordEventHandler)
Declaration
void add_OnConnectionPassword(ConnectionPasswordEventHandler value)
Parameters
Type | Name | Description |
---|---|---|
ConnectionPasswordEventHandler | value |
Delete()
Delete the connection from connection collection.
Declaration
void Delete()
Examples
The following code illustrates how to delete a connnection.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook= application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Set Connection string for DataSource
string ConnectionString = "OLEDB;Provider=Microsoft.JET.OLEDB.4.0;Password=\"\";User ID=Admin;Data Source=AccessDB.mdb";
//Add a connection to the workbook
IConnection Connection = workbook.Connections.Add("Connection1", "Sample connection with MsAccess", ConnectionString, "", ExcelCommandType.Sql);
//Add a QueryTable to sheet object
IListObject table = worksheet.ListObjects.AddEx(ExcelListObjectSourceType.SrcQuery, Connection, worksheet.Range["A1"]);
//Set Command Text for the Connection
table.QueryTable.CommandText = "Select * from tbltest";
//Set connection description
Connection.Description = "Sample Connection";
// Import data to the sheet from the database
table.Refresh();
//Remove Connection
Connection.Delete();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Tables.xlsx");
workbook.Close();
}
remove_OnConnectionPassword(ConnectionPasswordEventHandler)
Declaration
void remove_OnConnectionPassword(ConnectionPasswordEventHandler value)
Parameters
Type | Name | Description |
---|---|---|
ConnectionPasswordEventHandler | value |
Events
OnConnectionPassword
Represent the connection password event
Declaration
event ConnectionPasswordEventHandler OnConnectionPassword
Event Type
Type |
---|
ConnectionPasswordEventHandler |