Chris Pietschmann

In my own personal discussions with other developers and computer users I've seen a fair amount of confusion as to what a database is. Since the purpose of almost all applications is to Create/Read/Update/Delete data, they need to connect to a database, since data persistence storage need to be provided for the application to function as intended. If you want to stick to the simplest definition below, then a file cabinet is technically a database, but now that we're in the information age we don't really think of file cabinets as such since we can't search them very easily without physically looking at every folder or file it contains. Also, the my point in writing this article is to point out what databases are as they relate to software, so I'll keep things below within the digital sense.

Below I will describe what Databases, Software Database Server and Data Access Layers are. Also, I will define how a Web Service is really both a Data Access Layer or a Database.

What is a Database?

I would refer to the definition that Wikipedia gives as the simplest, most basic definition:

"A database is a collection of data..." from Wikipedia

And, to be a little more specific you can also look at the definition from Wiktionary:

"1. A collection of (usually) organized information in a regular structure, usually but not necessarily in a machine-readable format accessible by a computer.

2.A software program for storing, retrieving and manipulating a database" from Wiktionary

Most Commonly Used Databases

All software databases boil down to file stored on a hard drive or some other persistent storage, but for all practical purposes of developing software there are really 2 basic types of databases:

1. "Flat" File
2. Software Database Server

"Flat" File Databases

This is the most simplest form of a database used by software, and these are just files stored that contain a "machine-readble" representation of the data stored. Some of the common methods of formating the data within flat files are: XML, JSON, Delimiter-Separated Values (CSV; commonly Tab or Comma separated) or Binary. All of the previously mentioned data formats are "marchine-readable" and "humarn-readable" (meaning that a person could open the file and read it's contents directly) with the exception of the Binary format. The most common formats used in most applications are probably XML and CSV.

Software Database Servers

Software Databases are applications that provide the service of storing data, allowing you to query the data (usually using the SQL language), and exposing the data to be consumed/modified by another Software Application. At the core of a Software Database Server all data will end up in some kind of File stored within persistent storage (such as a hard drive) and most software database servers will store that data in Binary ("machin-readable" only) format. A couple of the most widely used Software Database Servers are: Microsoft SQL Server and MySQL.

Can an Software Application/Program Be a Database?

Yes! The above difinition of a Software Database Server described a Software Application that is also a Database because it provides the "service" of storing/retrieving data to some other Software Application.

What is a Data Access Layer (DAL)?

When talking about Software and Databases, you can't leave out Data Access Layers. Below is the definition of Data Access Layer from Wikipedia:

"... a layer of a computer program which provides simplified access to data stored in persistent storage of some kind ..."

in the simplest form, a Data Access Layer is just the code or part of a software application that connects directly to a Database. If your application stores data within a Flat File in XML format, then the code that Reads/Updates/Saves the XML file is the Data Access Layer. Also, if your application uses a Software Database Server such as Microsoft SQL Server to store data, then the code that communicates with the MS SQL Server is the Data Access Layer. Simply put, the Data Access Layer is the code that bridges the gap between the Application and the Database.

Is a Web Service a Database or a Data Access Layer?

Well, actually a Web Service is BOTH a Database and a Data Access Layer. To explain why, first we need to define what a Web Service is.

Below is Wikipedias definition of a Web Service:

"Web services are typically application programming (API) or web APIs that can be access over a network, such as the Internet, and executed on a remote system hosting the requested services."

Basically a Web Service is a Software Application that provides some kind of remotely accessible service that is consumed by another Software Application. If a Web Service provides the service of storing/retrieving data (as most do), then you loop back around to the exact same definition given above of a Software Database Server. Additionally, for a Web Service to be a database it can store/retrieve the data in either "flat" files directly, or use another separate Software Database Server that it accesses to do the storage/retrieval within persistent storage for it.

Additionally, Web Services are most often used as the code or part of a Software Application that connects directly to a Database and provides that data to the Application; bridging the gap between the Application and Database. And, now we have also looped back around to the exact same definition of a Data Access Layer (DAL).

Following the above definition and descriptions of what a Web Service is; it can be concluded that really a Web Service is BOTH a Database and a Data Access Layer.

Conclusion

As you can see, a Web Service can be both a Data Access Layer and a Database. Usually developers only think of a Web Service as a Data Access Layer, but since it provides a collection of data to another application, then it technically is also a Database. Also, the definitions of the terms Data Access Layer and Database as they relate to your Software Application/Program can vary slightly based on your perspective of how they are used. To a Web Developer or Silverlight Developer a Web Service is a Database, but in the perspective of the Software Architect designing the entire solution then the Web Services becomes just a Data Access Layer.

I don't mean to confuse anyone with this article, nor am I saying that a Data Access Layer and Database are the same thing in all cases. The main point of this article is just to point out that the definition of each term is relative to the way that each piece of the application interacts.

database, SoftwareBasics database, software development

I'm currently building a desktop application that needs to store a bunch of data on the users computer, so I've decided to use SQL Server Compact 3.5 for this. There's only one problem, Visual Studio 2008 doesn't support using LINQ to SQL with SQL Server Compact. However, the .NET Framework does and there's a work around to get it working in VS'08 that I'll describe below.

In case you aren't aware of what SQL Server Compact 3.5 is, here's the short description that Microsoft gives on their website: "Microsoft SQL Server Compact is a free SQL Server embedded database ideal for building standalone and occasionally connected applications for mobile devices, desktops, and Web clients." You can download it and view more information here: http://www.microsoft.com/Sqlserver/2008/en/us/compact.aspx

Steps to create a LINQ to SQL application using SQL Server Compact

To make this easy to follow I'm going to start with Creating a Solution in Visual Studio and go through all the steps needed to get it running off of SQL Server Compact.

Also, you'll first want to make sure you have SQL Server Compact 3.5 installed. It may have been installed when Visual Studio 2008 installed; I'm not sure which editions install SQL Server Compact 3.5, when I installed Visual Studio 2008 Team Suite it did install SQL Server Compact 3.5 too. If you need to install it, you can find the installer here: http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx

Step 1: Create your new project within Visual Studio

It doesn't matter if it's a Windows Forms or WPF applciation. For this example, I'll be creating a WPF application.

Step 2: Create your SQL Server Compact database. For Example: MyDatabase.sdf

You can do this by right-clicking on your Solution within the Solution Explorer and selecting "Add - New Item..." then select the "Data" category on the left and "Local Database" on the right.

Step 3: Add some Tables to the Database

You can do this within Visual Studio 2008 using the "Server Explorer". The easiest way to open up the Database within the Server Explorer is to just double click it withinthe Solution Explorer.

Also, in this example I'm just going to create a "Person" table with ID, FirstName and LastName columns.

Step 4: Create a .dbml descriptor file for the Database

To do this you need to use the SqlMetal.exe tool. Just type the following into the Visual Studio 2008 Command Prompt:

SqlMetal.exe MyDatabase.sdf /dbml:MyDatabase.dbml

By default, the SqlMetal.exe is located at drive:\Progream Files\Microsoft SDKs\Windows\vn.nn\bin.

Step 5: Add the Existing .dbml file to your project

Once you add the .dbml file, Visual Studio 2008 will automatically generate the .NET code (C# or VB.NET) for the System.Data.Linq.DataContext class that you'll use to access your data.

Step 6: Start Coding away using LINQ to SQL!

You can now code using LINQ and your new System.Data.Linq.DataContext class against your database.

A GUI UI for SqlMetal.exe

The SQL Metal Open Source User Interface Project over on CodePlex has been built just for the purpose of making it easier to generate mapping files for SQL Server Compact databases.

You can download it here: http://www.codeplex.com/sqlmetalosui

Related Links

Here are some related links on this topic:

LINQ with SQL Server Compact (a.k.a. DLINQ with SQL CE)

LINQ to SQL...compact!

Using LINQ to Access SQL Server Compact Directly - A follow up

database .net framework, sql, linq, visual studio

I installed SQL Server Compact 3.5 x86 and started testing it out when I got a nice "Unable to load DLL 'sqlceme35.dll" runtime exception. This seems like odd behaviour for a clean install. In a fairly quick search I found an article on the SQL Server Compact Team Blog that covers a couple possible causes/solutions for this exception.

http://blogs.msdn.com/sqlservercompact/archive/2007/10/26/can-t-find-p-invoke-dll-sqlcemenn-dll.aspx

My Solution - Target x86 Only

Even though there are 5 possible causes/solutions listed, only one of them was the issue I was experiencing and I think is probably the most common issue.

Quoted from the above post:

If your machine is a 64-bit box, you might have been got trapped into default target platform 'Any CPU' trap, please set the target platform as 'x86'.  Need more details, please visit ErikEJ's blog post.

To fix it all you need to do is open up the Build Configuration Manager within Visual Studio and change the Active Solution Platform for your application from "Any CPU" to "x86". Then rebuild and it'll work just fine.

The reason for this issue according to ErikEJ's post is "SQL Compact is only supported in 32 bit WoW (Windows on Windows) mode on the x64 platform." From my understanding of how things work, when you compile for "Any CPU" your application will be run under the 64-bit .NET Framework on x64 Windows, and since SQL Compact is only 32-bit then Windows is unable to fire it up within the same process. This is because a single process in Windows can be either 32-bit or 64-bit and cannot run both 32-bit and 64-bit code within the same process. This is why it runs without throwing the exception once you change your app to target "x86" (32-bit).

What about 64-bit (x64) support?

Since the above SQL Server Compact Team Blog post was written, they have released a x64 version of SQL Server Compact 3.5. So x64 IS supported.

Now, if you distribute your application targeting "Any CPU" and bootstrap the SQL Compact x86 installer within your applications installer you will end up having issues if your users are running a 64-bit edition of Windows. So , to really support x64 Windows you really have two options:

1. Change your application to target "x86" and distribute it that way. It'll run on both x86 and x64 editions of Windows.
2. Alternatively, you could create two seperate versions of the installer for your application; one that targets x86 and one that targets x64.

I prefer the first option because it has a couple benefits. Even though it only targets x86, it will work on x64 systems too, and you relieve users from being required to understand whether they should install the x86 or x64 version. Plus this way you only need to worry about building your application and installer a single time for each release.

At the below link you can download both the x86 and x64 versions of SQL Server Compact 3.5 SP1:

http://www.microsoft.com/downloads/details.aspx?FamilyId=DC614AEE-7E1C-4881-9C32-3A6CE53384D9&displaylang=en

database sql, .net framework

I've been lucky enough to get 2 free MSDN subscriptions over the last few years, so I've never needed to purchase Visual Studio, Office or SQL Server. The first MSDN subscription I won in a blogging contest back in 2006, and the second I got when I won the Microsoft MVP award earlier this year (2008). Before you start to think that I'm braging; just let me point out that I am very greatful for this. Below is a list/guide that I would follow if I wasn't so fortunate, and I'm not talking about softare piracy (that really is stealing!).

Now the point of this post... What if I didn't win these MSDN Subscriptions? Then, how exactly would I obtain the software necessary to do my job?

No the answer isn't to borrow CD/DVD's from work or a buddy. And, No it's not to fire up Bit Torrent. The software I mention below (all of it) are all freely available to anyone.

Free Tools for Developers

There a quire a few tools out there of various types. Below, I'll focus on the ways you can obtain, for FREE, software that you may otherwise have to pay for. Also, there are so many tools of various kinds, so I'll just be focusing on the main ones that you should be aware of.

Integrated Developement Environment (IDE)

• MS Visual Studio Express Editions - These are FREE editions of the full Visual Studio product. There is no restriction to selling the software you build using these tools, and they contain all the features necessary for you to build your software. These include Visual Basic Express, Visual C# Express, Visual C++ Express and Visual Web Developer Express.
• SharpDevelop - This is a FREE, Open Source IDE for developing .NET applications. It's been around since before Microsoft released the Visual Studio Express editions.
• MS Silverlight Tools for Visual Studio 2008 SP1 - This is a FREE add-on to Visual Studio (both Full Editions and Free Express editions) that enables you to build Silverlight 2 applications. You can find more info on this here.

Database

• MS SQL Server Express Edition - This is a FREE edition of SQL Server. There are some scalability requirements with it, but it's still perfect for development purposes.

Other Tools

• .NET Reflector - This allows you to easily view, navigate and searh through the class hierarchies of any .NET assembly; even if you don't have the source code. This tool is invaluable, since it can decompile .NET assemblies and show you the VB.NET, C# or IL code.
• Internet Explorer Developer Toolbar - This is an add-on to IE7 that adds a much improved (and needed) set of client-side/javascript debugging tools.

There are plenty of tools you can use to develop software for the Microsoft/Windows platform that are completely FREE. All you need is a copy of Windows to begin with, and that comes with any PC you buy.

How to obtain paid tools at No Co$t

Since you can't exactly go to Microsoft.com and download the Full/Paid versions of their software for Free; there are time when they give it away. You just need to look out for those nice opportunities and take advantage of them.

Here's a couple examples in the past that I took advantage of, if I remeber them correctly:

• Once upon a time there were some online screencasts for VB.NET from Microsoft. If you viewed a certain number of them within a certain time period, then you could enter your mailing address and Microsoft would send you a copy of Visual Basic .NET 2003 for FREE.
• Back in 2005, Microsoft had this "Ready to Launch" tour that was put on by the MSDN Events people (it was a free conference). If you attended you got a CD/DVD pack that included the Full, RTM versions of Visual Studio 2005 Standard Edition and SQL Server 2005 Standard Edition for FREE.
• In Dec. 2006, Microsoft gave a bunch of copies of Visual Studio 2008 Professional edition to the Wisconsin .NET Users Group to give away at a special Launch/Holiday meeting that month.
• In early 2008, Microsoft put on the {Heroes Happen Here} events across the country, again they were free copies of Windows Vista Ultimate w/ Service Pack 1, Visual Studio 2008 Standard, SQL Server 2008 CTP, and Windows Server 2008 1 year trial.

As you can see, I've been able to take advantage of a few FREE opportunities to obtain the tools I need to do my job. I really encourage you to take advantage of these if you can.

Happy coding, now go get your Free Tools. And, remember, Do Not Pirate Steal Software; would you want someone to steal the software you write, the same software you're trying to make money on?

asp.net, C#, database, Silverlight, vb.net .net framework, windows, software development, tools

Parsing a delimeted string in T-SQL is fairly simple to do, but it does take a nice little chunk of code. I most commonly use this chunk of code when I need to pass an array from an application into a SQL Stored Procedure. The best way to implement this code into an application would be to create a SQL Function that you pass in a delimted string and it returns a table of values. This way you don't have to duplicate the code everytime you want to use it.

The following example parses the delimeted string and places each value into a variable of type Table. Once the values are placed into the table you can do anything you need to save/process these values.

--declare the list of Cities
DECLARE @CityList varchar(8000)
SET @CityList = 'Milwaukee|Chicago|New York|Seattle|San Francisco'

--declare the delimeter between each City
DECLARE @Delimeter char(1)
SET @Delimeter = '|'

--Parse the string and insert each city into the @tblCity table
DECLARE @tblCity TABLE(City varchar(50))
DECLARE @City varchar(50)
DECLARE @StartPos int, @Length int
WHILE LEN(@CityList) > 0
  BEGIN
    SET @StartPos = CHARINDEX(@Delimeter, @CityList)
    IF @StartPos < 0 SET @StartPos = 0
    SET @Length = LEN(@CityList) - @StartPos - 1
    IF @Length < 0 SET @Length = 0
    IF @StartPos > 0
      BEGIN
        SET @City = SUBSTRING(@CityList, 1, @StartPos - 1)
        SET @CityList = SUBSTRING(@CityList, @StartPos + 1, LEN(@CityList) - @StartPos)
      END
    ELSE
      BEGIN
        SET @City = @CityList
        SET @CityList = ''
      END
    INSERT @tblCity (City) VALUES(@City)
END

--Show all Cities in the @tblCity table
SELECT * FROM @tblCity

database database, sql

This is the first time I ran into a situation where I needed to Join two tables (one with a varchar field and one with a varchar field that contains pipe delimited data) so I decided to post it for other who may not know you can do this. Below is a simplified example with solution of getting all rows of the first table whos values are contained in the pipe delimited field of the second table. This is really simple to do and you don't even have to use any User-Defined Functions or Cursors to do it. And I'm sure that not all of you who may read this know that you can use LIKE in a JOIN.

1 SELECT City
2 FROM Table1
3 JOIN Table2 ON Table2.CityList LIKE '%' + Table1.City + '%'

Table1
City varchar(30)

Table1 Sample Data
West Bend
Kewaskum
Hartford

Table2
CityList varchar(2000)

Table2 Sample Data
West Bend|Kewaskum
Kewaskum
Hartford

database database, sql,