Movies Project

In this section, a Movie model class and a Db database session class will be added into Movies project.

Project Setup

Make the following changes to Movies project:

Delete Class1.cs (or Class1.vb if you're using VB.Net) from the project.
Add NuGet Package DevZest.Data.SqlServer to this project.

Add Movie Class

Step 1. Add new class

Add new class Movie into project Movies, inherits from Model:

C#
VB.Net

using DevZest.Data;

namespace Movies
{
    public class Movie : Model
    {
    }
}

Imports DevZest.Data

Public Class Movie
    Inherits Model

End Class

The class can be viewed in Model Visualizer tool window:

Note

You can show Model Visualizer tool window by clicking menu "View" -> "Other Windows" -> "Model Visualizer" in Visual Studio.

Step 2. Add ID field

In Model Visualizer tool window, click the left top button, the following code snippet will be inserted:

C#
VB.Net

Tabbing through the code snippet to change the property name to ID and property type to _Int32. When done, press ESC to quit code snippet editing. You now have a readonly property ID as type _Int32, with a compile-time warning Missing registration for property 'ID'. You can fix this warning by moving the caret to the property name, and pressing CTRL-. in Visual Studio:

C#
VB.Net

Select Add Mounter _ID in the dropdown menu, RDO.Tools will automatically insert following code to register the property:

C#
VB.Net

...
public static readonly Mounter<_Int32> _ID = RegisterColumn((Movie _) => _.ID);
...

...
Public Shared ReadOnly _ID As Mounter(Of _Int32) = RegisterColumn(Function(x As Movie) x.ID)
...

Step 3. Annotate ID field

In Model Visualizer tool window, right click ID field, available annotations for current field will be displayed as context menu:

Click Identity, field ID will be annotated with Identity attribute:

C#
VB.Net

using DevZest.Data;
using DevZest.Data.Annotations;

namespace Movies
{
    public class Movie : Model
    {
        public static readonly Mounter<_Int32> _ID = RegisterColumn((Movie _) => _.ID);

        [Identity]
        public _Int32 ID { get; private set; }
    }
}

Imports DevZest.Data
Imports DevZest.Data.Annotations

Public Class Movie
    Inherits Model

    Public Shared ReadOnly _ID As Mounter(Of _Int32) = RegisterColumn(Function(x As Movie) x.ID)

    Private m_ID As _Int32
    <Identity>
    Public Property ID As _Int32
        Get
            Return m_ID
        End Get
        Private Set
            m_ID = Value
        End Set
    End Property
End Class

Step 4. Add primary key

In Model Visualizer tool window, click the left top dropdown button, then click Add Primary Key... from the drop down menu:

The following dialog will be displayed:

Field ID is detected automatically. Click OK button in the dialog, the following code will be generated automatically:

C#
VB.Net

...
public class Movie : Model<Movie.PK>
{
    public sealed class PK : CandidateKey
    {
        public PK(_Int32 id) : base(id)
        {
        }
    }

    protected sealed override PK CreatePrimaryKey()
    {
        return new PK(ID);
    }

    public class Key : Key<PK>
    {
        static Key()
        {
            Register((Key _) => _.ID, _ID);
        }

        protected sealed override PK CreatePrimaryKey()
        {
            return new PK(ID);
        }

        public _Int32 ID { get; private set; }
    }
...
}

Public Class Movie
    Inherits Model(Of Movie.PK)

    Public NotInheritable Class PK
        Inherits CandidateKey

        Public Sub New(id As _Int32)
            MyBase.New(id)
        End Sub
    End Class

    Protected NotOverridable Overrides Function CreatePrimaryKey() As PK
        Return New PK(ID)
    End Function

    Public Class Key
        Inherits Key(Of PK)

        Shared Sub New()
            Register(Function(x As Key) x.ID, _ID)
        End Sub

        Protected NotOverridable Overrides Function CreatePrimaryKey() As PK
            Return New PK(ID)
        End Function

        Private m_ID As _Int32

        Public Property ID As _Int32
            Get
                Return m_ID
            End Get
            Private Set
                m_ID = Value
            End Set
        End Property
    End Class
    ...
End Class

Step 5. Add other fields

Add following fields using steps described previously:

C#
VB.Net

Name	Type	Annotation(s)
Title	_String	`[StringLength(60, MinimumLength = 3)]`, `[Required]`, `[SqlNVarChar(60)]`
ReleaseDate	_DateTime	`Display(Name = "Release Date")]`, `[SqlDate]`, `[Required]`
Genre	_String	`[RegularExpression(@"^[A-Z]+[a-zA-Z""'\s-]*$")]`, `[Required]`, `[StringLength(30)]`, `[SqlNVarChar(30)]`
Price	_Decimal	`[SqlMoney]`, `[Required]`

Name	Type	Annotation(s)
Title	_String	`<StringLength(60, MinimumLength:=3)>`, `<Required>`, `<SqlNVarChar(60)>`
ReleaseDate	_DateTime	`<Display(Name:="Release Date")>`, `<SqlDate>`, `<Required>`
Genre	_String	`<RegularExpression("^[A-Z]+[a-zA-Z""'\s-]*$")>`, `<Required>`, `<StringLength(30)>`, `<SqlNVarChar(30)>`
Price	_Decimal	`<SqlMoney>`, `<Required>`

Note

Please keep in mind Model Visualizer is your best friend. It provides much more features which are not demonstrated in this tutorial for simplicity.

using DevZest.Data;
using DevZest.Data.Annotations;
using DevZest.Data.SqlServer;

namespace Movies
{
    public class Movie : Model<Movie.PK>
    {
        public sealed class PK : CandidateKey
        {
            public PK(_Int32 id) : base(id)
            {
            }
        }

        protected sealed override PK CreatePrimaryKey()
        {
            return new PK(ID);
        }

        public class Key : Key<PK>
        {
            static Key()
            {
                Register((Key _) => _.ID, _ID);
            }

            protected sealed override PK CreatePrimaryKey()
            {
                return new PK(ID);
            }

            public _Int32 ID { get; private set; }
        }

        public static readonly Mounter<_Int32> _ID = RegisterColumn((Movie _) => _.ID);
        public static readonly Mounter<_String> _Title = RegisterColumn((Movie _) => _.Title);
        public static readonly Mounter<_DateTime> _ReleaseDate = RegisterColumn((Movie _) => _.ReleaseDate);
        public static readonly Mounter<_String> _Genre = RegisterColumn((Movie _) => _.Genre);
        public static readonly Mounter<_Decimal> _Price = RegisterColumn((Movie _) => _.Price);

        [Identity]
        public _Int32 ID { get; private set; }

        [StringLength(60, MinimumLength = 3)]
        [Required]
        [SqlNVarChar(60)]
        public _String Title { get; private set; }

        [Display(Name = "Release Date")]
        [SqlDate]
        [Required]
        public _DateTime ReleaseDate { get; private set; }

        [RegularExpression(@"^[A-Z]+[a-zA-Z""'\s-]*$")]
        [Required]
        [StringLength(30)]
        [SqlNVarChar(30)]
        public _String Genre { get; private set; }

        [SqlMoney]
        [Required]
        public _Decimal Price { get; private set; }
    }
}

Imports DevZest.Data
Imports DevZest.Data.Annotations
Imports DevZest.Data.SqlServer

Public Class Movie
    Inherits Model(Of Movie.PK)

    Public NotInheritable Class PK
        Inherits CandidateKey

        Public Sub New(id As _Int32)
            MyBase.New(id)
        End Sub
    End Class

    Protected NotOverridable Overrides Function CreatePrimaryKey() As PK
        Return New PK(ID)
    End Function

    Public Class Key
        Inherits Key(Of PK)

        Shared Sub New()
            Register(Function(x As Key) x.ID, _ID)
        End Sub

        Protected NotOverridable Overrides Function CreatePrimaryKey() As PK
            Return New PK(ID)
        End Function

        Private m_ID As _Int32

        Public Property ID As _Int32
            Get
                Return m_ID
            End Get
            Private Set
                m_ID = Value
            End Set
        End Property
    End Class

    Public Shared ReadOnly _ID As Mounter(Of _Int32) = RegisterColumn(Function(x As Movie) x.ID)
    Public Shared ReadOnly _Title As Mounter(Of _String) = RegisterColumn(Function(x As Movie) x.Title)
    Public Shared ReadOnly _ReleaseDate As Mounter(Of _DateTime) = RegisterColumn(Function(x As Movie) x.ReleaseDate)
    Public Shared ReadOnly _Genre As Mounter(Of _String) = RegisterColumn(Function(x As Movie) x.Genre)
    Public Shared ReadOnly _Price As Mounter(Of _Decimal) = RegisterColumn(Function(x As Movie) x.Price)

    Private m_ID As _Int32
    <Identity>
    Public Property ID As _Int32
        Get
            Return m_ID
        End Get
        Private Set
            m_ID = Value
        End Set
    End Property

    Private m_Title As _String
    <StringLength(60, MinimumLength:=3)>
    <Required>
    <SqlNVarChar(60)>
    Public Property Title As _String
        Get
            Return m_Title
        End Get
        Private Set
            m_Title = Value
        End Set
    End Property

    Private m_ReleaseDate As _DateTime
    <Display(Name:="Release Date")>
    <SqlDate>
    <Required>
    Public Property ReleaseDate As _DateTime
        Get
            Return m_ReleaseDate
        End Get
        Private Set
            m_ReleaseDate = Value
        End Set
    End Property

    Private m_Genre As _String
    <RegularExpression("^[A-Z]+[a-zA-Z""'\s-]*$")>
    <Required>
    <StringLength(30)>
    <SqlNVarChar(30)>
    Public Property Genre As _String
        Get
            Return m_Genre
        End Get
        Private Set
            m_Genre = Value
        End Set
    End Property

    Private m_Price As _Decimal
    <SqlMoney>
    <Required>
    Public Property Price As _Decimal
        Get
            Return m_Price
        End Get
        Private Set
            m_Price = Value
        End Set
    End Property
End Class

Add Db Class

Add new class

Add new class Db into project Movies, inherits from SqlSession:

C#
VB.Net

using DevZest.Data.SqlServer;
using System.Data.SqlClient;

namespace Movies
{
    public partial class Db : SqlSession
    {
        public Db(string connectionString)
            : this(new SqlConnection(connectionString))
        {
        }

        public Db(SqlConnection sqlConnection)
            : base(sqlConnection)
        {
        }
    }
}

Imports System.Data.SqlClient
Imports DevZest.Data.SqlServer

Partial Public Class Db
    Inherits SqlSession
    Public Sub New(connectionString As String)
        Me.New(New SqlConnection(connectionString))
    End Sub

    Public Sub New(sqlConnection As SqlConnection)
        MyBase.New(sqlConnection)
    End Sub
End Class

The class can be viewed in Db Visualizer tool window:

Note

You can show Db Visualizer tool window by clicking menu "View" -> "Other Windows" -> "Db Visualizer" in Visual Studio.

Add table property

In Db Visualizer tool window, click the left top button, the following dialog will be displayed:

Select Movie from Model: combo box, then click button OK, a Movie property will be generated in class Db:

C#
VB.Net

using DevZest.Data;
using DevZest.Data.SqlServer;
using System.Data.SqlClient;

namespace Movies
{
    public partial class Db : SqlSession
    {
        public Db(string connectionString)
            : this(new SqlConnection(connectionString))
        {
        }

        public Db(SqlConnection sqlConnection)
            : base(sqlConnection)
        {
        }

        private DbTable<Movie> _movie;
        public DbTable<Movie> Movie
        {
            get
            {
                return GetTable(ref _movie);
            }
        }
    }
}

Imports DevZest.Data
Imports System.Data.SqlClient
Imports DevZest.Data.SqlServer

Partial Public Class Db
    Inherits SqlSession
    Public Sub New(connectionString As String)
        Me.New(New SqlConnection(connectionString))
    End Sub

    Public Sub New(sqlConnection As SqlConnection)
        MyBase.New(sqlConnection)
    End Sub

    Private m_Movie As DbTable(Of Movie)
    Public ReadOnly Property Movie As DbTable(Of Movie)
        Get
            Return GetTable(m_Movie)
        End Get
    End Property
End Class

Note

Please keep in mind Db Visualizer is your best friend. It provides additional features which are not demonstrated in this tutorial for simplicity.