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