public static void StoreData(SqlConnection conn, CachedDataTableIdentifier id)
        {
            lock (_padlock)
            {
                if (id.DataType == CachedDataType.None)
                {
                    throw new Exception("CachedDataType is None!");
                }

                if (_cache.ContainsKey(id))
                {
                    DataTable rm = _cache[id];
                    _cache.Remove(id);
                    if (rm != null)
                    {
                        rm.Clear();
                        rm.Dispose();
                    }
                }

                if (_updateHist.ContainsKey(id))
                {
                    _updateHist.Remove(id);
                }

                string script = String.Empty;
                switch (id.DataType)
                {
                case CachedDataType.Databases:
                    script = ResManager.GetDBScript("Script_GetDatabases");
                    break;

                case CachedDataType.Users:
                    script = ResManager.GetDBScript("Script_GetUsers");
                    break;

                case CachedDataType.Objects:
                    script = ResManager.GetDBScript("Script_CodeCompletionProposalWithoutProcParams");
                    break;

                default:
                    break;
                }

                SqlCommand     cmd     = null;
                SqlDataAdapter adapter = null;
                DataTable      tbl     = null;

                try
                {
                    cmd = new SqlCommand(script, conn);
                    cmd.CommandTimeout    = 0;
                    adapter               = new SqlDataAdapter();
                    adapter.SelectCommand = cmd;
                    tbl = new DataTable();
                    adapter.Fill(tbl);
                }
                finally
                {
                    if (cmd != null)
                    {
                        cmd.Dispose();
                    }
                    if (adapter != null)
                    {
                        adapter.Dispose();
                    }
                }

                CachedDataTableIdentifier key = new CachedDataTableIdentifier();
                key.CopyFrom(id);
                if (key.Timeout > 0)
                {
                    CacheDataTableTimeoutSpec tmSpec = new CacheDataTableTimeoutSpec();
                    tmSpec.Timeout     = key.Timeout;
                    tmSpec.RetrievedOn = DateTime.Now;
                    _updateHist.Add(key, tmSpec);
                }
                _cache.Add(key, tbl);
            }
        }
예제 #2
0
        public static IDictionary <string, SqlTable> GetTables(string sqlText, TableType tableType)
        {
            Dictionary <string, SqlTable> result = new Dictionary <string, SqlTable>();
            string expression = String.Empty;

            switch (tableType)
            {
            case TableType.Temp:
                expression = ResManager.GetRegularExpression("SqlAnalyze_TempTable");
                break;

            case TableType.Variable:
                expression = ResManager.GetRegularExpression("SqlAnalyze_LocalTableVariable");
                break;

            default:
                return(result);
            }

            expression = ReplaceDataTypesWithQualifiedOnes(expression);

            Regex r = new Regex(expression, RegexOptions.IgnoreCase | RegexOptions.Compiled);
            Match m;

            SqlTable tbl = null;
            Group    g   = null;

            for (m = r.Match(sqlText); m.Success; m = m.NextMatch())
            {
                g = m.Groups["TableName"];
                if (g == null)
                {
                    continue;
                }
                string tName = CleanIdentifier(g.Value);
                if (result.ContainsKey(tName))
                {
                    continue;
                }

                tbl           = new SqlTable(tableType);
                tbl.TableName = tName;
                result.Add(tbl.TableName, tbl);


                g = m.Groups["ColNames"];
                string cName = String.Empty;
                if (g != null)
                {
                    foreach (Capture c in g.Captures)
                    {
                        cName = CleanIdentifier(c.Value);
                        tbl.ColumnNames.Add(cName);
                    }
                }

                string dName = String.Empty;
                g = m.Groups["DataTypes"];
                if (g != null)
                {
                    foreach (Capture c in g.Captures)
                    {
                        dName = CleanIdentifier(c.Value);
                        tbl.ColumnDataTypes.Add(dName);
                    }
                }

                for (int i = 0; i < tbl.ColumnNames.Count; i++)
                {
                    string colName  = tbl.ColumnNames[i];
                    string dataType = String.Empty;
                    if (i < tbl.ColumnDataTypes.Count)
                    {
                        dataType = tbl.ColumnDataTypes[i];
                    }

                    tbl.FullyQualifiedColumns.Add(colName + (String.IsNullOrEmpty(dataType) ? String.Empty : " " + dataType));
                }
            }
            return(result);
        }
예제 #3
0
        public IList <ObjectGroupingItemData> GetChildren(int?parentID)
        {
            if (ConnParams == null)
            {
                throw new NullPropertyException("Connection parameters is null!");
            }

            IList <ObjectGroupingItemData> result = new List <ObjectGroupingItemData>();

            using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
            {
                string     cmdText = ResManager.GetDBScript("spPragmaSQL_ObjectGroup_List");
                SqlCommand cmd     = new SqlCommand(cmdText, conn);
                cmd.CommandTimeout = 0;
                AddParam(cmd, "@ParentID", System.Data.SqlDbType.Int, parentID);

                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                string name           = String.Empty;
                int?   type           = null;
                int?   id             = null;
                string parentObjName  = String.Empty;
                string createdBy      = String.Empty;
                string updatedBy      = String.Empty;
                string helpText       = String.Empty;
                string helpTextFormat = String.Empty;
                bool   dbObjectExists = true;

                ObjectGroupingItemData data = null;
                try
                {
                    while (reader.Read())
                    {
                        dbObjectExists = true;
                        name           = reader["Name"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["Name"];
                        type           = reader["ObjType"].GetType() == typeof(DBNull) ? null : (int?)reader["ObjType"];
                        id             = reader["ObjectID"].GetType() == typeof(DBNull) ? null : (int?)reader["ObjectID"];
                        parentObjName  = reader["ParentObjName"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["ParentObjName"];
                        createdBy      = reader["CreatedBy"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["CreatedBy"];
                        updatedBy      = reader["UpdatedBy"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["UpdatedBy"];

                        helpText       = reader["Description"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["Description"];
                        helpTextFormat = reader["DescriptionFormat"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["DescriptionFormat"];

                        try
                        {
                            dbObjectExists = reader["DBObjectExists"].GetType() == typeof(DBNull) ? false : (bool)reader["DBObjectExists"];
                        }
                        catch { }

                        data                 = ObjectGroupingItemDataFactory.Create(name, type, id, parentObjName, parentID, createdBy);
                        data.HelpText        = helpText;
                        data.HelpTextFormat  = helpTextFormat;
                        data.UpdatedBy       = updatedBy;
                        data.DbObjectMissing = !dbObjectExists && (type != DBObjectType.GroupingFolderY && type != DBObjectType.GroupingFolderY);
                        result.Add(data);
                    }
                }
                finally
                {
                    reader.Close();
                }
            }

            return(result);
        }
예제 #4
0
        public static IDictionary <string, SqlTableAlias> GetTableAliases(string sqlText, TableAliasType aliasType)
        {
            IDictionary <string, SqlTableAlias> result = new Dictionary <string, SqlTableAlias>();
            string expression = String.Empty;

            switch (aliasType)
            {
            case TableAliasType.From:
                expression = ResManager.GetRegularExpression("SqlAnalyze_TableAliasFrom");
                break;

            case TableAliasType.Join:
                expression = ResManager.GetRegularExpression("SqlAnalyze_TableAliasJoin");
                break;

            default:
                return(result);
            }

            Regex r = new Regex(expression, RegexOptions.IgnoreCase | RegexOptions.Compiled);
            Match m;

            SqlTableAlias ta = null;
            Group         g  = null;

            for (m = r.Match(sqlText); m.Success; m = m.NextMatch())
            {
                g = m.Groups["TableName"];
                if (g == null)
                {
                    continue;
                }

                string tName = CleanIdentifier(g.Value);
                if (tName.Contains("(") || tName.Contains(")"))
                {
                    continue;
                }

                tName = MatchTableName(tName);
                if (result.ContainsKey(tName))
                {
                    continue;
                }

                ta           = new SqlTableAlias(aliasType);
                ta.TableName = tName;
                result.Add(ta.TableName, ta);

                g = m.Groups["TableAlias"];

                if (g != null)
                {
                    if (CleanIdentifier(g.Value).Trim() == "on")
                    {
                        ta.TableAlias = ta.TableName;
                    }
                    else
                    {
                        ta.TableAlias = CleanIdentifier(g.Value);
                    }
                }
            }
            return(result);
        }