The current time is

Tuesday, November 17, 2009

CodeSmith & NetTiers Custom Stored Procedures

Running the CodeSmith generation tool using the NetTiers templates is a piece of cake if we're going to talk about generating code for database tables. However, when it comes to custom objects being generated by CodeSmith, it gets a bit rocky but it is still doable J

In order to generate code that would encapsulate a stored procedure that you created, you need to follow the steps below:

  1. Your procedure name must be discoverable, meaning that you should set certain settings in CodeSmith so that it could find it during generation. Follow the couple of points below to set the configurations in CodeSmith
    1. IncludeCustoms=true
    2. ProcedurePrefix=<EnterYourProcedurePrefix>
    3. CustomProcedureStartsWith=<EnterYourPattern>

      This pattern uses two placeholders: {0} for the TableName and {1} for the ProcedurePrefix (we've just talked about). So if we need to match the procedure name "myCusProc_myTableName_MethodNameToGenerate", we'll have to set {1} to be "myCusProc_" and appended to it would be the <TableName>. Therefore, the final template we're going to use for this property, CustomProcedureStartsWith, would look like that: {1}{0}_

    4. CustomNonMatchingReturnType=IDataReader|DataSet. You'll have to choose one of these datatypes to return your data in. Usually, I prefer IDataReader.
    5. If you want to generate code for your custom procedures only (i.e: You don't need to re-generate for all your database), then you should set the property ExecuteSQL=false, and the property SourceTables=<TheTablesThatWillContainTheNewlyGeneratedProceduresFunctions>.
    6. Finally, you can verify that CodeSmith generated code for your procedures by checking the Data layer in the base directory for the TableNameProviderBase.generatedCore.cs.


 

Enjoy generating your code J

Wednesday, November 11, 2009

SQL Server 2005 "View Dependencies" "bug"

Couple of days back, I was trying to get the table dependencies in SQL Server 2005, but I faced a weird "bug", I would say, that prevented me from seeing "All" the dependent tables on that specific table. I thought that "View Dependencies" in the SQL Server would find all the tables referencing the one I have selected, check its foreign keys and so, it can list the dependent tables but for some reason, some tables were not retrieved !!! I checked the differences between both tables referencing the parent table and found no differences at all !!!

After searching for this weird issue online, I found this totally "other" way for finding what I wanted which was listed in this website. I tried it and it worked :))

That was the SQL snippet that worked for me :))

-- Value 131527 shows objects that are dependent on the specified object
EXEC sp_MSdependencies N'HumanResources.[Employee]', null, 1315327

-- Value 1053183 shows objects that the specified object is dependent on
EXEC sp_MSdependencies N'HumanResources.[Employee]', null, 1053183

Of course, these numbers, passed as parameters to the procedure, mean something BIGGGG, but who cares for the moment... it's working NOW :)))

Enjoy SQLing :)