.NET Database Access

Content:

  1. Introduction
  2. ADO.NET
    1. Database specific usage
    2. Generic usage
  3. DataSet
    1. Untyped
    2. Typed
    3. Better case for DataSet
    4. Binding
    5. Hybrid
  4. LINQ to SQL (ORM)
  5. EF (ORM)
    1. Code first
    2. Database first
    3. Model first
    4. EF and join
  6. NHibernate (ORM)
    1. HQL
    2. LINQ
    3. NHibernate and join
  7. Dapper (ORM)
    1. Plain
    2. Contrib
  8. Calling stored procedures
  9. CLR (C#) SP and UDF
  10. NoSQL

Introduction:

.NET comes with a large number of database access technologies.

Possible reasons:

Database access technology .NET versions Years Notes
ADO.NET specific database 1.0- 2002- Improved support for generic database usage in 2.0 (2005).
DataSet 1.0- 2002- Including typed DataSet
LINQ to SQL (ORM) 3.5- 2008- Deprecated when EF arrived in 4.0 (2010)
EF (ORM) 4.0- 2010- Early version part of version 3.5 SP1
NHibernate (ORM) (not part of .NET) 2005- Open source (heavily inspired by Hibernate a Java ORM)

All the examples below will use the following database table:

CREATE TABLE T1 (
    F1 INTEGER NOT NULL,
    F2 VARCHAR(50),
    PRIMARY KEY(F1)
);

Most of the examples will be implementing this super simple DAL:

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}
Namespace DAL
    Public Class T1
        Public Property F1() As Integer
        Public Property F2() As String
        Public Sub New(_f1 As Integer, _f2 As String)
            F1 = _f1
            F2 = _f2
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("[{0},{1}]", F1, F2)
        End Function
    End Class
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

The discussion about what a DAL is and why it is good is beyond the scope for this article. Read about DAL here.

ADO.NET:

ADO.NET is the foundation for all database access in .NET. All the higher level interfaces (DataSet, ORM) are build on top of ADO.NET.

ADO.NET is also the the only database access technology suitable for bulk updates.

All .NET developers should know ADO.NET.

Database specific usage

.NET comes with ADO.NET classes for several database:

For other databases one need to download and install an ADO.NET provider from the database vendor. Almost all database vendors provide such.

Example for MS SQLServer:

using System;
using System.Collections.Generic;

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.SQLServer
{
    using System.Data;
    using System.Data.SqlClient;
    public class SQLServerDAL : IDAL
    {
        private string constr;
        public SQLServerDAL(string constr)
        {
            this.constr = constr;
        }
        public T1 GetOneByPK(int f1)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con))
                {
                    cmd.Parameters.Add("@f1", SqlDbType.Int);
                    cmd.Parameters["@f1"].Value = f1;
                    using(SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if(rdr.Read())
                        {
                            return new T1((int)rdr["f1"], (string)rdr["f2"]);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
        }
        public List<T1> GetAll()
        {
            List<T1> res = new List<T1>();
            using(SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("SELECT f1,f2 FROM t1", con))
                {
                    using(SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            List<T1> res = new List<T1>();
            using(SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con))
                {
                    cmd.Parameters.Add("@f1_start", SqlDbType.Int);
                    cmd.Parameters.Add("@f1_end", SqlDbType.Int);
                    cmd.Parameters["@f1_start"].Value = f1_start;
                    cmd.Parameters["@f1_end"].Value = f1_end;
                    using(SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetContainingF2(string f2)
        {
            List<T1> res = new List<T1>();
            using(SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con))
                {
                    cmd.Parameters.Add("@f2", SqlDbType.VarChar, 50);
                    cmd.Parameters["@f2"].Value = "%" + f2 + "%";
                    using(SqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public void SaveChanges(T1 o)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("UPDATE t1 SET f2 = @f2 WHERE f1 = @f1", con))
                {
                    cmd.Parameters.Add("@f1", SqlDbType.Int);
                    cmd.Parameters.Add("@f2", SqlDbType.VarChar, 50);
                    cmd.Parameters["@f1"].Value = o.F1;
                    cmd.Parameters["@f2"].Value = o.F2;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void SaveNew(T1 o)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("INSERT INTO t1(f1,f2) VALUES(@f1,@f2)", con))
                {
                    cmd.Parameters.Add("@f1", SqlDbType.Int);
                    cmd.Parameters.Add("@f2", SqlDbType.VarChar, 50);
                    cmd.Parameters["@f1"].Value = o.F1;
                    cmd.Parameters["@f2"].Value = o.F2;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void Remove(int f1)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("DELETE FROM t1 WHERE f1 = @f1", con))
                {
                    cmd.Parameters.Add("@f1", SqlDbType.Int);
                    cmd.Parameters["@f1"].Value = f1;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(List<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Main(string[] args)
        {
            IDAL dal = new DAL.SQLServer.SQLServerDAL(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic

Imports System.Data
Imports System.Data.SqlClient

Namespace DAL
    Public Class T1
        Public Property F1() As Integer
        Public Property F2() As String
        Public Sub New(_f1 As Integer, _f2 As String)
            F1 = _f1
            F2 = _f2
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("[{0},{1}]", F1, F2)
        End Function
    End Class
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

Namespace DAL.SQLServer
    Public Class SQLServerDAL
        Implements IDAL
        Private constr As String
        Public Sub New(constr As String)
            Me.constr = constr
        End Sub
        Public Function GetOneByPK(f1 As Integer) As T1 Implements IDAL.GetOneByPK
            Using con As New SqlConnection(constr)
                con.Open()
                Using cmd As New SqlCommand("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                    cmd.Parameters.Add("@f1", SqlDbType.Int)
                    cmd.Parameters("@f1").Value = f1
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                        If rdr.Read() Then
                            Return New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String))
                        Else
                            Return Nothing
                        End If
                    End Using
                End Using
            End Using
        End Function
        Public Function GetAll() As List(Of T1) Implements IDAL.GetAll
            Dim res As New List(Of T1)()
            Using con As New SqlConnection(constr)
                con.Open()
                Using cmd As New SqlCommand("SELECT f1,f2 FROM t1", con)
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1) Implements IDAL.GetRangeOfPK
            Dim res As New List(Of T1)()
            Using con As New SqlConnection(constr)
                con.Open()
                Using cmd As New SqlCommand("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con)
                    cmd.Parameters.Add("@f1_start", SqlDbType.Int)
                    cmd.Parameters.Add("@f1_end", SqlDbType.Int)
                    cmd.Parameters("@f1_start").Value = f1_start
                    cmd.Parameters("@f1_end").Value = f1_end
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetContainingF2(f2 As String) As List(Of T1) Implements IDAL.GetContainingF2
            Dim res As New List(Of T1)()
            Using con As New SqlConnection(constr)
                con.Open()
                Using cmd As New SqlCommand("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con)
                    cmd.Parameters.Add("@f2", SqlDbType.VarChar, 50)
                    cmd.Parameters("@f2").Value = "%" & f2 & "%"
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Sub SaveChanges(o As T1) Implements IDAL.SaveChanges
            Using con As New SqlConnection(constr)
                con.Open()
                Using cmd As New SqlCommand("UPDATE t1 SET f2 = @f2 WHERE f1 = @f1", con)
                    cmd.Parameters.Add("@f1", SqlDbType.Int)
                    cmd.Parameters.Add("@f2", SqlDbType.VarChar, 50)
                    cmd.Parameters("@f1").Value = o.F1
                    cmd.Parameters("@f2").Value = o.F2
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub SaveNew(o As T1) Implements IDAL.SaveNew
            Using con As New SqlConnection(constr)
                con.Open()
                Using cmd As New SqlCommand("INSERT INTO t1(f1,f2) VALUES(@f1,@f2)", con)
                    cmd.Parameters.Add("@f1", SqlDbType.Int)
                    cmd.Parameters.Add("@f2", SqlDbType.VarChar, 50)
                    cmd.Parameters("@f1").Value = o.F1
                    cmd.Parameters("@f2").Value = o.F2
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub Remove(f1 As Integer) Implements IDAL.Remove
            Using con As New SqlConnection(constr)
                con.Open()
                Using cmd As New SqlCommand("DELETE FROM t1 WHERE f1 = @f1", con)
                    cmd.Parameters.Add("@f1", SqlDbType.Int)
                    cmd.Parameters("@f1").Value = f1
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            Dim o As DAL.T1 = dal.GetOneByPK(f1)
            o.F2 = f2
            dal.SaveChanges(o)
        End Sub
        Public Shared Sub Print(lst As List(Of DAL.T1))
            Console.Write("{")
            For i As Integer = 0 To lst.Count - 1
                If i > 0 Then
                    Console.Write(",")
                End If
                Console.Write(lst(i))
            Next
            Console.WriteLine("}")
        End Sub
        Public Shared Sub Main(args As String())
            Dim dal As DAL.IDAL = New DAL.SQLServer.SQLServerDAL("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Console.WriteLine(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.SaveNew(New DAL.T1(999, "XXX"))
            Print(dal.GetAll())
            dal.Remove(999)
            Print(dal.GetAll())
            Console.ReadKey()
        End Sub
    End Class
End Namespace

Example for MySQL:

using System;
using System.Collections.Generic;

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.MySQL
{
    using MySql.Data.MySqlClient;
    public class MySQLDAL : IDAL
    {
        private string constr;
        public MySQLDAL(string constr)
        {
            this.constr = constr;
        }
        public T1 GetOneByPK(int f1)
        {
            using(MySqlConnection con = new MySqlConnection(constr))
            {
                con.Open();
                using(MySqlCommand cmd = new MySqlCommand("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con))
                {
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32);
                    cmd.Parameters["@f1"].Value = f1;
                    using(MySqlDataReader rdr = cmd.ExecuteReader())
                    {
                        if(rdr.Read())
                        {
                            return new T1((int)rdr["f1"], (string)rdr["f2"]);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
        }
        public List<T1> GetAll()
        {
            List<T1> res = new List<T1>();
            using(MySqlConnection con = new MySqlConnection(constr))
            {
                con.Open();
                using(MySqlCommand cmd = new MySqlCommand("SELECT f1,f2 FROM t1", con))
                {
                    using(MySqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            List<T1> res = new List<T1>();
            using(MySqlConnection con = new MySqlConnection(constr))
            {
                con.Open();
                using(MySqlCommand cmd = new MySqlCommand("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con))
                {
                    cmd.Parameters.Add("@f1_start", MySqlDbType.Int32);
                    cmd.Parameters.Add("@f1_end", MySqlDbType.Int32);
                    cmd.Parameters["@f1_start"].Value = f1_start;
                    cmd.Parameters["@f1_end"].Value = f1_end;
                    using(MySqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetContainingF2(string f2)
        {
            List<T1> res = new List<T1>();
            using(MySqlConnection con = new MySqlConnection(constr))
            {
                con.Open();
                using(MySqlCommand cmd = new MySqlCommand("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con))
                {
                    cmd.Parameters.Add("@f2", MySqlDbType.VarChar, 50);
                    cmd.Parameters["@f2"].Value = "%" + f2 + "%";
                    using(MySqlDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public void SaveChanges(T1 o)
        {
            using(MySqlConnection con = new MySqlConnection(constr))
            {
                con.Open();
                using(MySqlCommand cmd = new MySqlCommand("UPDATE t1 SET f2 = @f2 WHERE f1 = @f1", con))
                {
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32);
                    cmd.Parameters.Add("@f2", MySqlDbType.VarChar, 50);
                    cmd.Parameters["@f1"].Value = o.F1;
                    cmd.Parameters["@f2"].Value = o.F2;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void SaveNew(T1 o)
        {
            using(MySqlConnection con = new MySqlConnection(constr))
            {
                con.Open();
                using(MySqlCommand cmd = new MySqlCommand("INSERT INTO t1(f1,f2) VALUES(@f1,@f2)", con))
                {
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32);
                    cmd.Parameters.Add("@f2", MySqlDbType.VarChar, 50);
                    cmd.Parameters["@f1"].Value = o.F1;
                    cmd.Parameters["@f2"].Value = o.F2;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void Remove(int f1)
        {
            using(MySqlConnection con = new MySqlConnection(constr))
            {
                con.Open();
                using(MySqlCommand cmd = new MySqlCommand("DELETE FROM t1 WHERE f1 = @f1", con))
                {
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32);
                    cmd.Parameters["@f1"].Value = f1;
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(List<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Main(string[] args)
        {
            IDAL dal = new DAL.MySQL.MySQLDAL("Server=localhost;Database=Test;User Id=root;Password=");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic

Imports MySql.Data.MySqlClient

Namespace DAL
    Public Class T1
        Public Property F1() As Integer
        Public Property F2() As String
        Public Sub New(_f1 As Integer, _f2 As String)
            F1 = _f1
            F2 = _f2
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("[{0},{1}]", F1, F2)
        End Function
    End Class
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

Namespace DAL.MySQL
    Public Class MySQLDAL
        Implements IDAL
        Private constr As String
        Public Sub New(constr As String)
            Me.constr = constr
        End Sub
        Public Function GetOneByPK(f1 As Integer) As T1 Implements IDAL.GetOneByPK
            Using con As New MySqlConnection(constr)
                con.Open()
                Using cmd As New MySqlCommand("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32)
                    cmd.Parameters("@f1").Value = f1
                    Using rdr As MySqlDataReader = cmd.ExecuteReader()
                        If rdr.Read() Then
                            Return New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String))
                        Else
                            Return Nothing
                        End If
                    End Using
                End Using
            End Using
        End Function
        Public Function GetAll() As List(Of T1) Implements IDAL.GetAll
            Dim res As New List(Of T1)()
            Using con As New MySqlConnection(constr)
                con.Open()
                Using cmd As New MySqlCommand("SELECT f1,f2 FROM t1", con)
                    Using rdr As MySqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1) Implements IDAL.GetRangeOfPK
            Dim res As New List(Of T1)()
            Using con As New MySqlConnection(constr)
                con.Open()
                Using cmd As New MySqlCommand("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con)
                    cmd.Parameters.Add("@f1_start", MySqlDbType.Int32)
                    cmd.Parameters.Add("@f1_end", MySqlDbType.Int32)
                    cmd.Parameters("@f1_start").Value = f1_start
                    cmd.Parameters("@f1_end").Value = f1_end
                    Using rdr As MySqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetContainingF2(f2 As String) As List(Of T1) Implements IDAL.GetContainingF2
            Dim res As New List(Of T1)()
            Using con As New MySqlConnection(constr)
                con.Open()
                Using cmd As New MySqlCommand("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con)
                    cmd.Parameters.Add("@f2", MySqlDbType.VarChar, 50)
                    cmd.Parameters("@f2").Value = "%" & f2 & "%"
                    Using rdr As MySqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Sub SaveChanges(o As T1) Implements IDAL.SaveChanges
            Using con As New MySqlConnection(constr)
                con.Open()
                Using cmd As New MySqlCommand("UPDATE t1 SET f2 = @f2 WHERE f1 = @f1", con)
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32)
                    cmd.Parameters.Add("@f2", MySqlDbType.VarChar, 50)
                    cmd.Parameters("@f1").Value = o.F1
                    cmd.Parameters("@f2").Value = o.F2
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub SaveNew(o As T1) Implements IDAL.SaveNew
            Using con As New MySqlConnection(constr)
                con.Open()
                Using cmd As New MySqlCommand("INSERT INTO t1(f1,f2) VALUES(@f1,@f2)", con)
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32)
                    cmd.Parameters.Add("@f2", MySqlDbType.VarChar, 50)
                    cmd.Parameters("@f1").Value = o.F1
                    cmd.Parameters("@f2").Value = o.F2
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub Remove(f1 As Integer) Implements IDAL.Remove
            Using con As New MySqlConnection(constr)
                con.Open()
                Using cmd As New MySqlCommand("DELETE FROM t1 WHERE f1 = @f1", con)
                    cmd.Parameters.Add("@f1", MySqlDbType.Int32)
                    cmd.Parameters("@f1").Value = f1
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            Dim o As DAL.T1 = dal.GetOneByPK(f1)
            o.F2 = f2
            dal.SaveChanges(o)
        End Sub
        Public Shared Sub Print(lst As List(Of DAL.T1))
            Console.Write("{")
            For i As Integer = 0 To lst.Count - 1
                If i > 0 Then
                    Console.Write(",")
                End If
                Console.Write(lst(i))
            Next
            Console.WriteLine("}")
        End Sub
        Public Shared Sub Main(args As String())
            Dim dal As DAL.IDAL = New DAL.MySQL.MySQLDAL("Server=localhost;Database=Test;User Id=root;Password=")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Console.WriteLine(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.SaveNew(New DAL.T1(999, "XXX"))
            Print(dal.GetAll())
            dal.Remove(999)
            Print(dal.GetAll())
            Console.ReadKey()
        End Sub
    End Class
End Namespace

We note a few things:

And this is really how ADO.NET works. The exact same classes and methods. Only thing changing is the prefix in the class names. And no surprise:

Note that by tradition then ADO.NET connection classes comes with builtin connection pool that is enabled by default.

In my opinion it is unfortunatetly that ADO.NET makes it so easy to write database specific code, because as a result that is how most .NET developers start.

Generic usage

It is possible to write generic (database independent) ADO.NET code by using just interfaces.

Since .NET 1.0 it has been possible to use interfaces for everything except creating the basic connection:

using System;
using System.Collections.Generic;

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.Generic
{
    using System.Data;
    using System.Data.SqlClient;
    using MySql.Data.MySqlClient;
    public enum DatabaseType { SQL_SERVER, MYSQL }
    public class GenericDAL : IDAL
    {
        private DatabaseType dbtyp;
        private string constr;
        private IDbConnection GetConnection()
        {
            switch(dbtyp)
            {
                case DatabaseType.SQL_SERVER:
                    return new SqlConnection(constr);
                case DatabaseType.MYSQL:
                    return new MySqlConnection(constr);
                default:
                    throw new Exception("Unknown database type");
            }
        }
        public GenericDAL(DatabaseType dbtyp, string constr)
        {
            this.dbtyp = dbtyp;
            this.constr = constr;
        }
        public T1 GetOneByPK(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 = @f1";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = f1;
                    cmd.Parameters.Add(pf1);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        if(rdr.Read())
                        {
                            return new T1((int)rdr["f1"], (string)rdr["f2"]);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
        }
        public List<T1> GetAll()
        {
            List<T1> res = new List<T1>();
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1";
                    cmd.Connection = con;
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            List<T1> res = new List<T1>();
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end";
                    cmd.Connection = con;
                    IDbDataParameter pf1_start = cmd.CreateParameter();
                    pf1_start.ParameterName = "@f1_start";
                    pf1_start.DbType = DbType.Int32;
                    pf1_start.Value = f1_start;
                    cmd.Parameters.Add(pf1_start);
                    IDbDataParameter pf1_end = cmd.CreateParameter();
                    pf1_end.ParameterName = "@f1_end";
                    pf1_end.DbType = DbType.Int32;
                    pf1_end.Value = f1_end;
                    cmd.Parameters.Add(pf1_end);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetContainingF2(string f2)
        {
            List<T1> res = new List<T1>();
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2";
                    cmd.Connection = con;

                    IDbDataParameter pf2 = cmd.CreateParameter();
                    pf2.ParameterName = "@f2";
                    pf2.DbType = DbType.String;
                    pf2.Value = "%" + f2 + "%";
                    cmd.Parameters.Add(pf2);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public void SaveChanges(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "UPDATE t1 SET f2 = @f2 WHERE f1 = @f1";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = o.F1;
                    cmd.Parameters.Add(pf1);
                    IDbDataParameter pf2 = cmd.CreateParameter();
                    pf2.ParameterName = "@f2";
                    pf2.DbType = DbType.String;
                    pf2.Value = o.F2;
                    cmd.Parameters.Add(pf2);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void SaveNew(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO t1(f1,f2) VALUES(@f1,@f2)";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = o.F1;
                    cmd.Parameters.Add(pf1);
                    IDbDataParameter pf2 = cmd.CreateParameter();
                    pf2.ParameterName = "@f2";
                    pf2.DbType = DbType.String;
                    pf2.Value = o.F2;
                    cmd.Parameters.Add(pf2);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void Remove(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM t1 WHERE f1 = @f1";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = f1;
                    cmd.Parameters.Add(pf1);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

namespace Demo
{
    using DAL;
    using DAL.Generic;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(List<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Test(DatabaseType dbtyp, string constr)
        {
            Console.WriteLine(dbtyp + ":");
            IDAL dal = new DAL.Generic.GenericDAL(dbtyp, constr);
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
        }
        public static void Main(string[] args)
        {
            Test(DatabaseType.SQL_SERVER, @"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Test(DatabaseType.MYSQL, "Server=localhost;Database=Test;User Id=root;Password=");
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic

Imports System.Data
Imports System.Data.SqlClient

Imports MySql.Data.MySqlClient

Namespace DAL
    Public Class T1
        Public Property F1() As Integer
        Public Property F2() As String
        Public Sub New(_f1 As Integer, _f2 As String)
            F1 = _f1
            F2 = _f2
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("[{0},{1}]", F1, F2)
        End Function
    End Class
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

Namespace DAL.Generic
    Public Enum DatabaseType
        SQL_SERVER
        MYSQL
    End Enum
    Public Class GenericDAL
        Implements IDAL
        Private dbtyp As DatabaseType
        Private constr As String
        Private Function GetConnection() As IDbConnection
            Select Case dbtyp
                Case DatabaseType.SQL_SERVER
                    Return New SqlConnection(constr)
                Case DatabaseType.MYSQL
                    Return New MySqlConnection(constr)
                Case Else
                    Throw New Exception("Unknown database type")
            End Select
        End Function
        Public Sub New(dbtyp As DatabaseType, constr As String)
            Me.dbtyp = dbtyp
            Me.constr = constr
        End Sub
        Public Function GetOneByPK(f1 As Integer) As T1 Implements IDAL.GetOneByPK
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 = @f1"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = f1
                    cmd.Parameters.Add(pf1)
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        If rdr.Read() Then
                            Return New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String))
                        Else
                            Return Nothing
                        End If
                    End Using
                End Using
            End Using
        End Function
        Public Function GetAll() As List(Of T1) Implements IDAL.GetAll
            Dim res As New List(Of T1)()
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1"
                    cmd.Connection = con
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1) Implements IDAL.GetRangeOfPK
            Dim res As New List(Of T1)()
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end"
                    cmd.Connection = con
                    Dim pf1_start As IDbDataParameter = cmd.CreateParameter()
                    pf1_start.ParameterName = "@f1_start"
                    pf1_start.DbType = DbType.Int32
                    pf1_start.Value = f1_start
                    cmd.Parameters.Add(pf1_start)
                    Dim pf1_end As IDbDataParameter = cmd.CreateParameter()
                    pf1_end.ParameterName = "@f1_end"
                    pf1_end.DbType = DbType.Int32
                    pf1_end.Value = f1_end
                    cmd.Parameters.Add(pf1_end)
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetContainingF2(f2 As String) As List(Of T1) Implements IDAL.GetContainingF2
            Dim res As New List(Of T1)()
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2"
                    cmd.Connection = con
                    Dim pf2 As IDbDataParameter = cmd.CreateParameter()
                    pf2.ParameterName = "@f2"
                    pf2.DbType = DbType.[String]
                    pf2.Value = "%" & f2 & "%"
                    cmd.Parameters.Add(pf2)
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Sub SaveChanges(o As T1) Implements IDAL.SaveChanges
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "UPDATE t1 SET f2 = @f2 WHERE f1 = @f1"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = o.F1
                    cmd.Parameters.Add(pf1)
                    Dim pf2 As IDbDataParameter = cmd.CreateParameter()
                    pf2.ParameterName = "@f2"
                    pf2.DbType = DbType.[String]
                    pf2.Value = o.F2
                    cmd.Parameters.Add(pf2)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub SaveNew(o As T1) Implements IDAL.SaveNew
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "INSERT INTO t1(f1,f2) VALUES(@f1,@f2)"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = o.F1
                    cmd.Parameters.Add(pf1)
                    Dim pf2 As IDbDataParameter = cmd.CreateParameter()
                    pf2.ParameterName = "@f2"
                    pf2.DbType = DbType.[String]
                    pf2.Value = o.F2
                    cmd.Parameters.Add(pf2)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub Remove(f1 As Integer) Implements IDAL.Remove
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "DELETE FROM t1 WHERE f1 = @f1"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = f1
                    cmd.Parameters.Add(pf1)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            Dim o As DAL.T1 = dal.GetOneByPK(f1)
            o.F2 = f2
            dal.SaveChanges(o)
        End Sub
        Public Shared Sub Print(lst As List(Of DAL.T1))
            Console.Write("{")
            For i As Integer = 0 To lst.Count - 1
                If i > 0 Then
                    Console.Write(",")
                End If
                Console.Write(lst(i))
            Next
            Console.WriteLine("}")
        End Sub
        Public Shared Sub Test(dbtyp As DAL.Generic.DatabaseType, constr As String)
            Console.WriteLine(dbtyp.ToString() & ":")
            Dim dal As DAL.IDAL = New DAL.Generic.GenericDAL(dbtyp, constr)
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Console.WriteLine(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.SaveNew(New DAL.T1(999, "XXX"))
            Print(dal.GetAll())
            dal.Remove(999)
            Print(dal.GetAll())
        End Sub
        Public Shared Sub Main(args As String())
            Test(DAL.Generic.DatabaseType.SQL_SERVER, "Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
            Test(DAL.Generic.DatabaseType.MYSQL, "Server=localhost;Database=Test;User Id=root;Password=")
            Console.ReadKey()
        End Sub
    End Class
End Namespace

And since .NET 2.0 there has been a way to also create the database connections based on configuration without reffering to any database specific classes:

using System;
using System.Collections.Generic;

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.Generic
{
    using System.Data;
    using System.Data.Common;
    public class GenericDAL : IDAL
    {
        private string prov;
        private string constr;
        private IDbConnection GetConnection()
        {
            IDbConnection con = DbProviderFactories.GetFactory(prov).CreateConnection();
            con.ConnectionString = constr;
            return con;
        }
        public GenericDAL(string prov, string constr)
        {
            this.prov = prov;
            this.constr = constr;
        }
        public T1 GetOneByPK(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 = @f1";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = f1;
                    cmd.Parameters.Add(pf1);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        if(rdr.Read())
                        {
                            return new T1((int)rdr["f1"], (string)rdr["f2"]);
                        }
                        else
                        {
                            return null;
                        }
                    }
                }
            }
        }
        public List<T1> GetAll()
        {
            List<T1> res = new List<T1>();
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1";
                    cmd.Connection = con;
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            List<T1> res = new List<T1>();
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end";
                    cmd.Connection = con;
                    IDbDataParameter pf1_start = cmd.CreateParameter();
                    pf1_start.ParameterName = "@f1_start";
                    pf1_start.DbType = DbType.Int32;
                    pf1_start.Value = f1_start;
                    cmd.Parameters.Add(pf1_start);
                    IDbDataParameter pf1_end = cmd.CreateParameter();
                    pf1_end.ParameterName = "@f1_end";
                    pf1_end.DbType = DbType.Int32;
                    pf1_end.Value = f1_end;
                    cmd.Parameters.Add(pf1_end);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public List<T1> GetContainingF2(string f2)
        {
            List<T1> res = new List<T1>();
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2";
                    cmd.Connection = con;

                    IDbDataParameter pf2 = cmd.CreateParameter();
                    pf2.ParameterName = "@f2";
                    pf2.DbType = DbType.String;
                    pf2.Value = "%" + f2 + "%";
                    cmd.Parameters.Add(pf2);
                    using(IDataReader rdr = cmd.ExecuteReader())
                    {
                        while(rdr.Read())
                        {
                            res.Add(new T1((int)rdr["f1"], (string)rdr["f2"]));
                        }
                    }
                }
            }
            return res;
        }
        public void SaveChanges(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "UPDATE t1 SET f2 = @f2 WHERE f1 = @f1";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = o.F1;
                    cmd.Parameters.Add(pf1);
                    IDbDataParameter pf2 = cmd.CreateParameter();
                    pf2.ParameterName = "@f2";
                    pf2.DbType = DbType.String;
                    pf2.Value = o.F2;
                    cmd.Parameters.Add(pf2);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void SaveNew(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "INSERT INTO t1(f1,f2) VALUES(@f1,@f2)";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = o.F1;
                    cmd.Parameters.Add(pf1);
                    IDbDataParameter pf2 = cmd.CreateParameter();
                    pf2.ParameterName = "@f2";
                    pf2.DbType = DbType.String;
                    pf2.Value = o.F2;
                    cmd.Parameters.Add(pf2);
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void Remove(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Open();
                using(IDbCommand cmd = con.CreateCommand())
                {
                    cmd.CommandText = "DELETE FROM t1 WHERE f1 = @f1";
                    cmd.Connection = con;
                    IDbDataParameter pf1 = cmd.CreateParameter();
                    pf1.ParameterName = "@f1";
                    pf1.DbType = DbType.Int32;
                    pf1.Value = f1;
                    cmd.Parameters.Add(pf1);
                    cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

namespace Demo
{
    using DAL;
    using DAL.Generic;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(List<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Test(string prov, string constr)
        {
            Console.WriteLine(prov + ":");
            IDAL dal = new DAL.Generic.GenericDAL(prov, constr);
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
        }
        public static void Main(string[] args)
        {
            Test("System.Data.SqlClient", @"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Test("MySql.Data.MySqlClient", "Server=localhost;Database=Test;User Id=root;Password=");
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic

Imports System.Data
Imports System.Data.Common

Namespace DAL
    Public Class T1
        Public Property F1() As Integer
        Public Property F2() As String
        Public Sub New(_f1 As Integer, _f2 As String)
            F1 = _f1
            F2 = _f2
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("[{0},{1}]", F1, F2)
        End Function
    End Class
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

Namespace DAL.Generic
    Public Class GenericDAL
        Implements IDAL
        Private prov As String
        Private constr As String
        Private Function GetConnection() As IDbConnection
            Dim con As IDbConnection = DbProviderFactories.GetFactory(prov).CreateConnection()
            con.ConnectionString = constr
            Return con
        End Function
        Public Sub New(prov As String, constr As String)
            Me.prov = prov
            Me.constr = constr
        End Sub
        Public Function GetOneByPK(f1 As Integer) As T1 Implements IDAL.GetOneByPK
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 = @f1"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = f1
                    cmd.Parameters.Add(pf1)
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        If rdr.Read() Then
                            Return New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String))
                        Else
                            Return Nothing
                        End If
                    End Using
                End Using
            End Using
        End Function
        Public Function GetAll() As List(Of T1) Implements IDAL.GetAll
            Dim res As New List(Of T1)()
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1"
                    cmd.Connection = con
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1) Implements IDAL.GetRangeOfPK
            Dim res As New List(Of T1)()
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end"
                    cmd.Connection = con
                    Dim pf1_start As IDbDataParameter = cmd.CreateParameter()
                    pf1_start.ParameterName = "@f1_start"
                    pf1_start.DbType = DbType.Int32
                    pf1_start.Value = f1_start
                    cmd.Parameters.Add(pf1_start)
                    Dim pf1_end As IDbDataParameter = cmd.CreateParameter()
                    pf1_end.ParameterName = "@f1_end"
                    pf1_end.DbType = DbType.Int32
                    pf1_end.Value = f1_end
                    cmd.Parameters.Add(pf1_end)
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Function GetContainingF2(f2 As String) As List(Of T1) Implements IDAL.GetContainingF2
            Dim res As New List(Of T1)()
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2"
                    cmd.Connection = con
                    Dim pf2 As IDbDataParameter = cmd.CreateParameter()
                    pf2.ParameterName = "@f2"
                    pf2.DbType = DbType.[String]
                    pf2.Value = "%" & f2 & "%"
                    cmd.Parameters.Add(pf2)
                    Using rdr As IDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            res.Add(New T1(CInt(rdr("f1")), DirectCast(rdr("f2"), String)))
                        End While
                    End Using
                End Using
            End Using
            Return res
        End Function
        Public Sub SaveChanges(o As T1) Implements IDAL.SaveChanges
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "UPDATE t1 SET f2 = @f2 WHERE f1 = @f1"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = o.F1
                    cmd.Parameters.Add(pf1)
                    Dim pf2 As IDbDataParameter = cmd.CreateParameter()
                    pf2.ParameterName = "@f2"
                    pf2.DbType = DbType.[String]
                    pf2.Value = o.F2
                    cmd.Parameters.Add(pf2)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub SaveNew(o As T1) Implements IDAL.SaveNew
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "INSERT INTO t1(f1,f2) VALUES(@f1,@f2)"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = o.F1
                    cmd.Parameters.Add(pf1)
                    Dim pf2 As IDbDataParameter = cmd.CreateParameter()
                    pf2.ParameterName = "@f2"
                    pf2.DbType = DbType.[String]
                    pf2.Value = o.F2
                    cmd.Parameters.Add(pf2)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
        Public Sub Remove(f1 As Integer) Implements IDAL.Remove
            Using con As IDbConnection = GetConnection()
                con.Open()
                Using cmd As IDbCommand = con.CreateCommand()
                    cmd.CommandText = "DELETE FROM t1 WHERE f1 = @f1"
                    cmd.Connection = con
                    Dim pf1 As IDbDataParameter = cmd.CreateParameter()
                    pf1.ParameterName = "@f1"
                    pf1.DbType = DbType.Int32
                    pf1.Value = f1
                    cmd.Parameters.Add(pf1)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            Dim o As DAL.T1 = dal.GetOneByPK(f1)
            o.F2 = f2
            dal.SaveChanges(o)
        End Sub
        Public Shared Sub Print(lst As List(Of DAL.T1))
            Console.Write("{")
            For i As Integer = 0 To lst.Count - 1
                If i > 0 Then
                    Console.Write(",")
                End If
                Console.Write(lst(i))
            Next
            Console.WriteLine("}")
        End Sub
        Public Shared Sub Test(prov As String, constr As String)
            Console.WriteLine(prov & ":")
            Dim dal As DAL.IDAL = New DAL.Generic.GenericDAL(prov, constr)
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Console.WriteLine(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.SaveNew(New DAL.T1(999, "XXX"))
            Print(dal.GetAll())
            dal.Remove(999)
            Print(dal.GetAll())
        End Sub
        Public Shared Sub Main(args As String())
            Test("System.Data.SqlClient", "Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
            Test("MySql.Data.MySqlClient", "Server=localhost;Database=Test;User Id=root;Password=")
            Console.ReadKey()
        End Sub
    End Class
End Namespace

It is still the same ADO.NET pattern. Just a few changes:

I find it difficult to see any reason why to ever write database specific code in ADO.NET. But the fcat is that the majority of ADO.NET is written database specific. My guess is that it is because what develoeprs start with and they just stick to it.

DataSet:

DataSet is a higher level construct that makes it possible to write less trivial code.

It also has its downsides but more about that later.

It is possible to use DataSet with any database also in a generic way. But in practice DataSet is mostly used with MS SQLServer via ADO.NET SqlClient and MS Access via ADO.NET OLE DB support. So I will just use SqlClient in the examples.

Untyped

This is a standard DataSet.

using System;
using System.Collections.Generic;

using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public interface IDAL
    {
        DataSet GetOneByPK(int f1);
        DataSet GetAll();
        DataSet GetRangeOfPK(int f1_start, int f1_end);
        DataSet GetContainingF2(string f2);
        void Update(int f1, string f2);
        void Insert(int f1, string f2);
        void Delete(int f1);
    }
    public class DataSetDAL : IDAL
    {
        private string constr;
        public DataSetDAL(string constr)
        {
            this.constr = constr;
        }
        public DataSet GetOneByPK(int f1)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con);
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1"].Value = f1;
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public DataSet GetAll()
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1", con);
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public DataSet GetRangeOfPK(int f1_start, int f1_end)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con);
                da.SelectCommand.Parameters.Add("@f1_start", SqlDbType.Int);
                da.SelectCommand.Parameters.Add("@f1_end", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1_start"].Value = f1_start;
                da.SelectCommand.Parameters["@f1_end"].Value = f1_end;
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public DataSet GetContainingF2(string f2)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con);
                da.SelectCommand.Parameters.Add("@f2", SqlDbType.VarChar, 50);
                da.SelectCommand.Parameters["@f2"].Value = "%" + f2 + "%";
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public void Update(int f1, string f2)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con);
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1"].Value = f1;
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                new SqlCommandBuilder(da);
                ds.Tables["t1"].Rows[0]["f2"] = f2;
                da.Update(ds, "t1");
            }
        }
        public void Insert(int f1, string f2)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1", con);
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                new SqlCommandBuilder(da);
                DataRow dr = ds.Tables["t1"].NewRow();
                dr["f1"] = f1;
                dr["f2"] = f2;
                ds.Tables["t1"].Rows.Add(dr);
                da.Update(ds, "t1");
            }
        }
        public void Delete(int f1)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con);
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1"].Value = f1;
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                new SqlCommandBuilder(da);
                ds.Tables["t1"].Rows[0].Delete();
                da.Update(ds, "t1");
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            dal.Update(f1, f2);
        }
        public static void Print(DataSet ds)
        {
            if(ds.Tables["t1"].Rows.Count > 1) Console.Write("{");
            bool first = true;
            foreach(DataRow dr in ds.Tables["t1"].Rows)
            {
                if(first)
                {
                    first = false;
                }
                else
                {
                    Console.Write(",");
                }
                Console.Write("[" + (int)dr["f1"] + "," + (string)dr["f2"] + "]");
            }
            if(ds.Tables["t1"].Rows.Count > 1) Console.Write("}");
            Console.WriteLine();
        }
        public static void Main(string[] args)
        {
            IDAL dal = new DAL.DataSetDAL(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Print(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Print(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Print(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.Insert(999, "XXX");
            Print(dal.GetAll());
            dal.Delete(999);
            Print(dal.GetAll());
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient

Namespace DAL
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As DataSet
        Function GetAll() As DataSet
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As DataSet
        Function GetContainingF2(f2 As String) As DataSet
        Sub Update(f1 As Integer, f2 As String)
        Sub Insert(f1 As Integer, f2 As String)
        Sub Delete(f1 As Integer)
    End Interface
    Public Class DataSetDAL
        Implements IDAL
        Private constr As String
        Public Sub New(constr As String)
            Me.constr = constr
        End Sub
        Public Function GetOneByPK(f1 As Integer) As DataSet Implements IDAL.GetOneByPK
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1").Value = f1
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Function GetAll() As DataSet Implements IDAL.GetAll
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1", con)
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As DataSet Implements IDAL.GetRangeOfPK
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con)
                da.SelectCommand.Parameters.Add("@f1_start", SqlDbType.Int)
                da.SelectCommand.Parameters.Add("@f1_end", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1_start").Value = f1_start
                da.SelectCommand.Parameters("@f1_end").Value = f1_end
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Function GetContainingF2(f2 As String) As DataSet Implements IDAL.GetContainingF2
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con)
                da.SelectCommand.Parameters.Add("@f2", SqlDbType.VarChar, 50)
                da.SelectCommand.Parameters("@f2").Value = "%" & f2 & "%"
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Sub Update(f1 As Integer, f2 As String) Implements IDAL.Update
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1").Value = f1
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
                ds.Tables("t1").Rows(0)("f2") = f2
                da.Update(ds, "t1")
            End Using
        End Sub
        Public Sub Insert(f1 As Integer, f2 As String) Implements IDAL.Insert
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1", con)
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
                Dim dr As DataRow = ds.Tables("t1").NewRow()
                dr("f1") = f1
                dr("f2") = f2
                ds.Tables("t1").Rows.Add(dr)
                da.Update(ds, "t1")
            End Using
        End Sub
        Public Sub Delete(f1 As Integer) Implements IDAL.Delete
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1").Value = f1
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
                ds.Tables("t1").Rows(0).Delete()
                da.Update(ds, "t1")
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            dal.Update(f1, f2)
        End Sub
        Public Shared Sub Print(ds As DataSet)
            If ds.Tables("t1").Rows.Count > 1 Then
                Console.Write("{")
            End If
            Dim first As Boolean = True
            For Each dr As DataRow In ds.Tables("t1").Rows
                If first Then
                    first = False
                Else
                    Console.Write(",")
                End If
                Console.Write("[" & CInt(dr("f1")) & "," & DirectCast(dr("f2"), String) & "]")
            Next
            If ds.Tables("t1").Rows.Count > 1 Then
                Console.Write("}")
            End If
            Console.WriteLine()
        End Sub
        Public Shared Sub Main(args As String())
            Dim dal As DAL.IDAL = New DAL.DataSetDAL("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
            Print(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Print(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Print(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.Insert(999, "XXX")
            Print(dal.GetAll())
            dal.Delete(999)
            Print(dal.GetAll())
            Console.ReadKey()
        End Sub
    End Class
End Namespace

Functionality:

Typed

A DataSet is fundamentally an array of DataTable each being an array of DataRow each being an array of object. Not type safe at all.

To improve on the type safety the concept of typed DataSet has been created.

A typed DataSet is a class that extends DataSet and has properties to access tables and fields.

Visual Studio can generate such classes via a wizard.

using System;
using System.Collections.Generic;

using System.Data;
using System.Data.SqlClient;

using DataSetGen; // namespace for generated code with MyDataSet, MyDataSet.T1DataTable and MyDataSet.T1Row classes

namespace DAL
{
    public interface IDAL
    {
        MyDataSet GetOneByPK(int f1);
        MyDataSet GetAll();
        MyDataSet GetRangeOfPK(int f1_start, int f1_end);
        MyDataSet GetContainingF2(string f2);
        void Update(int f1, string f2);
        void Insert(int f1, string f2);
        void Delete(int f1);
    }
    public class DataSetDAL : IDAL
    {
        private string constr;
        public DataSetDAL(string constr)
        {
            this.constr = constr;
        }
        public MyDataSet GetOneByPK(int f1)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con);
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1"].Value = f1;
                MyDataSet ds = new MyDataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public MyDataSet GetAll()
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1", con);
                MyDataSet ds = new MyDataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public MyDataSet GetRangeOfPK(int f1_start, int f1_end)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con);
                da.SelectCommand.Parameters.Add("@f1_start", SqlDbType.Int);
                da.SelectCommand.Parameters.Add("@f1_end", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1_start"].Value = f1_start;
                da.SelectCommand.Parameters["@f1_end"].Value = f1_end;
                MyDataSet ds = new MyDataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public MyDataSet GetContainingF2(string f2)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con);
                da.SelectCommand.Parameters.Add("@f2", SqlDbType.VarChar, 50);
                da.SelectCommand.Parameters["@f2"].Value = "%" + f2 + "%";
                MyDataSet ds = new MyDataSet();
                da.Fill(ds, "t1");
                return ds;
            }
        }
        public void Update(int f1, string f2)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con);
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1"].Value = f1;
                MyDataSet ds = new MyDataSet();
                da.Fill(ds, "t1");
                new SqlCommandBuilder(da);
                ds.T1[0].F2 = f2;
                da.Update(ds, "t1");
            }
        }
        public void Insert(int f1, string f2)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1", con);
                MyDataSet ds = new MyDataSet();
                da.Fill(ds, "t1");
                new SqlCommandBuilder(da);
                MyDataSet.T1Row dr = ds.T1.NewT1Row();
                dr.F1 = f1;
                dr.F2 = f2;
                ds.T1.Rows.Add(dr);
                da.Update(ds, "t1");
            }
        }
        public void Delete(int f1)
        {
            using(SqlConnection con = new SqlConnection(constr))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con);
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int);
                da.SelectCommand.Parameters["@f1"].Value = f1;
                MyDataSet ds = new MyDataSet();
                da.Fill(ds, "t1");
                new SqlCommandBuilder(da);
                ds.T1.Rows[0].Delete();
                da.Update(ds, "t1");
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            dal.Update(f1, f2);
        }
        public static void Print(MyDataSet ds)
        {
            if(ds.T1.Rows.Count > 1) Console.Write("{");
            bool first = true;
            foreach(MyDataSet.T1Row dr in ds.T1.Rows)
            {
                if(first)
                {
                    first = false;
                }
                else
                {
                    Console.Write(",");
                }
                Console.Write("[" + dr.F1 + "," + dr.F2 + "]");
            }
            if(ds.T1.Rows.Count > 1) Console.Write("}");
            Console.WriteLine();
        }
        public static void Main(string[] args)
        {
            IDAL dal = new DAL.DataSetDAL(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Print(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Print(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Print(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.Insert(999, "XXX");
            Print(dal.GetAll());
            dal.Delete(999);
            Print(dal.GetAll());
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient

Imports DataSetGen

Namespace DAL
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As MyDataSet
        Function GetAll() As MyDataSet
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As MyDataSet
        Function GetContainingF2(f2 As String) As MyDataSet
        Sub Update(f1 As Integer, f2 As String)
        Sub Insert(f1 As Integer, f2 As String)
        Sub Delete(f1 As Integer)
    End Interface
    Public Class DataSetDAL
        Implements IDAL
        Private constr As String
        Public Sub New(constr As String)
            Me.constr = constr
        End Sub
        Public Function GetOneByPK(f1 As Integer) As MyDataSet Implements IDAL.GetOneByPK
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1").Value = f1
                Dim ds As New MyDataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Function GetAll() As MyDataSet Implements IDAL.GetAll
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1", con)
                Dim ds As New MyDataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As MyDataSet Implements IDAL.GetRangeOfPK
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", con)
                da.SelectCommand.Parameters.Add("@f1_start", SqlDbType.Int)
                da.SelectCommand.Parameters.Add("@f1_end", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1_start").Value = f1_start
                da.SelectCommand.Parameters("@f1_end").Value = f1_end
                Dim ds As New MyDataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Function GetContainingF2(f2 As String) As MyDataSet Implements IDAL.GetContainingF2
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f2 LIKE @f2", con)
                da.SelectCommand.Parameters.Add("@f2", SqlDbType.VarChar, 50)
                da.SelectCommand.Parameters("@f2").Value = "%" & f2 & "%"
                Dim ds As New MyDataSet()
                da.Fill(ds, "t1")
                Return ds
            End Using
        End Function
        Public Sub Update(f1 As Integer, f2 As String) Implements IDAL.Update
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1").Value = f1
                Dim ds As New MyDataSet()
                da.Fill(ds, "t1")
                Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
                ds.T1(0).F2 = f2
                da.Update(ds, "t1")
            End Using
        End Sub
        Public Sub Insert(f1 As Integer, f2 As String) Implements IDAL.Insert
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1", con)
                Dim ds As New MyDataSet()
                da.Fill(ds, "t1")
                Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
                Dim dr As MyDataSet.T1Row = ds.T1.NewT1Row()
                dr.F1 = f1
                dr.F2 = f2
                ds.T1.Rows.Add(dr)
                da.Update(ds, "t1")
            End Using
        End Sub
        Public Sub Delete(f1 As Integer) Implements IDAL.Delete
            Using con As New SqlConnection(constr)
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1 WHERE f1 = @f1", con)
                da.SelectCommand.Parameters.Add("@f1", SqlDbType.Int)
                da.SelectCommand.Parameters("@f1").Value = f1
                Dim ds As New MyDataSet()
                da.Fill(ds, "t1")
                Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
                ds.T1.Rows(0).Delete()
                da.Update(ds, "t1")
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            dal.Update(f1, f2)
        End Sub
        Public Shared Sub Print(ds As MyDataSet)
            If ds.T1.Rows.Count > 1 Then
                Console.Write("{")
            End If
            Dim first As Boolean = True
            For Each dr As MyDataSet.T1Row In ds.T1.Rows
                If first Then
                    first = False
                Else
                    Console.Write(",")
                End If
                Console.Write("[" & dr.F1 & "," & dr.F2 & "]")
            Next
            If ds.T1.Rows.Count > 1 Then
                Console.Write("}")
            End If
            Console.WriteLine()
        End Sub
        Public Shared Sub Main(args As String())
            Dim dal As DAL.IDAL = New DAL.DataSetDAL("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
            Print(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Print(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Print(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.Insert(999, "XXX")
            Print(dal.GetAll())
            dal.Delete(999)
            Print(dal.GetAll())
            Console.ReadKey()
        End Sub
    End Class
End Namespace

Better case for DataSet

Most will consider DataSet (untyped or typed) poorly suited for a DAL. For several reasons:

DataSet is much better suited for cases with no DAL.

Example:

using System;

using System.Data;
using System.Data.SqlClient;

namespace Demo
{
    public class Program
    {
        public static void UpdateF2(DataSet ds, int f1, string f2)
        {
            ds.Tables["t1"].Select("f1=" + f1)[0]["f2"] = f2;
        }
        public static void Print(DataRow[] drs)
        {
            if(drs.Length > 1) Console.Write("{");
            bool first = true;
            foreach(DataRow dr in drs)
            {
                if(first)
                {
                    first = false;
                }
                else
                {
                    Console.Write(",");
                }
                Console.Write("[" + (int)dr["f1"] + "," + (string)dr["f2"] + "]");
            }
            if(drs.Length > 1) Console.Write("}");
            Console.WriteLine();
        }
        public static void Print(DataSet ds)
        {
            Print(ds.Tables["t1"].Select());
        }
        public static void Main(string[] args)
        {
            using(SqlConnection con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test"))
            {
                SqlDataAdapter da = new SqlDataAdapter("SELECT f1,f2 FROM t1", con);
                DataSet ds = new DataSet();
                da.Fill(ds, "t1");
                new SqlCommandBuilder(da);
                Print(ds.Tables["t1"].Select("f1=2"));
                UpdateF2(ds, 2, "BBB");
                Print(ds.Tables[0].Select("f1=2"));
                UpdateF2(ds, 2, "BB");
                Print(ds.Tables[0].Select("f1=2"));
                Print(ds);
                Print(ds.Tables["t1"].Select("2 <= f1 AND f1 <= 4"));
                Print(ds.Tables["t1"].Select("f2 LIKE '%BB%'"));
                DataRow dr = ds.Tables["t1"].NewRow();
                dr["f1"] = 999;
                dr["f2"] = "XXX";
                ds.Tables["t1"].Rows.Add(dr);
                Print(ds);
                ds.Tables["t1"].Select("f1=999")[0].Delete();
                Print(ds);
                da.Update(ds, "t1");
                
            }
            Console.ReadKey();
        }
    }
}
Imports System

Imports System.Data
Imports System.Data.SqlClient

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(ds As DataSet, f1 As Integer, f2 As String)
            ds.Tables("t1").Select("f1=" & f1)(0)("f2") = f2
        End Sub
        Public Shared Sub Print(drs As DataRow())
            If drs.Length > 1 Then
                Console.Write("{")
            End If
            Dim first As Boolean = True
            For Each dr As DataRow In drs
                If first Then
                    first = False
                Else
                    Console.Write(",")
                End If
                Console.Write("[" & CInt(dr("f1")) & "," & DirectCast(dr("f2"), String) & "]")
            Next
            If drs.Length > 1 Then
                Console.Write("}")
            End If
            Console.WriteLine()
        End Sub
        Public Shared Sub Print(ds As DataSet)
            Print(ds.Tables("t1").Select())
        End Sub
        Public Shared Sub Main(args As String())
            Using con As New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
                Dim da As New SqlDataAdapter("SELECT f1,f2 FROM t1", con)
                Dim ds As New DataSet()
                da.Fill(ds, "t1")
                Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
                Print(ds.Tables("t1").Select("f1=2"))
                UpdateF2(ds, 2, "BBB")
                Print(ds.Tables(0).Select("f1=2"))
                UpdateF2(ds, 2, "BB")
                Print(ds.Tables(0).Select("f1=2"))
                Print(ds)
                Print(ds.Tables("t1").Select("2 <= f1 AND f1 <= 4"))
                Print(ds.Tables("t1").Select("f2 LIKE '%BB%'"))
                Dim dr As DataRow = ds.Tables("t1").NewRow()
                dr("f1") = 999
                dr("f2") = "XXX"
                ds.Tables("t1").Rows.Add(dr)
                Print(ds)
                ds.Tables("t1").Select("f1=999")(0).Delete()
                Print(ds)
                da.Update(ds, "t1")
            End Using
            Console.ReadKey()
        End Sub
    End Class
End Namespace

Note how much shorter the and simpleer the code is.

The above style will not work well for a 500000 lines of code application. No layering and no reuse will make it a mess. But it will work great for a 500 lines of code application. And there are actually also a need for small applications.

Binding

And there is another even more code saving case for DataSet: it can be used to data bind GUI controls to.

Example:

using System;

using System.Data;
using System.Data.SqlClient;

using System.Drawing;
using System.Windows.Forms;

namespace Demo
{
    public class DemoForm : Form
    {
        private SqlConnection con;
        private SqlDataAdapter da;
        private DataSet ds;
        private DataGridView dgv;
        private Button save;
        public DemoForm()
        {
            InitializeComponent();
        }
        private void InitializeComponent() 
        {
            // data
            con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            da = new SqlDataAdapter("SELECT f1,f2 FROM t1", con);
            ds = new DataSet();
            da.Fill(ds, "t1");
            new SqlCommandBuilder(da);
            // form
            dgv = new DataGridView();
            save = new Button();
            SuspendLayout();
            dgv.Location = new Point(50, 50);
            dgv.Size = new Size(300, 200);
            dgv.DataSource = ds.Tables["t1"];
            save.Location = new Point(50, 300);
            save.Size = new Size(100, 25);
            save.Text = "Save";
            save.Click += SaveClick;
            ClientSize = new Size(400, 400);
            Controls.Add(dgv);
            Controls.Add(save);
            Text = "Demo Form";
            ResumeLayout(false);
        }
        void SaveClick(object sender, EventArgs e)
        {
            da.Update(ds, "t1");
            ds.AcceptChanges();
            dgv.Refresh();
        }

    }
    public class Program
    {
        public static void Main(string[] args)
        {
            DemoForm f = new DemoForm();
            f.ShowDialog();    
        }
    }
}
Imports System

Imports System.Data
Imports System.Data.SqlClient

Imports System.Drawing
Imports System.Windows.Forms

Namespace Demo
    Public Class DemoForm
        Inherits Form
        Private con As SqlConnection
        Private da As SqlDataAdapter
        Private ds As DataSet
        Private dgv As DataGridView
        Private save As Button
        Public Sub New()
            InitializeComponent()
        End Sub
        Private Sub InitializeComponent()
            ' data
            con = New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
            da = New SqlDataAdapter("SELECT f1,f2 FROM t1", con)
            ds = New DataSet()
            da.Fill(ds, "t1")
            Dim dummy As SqlCommandBuilder = New SqlCommandBuilder(da)
            ' form
            dgv = New DataGridView()
            save = New Button()
            SuspendLayout()
            dgv.Location = New Point(50, 50)
            dgv.Size = New Size(300, 200)
            dgv.DataSource = ds.Tables("t1")
            save.Location = New Point(50, 300)
            save.Size = New Size(100, 25)
            save.Text = "Save"
            AddHandler save.Click, AddressOf SaveClick
            ClientSize = New Size(400, 400)
            Controls.Add(dgv)
            Controls.Add(save)
            Text = "Demo Form"
            ResumeLayout(False)
        End Sub
        Private Sub SaveClick(sender As Object, e As EventArgs)
            da.Update(ds, "t1")
            ds.AcceptChanges()
            dgv.Refresh()
        End Sub

    End Class
    Public Class Program
        Public Shared Sub Main(args As String())
            Dim f As New DemoForm()
            f.ShowDialog()
        End Sub
    End Class
End Namespace

This tiny program actually display the table in a grid and allows: changing values, inserting rows and deleting rows.

DataAdapter, DataSet and CommandBuilder handle almost all the database interaction behind the scenes.

Explanation:

Again it is very poor layering but it can work great for small applications.

Hybrid

A sometimes seen hybrid DAL is to:

LINQ to SQL (ORM):

LINQ for SQL was Microsofts first ORM for .NET. It is usually seen as a temporary solution waiting for EF (Entity Framework) to become available. It is SQLServer only.

ORM or O/R-Mapper or Object Relational Mapper frameworks are frameworks that maps between an object model and a relational data model. They enable the application code to deal only with the object model and then the ORM translate to relational database and SQL. Consider it "SQL free" database programming.

using System;
using System.Collections.Generic;

namespace DAL
{
    using System.Data.Linq.Mapping;
    [Table(Name="t1")]
    public class T1
    {
        [Column(Name="f1",IsPrimaryKey=true)]
        public int F1 { get; set; }
        [Column(Name="f2")]
        public string F2 { get; set; }
        public T1() : this(0, "")
        {
        }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.LINQtoSQL
{
    using System.Linq;
    using System.Data;
    using System.Data.Common;
    using System.Data.SqlClient;
    using System.Data.Linq;
    using System.Data.Linq.SqlClient;
    public class LINQtoSQLDAL : IDAL
    {
        private string constr;
        public LINQtoSQLDAL(string constr)
        {
            this.constr = constr;
        }
        public T1 GetOneByPK(int f1)
        {
            using(DataContext db = new DataContext(new SqlConnection(constr)))
            {
                return db.GetTable<T1>().FirstOrDefault(t1 => t1.F1 == f1);
            }
        }
        public List<T1> GetAll()
        {
            using(DataContext db = new DataContext(new SqlConnection(constr)))
            {
                return db.GetTable<T1>().ToList();
            }
        }
        public List<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            using(DataContext db = new DataContext(new SqlConnection(constr)))
            {
                return db.GetTable<T1>().Where(t1 => f1_start <= t1.F1 && t1.F1 <= f1_end).ToList();
            }
        }
        public List<T1> GetContainingF2(string f2)
        {
            using(DataContext db = new DataContext(new SqlConnection(constr)))
            {
                return db.GetTable<T1>().Where(t1 => t1.F2.Contains(f2)).ToList();
            }
        }
        public void SaveChanges(T1 o)
        {
            using(DataContext db = new DataContext(new SqlConnection(constr)))
            {
                T1 orig = db.GetTable<T1>().FirstOrDefault(t1 => t1.F1 == o.F1);
                orig.F2 = o.F2;
                db.SubmitChanges();
            }
        }
        public void SaveNew(T1 o)
        {
            using(DataContext db = new DataContext(new SqlConnection(constr)))
            {
                db.GetTable<T1>().InsertOnSubmit(o);
                db.SubmitChanges();
            }
        }
        public void Remove(int f1)
        {
            using(DataContext db = new DataContext(new SqlConnection(constr)))
            {
                db.GetTable<T1>().DeleteOnSubmit(db.GetTable<T1>().FirstOrDefault(t1 => t1.F1 == f1));
                db.SubmitChanges();
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(List<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Main(string[] args)
        {
            IDAL dal = new DAL.LINQtoSQL.LINQtoSQLDAL(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
            Console.ReadKey();
        }
    }
}

The attributes:

[Table(Name="t1")]
[Column(Name="f1",IsPrimaryKey=true)]
[Column(Name="f2")]

defines how the class is mapped to the database.

Imports System
Imports System.Collections.Generic

Imports System.Linq
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClient
Imports System.Data.Linq
Imports System.Data.Linq.Mapping
Imports System.Data.Linq.SqlClient

Namespace DAL
    <Table(Name := "t1")> _
    Public Class T1
        <Column(Name := "f1", IsPrimaryKey := True)> _
        Public Property F1() As Integer
        <Column(Name := "f2")> _
        Public Property F2() As String
        Public Sub New()
            Me.New(0, "")
        End Sub
        Public Sub New(_f1 As Integer, _f2 As String)
            F1 = _f1
            F2 = _f2
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("[{0},{1}]", F1, F2)
        End Function
    End Class
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

Namespace DAL.LINQtoSQL
    Public Class LINQtoSQLDAL
        Implements IDAL
        Private constr As String
        Public Sub New(constr As String)
            Me.constr = constr
        End Sub
        Public Function GetOneByPK(f1 As Integer) As T1 Implements IDAL.GetOneByPK
            Using db As New DataContext(New SqlConnection(constr))
                Return db.GetTable(Of T1)().FirstOrDefault(Function(t1) t1.F1 = f1)
            End Using
        End Function
        Public Function GetAll() As List(Of T1) Implements IDAL.GetAll
            Using db As New DataContext(New SqlConnection(constr))
                Return db.GetTable(Of T1)().ToList()
            End Using
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1) Implements IDAL.GetRangeOfPK
            Using db As New DataContext(New SqlConnection(constr))
                Return db.GetTable(Of T1)().Where(Function(t1) f1_start <= t1.F1 AndAlso t1.F1 <= f1_end).ToList()
            End Using
        End Function
        Public Function GetContainingF2(f2 As String) As List(Of T1) Implements IDAL.GetContainingF2
            Using db As New DataContext(New SqlConnection(constr))
                Return db.GetTable(Of T1)().Where(Function(t1) t1.F2.Contains(f2)).ToList()
            End Using
        End Function
        Public Sub SaveChanges(o As T1) Implements IDAL.SaveChanges
            Using db As New DataContext(New SqlConnection(constr))
                Dim orig As T1 = db.GetTable(Of T1)().FirstOrDefault(Function(t1) t1.F1 = o.F1)
                orig.F2 = o.F2
                db.SubmitChanges()
            End Using
        End Sub
        Public Sub SaveNew(o As T1) Implements IDAL.SaveNew
            Using db As New DataContext(New SqlConnection(constr))
                db.GetTable(Of T1)().InsertOnSubmit(o)
                db.SubmitChanges()
            End Using
        End Sub
        Public Sub Remove(f1 As Integer) Implements IDAL.Remove
            Using db As New DataContext(New SqlConnection(constr))
                db.GetTable(Of T1)().DeleteOnSubmit(db.GetTable(Of T1)().FirstOrDefault(Function(t1) t1.F1 = f1))
                db.SubmitChanges()
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            Dim o As DAL.T1 = dal.GetOneByPK(f1)
            o.F2 = f2
            dal.SaveChanges(o)
        End Sub
        Public Shared Sub Print(lst As List(Of DAL.T1))
            Console.Write("{")
            For i As Integer = 0 To lst.Count - 1
                If i > 0 Then
                    Console.Write(",")
                End If
                Console.Write(lst(i))
            Next
            Console.WriteLine("}")
        End Sub
        Public Shared Sub Main(args As String())
            Dim dal As DAL.IDAL = New DAL.LINQtoSQL.LINQtoSQLDAL("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Console.WriteLine(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.SaveNew(New DAL.T1(999, "XXX"))
            Print(dal.GetAll())
            dal.Remove(999)
            Print(dal.GetAll())
            Console.ReadKey()
        End Sub
    End Class
End Namespace

The attributes:

<Table(Name := "t1")>
<Column(Name := "f1", IsPrimaryKey := True)>
<Column(Name := "f2")>

defines how the class is mapped to the database.

LINQ to SQL is sort of deprecated and replaced by EF. But I have always liked LINQ to SQL. It is simple an deasy to use.

EF (ORM):

LINQ to SQL was sort of a temporary solution from Microsoft. Their real long term ORM strategy is EF (Entity Framework).

EF is a large and very powerfull ORM framework. But also a rather complex framework.

To really understand EF one need to understand the 5 layers:

And as a result EF can be used in 3 different styles:

code first
data model and EF model is generated from object model
database first
object model and EF model is generated from database model
model first
source code and data model is generated from EF model

I have an ambigious feeling about EF.

I appreciate the power and flexibility of it.

But on the other side I strongly dislike:

Code first

In many ways code first is the most simple approach and it is actually possible to just write ones data classes and then map them to an existing database.

using System;
using System.Collections.Generic;

namespace DAL
{
    using System.ComponentModel.DataAnnotations;
    using System.ComponentModel.DataAnnotations.Schema;
    [Table("t1")]
    public class T1
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Column("f1")]
        public int F1 { get; set; }
        [Column("f2")]
        public string F2 { get; set; }
        public T1() : this(0, "")
        {
        }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.EF
{
    using System.Data.Entity;
    using System.Linq;
    public class MyDbContext : DbContext
    {
        public MyDbContext(string constrkey) : base(constrkey)
        {
        }
        public DbSet<T1> T1 { get; set; }
    }
    public class EFDAL : IDAL
    {
        private string constrkey;
        public EFDAL(string constrkey)
        {
            this.constrkey = constrkey;
        }
        public T1 GetOneByPK(int f1)
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                return db.T1.FirstOrDefault(t1 => t1.F1 == f1);
            }
        }
        public List<T1> GetAll()
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                return db.T1.ToList();
            }
        }
        public List<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                return db.T1.Where(t1 => f1_start <= t1.F1 && t1.F1 <= f1_end).ToList();
            }
        }
        public List<T1> GetContainingF2(string f2)
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                return db.T1.Where(t1 => t1.F2.Contains(f2)).ToList();
            }
        }
        public void SaveChanges(T1 o)
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                T1 orig = db.T1.FirstOrDefault(t1 => t1.F1 == o.F1);
                orig.F2 = o.F2;
                db.SaveChanges();
            }
        }
        public void SaveNew(T1 o)
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                db.T1.Add(o);
                db.SaveChanges();
            }
        }
        public void Remove(int f1)
        {
            using(MyDbContext db = new MyDbContext(constrkey))
            {
                db.T1.Remove(db.T1.FirstOrDefault(t1 => t1.F1 == f1));
                db.SaveChanges();
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(List<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Test(string constrkey)
        {
            Console.WriteLine(constrkey + ":");
            IDAL dal = new DAL.EF.EFDAL(constrkey);
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
        }
        public static void Main(string[] args)
        {
            Test("SQLSERVER");
            Test("MYSQL");
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Imports System.Data.Entity
Imports System.Linq

Namespace DAL
    <Table("t1")> _
    Public Class T1
        <Key> _
        <DatabaseGenerated(DatabaseGeneratedOption.None)> _
        <Column("f1")> _
        Public Property F1() As Integer
        <Column("f2")> _
        Public Property F2() As String
        Public Sub New()
            Me.New(0, "")
        End Sub
        Public Sub New(_f1 As Integer, _f2 As String)
            F1 = _f1
            F2 = _f2
        End Sub
        Public Overrides Function ToString() As String
            Return String.Format("[{0},{1}]", F1, F2)
        End Function
    End Class
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

Namespace DAL.EF
    Public Class MyDbContext
        Inherits DbContext
        Public Sub New(constrkey As String)
            MyBase.New(constrkey)
        End Sub
        Public Property T1() As DbSet(Of T1)
    End Class
    Public Class EFDAL
        Implements IDAL
        Private constrkey As String
        Public Sub New(constrkey As String)
            Me.constrkey = constrkey
        End Sub
        Public Function GetOneByPK(f1 As Integer) As T1 Implements IDAL.GetOneByPK
            Using db As New MyDbContext(constrkey)
                Return db.T1.FirstOrDefault(Function(t1) t1.F1 = f1)
            End Using
        End Function
        Public Function GetAll() As List(Of T1) Implements IDAL.GetAll
            Using db As New MyDbContext(constrkey)
                Return db.T1.ToList()
            End Using
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1) Implements IDAL.GetRangeOfPK
            Using db As New MyDbContext(constrkey)
                Return db.T1.Where(Function(t1) f1_start <= t1.F1 AndAlso t1.F1 <= f1_end).ToList()
            End Using
        End Function
        Public Function GetContainingF2(f2 As String) As List(Of T1) Implements IDAL.GetContainingF2
            Using db As New MyDbContext(constrkey)
                Return db.T1.Where(Function(t1) t1.F2.Contains(f2)).ToList()
            End Using
        End Function
        Public Sub SaveChanges(o As T1) Implements IDAL.SaveChanges
            Using db As New MyDbContext(constrkey)
                Dim orig As T1 = db.T1.FirstOrDefault(Function(t1) t1.F1 = o.F1)
                orig.F2 = o.F2
                db.SaveChanges()
            End Using
        End Sub
        Public Sub SaveNew(o As T1) Implements IDAL.SaveNew
            Using db As New MyDbContext(constrkey)
                db.T1.Add(o)
                db.SaveChanges()
            End Using
        End Sub
        Public Sub Remove(f1 As Integer) Implements IDAL.Remove
            Using db As New MyDbContext(constrkey)
                db.T1.Remove(db.T1.FirstOrDefault(Function(t1) t1.F1 = f1))
                db.SaveChanges()
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            Dim o As DAL.T1 = dal.GetOneByPK(f1)
            o.F2 = f2
            dal.SaveChanges(o)
        End Sub
        Public Shared Sub Print(lst As List(Of DAL.T1))
            Console.Write("{")
            For i As Integer = 0 To lst.Count - 1
                If i > 0 Then
                    Console.Write(",")
                End If
                Console.Write(lst(i))
            Next
            Console.WriteLine("}")
        End Sub
        Public Shared Sub Test(constrkey As String)
            Console.WriteLine(constrkey & ":")
            Dim dal As DAL.IDAL = New DAL.EF.EFDAL(constrkey)
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BBB")
            Console.WriteLine(dal.GetOneByPK(2))
            UpdateF2(dal, 2, "BB")
            Console.WriteLine(dal.GetOneByPK(2))
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.SaveNew(New DAL.T1(999, "XXX"))
            Print(dal.GetAll())
            dal.Remove(999)
            Print(dal.GetAll())
        End Sub
        Public Shared Sub Main(args As String())
            Test("SQLSERVER")
            Test("MYSQL")
            Console.ReadKey()
        End Sub
    End Class
End Namespace

app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <configSections>
        <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
    </configSections>
    <connectionStrings>
        <add name="SQLSERVER" connectionString="Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test" providerName="System.Data.SqlClient" />
        <add name="MYSQL" connectionString="Server=localhost;Database=Test;User Id=root;Password=" providerName="MySql.Data.MySqlClient" />
    </connectionStrings>
    <entityFramework>
        <defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework"/>
        <providers>
            <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer"/>
            <provider invariantName="MySql.Data.MySqlClient" type="MySql.Data.MySqlClient.MySqlProviderServices, MySql.Data.Entity.EF6"/>
        </providers>
    </entityFramework>
</configuration>

One need both an ADO.NET provider and an EF provider defined in app.config.

Note that the data class is decorated with attributes to specify the persistence.

It is possible to avoid those by using specific conventions:

Even though this "convention over configuration" is being heavily pushed by Microsoft, then I do not think that it will work in most real world examples. One need to map between object model and relational database tables.

Database first

Using database first the data classes can be generated two different ways:

Command line will look something like:

edmgen /mode:FullGeneration /project:EFDB1st /provider:System.Data.SqlClient /connectionstring:"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test"
using System;
using System.Collections.Generic;

using EFDB1st; // namespace for generated code with T1 class and EFDB1stContext class

namespace DAL
{
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        List<T1> GetAll();
        List<T1> GetRangeOfPK(int f1_start, int f1_end);
        List<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
    public static class Util
    {
        public static string MyToString(this T1 o)
        {
            return string.Format("[{0},{1}]", o.F1, o.F2);    
        }
    }
}

namespace DAL.EF
{
    using System.Data.Entity;
    using System.Linq;
    public class EFDAL : IDAL
    {
        public T1 GetOneByPK(int f1)
        {
            using(EFDB1stContext db = new EFDB1stContext())
            {
                return db.T1.FirstOrDefault(t1 => t1.F1 == f1);
            }
        }
        public List<T1> GetAll()
        {
            using(EFDB1stContext db = new EFDB1stContext())
            {
                return db.T1.ToList();
            }
        }
        public List<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            using(EFDB1stContext db = new EFDB1stContext())
            {
                return db.T1.Where(t1 => f1_start <= t1.F1 && t1.F1 <= f1_end).ToList();
            }
        }
        public List<T1> GetContainingF2(string f2)
        {
            using(EFDB1stContext db = new EFDB1stContext())
            {
                return db.T1.Where(t1 => t1.F2.Contains(f2)).ToList();
            }
        }
        public void SaveChanges(T1 o)
        {
            using(EFDB1stContext db = new EFDB1stContext())
            {
                T1 orig = db.T1.FirstOrDefault(t1 => t1.F1 == o.F1);
                orig.F2 = o.F2;
                db.SaveChanges();
            }
        }
        public void SaveNew(T1 o)
        {
            using(EFDB1stContext db = new EFDB1stContext())
            {
                db.T1.AddObject(o);
                db.SaveChanges();
            }
        }
        public void Remove(int f1)
        {
            using(EFDB1stContext db = new EFDB1stContext())
            {
                db.T1.DeleteObject(db.T1.FirstOrDefault(t1 => t1.F1 == f1));
                db.SaveChanges();
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(List<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i].MyToString());
            }
            Console.WriteLine("}");
        }
        public static void Main(string[] args)
        {
            IDAL dal = new DAL.EF.EFDAL();
            Console.WriteLine(dal.GetOneByPK(2).MyToString());
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2).MyToString());
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2).MyToString());
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1 { F1 = 999, F2 = "XXX"});
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
            Console.ReadKey();
        }
    }
}
Imports System
Imports System.Collections.Generic

Imports System.Data.Entity
Imports System.Linq

Imports System.Runtime.CompilerServices

Imports EFDB1st

Public Module Util
    <Extension> _
    Public Function MyToString(o As T1) As String
        Return String.Format("[{0},{1}]", o.F1, o.F2)
    End Function
End Module

Namespace DAL
    Public Interface IDAL
        Function GetOneByPK(f1 As Integer) As T1
        Function GetAll() As List(Of T1)
        Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1)
        Function GetContainingF2(f2 As String) As List(Of T1)
        Sub SaveChanges(o As T1)
        Sub SaveNew(o As T1)
        Sub Remove(f1 As Integer)
    End Interface
End Namespace

Namespace DAL.EF
    Public Class EFDAL
        Implements IDAL
        Public Function GetOneByPK(f1 As Integer) As T1 Implements IDAL.GetOneByPK
            Using db As New EFDB1stContext()
                Return db.T1.FirstOrDefault(Function(t1) t1.F1 = f1)
            End Using
        End Function
        Public Function GetAll() As List(Of T1) Implements IDAL.GetAll
            Using db As New EFDB1stContext()
                Return db.T1.ToList()
            End Using
        End Function
        Public Function GetRangeOfPK(f1_start As Integer, f1_end As Integer) As List(Of T1) Implements IDAL.GetRangeOfPK
            Using db As New EFDB1stContext()
                Return db.T1.Where(Function(t1) f1_start <= t1.F1 AndAlso t1.F1 <= f1_end).ToList()
            End Using
        End Function
        Public Function GetContainingF2(f2 As String) As List(Of T1) Implements IDAL.GetContainingF2
            Using db As New EFDB1stContext()
                Return db.T1.Where(Function(t1) t1.F2.Contains(f2)).ToList()
            End Using
        End Function
        Public Sub SaveChanges(o As T1) Implements IDAL.SaveChanges
            Using db As New EFDB1stContext()
                Dim orig As T1 = db.T1.FirstOrDefault(Function(t1) t1.F1 = o.F1)
                orig.F2 = o.F2
                db.SaveChanges()
            End Using
        End Sub
        Public Sub SaveNew(o As T1) Implements IDAL.SaveNew
            Using db As New EFDB1stContext()
                db.T1.AddObject(o)
                db.SaveChanges()
            End Using
        End Sub
        Public Sub Remove(f1 As Integer) Implements IDAL.Remove
            Using db As New EFDB1stContext()
                db.T1.DeleteObject(db.T1.FirstOrDefault(Function(t1) t1.F1 = f1))
                db.SaveChanges()
            End Using
        End Sub
    End Class
End Namespace

Namespace Demo
    Public Class Program
        Public Shared Sub UpdateF2(dal As DAL.IDAL, f1 As Integer, f2 As String)
            Dim o As T1 = dal.GetOneByPK(f1)
            o.F2 = f2
            dal.SaveChanges(o)
        End Sub
        Public Shared Sub Print(lst As List(Of T1))
            Console.Write("{")
            For i As Integer = 0 To lst.Count - 1
                If i > 0 Then
                    Console.Write(",")
                End If
                Console.Write(lst(i).MyToString())
            Next
            Console.WriteLine("}")
        End Sub
        Public Shared Sub Main(args As String())
            Dim dal As DAL.IDAL = New DAL.EF.EFDAL()
            Console.WriteLine(dal.GetOneByPK(2).MyToString())
            UpdateF2(dal, 2, "BBB")
            Console.WriteLine(dal.GetOneByPK(2).MyToString())
            UpdateF2(dal, 2, "BB")
            Console.WriteLine(dal.GetOneByPK(2).MyToString())
            Print(dal.GetAll())
            Print(dal.GetRangeOfPK(2, 4))
            Print(dal.GetContainingF2("B"))
            dal.SaveNew(New T1() With { _
                .F1 = 999, _
                .F2 = "XXX" _
            })
            Print(dal.GetAll())
            dal.Remove(999)
            Print(dal.GetAll())
            Console.ReadKey()
        End Sub
    End Class
End Namespace

app.config:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
    <connectionStrings>
        <add name="EFDB1stContext" connectionString="Metadata=C:\Work\EFDB1st;Provider=System.Data.SqlClient;Provider Connection String='Server=ARNEPC4\SQLEXPRESS;Integrated Security=True;Database=Test;MultipleActiveResultSets=True'" providerName="System.Data.EntityClient" />
    </connectionStrings>
</configuration>

One just need a connection string, but that connection string has embedded a reference to the directory with the generated EF model files and a reference to an ADO.NET provider.

Connection string key is the name of the generated context class.

Model first

I will not show any example of model first. The support is in Visual Studio.

EF and join

With EF a join is explicit done in the code. Either the code explicit ask to include child data or the code explicit load child data later.

The demo will use code first approach.

Demo:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
 
namespace Join.EF
{
    [Table("orderline")]
    public class OrderLine
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Column("orderlineid")]
        public int OrderLineId { get; set; }
        [Column("orderid")]
        public int OrderId { get; set; }
        [Column("itemname")]
        public string ItemName { get; set; }
        [Column("itemprice")]
        public decimal ItemPrice { get; set; }
        [Column("quantity")]
        public int Quantity { get; set; }
        [NotMapped]
        public decimal ItemCost
        {
            get
            {
                return ItemPrice * Quantity;
            }
        }
    }
    [Table("myorder")]
    public class Order
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.None)]
        [Column("orderid")]
        public int OrderId { get; set; }
        [Column("customer")]
        public string Customer { get; set; }
        public IList<OrderLine> OrderLines { get; set; }
        [NotMapped]
        public decimal Cost
        {
            get
            {
                return OrderLines.Sum(ol => ol.ItemCost);
            }
        }
    }
    public class MyDbContext : DbContext
    {
        public MyDbContext(string constrkey) : base(constrkey)
        {
            Database.Log = Console.Write;
        }
        public DbSet<Order> Order { get; set; }
        public DbSet<OrderLine> OrderLine { get; set; }
    }
    public class Program
    {
        private static void Dump(Order o)
        {
            Console.WriteLine("{0} total {1:F2}:", o.Customer, o.Cost);
            foreach(OrderLine ol in o.OrderLines)
            {
                Console.WriteLine("  {0,-20} {1,9:F2} {2,3} {3,12:F2}", ol.ItemName, ol.ItemPrice, ol.Quantity, ol.ItemCost);
            }
        }
        private static void Find(MyDbContext db, int orderid) // join
        {
            Console.WriteLine("find orderid={0}:", orderid);
            Order o1 = db.Order.Include(o => o.OrderLines).FirstOrDefault(o => o.OrderId == orderid);
            Dump(o1);
        }
        private static void ListImplicit(MyDbContext db) // join
        {
            Console.WriteLine("list (implicit):");
            foreach(Order o in db.Order.Include(o => o.OrderLines))
            {
                Dump(o);
            }
        }
        private static void ListExplicit(MyDbContext db) // no join
        {
            Console.WriteLine("list (explicit):");
            foreach(Order o in db.Order.ToList()) // ToList aka materialization is necessary to avoid two concurrent query reads on same connection
            {
                o.OrderLines = db.OrderLine.Where(ol => ol.OrderId == o.OrderId).ToList();
                Dump(o);
            }
        }
        public static void Main(string[] args)
        {
            using(MyDbContext db = new MyDbContext("MYSQL"))
            {
                Find(db, 1);
                Find(db, 2);
                ListImplicit(db);
                ListExplicit(db);
            }
        }
    }
}

Basically there are no problems. If the code include the child data (eager loading), then it result in JOIN. If the code wait and explicit load child data (lazy loading) then it result in multiple SELECT. This is the obvious and expected behavior.

NHibernate (ORM):

NHibernate is basically a port of Hibernate (a very widely used Java ORM) to .NET.

The standard way of configuring NHibernate is via XML configuration files.

It is possible to configure via code. But since XML configuration files is the original Hibernate way, then I will use that.

NHibernate actually has two configuration files:

Nhibernate config file for SQLServer demo:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory>
        <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
        <property name="connection.connection_string">Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test</property>
        <property name="dialect">NHibernate.Dialect.MsSql2008Dialect</property>
        <property name="proxyfactory.factory_class">NHibernate.Bytecode.DefaultProxyFactoryFactory, NHibernate</property>
        <property name="show_sql">false</property>
        <property name="hbm2ddl.keywords">none</property>
    </session-factory>
</hibernate-configuration>

Nhibernate config file for MySQL demo:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory>
        <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
        <property name="connection.connection_string">Server=localhost;Database=Test;User Id=root;Password=</property>
        <property name="dialect">NHibernate.Dialect.MySQL55Dialect</property>
        <property name="proxyfactory.factory_class">NHibernate.Bytecode.DefaultProxyFactoryFactory, NHibernate</property>
        <property name="show_sql">false</property>
        <property name="hbm2ddl.keywords">none</property>
    </session-factory>
</hibernate-configuration>

Mapping config file for demo:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" >
    <class name="DAL.T1,NHibXXX" table="t1">
        <id name="F1">
            <column name="f1"/>
        </id>
        <property name="F2">
            <column name="f2"/>
        </property>
    </class>
</hibernate-mapping>

Note this:

<property name="show_sql">false</property>

If set to true then NHibernate will dump all executed SQL statements. That is great for troubleshooting performance problems.

NHibernate queries can be done using two different methodologies:

HQL
HQL (Hibernate Query Language) is a query language with an SQL like syntax. It originates from Java Hibernate.
LINQ
Hibernate LINQ works almost like EF LINQ

Note that is is almost like EF LINQ not exactly like EF LINQ.

I like NHibernate, but one of the main reasons may be that I know Java Hibernate and I like being able to things the same way in Java and C#. But users should be aware that it is a third party open source project and not supported by Microsoft. In my opinion that is not a showstopper, because it is so widely used that it is easy to find help on the internet for any problems.

An important feature of NHibernate is its level 2 cache. See details on how to use in Database and cache.

HQL

using System;
using System.Collections.Generic;

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1() : this(0, "")
        {
        }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        IList<T1> GetAll();
        IList<T1> GetRangeOfPK(int f1_start, int f1_end);
        IList<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.NHibHQL
{
    using NHibernate;
    using NHibernate.Cfg;
    public class NHibHQLDAL : IDAL
    {
        private ISessionFactory sf;
        public NHibHQLDAL(string cfgfnm, string mapfnm)
        {
            Configuration cfg = new Configuration();
            cfg.Configure(cfgfnm);
            cfg.AddXmlFile(mapfnm);
            sf =  cfg.BuildSessionFactory();
        }
        public T1 GetOneByPK(int f1)
        {
            using(ISession s = sf.OpenSession())
            {
                return s.Load<T1>(f1);
            }
        }
        public IList<T1> GetAll()
        {
            using(ISession s = sf.OpenSession())
            {
                return s.CreateQuery("SELECT t1 FROM T1 AS t1").List<T1>();
            }
        }
        public IList<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            using(ISession s = sf.OpenSession())
            {
                IQuery q = s.CreateQuery("SELECT t1 FROM T1 as t1 WHERE :f1_start <= t1.F1 AND t1.F1 <= :f1_end");
                q.SetParameter("f1_start", f1_start);
                q.SetParameter("f1_end", f1_end);
                return q.List<T1>();
            }
        }
        public IList<T1> GetContainingF2(string f2)
        {
            using(ISession s = sf.OpenSession())
            {
                IQuery q = s.CreateQuery("SELECT t1 FROM T1 as t1 WHERE f2 LIKE :f2");
                q.SetParameter("f2", "%" + f2 + "%");
                return q.List<T1>();
            }
        }
        public void SaveChanges(T1 o)
        {
            using(ISession s = sf.OpenSession())
            {
                using (ITransaction tx = s.BeginTransaction()) 
                {
                    s.Update(o);
                    tx.Commit();
                }
            }
        }
        public void SaveNew(T1 o)
        {
            using(ISession s = sf.OpenSession())
            {
                using (ITransaction tx = s.BeginTransaction()) 
                {
                    s.Save(o);
                    tx.Commit();
                }
            }
        }
        public void Remove(int f1)
        {
            using(ISession s = sf.OpenSession())
            {
                using (ITransaction tx = s.BeginTransaction()) 
                {
                    s.Delete(new T1(f1, ""));
                    tx.Commit();
                }
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(IList<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Test(string cfgfnm)
        {
            IDAL dal = new DAL.NHibHQL.NHibHQLDAL(cfgfnm, "mappings.xml");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
        }
        public static void Main(string[] args)
        {
            Test("sqlserver.xml");
            Test("mysql.xml");
            Console.ReadKey();
        }
    }
}

VB.NET and NHibernate is so rare a combination that I will not show any example.

LINQ

using System;
using System.Collections.Generic;

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1() : this(0, "")
        {
        }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        IList<T1> GetAll();
        IList<T1> GetRangeOfPK(int f1_start, int f1_end);
        IList<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.NHibLINQ
{
    using System.Linq;
    using NHibernate;
    using NHibernate.Cfg;
    using NHibernate.Linq;
    public class NHibLINQDAL : IDAL
    {
        private ISessionFactory sf;
        public NHibLINQDAL(string cfgfnm, string mapfnm)
        {
            Configuration cfg = new Configuration();
            cfg.Configure(cfgfnm);
            cfg.AddXmlFile(mapfnm);
            sf =  cfg.BuildSessionFactory();
        }
        public T1 GetOneByPK(int f1)
        {
            using(ISession s = sf.OpenSession())
            {
                return s.QueryOver<T1>().Where(t1 => t1.F1 == f1).SingleOrDefault();
            }
        }
        public IList<T1> GetAll()
        {
            using(ISession s = sf.OpenSession())
            {
                return s.QueryOver<T1>().List();
            }
        }
        public IList<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            using(ISession s = sf.OpenSession())
            {
                return s.QueryOver<T1>().Where(t1 => f1_start <= t1.F1 && t1.F1 <= f1_end).List();
            }
        }
        public IList<T1> GetContainingF2(string f2)
        {
            using(ISession s = sf.OpenSession())
            {
                return s.QueryOver<T1>().WhereRestrictionOn(t1 => t1.F2).IsLike("%" + f2 + "%").List();
            }
        }
        public void SaveChanges(T1 o)
        {
            using(ISession s = sf.OpenSession())
            {
                using (ITransaction tx = s.BeginTransaction()) 
                {
                    s.Update(o);
                    tx.Commit();
                }
            }
        }
        public void SaveNew(T1 o)
        {
            using(ISession s = sf.OpenSession())
            {
                using (ITransaction tx = s.BeginTransaction()) 
                {
                    s.Save(o);
                    tx.Commit();
                }
            }
        }
        public void Remove(int f1)
        {
            using(ISession s = sf.OpenSession())
            {
                using (ITransaction tx = s.BeginTransaction()) 
                {
                    s.Delete(new T1(f1, ""));
                    tx.Commit();
                }
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(IList<T1> lst)
        {
            Console.Write("{");
            for(int i = 0; i < lst.Count; i++)
            {
                if(i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Test(string cfgfnm)
        {
            IDAL dal = new DAL.NHibLINQ.NHibLINQDAL(cfgfnm, "mappings.xml");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
        }
        public static void Main(string[] args)
        {
            Test("sqlserver.xml");
            Test("mysql.xml");
            Console.ReadKey();
        }
    }
}

VB.NET and NHibernate is so rare a combination that I will not show any example.

NHibernate and join

With Hibernate the table relation is defined in the mapping:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" >
    <class name="Join.Hib.Order,Hib" table="myorder">
        <id name="OrderId">
            <column name="orderid"/>
        </id>
        <property name="Customer">
            <column name="customer"/>
        </property>
        <bag name="OrderLines" lazy="false" fetch="join">  <!-- use lazy="true" andfetch="select" for lazy loading -->
            <key column="orderid"/>  <!-- join field on many side -->
            <one-to-many class="Join.Hib.OrderLine"/>
        </bag>
    </class>
    <class name="Join.Hib.OrderLine,Hib" table="orderline">
        <id name="OrderLineId">
            <column name="orderlineid"/>
        </id>
        <property name="OrderId">
            <column name="orderid"/>
        </property>
        <property name="ItemName">
            <column name="itemname"/>
        </property>
        <property name="ItemPrice">
            <column name="itemprice"/>
        </property>
        <property name="Quantity">
            <column name="quantity"/>
        </property>
    </class>
</hibernate-mapping>

Demo:

using System;
using System.Collections.Generic;
using System.Linq;

using NHibernate;
using NHibernate.Cfg;

namespace Join.Hib
{
    public class OrderLine
    {
        public int OrderLineId { get; set; }
        public int OrderId { get; set; }
        public string ItemName { get; set; }
        public decimal ItemPrice { get; set; }
        public int Quantity { get; set; }
        public decimal ItemCost
        {
            get
            {
                return ItemPrice * Quantity;
            }
        }
    }
    public class Order
    {
        public int OrderId { get; set; }
        public string Customer { get; set; }
        public IList<OrderLine> OrderLines { get; set; }
        public decimal Cost
        {
            get
            {
                return OrderLines.Sum(ol => ol.ItemCost);
            }
        }
    }
    public class Program
    {
        private static void Dump(Order o)
        {
            Console.WriteLine("{0} total {1:F2}:", o.Customer, o.Cost);
            foreach(OrderLine ol in o.OrderLines)
            {
                Console.WriteLine("  {0,-20} {1,9:F2} {2,3} {3,12:F2}", ol.ItemName, ol.ItemPrice, ol.Quantity, ol.ItemCost);
            }
        }
        private static void Find(ISessionFactory sf, int orderid) // join
        {
            Console.WriteLine("find orderid={0}:", orderid);
            using(ISession s = sf.OpenSession())
            {
                Order o1 = s.Load<Order>(orderid);
                Dump(o1);
            }
        }
        private static void ListHQLImpl(ISessionFactory sf) // no join
        {
            Console.WriteLine("list (HQL - implicit):");
            using(ISession s = sf.OpenSession())
            {
                foreach(Order o in s.CreateQuery("SELECT o FROM Order o").List<Order>())
                {
                    Dump(o);
                }
            }    
        }
        private static void ListHQLExplJoin(ISessionFactory sf) // no join
        {
            Console.WriteLine("list (HQL - explicit join):");
            using(ISession s = sf.OpenSession())
            {
                foreach(Order o in s.CreateQuery("SELECT DISTINCT o FROM Order o JOIN o.OrderLines").List<Order>())
                {
                    Dump(o);
                }
            }    
        }
        private static void ListHQLExplJoinFetch(ISessionFactory sf) // join
        {
            Console.WriteLine("list (HQL - explicit join fetch):");
            using(ISession s = sf.OpenSession())
            {
                foreach(Order o in s.CreateQuery("SELECT DISTINCT o FROM Order o JOIN FETCH o.OrderLines").List<Order>())
                {
                    Dump(o);
                }
            }    
        }
        private static void LINQFind(ISessionFactory sf, int orderid) // join
        {
            Console.WriteLine("LINQ find orderid={0}:", orderid);
            using(ISession s = sf.OpenSession())
            {
                Order o1 = s.QueryOver<Order>().Where(o => o.OrderId == orderid).SingleOrDefault();
                Dump(o1);
            }
        }
        private static void LINQList(ISessionFactory sf) // join
        {
            Console.WriteLine("LINQ list:");
            using(ISession s = sf.OpenSession())
            {
                foreach(Order o in s.QueryOver<Order>().List().Distinct())
                {
                    Dump(o);
                }
            }    
        }
        public static void Main(string[] args)
        {
            Configuration cfg = new Configuration();
            cfg.Configure("mysql.xml");
            cfg.AddXmlFile("mappings.xml");
            ISessionFactory sf = cfg.BuildSessionFactory();
            Find(sf, 1);
            Find(sf, 2);
            ListHQLImpl(sf);
            ListHQLExplJoin(sf);
            ListHQLExplJoinFetch(sf);
            LINQFind(sf, 1);
            LINQFind(sf, 2);
            LINQList(sf);
        }
    }
}

We see that:

result in JOIN, while:

result in multiple SELECT.

Dapper (ORM):

Dapper is an open source ORM available via NuGet.

Dapper use a minimalistic approach to ORM. It uses SQL as query language. It does not come with its own manager but is implemented as extension methods to a standard ADO.NET IDbConnection plus some utility functionality. It uses convention instead of configuration.

I like the simple approach.

Dapper can be used two ways:

Plain

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;

using Dapper;

namespace DAL
{
    public class T1
    {
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1() : this(0, "")
        {
        }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        IList<T1> GetAll();
        IList<T1> GetRangeOfPK(int f1_start, int f1_end);
        IList<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.DapperPlain
{
    public class DapperDAL : IDAL
    {
        private string prov;
        private string constr;
        private IDbConnection GetConnection()
        {
            IDbConnection con = DbProviderFactories.GetFactory(prov).CreateConnection();
            con.ConnectionString = constr;
            con.Open();
            return con;
        }
        public DapperDAL(string prov, string constr)
        {
            this.prov = prov;
            this.constr = constr;
        }
        public T1 GetOneByPK(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                return con.QueryFirst<T1>("SELECT f1,f2 FROM t1 WHERE f1 = @f1", new { f1 = f1 });
            }
        }
        public IList<T1> GetAll()
        {
            using(IDbConnection con = GetConnection())
            {
                return con.Query<T1>("SELECT f1,f2 FROM t1").ToList();
            }
        }
        public IList<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            using(IDbConnection con = GetConnection())
            {
                return con.Query<T1>("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", new { f1_start = f1_start, f1_end = f1_end }).ToList();
            }
        }
        public IList<T1> GetContainingF2(string f2)
        {
            using(IDbConnection con = GetConnection())
            {
                return con.Query<T1>("SELECT f1, f2 FROM t1 WHERE f2 LIKE @f2", new { f2 = "%" + f2 + "%" }).ToList();
            }
        }
        public void SaveChanges(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Execute("UPDATE t1 SET f2 = @f2 WHERE f1 = @f1", new { f1 = o.F1, f2 = o.F2 });
            }
        }
        public void SaveNew(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Execute("INSERT INTO t1(f1,f2) VALUES(@f1,@f2)", new { f1 = o.F1, f2 = o.F2 });
            }
        }
        public void Remove(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Execute("DELETE FROM t1 WHERE f1 = @f1", new { f1 = f1 });
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(IList<T1> lst)
        {
            Console.Write("{");
            for (int i = 0; i < lst.Count; i++)
            {
                if (i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Test(string prov, string constr)
        {
            IDAL dal = new DAL.DapperPlain.DapperDAL(prov, constr);
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
        }
        public static void Main(string[] args)
        {
            Test("System.Data.SqlClient", @"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Test("MySql.Data.MySqlClient", "Server=localhost;Database=Test;User Id=root;Password=");
        }
    }
}

VB.NET and Dapper is so rare a combination that I will not show any example.

Contrib

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;

using Dapper;
using Dapper.Contrib.Extensions;

namespace DAL
{
    [Table("t1")]
    public class T1
    {
        [ExplicitKey]
        public int F1 { get; set; }
        public string F2 { get; set; }
        public T1() : this(0, "")
        {
        }
        public T1(int f1, string f2)
        {
            F1 = f1;
            F2 = f2;
        }
        public override string ToString()
        {
            return string.Format("[{0},{1}]", F1, F2);
        }
    }
    public interface IDAL
    {
        T1 GetOneByPK(int f1);
        IList<T1> GetAll();
        IList<T1> GetRangeOfPK(int f1_start, int f1_end);
        IList<T1> GetContainingF2(string f2);
        void SaveChanges(T1 o);
        void SaveNew(T1 o);
        void Remove(int f1);
    }
}

namespace DAL.DapperContrib
{
    public class DapperDAL : IDAL
    {
        private string prov;
        private string constr;
        private IDbConnection GetConnection()
        {
            IDbConnection con = DbProviderFactories.GetFactory(prov).CreateConnection();
            con.ConnectionString = constr;
            con.Open();
            return con;
        }
        public DapperDAL(string prov, string constr)
        {
            this.prov = prov;
            this.constr = constr;
        }
        public T1 GetOneByPK(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                return con.QueryFirst<T1>("SELECT f1,f2 FROM t1 WHERE f1 = @f1", new { f1 = f1 });
            }
        }
        public IList<T1> GetAll()
        {
            using(IDbConnection con = GetConnection())
            {
                return con.Query<T1>("SELECT f1,f2 FROM t1").ToList();
            }
        }
        public IList<T1> GetRangeOfPK(int f1_start, int f1_end)
        {
            using(IDbConnection con = GetConnection())
            {
                return con.Query<T1>("SELECT f1,f2 FROM t1 WHERE f1 BETWEEN @f1_start AND @f1_end", new { f1_start = f1_start, f1_end = f1_end }).ToList();
            }
        }
        public IList<T1> GetContainingF2(string f2)
        {
            using(IDbConnection con = GetConnection())
            {
                return con.Query<T1>("SELECT f1, f2 FROM t1 WHERE f2 LIKE @f2", new { f2 = "%" + f2 + "%" }).ToList();
            }
        }
        public void SaveChanges(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Update(o);
            }
        }
        public void SaveNew(T1 o)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Insert(o);
            }
        }
        public void Remove(int f1)
        {
            using(IDbConnection con = GetConnection())
            {
                con.Delete(new T1(f1, ""));
            }
        }
    }
}

namespace Demo
{
    using DAL;
    public class Program
    {
        public static void UpdateF2(IDAL dal, int f1, string f2)
        {
            T1 o = dal.GetOneByPK(f1);
            o.F2 = f2;
            dal.SaveChanges(o);
        }
        public static void Print(IList<T1> lst)
        {
            Console.Write("{");
            for (int i = 0; i < lst.Count; i++)
            {
                if (i > 0) Console.Write(",");
                Console.Write(lst[i]);
            }
            Console.WriteLine("}");
        }
        public static void Test(string prov, string constr)
        {
            IDAL dal = new DAL.DapperContrib.DapperDAL(prov, constr);
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BBB");
            Console.WriteLine(dal.GetOneByPK(2));
            UpdateF2(dal, 2, "BB");
            Console.WriteLine(dal.GetOneByPK(2));
            Print(dal.GetAll());
            Print(dal.GetRangeOfPK(2, 4));
            Print(dal.GetContainingF2("B"));
            dal.SaveNew(new T1(999, "XXX"));
            Print(dal.GetAll());
            dal.Remove(999);
            Print(dal.GetAll());
        }
        public static void Main(string[] args)
        {
            Test("System.Data.SqlClient", @"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test");
            Test("MySql.Data.MySqlClient", "Server=localhost;Database=Test;User Id=root;Password=");
        }
    }
}

VB.NET and Dapper is so rare a combination that I will not show any example.

Calling stored procedures:

The usage of stored procedures are not universally considered a good idea. But the reality is that they are sometimes used. Especially in Oracle DB and MS SQLServer environments.

The basic API for accessing stored procedures is the same as for using plain SQL statements with parameters, but stored procedures do come with a few special features that require special handling in the API.

Two such features are:

The following example illustrates how to handle that.

Stored procedures (for MS SQLServer):

CREATE PROCEDURE usp_multi @arg INTEGER
AS
BEGIN
    SELECT @arg+1 AS v
    SELECT 2*@arg AS v
END;
GO
CREATE PROCEDURE usp_return @inarg INTEGER, @outarg INTEGER OUT
AS
BEGIN
    SELECT @inarg+1 AS v
    SELECT @outarg = @inarg+2 
    RETURN @inarg+3
END;
GO

Yes - they are trivial, but they will illustrate the points just fine.

Code:

using System;
using System.Data;
using System.Data.SqlClient;

namespace SPUDF
{
    public class Program
    {
        private static void TestMultiResultSets()
        {
            using(SqlConnection con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test"))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("usp_multi", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter arg = new SqlParameter("@arg", SqlDbType.Int);
                    cmd.Parameters.Add(arg);
                    cmd.Parameters["@arg"].Value = 123;
                    using(SqlDataReader rdr = cmd.ExecuteReader()) {
                        while(rdr.Read())
                        {
                            Console.WriteLine((int)rdr["v"]);
                        }
                        rdr.NextResult(); // switch from first to second result set
                        while(rdr.Read())
                        {
                            Console.WriteLine((int)rdr["v"]);
                        }
                    }
                }
            }
        }
        private static void TestMultiReturnTypes()
        {
            using(SqlConnection con = new SqlConnection(@"Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test"))
            {
                con.Open();
                using(SqlCommand cmd = new SqlCommand("usp_return", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    SqlParameter inarg = new SqlParameter("@inarg", SqlDbType.Int); // in argument
                    inarg.Direction = ParameterDirection.Input;
                    cmd.Parameters.Add(inarg);
                    SqlParameter outarg = new SqlParameter("@outarg", SqlDbType.Int); // out argument
                    outarg.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(outarg);
                    SqlParameter retval = new SqlParameter("@retval", SqlDbType.Int); // return value
                    retval.Direction = ParameterDirection.ReturnValue;
                    cmd.Parameters.Add(retval);
                    cmd.Parameters["@inarg"].Value = 123;
                    using(SqlDataReader rdr = cmd.ExecuteReader()) {
                        while(rdr.Read())
                        {
                            Console.WriteLine((int)rdr["v"]);
                        }
                    }
                    Console.WriteLine("output arg = " + (int)cmd.Parameters["@outarg"].Value);
                    Console.WriteLine("return value = " + (int)cmd.Parameters["@retval"].Value);
                }
            }
        }
        public static void Main(string[] args)
        {
            TestMultiResultSets();
            TestMultiReturnTypes();
        }
    }
}
Imports System
Imports System.Data
Imports System.Data.SqlClient

Namespace SPUDF
    Public Class Program
        Private Shared Sub TestMultiResultSets()
            Using con As New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
                con.Open()
                Using cmd As New SqlCommand("usp_multi", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim arg As New SqlParameter("@arg", SqlDbType.Int)
                    cmd.Parameters.Add(arg)
                    cmd.Parameters("@arg").Value = 123
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            Console.WriteLine(CInt(rdr("v")))
                        End While
                        rdr.NextResult() ' switch from first to second result set
                        While rdr.Read()
                            Console.WriteLine(CInt(rdr("v")))
                        End While
                    End Using
                End Using
            End Using
        End Sub
        Private Shared Sub TestMultiReturnTypes()
            Using con As New SqlConnection("Server=ARNEPC4\SQLEXPRESS;Integrated Security=true;Database=Test")
                con.Open()
                Using cmd As New SqlCommand("usp_return", con)
                    cmd.CommandType = CommandType.StoredProcedure
                    Dim inarg As New SqlParameter("@inarg", SqlDbType.Int) ' in argument
                    inarg.Direction = ParameterDirection.Input
                    cmd.Parameters.Add(inarg)
                    Dim outarg As New SqlParameter("@outarg", SqlDbType.Int) ' out argument
                    outarg.Direction = ParameterDirection.Output
                    cmd.Parameters.Add(outarg)
                    Dim retval As New SqlParameter("@retval", SqlDbType.Int) ' return value
                    retval.Direction = ParameterDirection.ReturnValue
                    cmd.Parameters.Add(retval)
                    cmd.Parameters("@inarg").Value = 123
                    Using rdr As SqlDataReader = cmd.ExecuteReader()
                        While rdr.Read()
                            Console.WriteLine(CInt(rdr("v")))
                        End While
                    End Using
                    Console.WriteLine("output arg = " & CInt(cmd.Parameters("@outarg").Value))
                    Console.WriteLine("return value = " & CInt(cmd.Parameters("@retval").Value))
                End Using
            End Using
        End Sub
        Public Shared Sub Main(args As String())
            TestMultiResultSets()
            TestMultiReturnTypes()
        End Sub
    End Class
End Namespace

CLR (C#) SP and UDF

Everybody knows that MS SQL Server allows for SP (Stored Procedures) and UDF (User Defined Dunctions) in T-SQL.

Relative few are aware that MS SQL Server allows for CLR SP and UDF aka SP and UDF written in C# (or VB.NET).

This can be very conveneient. SQL and T-SQL is a great language for doing relational operations. SQL and T-SQL is *NOT* a great language for doing math, text manpulation and advanced logic in. C# on the other hand is an excellent language for those tasks.

Replacing functionality unsuited for SQL and T-SQL with same functionality in C# can sometimes result in much cleaner, more readable and more maintainable code.

CLR example UDF's:

using System;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;

namespace UDF
{
    public class Util
    {
        [SqlFunction]
        public static SqlString DateTimeFormat(DateTime dt, string fmt)
        {
            return dt.ToString(fmt);
        }
        [SqlFunction]
        public static SqlString RegexReplace(string s, string pat, string rpl)
        {
            return Regex.Replace(s, pat, rpl);
        }
    }
}

CLR example SP:

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;


namespace SP
{
    public class Demo
    {
        [SqlProcedure]
        public static void Test(int arg)
        {
            SqlDataRecord rec = new SqlDataRecord(new SqlMetaData("f1", SqlDbType.Int), new SqlMetaData("f2", SqlDbType.VarChar, 50));
            SqlContext.Pipe.SendResultsStart(rec);
            for(int i = arg; i < arg + 10; i++) 
            {
                rec.SetInt32(0, i + 1);
                rec.SetString(1, new string((char)('A' + i), i + 1));
                SqlContext.Pipe.SendResultsRow(rec);
            }
            SqlContext.Pipe.SendResultsEnd();    
        }
    }
}

For real code some additional code to handle NULL input, exceptions etc. should definitely be added.

SQL to load:

CREATE ASSEMBLY UDFUtil FROM 'C:\Work\SPUDF\UDFUtil.dll' WITH PERMISSION_SET = SAFE;
GO
CREATE ASSEMBLY SPDemo FROM 'C:\Work\SPUDF\SPDemo.dll' WITH PERMISSION_SET = SAFE;
GO
CREATE FUNCTION DateTimeFormat(@dt DATETIME, @fmt NVARCHAR(50))
RETURNS NVARCHAR(50)
AS
EXTERNAL NAME UDFUtil.[UDF.Util].DateTimeFormat;
GO
CREATE FUNCTION RegexReplace(@s NVARCHAR(255), @pat NVARCHAR(50), @rpl NVARCHAR(50))
RETURNS NVARCHAR(255)
AS
EXTERNAL NAME UDFUtil.[UDF.Util].RegexReplace;
GO
CREATE PROCEDURE SPDemo(@arg INTEGER)
AS
EXTERNAL NAME SPDemo.[SP.Demo].Test;
GO

Demo:

1> SELECT dbo.DateTimeFormat(GETDATE(),'dd-MM-yyyy HH:mm');
2> SELECT dbo.DateTimeFormat(GETDATE(),'dd-MMM-yyyy HH:mm');
3> SELECT dbo.DateTimeFormat(GETDATE(),'yyyyMMddHHmm');
4> GO

 --------------------------------------------------
 10-08-2018 20:17

(1 row affected)

 --------------------------------------------------
 10-Aug-2018 20:17

(1 row affected)

 --------------------------------------------------
 201808102017

(1 row affected)
1> SELECT dbo.RegexReplace('<all><one a=''1'' b=''11''/><one a=''2'' b=''22''/></all>','<one a=''(\d+)'' b=''(\d+)''/>','<one><a>$1</a><b>$2</b></one>');
2> GO

 -------------------------------------------------------------------------------
 <all><one><a>1</a><b>11</b></one><one><a>2</a><b>22</b></one></all>

(1 row affected)
1> EXEC dbo.SPDemo 3;
2> GO
 f1          f2
 ----------- --------------------------------------------------
           4 DDDD
           5 EEEEE
           6 FFFFFF
           7 GGGGGGG
           8 HHHHHHHH
           9 IIIIIIIII
          10 JJJJJJJJJJ
          11 KKKKKKKKKKK
          12 LLLLLLLLLLLL
          13 MMMMMMMMMMMMM

(10 rows affected)

SQL to unload:

DROP FUNCTION DateTimeFormat;
DROP FUNCTION RegexReplace;
DROP PROCEDURE SPDemo;
GO
DROP ASSEMBLY UDFUtil;
DROP ASSEMBLY SPDemo;
GO

NOTE: both the UDF and the SP can be used in any context:

NoSQL:

All of the above are for relational (SQL) databases. For NoSQL databases see:

Article history:

Version Date Description
1.0 September 25th 2016 Initial version
1.1 October 7th 2016 Add content overview
1.2 August 8th 2018 Add section on calling stored procedures
1.3 August 12th 2018 Add section on CLR SP and UDF
1.4 September 1st 2019 Add section on Dapper
1.5 January 24th 2021 Add sections on EF and NHibernate join

Other articles:

See list of all articles here

Comments:

Please send comments to Arne Vajhøj