robot是咱建立的数据库,我用Show table获取robot数据库中的所有表,但是reader( MySqlDataReader 类型的)的Values集合中只有第一个表,其他表没有获取到,请哪位高手,看一下,下面是我写的测试代码。using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
using MySql.Data.MySqlClient;
namespace Try_one
{
class MySQLResults
{
public MySQLResults(string sql)
{
MySqlConnection conn = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
conn = new MySqlConnection("Server=localhost;User Id=robot;Password=robot;Persist Security Info=True;Database=robot");
command = conn.CreateCommand();
command.CommandText = sql;
conn.Open(); reader = command.ExecuteReader();
}
catch (MySqlException se)
{
conn.Close();
command = null;
reader.Close();
}
}
}
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent(); }
private void btTest_Click(object sender, EventArgs e)
{
new MySQLResults("show tables");
}
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
using MySql.Data.MySqlClient;
namespace Try_one
{
class MySQLResults
{
public MySQLResults(string sql)
{
MySqlConnection conn = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
conn = new MySqlConnection("Server=localhost;User Id=robot;Password=robot;Persist Security Info=True;Database=robot");
command = conn.CreateCommand();
command.CommandText = sql;
conn.Open(); reader = command.ExecuteReader();
}
catch (MySqlException se)
{
conn.Close();
command = null;
reader.Close();
}
}
}
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent(); }
private void btTest_Click(object sender, EventArgs e)
{
new MySQLResults("show tables");
}
列出当前DB中所有表:
select name from dbo.sysobjects where xtype='u' and (not name LIKE 'dtproperties')
列出表中所有字段:
SELECT dbo.sysobjects.name as Table_name, dbo.syscolumns.name AS Column_name
FROM dbo.syscolumns INNER JOIN
dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id
WHERE dbo.sysobjects.name='TM_User'and (dbo.sysobjects.xtype = 'u') AND (NOT (dbo.sysobjects.name LIKE 'dtproperties'))
mysql中,用show tables语句没错.
To楼主,
检查reader是不是已经关闭了.
select name from dbo.sysobjects where xtype='u' and (not name LIKE 'dtproperties')直接执行就可以了!
使用SqlConnection的GetSchema方法,指定架构名为Tables,就可以获取SqlConnection连接字符串中指定数据库的所有表名。
使用SqlConnection的GetSchema方法,指定架构名为Tables,就可以获取SqlConnection连接字符串中指定数据库的所有表名。
使用SqlConnection的GetSchema方法,指定架构名为Tables,就可以获取SqlConnection连接字符串中指定数据库的所有表名。我试过了,不行啊,倒数第二句报异常! public override DataTable GetSchema(string collectionName)
{
if (collectionName == null)
collectionName = SchemaProvider.MetaCollection; return GetSchema(collectionName, null);
}
public override DataTable GetSchema(string collectionName, string[] restrictionValues)
{
/* string msg = String.Format("collection name2 = {0}", collectionName);
if (restrictionValues != null)
foreach (string s in restrictionValues)
{
msg += String.Format(" res={0}", s);
}
System.Windows.Forms.MessageBox.Show(msg);
*/
if (collectionName == null)
collectionName = SchemaProvider.MetaCollection; string[] restrictions = null;
if (restrictionValues != null)
{
restrictions = (string[]) restrictionValues.Clone(); for (int x = 0; x < restrictions.Length; x++)
{
string s = restrictions[x];
if (s != null)
{
if (s.StartsWith("`"))
s = s.Substring(1);
if (s.EndsWith("`"))
s = s.Substring(0, s.Length - 1);
restrictions[x] = s;
}
}
} DataTable dt = schemaProvider.GetSchema(collectionName, restrictions);
return dt;
}
public DataTable GetTableNames(string sql)
{
MySqlConnection conn = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
DataTable dt = new DataTable();
try
{
conn = new MySqlConnection("Server=localhost;User Id=robot;Password=robot;Persist Security Info=True;Database=robot");
command = conn.CreateCommand();
command.CommandText = sql;
conn.Open(); reader = command.ExecuteReader();
dt.Load(reader); }
catch (MySqlException se)
{
conn.Close();
command = null;
reader.Close();
}
return dt;
}
}
然后在需要显示的时候,绑定到一个空间上显示出来就行了
select name from master..sysdatabases--所有表名
SELECT name FROM sysobjects WHERE (xtype = 'U')
应该是:MySQLDataReader MyReader = MyCmd.ExecuteReader();
while (MyReader.Read())
{ }
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
public DataTable GetTableNames(string sql)
{
MySqlConnection conn = null;
MySqlCommand command = null;
MySqlDataReader reader = null;
DataTable dt = new DataTable();
try
{
conn = new MySqlConnection("Server=localhost;User Id=robot;Password=robot;Persist Security Info=True;Database=robot");
command = conn.CreateCommand();
command.CommandText = sql;
conn.Open(); reader = command.ExecuteReader();
dt.Load(reader); }
catch (MySqlException se)
{
conn.Close();
command = null;
reader.Close();
}
return dt;
}
private void btTest_Click(object sender, EventArgs e)
{
DataTable table = GetTableNames("show tables");
} }
}
你网form上托一个控件DataGridView然后
private void btTest_Click(object sender, EventArgs e)
{
DataTable table = GetTableNames("show tables");
dataGridView1.DataSource = table;
}
如果您想获取数据库中所有TableName的话,可以使用以上其他楼主的方式进行取得.
但是可以使用更加简单的方式进行获取,在ADO.NET中列出了在SqlClientMetaDataCollectionNames枚举中所支持的架构.
Sample Codes:using System;
using System.Data;
using System.Data.SqlClient;
public class Test
{
static void Main()
{
SqlConnectionStringBuilder csb = CreateStringBuilder();
SqlConnection conn = new SqlConnection(csb.ConnectionString);
DataTable tbl = conn.GetSchema(SqlClientMetaDataCollectionNames.Tables); //执行相关显示
DisplayTableInfo(tbl);
} static SqlConnectionStringBuilder CreateStringBuilder() { //... }
static void DisplayTableInfo(DataTable tbl) { //... }
}