数据库取表名、列名操作

OracleConnection oracleconn;

SqlConnection sqlserverconn;

MySQLConnection mysqlconn;

 

string constrfororacle = "Data Source=jxorcl; user=scott; password=oracle;";

string constrforsqlserver = "Server=xht; UID=sa; PWD=apts; database=jx_pbatv109";

string constrformysql = "Server=localhost; database=bugfree; user=root; password=";

 

oracleconn = new OracleConnection(constrfororacle);

sqlserverconn = new SqlConnection(constrforsqlserver);

mysqlconn = new MySQLConnection(new MySQLConnectionStrin(server,database,uid,password).AsString);

 

/// <summary>

/// 查询指定数据库中的所有表，oracle、sqlserver、mysql

/// </summary>

/// <param name="databasetype"></param>

/// <returns></returns>

public ArrayList queryTables(string databasetype)

{

    ArrayList list = new ArrayList();

    if (databasetype.Equals("Oracle"))

    {

        try

        {

            string str = "SELECT * FROM user_all_tables order by table_name asc";

            OracleDataAdapter da = new OracleDataAdapter(str, oracleconn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            DataTable dt = ds.Tables[0];

            foreach (DataRow dr in dt.Rows)

            {

                list.Add(dr[0].ToString().Trim());

            }

            return list;

        }

        catch (OracleException ee)

        {

            MessageBox.Show(ee.Message.ToString());

            flag = false;

            return null;

        }

    }

    else if (databasetype.Equals("SQLServer"))

    {

        try

        {

            string str = "SELECT * FROM sys.tables";

            SqlDataAdapter da = new SqlDataAdapter(str, sqlserverconn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            DataTable dt = ds.Tables[0];

            foreach (DataRow dr in dt.Rows)

            {

                list.Add(dr[0].ToString().Trim());

            }

            return list;

        }

        catch (SqlException ee)

        {

            MessageBox.Show(ee.Message.ToString());

            return null;

        }

    }

    else

    {

        try

        {

            string str = "show tables";

            MySQLDataAdapter da = new MySQLDataAdapter(str, mysqlconn);

            DataSet ds = new DataSet();

            da.Fill(ds);

            DataTable dt = ds.Tables[0];

            foreach (DataRow dr in dt.Rows)

            {

                list.Add(dr[0].ToString().Trim());

            }

            return list;

        }

        catch (MySQLException ee)

        {

            MessageBox.Show(ee.Message.ToString());

            flag = false;

            return null;

        }

    }

 

}

 

/// <summary>

/// 查询指定表中的所有列，oracle、sqlserver、mysql

/// </summary>

/// <param name="databasetype"></param>

/// <param name="tablename"></param>

/// <returns></returns>

public DataTable queryColumns(string databasetype, string tablename)

{

    ArrayList list = new ArrayList();

    if (databasetype.Equals("Oracle"))

    {

        string str = "SELECT column_name FROM  user_tab_cols wheretable_name='"+ tablename + "'";

        OracleDataAdapter da = new OracleDataAdapter(str, oracleconn);

        DataSet ds = new DataSet();

        da.Fill(ds);

        DataTable dt = ds.Tables[0];

        DataRow dr = dt.NewRow();

        dr[0] = "";

        dt.Rows.InsertAt(dr, 0);

        return dt;

    }

    else if (databasetype.Equals("SQLServer"))

    {

        string str = "SELECT name FROM  sys.columns where object_id = (select object_id from sys.tables where name = '" + tablename + "')";

        SqlDataAdapter da = new SqlDataAdapter(str, sqlserverconn);

        DataSet ds = new DataSet();

        da.Fill(ds);

        DataTable dt = ds.Tables[0];

        DataRow dr = dt.NewRow();

        dr[0] = "";

        dt.Rows.InsertAt(dr, 0);

        return dt;

    }

    else

    {

        string str = "show fields from " + tablename + "";

        MySQLDataAdapter da = new MySQLDataAdapter(str, mysqlconn);

        DataSet ds = new DataSet();

        da.Fill(ds);

        DataTable dt = ds.Tables[0];

        DataRow dr = dt.NewRow();

        dr[0] = "";

        dt.Rows.InsertAt(dr, 0);

        return dt;

    }

}