C# 列举 服务器,数据库,表,字段等 SQL Server 2000 与 Access

C#

列举局域网内所有的SQL Server2000服务器,数据库,表

使用的对象:SQLDMO     (通过添加引用:COM,Microsoft SQLDMO Object Library 即可)

            SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
            SQLDMO.NameList sqlServers = sqlApp.ListAvailableSQLServers();
            com_Sser.Items.Clear();
            for (int i = 1; i <= sqlServers.Count; i++)
            {
                object srv = sqlServers.Item(i);
                if (srv != null)
                {
                    this.com_Sser.Items.Add(srv);
                }
            }

 

列举服务器里的所有数据库              //通过检查master数据库里的sysdatabases表

            //构造连接字符串,列举数据库
            string constr = "Data Source=" + com_Sser.Items[com_Sser.SelectedIndex].ToString() + ";uid="
                + txt_JoinUid.Text + ";pwd=" + txt_JoinPwd.Text + ";Initial Catalog=master";

            string sqlstr = "select name from sysdatabases";
            SqlDataAdapter sqldap = new SqlDataAdapter(sqlstr, constr);
            DataSet ds = new DataSet();
            try
            {
                sqldap.Fill(ds,"DataBases");
                com_Sdb.DataSource = ds.Tables["DataBases"].DefaultView;
                com_Sdb.DisplayMember = "name";
            }
            catch
            {
                MessageBox.Show("连接有误");
                return;
            }

列举数据库里的所有表:

            string constr = "Data Source=" + com_Sser.Items[com_Sser.SelectedIndex].ToString() + ";uid="
                + txt_JoinUid.Text + ";pwd=" + txt_JoinPwd.Text + ";Initial Catalog=" +
                com_Sdb.Text;
            string sqlstr = "select name from sysobjects where xtype='U '";
            SqlDataAdapter sqldap = new SqlDataAdapter(sqlstr, constr);
            DataSet ds = new DataSet();
            try
            {
                sqldap.Fill(ds, "DataBases");
                com.DataSource = ds.Tables["DataBases"].DefaultView;
                com.DisplayMember = "name";
            }
            catch
            {
                MessageBox.Show("连接有误");
                return;
            }

列举表的所有字段.这个与Access的方式基本是一样的:

            string constr = "Data Source=" + com_Sser.Items[com_Sser.SelectedIndex].ToString() + ";uid="
                + txt_JoinUid.Text + ";pwd=" + txt_JoinPwd.Text + ";Initial Catalog=" +
                com_Sdb.Text;
            string sqlstr = "select * from " + comTab.Text;
            SqlDataAdapter sqldap = new SqlDataAdapter(sqlstr, constr);
            DataSet ds = new DataSet();
            try
            {
                sqldap.Fill(ds, "tab");
                com.Items.Clear();
                foreach (DataColumn datacol in ds.Tables["tab"].Columns)
                {
                    com.Items.Add(datacol.Caption);
                }
            }
            catch
            {
                MessageBox.Show("连接有误");
                return;
            }

Access:

打开Access:

            OpenFileDialog a = new OpenFileDialog();
            a.Filter = "Access 2003 *.mdb|*.mdb";//关于Filter:"acc *.mdb|*.mdb;*.*|bcc *.*|*.*"
            a.ShowDialog();
            if (File.Exists(a.FileName))
            {
                txt_Apath.Text = a.FileName;
            }

列举Access中的所有表

            string constr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + txt_Apath.Text +
            ";Jet OLEDB:DataBase Password=" + txt_JoinPwd.Text;
            DataTable dt = new DataTable();
            OleDbConnection oleConn = new OleDbConnection(constr);
            try
            {
                oleConn.Open();
                dt = oleConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
                int iCount = dt.Rows.Count;
                com.Items.Clear();
                for (int i = 0; i < iCount; i++)
                {
                    com.Items.Add(dt.Rows[i]["Table_name"].ToString());//这些字符串的意义,我也不懂
                }
            }
            catch
            {
                MessageBox.Show("连接出错");
                oleConn.Close();
                return;
            }