public static object Express() { ////Express方法, 用户可在 SELECT 字段中注入自定义的一段SQL脚本。 ////因为ODA 的表达式,是应用者注入的一段SQL语句,所以SQL注入的风险及是否可以跨数据库,就用开发者掌握了。 ODAParameter p1 = new ODAParameter() { ColumnName = "Params1", DBDataType = ODAdbType.OVarchar, Direction = System.Data.ParameterDirection.Input, ParamsName = ODAParameter.ODAParamsMark + "Params1", ParamsValue = "我是第一个参数的值", Size = 2000 }; ODAParameter p2 = new ODAParameter() { ColumnName = "Params2", DBDataType = ODAdbType.OVarchar, Direction = System.Data.ParameterDirection.Input, ParamsName = ODAParameter.ODAParamsMark + "Params2", ParamsValue = "这是SQL语句注入", Size = 2000 }; ODAContext ctx = new ODAContext(); var U = ctx.GetCmd <CmdSysUser>(); object data = U.Where(U.ColStatus == "O", U.ColIsLocked == "N") .Select(U.Function.Express("1+1").As("COMPUTED"), U.Function.Express(" null ").As("NULL_COLUMN"), U.Function.Express(" 'Function( + " + ODAParameter.ODAParamsMark + "Params1, " + ODAParameter.ODAParamsMark + "Params2)' ", p1, p2).As("SQL_Injection")); return(data); }
public override DataTable GetUserProcedureArguments(string ProcedureName) { string SqlArg = new StringBuilder().Append("SELECT arg.object_name PROCEDURE_NAME, arg.ARGUMENT_NAME,arg.DATA_TYPE,") .Append(" DECODE(arg.DATA_TYPE,'CHAR','OChar','VARCHAR','OVarchar','VARCHAR2','OVarchar','NVARCHAR2','OVarchar','MLSLABEL','OVarchar','UROWID','OVarchar','URITYPE','OVarchar','CHARACTER','OVarchar','CLOB','OVarchar', ") .Append(" 'INTEGER','OInt','INT','OInt','SMALLINT','OInt','DATE','ODatetime','LONG','ODecimal','DECIMAL','ODecimal','NUMERIC','ODecimal','REAL','ODecimal','NUMBER','ODecimal','BLOB','OBinary','BFILE','OBinary','PL/SQL TABLE','OArrary','REF CURSOR','OTable','OVarchar') ODA_DATATYPE, ") .Append(" arg.POSITION,arg.IN_OUT DIRECTION, ") .Append(" NVL(DECODE(arg.DATA_TYPE,'BLOB',-1,'CLOB',-1,arg.DATA_LENGTH),-1) LENGTH") .Append(" from user_objects o,user_arguments arg") .Append(" where o.object_type='PROCEDURE' ") .Append(" and o.OBJECT_NAME = arg.OBJECT_NAME ") .Append(" and arg.PACKAGE_NAME is null") .Append(" and o.OBJECT_ID = arg.OBJECT_ID") .Append(" and o.OBJECT_NAME = @ProcedureName") .Append(" union ") .Append(" select arg.package_name||'.'|| arg.object_name PROCEDURE_NAME, arg.ARGUMENT_NAME, arg.DATA_TYPE,") .Append(" DECODE(arg.DATA_TYPE,'CHAR','OChar','VARCHAR','OVarchar','VARCHAR2','OVarchar','NVARCHAR2','OVarchar','MLSLABEL','OVarchar','UROWID','OVarchar','URITYPE','OVarchar','CHARACTER','OVarchar','CLOB','OVarchar', ") .Append(" 'INTEGER','OInt','INT','OInt','SMALLINT','OInt','DATE','ODatetime','LONG','ODecimal','DECIMAL','ODecimal','NUMERIC','ODecimal','REAL','ODecimal','NUMBER','ODecimal','BLOB','OBinary','BFILE','OBinary','PL/SQL TABLE','OArrary','REF CURSOR','OTable','OVarchar') ODA_DATATYPE, ") .Append(" arg.POSITION,arg.IN_OUT DIRECTION,") .Append(" NVL(DECODE(arg.DATA_TYPE,'BLOB',-1,'CLOB',-1,arg.DATA_LENGTH),-1) LENGTH") .Append(" from user_objects o,user_arguments arg") .Append(" where o.object_type='PACKAGE' ") .Append(" and o.OBJECT_NAME = arg.package_name ") .Append(" and o.OBJECT_ID = arg.OBJECT_ID") .Append(" and arg.package_name||'.'|| arg.object_name =@ProcedureName") .Append(" ORDER BY PROCEDURE_NAME ,POSITION ").ToString(); ODAParameter p = new ODAParameter() { DBDataType = ODAdbType.OVarchar, Direction = ParameterDirection.Input, ParamsName = "@ProcedureName", ParamsValue = ProcedureName, Size = 200 }; DataTable Dttmp = Select(SqlArg, new ODAParameter[] { p }); Dttmp.TableName = "PROCEDURE_ARGUMENTS"; return(Dttmp); }
public static object UserSQL() { ///如果SQL语可以重复使用,或者为有程序更规范,推荐派生 ODACmd 类 重写SQL生成方法 ODAContext ctx = new ODAContext(); var sql = ctx.GetCmd <SQLCmd>(); var data = sql.Select("SELECT * FROM SYS_USER WHERE USER_ACCOUNT = @T1", ODAParameter.CreateParam("@T1", "User1")); return(data); }
void bgw_DoWork(object sender, DoWorkEventArgs e) { string[] rtlMsg = new string[2]; try { StringBuilder sbrlt = new StringBuilder(); TransferParams prm = e.Argument as TransferParams; BackgroundWorker bgw = sender as BackgroundWorker; StringBuilder tblScript = new StringBuilder(); string TargetDB = prm.TargetDB.DBAType.ToString(); var TblPkeys = prm.SourceDB.GetPrimarykey(); for (int i = 0; i < prm.TranTable.Count; i++) { ReportStatus RS = new ReportStatus() { Percent = i * 100 / prm.TranTable.Count, TransObject = prm.TranTable[i], TransType = "Table" }; bgw.ReportProgress(RS.Percent, RS); DataRow[] drs = prm.SrcTables.Select("TABLE_NAME ='" + prm.TranTable[i] + "'"); if (drs == null || drs.Length == 0) { continue; } DBColumnInfo[] ColumnInfo = new DBColumnInfo[drs.Length]; bool isBigData = false; try { for (int j = 0; j < drs.Length; j++) { int Scale = 0; int.TryParse(drs[j]["SCALE"].ToString().Trim(), out Scale); int length = 2000; int.TryParse(drs[j]["LENGTH"].ToString().Trim(), out length); string ColumnName = drs[j]["COLUMN_NAME"].ToString().Trim(); DBColumnInfo DBColInfo = new DBColumnInfo() { ColumnName = ColumnName, ColumnType = drs[j]["DATATYPE"].ToString().Trim(), Length = length, Scale = Scale, NotNull = drs[j]["NOT_NULL"].ToString().Trim().ToUpper() == "Y", }; CurrentDatabase.GetTargetsType(prm.SourceDB.DBAType.ToString(), TargetDB, ref DBColInfo); ColumnInfo[j] = DBColInfo; isBigData = isBigData || DBColInfo.IsBigData; } } catch (Exception ex) { sbrlt.AppendLine(string.Format("表【{0}】字段异常,异常信息:{1} ", prm.TranTable[i], ex.Message)); } string sql = ""; try { string[] Pkeys = null; if (TblPkeys != null && TblPkeys.ContainsKey(prm.TranTable[i])) { Pkeys = TblPkeys[prm.TranTable[i]]; } sql = this.CreateTable(prm.TargetDB, prm.TranTable[i], ColumnInfo, Pkeys); tblScript.AppendLine(sql); } catch (Exception ex) { sbrlt.AppendLine(string.Format("读取表【{0}】主键并生成建表脚本时生异常,异常信信:{1} ", prm.TranTable[i], ex.Message)); } try { if (prm.NeedTransTable) { try { string dropSQL = "DROP TABLE " + prm.TranTable[i]; prm.TargetDB.ExecuteSQL(dropSQL, null); } catch { } prm.TargetDB.ExecuteSQL(sql.ToString(), null); } ReportStatus RST = new ReportStatus() { Percent = (i + 1) * 100 / prm.TranTable.Count, TransObject = "Table [" + prm.TranTable[i] + "] Created", TransType = "Table" }; bgw.ReportProgress(RS.Percent, RST); } catch (Exception ex) { sbrlt.AppendLine(string.Format("创建表【{0}】时发生异常,建表脚本 {1} ,异常信信:{2} ", prm.TranTable[i], sql.ToString(), ex.Message)); } try { if (prm.NeedTransData) { var trgCol = prm.TargetDB.GetTableColumns().Select("TABLE_NAME ='" + prm.TranTable[i] + "'"); ODAParameter[] Oprms = new ODAParameter[trgCol.Length]; for (int j = 0; j < trgCol.Length; j++) { int collng = 2000; int.TryParse(trgCol[j]["LENGTH"].ToString().Trim(), out collng); DBColumnInfo ODAColInfo = new DBColumnInfo() { ColumnName = trgCol[j]["COLUMN_NAME"].ToString(), ColumnType = trgCol[j]["DATATYPE"].ToString().Trim(), Length = collng, IsBigData = isBigData, NoLength = false, Scale = 0, NotNull = false }; CurrentDatabase.GetTargetsType(prm.TargetDB.DBAType.ToString(), "ODA", ref ODAColInfo); ODAdbType OdaType = (ODAdbType)Enum.Parse(typeof(ODAdbType), ODAColInfo.ColumnType, true); Oprms[j] = new ODAParameter() { ColumnName = trgCol[j]["COLUMN_NAME"].ToString(), DBDataType = OdaType, Direction = ParameterDirection.Input, ParamsName = trgCol[j]["COLUMN_NAME"].ToString(), Size = collng }; } int total = 0; int maxR = isBigData ? 50 : 10000; int startIndx = 0; DataTable DT_total = CurrentDatabase.DataSource.Select("SELECT COUNT(*) FROM " + prm.TranTable[i], null); int.TryParse(DT_total.Rows[0][0].ToString(), out total); while (startIndx < total) { ReportStatus RSData0 = new ReportStatus() { Percent = total == 0 ? 0 : startIndx * 100 / total, TransObject = prm.TranTable[i] + " Preparing " + startIndx.ToString() + " ~ " + (startIndx + maxR).ToString() + "/" + total.ToString() + " record ", TransType = "Data" }; bgw.ReportProgress(RS.Percent, RSData0); DataTable Source = CurrentDatabase.DataSource.Select("SELECT * FROM " + prm.TranTable[i], null, startIndx, maxR, null); Source.TableName = prm.TranTable[i]; int endIdx = (startIndx + maxR) > total ? total : startIndx + maxR; ReportStatus RSData1 = new ReportStatus() { Percent = total == 0 ? 0 : endIdx * 100 / total, TransObject = prm.TranTable[i] + " Importing " + startIndx.ToString() + " ~ " + endIdx.ToString() + "/" + total.ToString() + " record ", TransType = "Data" }; bgw.ReportProgress(RS.Percent, RSData1); TarDB.Import(Source, Oprms); startIndx = startIndx + maxR; } } } catch (Exception ex) { sbrlt.AppendLine(string.Format("导入数据到表【{0}】时发生异常:{1} ", prm.TranTable[i], ex.Message)); } } if (sbrlt.Length == 0) { sbrlt.Append("数据复制完成!"); } rtlMsg[0] = sbrlt.ToString(); rtlMsg[1] = tblScript.ToString(); } catch (Exception ex) { rtlMsg[0] = ex.ToString(); } e.Result = rtlMsg; }
void bgw_DoWork(object sender, DoWorkEventArgs e) { try { StringBuilder sbrlt = new StringBuilder(); TransferParams prm = e.Argument as TransferParams; BackgroundWorker bgw = sender as BackgroundWorker; for (int i = 0; i < prm.TranTable.Count; i++) { ReportStatus RS = new ReportStatus() { Percent = i * 100 / prm.TranTable.Count, TransObject = prm.TranTable[i], TransType = "Table" }; bgw.ReportProgress(RS.Percent, RS); DataRow[] drs = prm.SrcTables.Select("TABLE_NAME ='" + prm.TranTable[i] + "'"); if (drs == null || drs.Length == 0) { continue; } DatabaseColumnInfo[] ColumnInfo = new DatabaseColumnInfo[drs.Length]; ODAParameter[] Oprms = new ODAParameter[drs.Length]; bool isBigData = false; for (int j = 0; j < drs.Length; j++) { int Scale = 0; int.TryParse(drs[j]["SCALE"].ToString().Trim(), out Scale); int length = 2000; int.TryParse(drs[j]["LENGTH"].ToString().Trim(), out length); ColumnInfo[j] = prm.TargetDB.ODAColumnToOrigin(drs[j]["COLUMN_NAME"].ToString(), drs[j]["ODA_DATATYPE"].ToString().Trim(), length, Scale); ColumnInfo[j].NotNull = drs[j]["NOT_NULL"].ToString().Trim().ToUpper() == "Y"; ODAdbType DBDataType = ODAdbType.OVarchar; Enum.TryParse <ODAdbType>(drs[j]["ODA_DATATYPE"].ToString().Trim(), out DBDataType); if (DBDataType == ODAdbType.OBinary) { isBigData = true; } Oprms[j] = new ODAParameter() { ColumnName = drs[j]["COLUMN_NAME"].ToString(), DBDataType = DBDataType, Direction = ParameterDirection.Input, ParamsName = drs[j]["COLUMN_NAME"].ToString(), Size = ColumnInfo[j].Length }; } if (prm.NeedTransTable) { string[] Pkeys = prm.SourceDB.GetPrimarykey(prm.TranTable[i]); string tlt = this.CreateTable(prm.TargetDB, prm.TranTable[i], ColumnInfo, Pkeys); if (!string.IsNullOrWhiteSpace(tlt)) { sbrlt.AppendLine(tlt); continue; } ReportStatus RST = new ReportStatus() { Percent = (i + 1) * 100 / prm.TranTable.Count, TransObject = "Table [" + prm.TranTable[i] + "] Created", TransType = "Table" }; bgw.ReportProgress(RS.Percent, RST); } if (prm.NeedTransData) { for (int j = 0; j < drs.Length; j++) { Oprms[j] = new ODAParameter() { ColumnName = drs[j]["COLUMN_NAME"].ToString(), DBDataType = (ODAdbType)Enum.Parse(typeof(ODAdbType), drs[j]["ODA_DATATYPE"].ToString().Trim()), Direction = ParameterDirection.Input, ParamsName = drs[j]["COLUMN_NAME"].ToString(), Size = ColumnInfo[j].Length }; } int total = 0; int maxR = isBigData ? 50 : 10000; int startIndx = 0; DataTable DT_total = CurrentDatabase.DataSource.Select("SELECT COUNT(*) FROM " + prm.TranTable[i], null); int.TryParse(DT_total.Rows[0][0].ToString(), out total); while (startIndx < total) { ReportStatus RSData0 = new ReportStatus() { Percent = total == 0 ? 0 : startIndx * 100 / total, TransObject = prm.TranTable[i] + " Preparing " + startIndx.ToString() + " ~ " + (startIndx + maxR).ToString() + "/" + total.ToString() + " record ", TransType = "Data" }; bgw.ReportProgress(RS.Percent, RSData0); DataTable Source = CurrentDatabase.DataSource.Select("SELECT * FROM " + prm.TranTable[i], null, startIndx, maxR, null); Source.TableName = prm.TranTable[i]; int endIdx = (startIndx + maxR) > total ? total : startIndx + maxR; ReportStatus RSData1 = new ReportStatus() { Percent = total == 0 ? 0 : endIdx * 100 / total, TransObject = prm.TranTable[i] + " Importing " + startIndx.ToString() + " ~ " + endIdx.ToString() + "/" + total.ToString() + " record ", TransType = "Data" }; bgw.ReportProgress(RS.Percent, RSData1); TarDB.Import(Source, Oprms); startIndx = startIndx + maxR; } } } if (sbrlt.Length == 0) { sbrlt.Append("数据复制完成!"); } e.Result = sbrlt.ToString(); } catch (Exception ex) { e.Result = ex.ToString(); } }
void bgw_DoWork(object sender, DoWorkEventArgs e) { string[] rtlMsg = new string[2]; try { StringBuilder sbrlt = new StringBuilder(); TransferParams prm = e.Argument as TransferParams; BackgroundWorker bgw = sender as BackgroundWorker; StringBuilder tblScript = new StringBuilder(); string TargetDB = prm.TargetDB.DBAType.ToString(); for (int i = 0; i < prm.TranTable.Count; i++) { ReportStatus RS = new ReportStatus() { Percent = i * 100 / prm.TranTable.Count, TransObject = prm.TranTable[i], TransType = "Table" }; bgw.ReportProgress(RS.Percent, RS); DataRow[] drs = prm.SrcTables.Select("TABLE_NAME ='" + prm.TranTable[i] + "'"); if (drs == null || drs.Length == 0) { continue; } DatabaseColumnInfo[] ColumnInfo = new DatabaseColumnInfo[drs.Length]; ODAParameter[] Oprms = new ODAParameter[drs.Length]; bool isBigData = false; for (int j = 0; j < drs.Length; j++) { int Scale = 0; int.TryParse(drs[j]["SCALE"].ToString().Trim(), out Scale); int length = 2000; int.TryParse(drs[j]["LENGTH"].ToString().Trim(), out length); string TargetDBDataType = CurrentDatabase.GetTargetsType(drs[j]["DATATYPE"].ToString().Trim(), prm.TargetDB.DBAType.ToString(), TargetDB); string ODAType = CurrentDatabase.GetTargetsType(drs[j]["DATATYPE"].ToString().Trim(), CurrentDatabase.DataSource.DBAType.ToString(), "ODA"); ODAdbType DBDataType = ODAdbType.OVarchar; Enum.TryParse <ODAdbType>(ODAType, true, out DBDataType); ColumnInfo[j] = new DatabaseColumnInfo() { ColumnType = TargetDBDataType, Length = length <= 0 ? 2000 : length, Name = prm.TargetDB.ToDBColumnName(drs[j]["COLUMN_NAME"].ToString()), NotNull = drs[j]["NOT_NULL"].ToString().Trim().ToUpper() == "Y", Scale = Scale, NoLength = DBDataType == ODAdbType.OBinary || DBDataType == ODAdbType.OInt || DBDataType == ODAdbType.ODatetime }; if (DBDataType == ODAdbType.OBinary) { isBigData = true; } Oprms[j] = new ODAParameter() { ColumnName = drs[j]["COLUMN_NAME"].ToString(), DBDataType = DBDataType, Direction = ParameterDirection.Input, ParamsName = drs[j]["COLUMN_NAME"].ToString(), Size = ColumnInfo[j].Length }; } string[] Pkeys = prm.SourceDB.GetPrimarykey(prm.TranTable[i]); for (int k = 0; Pkeys != null && Pkeys.Length > k; k++) { Pkeys[k] = prm.TargetDB.ToDBColumnName(Pkeys[k]); } string sql = this.CreateTable(prm.TargetDB, prm.TranTable[i], ColumnInfo, Pkeys); tblScript.AppendLine(sql); if (prm.NeedTransTable) { if (prm.NeedTransTable) { try { string dropSQL = "DROP TABLE " + prm.TranTable[i]; prm.TargetDB.ExecuteSQL(dropSQL, null); } catch { } prm.TargetDB.ExecuteSQL(sql.ToString(), null); } ReportStatus RST = new ReportStatus() { Percent = (i + 1) * 100 / prm.TranTable.Count, TransObject = "Table [" + prm.TranTable[i] + "] Created", TransType = "Table" }; bgw.ReportProgress(RS.Percent, RST); } if (prm.NeedTransData) { int total = 0; int maxR = isBigData ? 50 : 10000; int startIndx = 0; DataTable DT_total = CurrentDatabase.DataSource.Select("SELECT COUNT(*) FROM " + prm.TranTable[i], null); int.TryParse(DT_total.Rows[0][0].ToString(), out total); while (startIndx < total) { ReportStatus RSData0 = new ReportStatus() { Percent = total == 0 ? 0 : startIndx * 100 / total, TransObject = prm.TranTable[i] + " Preparing " + startIndx.ToString() + " ~ " + (startIndx + maxR).ToString() + "/" + total.ToString() + " record ", TransType = "Data" }; bgw.ReportProgress(RS.Percent, RSData0); DataTable Source = CurrentDatabase.DataSource.Select("SELECT * FROM " + prm.TranTable[i], null, startIndx, maxR, null); Source.TableName = prm.TranTable[i]; int endIdx = (startIndx + maxR) > total ? total : startIndx + maxR; ReportStatus RSData1 = new ReportStatus() { Percent = total == 0 ? 0 : endIdx * 100 / total, TransObject = prm.TranTable[i] + " Importing " + startIndx.ToString() + " ~ " + endIdx.ToString() + "/" + total.ToString() + " record ", TransType = "Data" }; bgw.ReportProgress(RS.Percent, RSData1); TarDB.Import(Source, Oprms); startIndx = startIndx + maxR; } } } if (sbrlt.Length == 0) { sbrlt.Append("数据复制完成!"); } rtlMsg[0] = sbrlt.ToString(); rtlMsg[1] = tblScript.ToString(); } catch (Exception ex) { rtlMsg[0] = ex.ToString(); } e.Result = rtlMsg; }