Latest Articles: ADO .net

This week a client sent me an Excel Spreadsheet that needed to populate several tables in a SQL Server Database. To know me knows I hate data entry of any kind and there was no chance I was entering the Excel data in manually.

Thankfully, we don't have to. You can use the OleDb Managed Data Provider in the .NET Framework to read an Excel Spreadsheet using ADO.NET and C# just like you would with a database.

If you have to generate or read simple Excel content, ADO .net is a good choice, it can run on the server side and has a good performance.

A bit old but quite interesting Web Cast about ADO .net Entity Framework. Good to know the basics of this technology and what can we expect from it.

Just to get the basics concepts and advantages.

Interesting community letter and Ms answer, is ADO .net Entity Framework a 1.0 version that sould not be used? It's a good idea to wait for a 2.0 version?

Reading his article can help you on building up your own decision, you will read opinions from both sources.

Silverlight + ADO .net Services (formerly know as Astoria) is the perfect match to develop RIA applications, superb front end using silverlight, easy datalayer development using ADO .net Services (generates automatically a DAL based on web services).

Quite interesting, how to perform CRUD operations on a Silverlight DataGrid using ADO .net services (data web services and ADO .net entity framework).

The article is quite summarized, you will need to download the sample code.

Office 2007 introduced the Open XML format.. . an open format but quite complex, to make things simplers you have the chance of separating layout from content by attaching a pure XML data file to the docx (word document) and map it with user control in your word layout (the only big limitations is that you can not use it for detail / table information :-().

This samples shows you how to open a docx, grab the XML data file embedded on the docx and replace it by another read from a database (SQL Server).

Very interesting sample, if you are using Visual Studio 2008 forget about the lines that tells you how to install .net fx 3.0 :-)

When you look for Office 2007 and content controls samples / documentation is all more related to handling XML and associate it with Ms Word using VBA

This sample goes one step forward: how to associate controls to data retrieved from a dataset (built up using select query from a Database) using VSTO 3.0 (Visual Studio Tools for Office and real .net coding !)

Configuring ADO .net connection strings is a prone to error task, you have to remember every single parameter and setting

This cheatlist will help you configuring any SQL Server 2005 configuration you can find help as well for other DB engines, Oracle, DBase, Paradox, DB 2...

About NULLABLE DataReader Wrapper: It's not yet in the framework. ADO.NET just doesn't leverage nullable value types in this release.

For the DataReader, we would need a new interface, say INullableDataReader to return the nullable types.

If you are using generics, datareaders, and nulls in your DB this post will save you a lot of headaches.

Here's an implementation of INullableDataReader a utility class for wrapping an IDataReader in a INullableDataReader, which should be helpful. Also it illustrates some of the issues in integrating nullable types and ADO.NET. Nullable types really would require a seperate ADO.NET library. Using nullable types in va Data Access Layer is a great idea, but we will have to build up the infrastructure to do it seperately from ADO.NET, for now.

How to launch an SSIS job from a remote machine (e.g. an ASP .net application hosted on an IIS).

You will find two approaches on using ADO .net (calling a system stored proc) the other one via Web Service.

Last weekend I posted about how to implement super efficient data-paging using the new ROW_NUMBER() within SQL 2005 (which is supported with the free SQL Express edition and up) and the new ASP.NET 2.0 GridView/ObjectDataSource controls.

The BIG win with this approach is that it enables a web-application to efficiently retrieve only the specific rows it needs from the database, and avoid having to pull back dozens, hundreds, or even thousands of results to the web-server – only to then end up trimming them down to the 10-15 you end up displaying on a web-page (which is what most data-paging solutions do today). This can lead to significant performance wins within your application (both in terms of latency and throughput), and reduce the load on your database.

A few people have asked me to also provide a similar sample that demonstrates how to also implement efficient data-paging using an ASP.NET DataList or Repeater control instead (since neither of these have built-in paging UI semantics – but they do have the big benefit of allowing developers to have tight control over the html generated).