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	Description
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.ConncetionId);

            //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 Type

Declaration

ExcelConnectionsType DataBaseType { get; }

Property Value

Type	Description
ExcelConnectionsType

Examples

The following code illustrates how to get the 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

Represent the connection description.

Declaration

string Description { get; set; }

Property Value

Type	Description
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

Represent the connection name.

Declaration

string Name { get; set; }

Property Value

Type	Description
System.String

Examples

The following code illustrates how name can be set and accessed for the connection.

        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	Description
ODBCConnection

Remarks

If connection type is OLEDB then to customize connection properties OLEDBConnection 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	Description
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

Represent the connection range.read only

Declaration

IRange Range { get; }

Property Value

Type	Description
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

Delete()

Delete the connection

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();
        }

Events

OnConnectionPassword

Represent the connection password event

Declaration

event ConnectionPasswordEventHandler OnConnectionPassword

Event Type

Type	Description
ConnectionPasswordEventHandler