public DataTable GetColumns(string DatabaseName, string TableName)
{
DataTable dt = new DataTable();
dt.Columns.Add("ColumnName", typeof(string));
dt.Columns.Add("ColumnType", typeof(string));
dt.Columns.Add("ColumnSize", typeof(int));
dt.Columns.Add("ColumnKey", typeof(bool));
dt.Columns.Add("ColumnNull", typeof(bool));
dt.Columns.Add("ColumnID", typeof(int));
dt.Columns.Add("ColumnRemark", typeof(string));
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer2 svr = new SQLDMO.SQLServer2Class();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table2 myTb = new SQLDMO.Table2Class();
foreach (SQLDMO.Table2 tb in myDb.Tables)
{
if (tb.Name == TableName)
{
myTb = tb;
}
}
foreach (SQLDMO.Column2 column in myTb.Columns)
{
DataRow dr = dt.NewRow();
dr["ColumnName"] = column.Name;
dr["ColumnType"] = column.Datatype;
dr["ColumnSize"] = column.Length;
dr["ColumnKey"] = column.InPrimaryKey;
dr["ColumnNull"] = column.AllowNulls;
dr["ColumnID"] = column.ID;
dr["ColumnRemark"] = column.Properties.Application.ODBCVersionString;
dt.Rows.Add(dr);
}
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("连接数据库出错：" + e.Message);
return(null);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return(dt);
}
private void doExport(String filename, Int32 index)
{
switch (index)
{
case 1: // Scenario Files (*.xml)|*.xml
this.Cursor = Cursors.WaitCursor;
FileInfo xmlFile = new FileInfo(filename);
if (!xmlFile.Extension.Equals(".xml", StringComparison.InvariantCultureIgnoreCase))
{
filename = xmlFile.FullName + ".xml";
}
ExportTool<IXPathNavigable> exportTool = new ExportTool<IXPathNavigable>();
ExportToXmlFileAdapter toXmlFileAdapter = new ExportToXmlFileAdapter(filename);
DDD_4_2_Exporter dddExporter = new DDD_4_2_Exporter(vsgController,
projectController,
true);
try
{
exportTool.Export(dddExporter, toXmlFileAdapter);
this.Text = "Visual Scenario Generator - " + filename;
}
catch(Exception ex)
{
MessageBox.Show(ex.Message, ex.InnerException.Message);
}
this.Cursor = Cursors.Default;
break;
case 2: // Raw Scenario Files
this.Cursor = Cursors.WaitCursor;
//// The following commented out code is for dumping mysql
//FileInfo sqlFile = new FileInfo(filename);
//if (!sqlFile.Extension.Equals(".sql", StringComparison.InvariantCultureIgnoreCase))
//{
// filename = sqlFile.FullName + ".sql";
//}
//RegistryKey key = Registry.LocalMachine.OpenSubKey(@"SOFTWARE\MySQL AB\MySQL Server 5.0");
//if (key.GetValue("Location") != null)
//{
// String path = key.GetValue("Location").ToString();
// Process process = new Process();
// process.StartInfo.FileName = Path.Combine(path, @"bin\mysqldump.exe");
// if (password.Equals(String.Empty))
// process.StartInfo.Arguments = String.Format("-u {0} -r \"{1}\" {2}", username, filename, database);
// else
// process.StartInfo.Arguments = String.Format("-u {0} -p{1} -r \"{2}\" {3}", username, password, filename, database);
// process.StartInfo.WindowStyle = ProcessWindowStyle.Hidden;
// try
// {
// process.Start();
// process.WaitForExit();
// }
// catch (Exception e)
// {
// MessageBox.Show(e.Message, "Export error");
// }
//}
try
{
FileInfo mdbFile = new FileInfo(filename);
if (!mdbFile.Extension.Equals(".bak", StringComparison.InvariantCultureIgnoreCase))
{
filename = mdbFile.FullName + ".bak";
}
SQLDMO.SQLServer2 sqlServer = new SQLDMO.SQLServer2Class();
sqlServer.Connect(server, username, password);
SQLDMO.Backup2 backup = new SQLDMO.Backup2Class();
backup.Devices = backup.Files;
backup.Files = "[" + filename + "]";
backup.Database = database;
backup.SQLBackup(sqlServer);
scenarioFilename = filename;
this.Text = "Visual Scenario Generator - " + scenarioFilename;
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Export error");
}
this.Cursor = Cursors.Default;
break;
}
}
private void doImport(String filename, Int32 index)
{
switch (index)
{
case 1: // Scenario Files (*.xml)|*.xml
importTool = new ImportTool();
importTool.PutTypeInName = false;
importTool.PutNameInDescription = false;
// Dtermine the correct adapter to load.
XmlDocument document = new XmlDocument();
document.Load(filename);
AME.Adapters.IImportAdapter adapter = null;
String schema = document.DocumentElement.GetAttribute("noNamespaceSchemaLocation", "http://www.w3.org/2001/XMLSchema-instance");
switch (schema)
{
case "DDDSchema_4_1.xsd":
adapter = new Adapters.DDD4_1_Importer(vsgController);
break;
case "DDDSchema_4_1_1.xsd":
adapter = new Adapters.DDD4_1_Importer(vsgController);
break;
case "DDDSchema_4_0_2.xsd":
adapter = new Adapters.DDD4_0_2_Importer(vsgController);
break;
case "DDDSchema_4_2.xsd":
adapter = new Adapters.DDD4_2_Importer(vsgController);
break;
default:
adapter = new Adapters.DDD4_0_Importer(vsgController);
break;
}
//document.Schemas.
//Adapters.DDD4_0_Importer ddd4_0_Schema = new Adapters.DDD4_0_Importer();
importSuccess = importTool.Import(vsgController, adapter, filename, this, true);
if (importSuccess)
{
// Wait for import to finish.
// It will send a component update which will enable this back.
this.Enabled = false;
scenarioFilename = filename;
}
break;
case 2: // Raw Scenario Files
//// The following commented out code is for imported dumped mysql
//try
//{
// vsgController.ImportSql(filename);
// ComponentOptions compOptions = new ComponentOptions();
// compOptions.LevelDown = 1;
// IXPathNavigable iNav = projectController.GetRootComponents(compOptions);
// XPathNavigator nav = iNav.CreateNavigator();
// if (nav != null)
// {
// XPathNavigator navScenario = nav.SelectSingleNode(String.Format("Components/Component[@Type='{0}']", projectController.RootComponentType));
// if (navScenario != null)
// {
// String id = navScenario.GetAttribute("ID", navScenario.NamespaceURI);
// openScenario(Int32.Parse(id)); // need to do this better;
// }
// }
//}
//catch (Exception e)
//{
// MessageBox.Show(e.Message, "Export error");
//}
try
{
vsgController.DropDatabase();
SQLDMO.SQLServer2 sqlServer = new SQLDMO.SQLServer2Class();
sqlServer.Connect(server, username, password);
SQLDMO.Restore2 restore = new SQLDMO.Restore2Class();
restore.Devices = restore.Files;
restore.Files = "[" + filename + "]";
restore.Database = database;
restore.ReplaceDatabase = true;
restore.SQLRestore(sqlServer);
ComponentOptions compOptions = new ComponentOptions();
compOptions.LevelDown = 1;
IXPathNavigable iNav = projectController.GetRootComponents(compOptions);
XPathNavigator nav = iNav.CreateNavigator();
if (nav != null)
{
XPathNavigator navScenario = nav.SelectSingleNode(String.Format("Components/Component[@Type='{0}']", projectController.RootComponentType));
if (navScenario != null)
{
String id = navScenario.GetAttribute("ID", navScenario.NamespaceURI);
openScenario(Int32.Parse(id)); // need to do this better;
scenarioFilename = filename;
this.Text = "Visual Scenario Generator - " + scenarioFilename;
}
}
}
catch (Exception e)
{
MessageBox.Show(e.Message, "Import error");
}
break;
}
}
public bool UpdateTable(string DatabaseName, string TableName, DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer2 svr = new SQLDMO.SQLServer2Class();
Hashtable ht1 = new Hashtable();
Hashtable ht2 = new Hashtable();
Hashtable ht3 = new Hashtable();
string keyname1 = "";
string keyname2 = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table2 myTb = new SQLDMO.Table2Class();
foreach (SQLDMO.Table2 tb in myDb.Tables)
{
if (tb.Name == TableName)
myTb = tb;
}
foreach (SQLDMO.Column2 column in myTb.Columns)
{
ht1.Add(column.ID, column);
if (column.InPrimaryKey)
keyname1 = column.Name;
}
foreach (DataRow dr in dt.Rows)
{
if (dr["ColumnID"] != DBNull.Value)
{
ht2.Add(dr["ColumnID"].ToString(), dr);
}
else
{
ht3.Add("1", dr);
}
if (bool.Parse(dr["ColumnKey"].ToString()))
keyname2 = dr["ColumnName"].ToString();
}
foreach (DictionaryEntry de3 in ht3)
{
DataRow dr3 = (DataRow)de3.Value;
SQLDMO.Column2 column = new SQLDMO.Column2Class();
column.Name = dr3["ColumnName"].ToString();
column.Datatype = dr3["ColumnType"].ToString();
column.Length = int.Parse(dr3["ColumnSize"].ToString());
column.AllowNulls = bool.Parse(dr3["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
foreach (DictionaryEntry de1 in ht1)
{
if (ht2.Contains(de1.Key.ToString()))
{
DataRow dr = (DataRow)ht2[de1.Key.ToString()];
SQLDMO.Column2 co = (SQLDMO.Column2)de1.Value;
SQLDMO.Column2 cm = (SQLDMO.Column2)myTb.Columns.Item(co.Name);
if (dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "timestamp")
continue;
if (dr["ColumnName"].ToString() == cm.Name && dr["ColumnType"].ToString() == cm.Datatype && int.Parse(dr["ColumnSize"].ToString()) == cm.Length && bool.Parse(dr["ColumnNull"].ToString()) == cm.AllowNulls)
continue;
if (dr["ColumnName"].ToString() != cm.Name)
{
cm.Name = dr["ColumnName"].ToString();
}
if (dr["ColumnType"].ToString() != cm.Datatype)
{
switch (dr["ColumnType"].ToString())
{
case "bigint":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "binary":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "bit":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "char":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "datetime":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "decimal":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "float":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "image":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "int":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "money":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "nchar":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "ntext":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "numeric":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 9, 3);
break;
case "nvarchar":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "real":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "text":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "timestamp":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "varchar":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
}
}
if (dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "timestamp")
{
continue;
}
else
{
cm.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
}
}
else
{
SQLDMO.Column2 cm1=new SQLDMO.Column2Class();
cm1=(SQLDMO.Column2)de1.Value;
myTb.Columns.Remove(cm1.Name);
}
}
if (keyname1 != keyname2)
{
if(keyname1!="")
myTb.Keys.Remove(keyname1);
}
if (keyname2 != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyname2;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyname2);
myTb.Keys.Add(key);
}
return true;
}
catch (Exception e)
{
//throw (new Exception("�������ݿ�����" + e.Message));
ShowError("������ݿ�ʧ��!" + e.Message);
return false;
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
}
public DataTable GetColumns(string DatabaseName, string TableName)
{
DataTable dt = new DataTable();
dt.Columns.Add("ColumnName", typeof(string));
dt.Columns.Add("ColumnType", typeof(string));
dt.Columns.Add("ColumnSize", typeof(int));
dt.Columns.Add("ColumnKey", typeof(bool));
dt.Columns.Add("ColumnNull", typeof(bool));
dt.Columns.Add("ColumnID", typeof(int));
dt.Columns.Add("ColumnRemark", typeof(string));
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer2 svr = new SQLDMO.SQLServer2Class();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table2 myTb = new SQLDMO.Table2Class();
foreach (SQLDMO.Table2 tb in myDb.Tables)
{
if (tb.Name == TableName)
myTb = tb;
}
foreach (SQLDMO.Column2 column in myTb.Columns)
{
DataRow dr = dt.NewRow();
dr["ColumnName"] = column.Name;
dr["ColumnType"] = column.Datatype;
dr["ColumnSize"] = column.Length;
dr["ColumnKey"] = column.InPrimaryKey;
dr["ColumnNull"] = column.AllowNulls;
dr["ColumnID"] = column.ID;
dr["ColumnRemark"] = column.Properties.Application.ODBCVersionString;
dt.Rows.Add(dr);
}
}
catch (Exception e)
{
//throw (new Exception("�������ݿ�����" + e.Message));
ShowError("�������ݿ�����" + e.Message);
return null;
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return dt;
}
/// <summary>
/// 执行SQL脚本文件，用到SQLDMO.DLL
/// 该组件需要注册
/// </summary>
/// <param name="dbServerName"></param>
/// <param name="dbUser"></param>
/// <param name="dbPwd"></param>
/// <param name="dbName"></param>
/// <param name="sqlFile"></param>
/// <param name="cmdEncod">发送的编码格式</param>
public static void ExecSqlFileByDMO(string dbServerName, string dbUser, string dbPwd, string dbName, string sqlFile,Encoding cmdEncod)
{
SQLDMO.SQLServer2 sqlserver = new SQLDMO.SQLServer2Class();
sqlserver.Connect(dbServerName , dbUser , dbPwd);
SQLDMO.Databases mydbs = sqlserver.Databases;
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)mydbs.Item(dbName, dbUser );
System.IO.StreamReader s = null;
string sql = string.Empty;
try
{
s = new System.IO.StreamReader(sqlFile, cmdEncod);
sql = s.ReadToEnd();
mydb.ExecuteImmediate(sql, SQLDMO.SQLDMO_EXEC_TYPE.SQLDMOExec_Default, sql.Length);
}
finally
{
s.Close();
s.Dispose();
s = null;
sqlserver.DisConnect();
}//try
}
public bool UpdateTable(string DatabaseName, string TableName, DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer2 svr = new SQLDMO.SQLServer2Class();
Hashtable ht1 = new Hashtable();
Hashtable ht2 = new Hashtable();
Hashtable ht3 = new Hashtable();
string keyname1 = "";
string keyname2 = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database2 myDb = new SQLDMO.Database2Class();
myDb = (SQLDMO.Database2)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table2 myTb = new SQLDMO.Table2Class();
foreach (SQLDMO.Table2 tb in myDb.Tables)
{
if (tb.Name == TableName)
{
myTb = tb;
}
}
foreach (SQLDMO.Column2 column in myTb.Columns)
{
ht1.Add(column.ID, column);
if (column.InPrimaryKey)
{
keyname1 = column.Name;
}
}
foreach (DataRow dr in dt.Rows)
{
if (dr["ColumnID"] != DBNull.Value)
{
ht2.Add(dr["ColumnID"].ToString(), dr);
}
else
{
ht3.Add("1", dr);
}
if (bool.Parse(dr["ColumnKey"].ToString()))
{
keyname2 = dr["ColumnName"].ToString();
}
}
foreach (DictionaryEntry de3 in ht3)
{
DataRow dr3 = (DataRow)de3.Value;
SQLDMO.Column2 column = new SQLDMO.Column2Class();
column.Name = dr3["ColumnName"].ToString();
column.Datatype = dr3["ColumnType"].ToString();
column.Length = int.Parse(dr3["ColumnSize"].ToString());
column.AllowNulls = bool.Parse(dr3["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
foreach (DictionaryEntry de1 in ht1)
{
if (ht2.Contains(de1.Key.ToString()))
{
DataRow dr = (DataRow)ht2[de1.Key.ToString()];
SQLDMO.Column2 co = (SQLDMO.Column2)de1.Value;
SQLDMO.Column2 cm = (SQLDMO.Column2)myTb.Columns.Item(co.Name);
if (dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "timestamp")
{
continue;
}
if (dr["ColumnName"].ToString() == cm.Name && dr["ColumnType"].ToString() == cm.Datatype && int.Parse(dr["ColumnSize"].ToString()) == cm.Length && bool.Parse(dr["ColumnNull"].ToString()) == cm.AllowNulls)
{
continue;
}
if (dr["ColumnName"].ToString() != cm.Name)
{
cm.Name = dr["ColumnName"].ToString();
}
if (dr["ColumnType"].ToString() != cm.Datatype)
{
switch (dr["ColumnType"].ToString())
{
case "bigint":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "binary":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "bit":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "char":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "datetime":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "decimal":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "float":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "image":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "int":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "money":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "nchar":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "ntext":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "numeric":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 9, 3);
break;
case "nvarchar":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
case "real":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "text":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "timestamp":
cm.AlterDataType(dr["ColumnType"].ToString(), -1, -1, -1);
break;
case "varchar":
cm.AlterDataType(dr["ColumnType"].ToString(), int.Parse(dr["ColumnSize"].ToString()), 0, 0);
break;
}
}
if (dr["ColumnType"].ToString() == "text" || dr["ColumnType"].ToString() == "image" || dr["ColumnType"].ToString() == "timestamp")
{
continue;
}
else
{
cm.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
}
}
else
{
SQLDMO.Column2 cm1 = new SQLDMO.Column2Class();
cm1 = (SQLDMO.Column2)de1.Value;
myTb.Columns.Remove(cm1.Name);
}
}
if (keyname1 != keyname2)
{
if (keyname1 != "")
{
myTb.Keys.Remove(keyname1);
}
}
if (keyname2 != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyname2;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyname2);
myTb.Keys.Add(key);
}
return(true);
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("添加数据库失败!" + e.Message);
return(false);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
}
