Reference a Biml connectionString in Bimlscript (C# Control Nuggets)

Question

I have a connection in Biml:

<Connections>
        <OleDbConnection Name="MyConn" ConnectionString="Data Source=localhost;Initial Catalog=MyDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;" CreateInProject="true"/>
</Connections>

I have also a c# control nuggets code to get a data table from the database (MyDB):

<#
    string ConnectionString = "Data Source=localhost;Initial Catalog=MyDB;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;";
    DataTable MyTable;
    MyTable = ExternalDataAccess.GetDataTable(ConnectionString,"SELECT * from dbo.MyTable");
#>

Is it possible not to repeat the connection string and reference "MyConn" directly in Bimlscript? I mean something like:

<#
DataTable = MyTable;    
MyTable = ExternalDataAccess.GetDataTable(MyConn,"SELECT * from dbo.MyTable");
#>

Thanks, Ziad

You just wrote how to do it. You can reuse CONNECTIONSTRING anywhere in your script. — SqlKindaGuy, Dec 11 '17 at 09:44
It does not work like that, because "MyConn" is the OledbConnection name of an Biml attribute and inside the connection tage and the ConnectionString Parameter in GetDataTable() is C# Parameter, so the compailer won't be able to recognize the Biml attribute as an .NET string varaible. The idea that I do want to get rid of the (string ConnectionString) in the second code block in my example and resuse MyConn that is defined in the Biml OldebConnectoin tag as a parameter for GetDataTable() function — Ziad Salem, Dec 11 '17 at 09:59
Sorry i didnt understand your question then. But now i do, and you cant do what you want. You need to define a variable. You cant use BIML connectionstrings inside your C# variables. However you can reuse this connection everywhere. Even inside BIML tags. But you cannot do it the other way around. Your BIML connection is only PROJECT or PACKAGE enabled connections. They cannot be used to fetch C# data. — SqlKindaGuy, Dec 11 '17 at 10:00
even If I want to access the Connection Managers in C# code? because the connection is being created as project connection? — Ziad Salem, Dec 11 '17 at 10:03
You can access your Connection MAnager as long its in BIML tags. You cant use it within C# nuggets. — SqlKindaGuy, Dec 11 '17 at 10:04

billinkc · Accepted Answer · 2017-12-21T01:28:04.733

2

Mobile at the moment but code approximately

ExternalDataAccess.GetDataTable((AstDbConnectionNode)RootNode.Connections["MyConn"],"SELECT * ...);

This assumes that the connection manage had been defined as I discussed on my other answer

You can also access the connection string from a connection object though the RenderedConnectionString property

edited Dec 21 '17 at 01:28

answered Dec 11 '17 at 13:15

billinkc

59,250
9
102
159

Thanks @billinkc. It woked just as I wanted to. There is only one small thing that we the connection should be casted to AstDbConnectionNode. so the final code looks like: ExternalDataAccess.GetDataTable((AstDbConnectionNode)RootNode.Connections["MyConn"],"SELECT * ...); – Ziad Salem Dec 18 '17 at 09:35

Reference a Biml connectionString in Bimlscript (C# Control Nuggets)

1 Answers1