コード例 #1
0
ファイル: Helper.cs プロジェクト: kstubs/ProjFlx
            public static void getResultGrouped(results Results, XmlWriter XWriter, params string[] Grouped)
            {
                XWriter.WriteStartElement("results");
                XWriter.WriteAttributeString("name", Results.name);
                XWriter.WriteAttributeString("ProjectSqlFile", Results.ProjectSqlFile);
                XWriter.WriteStartElement("schema");
                XWriter.WriteRaw(Helper.StripXmlDecleration(Results.schema[0].Serialize()));
                XWriter.WriteEndElement();
                XWriter.WriteStartElement("result");

                var groupingValues = new Dictionary<string, string>();
                foreach(var s in Grouped)
                    groupingValues.Add(s.Trim(), null);
                int depth = 0;

                foreach (var row in Results.result.row)
                {
                    // check for differences - open element if null
                    for (int x = 0; x < groupingValues.Count; x++)
                    {

                        depth = x;
                        string key = groupingValues.Keys.ElementAt(x);
                        if (groupingValues[key] == null)
                        {
                            XWriter.WriteStartElement(key);
                            XWriter.WriteAttributeString("value", row.AnyAttr.LookupValue(key));
                            groupingValues[key] = row.AnyAttr.LookupValue(key);
                            continue;
                        }

                        if(!groupingValues[key].Equals(row.AnyAttr.LookupValue(key)))
                        {
                            // close elements
                            for(int j = x; j < groupingValues.Count; j++)
                                XWriter.WriteEndElement();

                            // open elements
                            for(int k = x; k < groupingValues.Count; k++)
                            {
                                key = groupingValues.Keys.ElementAt(k);
                                XWriter.WriteStartElement(key);
                                XWriter.WriteAttributeString("value", row.AnyAttr.LookupValue(key));
                                groupingValues[key] = row.AnyAttr.LookupValue(key);
                            }
                        }
                    }
                    getRowGrouped(row, XWriter);
                }
                // close last grouping items
                for(int x = 0; x < groupingValues.Count; x++)
                    XWriter.WriteEndElement();

                XWriter.WriteEndElement();
                XWriter.WriteEndElement();
            }
コード例 #2
0
ファイル: ProjectFlxSql.cs プロジェクト: kstubs/ProjFlx
 public static bool LoadFromFile(string fileName, out results obj)
 {
     System.Exception exception = null;
     return LoadFromFile(fileName, out obj, out exception);
 }
コード例 #3
0
ファイル: ProjectFlxSql.cs プロジェクト: kstubs/ProjFlx
 /// <summary>
 /// Deserializes xml markup from file into an results object
 /// </summary>
 /// <param name="fileName">string xml file to load and deserialize</param>
 /// <param name="obj">Output results object</param>
 /// <param name="exception">output Exception value if deserialize failed</param>
 /// <returns>true if this XmlSerializer can deserialize the object; otherwise, false</returns>
 public static bool LoadFromFile(string fileName, out results obj, out System.Exception exception)
 {
     exception = null;
     obj = default(results);
     try {
         obj = LoadFromFile(fileName);
         return true;
     }
     catch (System.Exception ex) {
         exception = ex;
         return false;
     }
 }
コード例 #4
0
ファイル: ProjectFlxSql.cs プロジェクト: kstubs/ProjFlx
 public static bool Deserialize(string xml, out results obj)
 {
     System.Exception exception = null;
     return Deserialize(xml, out obj, out exception);
 }
コード例 #5
0
ファイル: ProjectFlxSql.cs プロジェクト: kstubs/ProjFlx
 /// <summary>
 /// Deserializes workflow markup into an results object
 /// </summary>
 /// <param name="xml">string workflow markup to deserialize</param>
 /// <param name="obj">Output results object</param>
 /// <param name="exception">output Exception value if deserialize failed</param>
 /// <returns>true if this XmlSerializer can deserialize the object; otherwise, false</returns>
 public static bool Deserialize(string xml, out results obj, out System.Exception exception)
 {
     exception = null;
     obj = default(results);
     try {
         obj = Deserialize(xml);
         return true;
     }
     catch (System.Exception ex) {
         exception = ex;
         return false;
     }
 }
コード例 #6
0
        void _Query(ProjectFlx.Schema.SchemaQueryType query, bool IgnoreResults)
        {
            string timingToken = String.Format("ProjectFlxDB.DB.SchemaBase.DatabaseQuery {0}.{1}", query.project, query.name);

            if (Timing != null)
            {
                Timing.Start(timingToken);
            }

            try
            {
                if (query.paging == null)
                {
                    query.paging = new ProjectFlx.Schema.paging();
                }

                ProjectFlx.Schema.results rslts;
                _Projresults.results.Add(rslts = new ProjectFlx.Schema.results());
                rslts.schema.Add(query);
                rslts.name = query.name;

                if (_database.State != ConnectionState.Open)
                {
                    _database.InitializeConnection();
                    _database.Open();
                }

                InitializeCommand();

                // command timeout
                if (query.scripttimeoutSpecified)
                {
                    _command.CommandTimeout = query.scripttimeout;
                }

                switch (query.command.type)
                {
                case ProjectFlx.Schema.commandType.StoredProcedure:
                    _command.CommandText = ProjectFlx.Schema.Helper.FlattenList(query.command.name.Text);
                    _command.CommandType = System.Data.CommandType.StoredProcedure;
                    break;

                case ProjectFlx.Schema.commandType.Select:
                    _command.CommandText = ProjectFlx.Schema.Helper.FlattenList(query.command.text.Text);
                    _command.CommandType = System.Data.CommandType.Text;
                    break;
                }

                _command.Parameters.Clear();    // be sure to clear parms in case _comman reused
                foreach (ProjectFlx.Schema.parameter parm in query.parameters.parameter)
                {
                    // short circuit
                    if (query.command.type == Schema.commandType.Select)
                    {
                        string replace = String.Format("[{0}]", parm.name);
                        _command.CommandText = _command.CommandText.Replace(replace, ProjectFlx.Schema.Helper.FlattenList(parm.Text));
                        continue;
                    }

                    // guarantee that we setup variable for out param types
                    bool isoutparm = parm.inout == ProjectFlx.Schema.inoutType.inout || parm.inout == ProjectFlx.Schema.inoutType.@out;

                    // assume null parameter value if collection is length of 0
                    // see _fillParmsWeb for implementation details on
                    // passing null and empty strings
                    if (!parm.blankIsNull && parm.Text.Count == 0)
                    {
                        parm.Text.Add("");
                    }

                    if (parm.Text.Count == 0 && !isoutparm)
                    {
                        continue;
                    }

                    var value = ProjectFlx.Schema.Helper.FlattenList(parm.Text);

                    if (value != null || isoutparm)
                    {
                        SqlParameter inoutparm;

                        if (parm.type == ProjectFlx.Schema.fieldType.date)
                        {
                            string dtValue = null;
                            if (value.EndsWith(" GMT"))
                            {
                                dtValue = value.Substring(0, value.LastIndexOf(" GMT"));
                            }
                            if (value.EndsWith(" UTC"))
                            {
                                dtValue = value.Substring(0, value.LastIndexOf(" UTC"));
                            }
                            if (dtValue == null)
                            {
                                dtValue = value;
                            }

                            var dt = DateTime.Parse("1970-1-1 01:01:01");
                            DateTime.TryParse(dtValue, out dt);

                            if (dt.ToString("d").Equals("1/1/1970") && dt.ToString("t").Equals("1:1 AM"))
                            {
                                throw new Exception("Could not parse date: " + value);
                            }

                            inoutparm = _command.Parameters.AddWithValue(parm.name, dt);
                        }
                        else if (parm.type == Schema.fieldType.json)
                        {
                            inoutparm = _command.Parameters.AddWithValue(parm.name, value);
                        }
                        else
                        {
                            inoutparm = _command.Parameters.AddWithValue(parm.name, value);
                        }


                        switch (parm.inout)
                        {
                        case ProjectFlx.Schema.inoutType.inout:
                            inoutparm.Direction = ParameterDirection.InputOutput;
                            inoutparm.DbType    = getDBTypeForSchemaParm(parm);
                            break;

                        case ProjectFlx.Schema.inoutType.@out:
                            inoutparm.Direction = ParameterDirection.Output;
                            inoutparm.DbType    = getDBTypeForSchemaParm(parm);
                            break;
                        }

                        // enforce size for inout params (text only)
                        if (parm.type == ProjectFlx.Schema.fieldType.text && isoutparm)
                        {
                            if (parm.size > 0)
                            {
                                inoutparm.Size = parm.size;
                            }
                            else
                            {
                                throw new Exception(String.Format("Expecting parameter size for parameter {0} in query {1}", parm.name, query.name));
                            }
                        }

                        // validate json text type
                        if (parm.type == Schema.fieldType.json || parm.type == Schema.fieldType.tryjson)
                        {
                            try
                            {
                                var jsonObj = Newtonsoft.Json.Linq.JObject.Parse(Schema.Helper.FlattenList(parm.Text));
                            }
                            catch (Exception handled)
                            {
                                throw new Exception("Invalid Json for Parameter: " + parm.name, handled);
                            }
                        }
                    }
                }

                int result = 0;

                switch (query.command.action)
                {
                case ProjectFlx.Schema.actionType.NonQuery:
                    result = _command.ExecuteNonQuery();
                    if (IgnoreResults == true)
                    {
                        return;
                    }
                    // populate output parameter values
                    foreach (SqlParameter parm in _command.Parameters)
                    {
                        if (parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output)
                        {
                            ProjectFlx.Schema.parameter outboundParm = query.parameters.parameter.Find(delegate(ProjectFlx.Schema.parameter g) { return(g.name == parm.ParameterName); });
                            if (outboundParm != null)
                            {
                                outboundParm.Text = new List <string>();
                                outboundParm.Text.Add(Convert.ToString(parm.Value));
                            }
                        }
                    }

                    // set sorm sort of result
                    break;

                case ProjectFlx.Schema.actionType.Result:

                    var cachekey    = cacheKeyHelper(query);
                    var cacheresult = GetCache(cachekey);
                    if (cacheresult != null && CachingEnabled)
                    {
                        rslts.result = cacheresult;
                    }
                    else
                    {
                        using (SqlDataReader reader = _command.ExecuteReader())
                        {
                            if (IgnoreResults == true)
                            {
                                return;
                            }

                            readerToResult(reader, rslts.result, query.fields, query.paging);

                            if (_cache != null && _cachingEnabled)
                            {
                                var key = cacheKeyHelper(query);
                                SaveCache(key, rslts.result);
                            }


                            // include sub results (StoredProcedure returns more than one Result Set)
                            while (reader.NextResult())
                            {
                                if (_cache != null && _cachingEnabled)
                                {
                                    throw new Exception("Caching not supported for Suquery Queries");
                                }

                                if (query.subquery != null)
                                {
                                    readerToResult(reader, rslts.subresult, query.subquery.fields, query.paging);
                                }
                            }
                        }
                    }
                    break;

                case ProjectFlx.Schema.actionType.Scalar:
                    Object objresult = _command.ExecuteScalar();
                    int    scalar    = 0;
                    int.TryParse(objresult == null ? "0" : objresult.ToString(), out scalar);
                    _scalar = scalar;
                    if (IgnoreResults == true)
                    {
                        return;
                    }
                    var          r   = new ProjectFlx.Schema.result();
                    var          i   = new ProjectFlx.Schema.row();
                    XmlDocument  xm  = new XmlDocument();
                    XmlAttribute att = xm.CreateAttribute("Scalar");
                    att.Value = _scalar.ToString();
                    i.AnyAttr.Add(att);
                    r.row.Add(i);
                    _Projresults.results.Last().result = r;

                    // populate output parameter values
                    foreach (SqlParameter parm in _command.Parameters)
                    {
                        if (parm.Direction == ParameterDirection.InputOutput || parm.Direction == ParameterDirection.Output)
                        {
                            ProjectFlx.Schema.parameter outboundParm = query.parameters.parameter.Find(delegate(ProjectFlx.Schema.parameter g) { return(g.name == parm.ParameterName); });
                            if (outboundParm != null)
                            {
                                outboundParm.Text = new List <string>();
                                outboundParm.Text.Add(Convert.ToString(parm.Value));
                            }
                        }
                    }

                    break;
                }
            }
            finally
            {
                if (Timing != null)
                {
                    Timing.Stop(timingToken);
                }
            }
        }