Updated Dec 06, 2008 by timascott
Labels:	Featured

AuthoringExample

#code samples

DbVerse Authoring Code Samples

All code lives in a single class that derives from MethodsBase. I prefer to divide this class into three code files, as follows.

Methods.cs

In this file I put all public methods. These are the methods exposed to the user -- via the console, as command line parameters, or via the Windows UI.

//The Server and Db objects represent the server and database.  These objects are
//constructed based on configuration settings (either connection string or
//discreet settings).

public partial class Methods : MethodsBase
{
    private const string defNowName = "defNow";

    public void CreateDatabase()
    {
        Server.CreateDatabase(databaseName);
    }

    public void RecreateSeedDatabase()
    {
        if (Server.HasDatabase(databaseName))
        {
            Db.Drop();
        }
        CreateDatabase();
        PopulateSeedDatabase();
    }

    public void PopulateSeedDatabase()
    {
        Db.CreateDefault(defNowName, "(getdate())");
        CreateSchema();
        Db.GenerateRelations();
        AddSeedData();
       //a method on the base class that runs all Update methods in the proper order
        ApplyAllUpdates();
    }

    public void DropDatabase()
    {
        Db.Drop();
    }

    public void ClearDatabase()
    {
        Db.Clear();
    }

    public void ApplyAllNewUpdates()
    {
        //a method on the base class that runs all Update methods that have not yet
        //been applied to the particular database
        ApplyNewUpdates();
    }
}

Methods.Initialize.cs

In this file I put the methods responsible for initializing the database.

One thing to note in the following example is, AddTable, AddColumn and similar methods have many overloads. Overloads with few parameters follow DbVerse conventions. More parameters give you greater control. (NOTE: For ultimate control, all DbVerse objects expose the SMO objects they wrap giving you ultimate control.)

public partial class Methods
{
    private void CreateSchema()
    {
        Db.AddTable("Person")
            .AddColumn("FirstName", DataType.VarChar(100))
            .AddColumn("LastName", DataType.VarChar(100))
            .AddColumn("CompanyID", DataType.UniqueIdentifier, "Company")
            .AddIndex("FirstName", "LastName");

        Db.AddTable("Company")
            .AddColumn("Name", DataType.VarChar(100));

        Db.AddTable("ProductOrder")
            .AddColumn("OrderNumber", DataType.Int)
            .AddColumn("CustomerID", DataType.UniqueIdentifier, "Company", "CompanyID")
            .AddColumn("Quantity", DataType.Int)
            .AddColumn("Price", DataType.Money)
            .AddColumn("Cost", DataType.Money)
            .AddColumn("TakenBy", DataType.UniqueIdentifier, "Person", "PersonID")
            .AddColumnWithDefaultValue("Priority", DataType.Bit, false, "0")
            .AddColumn("DateCreated", DataType.DateTime, false, defNowName)
            .AddColumn("TypeCode", DataType.Int, "OrderType")
            .AddUniqueIndex("OrderNumber");

        Db.AddTable("OrderType", "OrderTypeID", DataType.Int, false)
            .AddColumn("Name", DataType.VarChar(20))
            .AddColumn("Sort", DataType.Int);
    }

    private void AddSeedData()
    {
        Db.Table("OrderType")
            .Insert("1, 'Submitted', 1")
            .Insert("2, 'InProcess', 2")
            .Insert("3, 'OnHold', 3")
            .Insert("4, 'Completed', 4")
            .Insert("5, 'Cancelled', 5");
    }
}

Methods.Update.cs

In this file I put all Update methods. These methods must be numbered in the order in which they will run. Putting them together in one code file makes it easier to keep track of that numbering.

public partial class Methods
{
    [Update(0, "Tim", "2/19/2008")]
    public void AddAppSettingsTable()
    {
        Db.AddTable("AppSettings")
            .AddColumn("Name", DataType.VarChar(50))
            .AddColumn("Value", DataType.VarCharMax);
    }

    [Update(1, "Tim", "2/19/2008")]
    public void AddActiveOrdersByDateRangeStoredProc()
    {
        Db.AddStoredProcedure("sp_ActiveOrdersByDateRange",
@"SELECT * FROM Orders 
WHERE 
OrderType IN('Submitted', 'InProcess') AND
DateCreated > @startDate AND
DateCreated < @endDate",
              new DetachedParameter("startDate", DataType.DateTime),
              new DetachedParameter("endDate", DataType.DateTime));
    }

    [Update(2, "Tim", "2/24/2008")]
    public void ConvertOrderNumbers()
    {
        Db.ExecuteScript("convert_order_numbers_to_new_scheme");
    }
}