c#获得目标服务器中所有数据库名、表名、列名的实现代码

时间:2021-05-20

复制代码 代码如下:
      /// <summary>
/// 获得目标服务器所有数据库名
/// </summary>
/// <param name="serverName"></param>
/// <param name="userName"></param>
/// <param name="password"></param>
public void getDataBaseNameList(string serverName, string userName, string password)
{
SQLDMO.Application sqlApplication = new SQLDMO.ApplicationClass();
SQLDMO.SQLServer sqlServer = new SQLDMO.SQLServerClass();

sqlServer.Connect(serverName, userName, password); // 连接服务器

foreach (SQLDMO.Database databBase in sqlServer.Databases)
{
if (databBase.Name != null)
{
this.DataBaseTreeView.Nodes.Add(databBase.Name);

getDataBaseTableList(serverName, userName, password, databBase.Name);
}
}
}


/// <summary>
/// 加载数据库中表
/// </summary>
/// <param name="serverName">服务器名</param>
/// <param name="userName">用户名</param>
/// <param name="password">密码</param>
/// <param name="dataBaseName">数据库名</param>
private void getDataBaseTableList(string serverName, string userName, string password, string dataBaseName)
{
SQLDMO.SQLServer Server = new SQLDMO.SQLServerClass();

//连接到服务器
Server.Connect(serverName, userName, password);

//对所有的数据库遍历,获得指定数据库
for (int i = 0; i < Server.Databases.Count; i++)
{
//判断当前数据库是否是指定数据库
if (Server.Databases.Item(i + 1, "dbo").Name == dataBaseName)
{
//获得指定数据库
SQLDMO._Database db = Server.Databases.Item(i + 1, "dbo");

//获得指定数据库中的所有表
for (int j = 0; j < db.Tables.Count; j++)
{
this.DataBaseTreeView.Nodes[i].Nodes.Add(db.Tables.Item(j + 1, "dbo").Name);
}
}
}
}


/// <summary>
/// 获得表中所有列名
/// </summary>
/// <param name="serverName">服务器名</param>
/// <param name="userName">用户名</param>
/// <param name="password">密码</param>
/// <param name="tableName">表名</param>
/// <param name="dataBaseName">数据库名</param>
/// <returns></returns>
public string getRowListFromTable(string serverName, string userName, string password, string tableName, string dataBaseName)
{
string result = string.Empty;

string connectionString = string.Empty;
connectionString += "server=" + serverName;
connectionString += ";Pwd=" + password;
connectionString += ";UID=" + userName;
connectionString += ";Database=" + dataBaseName;

string commandString = string.Empty;
commandString += "select name from syscolumns where id=object_id('";
commandString += tableName;
commandString += "')";

SqlConnection sqlConnection = new SqlConnection(connectionString);
SqlCommand sqlCommand = new SqlCommand(commandString, sqlConnection);

SqlDataAdapter dataAdapter = new SqlDataAdapter(commandString, sqlConnection);

DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);

DataTable dataTable = dataSet.Tables[0];

// DataTable dataTable = sqlConnection.GetSchema("Tables");
foreach (DataRow row in dataTable.Rows)
{
result += row[0].ToString() + "-";
}

if (result != null)
{
return result;
}
else
{
return "0";
}
}

声明:本页内容来源网络,仅供用户参考;我单位不保证亦不表示资料全面及准确无误,也不保证亦不表示这些资料为最新信息,如因任何原因,本网内容或者用户因倚赖本网内容造成任何损失或损害,我单位将不会负任何法律责任。如涉及版权问题,请提交至online#300.cn邮箱联系删除。

相关文章