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); }