Make your life a little easier with ODBC

I've written about the DLL import problem of BluePrism, of possible workaround with custom DLLs, but we can also make our life easier when we can.
For example in my last project i had to work with a lot of data sources as SQL Server, MySQL and Access all together.
All those above are data sources which have connectors for ODBC.
This means we can deal with all DBMS (and much more) simply installing ODBC connectors in the robot machine and using only this VBO to deal with that.
The key is to pass the driver info to the VBO so connection string could be composed.

So this are the VBO actions i created to deal with query.

Read in collection

The action you need the most.

It has two piece of code, because you could need to get lot of data (more than 300.000 rows).
Using the SqlCommand variable you can also do something more than "Select * from table" but you can also filter before getting the data.
The first code block is faster but will not work with lot of data, and the second one will be slower but it will work everytime.
Pay attention kiddo, BP passes parameter by value and not by reference, so you could need to do the "business work" in the VBO action, in order not to go "Out of memory".

OdbcConnection connection = new OdbcConnection(connectionString);
result = new DataTable();
exception = "";

try
{
 connection.Open();
 OdbcCommand command = new OdbcCommand(sqlCommand, connection);
 OdbcDataReader dataReader = command.ExecuteReader();
 result.Load(dataReader);
}
catch (Exception ex)
{ 
 exception = ex.Message;
}
finally
{
 connection.Close();
}

And this is the "big data" one.

OdbcConnection connection = new OdbcConnection(connectionString);
result = new DataTable();
exception = "";

try
{
 connection.Open();
 OdbcCommand command = new OdbcCommand(sqlCommand, connection);
 OdbcDataReader dataReader = command.ExecuteReader();

 DataTable resultSchema = dataReader.GetSchemaTable();
 List listCols = new List();
 if (resultSchema != null)
 {
  foreach (DataRow drow in resultSchema.Rows)
  {
   string columnName = System.Convert.ToString(drow["ColumnName"]);
   DataColumn column = new DataColumn(columnName, (Type)(drow["DataType"]));
   column.Unique = (bool)drow["IsUnique"];
   column.AllowDBNull = (bool)drow["AllowDBNull"];
   column.AutoIncrement = (bool)drow["IsAutoIncrement"];
   listCols.Add(column);
   result.Columns.Add(column);
  }
 }

 DataRow dataRow = null;
 while (dataReader.Read())
 {
  dataRow = result.NewRow();
  for (int i = 0; i < listCols.Count; i++)
  {
   dataRow[((DataColumn)listCols[i])] = dataReader[i];
  }
  result.Rows.Add(dataRow);
 }
}
catch (Exception ex)
{
 exception = ex.Message;
}
finally
{
 connection.Close();
 System.GC.Collect();
}

 Execute Command 

Just executes the query

OdbcConnection connection = new OdbcConnection(connectionString);
exception = "";

try
{
 connection.Open();
 OdbcCommand command = new OdbcCommand(sqlCommand, connection);
 command.ExecuteNonQuery();
}
catch (Exception ex)
{ 
 exception = ex.Message;
}
finally
{
 connection.Close();
}

 Execute Commands in a transaction

Executes more queries in a single transaction

OdbcConnection connection = new OdbcConnection(connectionString);
OdbcTransaction transaction = null;
exception = "";

try
{
 connection.Open();
 transaction = connection.BeginTransaction();
 OdbcCommand command = null;
 
 foreach (DataRow dr in sqlCommands.Rows)
 {
  string sqlCommand = dr["Command"].ToString();
  command = new OdbcCommand(sqlCommand, connection, transaction);
  command.ExecuteNonQuery();
 }
 
 transaction.Commit();
}
catch (Exception ex)
{ 
 transaction.Rollback();
 exception = ex.Message;
}
finally
{
 connection.Close();
}

A good strategy could be create CRUD action for ODBC driver in order to implement implicitily the repository pattern.
Changing the ConnectionString will just change the DataSource type and help creating a "universal" repository.
And... that's it for now!

Read More

Import-SPWeb preserves ListItemId?

If you're reading this you have my same problem, you don't have an answers to this question, and maybe you need to move a list across site across site collections/web applications/farms without changing the list item id.
Someone says yes, others say no. The answer is that both answers are true, it depends on the preconditions.

Let's start from this article which explains just a little bit how the ContentDB works.
So, let's create a custom list named "Another custom list" and let's populate it with a bunch of items.

Yeah just 3

So let's look what happens in the ContentDB.
At first let's look at the list id, and we can find it with just a select on the AllLists table querying by title.

Aggiungi didascalia

With the list id in your hands you can find all the entry of the items in AllUserData table, which is the repository of all the ContentDB Items (you can also have a look to the composed primary key of the table from the previous screenshot).

Remember something?

Now, let's delete item 1 and create item 4 and 5 and let's see what happends (Please note that Item1 is still in the content db until i delete it from site collection recycle bin). Moreover in the AllListAux i can now see the counter used to give the list item id to the object.

So this is what happens behind the scenes

Mmm.. Aux table

It's now time to export the list and take a look to what SharePoint made, so unzipping the cmp file, i can read this in the Manifest.xml
You can see that every item is serialized in xml, and surpise surprise there is an attribute called IntId, so let's see what happens importing this cmp file in another site collection:

Import-SPWeb http://sp2013 -Path "C:\TEMP\anothercustomlist.cmp" -IncludeUserSecurity -UpdateVersions Overwrite

and the result is that i've now a new list with a new id, but same informations in the AllListAux and AllUserData.

Only GUIDs changed in the import

and only GUIDs

So, even if i'm not showing you the AllUserData table, also ListItem IDs remains the same, so the answer to our question seems to be yes.
But what if i try to reimport? Well, items are not overwritten and i can see that same object are replicated with different list item id which starts from the value in the aux table.
So the answer is no if you are importing in a preexisting list with other items, because you never know what could be the next item id.

Below you can find some code i used to do a final test in order to get a lot of item from a list with more than 6000 items (which is not the one of this example) from the source list and the destination one, in order to do a final compare which led me to understand that Import-SPWeb is something good :)

string siteUrl = "http://sp2013/sites/test";
string listTitle = "Test";
string user = "Administrator";
string domain = "DEV";
string password = "Password";

using (ClientContext clientContext = new ClientContext(siteUrl))
{
 clientContext.Credentials = new NetworkCredential(user, password, domain);

 List list = clientContext.Web.Lists.GetByTitle(listTitle);
 clientContext.Load(list);
 clientContext.ExecuteQuery();

 ListItemCollectionPosition itemPosition = null;
 while (true)
 {
  CamlQuery camlQuery = new CamlQuery();
  camlQuery.ListItemCollectionPosition = itemPosition;
  camlQuery.ViewXml = @"
  
   
   
  
  1000
   ";

  ListItemCollection listItems = list.GetItems(camlQuery);
  clientContext.Load(listItems);
  clientContext.ExecuteQuery();

  itemPosition = listItems.ListItemCollectionPosition;

  foreach (ListItem listItem in listItems)
  {
   Console.WriteLine("Item Title: {0}", listItem["Title"]);
  }


  if (itemPosition == null)
   break;

  Console.WriteLine(itemPosition.PagingInfo);
  Console.WriteLine();
 }
}

Read More

BluePrism: drill down about housekeeping BP DB

As you already know, it's so easy to finish space on a DB server when you use BP.
The reason could be, a poor design of logging (e.g log everything) or maybe logging in a loop.
It's clear that you don't need to log everything, but only what you have effectively to justify if you make a mess and also log errors for sure.
Now you are thinking: what the hell I've done? Why i was so stupid to log everything?

Well, you are in good company, I did the same. But now it's time to fix everything.
Now you are thinking that you can use Archiving feature to store the session log somewhere but what if archiving doesn't work, or mabe you just want to erase the unneeded data.

Always remember that delete a lot of data does not mean only wasting a lot of time, but also to see the transaction log growing a lot, so try this only when you are sure you can backup the DB.
It's clear that there is always the risk of corrupting the DB when you do something at the core level of a product.

So let's begins, the first thing you can do it's to delete the content in BPAScheduleLog and BPAScheduleLogEntry. The scheduler log grows perpetually so could be a good idea to trucate BPAScheduleLogEntry and delete BPAScheduleLog, this is what BP says, they also provide you a script to delete all data in a particular time frame, but this is another story.

The other massively filled table is BPASessionLog_NonUnicode, and here BP propose a script which helps you delete all entries, but in our case we want to selectively delete only the log entry we need (maybe log about a specific Business Object or Process Page).

BP said it could work so, before applying on a real DB, let's test it up.
Let's create the base case, so I created 2 processes:

• Test.Log1
• Page1: calls Test.VBO.Log1 actions
• Page2: calls Test.VBO.Log2 actions
• Test.Log2
• Page1: calls Test.VBO.Log1 actions
• Page2: calls Test.VBO.Log2 actions

and 2 VBOs

• Test.VBO.Log1
• Action1: returns 2+2
• Action2: returns 2*2
• Test.VBO.Log2
• Action1: returns 2-2
• Action2: returns 2/2

Log is enabled everywhere, and i made every process run 2 times on the same machine.

 Here i entered in the DB and i discovered with this simple query what combination of:

• Process name
• Process page name
• VBO 
• VBO action

is logged the most

SELECT processname,pagename,objectname,actionname,count(*) as num
FROM [BluePrism].[dbo].[BPASessionLog_NonUnicode]
group by processname,pagename,objectname,actionname order by num desc

And here... only the brave... what happens if i selectively delete rows here?
The answer is: absolutely nothing, i posted some screenshot below indeed that makes you understand the only pitfall i noticed on my local Blue Prism installation.

So, have fun cleaning your DB from unnecessary rows.
See ya.

Read More

A Blue Prism project with custom DLLs: load DLL from everywhere

I admit, this is one of the most painful points when i work with Blue Prism. As explained in other posts about DLLs, we have to put class libraries into the root folder of Blue Prism in order to use classes.
This could be a problem when you don't have the rights to access Blue Prism root folder, so with the great Antonio Durante, we find a way to solve this problem using another forgotten beauty: the reflection.

Here you can download a little project in Visual Studio, which is composed of some lines of code which simply get the content of a editable PDF and return as a DataTable containing as many rows as lines of text found in the PDF.
This piece of code uses iTextSharp.dll to do the work so we have a depencency here.

public class Program
{
 public Program(string blablabla)
 {
  // This is a fake constructor
 }

 public static DataTable LoadPdf(string path)
 {
  List data = PdfHelper.ExtractTextFromInvoice(path);

  DataTable table = new DataTable();
  table.Columns.Add("Row", typeof(string));

  DataRow row11 = table.NewRow();
  row11["Row"] = "Yeah 1.1 version as well";
  table.Rows.Add(row11);

  foreach (string item in data)
  {
   DataRow row = table.NewRow();
   row["Row"] = item;
   table.Rows.Add(row);
  }

  return table;
 }

 static void Main(string[] args)
 {
    DataTable t = LoadPdf(@"C:\Users\v.valrosso\Downloads\test1.pdf");
 }
}

The code here is very simple, but just to see again how reflection work, i write also this code to test everything. As you can see i load only the assembly in a folder where you can find also the referenced DLL (i've used directly the Debug output folder of the dummy project)

class Program
{
 static void Main(string[] args)
 {
  string assemblyPath = @"C:\TFS\Test\Code\Poc.ReferencingCode\bin\Debug\Poc.ReferencingCode.dll";

  Assembly asm = Assembly.LoadFrom(assemblyPath);
  //Assembly asm = Assembly.Load(File.ReadAllBytes(assemblyPath));
  Type t = asm.GetType("Poc.ReferencingCode.Program");

  var methodInfo = t.GetMethod("LoadPdf", new Type[] { typeof(string) });
  if (methodInfo == null)
  {
   // never throw generic Exception - replace this with some other exception type
   throw new Exception("No such method exists.");
  }

  object o = Activator.CreateInstance(t);

  object[] parameters = new object[1];
  parameters[0] = @"C:\Users\v.valrosso\Downloads\test1.pdf";       

  DataTable r = (DataTable)methodInfo.Invoke(o, parameters);
  Console.WriteLine(r);
 }
}

After that we can just play with BP creating a new VBO just pasting the code and ... les jeux sont faits.

Just  a warning, BP locks the DLL so you have think something more smart (Antonio and I have developed something very very smart but sadly i cannot show you because of it's top secret).

As usual thanks to my colleague and friend Antonio Durante for the wonderful work we make together everyday.

Read More