Database object creation

Now you can include the code that creates the database objects in your release project

One of the key features of version 1.5 of this library is the DBQuery.Create.xxx statements.
It is now just easy to build Tables, Views, Indexes and Stored Procedures against any supporting database engine.

There are a lot of examples in the unit tests included in the source code, but as a starter for ten...

Creating a table

   DBQuery createCourse = DBQuery.Create.Table("dbo", "Course")
                     .Add("course_id", DbType.Int32, DBColumnFlags.PrimaryKey | DBColumnFlags.AutoAssign)
                     .Add("course_school_id", DbType.Int32)
                     .Add("course_name", DbType.AnsiString, 50)
                     .Add("course_desc", DbType.String, 255, DBColumnFlags.Nullable)
                     .Add("created_date", DbType.DateTime).Default(DBFunction.GetDate())
                     .Add("modified_date", DbType.DateTime).Default(DBFunction.GetDate())
                     .Constraints(
                          DBForeignKey.ForeignKey("fk_Course_2_School").Column("course_school_id")
                                              .References("Schools").Column("school_id")
                     );

    public virtual void CreateSchema(DBDatabase db)
    {
         //wrap this in a transaction or try catch block as required
         db.ExecuteNonQuery(createCourse);
         //Add anything else required

    }


And as the createCourse variable is still a valid object - it can even be generated at runtime.
I can think of a number of installation tools and applications where concatenated SQL statements could do with cleaning up.

CREATE TABLE [dbo].[Course] (
		[course_id] INT PRIMARY KEY NOT NULL IDENTITY, 
		[course_school_id] INT NOT NULL, 
		[course_name] VARCHAR(50) NOT NULL, 
		[course_desc] NVARCHAR(255) NULL, 
		[created_date] DATETIME NOT NULL DEFAULT GETDATE() , 
		[modified_date] DATETIME NOT NULL DEFAULT GETDATE() , 
		CONSTRAINT [fk_Course_2_School] FOREIGN KEY  ([course_school_id])  REFERENCES [Schools] ([school_id]) )

And once it works it's in the code, compiled and safe.

Alter? - not yet, but it's coming

Last edited Aug 15, 2012 at 4:48 PM by perceiveit, version 4

Comments

No comments yet.