/// <summary>
/// 得到指定数据库中所有用户表的名字
/// </summary>
public static string[] GetAllUserTableNames(string connStr)
{
IADOBase adoBase = new SqlADOBase(connStr);
string query = "select name from sysobjects where OBJECTPROPERTY(id ,'IsUserTable')=1";
DataSet ds = adoBase.DoQuery(query);
ArrayList list = new ArrayList();
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
string name = ds.Tables[0].Rows[i][0].ToString();
if (name.ToLower() != "dtproperties")
{
list.Add(name);
}
}
string[] names = new string[list.Count];
for (int i = 0; i < names.Length; i++)
{
names[i] = list[i].ToString();
}
return(names);
}
/// <summary>
/// 得到所有数据库的系统信息
/// </summary>
public static DbSysInformation[] GetAllDbSysInfos(string dbIP, string user, string pwd)
{
string connStr = string.Format("Server = {0} ;User = {1} ;Pwd = {2}", dbIP, user, pwd);
IADOBase adoBase = new SqlADOBase(connStr);
try
{
DataSet ds = adoBase.DoQuery("sp_helpdb");
DbSysInformation[] dbInfos = new DbSysInformation[ds.Tables[0].Rows.Count];
for (int i = 0; i < dbInfos.Length; i++)
{
dbInfos[i] = new DbSysInformation();
dbInfos[i].ID = int.Parse(ds.Tables[0].Rows[i]["DbID"].ToString());
dbInfos[i].Name = ds.Tables[0].Rows[i]["Name"].ToString();
dbInfos[i].Owner = ds.Tables[0].Rows[i]["Owner"].ToString();
dbInfos[i].TimeCreated = DateTime.Parse(ds.Tables[0].Rows[i]["Created"].ToString());
}
return(dbInfos);
}
catch (Exception ee)
{
throw ee;
}
}
/// <summary>
/// 删除数据库中的指定表
/// </summary>
public void RemoveTable(string connStr, string tableName)
{
IADOBase adoBase = new SqlADOBase(connStr);
string deleteStr = string.Format("Drop Table {0}", tableName);
adoBase.DoCommand(deleteStr);
}
//使用系统存储过程
public DBTableDetail GetTableStruct(string tableName)
{
IADOBase adoBase = new SqlADOBase(this.connectionStr);
DataSet ds = adoBase.DoQuery("sp_columns " + tableName);
DataTable tb = ds.Tables[0];
DBTableDetail tableDetail = new DBTableDetail();
tableDetail.TableName = tableName;
tableDetail.Columns = new DBColumnInfo[tb.Rows.Count];
for (int i = 0; i < tb.Rows.Count; i++)
{
tableDetail.Columns[i] = new DBColumnInfo();
tableDetail.Columns[i].ColumnName = tb.Rows[i]["Column_Name"].ToString();
tableDetail.Columns[i].ColumnType = tb.Rows[i]["Type_Name"].ToString();
if (tableDetail.Columns[i].ColumnType == "int identity")
{
tableDetail.Columns[i].ColumnType = "int";
}
tableDetail.Columns[i].Length = int.Parse(tb.Rows[i]["Length"].ToString());
tableDetail.Columns[i].AllowNull = (tb.Rows[i]["Nullable"].ToString() == "1");
tableDetail.Columns[i].IsAutoID = (tb.Rows[i]["SS_DATA_TYPE"].ToString() == "56");
tableDetail.Columns[i].Description = tb.Rows[i]["Remarks"].ToString();
if ((tableDetail.Columns[i].Description == null) || (tableDetail.Columns[i].Description == ""))
{
tableDetail.Columns[i].Description = tableDetail.Columns[i].ColumnName;
}
string dv = tb.Rows[i]["Column_def"].ToString();
if (dv == "")
{
tableDetail.Columns[i].DefaultValue = "";
}
else
{
string[] str = dv.Split(new char[] { '(', ')' });
string[] parts = str[1].Split('\'');
if (parts.Length >= 2)
{
tableDetail.Columns[i].DefaultValue = parts[1];
}
else
{
if (str.Length > 0)
{
tableDetail.Columns[i].DefaultValue = str[1];
}
}
}
}
this.SetPKeys(tableDetail, tableName);
return(tableDetail);
}
/// <summary>
/// 在数据库中创建指定表
/// </summary>
public void CreateTable(string connStr, DBTableDetail tableInfo)
{
//形成SQL语句
StringBuilder strBuilder = new StringBuilder();
string str_create = string.Format("Create Table {0}", tableInfo.TableName);
strBuilder.Append(str_create);
strBuilder.Append(" (");
int start = 0;
if (tableInfo.Columns[0].ColumnType == "int")
{
if (tableInfo.Columns[0].IsAutoID)
{
strBuilder.Append(string.Format("{0} int PRIMARY KEY IDENTITY ,", tableInfo.Columns[0].ColumnName));
start = 1;
}
}
for (int i = start; i < tableInfo.Columns.Length; i++)
{
bool length_fixed = DBSystemHelper.DBType_lengthFixed(tableInfo.Columns[i].ColumnType);
string item;
if (length_fixed)
{
item = string.Format("{0} {1} ", tableInfo.Columns[i].ColumnName, tableInfo.Columns[i].ColumnType);
}
else
{
item = string.Format("{0} {1}({2}) ", tableInfo.Columns[i].ColumnName, tableInfo.Columns[i].ColumnType);
}
if ((tableInfo.Columns[i].DefaultValue != null) && (tableInfo.Columns[i].DefaultValue != ""))
{
item += string.Format("DEFAULT {0}", tableInfo.Columns[i].DefaultValue);
}
if (tableInfo.Columns[i].IsPkey)
{
item += "PRIMARY KEY";
}
if (i != tableInfo.Columns.Length - 1)
{
item += " , ";
}
strBuilder.Append(item);
}
strBuilder.Append(" )");
//插入数据库
IADOBase adoBase = new SqlADOBase(connStr);
adoBase.DoCommand(strBuilder.ToString());
}
/// <summary>
/// 还原指定数据库
/// </summary>
public static void RestoreDb(string dbIP, string user, string pwd, string bakFilePath, string dbName)
{
string connStr = string.Format("Server = {0} ;User = {1} ;Pwd = {2}", dbIP, user, pwd);
IADOBase adoBase = new SqlADOBase(connStr);
string command = string.Format("use {0} restore database {0} from disk = '{1}'", dbName, bakFilePath);
adoBase.DoCommand(command);
}
/// <summary>
/// 删除指定数据库
/// </summary>
public static void RemoveDb(string dbIP, string user, string pwd, string dbName)
{
string connStr = string.Format("Server = {0} ;User = {1} ;Pwd = {2}", dbIP, user, pwd);
IADOBase adoBase = new SqlADOBase(connStr);
string command = string.Format("Drop database {0}", dbName);
adoBase.DoCommand(command);
}
private void SetPKeys(DBTableDetail tableDetail, string tableName)
{
IADOBase adoBase = new SqlADOBase(this.connectionStr);
DataSet ds = adoBase.DoQuery("sp_pkeys " + tableName);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
foreach (DBColumnInfo colInfo in tableDetail.Columns)
{
if (colInfo.ColumnName == ds.Tables[0].Rows[i]["COLUMN_NAME"].ToString())
{
colInfo.IsPkey = true;
break;
}
}
}
}