Saturday, November 1, 2008

Using LINQ with SQL Server Compact Edition

I was trying to use MS Access with one of the applications I was writing for a non-profit organization but thought about the security of the database file. That being said, I opted to use SQL Server Compact Edition as it is just a small application that requires a database. This would enable the database to be embedded with the application and minimize the need to configure a database engine as a service, like how you would do with SQL Server Express. Since I wanted to use LINQ, I was quite surprised when the Design Surface threw an error saying that the data provider for SQL Server Compact was not supported. Good thing there's SqlMetal. I use SqlMetal to automate generation of DBML files for multiple SQL Server databases. Eventhough the Design Surface does not support SQL Server Compact, you can simply import the generated DBML file in your Visual Studio project. You can generate the DBML file for the SQL Server Compact database by running the SqlMetal command below:

SqlMetal /dbml:northwind.dbml northwind.sdf

Run the Windows SDK tool <drive>:\Program Files\Microsoft Visual Studio 9.0\VC\SqlMetal.exe against your generated sdf file. Make sure you navigate to the folder containing the SDF file, typically the Visual Studio project folder. You can also generate the .vb or .cs code depending on your preference

Check out this blog post from the SQL Server Compact Team on using LINQ with SQL Server Compact

Thursday, October 30, 2008

Another one-liner in Windows PowerShell

I did mention that I have been trying to convert my VBScript files to PowerShell scripts due to ease of coding. One of them happened to be a script that iterates a folder containing logs, reads the contents of the log files and appends them to a text file (I was actually trying to consolidate all the RADIUS logs for parsing and storage in a database). In VBScript, here's how it is written

Const ForReading = 1
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set folder = objFSO.GetFolder("d:\a")
Set outfile = objFSO.CreateTextFile("d:\testout.txt")

for each file in folder.Files
Set testfile = objFSO.OpenTextFile(file.path, ForReading)
Do While Not testfile.AtEndOfStream

'write to a single output file

Set objFSO = Nothing

Set folder = Nothing
Set outfile = Nothing

Here's how you can do it in PowerShell - with the cmdlet aliases

ls d:\a\ gc ac d:\testout.txt

This should be more than enough reason to learn PowerShell as a system administratior. More to come