public string GetDataByJson(string Procedure, string para, int numcurr, string dbName, string dbUser, string dbPwd) { _conString = string.Format(_conString, dbName, dbUser, dbPwd); OracleConnection connection; DataTable dt; try { if (!para.Contains("*|*")) { object[] ParaIn = para.Split('|'); string exeStatement, temp = ""; dt = new DataTable(Procedure); connection = new OracleConnection(_conString); connection.Open(); OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); //exeStatement = "Call " + Procedure + "(" + temp + ",:p_cv_1)"; exeStatement = "Call " + Procedure + "(" + temp; String outparr = ""; for (int ou = 1; ou <= numcurr; ou++) { outparr += ",:p_cv_" + ou; command.Parameters.Add(":p_cv_" + ou, OracleType.Cursor).Direction = ParameterDirection.Output; } exeStatement += outparr + ")"; } else //no parram { //select data for grid without any condition exeStatement = "Call " + Procedure + "("; String outparr = ""; for (int ou = 1; ou <= numcurr; ou++) { outparr += ":p_cv_" + ou + ","; command.Parameters.Add(":p_cv_" + ou, OracleType.Cursor).Direction = ParameterDirection.Output; } outparr = outparr.Substring(0, outparr.Length - 1); exeStatement += outparr + ")"; exeStatement = "Call " + Procedure + "(:p_rtn_value)"; command.Parameters.Add(":p_rtn_value", OracleType.Cursor).Direction = ParameterDirection.Output; } command.CommandText = exeStatement; OracleDataReader reader = command.ExecuteReader(); DataSet ds = new DataSet(); do { DataTable table = new DataTable(); table.Load(reader); ds.Tables.Add(table); } while (!reader.IsClosed); connection.Close(); connection.Dispose(); json = CJson.ResultSet2JSONString(ds); return(json); } else { //tach thanh nhieu dong string[] stringSeparators = new string[] { "*|*" }; string[] rows = para.Split(stringSeparators, StringSplitOptions.None); DataTable rs = new DataTable(Procedure); connection = new OracleConnection(_conString); connection.Open(); dt = new DataTable(Procedure); foreach (string row in rows) { string[] stringSeparators1 = new string[] { "|" }; object[] ParaIn = row.Split(stringSeparators1, StringSplitOptions.None); string exeStatement = "", temp = ""; dt = new DataTable(Procedure); try { OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); exeStatement = "Call " + Procedure + "(" + temp + ",:p_cv_1)"; command.Parameters.Add(":p_cv_1", OracleType.Cursor).Direction = ParameterDirection.Output; } else { //select data for grid without any condition exeStatement = "Call " + Procedure + "(:p_cv_1)"; command.Parameters.Add(":p_cv_1", OracleType.Cursor).Direction = ParameterDirection.Output; } command.CommandText = exeStatement; OracleDataReader reader = command.ExecuteReader(); dt.Load(reader); if (rs.Columns.Count == 0) { rs = dt.Clone(); if (rs.Columns.IndexOf("_RSTATUS_".ToLower()) == -1) { rs.Columns.Add("_RSTATUS_"); } } foreach (DataRow r in dt.Rows) { rs.Rows.Add(r.ItemArray); //rs.Rows.Add(new object[] { r.ItemArray, "OK" });// += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "OK", "" }); } } catch (Exception e) { if (rs.Columns.Count == 0) { rs = dt.Clone(); if (rs.Columns.IndexOf("_RSTATUS_".ToLower()) == -1) { rs.Columns.Add("_RSTATUS_"); } } foreach (DataRow r in dt.Rows) { rs.Rows.Add(r.ItemArray); //rs.Rows.Add(new object[] { r.ItemArray, "NOOK" });// += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "OK", "" }); } } } DataSet ds = new DataSet(); ds.Tables.Add(rs); json = CJson.ResultSet2JSONString(ds); connection.Close(); connection.Dispose(); //json = JsonConvert.SerializeObject(rs, Formatting.Indented); return(json); } return(""); } catch (Exception ex) { json = CJson.ResultSet2JSONError(100, ex.Message, Procedure, para); return(json); } }
public string OnExcute(string Procedure, string para, string dbName, string dbUser, string dbPwd) { _conString = string.Format(_conString, dbName, dbUser, dbPwd); OracleConnection connection; try { if (!para.Contains("*|*")) { string[] stringSeparators = new string[] { "|!" }; object[] ParaIn = para.Replace("|!", "|").Split('|'); string exeStatement = "", temp = ""; connection = new OracleConnection(_conString); connection.Open(); string rs = ""; try { OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); exeStatement = "Call " + Procedure + "(" + temp + ")"; } command.CommandText = exeStatement; command.ExecuteNonQuery(); DataSet ds = new DataSet(); DataTable dt = new DataTable(Procedure); dt.Columns.Add("parr"); dt.Columns.Add("status"); dt.Rows.Add(new object[] { para, "OK" }); ds.Tables.Add(dt); rs = CJson.ResultSet2JSONString(ds); } catch (Exception e) { DataSet ds = new DataSet(); DataTable dt = new DataTable(Procedure); dt.Columns.Add("parr"); dt.Columns.Add("status"); dt.Rows.Add(new object[] { para, "NOOK" }); ds.Tables.Add(dt); rs = CJson.ResultSet2JSONString(ds); //rs = string.Format("{0}|!{1}|!{2}", new object[] { para, "NOOK", e.Message }); } connection.Close(); connection.Dispose(); return(rs); } else { //tach thanh nhieu dong string[] stringSeparators = new string[] { "*|*" }; string[] rows = para.Split(stringSeparators, StringSplitOptions.None); connection = new OracleConnection(_conString); connection.Open(); string rs = ""; DataSet ds = new DataSet(); DataTable dt = new DataTable(Procedure); dt.Columns.Add("parr"); dt.Columns.Add("status"); foreach (string row in rows) { //string[] stringSeparators1 = new string[] { "|!" }; //object[] ParaIn = row.Split(stringSeparators, StringSplitOptions.None); object[] ParaIn = row.Replace("|!", "|").Split('|'); string exeStatement = "", temp = ""; try { OracleCommand command = new OracleCommand(Procedure, connection); command.CommandType = CommandType.Text; for (int i = 0; i < ParaIn.Length; i++) { temp += "'" + ParaIn[i].ToString().Trim() + "',"; } if (temp.Length >= 3 && ParaIn.Length > 0) { temp = temp.Substring(0, temp.Length - 1); exeStatement = "Call " + Procedure + "(" + temp + ")"; } command.CommandText = exeStatement; command.ExecuteNonQuery(); dt.Rows.Add(new object[] { row, "OK" }); // rs += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "OK", "" }); } catch (Exception e) { dt.Rows.Add(new object[] { row, "NOOK" }); //rs += string.Format("{0}|!{1}|!{2}*|*", new object[] { row, "NOOK", e.Message }); } } ds.Tables.Add(dt); rs = CJson.ResultSet2JSONString(ds); connection.Close(); connection.Dispose(); return(rs); } } catch (Exception ex) { return(ex.Message); } }