public CreateDataSqlParams GetCreateOracleSql()
{
if (_tableDataRow == null)
{
return(null);
}
StringBuilder t1 = new StringBuilder();
StringBuilder t2 = new StringBuilder();
OracleParameter[] parms = new OracleParameter[_tableDataRow.tableColumnNames.Length];
for (int i = 0; i < _tableDataRow.tableColumnNames.Length; i++)
{
Type dataType = _tableDataRow.tableColumnTypes[i];
string culn = OracleTableClass.GetOracleColumnName(_tableDataRow.tableColumnNames[i]);
t1.Append(culn + ",");
t2.Append(":" + culn + ",");
parms[i] = new OracleParameter(":" + culn, _tableDataRow.TableValues[i]);
}
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into " + _tableDataRow.tableName + "(");
strSql.Append(t1.ToString().TrimEnd(','));
strSql.Append(")");
strSql.Append(" values (");
strSql.Append(t2.ToString().TrimEnd(','));
strSql.Append(")");
CreateDataSqlParams sqlp = new CreateDataSqlParams();
sqlp.sql = strSql.ToString();
sqlp.sql_params = parms;
return(sqlp);
}
public List <DbData> GetTableData(string tableName, int start = 0, int length = -1)
{//select * from(select t.*,ROWNUM r from pt_oper_log t )where r>50 and r<=100
string row = "row" + Guid.NewGuid().ToString("N").Substring(24);
//private string _sqlFormat = "select * from (select t.*, rownum {0} from ({1}) t where rownum <= {2}) where {0} > {3}";
string sql = "select * from(select t.*,ROWNUM " + row + " from " + tableName + " t) where " + row + ">" + start;
if (length > 0)
{
sql += " and " + row + "<=" + (start + length);
}
DataTable dt = _oracleHelper.ExecuteDataTable(sql);
List <DbData> datas = new List <DbData>();
OracleTableClass otc = GetTable(tableName) as OracleTableClass;
string[] tableColumnNames = new string[dt.Columns.Count - 1];
object[] tableColumnDbTypes = new object[dt.Columns.Count - 1];
Type[] tableColumnTypes = new Type[dt.Columns.Count - 1];
for (int i = 0; i < dt.Columns.Count - 1; i++)
{
tableColumnNames[i] = dt.Columns[i].ColumnName;
tableColumnTypes[i] = dt.Columns[i].DataType;
if (otc != null)
{
tableColumnDbTypes[i] = MyDbHelper.GetDbDataType(Convert.ToString(((OracleTableColClass)(otc[dt.Columns[i].ColumnName])).data_type), MyDbType.Oracle, MyDbType.Oracle);
}
else
{
tableColumnDbTypes[i] = dt.Columns[i].DataType;
}
}
foreach (DataRow item in dt.Rows)
{
TableDataRow tdr = new TableDataRow();
tdr.dbType = MyDbType.Oracle;
object[] tableValues = new object[dt.Columns.Count - 1];
for (int i = 0; i < dt.Columns.Count - 1; i++)
{
tableValues[i] = item.ItemArray[i];
}
DbData dbData = new DbData(MyDbType.Oracle, tableName, tableColumnNames, tableColumnTypes, tableColumnDbTypes, tableValues);
datas.Add(dbData);
}
return(datas);
}
public List <ITableClass> GetTables()
{
if (_tables.Count == 0 && !_isLoadTabs)
{
string sql = "select a.table_name,a.tablespace_name,a.pct_free,a.pct_used,a.pct_increase,a.ini_trans,a.max_trans,a.initial_extent,a.next_extent,a.min_extents,a.max_extents,a.buffer_pool,b.comments from user_tables a left join user_tab_comments b on a.table_name=b.table_name order by a.table_name";
DataTable dt = _oracleHelper.ExecuteDataTable(sql);
foreach (DataRow item in dt.Rows)
{
OracleTableClass otc = new OracleTableClass(item);
otc.SetOracleHelper(_oracleHelper);
_tables.Add(otc);
}
_isLoadTabs = true;
}
List <ITableClass> t = new List <ITableClass>();
t.AddRange(_tables);
return(t);
}
public List<ITableClass> GetTables()
{
if (_tables.Count == 0 && !_isLoadTabs)
{
string sql = "select a.table_name,a.tablespace_name,a.pct_free,a.pct_used,a.pct_increase,a.ini_trans,a.max_trans,a.initial_extent,a.next_extent,a.min_extents,a.max_extents,a.buffer_pool,b.comments from user_tables a left join user_tab_comments b on a.table_name=b.table_name order by a.table_name";
DataTable dt = _oracleHelper.ExecuteDataTable(sql);
foreach (DataRow item in dt.Rows)
{
OracleTableClass otc = new OracleTableClass(item);
otc.SetOracleHelper(_oracleHelper);
_tables.Add(otc);
}
_isLoadTabs = true;
}
List<ITableClass> t = new List<ITableClass>();
t.AddRange(_tables);
return t;
}
