8 people following this project (follow)

Overview

SqlSiphon is an ADO.NET wrapper that simplifies the process of connecting to a MS SQL Server database and executing Transact-SQL Stored Procedures on it. It's been running in one iteration or another in about 4 different production systems for the last 2 years.

The project's core ethos is pretty simple: keep everything to the most basic datatypes possible. Part of the motivation for the project is to abstract away the details of ADO.NET or any other particular data access API being used, to make for easier serialization over Web services. Mapping method calls from C# to SP calls in the database provides an extra layer of type safety as well, for protection against SQL Injection attacks.

As a very simple Object-Relational Mapping system, the database dictates the structure of the class objects and method calls. This is in contrast to heavier ORMs such as Hibernate. Databases have a way of sticking around longer than programs, so I felt it important that the DB be the master here.

Database support

Currently, it has been tested and used regularly with MS SQL Server 2005 and 2008.

Features

SqlSiphon.SqlClient.DataAccessLayer is an abstract class from which one inherits to create a mostly automatic data access layer. The class provides a number of protected methods that are intended are called from your DAL class with methods that match the signature of store procedures in your database. Each method determines the name of the stored procedure to execute and the name of the parameters to pass to it based on the name and parameters of the method from which they are called.
  • void Execute(params object[] parameters): a method for executing a stored procedure without returning any results.
  • T GetOne<T>(params object[] parameters): executes a stored procedure and returns the first row from the result set. If T is a primitive type, the first parameter passed to the method is interpreted as the column name for the field to retrieve. If T is an "entity class" (a class with default constructor and publicly accessible fields), it maps the columns of the table to the fields in the class.
  • System.Collections.Generic.List<T> GetList<T>(params object[] parameters): executes a stored procedure and returns all rows from the result set as objects in a generic List. If T is a primitive type, the first parameter passed to the method is interpreted as the column name for the field to retrieve. If T is an "entity class" (a class with default constructor and publicly accessible fields), it maps the columns of the table to the fields in the class.

Future Work

  • Expand database support
    • MS SQL Server 2000 is untested, but probably should work.
    • SQL Server Compact Edition could be supported with little effort.
    • MySQL
    • PostGresSQL
    • Oracle
    • Insert your favorite here
  • Auto-generators
    • A stored procedure for generating C#/VB.NET stubs for data access layers implementing the SqlSiphon system could easily be built with queries against the MS SQL INFORMATION_SCHEMA.
    • A stored procedure for generation C#/VB.NET entity classes for mapping to tables in the database
    • A query inspector for evaluating the possible result set of a stored procedure to select the right execution method

Last edited Oct 29 2009 at 4:46 PM by capn_midnight, version 8