private void RetrieveData()
{
bool lb_result = false;
//写dgv_columns
string ls_sql = "SELECT Table_Name, Field_Name,Field_Desc, Field_Type, IsPK,CanNull,ShowIndex,IsIdentity " +
" FROM IF_field_infor Where Table_Name ='" + _TableName + "' order by ShowIndex";
DataTable ldt = new DataTable();
if (AccessDBop.SQLSelect(ls_sql, ref ldt))
{
dgv_columns.DataSource = ldt;
}
//写cb_CompareTable 下拉
if (_TableType != "Z")
{
ls_sql = "select Table_Name,Table_desc From IF_Table_infor" +
" Where Table_Type = 'Z'";
ldt = new DataTable();
if (AccessDBop.SQLSelect(ls_sql, ref ldt))
{
if (ldt.Rows.Count > 0)
{
cb_CompareTable.DisplayMember = "Table_desc";
cb_CompareTable.ValueMember = "Table_Name";
cb_CompareTable.DataSource = ldt;
cb_CompareTable.SelectedIndex = -1;
cb_CompareTable.SelectedIndex = 0;
}
}
}
if (ucbTablePosition.Text == Project.DB_Alias)
{
ls_sql = "select Db_type,Db_name,Db_server,Db_User,Db_Password From IF_DBConnectionInfor" +
" Where bz = 0";
}
else if (ucbTablePosition.Text == Project.theirDB_Alias)
{
ls_sql = "select Db_type,Db_name,Db_server,Db_User,Db_Password From IF_DBConnectionInfor" +
" Where bz = 1";
}
else
{
ls_sql = "";
}
ldt = new DataTable();
if (AccessDBop.SQLSelect(ls_sql, ref ldt))
{
if (ldt.Rows.Count > 0)
{
//constr = "";
ls_type = ldt.Rows[0]["Db_type"].ToString();
if (ls_type == "sql")
{
//constr = "server = " + ldt.Rows[0]["Db_server"]
// + ";database = " + ldt.Rows[0]["Db_name"]
//+ ";Uid = " + ldt.Rows[0]["Db_User"]
//+ ";pwd = " + ldt.Rows[0]["Db_Password"];
ls_sql = "SELECT Name FROM SysObjects Where XType in('U','V') ORDER BY Name";
ldt = new DataTable();
if (ucbTablePosition.Text == Project.DB_Alias)
{
lb_result = SQLServerDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt, "");
}
else
{
lb_result = SQLServerDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt, "");
}
if (lb_result)
{
cb_DatabaseTable.DisplayMember = "Name";
cb_DatabaseTable.ValueMember = "Name";
cb_DatabaseTable.DataSource = ldt;
cb_DatabaseTable.SelectedIndex = -1;
cb_DatabaseTable.SelectedIndex = 0;
}
dgv_columns.Columns["ISIDENTITY"].Visible = true;
}
else if (ls_type == "ora")
{
//constr = "server = " + ldt.Rows[0]["Db_server"]
// + ";Uid = " + ldt.Rows[0]["Db_User"]
// + ";pwd = " + ldt.Rows[0]["Db_Password"];
ls_sql = ("SELECT table_name as Name from user_tables where tablespace_name in"
+ " ("
+ " select default_tablespace from dba_users"
+ " where username = '******'"
+ " ) UNION SELECT VIEW_NAME AS NAME FROM User_Views").ToUpper();
ldt = new DataTable();
if (ucbTablePosition.Text == Project.DB_Alias)
{
lb_result = OracleDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
}
else
{
lb_result = OracleDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
}
if (lb_result)
{
cb_DatabaseTable.DisplayMember = "Name";
cb_DatabaseTable.ValueMember = "Name";
cb_DatabaseTable.DataSource = ldt;
cb_DatabaseTable.SelectedIndex = -1;
cb_DatabaseTable.SelectedIndex = 0;
}
dgv_columns.Columns["ISIDENTITY"].Visible = false;
}
else if (ls_type == "mysql")
{
string db_name = ucbTablePosition.Text == Project.DB_Alias ? Project.DbName : Project.theirDbName;
ls_sql = "select table_name as name from INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' and table_schema = '" + db_name + "'";
ldt = new DataTable();
if (ucbTablePosition.Text == Project.DB_Alias)
{
lb_result = MySqlDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
}
else
{
lb_result = MySqlDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
}
if (lb_result)
{
cb_DatabaseTable.DisplayMember = "Name";
cb_DatabaseTable.ValueMember = "Name";
cb_DatabaseTable.DataSource = ldt;
cb_DatabaseTable.SelectedIndex = -1;
cb_DatabaseTable.SelectedIndex = 0;
}
dgv_columns.Columns["ISIDENTITY"].Visible = true;
}
else if (ls_type == "access")
{
//string db_name = ucbTablePosition.Text == Project.DB_Alias ? Project.DbName : Project.theirDbName;
//ls_sql = "SELECT name FROM MSYSOBJECTS WHERE TYPE=1 AND NAME NOT LIKE 'Msys*'";
//ldt = new DataTable();
//if (ucbTablePosition.Text == Project.DB_Alias)
// lb_result = AccessDBop.SQLSelect(Project, ls_sql, ref ldt);
//else
// lb_result = AccessDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
//if (lb_result)
//{
// cb_DatabaseTable.DisplayMember = "Name";
// cb_DatabaseTable.ValueMember = "Name";
// cb_DatabaseTable.DataSource = ldt;
// cb_DatabaseTable.SelectedIndex = -1;
// cb_DatabaseTable.SelectedIndex = 0;
//}
//dgv_columns.Columns["ISIDENTITY"].Visible = true;
MessageBox.Show("暂不支持access");
}
}
}
}
private void InsertPublicRowsFromDataBase(string as_formtype_id)
{
//if (_TableName != "") return;
string ls_sql = "";
if (ls_type == "sql")
{
ls_sql = "select "
+ " '' as Table_Name"
+ " ,A.column_name as Field_Name"
+ " ,'' as Field_Desc"
+ " , data_type + case when character_maximum_length Is null or character_maximum_length > 8000 then '' else '(' + convert(varchar(10),character_maximum_length) + ')' end as Field_Type"
+ " ,case when B.COLUMN_NAME Is null then 'N' else 'Y' end as IsPK"
+ " ,case when A.IS_NULLABLE = 'No' then 'N' else 'Y' end as CanNull"
+ " ,0 as ShowIndex"
+ " ,case when COLUMNPROPERTY(OBJECT_ID('" + as_formtype_id + "'),A.COLUMN_NAME,'IsIdentity') = 1 then 'Y' else 'N' end as IsIdentity"
+ " from information_schema.columns A"
+ " left join"
+ " INFORMATION_SCHEMA .KEY_COLUMN_USAGE B"
+ " on "
+ " A.table_name = B.table_name"
+ " and A.column_name = B.column_name"
+ " where A.table_name='" + as_formtype_id + "'";
}
else if (ls_type == "ora")
{
ls_sql = "SELECT '' AS TABLENAME, B.COLUMN_NAME AS FIELDNAME, COMMENTS AS FIELD_DESC,"
+ " CASE WHEN (B.DATA_TYPE = 'INTERGER') OR (B.DATA_TYPE = 'FLOAT') OR (B.DATA_TYPE = 'DATE') THEN B.DATA_TYPE "
+ " WHEN B.DATA_TYPE = 'NUMBER' THEN B.DATA_TYPE || '(' || TO_CHAR(B.DATA_LENGTH) || ',' || TO_CHAR(DATA_SCALE) || ')'"
+ " ELSE B.DATA_TYPE || '(' || B.DATA_LENGTH || ')' END AS FIELD_TYPE,"
+ " CASE WHEN C.COLUMN_NAME IS NULL THEN 'N' ELSE 'Y' END AS ISPK,"
+ " B.NULLABLE AS CANNULL,"
+ " 0 AS SHOWINDEX,"
+ " 'N' AS ISIDENTITY"
+ " FROM USER_COL_COMMENTS A INNER JOIN USER_TAB_COLUMNS B"
+ " ON A.TABLE_NAME = B.TABLE_NAME AND A.COLUMN_NAME = B.COLUMN_NAME"
+ " AND A.TABLE_NAME = '" + as_formtype_id + "'"
+ " LEFT JOIN"
+ " ("
+ " SELECT * FROM DBA_CONS_COLUMNS "
+ " WHERE CONSTRAINT_NAME "
+ " IN (SELECT CONSTRAINT_NAME "
+ " FROM DBA_CONSTRAINTS"
+ " WHERE OWNER='SCM_MAIN'"
+ " AND CONSTRAINT_TYPE='P'"
+ " AND TABLE_NAME = '" + as_formtype_id + "'"
+ " )"
+ " ORDER BY CONSTRAINT_NAME,POSITION"
+ " ) C"
+ " ON A.TABLE_NAME = C.TABLE_NAME"
+ " AND B.COLUMN_NAME = C.COLUMN_NAME";
}
else if (ls_type == "mysql")
{
ls_sql = "select '' as Table_Name, column_name as Field_Name,'' as Field_Desc ,column_type as Field_Type,"
+ " case when COLUMN_KEY = 'PRI' then 'Y' else 'N' end as IsPK,"
+ " case when IS_NULLABLE = 'No' then 'N' else 'Y' end as CanNull,"
+ " 0 as ShowIndex,"
+ " 'N' as IsIdentity"
+ " from Information_schema.columns"
+ " where table_Name ='" + as_formtype_id + "'";
}
else if (ls_type == "access")
{
MessageBox.Show("暂不支持自动获取!");
}
DataTable ldt = new DataTable();
bool lb_result = false;
if (ls_type == "sql")
{
if (ucbTablePosition.Text == Project.DB_Alias)
{
lb_result = SQLServerDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt, "");
}
else
{
lb_result = SQLServerDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt, "");
}
}
else if (ls_type == "ora")
{
if (ucbTablePosition.Text == Project.DB_Alias)
{
lb_result = OracleDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
}
else
{
lb_result = OracleDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
}
}
else if (ls_type == "mysql")
{
if (ucbTablePosition.Text == Project.DB_Alias)
{
lb_result = MySqlDBop.SQLSelect(Project.DB_Connection, ls_sql, ref ldt);
}
else
{
lb_result = MySqlDBop.SQLSelect(Project.TheirDB_Connection, ls_sql, ref ldt);
}
}
if (lb_result)
{
DataTable ldt_old = (DataTable)dgv_columns.DataSource;
for (int i = ldt_old.Rows.Count - 1; i >= 0; i--)
{
if (ldt_old.Rows[i].RowState != DataRowState.Deleted)
{
ldt_old.Rows[i].Delete();
}
}
foreach (DataRow dr in ldt.Rows)
{
DataRow ldr = ldt_old.NewRow();
ldr[1] = dr[1];
ldr[2] = dr[2];
ldr[3] = dr[3];
ldr[4] = dr[4];
ldr[5] = dr[5];
ldr[6] = dr[6];
ldr[7] = dr[7];
ldt_old.Rows.Add(ldr);
}
}
}