public void execute(Connection conn)
{
// arguments for sqlcmd.exe utility
var args = String.Format(@" -S {0} -U {1} -P {2} -d {3} -i {4} ", conn.serverName, conn.username, conn.password, dbName, sql);
try
{
Process p = new Process();
p.StartInfo.UseShellExecute = false;
p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.FileName = "sqlcmd";
p.StartInfo.Arguments = args;
p.Start();
// waiting for exit makes this very slow
// but not waiting can cause memory overflow
string output = p.StandardOutput.ReadToEnd();
p.WaitForExit();
p.Close();
success = true;
}
catch (Exception e)
{
success = false;
exception = e.ToString();
}
finally
{
attempts++;
}
}
public ScriptDbObjects(Connection connection, string filePath, string dbName)
{
// ensure the provided path is available
if (!Directory.Exists(filePath))
{
Console.WriteLine("\nERROR: the Path \"" + filePath + "\" does not exist\n");
return;
}
// start
DateTime began = DateTime.Now;
int objectCount = 0;
List<string> databases = new List<string>();
if (dbName.Length > 0) databases.Add(dbName);
// if no database specified, script 'em all
if (databases.Count == 0) databases = getAllDatabases(connection);
foreach (string db in databases)
{
ScriptDB scr = new ScriptDB(db, connection);
scr.scriptDB(filePath);
objectCount += scr.objectCount;
}
// done
DateTime ended = DateTime.Now;
Console.WriteLine("\nProgram began " + began.ToLongTimeString() + ", ended: " + ended.ToLongTimeString());
Console.WriteLine(objectCount.ToString() + " objects scripted.\n");
}
public ScriptData(Connection conn, string dbFilesPath, string dbName, string tblName, int limit, bool fixtures, string where)
{
Console.WriteLine(String.Format(@"Generating data insert script for: {0}.dbo.{1}", dbName, tblName));
// generate data insert script as <dbFilePath>\Data\<tblName>.insert.sql
string dbDataScriptsPath = getDataScriptsPath(dbFilesPath, dbName, fixtures);
string filename = String.Format(@"{0}\{1}.insert.sql", dbDataScriptsPath, tblName);
m_table = tblName;
m_database = dbName;
m_limit_results = limit;
m_for_fixtures = fixtures;
m_where_clause = where;
m_tbl_has_identity = hasIdentity(conn, dbName, tblName);
if (m_tbl_has_identity == false) Console.WriteLine("table has no identity column !!");
ArrayList cols = columns(conn);
Console.WriteLine("got " + cols.Count + " columns ");
List<Hashtable> data = tableData(cols, conn);
Console.WriteLine("got " + data.Count + " rows ");
// write file
generateDataScript(filename, cols, data);
Console.WriteLine("Done ... \n");
}
public static string[] getTables(Connection conn, string dbName)
{
ArrayList tbls = new ArrayList();
SqlConnection sqlconn = new SqlConnection(conn.connectionString());
sqlconn.Open();
SqlCommand cmd = new SqlCommand(dbName + ".dbo.sp_tables", sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
if (
Convert.ToString(rdr["TABLE_TYPE"]) == "TABLE" &&
Convert.ToString(rdr["TABLE_OWNER"]) != "sys"
) tbls.Add(rdr["TABLE_NAME"]);
}
rdr.Close();
return tbls.ToArray(typeof(string)) as string[];
}
private void runScripts(string[] files, Connection conn, string dbName)
{
foreach (string sql in files)
{
Console.WriteLine(sql);
totalScripts++;
try
{
ScriptExecute exec = new ScriptExecute(sql, dbName, conn);
if (exec.success == false)
{
failedScripts.Add(exec);
}
}
catch (Exception e)
{
Console.WriteLine(e.InnerException);
}
}
}
// **********************************************
// run using the sqlcmd utility
private void runScripts(string dir, Connection conn, string dbName)
{
if (Directory.Exists(dir))
{
string[] sqlfiles = Directory.GetFiles(dir);
runScripts(sqlfiles, conn, dbName);
}
}
private Database createDB(Connection conn, string dbName, string dbFilePath)
{
Server svr = conn.server();
if (svr.Databases.Contains(dbName) == true)
{ // drop it like it's hot
try
{
Console.WriteLine("Database [" + dbName + "] exists. Drop it? Y/n");
string ok = Console.ReadLine();
if (ok.ToLower() == "y")
{
//svr.KillDatabase(dbName);
Console.WriteLine("Dropping [" + dbName + "]");
Database dropme = svr.Databases[dbName];
dropme.Drop();
}
else
{
return null;
}
}
catch (Exception e)
{
Console.WriteLine("Didn't drop database [" + dbName + "]");
Console.WriteLine("Database may be in use. Try this command again. Otherwise, try dropping the database manually using SQL Server Management Studio, then try again.");
//Console.WriteLine(e);
svr.KillDatabase(dbName);
return null;
}
}
// create new db
Database db = conn.database(dbName);
try
{
Console.WriteLine("Creating new database [" + dbName + "]");
db.Create();
}
catch (Exception e) // permissions?
{
Console.WriteLine("ERROR - create database failed.");
Console.WriteLine(e.InnerException);
return null;
}
return db;
}
private void createDb(Connection conn, string dbPath, string dbName)
{
// get sql files to execute in order of
// ./SchemaObjects >> Tables, Views, Functions, Procs
// ./Data & ./Fixtures
string dbFilePath = dbPath + "\\" + dbName;
if (isDatabaseDirectory(dbName, dbFilePath))
{
Database db = createDB(conn, dbName, dbFilePath);
if (db == null)
{
Console.WriteLine("\nquitting .... ");
return;
}
// TODO _ only run the permission atatements (not 'create database')
// runScripts(dbFilePath + @"\SchemaObjects", db);
runScripts(dbFilePath + @"\SchemaObjects\Tables", db);
runScripts(dbFilePath + @"\SchemaObjects\Tables\Keys", db);
//runScripts(dbFilePath + @"\SchemaObjects\Tables\Keys", db, "*.pkey.sql");
//runScripts(dbFilePath + @"\SchemaObjects\Tables\Keys", db, "*.fkey.sql");
//runScripts(dbFilePath + @"\SchemaObjects\Tables\Keys", db, "*.ukey.sql");
runScripts(dbFilePath + @"\SchemaObjects\Tables\Indexes", db);
runScripts(dbFilePath + @"\SchemaObjects\Tables\Constraints", db);
runScripts(dbFilePath + @"\SchemaObjects\Tables\Triggers", db);
runScripts(dbFilePath + @"\SchemaObjects\Views", db);
runScripts(dbFilePath + @"\SchemaObjects\Views\Indexes", db);
runScripts(dbFilePath + @"\SchemaObjects\Views\Triggers", db);
runScripts(dbFilePath + @"\SchemaObjects\Functions", db);
runScripts(dbFilePath + @"\SchemaObjects\Stored Procedures", db);
runScripts(dbFilePath + @"\Data", db);
retryFailedScripts(db);
if (with_fixtures == true)
{
Console.WriteLine("\nBuilding test data from fixtures.\n");
runScripts(dbFilePath + @"\Fixtures", db);
//retryFailedScripts(db);
}
Console.WriteLine("\n" + totalScripts + " scripts executed. " + failedScripts.Count + " failed.\n");
}
else
{
Console.WriteLine(dbFilePath + " doesn't look like is contains database object creation scripts.");
}
}
public ScriptDB(string dbName, Connection conn)
{
_database = dbName;
_connection = conn;
}
public static bool hasIdentity(Connection conn, string dbName, string tblName)
{
//var sql = String.Format(@"SELECT COUNT(*) FROM {0}.SYS.IDENTITY_COLUMNS WHERE OBJECT_NAME(OBJECT_ID) = '{1}'", dbName, tblName);
var sql = String.Format(@"USE {0} SELECT OBJECTPROPERTY(OBJECT_ID('{1}'), 'TableHasIdentity')", dbName, tblName);
SqlConnection sqlconn = new SqlConnection(conn.connectionString());
sqlconn.Open();
SqlCommand cmd = new SqlCommand(sql,sqlconn);
if ((int)cmd.ExecuteScalar() == (int)0)
return false;
else
return true;
}
public ScriptData(Connection conn, string dbFilesPath, string dbName, string tblName)
: this(conn, dbFilesPath, dbName, tblName, -1, false, "")
{
}
private List<Hashtable> tableData(ArrayList cols, Connection conn)
{
// limiting results?
string limit = "";
string order_by = ""; // if negative number for LIMIT then order descending (by first column)
if (m_limit_results != 0)
{
// get top n
limit = String.Format(@"TOP {0} ", Math.Abs(m_limit_results).ToString());
// ordering
order_by = "ORDER BY " + cols[0];
if (m_limit_results < 0) order_by += " DESC";
}
string command = String.Format(@"SELECT {0}* FROM [{1}].[dbo].[{2}] WITH(NOLOCK) {3} {4}", limit, m_database, m_table, m_where_clause, order_by);
SqlConnection sqlconn = new SqlConnection(conn.connectionString());
sqlconn.Open();
SqlCommand cmd = new SqlCommand(command, sqlconn);
SqlDataReader rdr = cmd.ExecuteReader();
List<Hashtable> rows = new List<Hashtable>();
while (rdr.Read())
{
Hashtable row = new Hashtable();
foreach (string c in cols)
{
row.Add(c, rdr[c]);
}
rows.Add(row);
}
rdr.Close();
return rows;
}
private ArrayList columns(Connection conn)
{
ArrayList cols = new ArrayList();
SqlConnection sqlconn = new SqlConnection(conn.connectionString());
sqlconn.Open();
SqlCommand cmd = new SqlCommand(m_database+".dbo.sp_columns", sqlconn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@table_name", m_table));
// execute the command
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
cols.Add(rdr["COLUMN_NAME"]);
}
rdr.Close();
return cols;
}
static void Main(string[] args)
{
/* args expected:
* <UtilityName>
* -f <path to database script(s) folder>
* -s <server instance>
* -u <username>
* -p <password>
* -d <database name>
* -t trusted connection
*
* [for scripting data]
* --table <table name>
* --limit <limit results>
* --alltables
* --fixtures [also for building database]
* --where <clause>
*/
// basic options
string svr = "";
string usr = "";
string pwd = "";
string dbs = "";
string pth = "";
bool trustedConnection = false;
// other options
string tbl = "";
int limit_results = 0;
bool all_tables = false;
bool with_fixtures = false;
string where_clause = "";
// 7+ parameters are required
if (args.Length < 8)
{
PrintInstructions();
return;
}
// first arg should be the utility required
string util = args[0];
if (
util.ToLower() != CMD_SCRIPT_OBJECTS &&
util.ToLower() != CMD_CREATE_DATABASE &&
util.ToLower() != CMD_SCRIPT_DATA
)
{
PrintInstructions();
return;
}
for (int i = 1; i < args.Length; i++)
{
switch (args[i])
{
case "-s":
svr = args[i + 1];
break;
case "-f":
pth = args[i + 1];
break;
case "-d":
dbs = args[i + 1];
break;
case "-u":
usr = args[i + 1];
break;
case "-p":
pwd = args[i + 1];
break;
case "-t":
if (pwd != "" || usr != "") Console.WriteLine(args[i] + "argument only applies if no usrname or password is set");
trustedConnection = true;
break;
case "--fixtures":
if (util.ToLower() == CMD_SCRIPT_OBJECTS) Console.WriteLine(args[i] + " argument does not apply to the ScriptDbObjects utility");
with_fixtures = true;
break;
case "--where":
if (util.ToLower() != CMD_SCRIPT_OBJECTS || with_fixtures == false) Console.WriteLine(args[i] + " argument only applies to ScriptData utility when creating fixture scripts");
where_clause = "WHERE "+args[i + 1];
break;
case "--table":
if (util.ToLower() != CMD_SCRIPT_DATA) Console.WriteLine(args[i] + " argument only applies to ScriptData utility");
tbl = args[i + 1];
break;
case "--limit":
if (util.ToLower() != CMD_SCRIPT_DATA) Console.WriteLine(args[i] + " argument only applies to ScriptData utility");
try
{
limit_results = Convert.ToInt32(args[i + 1]);
}
catch
{
Console.WriteLine("Invalid value supplied for " + args[i] + " argument");
}
break;
case "--alltables":
if (util.ToLower() != CMD_SCRIPT_DATA) Console.WriteLine(args[i] + " argument only applies to ScriptData utility");
all_tables = true;
break;
}
}
//
// all of these are required all the time
if (pwd != "" && trustedConnection || usr != "" && trustedConnection || svr == "" || pth == "")
{
PrintInstructions();
return;
}
// if creating a database - it should only be a local instance??
/*
if (util.ToLower() == CMD_CREATE_DATABASE && !svr.Contains(@"\SQLEXPRESS"))
{
Console.WriteLine("The " + util + " function should only be used on workstation installations of SQLEXPRESS.");
return;
}
*/
// if creating a database or insert script, need the db name
/*
if ((util.ToLower() == CMD_CREATE_DATABASE || util.ToLower() == CMD_SCRIPT_DATA) && dbs == "")
{
Console.WriteLine("The " + util + " function requires the database name to be specified.");
PrintInstructions();
return;
}
*/
// if scripting table data, tablename must be provided
if (util.ToLower() == CMD_SCRIPT_DATA && tbl == "" && all_tables == false)
{
Console.WriteLine("The " + util + " function requires the table name to be specified unless --alltables is specified.");
PrintInstructions();
return;
}
// establish and test db connection
Connection connection = new Connection(svr, usr, pwd, trustedConnection);
if (connection.testConnection() != true)
{
Console.WriteLine("Database connection could not be established.");
Console.WriteLine("Enter v to view the connection error message, q to quit.");
string nxt = Console.ReadLine();
if (nxt.ToLower() == "v")
{
Console.WriteLine("\n" + connection.connectionError + "\n");
Console.ReadLine();
}
return;
}
// Create Database
/*
if (util.ToLower() == CMD_CREATE_DATABASE)
{
CreateDatabase db = new CreateDatabase(connection, pth, dbs, with_fixtures);
}
*/
// Script Objects
if (util.ToLower() == CMD_SCRIPT_OBJECTS)
{
ScriptDbObjects dbo = new ScriptDbObjects(connection, pth, dbs);
}
// Script Data
if (util.ToLower() == CMD_SCRIPT_DATA)
{
string[] tables;
if (all_tables == true)
tables = ScriptData.getTables(connection, dbs);
else
tables = new string[]{tbl};
foreach (string t in tables)
{
ScriptData sd = new ScriptData(connection, pth, dbs, t, limit_results, with_fixtures, where_clause);
}
}
}
static List<string> getAllDatabases(Connection connstr)
{
List<string> dbs = new List<string>();
string command = "SELECT "
+ " name "
+ "FROM "
+ " master.dbo.sysdatabases "
+ "WHERE "
+ " name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) "
+ "ORDER BY "
+ " name";
SqlConnection cn = new SqlConnection(connstr.connectionString());
cn.Open();
SqlCommand cmd = new SqlCommand(command, cn);
// issue the query
SqlDataReader rdr = null;
try
{
rdr = cmd.ExecuteReader();
if (rdr != null && !rdr.IsClosed)
{
if (rdr.HasRows)
{
while (rdr.Read())
{
dbs.Add(rdr["name"].ToString());
}
}
}
}
catch (Exception e)
{
Console.WriteLine("\nUnable to retrieve list of databases from server \n");
Console.WriteLine(e);
}
return dbs;
}
public CreateDatabase(Connection conn, string dbPath, string dbName, bool withFixtures)
{
with_fixtures = withFixtures;
createDb(conn, dbPath, dbName);
}
public CreateDatabase(Connection conn, string dbPath, string dbName)
{
createDb(conn, dbPath, dbName);
}
public ScriptExecute(string sqlFile, string db, Connection conn)
{
sql = sqlFile;
dbName = db;
execute(conn);
}
