Implementation of agile database CI

We can see that everyone is very concerned about the continuous integration of databases in CI! In order to provide a little complete information earlier and let everyone have actual operations, I adopted another method to increase the speed:

The methods described in this article are as follows:. NET DeliveryThe implementation in this book is not exactly the same in my implementation, and the materials have not yet been sorted out. The key tool I mentioned in the previous article on CI is the database comparison tool, in my implementation, I believe you have read the selection. (If I want to introduce it here, I think its implementation is not elegant enough. NET open-source tool DaBCoS is not mature yet and its development is not complete yet. The tool provided by Red gate is a product-level tool, which is excellent, the trial version of MDAC2.8 is required for installation. (The reason why mdac2.8 is not installed is that the tool cannot be installed after MDAC2.8 is installed. I hope you can ),. NET Delivery is a redgate-based custom Nant Task, which is very convenient. In addition, you 'd better understand and implement CI. You can refer to this article (thanks to coolbug ).

I will provide. NET Delivery-related resources later, but I may not be able to find a complete ebook. If I can find it, I may wish to leave a link!
I will not post the code in this article. Don't worry! I will provide download connections later (or waste the space in the blog Park), and people will have more complete code!

The first step is to complete the Nant task for executing SQL statements (this does not require the red-gate tool ):
ManualDBTask. cs
ObjectComparer. cs (it is very important to implement the execution sequence of SQL scripts in integration)

Expand the red-gate tool to compare database differences and synchronize Nant tasks generated by scripts:
(Note: To download the red-gate trial version, if anyone cares about my implementation method, let's talk about it later !)
DBInfo. cs
AutoDBTask. cs

The key tasks have been completed. The following are the use of CI tools such as Nant and CCNET. The following build files are believed to have a certain CI Foundation. You can modify the configuration path slightly, if you have any questions, we will discuss:
Series build xml files (download below)

An application in my implementation may be useful to you. I use SqlDmo (a com component) to access a specific database and generate the required script, the program code is as follows (you can Refctor to meet your needs ):

Database script generation
Using System;
Using System. IO;
Using System. Text;

Namespace ScriptMSDB
{
Public class ScriptDB
{
[STAThread]
Public static int Main (string [] args)
{
If (args. Length! = 3)
{
Console. WriteLine (@ "\ nUsage:> ScriptMSDB database-server database-name output-dir ");
Return 1;
}

String serverName = args [0];
String databaseName = args [1];
String outputPath = args [2];

Try
{
If (! System. IO. Directory. Exists (outputPath ))
{
Directory. CreateDirectory (outputPath );
}
}
Catch (Exception exc)
{
Console. WriteLine (exc. ToString ());
Return 1;
}

SQLDMO. SQLServer srv = new SQLDMO. SQLServerClass ();
Srv. LoginSecure = true; // refactor to use uid and pwd
Srv. Connect (serverName, null, null );
SQLDMO. Database database = null;
Foreach (SQLDMO. Database db in srv. Databases)
{
If (db. Name = databaseName)
{
Database = db;
Break;
}
}

SQLDMO. SQLDMO_SCRIPT_TYPE param = SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_Drops |
SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeIfNotExists |
SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_IncludeHeaders |
SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default |
SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_PrimaryKey |
SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Checks |
SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_Defaults |
SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_DRI_UniqueKeys;
// | SQLDMO. SQLDMO_SCRIPT_TYPE.SQLDMOScript_ToFileOnly;

StringBuilder sb = new StringBuilder ();

Try
{
Foreach (SQLDMO. User u in database. Users)
{
If (! U. SystemObject)
{
Sb. Append (u. Script (param, null, SQLDMO. SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_NoCollation ));
}
}

Foreach (SQLDMO. Table t in database. Tables)
{
If (! T. SystemObject)
{
Sb. Append (t. Script (param, null, null, SQLDMO. SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_NoCollation ));
// T. Script (param, Path. Combine (outputPath, t. Name + ". SQL"), null, SQLDMO. SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_NoCollation );

// Console. WriteLine ("Processing" + t. Name );
}
}

Foreach (SQLDMO. StoredProcedure sp in database. StoredProcedures)
{
If (! Sp. SystemObject)
{
Sb. Append (sp. Script (param, null, SQLDMO. SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_NoCollation ));
}
}

Foreach (SQLDMO. View v in database. Views)
{
If (! V. SystemObject)
{
Sb. Append (v. Script (param, null, SQLDMO. SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_NoCollation ));
}
}

Foreach (SQLDMO. UserDefinedDatatype ut in database. UserDefinedDatatypes)
{
Sb. Append (ut. GenerateSQL (database ));
}

// Foreach (SQLDMO. UserDefinedFunction)
If (! OutputPath. EndsWith (@"\"))
OutputPath + = @"\";

If (File. Exists (outputPath + "Create-" + databaseName + ". SQL "))
{
FileInfo f = new FileInfo (outputPath + "Create-" + databaseName + ". SQL ");
F. Attributes = FileAttributes. Normal;
F. Delete ();
}

StreamWriter sw = new StreamWriter (outputPath + "Create-" + databaseName + ". SQL ");
Try
{
Sw. Write (sb. ToString ());
}
Finally
{
Sw. Close ();
}

Srv. DisConnect ();
Return 0;
}
Catch (Exception exc)
{
Console. WriteLine (exc. ToString ());

Srv. DisConnect ();
Return 1;
}
}
}
}

Do not forget to add a reference to sqldmo (add reference in vs.net, select Microsoft SQLDMO Object Library in com), and then compile it for use in Nant.

Download resources:
Download the. NET Delivery database integration chapter.
. NET Delivery source code

The code above can be found in chapter8 In the download. Note that if you do not use the tool of another version (I use the tool configured in my environment and the version is newer ), be careful when modifying the build File!

Refer to the following sections to learn the deployment methods and their respective advantages and disadvantages. It is also very important to implement integration in practice. In addition, this section describes only one case of database integration CI. In actual ECI, there may be more additional factors to consider, but this implementation should at least let you understand and accept its advantages. hope you can share your experiences!