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