コード例 #1
0
    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);
        }
    }