private static void AddUserToRole(User user, string role)
{
if (!(user.IsMember(role)))
{
user.AddToRole(role);
user.Alter();
}
}
private static void AddUserToRoles(User user, params string[] roles)
{
if (roles.IsNullOrEmpty())
return;
foreach (var role in roles)
{
if (!(user.IsMember(role)))
{
user.AddToRole(role);
}
}
user.Alter();
}
static void Main(string[] args)
{
//Parse command line parameters
Dictionary<string, string> param = new Dictionary<string, string>();
foreach (var v in args)
{
int p = v.IndexOf("=");
if (p > 0)
{
param.Add(v.Substring(0, p), v.Substring(p + 1, v.Length - p - 1));
}
}
if (param.Count == 0)
{
//just show help
ShowUsage();
WriteLine("Press any key to close");
Console.ReadKey();
Environment.Exit(0);
}
string server;
string database;
string newDatabase = null;
string user = null;
string password = null;
string databaseFileName = "";
if (!param.TryGetValue("server", out server)) server = ".";
if (!param.TryGetValue("db", out database)) WarnAndExit("\"db\" command line parameter was not specified");
if (!param.TryGetValue("newdb", out newDatabase)) newDatabase = null;
if (!param.TryGetValue("user", out user)) user = null;
if (!param.TryGetValue("password", out password)) password = null;
if (!param.TryGetValue("filename", out databaseFileName)) databaseFileName = null;
if (string.IsNullOrEmpty(newDatabase)) newDatabase = database + "_Unicode";
//Connect to the specified server
ServerConnection connection = new ServerConnection(server);
Database db = null;
Server svr = null;
if (string.IsNullOrEmpty(user))
{
connection.LoginSecure = true; //Windows authentication
}
else
{
connection.LoginSecure = false;
connection.Login = user;
connection.Password = password;
}
try
{
svr = new Server(connection);
db = svr.Databases[database];
}
catch (Exception e)
{
WarnAndExit(string.Format("Could not connect to server \"{0}\": {1}", server, e.Message));
}
if (db == null) WarnAndExit(string.Format("Database \"{0}\" does not exist on the server \"{1}\"", database, server));
//create new database
Database newDB = svr.Databases[newDatabase];
if (newDB != null)
{
try
{
Notify(string.Format("Dropping the existing \"{0}\" database", newDatabase));
svr.KillAllProcesses(newDatabase);
svr.KillDatabase(newDatabase);
}
catch (Exception e)
{
ReportException(e);
}
newDB = svr.Databases[newDatabase];
}
if (newDB != null)
{
WriteLine(string.Format("Target database \"{0}\" already exists. All existing data will be deleted", newDatabase), OutputKind.Warning);
//store views/tables/triggers in a list
List<View> oldViews = new List<View>();
foreach (View v in newDB.Views) if (!v.IsSystemObject) oldViews.Add(v);
List<Table> oldTables = new List<Table>();
foreach (Table t in newDB.Tables) if (!t.IsSystemObject) oldTables.Add(t);
List<Trigger> oldTriggers = new List<Trigger>();
foreach (Trigger t in newDB.Triggers) if (!t.IsSystemObject) oldTriggers.Add(t);
//delete 'em
foreach (Trigger t in oldTriggers) t.Drop();
foreach (View v in oldViews) v.Drop();
foreach (Table t in oldTables) t.Drop();
}
else
{
WriteLine(string.Format("Creating new database \"{0}\" ", newDatabase), OutputKind.Info);
newDB = new Database(svr, newDatabase);
newDB.Collation = db.Collation;
newDB.DefaultSchema = db.DefaultSchema; //should it be "sysdba"?
FileGroup dbFG = new FileGroup(newDB, "PRIMARY");
newDB.FileGroups.Add(dbFG);
//Now add primary db file to file group
if (string.IsNullOrEmpty(databaseFileName))
{
string oldDatabaseFilename = db.FileGroups[0].Files[0].FileName;
string directory = Path.GetDirectoryName(oldDatabaseFilename);
databaseFileName = directory + @"\" + newDatabase + ".mdf";
}
DataFile df1 = new DataFile(dbFG, "SalesLogix_Data");
dbFG.Files.Add(df1);
df1.FileName = databaseFileName;
df1.Size = 10.0*1024.0;
df1.GrowthType = FileGrowthType.Percent;
df1.Growth = 25.0;
try
{
newDB.Create();
}
catch (Exception e)
{
WriteLine(string.Format("Could not create database \"{0}\"", newDatabase), OutputKind.Error);
ReportException(e);
WarnAndExit("");
}
}
//copy the users
foreach (User oldUser in db.Users)
{
User newUser = newDB.Users[oldUser.Name];
if (newUser == null)
{
Notify("Processing user " + oldUser.Name);
try
{
newUser = new User(newDB, oldUser.Name);
newUser.DefaultSchema = oldUser.DefaultSchema;
newUser.UserType = oldUser.UserType;
newUser.Login = oldUser.Login;
newDB.Users.Add(newUser);
newUser.Create();
StringCollection roles = oldUser.EnumRoles();
foreach (string role in roles) newUser.AddToRole(role);
newUser.Alter();
}
catch (Exception e)
{
ReportException(e);
}
}
}
//copy schemas
foreach (Schema oldSchema in db.Schemas)
{
Schema newSchema = newDB.Schemas[oldSchema.Name];
if (newSchema == null)
{
Notify("Processing schema " + oldSchema.Name);
try
{
newSchema = new Schema(newDB, oldSchema.Name);
newSchema.Owner = oldSchema.Owner;
newDB.Schemas.Add(newSchema);
newSchema.Create();
}
catch (Exception e)
{
ReportException(e);
}
}
}
//copy datatype
foreach (UserDefinedDataType oldType in db.UserDefinedDataTypes)
{
UserDefinedDataType newType = newDB.UserDefinedDataTypes[string.Format("[{0}].[{1}]", oldType.Owner, oldType.Name)];
if (newType == null) newType = newDB.UserDefinedDataTypes[oldType.Name];
if (newType == null)
{
Notify("Processing user data type " + oldType.Name);
try
{
newType = new UserDefinedDataType(newDB, oldType.Name, oldType.Schema);
newType.Owner = oldType.Owner;
//adjust the type correctly
string systemType = oldType.SystemType;
switch (systemType.ToUpper())
{
case "VARCHAR":
{
systemType = "NVARCHAR";
break;
}
case "CHAR":
{
systemType = "NCHAR";
break;
}
case "TEXT":
{
systemType = "NTEXT";
break;
}
}
newType.SystemType = systemType;
newType.Length = oldType.Length;
newType.Nullable = oldType.Nullable;
newType.Default = oldType.Default;
newType.DefaultSchema = oldType.DefaultSchema;
newType.NumericPrecision = oldType.NumericPrecision;
newType.NumericScale = oldType.NumericScale;
newType.Rule = oldType.Rule;
newType.RuleSchema = oldType.RuleSchema;
newDB.UserDefinedDataTypes.Add(newType);
newType.Create();
}
catch (Exception e)
{
ReportException(e);
}
}
}
//copy the schema
StringBuilder sb = new StringBuilder();
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
options.DriAllConstraints = true;
options.DriIndexes = true;
options.FullTextIndexes = true;
options.ExtendedProperties = true;
options.NoCollation = true; //we will convert TEXT to NTEXT anyway
options.NoCommandTerminator = false; //we do need this - otherwise CREATE VIEW has problems
options.ClusteredIndexes = true;
options.NonClusteredIndexes = true;
options.SchemaQualify = true;
options.ScriptSchema = true;
options.SchemaQualifyForeignKeysReferences = true;
options.IncludeDatabaseContext = false; //since we wil be executing in a different DB context
options.Triggers = true;
//copy tables
foreach (Table table in db.Tables)
{
if (!table.IsSystemObject)
{
Table oldTable = newDB.Tables[table.Name];
if (oldTable != null)
{
try
{
Notify("Dropping existing table " + oldTable.Name);
oldTable.Drop();
}
catch (Exception e)
{
ReportException(e);
}
}
sb.Length = 0;
//don't script inserts - we run out of memory
options.ScriptData = false;
StringCollection coll = table.Script(options);
foreach (string str in coll)
{
sb.AppendLine(str);
}
Notify("Creating table " + table.Name);
string sql = ChangeAnsiToUnicode(sb.ToString());
try
{
newDB.ExecuteNonQuery(sql);
}
catch (Exception e)
{
ReportException(e, sql);
}
var x = Missing.Value;
}
}
//copy views
foreach (View view in db.Views)
{
if (!view.IsSystemObject)
{
View oldView = newDB.Views[view.Name];
if (oldView != null)
{
try
{
Notify("Dropping existing view " + oldView.Name);
oldView.Drop();
}
catch (Exception e)
{
ReportException(e);
}
}
sb.Length = 0;
StringCollection coll = view.Script(options);
foreach (string str in coll)
{
string line = str; //make it a local avriable since we change it below
if (line.Trim().StartsWith("create view", true, CultureInfo.CurrentCulture))
{
//'CREATE VIEW' must be the first statement in a query batch.
sb.AppendLine("GO");
//make sure we have the right schema when we call CREATE VIEW
if ((line.ToUpper().IndexOf(".[" + view.Name.ToUpper()) < 0) && //if there is no preceeing "]." before [viewname], the schema is not specified
(line.ToUpper().IndexOf("SYSDBA." + view.Name.ToUpper()) < 0))
{
int p = line.ToUpper().IndexOf(string.Format("[{0}]", view.Name.ToUpper()));
if (p >= 0)
{
line = line.Replace(string.Format("[{0}]", view.Name), string.Format("[sysdba].[{0}]", view.Name));
}
else
{
p = line.ToUpper().IndexOf(string.Format("CREATE VIEW {0}", view.Name.ToUpper()));
if (p >= 0)
{
line = line.ToUpper().Replace(string.Format("CREATE VIEW {0}", view.Name), string.Format("CREATE VIEW [sysdba].[{0}]", view.Name));
}
}
}
}
sb.AppendLine(line);
}
try
{
Notify("Creating view " + view.Name);
newDB.ExecuteNonQuery(sb.ToString());
}
catch (Exception e)
{
ReportException(e, sb.ToString());
}
}
}
//now stuff existing data into the new db
string connectionString = "";
if (string.IsNullOrEmpty(user))
{
//Windows Auth
connectionString = string.Format("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog={0};Data Source={1}",
newDatabase, server);
}
else
{
//SQL Auth
connectionString = string.Format("Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3}",
password, user, newDatabase, server);
}
SqlConnection newConnection = new SqlConnection(connectionString);
newConnection.Open();
SqlBulkCopy bulkCopy = new SqlBulkCopy(newConnection);
bulkCopy.BulkCopyTimeout = int.MaxValue;
foreach (Table table in db.Tables)
{
if (!table.IsSystemObject)
{
try
{
Notify("Copying data to table " + table.Name);
string sql = string.Format("SELECT * FROM {0}.{1}", table.Owner, table.Name);
using (DataSet dataset = db.ExecuteWithResults(sql))
{
bulkCopy.DestinationTableName = string.Format("{0}.{1}", table.Owner, table.Name);
if (dataset.Tables.Count > 0)
{
DataTable t = dataset.Tables[0];
bulkCopy.WriteToServer(t);
}
}
}
catch (Exception e)
{
ReportException(e);
}
}
GC.Collect(); //we can use lots of memory above!
}
//make sure the DB is marked as Unicode
string markAsUnicodeSQL = "UPDATE SYSDBA.SYSTEMINFO SET UNICODE = 'T' ";
try
{
Notify("Marking the new database as Unicode enabled");
newDB.ExecuteNonQuery(markAsUnicodeSQL);
}
catch (Exception e)
{
ReportException(e, markAsUnicodeSQL);
}
//convert the virtual file system
Notify("Updating VIRTUALFILESYSTEM table...");
try
{
//we need a conneciton for updates since SMO won't let us use parameters for updates
//when we update large binary fields (VIRTUALFILESYSTEM.ITEMDATA)
string updateConnectionString = "";
if (string.IsNullOrEmpty(user))
{
//Windows Auth
updateConnectionString = string.Format("Integrated Security=SSPI;Persist Security Info=False;Initial Catalog={0};Data Source={1}",
newDatabase, server);
}
else
{
//SQL Auth
updateConnectionString = string.Format("Password={0};Persist Security Info=True;User ID={1};Initial Catalog={2};Data Source={3}",
password, user, newDatabase, server);
}
SqlConnection updateConnection = new SqlConnection(updateConnectionString);
updateConnection.Open();
string vfsSql =
"SELECT VIRTUALFILESYSTEMID, ITEMNAME, ITEMDATA, ISCOMPRESSED FROM SYSDBA.VIRTUALFILESYSTEM WHERE ITEMNAME LIKE '%.entity.xml' ";
using (DataSet vfsDataset = newDB.ExecuteWithResults(vfsSql))
{
if (vfsDataset.Tables.Count > 0)
{
DataTable t = vfsDataset.Tables[0];
foreach (DataRow row in t.Rows)
{
string itemName = (string) row["ITEMNAME"];
string[] arrItemName = itemName.Split(new string[] {"."}, StringSplitOptions.None);
//Name.TABLE.entity.xml
if (arrItemName.Length > 2)
{
string entityName = arrItemName[0];
string tableName = arrItemName[1];
Notify(string.Format("Updating entity {0} (Table {1})", entityName, tableName));
try
{
Table table = null;
foreach (Table currTable in newDB.Tables)
{
if ((String.Compare(currTable.Name, tableName, true) == 0) && (String.Compare(currTable.Owner, "sysdba", true) == 0))
{
table = currTable;
break;
}
}
if (table != null)
{
object ItemData = row["ITEMDATA"]; //we expect an array
if (ItemData != null)
{
object strIsCompressed = row["ISCOMPRESSED"];
Boolean bIsCompressed = (strIsCompressed != null) &&
(string.Compare((string) strIsCompressed, "T", true) ==
0);
using (var memoryStream = UnpackItemData((byte[]) ItemData, bIsCompressed))
{
XmlDocument xmlEntityDocument = new XmlDocument();
memoryStream.Position = 0;
xmlEntityDocument.Load(memoryStream);
Boolean XmlModified = false;
foreach (Column column in table.Columns)
{
//is this a string derived type?
SqlDataType dataType = column.DataType.SqlDataType;
if (column.DataType.SqlDataType == SqlDataType.UserDefinedDataType)
{
UserDefinedDataType colType = newDB.UserDefinedDataTypes[string.Format("[{0}].[{1}]", column.DataType.Schema, column.DataType.Name)];
if (colType == null) colType = newDB.UserDefinedDataTypes[column.DataType.Name];
if (colType != null)
{
switch (colType.SystemType.ToUpper())
{
case "NVARCHAR":
{
dataType = SqlDataType.NVarChar;
break;
}
case "NCHAR":
{
dataType = SqlDataType.NChar;
break;
}
case "NTEXT":
{
dataType = SqlDataType.NText;
break;
}
}
}
}
if ((dataType == SqlDataType.NChar) ||
(dataType == SqlDataType.NText) ||
(dataType == SqlDataType.NVarChar) ||
(dataType == SqlDataType.NVarCharMax)
)
{
XmlModified |= ModifyEntityColumn(xmlEntityDocument, column.Name);
}
}
if (XmlModified)
{
//now save it
memoryStream.SetLength(0);
xmlEntityDocument.Save(memoryStream);
memoryStream.Position = 0;
byte[] newData = PackItemData(memoryStream, true, ref bIsCompressed);
string updateSql =
string.Format(
"UPDATE sysdba.VIRTUALFILESYSTEM SET ITEMDATA = @DATA, ISCOMPRESSED = @COMPRESSED WHERE VIRTUALFILESYSTEMID = '{0}'",
row["VIRTUALFILESYSTEMID"]
);
SqlParameter parameter;
SqlCommand command = new SqlCommand(updateSql, updateConnection);
parameter = command.Parameters.Add("@DATA", SqlDbType.Image);
parameter.Value = newData;
parameter = command.Parameters.Add("@COMPRESSED", SqlDbType.NVarChar);
parameter.Value = bIsCompressed ? "T" : "F";
command.ExecuteNonQuery();
}
}
}
}
}
catch (Exception e)
{
ReportException(e);
}
}
}
}
}
}
catch (Exception e)
{
ReportException(e);
}
GC.Collect();
}
public void CreateUser(string name, string database, string defaultSchema, string login, string[] roles)
{
Database db = _server.Databases[database];
User user = new User(db, name) {Login = login, DefaultSchema = defaultSchema};
user.Create();
for (int i = 0; i < roles.Length; i++)
user.AddToRole(roles[i]);
user.Alter();
}