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;
}
}