public void UpdateHelpText(ObjectGroupingItemData data)
{
if (ConnParams == null)
{
throw new NullPropertyException("Connection parameters is null!");
}
if (data == null)
{
return;
}
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("spPragmaSQL_ObjectGroup_UpdateDescription");
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
AddParam(cmd, "@ObjectID", SqlDbType.Int, data.ID);
AddParam(cmd, "@Description", SqlDbType.Text, data.HelpText);
AddParam(cmd, "@DescriptionFormat", SqlDbType.VarChar, data.HelpTextFormat);
AddParam(cmd, "@UpdatedBy", SqlDbType.VarChar, _connParams.CurrentUsername);
conn.Open();
cmd.ExecuteNonQuery();
data.UpdatedBy = _connParams.CurrentUsername;
}
}
public bool IsObjectGroupingSupportInstalled()
{
int result = 0;
if (ConnParams == null)
{
throw new NullPropertyException("Connection parameters is null!");
}
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("Script_ObjectGroupingSupportCheck");
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
result = reader["Result"].GetType() == typeof(DBNull) ? 0 : (int)reader["Result"];
}
}
finally
{
reader.Close();
}
}
return(result == 1 ? true : false);
}
public void AddItem(ObjectGroupingItemData data, string desc, string descFormat)
{
if (ConnParams == null)
{
throw new NullPropertyException("Connection parameters is null!");
}
if (data == null)
{
throw new NullParameterException("Item data is null!");
}
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("spPragmaSQL_ObjectGroup_Insert");
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
AddParam(cmd, "@ParentID", SqlDbType.Int, data.ParentID);
AddParam(cmd, "@Name", SqlDbType.VarChar, data.Name);
AddParam(cmd, "@ParentObjName", SqlDbType.VarChar, data.ParentObjectName);
AddParam(cmd, "@Description", SqlDbType.Text, desc);
AddParam(cmd, "@DescriptionFormat", SqlDbType.VarChar, descFormat);
AddParam(cmd, "@ObjType", SqlDbType.Int, data.Type);
AddParam(cmd, "@CreatedBy", SqlDbType.VarChar, _connParams.CurrentUsername);
SqlParameter param = cmd.Parameters.Add("@ObjectID", System.Data.SqlDbType.Int);
param.Direction = System.Data.ParameterDirection.Output;
conn.Open();
cmd.ExecuteNonQuery();
data.ID = (int?)param.Value;
}
}
public List <string> GetAllParents(int id)
{
object parents = null;
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("Script_ObjectGroupingSupport_GetAllParents");
cmdText = String.Format(cmdText, id);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
parents = reader[0];
}
}
finally
{
reader.Close();
}
}
if (parents == null || parents.GetType() == typeof(DBNull))
{
return(null);
}
return(new List <string>(parents.ToString().Split(new char[1] {
','
}, StringSplitOptions.RemoveEmptyEntries)));
}
/// <summary>
/// Gets create script of a stored proc, function, view or triggert
/// </summary>
/// <param name="objectId"></param>
/// <returns></returns>
public static string GetObjectCreateScript(SqlConnection conn, long objectId)
{
StringBuilder sb = new StringBuilder();
if (conn == null)
{
throw new Exception("Database connection object null.");
}
if (conn.State != ConnectionState.Open)
{
throw new InvalidConnectionState(String.Format("Invalid database connection state. {0}", conn.State));
}
SqlCommand cmd = new SqlCommand(ResManager.GetDBScript("Script_GetObjectCreateScript"), conn);
cmd.CommandTimeout = 0;
SqlParameter param = cmd.Parameters.Add("@id", SqlDbType.Int);
param.Value = objectId;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sb.Append((string)reader["text"]);
}
reader.Close();
string result = sb.ToString();
result = result.TrimEnd('\r', '\n');
return(result);
}
public static string GetRoutineParams(SqlConnection sqlConnection, string routineName)
{
StringBuilder sb = new StringBuilder();
SqlConnection conn = SqlConn;
if (sqlConnection != null)
{
conn = sqlConnection;
}
string cmdText = ResManager.GetDBScript("Script_Schema_RoutineParams");
cmdText = String.Format(cmdText, routineName);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
string paramName = String.Empty;
string paramMode = String.Empty;
try
{
int rowCnt = 0;
while (reader.Read())
{
paramName = (string)reader["PARAMETER_NAME"];
if (String.IsNullOrEmpty(paramName))
{
continue;
}
rowCnt++;
paramMode = (reader["PARAMETER_MODE"].GetType() != typeof(DBNull)) ? (string)reader["PARAMETER_MODE"] : "IN";
paramMode = (paramMode.ToLowerInvariant().Contains("out")) ? " OUTPUT" : String.Empty;
if (rowCnt == 1)
{
sb.Append(paramName + paramMode);
}
else
{
sb.Append(", " + paramName + paramMode);
}
}
}
finally
{
reader.Close();
}
return(sb.ToString());
}
public void InstallObjectGroupingSupport()
{
if (ConnParams == null)
{
throw new NullPropertyException("Connection parameters is null!");
}
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("Script_ObjectGroupingSupport");
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
conn.Open();
cmd.ExecuteNonQuery();
}
}
public void LoadDataTypes(ConnectionParams cp, bool wantEmpty)
{
ConnectionParams tmpCp = cp;
if (tmpCp == null)
{
tmpCp = _cp;
}
if (_tbl != null)
{
_tbl.PrimaryKey = null;
_tbl = null;
}
cmbType.Items.Clear();
ClearAll();
string cmdText = ResManager.GetDBScript("Script_GetDataTypes");
using (SqlConnection conn = tmpCp.CreateSqlConnection(true, false))
{
_tbl = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmdText, conn);
adapter.Fill(_tbl);
}
DataColumn[] PrimaryKeyColumns = new DataColumn[1];
PrimaryKeyColumns[0] = _tbl.Columns["name"];
_tbl.PrimaryKey = PrimaryKeyColumns;
if (wantEmpty)
{
cmbType.Items.Add(String.Empty);
}
foreach (DataRow row in _tbl.Rows)
{
cmbType.Items.Add(((string)row["name"]).ToLowerInvariant());
}
if (cmbType.Items.Count > 0)
{
cmbType.SelectedIndex = 0;
}
EvaluateDataType();
}
public void DeleteItem(int?id)
{
if (ConnParams == null)
{
throw new NullPropertyException("Connection parameters is null!");
}
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("spPragmaSQL_ObjectGroup_Delete");
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
AddParam(cmd, "@ObjectID", SqlDbType.Int, id);
conn.Open();
cmd.ExecuteNonQuery();
}
}
public static string GetColumns(SqlConnection sqlConnection, string objName, bool isParamList)
{
StringBuilder sb = new StringBuilder();
SqlConnection conn = SqlConn;
if (sqlConnection != null)
{
conn = sqlConnection;
}
string cmdText = ResManager.GetDBScript("Script_Schema_Columns");
cmdText = String.Format(cmdText, objName);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
string colName = String.Empty;
try
{
int rowCnt = 0;
while (reader.Read())
{
rowCnt++;
colName = (string)reader["COLUMN_NAME"];
if (rowCnt == 1)
{
sb.AppendLine("\t" + (isParamList ? "@" : String.Empty) + colName);
}
else
{
sb.AppendLine("\t, " + (isParamList ? "@" : String.Empty) + colName);
}
}
}
finally
{
reader.Close();
}
return(sb.ToString());
}
public DataTable GetObjectGroupingStats(string objectName)
{
DataTable result = new DataTable();
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("Script_ObjectGroupingSupportGroupStats");
cmdText = String.Format(cmdText, objectName);
SqlCommand cmd = new SqlCommand(cmdText, conn);
SqlDataAdapter da = new SqlDataAdapter();
da.SelectCommand = cmd;
da.Fill(result);
}
return(result);
}
private void ListObjects(string objType, string filter)
{
string commandText = String.Empty;
if (objType == "All")
{
string inCr = PrepareInCriteria(); //ResManager.GetDBScript("Script_ObjectList_InCriteria");
commandText = String.Format(ResManager.GetDBScript("Script_ObjectListAll"), inCr, filter);
}
else
{
commandText = String.Format(ResManager.GetDBScript("Script_ObjectList"), objType, filter);
}
grdSource.DataSource = null;
if (_dtSource != null)
{
_dtSource.Dispose();
_dtSource = null;
}
using (SqlConnection conn = _cp.CreateSqlConnection(true, false))
{
SqlCommand cmd = new SqlCommand(commandText);
cmd.Connection = conn;
SqlDataAdapter ad = new SqlDataAdapter();
try
{
ad.SelectCommand = cmd;
_dtSource = new DataTable();
ad.Fill(_dtSource);
bsSource.DataSource = _dtSource;
grdSource.DataSource = bsSource;
}
finally
{
ad.Dispose();
ad = null;
}
}
}
public HelpTextDefinition GetHelpText(int?id)
{
if (ConnParams == null)
{
throw new NullPropertyException("Connection parameters is null!");
}
HelpTextDefinition result = new HelpTextDefinition();
using (SqlConnection conn = new SqlConnection(_connParams.ConnectionString))
{
string cmdText = ResManager.GetDBScript("spPragmaSQL_ObjectGroup_GetHelpText");
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
AddParam(cmd, "@ObjectID", SqlDbType.Int, id);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
string desc = String.Empty;
string descFormat = String.Empty;
string updatedBy = String.Empty;
try
{
while (reader.Read())
{
desc = reader["Description"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["Description"];
descFormat = reader["DescriptionFormat"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["DescriptionFormat"];
updatedBy = reader["UpdatedBy"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["UpdatedBy"];
result.HelpText = desc;
result.Format = descFormat;
result.UpdatedBy = updatedBy;
break;
}
}
finally
{
reader.Close();
}
}
return(result);
}
public static ObjectInfo GetObjectInfo(string connectionString, string database, string objName)
{
if (String.IsNullOrEmpty(connectionString))
{
throw new NullParameterException("Connection string is null or empty!");
}
ObjectInfo result = null;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
if (!String.IsNullOrEmpty(database))
{
conn.ChangeDatabase(database);
}
string script = ResManager.GetDBScript("Script_GetObjectInfoByName");
script = String.Format(script, objName);
SqlCommand cmd = new SqlCommand(script, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
result = new ObjectInfo();
result.ObjectID = (int)reader["id"];
result.ObjectName = (string)reader["name"];
result.ObjectTypeAbb = (string)reader["xtype"];
result.ObjectType = DBConstants.GetDBObjectType((reader["xtype"] as string));
result.ObjectOwner = (string)reader["owner"];
}
}
finally
{
reader.Close();
}
}
return(result);
}
public static string GetColumnsAsParamListWithDataType(SqlConnection sqlConnection, string objName, bool eachInNewLine)
{
StringBuilder sb = new StringBuilder();
SqlConnection conn = SqlConn;
if (sqlConnection != null)
{
conn = sqlConnection;
}
string cmdText = ResManager.GetDBScript("Script_Schema_Columns");
cmdText = String.Format(cmdText, objName);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
string colName = String.Empty;
string dataType = String.Empty;
int len = 0;
byte prec = 0;
int scale = 0;
string fullParamName = String.Empty;
try
{
int rowCnt = 0;
while (reader.Read())
{
rowCnt++;
colName = (string)reader["COLUMN_NAME"];
len = (reader["CHARACTER_MAXIMUM_LENGTH"].GetType() != typeof(DBNull)) ? (int)reader["CHARACTER_MAXIMUM_LENGTH"] : 0;
prec = (reader["NUMERIC_PRECISION"].GetType() != typeof(DBNull)) ? (byte)reader["NUMERIC_PRECISION"] : (byte)0;
scale = (reader["NUMERIC_SCALE"].GetType() != typeof(DBNull)) ? (int)reader["NUMERIC_SCALE"] : 0;
dataType = (string)reader["DATA_TYPE"];
dataType = DBConstants.GetFullyQualifiedDataTypeName(true, dataType, len, scale, prec);
if (rowCnt == 1)
{
fullParamName = "@" + colName + " " + dataType;
}
else
{
fullParamName = ", @" + colName + " " + dataType;
}
if (eachInNewLine)
{
sb.AppendLine(fullParamName);
}
else
{
sb.Append(fullParamName);
}
}
}
finally
{
reader.Close();
}
return(sb.ToString());
}
public static IList <TableColumnSpec> GetTableColumnsSpecification(ConnectionParams cp, string tableName)
{
IList <TableColumnSpec> result = new List <TableColumnSpec>();
IList <string> primaryKeys = new List <string>();
TableColumnSpec col = null;
TableColumnSpec identityCol = null;
using (SqlConnection conn = new SqlConnection(cp.ConnectionString))
{
conn.Open();
// 1- Read primary keys
string script = "exec sp_pkeys '{0}'";
script = String.Format(script, tableName);
SqlCommand cmd = new SqlCommand(script, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
primaryKeys.Add(((string)reader["COLUMN_NAME"]).ToLowerInvariant());
}
}
finally
{
reader.Close();
}
// 2- Read columns
script = ResManager.GetDBScript("Script_GetTableColumns");
script = String.Format(script, tableName);
cmd.CommandText = script;
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
col = new TableColumnSpec();
col.TableName = tableName;
col.TableId = (int)reader["id"];
col.Name = (string)reader["name"];
col.Collation = reader["collation"].GetType() == typeof(DBNull) ? String.Empty : (string)reader["collation"];
col.IsComputed = (int)reader["iscomputed"] == 0 ? false : true;
col.IsNullable = (int)reader["isnullable"] == 0 ? false : true;
col.IsIdentity = (int)reader["isIdentity"] == 0 ? false : true;
col.DataType = reader["typename"].GetType() == typeof(DBNull) ? "[???]" : (string)reader["typename"];
col.Length = reader["lengthx"].GetType() == typeof(DBNull) ? (int)0 : (int)reader["lengthx"];
col.Precision = reader["prec"].GetType() == typeof(DBNull) ? (short)0 : (short)reader["prec"];
col.Scale = reader["scale"].GetType() == typeof(DBNull) ? 0 : (int)reader["scale"];
col.FullyQualifiedDataType = DBConstants.GetFullyQualifiedDataTypeName(true, col.DataType, col.Length, col.Scale, col.Precision);
col.IsInPrimaryKey = primaryKeys.Contains(col.Name.ToLowerInvariant());
result.Add(col);
if (col.IsIdentity && identityCol == null)
{
identityCol = col;
}
}
}
finally
{
reader.Close();
}
// 3- Read identity seed and increment
if (identityCol != null)
{
script = ResManager.GetDBScript("Script_GetTableIdentityIncrementAndSeed");
script = String.Format(script, tableName);
cmd.CommandText = script;
reader = cmd.ExecuteReader();
try
{
while (reader.Read())
{
identityCol.IdentitySeed = reader["idSeed"].GetType() == typeof(DBNull) ? 0 : (decimal)reader["idSeed"];
identityCol.IdentityIncrement = reader["idIncrement"].GetType() == typeof(DBNull) ? 0 : (decimal)reader["idIncrement"];
break;
}
}
finally
{
reader.Close();
}
}
}
return(result);
}
public static string GetTableValuedFunctionResultColumns(SqlConnection sqlConnection, string functionName, bool localTableVar, string localTableVarName)
{
StringBuilder sb = new StringBuilder();
SqlConnection conn = SqlConn;
if (sqlConnection != null)
{
conn = sqlConnection;
}
string cmdText = ResManager.GetDBScript("Script_Schema_RoutineColumns");
cmdText = String.Format(cmdText, functionName);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
string colName = String.Empty;
string dataType = String.Empty;
int len = 0;
byte prec = 0;
int scale = 0;
string nullableMode = String.Empty;
string collationName = String.Empty;
try
{
int rowCnt = 0;
while (reader.Read())
{
rowCnt++;
colName = "[" + (string)reader["COLUMN_NAME"] + "]";
len = (reader["CHARACTER_MAXIMUM_LENGTH"].GetType() != typeof(DBNull)) ? (int)reader["CHARACTER_MAXIMUM_LENGTH"] : 0;
prec = (reader["NUMERIC_PRECISION"].GetType() != typeof(DBNull)) ? (byte)reader["NUMERIC_PRECISION"] : (byte)0;
scale = (reader["NUMERIC_SCALE"].GetType() != typeof(DBNull)) ? (int)reader["NUMERIC_SCALE"] : 0;
dataType = (string)reader["DATA_TYPE"];
nullableMode = (reader["IS_NULLABLE"].GetType() != typeof(DBNull)) ? (string)reader["IS_NULLABLE"] : "NO";
nullableMode = (nullableMode.ToLowerInvariant() == "YES") ? " NULL" : " NOT NULL";
collationName = (reader["COLLATION_NAME"].GetType() != typeof(DBNull)) ? (string)reader["COLLATION_NAME"] : String.Empty;
collationName = (!String.IsNullOrEmpty(collationName)) ? " COLLATE " + collationName : String.Empty;
dataType = DBConstants.GetFullyQualifiedDataTypeName(true, dataType, len, scale, prec);
if (rowCnt == 1)
{
sb.AppendLine("\t" + colName + " " + dataType + collationName + nullableMode);
}
else
{
sb.AppendLine("\t, " + colName + " " + dataType + collationName + nullableMode);
}
}
}
finally
{
reader.Close();
}
string result = sb.ToString();
if (localTableVar)
{
result = "DECLARE @" + (String.IsNullOrEmpty(localTableVarName) ? "tbl" : localTableVarName) + " TABLE"
+ "\n(\n"
+ result
+ "\n)\n";
}
return(result);;
}
public static string GetProcedureExecuteScript(SqlConnection sqlConnection, string spName)
{
StringBuilder sb = new StringBuilder();
StringBuilder sbDeclare = new StringBuilder();
StringBuilder sbExecute = new StringBuilder();
SqlConnection conn = SqlConn;
if (sqlConnection != null)
{
conn = sqlConnection;
}
string cmdText = ResManager.GetDBScript("Script_Schema_RoutineParams");
cmdText = String.Format(cmdText, spName);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
string paramName = String.Empty;
string dataType = String.Empty;
int len = 0;
byte prec = 0;
int scale = 0;
string paramMode = String.Empty;
string schemaSpec = String.Empty;
try
{
sbDeclare.AppendLine("DECLARE @RC int");
sbExecute.Append("EXECUTE @RC = ");
int rowCnt = 0;
while (reader.Read())
{
rowCnt++;
paramName = (string)reader["PARAMETER_NAME"];
len = (reader["CHARACTER_MAXIMUM_LENGTH"].GetType() != typeof(DBNull)) ? (int)reader["CHARACTER_MAXIMUM_LENGTH"] : 0;
prec = (reader["NUMERIC_PRECISION"].GetType() != typeof(DBNull)) ? (byte)reader["NUMERIC_PRECISION"] : (byte)0;
scale = (reader["NUMERIC_SCALE"].GetType() != typeof(DBNull)) ? (int)reader["NUMERIC_SCALE"] : 0;
dataType = (string)reader["DATA_TYPE"];
paramMode = (reader["PARAMETER_MODE"].GetType() != typeof(DBNull)) ? (string)reader["PARAMETER_MODE"] : "IN";
paramMode = (paramMode.ToLowerInvariant().Contains("out")) ? " OUTPUT" : String.Empty;
dataType = DBConstants.GetFullyQualifiedDataTypeName(true, dataType, len, scale, prec);
if (String.IsNullOrEmpty(schemaSpec))
{
schemaSpec = "[" + (string)reader["SPECIFIC_CATALOG"] + "].[" + (string)reader["SPECIFIC_SCHEMA"] + "].[" + spName + "] ";
sbExecute.Append(schemaSpec);
}
sbDeclare.AppendLine("DECLARE " + paramName + " " + dataType);
if (rowCnt == 1)
{
sbExecute.Append(paramName + paramMode);
}
else
{
sbExecute.Append(", " + paramName + paramMode);
}
}
}
finally
{
reader.Close();
}
if (String.IsNullOrEmpty(schemaSpec))
{
schemaSpec = "[dbo].[" + sqlConnection.Database + "].[" + spName + "] ";
sbExecute.Append(schemaSpec);
}
sb.Append(sbDeclare.ToString());
sb.AppendLine();
sb.Append(sbExecute.ToString());
return(sb.ToString());
}
public static string GetRoutineParamsWithDataType(SqlConnection sqlConnection, string routineName)
{
StringBuilder sb = new StringBuilder();
SqlConnection conn = SqlConn;
if (sqlConnection != null)
{
conn = sqlConnection;
}
string cmdText = ResManager.GetDBScript("Script_Schema_RoutineParams");
cmdText = String.Format(cmdText, routineName);
SqlCommand cmd = new SqlCommand(cmdText, conn);
cmd.CommandTimeout = 0;
SqlDataReader reader = cmd.ExecuteReader();
string paramName = String.Empty;
string dataType = String.Empty;
int len = 0;
byte prec = 0;
int scale = 0;
string paramMode = String.Empty;
try
{
int rowCnt = 0;
while (reader.Read())
{
paramName = (string)reader["PARAMETER_NAME"];
if (String.IsNullOrEmpty(paramName))
{
continue;
}
rowCnt++;
len = (reader["CHARACTER_MAXIMUM_LENGTH"].GetType() != typeof(DBNull)) ? (int)reader["CHARACTER_MAXIMUM_LENGTH"] : 0;
prec = (reader["NUMERIC_PRECISION"].GetType() != typeof(DBNull)) ? (byte)reader["NUMERIC_PRECISION"] : (byte)0;
scale = (reader["NUMERIC_SCALE"].GetType() != typeof(DBNull)) ? (int)reader["NUMERIC_SCALE"] : 0;
dataType = (string)reader["DATA_TYPE"];
paramMode = (reader["PARAMETER_MODE"].GetType() != typeof(DBNull)) ? (string)reader["PARAMETER_MODE"] : "IN";
paramMode = (paramMode.ToLowerInvariant().Contains("out")) ? " OUTPUT" : String.Empty;
dataType = DBConstants.GetFullyQualifiedDataTypeName(true, dataType, len, scale, prec);
if (rowCnt == 1)
{
sb.Append(paramName + " " + dataType + paramMode);
}
else
{
sb.Append(", " + paramName + " " + dataType + paramMode);
}
}
}
finally
{
reader.Close();
}
return(sb.ToString());
}
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 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);
}
}