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