[ADO.NET] 如何 列舉 SQL Server 資料庫 / 資料表
1.資料庫主要是用 System.Data.CommandType 來進行列舉
cmd.CommandType = CommandType.StoredProcedure;
2.資料庫列舉主要是SQL語法(阿~變數名稱我本來要取qs = query string)。
string cs = "select * from information_schema.Tables where TABLE_TYPE='BASE TABLE'";
3.結構列舉也是
string cs = "select * from information_schema.Tables where TABLE_TYPE='BASE TABLE'";
4.這篇範例用法都跟前面幾篇一樣，就小偷懶一下!!!
5.SQL 語法還是我最弱的一環，加油吧!!!余小章~
http://www.1keydata.com/tw/sql/sql.html
如何列舉SQL Server 中的資料庫
C#
private void Form1_Load(object sender, EventArgs e)
{
string cs = "data source=.\\sqlexpress;Integrated Security=SSPI;";
//1.建立連線
cn = new SqlConnection(cs);
if (cn.State != ConnectionState.Open)
{
//2.開啟連線
cn.Open();
}
else
{
Console.WriteLine("資料庫已被佔用");
return;
}
using (SqlCommand cmd = new SqlCommand("sp_helpdb", cn))
{
//3.使用CommandType.StoredProcedure 列舉 資料庫
cmd.CommandType = CommandType.StoredProcedure;
//4.利用SqlDataReader讀取
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
//5.Show出資料庫
this.comboBox1.Items.Add(dr[0].ToString());
}
}
}
}
VB
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim cs As String = "data source=.\sqlexpress;Integrated Security=SSPI;"
'1.建立連線
cn = New SqlConnection(cs)
If cn.State <> ConnectionState.Open Then
'2.開啟連線
cn.Open()
Else
Console.WriteLine("資料庫已被佔用")
Return
End If
Using cmd As New SqlCommand("sp_helpdb", cn)
'3.使用CommandType.StoredProcedure 列舉 資料庫
cmd.CommandType = CommandType.StoredProcedure
'4.利用SqlDataReader讀取
Using dr As SqlDataReader = cmd.ExecuteReader()
While dr.Read()
'5.Show出資料庫
Me.comboBox1.Items.Add(dr(0).ToString())
End While
End Using
End Using
End Sub
如何列舉資料庫中的資料表結構
C#
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
{
string cs = "select * from information_schema.Tables where TABLE_TYPE='BASE TABLE'";
string strDB = comboBox1.SelectedItem.ToString();
this.dataGridView1.DataSource = null;
//變更資料庫
cn.ChangeDatabase(comboBox1.SelectedItem.ToString());
using (SqlCommand cmd = new SqlCommand(cs, cn))
{
using (SqlDataReader dr = cmd.ExecuteReader())
{
this.comboBox2.Items.Clear();
while (dr.Read())
{
//SHOW出資料表
this.comboBox2.Items.Add(dr["TABLE_NAME"].ToString());
}
}
}
}
private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
{
string strTable = comboBox2.SelectedItem.ToString();
string cs = "select * from information_schema.columns where table_name=@table_name";
//變更資料庫
cn.ChangeDatabase(comboBox1.SelectedItem.ToString());
using (SqlCommand cmd = new SqlCommand(cs, cn))
{
cmd.Parameters.Add(new SqlParameter("@table_name", strTable));
//資料庫繫結
DataSet ds = new DataSet();
using (SqlDataAdapter da = new SqlDataAdapter(cmd))
{
da.Fill(ds, "schema");
}
this.dataGridView1.DataSource = ds.Tables["schema"];
}
}
VB
Private Sub comboBox1_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles comboBox1.SelectedIndexChanged
Dim cs As String = "select * from information_schema.Tables where TABLE_TYPE='BASE TABLE'"
Dim strDB As String = comboBox1.SelectedItem.ToString()
Me.dataGridView1.DataSource = Nothing
cn.ChangeDatabase(comboBox1.SelectedItem.ToString())
Using cmd As New SqlCommand(cs, cn)
Using dr As SqlDataReader = cmd.ExecuteReader()
Me.comboBox2.Items.Clear()
While dr.Read()
Me.comboBox2.Items.Add(dr("TABLE_NAME").ToString())
End While
End Using
End Using
End Sub
Private Sub comboBox2_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles comboBox2.SelectedIndexChanged
Dim strTable As String = comboBox2.SelectedItem.ToString()
Dim cs As String = "select * from information_schema.columns where table_name=@table_name"
'變更資料庫
cn.ChangeDatabase(comboBox1.SelectedItem.ToString())
Using cmd As New SqlCommand(cs, cn)
cmd.Parameters.Add(New SqlParameter("@table_name", strTable))
'資料庫繫結
Dim ds As New DataSet()
Using da As New SqlDataAdapter(cmd)
da.Fill(ds, "schema")
End Using
Me.dataGridView1.DataSource = ds.Tables("schema")
End Using
End Sub
範例下載：SQLEnum.rar
VB的Code怎麼看都比C#長很多…是因為VB幫做的事比較多
光是Handle就一大票了……
若有謬誤，煩請告知，新手發帖請多包涵
Microsoft MVP Award 2010~2017 C# 第四季
Microsoft MVP Award 2018~2022 .NET
