private IFrmMain frmMain = null; //主窗体
#endregion
#region [ 事件: 窗体加载 ]
/// <summary>
/// 窗体加载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FrmSqlList_Load(object sender, EventArgs e)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass(); //分布式管理对象
try
{
SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();//获取服务器名称列表
if (serverList.Count > 0)
{
for (int i = 1; i <= serverList.Count; i++)
{
ltbServer.Items.Add(serverList.Item(i));//绑定到ListBox
}
}
else
{
ltbServer.Items.Add("(local)"); //如果没有活动的服务器，加载本机
}
ltbServer.SelectedIndex = 0;
}
catch (Exception ex)
{
ErrorMessage(3010010, ex.StackTrace, "[FrmDBSet:LoadSqlList]", ex.Message);
//3010010frmMain.ErrorMessage(ee.Message, true, false);
}
finally
{
sqlApp.Quit();
}
}
public ArrayList GetServerList()
{
ArrayList alServers = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
try
{
SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();
for (int i = 1; i <= serverList.Count; i++)
{
alServers.Add(serverList.Item(i));
//comboBox1.Items.Add(serverList.Item(i));
//listBox1.Items.Add(serverList.Item(i));
}
}
catch (Exception e)
{
throw (new Exception("Take the database server list error：" + e.Message));
}
finally
{
sqlApp.Quit();
}
return(alServers);
}
// 在用户的配置时，我们需要列出当前局域网内所有的数据库服务器，并且要列出指定服务器的所有数据库，实现代码如下：
// 取得数据库服务器列表：
/// <summary>
/// 获取数据库服务器列表
/// </summary>
/// <returns></returns>
public static ArrayList GetServerList(out bool isErr)
{
ArrayList allServers = new ArrayList();
try
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
// 将局域网类的所有能够连接SQL的服务器名称列出
SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();
allServers.Add("(local)");
for (int i = 1; i <= serverList.Count; i++)
{
// 添加到列表中
allServers.Add(serverList.Item(i));
}
sqlApp.Quit();
isErr = true;
}
catch (Exception e)
{
isErr = SqlErrRepair(e.Message.ToString());
return(GetServerList(out isErr));
}
return(allServers);
}
//取得指定数据库服务器的数据库列表
public ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(strServerName, strUserName, strPwd);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString().Trim());
return(alDbs);
}
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
}
svr.DisConnect();
sqlApp.Quit();
return(alDbs);
}
private IFrmMain frmMain = null; //主窗体
#endregion
#region [ 事件: 窗体加载 ]
/// <summary>
/// 窗体加载
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void FrmSqlList_Load(object sender, EventArgs e)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass(); //分布式管理对象
try
{
SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();//获取服务器名称列表
if (serverList.Count > 0)
{
for (int i = 1; i <= serverList.Count; i++)
{
ltbServer.Items.Add(serverList.Item(i));//绑定到ListBox
}
}
else
{
ltbServer.Items.Add("(local)"); //如果没有活动的服务器，加载本机
}
ltbServer.SelectedIndex = 0;
}
catch (Exception ex)
{
ErrorMessage(3010010, ex.StackTrace, "[FrmDBSet:LoadSqlList]", ex.Message);
//3010010frmMain.ErrorMessage(ee.Message, true, false);
}
finally
{
sqlApp.Quit();
}
}
/// <summary>
/// 返回数据库列表
/// </summary>
/// <returns></returns>
public ArrayList GetDbList()
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
}
}
catch (Exception e)
{
throw (new Exception("连接数据库出错：" + e.Message));
}
finally
{
svr.Close();
sqlApp.Quit();
}
return(alDbs);
}
// 在用户的配置时，我们需要列出当前局域网内所有的数据库服务器，并且要列出指定服务器的所有数据库，实现代码如下：
// 取得数据库服务器列表：
/// <summary>
/// 获取数据库服务器列表
/// </summary>
/// <returns></returns>
public static ArrayList GetServerList(out bool isErr)
{
ArrayList allServers = new ArrayList();
try
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
// 将局域网类的所有能够连接SQL的服务器名称列出
SQLDMO.NameList serverList = sqlApp.ListAvailableSQLServers();
allServers.Add("(local)");
for (int i = 1; i <= serverList.Count; i++)
{
// 添加到列表中
allServers.Add(serverList.Item(i));
}
sqlApp.Quit();
isErr = true;
}
catch (Exception e)
{
isErr = SqlErrRepair(e.Message.ToString());
return GetServerList(out isErr);
}
return allServers;
}
private void FillDBServer()
{
if (cbxDBServerList.Items.Count == 0)
{
SQLDMO.NameList names;
SQLDMO.ApplicationClass ac = new SQLDMO.ApplicationClass();
names = ac.ListAvailableSQLServers();
string[] serverList = new string[names.Count];
for (int i = 0; i < serverList.Length; i++)
{
serverList[i] = names.Item(i);
}
foreach (string severName in serverList)
{
if (severName != null)
{
cbxDBServerList.Items.Add(severName);
}
}
}
}
public ArrayList GetStored(string DatabaseName)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
foreach (SQLDMO.StoredProcedure db in mydb.StoredProcedures)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
}
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("连接数据库出错：" + e.Message);
return(null);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return(alDbs);
}
//Get the database list
public ArrayList GetDbList()
{
string ServerName = server;
string UserName = uid;
string Password = pwd;
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
alDbs.Add(db.Name);
}
//listBox2.Items.Add(db.Name);
}
}
catch (Exception e)
{
MessageBox.Show("There was an error connecting to the database：" + e.Message);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return(alDbs);
}
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);
}
public bool InsertTable(string DatabaseName, string TableName, DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
string keyName = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database myDb = new SQLDMO.DatabaseClass();
myDb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table myTb = new SQLDMO.TableClass();
myTb.Name = TableName;
foreach (DataRow dr in dt.Rows)
{
SQLDMO.Column column = new SQLDMO.ColumnClass();
column.Name = dr["ColumnName"].ToString();
column.Datatype = dr["ColumnType"].ToString();
column.Length = int.Parse(dr["ColumnSize"].ToString());
if (bool.Parse(dr["ColumnKey"].ToString()))
{
keyName = column.Name;
}
column.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
if (keyName != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyName;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyName);
myTb.Keys.Add(key);
}
myDb.Tables.Add(myTb);
return(true);
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
ShowError("添加数据库失败!" + e.Message);
return(false);
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
}
public static string[] GetSqlServers()
{
List<string> servers = new List<string>();
SQLDMO.ApplicationClass application = new SQLDMO.ApplicationClass();
foreach (SQLDMO.ServerGroup serverGroup in application.ServerGroups)
{
foreach (SQLDMO.RegisteredServer server in serverGroup.RegisteredServers)
{
servers.Add(server.Name);
}
}
return (string[])servers.ToArray();
}
public static string[] GetSqlServers()
{
List <string> servers = new List <string>();
SQLDMO.ApplicationClass application = new SQLDMO.ApplicationClass();
foreach (SQLDMO.ServerGroup serverGroup in application.ServerGroups)
{
foreach (SQLDMO.RegisteredServer server in serverGroup.RegisteredServers)
{
servers.Add(server.Name);
}
}
return((string[])servers.ToArray());
}
/// <summary>
/// Handles the DropDown event of the comboBoxSqlServer control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
private void comboBoxSqlServer_DropDown(object sender, EventArgs e)
{
comboBoxSqlServer.Items.Clear();
Application.UseWaitCursor = true;
try
{
SQLDMO.ApplicationClass app = new SQLDMO.ApplicationClass();
SQLDMO.NameList servers = app.ListAvailableSQLServers();
for (int i = 1; i <= servers.Count; i++)
{
comboBoxSqlServer.Items.Add(servers.Item(i));
}
}
catch (Exception ex)
{
Trace.WriteLine(ex, "Mediachase.Ibn.ConfigurationUI.SQLDMO");
}
Application.UseWaitCursor = false;
}
/// <summary>
/// 登录服务器，获取数据库列表
/// </summary>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPwd">密码</param>
/// <returns></returns>
public static ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
// 连接到数据库
svr.Connect(strServerName, strUserName, strPwd);
// 将服务器上所有的数据库遍历
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
if (db.Name != "master" && db.Name != "model" &&
db.Name != "msdb" && db.Name != "pubs" &&
db.Name != "Northwind" && db.Name != "tempdb")
{
alDbs.Add(db.Name);
}
}
}
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
MessageBox.Show("数据库连接失败");
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return(alDbs);
}
/// <summary>
/// �������ݿ��б�
/// </summary>
/// <returns></returns>
public ArrayList GetDbList()
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
alDbs.Add(db.Name);
}
}
catch (Exception e)
{
throw (new Exception("�������ݿ�����" + e.Message));
}
finally
{
svr.Close();
sqlApp.Quit();
}
return alDbs;
}
/// <summary>
/// Handles the DropDown event of the comboBoxSqlServer control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.EventArgs"/> instance containing the event data.</param>
private void comboBoxSqlServer_DropDown(object sender, EventArgs e)
{
comboBoxSqlServer.Items.Clear();
Application.UseWaitCursor = true;
try
{
SQLDMO.ApplicationClass app = new SQLDMO.ApplicationClass();
SQLDMO.NameList servers = app.ListAvailableSQLServers();
for (int i = 1; i <= servers.Count; i++)
{
comboBoxSqlServer.Items.Add(servers.Item(i));
}
}
catch (Exception ex)
{
Trace.WriteLine(ex, "Mediachase.Ibn.ConfigurationUI.SQLDMO");
}
Application.UseWaitCursor = false;
}
/// <summary>
/// 登录服务器，获取数据库列表
/// </summary>
/// <param name="strServerName">服务器名称</param>
/// <param name="strUserName">用户名</param>
/// <param name="strPwd">密码</param>
/// <returns></returns>
public static ArrayList GetDbList(string strServerName, string strUserName, string strPwd)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
// 连接到数据库
svr.Connect(strServerName, strUserName, strPwd);
// 将服务器上所有的数据库遍历
foreach (SQLDMO.Database db in svr.Databases)
{
if (db.Name != null)
{
if (db.Name != "master" && db.Name != "model"
&& db.Name != "msdb" && db.Name != "pubs"
&& db.Name != "Northwind" && db.Name != "tempdb")
{
alDbs.Add(db.Name);
}
}
}
}
catch (Exception e)
{
//throw (new Exception("连接数据库出错：" + e.Message));
MessageBox.Show("数据库连接失败");
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return alDbs;
}
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 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 bool InsertTable(string DatabaseName, string TableName,DataTable dt)
{
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
string keyName = "";
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database myDb = new SQLDMO.DatabaseClass();
myDb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
SQLDMO.Table myTb = new SQLDMO.TableClass();
myTb.Name = TableName;
foreach (DataRow dr in dt.Rows)
{
SQLDMO.Column column = new SQLDMO.ColumnClass();
column.Name = dr["ColumnName"].ToString();
column.Datatype = dr["ColumnType"].ToString();
column.Length = int.Parse(dr["ColumnSize"].ToString());
if (bool.Parse(dr["ColumnKey"].ToString()))
{
keyName = column.Name;
}
column.AllowNulls = bool.Parse(dr["ColumnNull"].ToString());
myTb.Columns.Add(column);
}
if (keyName != "")
{
SQLDMO.Key key = new SQLDMO.KeyClass();
key.Name = keyName;
key.Type = SQLDMO.SQLDMO_KEY_TYPE.SQLDMOKey_Primary;
key.KeyColumns.Add(keyName);
myTb.Keys.Add(key);
}
myDb.Tables.Add(myTb);
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;
}
private void FillDBServer()
{
if (cbxDBServerList.Items.Count == 0)
{
SQLDMO.NameList names;
SQLDMO.ApplicationClass ac = new SQLDMO.ApplicationClass();
names = ac.ListAvailableSQLServers();
string[] serverList = new string[names.Count];
for (int i = 0; i < serverList.Length; i++)
{
serverList[i] = names.Item(i);
}
foreach (string severName in serverList)
{
if (severName != null)
{
cbxDBServerList.Items.Add(severName);
}
}
}
}
public ArrayList GetTables(string DatabaseName)
{
ArrayList alDbs = new ArrayList();
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer svr = new SQLDMO.SQLServerClass();
try
{
svr.Connect(ServerName, UserName, Password);
SQLDMO.Database mydb = new SQLDMO.DatabaseClass();
mydb = (SQLDMO.Database)svr.Databases.Item(DatabaseName, "owner");
foreach (SQLDMO.Table db in mydb.Tables)
{
if (db.Name != null)
alDbs.Add(db.Name);
}
}
catch (Exception e)
{
//throw (new Exception("�������ݿ�����" + e.Message));
ShowError("�������ݿ�����" + e.Message);
return null;
}
finally
{
svr.DisConnect();
sqlApp.Quit();
}
return alDbs;
}
