Interface IConnection

Represent the connection source

Inherited Members
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.UWP.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

Extension Methods