/// <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; } } } }