PetaPoco nuances and triggers

The PetaPoco shortcut ppDB.Insert(obj) or ppDB.Update(obj) cannot be used if the database table has trigger because the following error will be generated:

“The target table '[db table name]' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.”

  

This is because the sql generated by PetaPoco is the following:

INSERT INTO [db table] ([col1], [col2], ... [coln]) OUTPUT INSERTED.[identityColName] VALUES (@0,@1,...@n)

PetaPoco nuances with transactions

PetaPoco has a couple of really easy ways of inserting/updating records. 

The following are the object initializations used below:

private PetaPoco.Database ppObj = new PetaPoco.Database("[connection string name]");
ClassName.ObjName objInstance = new ClassName.ObjName();

One way is to create an object based upon the class created from the database.tt file and use the insert()/update() method:

objInstance.Insert();

As I found out, this approach does not allow you to rollback a transaction. If you run the insert()/update() on the PetaPoco object instead then you can rollback transactions:

ppObj.Insert(objInstance);

Trapping emails generated in C# when there is no SMTP server

If you need to generate emails in your app, I’ve found a way to test them in the dev environment. Visit http://smtp4dev.codeplex.com/downloads/get/269147 and download the tool. The executable in the ZIP file will spawn a mini-smtp server. Click the “options” button and then the “server” tab. Set the listen interface to 127.0.0.1, I think you can leave the port at 25.

Add the following to your web.config file in the <configuration> section. Port will be whatever number you specified for smtp4dev:

<system.net>
   <mailSettings>
     <smtp>
       <network host="localhost" port="25" />
     </smtp>
   </mailSettings>
</system.net>

Write your code as normal to generate & send the email. Smpt4dev will notify you that you’ve received an email and you can click the “view” button to see the email message in Outlook.

The app sits in the tool tray under “hidden icons”, so no need to start the executable again. Closing the smpt4dev UI doesn’t end the process and running the exe again will produce a “server failed” message as shown below. View hidden icons in the tool tray close any extra occurrences of smtp4dev.

Linq-to-SQL and Identity Columns

I recently had a situation where I had to insert records into a db table. The table had an identity column but no primary key (go figure). This presented hurdle 1 of how to insert the records since linq-to-sql's standard approach of creating a new table item, populating the fields and then inserting the new record only works if your table has a primary key.

Hurdle 1 was overcome by creating a dynamic SQL statement and using ExecuteQuery. This was fine until I had to retrieve the ID of the record that was just inserted (Hurdle #2). appending select scope_identity() after my insert statement was not playing nicely. If I specified the ExecuteQuery to be of type I would receive "Specified cast not valid". If I specified the ExecuteQuery to be of type the identity column was 0.

I overcame hurdle #2 with the following:
1) specified the ExecuteQuery to be of type
2) generated the following SQL Statement: "; select * from where = (select scope_identity())"

This returned a full record from the table with the correct value in the identityColumn property

Nintex SharePointTaskId, InfoPath, and conditional formatting

I was recently working on an InfoPath form that was used to drive a Nintex workflow. I had gotten to the point where I needed to make sure not just any ole user could respond to a task as terrible things happened (the rules in the InfoPath form were applied, but the workflow would not move forward and chaos ensued).

My search began with the SharePointTaskId property in the GetRunningWorkflowTasksForCurrentUser web service method that Nintex provides. I noticed some oddities in the process:

1) in code, the value was always empty. just to make sure I wasn't hallucinating I copied the outerxml of my xpath query. every node in the subtree had an empty value. YET, if I applied conditional formatting by saying "is not blank", it worked.

2) if I tried to perform conditional formatting by saying where SharePointTaskId is blank, it did not work. This had me scratching my head since the "is not blank" worked perfectly.

My solution for the second issue was to compare the WorkflowName property against a pattern (e.g. WorkflowName does not match pattern \p{L}+ -- in other words, WorkflowName has to have at least 1 letter)

Moving Nintex Workflow to a new site

As stated in an earlier blog my team was tasked with creating a SharePoint staging environment that mirrored production. Part of the SharePoint site included Nintex workflows. This did not go as smoothly as I had thought, either. Even though central admin indicated that the connection to the database had been established, whenever I tried to view my existing workflows in the SharePoint site I would see the following message: Failed to open a connection to the Nintex Workflow configuration database.
System.Data.SqlClient.SqlException: Cannot open database "" requested by the login. The login failed. Login failed for user ''.

I stumbled upon this thread on the Nintex site which pointed me to the resolution. The appPool user account needed: 1) permissions to the Nintex database; 2) to be added to the role WSS_Content_Application_Pools

reason #312 why I hate SharePoint

I know, everyone has many reasons to hate SharePoint, and they are all valid. So, this is really just another in a long stream, but I wanted to share it any way.

We were tasked to create a staging environment which mirrored production. Sounds simple, right? Wrong. The first naive step was to create a site collection backup of production. Getting it onto the staging environment proved painful enough due to its size, but after it was finally uploaded I thought we were home free. The restore failed... repeatedly... miserably. The error message that displayed was: "The site collection could not be restored. If this problem persists, please make sure the content databases are available and have sufficient free space." I tried increasing the database size. I freed up room on the hard disk. I created a blank SharePoint site and tried to restore into that since every time I ran the restore into the existing SharePoint site the database just kept getting bigger, bigger than the production database, even though I was specifying "overwrite".

After happening upon a post where a restore was generating the same error message I noticed one responder said to can the whole site collection restore notion and just do a backup/restore of the content database.

I did it, it worked. W00T! It's not complete in that we still have to manually get the customizations back into the site, but it at least got us about 85% - 90% there.