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