EntityFramework Core -> scaffold POCO classes from DB into .Net Standard proj

• Add Microsoft.EntityFrameworkCore.SqlServer from Nuget
• From the Package Manager Console install entity frameworkcore tools

Install-Package Microsoft.EntityFrameworkCore.Tools

• Have a project that targets Core or Framework as the startup project in the solution (or can use the –StartupProject ‘MyStartUpProject’ flag during the scaffold)
• Tell EF to build the context and POCO classes

Scaffold-DbContext "Server=xxxxx;Database=xxxxxx;Trusted_Connection=True;” Microsoft.EntityFrameworkCore.SqlServer –OutputDir Models –Project Tdhb.MyStandardLib

• Puts the ‘Models’ folder into the Tdhb.MyStandardLib project

So you change the database didn’t you. Now the POCOs are out of sync with the DB.

• Run the scaffold command again but put –Force on the end to overwrite existing files

Scaffold-DbContext "Server=xxxxx;Database=xxxxxx;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Project Tdhb.MyStandardLib -Force

If not having a startup project that targets Core of Framework then get the error (either add project (core or framework) and set as startup or use the StartupProject flog:

Startup project 'Tdhb.MyStandardLib' targets framework '.NETStandard'. There is no runtime associated with this framework, and projects targeting it cannot be executed directly. To use the Entity Framework Core Package Manager Console Tools with this project, add an executable project targeting .NET Framework or .NET Core that references this project, and set it as the startup project; or, update this project to cross-target .NET Framework or .NET Core. For more information on using the EF Core Tools with .NET Standard projects, see https://go.microsoft.com/fwlink/?linkid=2034705

.net programmatically download a file

        /// <summary>

        ///  retrieves the created pdf file

        /// </summary>

        private void DownloadFile(string documentSourceUri, string outputFilePath)

        {

            WebClient client = new WebClient

            {

                Credentials = new NetworkCredential(_ftkGetUser, _ftkGetPassword)

            };

            client.DownloadFile(documentSourceUri, outputFilePath);

        }

SSIS setting up ADO.net source & connection using params/variables

Problem:

The .net 'OracleClient Data Provider' does not appear to save the password locally so fails when run in visual studio - but runs when deployed and the password is set via the configuration in SSMS. The .net 'ODP.NET, Managed Driver' works locally, but does not work when deployed on servers (requires components to be installed). This means that two connections are setup in the package; the Managed Driver provider connection is used locally, then the sources are switched to use the OracleClient provider connection before being deployed. And that is a pain.

Solution:

If the ConnectionString value of the OracleClient provider is setup in the expressions then it will work because the password is explicitly set. However, the connectionstring needs to vary depending on where (what environment) the package is deployed, so it needs to be in something that can be configured after deployment. Putting the connection string in a param means it is visible to people from SSMS; making the param sensitive stops it being visible but makes it difficult to know how else this package is configured (e.g. what server it is connecting too).

Get around this by having the password in a sensitive param and the rest of the connectionstring in a normal param and combine them at run time. Since params are readonly the result of the combination has to put in a variable, and the connection's connectionstring linked to that variable.

At design time if that variable does not hold a valid connectionstring then Visual Studio will freak out and mark the connection as offline, so need to have a valid connectionstring in the variable as well.

tl;dr:

• Have parts (most of it) of the connection string in a non-sensitive param
• Have the password in its own sensitive param
• Build the connection string when the package starts
• Params are readonly, so store the constructed connection string into a variable
• Link the ConnectionString value of the connection to the variable (using expressions)
• Keep the design time value of the variable populated to something valid so that at design time Visual Studio doesn't freak out and mark the connection as being in offline mode

Setup variable with a full connection string that can be used in Visual Studio

Link the connection's connectionstring to the variable using expressions - click on the connection and locate the expressions value in the properties

Create params to hold the password and rest of the connectionstring

When package starts have a script task to build the connectionString

On the script task give access to the params and the varaibles

In the script task build the connectionString from the params and 

Once package is deployed configure the param values as required

SSIS manipulating variables and params in a script task

For the script task to be able to access varaibles and parameters the task needs to be given access to them. Do this by double clicking it and entering them as either readonly or readwrite values.

Getting and setting variables

string query = Convert.ToString(Dts.Variables["User::PrimaryNurseQuery"].Value);

Dts.Variables["User::PrimaryNurseQuery"].Value = query;

Getting parameters

string idsToExclude = Convert.ToString(Dts.Variables["$Package::PrimaryNurseIdsToExclude"].Value);

NOTE: needs the $


The whole thing:

try

{

    bool fireAgain = true;

    string idsToExclude = Convert.ToString(Dts.Variables["$Package::PrimaryNurseIdsToExclude"].Value);

       if (string.IsNullOrEmpty(idsToExclude))

       {

              idsToExclude = "'NoneFound'";

       }

    Dts.Events.FireInformation(10, "Build Primary Nurse SQL Command","Got idsToExclude as " + idsToExclude, "", 0, ref fireAgain);

       string query = Convert.ToString(Dts.Variables["User::PrimaryNurseQuery"].Value);

    query = query.Replace("'ReplaceMe'", idsToExclude);

       Dts.Variables["User::PrimaryNurseQuery"].Value = query;

       Dts.Events.FireInformation(10, "Build Primary Nurse SQL Command", "Query to use is " + query, "", 0,ref fireAgain);

       Dts.TaskResult = (int) ScriptResults.Success;

}

catch (Exception ex)

{

       MessageBox.Show(ex.ToString());

    Dts.Events.FireError(10, "Build Primary Nurse SQL Command", "Error: " + ex.ToString(), "", 0);

    Dts.TaskResult = (int) ScriptResults.Failure;

}

SSIS ADO.net has no option to run a query from a variable

why would you do this to me ado.net?

There is no option to run 'SQL command from variable' like the OLD DB source. Can use expressions to get around this.

No option to use a variable holding the command:

Go to the Data Flow and click on the back ground (i.e. make sure you don't have a task selected)

marvel at my naming scheme.

Go to the properties and locate the Expressions

Click the + and then the ... to open the expression builder. On the right hand side you can find the SqlCommand property for the souce object then use the expression builder to either use a variable for the whole query, or build up a query manually

Manually constructing the query looks something like this:

"SELECT field1, field2 FROM table1 WHERE table1.nhi in (" + @[User::AdmissionNhiLeadingSpaceList] + ")"

SSIS using variables and params in queries

Params can be set at design time and updated once the package is deployed - can put params or parts of queries into these
Variables are internal to the package & can be set at design time but not updated once deployed; sql can be exectued and the results put into a variable - can put variables or parts of variables into querries


Putting a query into a param so it can be updated after package is deployed

1. Add the param on the Parameters tab
2. When querying the data tell it to use the command in the variable. The '$Package' syntax with the $ appears to be for params e.g. $Package::PrimaryNurseQuery. Variables are like User::MyVariableName.
   
    
3. Once the package is deployed access the parameters in SSIS by right clicking the package
   
   
4. Update the parameter to whatever is needed

Selecting data into a variable, then using that variable as part of a subsequent query

The variable can be used by multiple containers

1. Create a variable in SSIS -> Variables that will store the result of a query. Can put a dummy value in it to start with
   
2. Create another variable that holds the template for your second query, called something like the poorly named 'SQLCommandMHSmart'. It holds something like SELECT field1, field2 FROM table1 WHERE table1.admissionNumber in (encounterList) AND table1.nhi in (nhiList). This is bad SQL, the 'encounterList' and 'nhiList' will be replaced with values later e.g. encounterList would be replaced with the string '1234567','222222','323456'.
3. Create an Execute SQL task 
   
4. Put the query into the execute sql task, this one will return data in format 'value1','value2','value3'... so that it can easily be used in an 'in' clause. 
5. This is what this query looks like
   
6. Set the Execute SQL task to store data into the variable
7. Create a script task that is going to use the above variable and build up a new query and will store the constructed query in another variable (in this case SQLCommandMHSmart). This is kind of dumb, it's really just doing string manipulation and script tasks are annoying to use. 
8. First, set the script task so it has access to the right variables
   
9. Click the Edit Script button and wait for eternity for the new instance of visual studio to open. It takes a while. Feel yourself aging. Look, here comes retirement. The script task contents will look something like this:
   
10. it's very exciting string manipulation, putting the contents of one variable into the template held by a second variable, then saving that back into the second variable.
11. The query is now ready to use. When getting data from the next source set the query as coming from the variable