Slick trick for doing a Choose() in C#

Back in my VB days, I frequently found the Choose() function handy.
I frequently used it for converting integer values to boolean for checkboxes and such.

Dim i as integer
i=1
Checkbox1.checked=Choose(i,false,true)

Nice and clean.

However, in C# I have been relegated to doing this:

int i=1;
if(i==1)
{
Checkbox1.checked=true;
}
else
{
Checkbox1.checked=false;
}

Much messier and longer.  If I have a dozen checkbox fields, this becomes excessively long.

So today I finally had enough and searched for a better way until I found this trick.  I just declare an array of boolean values on the fly and access the one I want.

int i=1;
Checkbox1.checked=new[] {false,true }[i];

There we go!  Back to a 1 liner that is easy to read.

Shrinking a database

Most companies running Dynamics GP are using the Full backup model.  This means that they are backing up the log files more frequently than the database.

When on this model, it should not be necessary to shrink the data or log file very often.

However, there are instances where you would want to do this, such as immediately after archiving (removing) a large amount of data from the database.

Here are the commands to get the files shrunk back down. You should do this at a time when no users or other processes are accessing the database.

--1. Create a full backup of the database.  Since this will involve changing the backup model, we will be breaking any log chains that exist.

--2. Set the database to single user mode.  This will prevent anything from connecting to the database while we are working on it

ALTER DATABASE [YourDbName] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

--3. Set the recovery model to simple.  This will allow us to quickly shrink the files.

ALTER DATABASE [YourDbName] SET RECOVERY SIMPLE;

GO

--4. Shrink the data and log files.  The number here represents 10% free space after shrinking.  This can take a while to run.

DBCC SHRINKDATABASE ([YourDbName], 10);

GO

--5. Shrinking trashes the indexes, so now we reorganize them.  This can take a very long time to run.

USE [YourDbName]

GO

DECLARE @TableName VARCHAR(255),

@sql NVARCHAR(500)

DECLARE TableCursor CURSOR FOR

SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

FROM sys.tables

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REORGANIZE;'

EXEC (@sql)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

GO

--6. Set recovery model back to full

ALTER DATABASE [YourDbName] SET RECOVERY FULL;

GO

--7. Set Database back to multiuser mode

ALTER DATABASE [YourDbName] SET MULTI_USER WITH ROLLBACK IMMEDIATE;

GO

--8. Remember to take a new full backup after you are done so that the full backup model can start a new log chain.

SmartConnect can't see my custom proc

I have repeatedly been frustrated when trying to install custom nodes in eOne SmartConnect.
There is little documentation on the web and this has been a pain for several versions, so I have decided to maintain this post to list ways in which we have gotten it to work.

First, lets detail the steps installing a node in smartconnnect (assuming whe stored proc is already installed.  Let's assume the stored proc name is MyEconnectProc.)
1. Launch Smartconnect as an administrator
2. Click the Maintenance Tab and then choose Node Maintenance.

Note:  If you do not see a list of nodes in the window you need to stop here and follow the SmartConect installation documentation to install the default GP nodes before proceeding.  Make sure you restart SQL server after this is done, and then restart SmartConnect.

3. Navigate to the point in the tree where you want your node to appear.  I want this one to appear under Payables > Vendors, so I will right click on that node and choose Add New Node.

4. In the window that pops up, I need to enter the name of my proc in the Technical Name.  It is important that it be capitalized the same as the proc in the database.  Click tab.  The Technical Name should gray out.  If you get an error, see troubleshooting steps below.

5. Enter the Display Name

6. Check the parameters that should be required.

7. Click Save.

You can now create maps and direct SmartConnect to call this proc as an eConnect proc.



So here is the scenario when it goes wrong:

1. I create a custom eConnect proc.  For the sake of this example I am NOT using node builder or any other tool.  I am just writing a sql proc and is econnect compliant.

2. I install this proc on a Dynamics GP company database.

3. I launch SmartConnect as an admin.  Go to node maintenance to add the node. For seemingly no reason, SmartConnect frequently decides that the proc does not exist.


Things to check before pulling out the old sledgehammer and reinstalling SmartConnect:

1. Make sure the proc is eConnect compliant.  Spelling on the output parameter names is important.
@O_iErrorState INT=0 OUTPUT,
@oErrString VARCHAR(255)='' OUTPUT

2. Try installing the custom node on all company databases and the DYNAMICS database before adding the node.  SmartConnect doesn't give us a way to tell it WHERE the proc is, so this works sometimes to get the node added.  You can then drop the proc from the databases where you don't need it.

3. Make sure the proc does not have any funky characters in the name (like - (dash)).  SmartConnect does not seem to like that.

4. Make sure there is a Pre and Post proc present, even if you are not using them.

5. Make sure all parameters start with @I_v except the 2 output parameters at the end.

6. Try restarting SQL server if possible, then restarting SmartConnect


If all else fails, you may have to use SQL Stored Procedure as that destination instead of eConnect Proc.