When reading binary files a programmer needs to know the format the file was saved in. A grouping of 4 bytes may be 4 alphanumeric characters, a 32-bit integer, a decimal, or some other data type. If you know a 4 byte grouping is a number your next question will be what is the endian format of the file.

The endian format describes the sequence in which the bytes are stored in memory. Endianness comes in 2 formats: big-endian and little-endian. In big-endian the most significant byte is stored first. This means the most significant byte is stored at the lowest memory address. In little-endian the least significant byte is stored first.

For example the number 1,055,291 converts to ‘101A3B’ in hexadecimal. If we store this as a 32-bit integer in big-endian format it looks like this: 0 0 1 0 1 A 3 B

But in little-endian format it looks like this: 3 B 1 A 1 0 0 0

It’s important to note that within the byte the bit order is always big-endian so the byte ‘3B’ is stored ‘00111011’ in both big-endian and little-endian.

All processors are designated either big-endian or little-endian. You can use System.BitConverter.IsLittleEndian in C# to determine what endianness your machine is using. The value will be TRUE if the machine is little-endian and FALSE if it is big-endian. IBM’s 370 mainframes and Motorola processors are big-endian. Intel processors and their clones are little-endian.

The terms big-endian and little-endian come from Jonathan Swift’s book Gulliver’s Travels. In the book the Lilliputian King requires his subjects, the Little Endians, to break their eggs at the small end. A group rebelling against the king, the Big Endians, want to break their eggs at the large end.

One recent TSG project involved a comprehensive migration from Access to SQL Server for a client’s application. This application moved from a departmental solution to one that had become mission critical for the organization. The project was broken down to multiple phases. Phase I, involved migrating mission critical data from MS Access to MS SQL Server 2008. Phase II included conversion of the database backend of a Windows Forms Application and a Web Service to SQL Server 2008. Finally, in Phase III, the development team is replacing an Access Forms Application with a web based ASP.NET application.

In phase II, it is necessary to convert dozens of in-line SQL commands over to SQL Server 2008 Stored Procedures. Also, it is required that the Windows Forms Application pass multiple datasets to a Web Service Method which will run a Stored Procedure to update the SQL 2008 database.

It would sure make coding the Web Service Method easier if we could just pass the datasets (expressed as data tables) directly to the stored procedure. Well, in SQL Server 2008, you can! Enter Table-Valued Parameters. This new Stored Procedure parameter type allows a Stored Procedure to receive multiple rows of data as input.

Before we consider the design of the stored procedure, let’s look at a snippet of the Web Service Method that is called by the Windows Forms Application as follows:

<WebMethod(Description:="Pass in DataSets from the Client Application")> _
Public Sub SynchronizeToDB (ByVal ParamArray DataSetsFromClient() As DataSet)

.....

End Sub



The Windows Forms Application passes an array of datasets to the Web Service Method. In this case, each dataset contains only one table which corresponds to one table in the SQL Server database. It is necessary to convert each dataset to a data table prior to running the Stored Procedure. Converting the dataset to a data table is accomplished as follows:

DT = New DataView(DataSetsFromClient(x).Tables(0), "Filter", "Sort", DataViewRowState.CurrentRows).ToTable()



The data table object “DT” is now ready to be passed to the stored procedure, but first we must define the stored procedure parameter that is used to pass the data table. Below is a snippet from the parameter definition:

ExecImportProc.Parameters.Add(New SqlParameter("@ImportTable", SqlDbType.Structured))



Note that the SqlDbType property of the SqlParameter is set to “Structured”. Using the Structured setting instructs the stored procedure to expect a parameter that is a table-valued parameter.

Next, we set the value property of the stored procedure parameter as follows:

ExecImportProc.Parameters(0).Value = DT



On the database side, a snippet from our stored procedure looks like this:

CREATE PROCEDURE [DataSetsFromClient]
@ImportTable tblFieldInject READONLY
AS

.....



In order to make this work, the format of the data table being passed in must match a previously defined “table type” on the database server. The ability to create a User-Defined Table Type is also a new feature of SQL Server 2008. In this case, the table being passed in is of type “tblFieldInject”. So, it behaves similar to a user defined data type, but in this case, it is a table. The table is defined thusly:

CREATE TYPE [tblFieldInject] AS TABLE(
[well] [int] NOT NULL,
[date] [datetime] NOT NULL,
[metertoday] [int] NULL,
[meteryesterday] [int] NULL,
[barrels] [int] NULL,
PRIMARY KEY CLUSTERED
(
[well] ASC,
[date] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)



To summarize the concept of Table-Valued parameters in SQL Server 2008, I will leave you with a quote from the MSDN web site:

Table-valued parameters are a new parameter type in SQL Server 2008. Table-valued parameters are declared by using user-defined table types. You can use table-valued parameters to send multiple rows of data to a Transact-SQL statement or a routine, such as a stored procedure or function, without creating a temporary table or many parameters.

Table-valued parameters are like parameter arrays in OLE DB and ODBC, but offer more flexibility and closer integration with Transact-SQL. Table-valued parameters also have the benefit of being able to participate in set-based operations.

Have a great day!
Andrew Berry

On June 3rd, at the Energy Capital Week Symposium (www.energycapitalweek.com), Brad Nickle will be guest speaker at the Senior Financial Officer Forum. Mr. Nickle will be speaking on “Communicating with Partners: Best Practices” which will include insights on how Information Technology can be harnessed to keep capital partners informed on financial performance of oil and gas assets. Mr. Nickle provides consulting services to PartnerMetrics (www.partnermetrics.com) which is a web base portfolio management system designed specifically for the oil and gas industry.

One way to develop applications that accomplishes Form and Function economically is through the use of object oriented design built around a framework. With frameworks, the goal is a concrete implementation of the mantra “Code Reuse”. Frameworks expedite the software development cycle by facilitating consistency in coding implementations. Frameworks allow developers to create the best possible software in the least amount of time.

Yet the bar is continually raised because of the challenge organizations face to positively affect “the bottom line”. Competition is constantly driving corporations to meet greater demands at lower costs. To meet ever increasing demands of competition, the platforms, tools, and framework used by software developers must evolve to support changing business climates.

The .NET Framework provides a clear example of this. Microsoft has continually upgraded the framework to meet ever changing demands. The idea of a Framework came about sometime during the last quarter of year 2000. By 2002 the first Framework was release by Microsoft. Corresponding improvements were made in 2005 (version 2.0) and 2006 (version 3.0). Recently Microsoft advance further improvements with the release of .NET Framework 3.5 which includes:

• Deep integration of Language Integrated Query (LINQ) and data awareness. This new feature will let you write code written in LINQ-enabled languages to filter, enumerate, and create projections of several types of SQL data, collections, XML, and DataSets by using the same syntax.
• ASP.NET AJAX lets you create more efficient, more interactive, and highly-personalized Web experiences that work across all the most popular browsers.
• New Web protocol support for building WCF services including AJAX, JSON, REST, POX, RSS, ATOM, and several new WS-* standards.
• Full tooling support in Visual Studio 2008 for WF, WCF, and WPF, including the new workflow-enabled services technology.
• New classes in .NET Framework 3.5 base class library (BCL) that address many common customer requests.

Creating a modern application is not simple task- the requirements are substantial. Traditional concerns such as creating effective business logic and allowing access via a Web browser are still important, but they’re no longer enough. Modern applications present a range of new challenge. For example the user wants their browser interface to act like all the data on the web is stored locally. The amount of data represented using XML has increased significantly over the past few years. These are just a couple of the challenges that have been addressed in .NET Framework 3.5.

ASP.NET AJAX has been implemented to improve browser applications response times. Prior to the .NET Framework a page would make a call to a database or to a web service. When a data was requested the page that requested the data was loaded. However in .NET Framework 3.5 the page doesn’t have to be reloaded every time a page makes a request to a database or web service.

WCF is the recommended Microsoft way of handling data across platforms and applications. Rather than requiring developers to use a different API for each kind of communication, WCF provides a common approach using a common API. Therefore, application data passed to WCF does not have to be in xml format, it can be in the format that is needed. Communication between applications, whether within an organization or across organizations, is a fundamental part of modern software challenge. The .NET Framework 3.5 addresses this challenge via the service-oriented approach of WCF.