How can one use SQL authentication for SMO-based database connection syntax?

Aug 5, 2009 at 12:17 PM

All --

Please help.

How can one use SQL authentication for SMO-based database connection syntax?

<#@ template language=”C#v3.5” #>
<#@
output extension=”SQL
” #>
<#@
assembly name=”Microsoft.SqlServer.ConnectionInfo
” #>
<#@
assembly name=”Microsoft.SqlServer.Smo
” #>
<#@
import namespace=”Microsoft.SqlServer.Management.Smo
” #>
<#
    Server server =
new
Server(@"YourServerName\SqlExpress");
    Database database =
new Database(server, “Northwind”
);
    Table table =
new Table(database, “Products”
);
    table.Refresh();
#>

(This is somewhat tertiary to the top of T4 in general; but, since the T4 Toolbox samples often use SMO-based database connnections

 

(Yes, I did try to check the SMO syntax at MSDN but, unfortunately, I could not get what I wanted-- I will accept the blame for that if it is my error-- but, I am not convinced that using a standard connection string CAN be done and I am thinking that maybe it cannot be done and that is why I cannot find the syntax for it. Chicken. Egg. Whatever. I just need help.)

(What I really want to do is pass a connection string to SMO-- that would be nice and, I daresay, typical.)

Please advise.

Thank you.

-- Mark Kamoski

Coordinator
Aug 5, 2009 at 12:45 PM

Here is an example: http://msdn.microsoft.com/en-us/library/ms162132.aspx

 

Aug 5, 2009 at 1:09 PM
Edited Aug 5, 2009 at 1:09 PM
olegsych wrote:

Here is an example: http://msdn.microsoft.com/en-us/library/ms162132.aspx

 

My hero.

Oleg, I don't know who you are-- but-- I have to say "you rock".

Great work on all this T4 stuff. Very helpful. Very timely. Etc.

Thanks so much for doing the heavy-lifting and sharing the fruits of your labor.

-- Mark Kamoski

Coordinator
Aug 5, 2009 at 3:55 PM

Thanks, Mark. I appreciate it. Let me know if/when you get to the point of having a reasonably complete code generator based on SMO - it would be nice to package it in the T4 Toolbox.

Aug 5, 2009 at 5:25 PM
Edited Aug 5, 2009 at 5:30 PM
olegsych wrote:

Thanks, Mark. I appreciate it. Let me know if/when you get to the point of having a reasonably complete code generator based on SMO - it would be nice to package it in the T4 Toolbox.

Oleg --

I have a sample now, zipped and ready to send/upload.

In fact, I have it in production, after working with this stuff for less than day. Nice. Saving tons of time already.

It is not pretty, it may or may not be what you are looking for in a sample, it is not elegant, it takes a little setup, but the code it produces compiles just fine and it replaces hand-written code and (as is the case with code generation) the code can get better and better as I go.

(So nice.)

Where shall I send/upload it?

(I will use your "contact me" form at http://www.olegsych.com/about/ and send my email address to you directly, just in case.)

Thank you.

-- Mark Kamoski

Coordinator
Aug 5, 2009 at 9:30 PM

Thanks Mark. You can upload it as a patch here on CodePlex. I have an SMO-based sample that I used in the T4 tutorial series, however, I never took it to the next level and didn't packaged it in the T4 Toolbox. I try to avoid using database as a model for code generation in my work, which is why I never took this sample to production. However, I know that other people do and I hear requests to have something like this often. I am hoping that someone who actually uses database for code generation could step up and lead the implementation of this code generator in the T4 Toolbox.

Aug 6, 2009 at 3:12 PM
olegsych wrote:

...You can upload it as a patch here on CodePlex...

Oleg --

That is done.

I uploaded "UtilityClassGenerationSampleV200908061058.zip" and it is an OK sample.

It is by no means a best-practice-sample.

However, it is in-production here at my end and working fine.

Being a fan of code generation, I may make more samples of this kind and will plan to upload them when appropriate.

 

<sidebar>

Mostly off-topic, I want to add this for those reading this that are not convinced about code generation.

I would like to take this opportunity to propogate the following bit of code-generation-evangelization, just because I think it is a good idea to do so...

"

What are the advantages of code generation?

The advantages of code generation techniques can be lumped into four categories:

Quality: The quality of the code built by a generator is directly related to the quality of the code or templates used to build the target code. As the quality of that code or templates is increased and the output code is regenerated then the quality of the whole code base is increased.

Consistency: The target code created by code generators is extremely consistent. The variable names, method names and class names are built in the same way across all of the output code. This makes the target code easy to use and easy to layer more functionality on top of.

Productivity: It’s easy to recognize the initial benefit of code generation the first time the generator is run. You start with an input that contains the design of the output code, and almost instantaneously you have output code that implements your design. However, the real productivity gain starts when you run the generator subsequent times to create new code based on design changes. How often do you see a project where the requirements haven’t changed? That’s why the ability to generate new code based on changing requirements is so important.

Abstraction: Some generators build code based on abstract models of the target code. For example, you may build an SQL schema and database access layer code from an XML definition of the tables, fields, relationships and queries in a database. The value of this abstraction is that the generator can be retargeted to build code for another platform. This means that you have portability between platforms for your business logic and rules that does not rely on building the code on portability platforms.

"

Reference: 

http://www.codegeneration.net/tiki-index.php?page=FrequentlyAskedQuestions

</sidebar>

 

HTH.

Thank you.

-- Mark Kamoski

Coordinator
Aug 6, 2009 at 8:20 PM

Mark,

Thanks for sharing this. Could you describe in a few sentences what this code generator allows you to do?

Thanks,

Oleg

Aug 6, 2009 at 9:00 PM
olegsych wrote:

...Could you describe in a few sentences what this code generator allows you to do?...

Oleg --

Regarding that, I will try in several ways-- but the "design process" here in my mind is VERY much "ad hoc" at best, although I do try, and I must say "it works for me" and I make no apology for that but surely "it may not be for everyone", so buyer beware.

:-)

Description (1)...

The code allows me to generate utility classes for all semi-static "code-tables", (AKA look-up tables, AKA lists of values), in my database and create IntelliSense access to the members by value not by ID, which allows enforcement of typical filtering tasks, reporting tasks, etc. It is for, "system-level" values. A typical use is the point it at the asp_Roles table and then calls to User.IsInRole(string roleName) can be made quite easily. So the "hide this button if the user is an admin" business rule is easy to implement-- Otherwise that roleName "admin" gets hard-coded somewhere. Objections for deletions of values, renaming of values exist. Those can be handled by system-rules, admin workflow, and careful coding-- but, in the end, one still must get around where to be that hardcode "admin" and whether to manage it manually or semi-automatically (such as with this type of code generation)-- where the User.IsInRole() example is, I think a great one because I have not yet seen a good way other than this to quickly write "hide this button if not in the admin role" rule. Etc. However, this templates set is also, and probably mainly, my first proof-of-concept to use T4 and I plan to use it as a prototype for future code generation tasks, such as to replace my "create base Manager classes for the Disconnected Linq-To-Sql CodePlex code", which I do now with a custom generator and may just rewrite using T4. Etc.

Description (2)...

The "stock" description that I added when I did the upload is as follows...

This is a sample, "UtilityClassGenerationSampleV200908061058.zip". It shows one way to generate utility classes for "code tables" in your database. This sample generates a utility class that allows for consumer-friendly code-value lookup. The concepts used in this sample can be used in any number of ways. This is is a GEFN-sample, This is not a best-practices-sample. Thanks to Oleg Sych for all this.

Description (3)...

Inside, the code is fairly well documented, and here I excerpt...

//The purpose of this template set is to generate helpers that expose code-table values for programmatic access, for filtering etc.

//Note that "Psv" stands for "pipe-separated-values".

//Note that a "code-table" is a AKA "table of values", "table of lookup values", "a lookup table", "a type table", etc.

//Note that a "code-table" here must have a single-column case-insensitive-unique text value.

//Example: To enforce the business rule "all non-US citizens must provide a country-of-birth value", one generally
//either hard-code the value somewhere or do other tricks, each of which is manually maintained. But, we still need the rule.

//Objection: If the code table value gets deleted or changed then you have to recompile.
//Maybe. But the hand-hardcode would break too. This is easier to regenerate. And this risk can be mitigated by admin flow and code.

    //This is part of a T4 template set.
    //_T4CodeUtility02File.tt
    //_T4CodeUtility02Generator.tt
    //_T4CodeUtility02Template.tt

    //The File.tt uses the Generator.tt and the Generator.tt uses the Template.tt to run.

    //The number is the "version" of this template set.

    //This generates ".cs" files, not ".vb" files.
   
    //TODO. Set the values below to match your desired project settings.
   
    //This is the namespace that will be used for the the output classes.
    myGenerator.CodeUtilityNamespace = "Team.Cle.BusinessLayer.BusinessUtilities";
   
    //This is the prefix that will be used for the the output classnames and filenames.
    myGenerator.CodeUtilityPrefix = "";
   
    //This is the suffix that will be used for the the output classnames and filenames.
    myGenerator.CodeUtilitySuffix = "Utility";
   
    //This is the database-name to use for all operations.
    myGenerator.DatabaseName = "Clea";
   
    //This is the database-server-name to use for all operations.
    myGenerator.ServerName = "cen-db";
   
    //This is the list of code-table prefixes, where if a table name starts with any prefix in the list then that table is a "code table".
    myGenerator.CodeTablePrefixPsv = "";
   
    //This is the list of code-table suffixes, where if a table name starts with any prefix in the list then that table is a "code table".
    myGenerator.CodeTableSuffixPsv = "Code";
   
    //This is the list of column names, where one must exist in each code-table.
    myGenerator.CodeValueColumnNamePsv = "Name|NameText";

That's the gist of it, in brain-dump form. I wish it were more polished but it isn't.

HTH.

Thank you.

-- Mark Kamoski