SharePoint – The Start

Last year I decided to learn more about Microsoft Office SharePoint Server (MOSS) and in particular Excel Services. Instead of setting up a VMWare configuration I decided to buy a physical server to which my developing machine will be connected to as the only client.  Because of my security approach none of these two machines have access to Internet. Yes, I’m the first to admit that I’m paranoid but that is the way it is.

Hardware
I got a nice price for the server Fujitsu Siemens FSC PRIMERGY ECONEL 100 S2 and bought more memory so it now has 2 GB. The server has two hard drives and therefore I decided to use a RAID 0  solution so one drive mirror the other drive. Previously I only had a 2 port KVM Switch but now I needed a 4 port Switch so I can switch between my laptop, my development machine and the server. The model I selected was Athen CS1734B which is one of a very few KVM Switches that can work with Windows Server 2008.

Softwares
I decided to use Windows Server 2008 x64 standard edition which also gives access to the Hyper V technology. Unless we have all the knowledge required to configure and running a server and installing all the softwares we need some helps. In my case I prefer printed books and the help I got is from the book “Windows Server 2008 A Beginner’s Guide“. It covers the most basic to install and configure a Windows 2008 server and is written in a understandable way.

Next, a SQL Server 2008 Express Edition was installed and it did not cause any problems at all.

Installing a MOSS 2007 server requires one important preparation, to set up 11 accounts with names and passwords. They are required for both the Windows SharePoint Services (WSS) and for the MOSS server. Andreas Glaser has published a nice installation guide; Installing MOSS 2007 on Windows Server 2008 and SQL Server 2008 . However, to understand more a book is required and I was recommended to use “Beginning SharePoint 2007 Administration” written by my fellow countryman Göras Husman. At present I have only started to read the book.

Finally, the only thing that remains to do is to learn more about MOSS and what we can do with its Excel Services!

Kind regards,
Dennis

Learning LINQ

At present my time table does not permit me to explore new things so I have postponed delving into some areas to the next year.

However, in some cases I have made some research to get access to interesting sources and tools which I hope will help me out. LINQ is one area that is of high interest which may replace SQL within the foreseeable future. VS 2008 is the first development suite that includes LINQ.

The Book
I prefer books that target one specific area and the book LINQ in Action seems to fit in well. Actually, the book has its own site and it is also available as a PDF ebook. For more information please see LINQ in Action. 

The Tool
The tool LINQPAD seems to be very useful to learn and manage LINQ. It may also be more promising as it also is available for free. You can visit its homepage at the following URL: LINQPAD

The developer of this tool, Joseph Albahari, is also one of two authors of the book C# 3.0 in a Nutshell which is highly recommended if we want to learn C# together with another book, Programming C# 3.0.

Please feel free to comment available sources about LINQ as well as tools for learning how to use it.

Kind regards,
Dennis

Some links to useful sources

A new and time consuming project keeps me busy so at present I don’t have the time to post here on a regular basis. Anyway, the following links points to several recently published articles by MSFT. 

Finally, the version 1.0 of Open XML Format SDK is released. It provides us with new tools to interact with the XML Format: Open XML Format SDK  

And the following link gives information on  how to use it: Welcome to the Open XML Format SDK 1.0    

In the light of the SDK, one of the questions that should be addressed is how strong is actually the protection for Excel 2007 Workbooks? If the time permits me I will take a closer look into this area.

Finally, MSFT has recently released two VSTO articles that explicit targeting VSTO 2008 (aka version 3.0) and Office 2007:

Manipulating Excel 2007 and PowerPoint 2007 Files with the Open XML Object Model (Part 1 of 2)
Manipulating Excel 2007 and PowerPoint 2007 Files with the Open XML Object Model (Part 2 of 2)

For those of You who use SQL Server 2000 and would like to implement XML the following page should be of high interest: SQLXML

As usual, check out Andrew Whitechapel’s blog on a regular basis. Some of the technical stuff is beyond my knowledge and understanding but I learn a lot by just reading the posts: Andrew Whitechapel blog

Finally and just for fun, did You know that VS 2008 is shipped with a larger library of icons and images? On my developer machine the zipped library file was located in the folder:
C:\Program Files\Microsoft Visual Studio 9.0\Common7\VS2008ImageLibrary\1033

Kind regards,
Dennis

Export data from DataGridView to Excel

A common task is to view filtered data from databases in Windows applications where the users then can select records to be imported into Excel. In this context the main issue is usually how to achieve an acceptable performance when writing the selected data to Excel.

When it comes to automation of Excel the communication between Excel and for instance a .NET application is said to be an Out-of-Process communication as Windows allocate separated memory areas to them. In other words, the applications do not share the same memory area which has a negative impact on the overall performance.

When it comes to .NET automation of Excel there are two additional aspects we need to be aware of:

• The COM interop calls themselves create an overhead and
• The more calls to Excel Object Model the slower it gets.

The best approach is therefore to reduce the number of calls to Excel Object Model and before calling Excel prepare the data as much as possible. In VBA we have the data type ‘Variant’ which is good to use when working with cells’s data. In .NET we have a similar data type by the name of ’Object’. The final key to increase the speed is to use as much as possible 2D arrays (which are 0-based) and when necessary declare them as ’Object’.

The Case

For the case I use the Northwind database which is connected to a SQL Server 2005 where the data is presented for the users in the following Windows form:

 

The screenshot below shows all the code in use for the case. The ‘Export’ procedure shows the use of 2D arrays where they in the first step are populated with data in managed code. In the next step the data is ‘dumped’ in one go into the targeting COM worksheet:

The above code can be downloaded from here. Of course, in the above case we could have reduced the number of arrays to only use one.

When I have managed to fully port myself to VS 2008 and when SQL Server 2008 is out I will make some posts about LINQ to SQL.

Kind regards,
Dennis

.NET Connection Tool

Introduction 
.NET Connection is a new free .NET based tool for MS Excel to create and store connection strings in to a various number of databases and to other sources. This is my first public free .NET based tool for MS Excel and it will be followed by additional tools based on the .NET technology in the future.

It can create connection strings with both the Data Link Wizard as well as with the .NET Data Link Wizard. The .NET Data Link Wizard provides us with access to .NET Framework Data Providers to create connection strings with. These Data Providers are not available through the Data Link Wizard.

To store and handle the connection strings it uses a Microsoft Database (aka Access database). It may appear as an ‘overkill’ but in the future it will also handle additional  data of different types.

Per se it is a COM Add-in which has been developed and deployed with Add-in Express 2007 for Microsoft .NET. It is also an update of my free ADO Connection Add-In for Excel.

Requirements & Installation 

Requirements:

• Excel 2000, 2002, 2003 or 2007
• .NET Framework 2.0 and higher
• Windows 2000 SP-4, Windows XP SP-2 or Windows Vista
• (The option “Trust access to the VBA Project object model” must be checked – I still prefer that this is done by the owners of the computers then changing it through Windows Registry – code of ethics )

To install .NET Connection Tool You download it from here, save it to the hard drive and then execute the MSI-package.

The package has intentionally been kept simple and clean. It does not check if the above requirements are met or not.

Using .NET Connection Tool
After the installation of the tool a new item has been added to the menu in the VB Editor as the following screenshot shows:

When selecting the command .NET Wizard it starts with the following dialogform:

In the next step the following dialogform is showed:

The following dialogforms are similar to both Wizards which make it possible to store the created connection string with a unique name:

Please also see the startpage at MSDN for .NET Data Providers

Let me me know what You think about it and also how it can be improved.

Special note:
The .NET Connection Tool is provided “AS IS” and with no liabilities from Dennis Wallentin or XL-Dennis.

Kind regards,
Dennis 

Transfer Excel ‘07 data to SQL Server 2005 EE with ADO.NET

I have decided to place all code in articles at my English site and refer to them in the blogpost instead of trying to ‘formatting’ the code here. 

An update:
My good local friend Stefan Holmberg had the kindness to give me some additional input about ADO.NET 2.0. In return I have updated the sample in use where the method UpdateBatchSize of the SQLDataAdapter allow us to add all records in a batch mode to the database. 

This post is about how we can:

• Retrieve data from closed workbooks (Excel 2007) with a new Provider and
• Update a SQL Server 2005 EE (Express Edition) database with the retrieved data. 

With the release of Office 2007 the Jet OLEDB Provider has been replaced with a new Provider. The new Provider’s name is ”Microsoft ACE OLEDB 12.0″ and there is the following good news with it:

• The 255 charachter limitation is removed.
• It reads both Excel 8 ’source databases’ (version 2000/2002 (XP) and 2003) and Excel 12 (version 2007) ’source databases’. It means that the provider can read workbooks in the following file formats XLS, XLSX, XLSM and XLSB.

The provider works with both classic ADO and with ADO.NET. Since this blog is about .NET and Excel I decided to create an example (available on my English site) where we use ADO.NET and with untyped Datasets:

Transfer Excel ‘07 data to SQL Server 2005 EE with ADO.NET

The use of Datasets can be discussed but here I find it to be a rather smooth solution, especially when using ‘local’ dataset where the data from it is merged into a ‘public’ dataset.

At present I’ve not yet find any sources that explicit discuss the provider and Excel workbooks as ‘database sources’.  Please let me know if You know of any source related to the subject.

Kind regards,
Dennis

Using SQL Server .NET Data Provider to connect to SQL Server 2005

There was some comments on my previously post about how to connect to SQL Server 2005. I decided to create a special post about it and I hope that it can give some guidelines when it comes to create DSN-less connection strings.

The SQL Server .NET Data Provider uses Tabular Data Stream (TDS) to communicate with the SQL Server, which actually is the same protocol that SQL Server use as the native protocol. The advantage is that by using this Data Provider the performance increase heavily compared with going via the OLE DB layer or the ODBC layer. The CLR (Common Language Runtime) don’t need to marshal the COM data types to .NET CLR data types each time a SQL Server’s database is accessed.

The drawback is that we can’t build connection strings by using the Data Link Wizard in Excel 2000 - 2003 in the same way as we can do for OLE DB Providers and ODBC drivers. It requires that we know all the information before creating the connection strings. 

In Excel 2007 we can use the Data Connection Wizard which also requires that we have all the required information available before building the connection.

Workaround – Creating a connection string in .NET

An alternative approach to retrieve the connection string is to:

• Create a project in VB.NET.
• Start the wizard for connecting to a database, Tools | Connect to database…
• Complete the required information in the main dialog and test the connection.
• Click on the Advanced… button.
• Copy the connection string in the bottom of the dialog.

Syntax – Local connection

In order to connect to a local running instance of SQL Server 2005 the following syntax can be used:

“Provider=SQLNCLI.1;
“Integrated Security=SSPI;
“Persist Security Info=False;
“Initial Catalog=Name of the database;
“Data Source=Name of the Computer\Name of the running instance of SQL Server”

The following connection string is valid for me when connecting to my local server:

“Provider=SQLNCLI.1;
“Integrated Security=SSPI;
“Persist Security Info=False;
“Initial Catalog=AdventureWorks;
“Data Source=IBM\SQLLocal” 

Syntax - Remote connection 

In order to connect to a remote SQL Server 2005 the following syntax can be used:

“Provider=SQLNCLI.1;
“Persist Security Info=False;
“User ID=User;
“Initial Catalog=Name of the database associated with the User ID;
“Data Source=IP-number”

The following connection string is valid for me when connecting to my remote server:

“Provider=SQLNCLI.1;
“Persist Security Info=False;
“User ID=sa;
“Initial Catalog=AdventureWorks;
“Data Source=xx.xxx.xxx.xx”

The above connection strings does not save the password for any UserID which will require some additional processing. Personally I use a ‘Data Access Component’ (DLL) to do the work.

Although I have not tested it but the above connection strings should also work with the Express Edition of SQL Server 2005. 

Kind regards,
Dennis