Interface IConnection

Represent the connection source

Inherited Members

IParentApplication.Application

IParentApplication.Parent

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.Base.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