private Model.StoredProcedure GetNewStoredProcedure(SQLDMO.StoredProcedure dmoStoredProcedure)
{
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(dmoStoredProcedure.Name, Script.GetSingluar(dmoStoredProcedure.Name), false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//SQLDMO.Column[] dmoColumns = GetColumns(dmoStoredProcedure);
//foreach (SQLDMO.Column dmoColumn in dmoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + dmoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
* {
* continue;
* }
*
* Column column = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), dmoColumn.Name, storedProcedure, ordinalPosition, dmoColumn.AllowNulls, dmoColumn.PhysicalDatatype, dmoColumn.Length,
* dmoColumn.InPrimaryKey, false, false, dmoColumn.Identity);
* storedProcedure.Columns.Add(column);
* ordinalPosition++;*/
//}
// Parameters
SQLDMO.QueryResults queryResults = dmoStoredProcedure.EnumParameters();
for (int i = 1; i <= queryResults.Rows; i++)
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(queryResults.GetColumnString(i, 1), queryResults.GetColumnString(i, 1).Replace("@", ""), queryResults.GetColumnString(i, 2));
//storedProcedure.AddParameter(parameter);
}
return(storedProcedure);
}
private Model.StoredProcedure GetNewStoredProcedure(Microsoft.SqlServer.Management.Smo.StoredProcedure smoStoredProcedure)
{
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(smoStoredProcedure.Name, Script.GetSingluar(smoStoredProcedure.Name), false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//Microsoft.SqlServer.Management.Smo.Column[] smoColumns = GetColumns(smoStoredProcedure);
//foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
* {
* continue;
* }
*
* Column column = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), smoColumn.Name, storedProcedure, ordinalPosition, smoColumn.Nullable, smoColumn.PhysicalDatatype, smoColumn.Length,
* smoColumn.InPrimaryKey, false, false, smoColumn.Identity);
* storedProcedure.Columns.Add(column);
* ordinalPosition++;*/
//}
// Parameters
foreach (Microsoft.SqlServer.Management.Smo.Parameter smoParameter in smoStoredProcedure.Parameters)
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(smoParameter.Name, smoParameter.Name.Replace("@", ""), smoParameter.DataType.Name);
//storedProcedure.AddParameter(parameter);
}
return(storedProcedure);
}
private Model.StoredProcedure GetNewStoredProcedure(SQLDMO.StoredProcedure dmoStoredProcedure)
{
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(dmoStoredProcedure.Name, Script.GetSingluar(dmoStoredProcedure.Name), false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//SQLDMO.Column[] dmoColumns = GetColumns(dmoStoredProcedure);
//foreach (SQLDMO.Column dmoColumn in dmoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + dmoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
{
continue;
}
Column column = new Column(dmoColumn.Name, Script.GetSingluar(dmoColumn.Name), dmoColumn.Name, storedProcedure, ordinalPosition, dmoColumn.AllowNulls, dmoColumn.PhysicalDatatype, dmoColumn.Length,
dmoColumn.InPrimaryKey, false, false, dmoColumn.Identity);
storedProcedure.Columns.Add(column);
ordinalPosition++;*/
//}
// Parameters
SQLDMO.QueryResults queryResults = dmoStoredProcedure.EnumParameters();
for (int i = 1; i <= queryResults.Rows; i++)
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(queryResults.GetColumnString(i, 1), queryResults.GetColumnString(i, 1).Replace("@", ""), queryResults.GetColumnString(i, 2));
//storedProcedure.AddParameter(parameter);
}
return storedProcedure;
}
private Model.StoredProcedure GetNewStoredProcedure(Microsoft.SqlServer.Management.Smo.StoredProcedure smoStoredProcedure)
{
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(smoStoredProcedure.Name, Script.GetSingluar(smoStoredProcedure.Name), false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//Microsoft.SqlServer.Management.Smo.Column[] smoColumns = GetColumns(smoStoredProcedure);
//foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
{
continue;
}
Column column = new Column(smoColumn.Name, Script.GetSingluar(smoColumn.Name), smoColumn.Name, storedProcedure, ordinalPosition, smoColumn.Nullable, smoColumn.PhysicalDatatype, smoColumn.Length,
smoColumn.InPrimaryKey, false, false, smoColumn.Identity);
storedProcedure.Columns.Add(column);
ordinalPosition++;*/
//}
// Parameters
foreach (Microsoft.SqlServer.Management.Smo.Parameter smoParameter in smoStoredProcedure.Parameters)
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(smoParameter.Name, smoParameter.Name.Replace("@", ""), smoParameter.DataType.Name);
//storedProcedure.AddParameter(parameter);
}
return storedProcedure;
}
//private void ProcessProsWithTempReferences(List<StoredProcDef> storedProcs)
//{
// bool added = true;
// while (added)
// {
// added = false;
// for (int i = 0; i < storedProcs.Count; i++)
// {
// string name = storedProcs[i].Name;
// name = name.Substring(0, name.IndexOf("|"));
// if (StoredProcsWithTempReferences.BinarySearch(name) >= 0)
// {
// continue;
// }
// for (int procCounter = 0; procCounter < StoredProcsWithTempReferences.Count; procCounter++)
// {
// string badProc = StoredProcsWithTempReferences[procCounter];
// if (storedProcs[i].Body.IndexOf(badProc) > 0)
// {
// StoredProcsWithTempReferences.Add(name);
// StoredProcsWithTempReferences.Sort();
// added = true;
// break;
// }
// }
// }
// }
//}
private Model.StoredProcedure GetNewStoredProcedure(string storedProcedureName, string schema)
{
if (string.IsNullOrEmpty(schema))
{
throw new ArgumentException("Schema must be specified to get stored proc.");
}
Interfaces.Events.RaiseObjectBeingProcessedEvent(storedProcedureName, "SP");
//_columns = null;
//_parameters = null;
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(storedProcedureName, false);
storedProcedure.Enabled = false;
storedProcedure.Schema = schema;
DataRow[] parameterRows = Parameters.Select(string.Format("ProcedureName = '{0}'", storedProcedureName));
foreach (DataRow parameterRow in parameterRows)
{
Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(
parameterRow["PARAMETER_NAME"].ToString().Replace("@", ""),
parameterRow["DATA_TYPE"].ToString(),
parameterRow["PARAMETER_MODE"].ToString(),
int.Parse(parameterRow["ORDINAL_POSITION"].ToString()),
parameterRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : int.Parse(parameterRow["CHARACTER_MAXIMUM_LENGTH"].ToString()),
parameterRow.IsNull("NUMERIC_PRECISION") ? 0 : int.Parse(parameterRow["NUMERIC_PRECISION"].ToString()),
parameterRow.IsNull("NUMERIC_SCALE") ? 0 : int.Parse(parameterRow["NUMERIC_SCALE"].ToString()));
storedProcedure.AddParameter(parameter);
}
FillStoredProcedureColumns(storedProcedure);
return(storedProcedure);
}
public Model.StoredProcedure[] GetStoredProcedures()
{
List <Model.StoredProcedure> storedProcedures = new List <Model.StoredProcedure>();
foreach (SQLDMO.StoredProcedure dmoStoredProcedure in Database.StoredProcedures)
{
Model.StoredProcedure storedProcedure = GetNewStoredProcedure(dmoStoredProcedure);
storedProcedures.Add(storedProcedure);
}
return((Model.StoredProcedure[])storedProcedures.ToArray());
}
private Model.StoredProcedure GetNewStoredProcedure(string storedProcName)
{
ArchAngel.Interfaces.Events.RaiseObjectBeingProcessedEvent(storedProcName, "SP");
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(storedProcName, false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//Microsoft.SqlServer.Management.Smo.Column[] smoColumns = GetColumns(smoStoredProcedure);
//foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
* {
* continue;
* }
*
* Column column = new Column(smoColumn.Name, Script.GetSingular(smoColumn.Name), smoColumn.Name, storedProcedure, ordinalPosition, smoColumn.Nullable, smoColumn.PhysicalDatatype, smoColumn.Length,
* smoColumn.InPrimaryKey, false, false, smoColumn.Identity);
* storedProcedure.Columns.Add(column);
* ordinalPosition++;*/
//}
// Parameters
string sql = string.Format(@"
SELECT PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, PARAMETER_MODE
FROM {0}.INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = '{1}' ", DatabaseName, storedProcName);
System.Data.SqlClient.SqlDataReader dr = null;
try
{
dr = RunQuerySqlClient(sql);
while (dr.Read())
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(parameterRow["PARAMETER_NAME"].ToString(), parameterRow["PARAMETER_NAME"].ToString().Replace("@", ""), parameterRow["DATA_TYPE"].ToString(),
//parameterRow["PARAMETER_MODE"].ToString(), System.Convert.ToInt32(parameterRow["ORDINAL_POSITION"]), System.Convert.ToInt32(parameterRow["CHARACTER_MAXIMUM_LENGTH"]));
//storedProcedure.AddParameter(parameter);
}
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return(storedProcedure);
}
public Model.StoredProcedure[] GetStoredProcedures()
{
List <Model.StoredProcedure> storedProcedures = new List <Model.StoredProcedure>();
foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure smoStoredProcedure in Database.StoredProcedures)
{
if (!smoStoredProcedure.IsSystemObject)
{
Model.StoredProcedure storedProcedure = GetNewStoredProcedure(smoStoredProcedure);
storedProcedures.Add(storedProcedure);
}
}
return((Model.StoredProcedure[])storedProcedures.ToArray());
}
private static Model.StoredProcedure.Parameter FindAssociatedParameter(string alias)
{
if (typeof(Model.StoredProcedure).IsInstanceOfType(frmAssociationWizard.Association.AssociatedObject))
{
Model.StoredProcedure associatedObject = (Model.StoredProcedure)frmAssociationWizard.Association.AssociatedObject;
foreach (Model.StoredProcedure.Parameter parameter in associatedObject.Parameters)
{
if (Slyce.Common.Utility.StringsAreEqual(parameter.Alias, alias, false))
{
return(parameter);
}
}
}
return(null);
}
private Model.StoredProcedure GetNewStoredProcedure(string storedProcedureName)
{
ArchAngel.Interfaces.ProjectHelper.RaiseObjectBeingProcessedEvent(storedProcedureName, "SP");
_parameters = null;
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(storedProcedureName, false);
storedProcedure.Enabled = false;
DataRow[] parameterRows = Parameters.Select(string.Format("ProcedureName = '{0}'", storedProcedureName));
foreach (DataRow parameterRow in parameterRows)
{
Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(parameterRow["PARAMETER_NAME"].ToString(), parameterRow["DATA_TYPE"].ToString(),
parameterRow["PARAMETER_MODE"].ToString(), System.Convert.ToInt32(parameterRow["ORDINAL_POSITION"]), parameterRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : System.Convert.ToInt32(parameterRow["CHARACTER_MAXIMUM_LENGTH"]));
storedProcedure.AddParameter(parameter);
}
return(storedProcedure);
}
public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
{
string sql = "exec [" + storedProcedure.Name + "] ";
for (int i = 0; i < storedProcedure.Parameters.Length; i++)
{
Model.StoredProcedure.Parameter parameter = storedProcedure.Parameters[i];
sql += parameter.Name + "=NULL";
if (i < storedProcedure.Parameters.Length - 1)
{
sql += ", ";
}
}
DataTable dataTable = RunQueryDataTable(sql);
while (storedProcedure.Columns.Length > 0)
{
storedProcedure.RemoveColumn(storedProcedure.Columns[0]);
}
foreach (DataColumn dataColumn in dataTable.Columns)
{
Column column = new Column(
dataColumn.ColumnName,
false,
storedProcedure,
dataColumn.Ordinal,
dataColumn.AllowDBNull,
GetSqlDataType(dataColumn.DataType),
0,
false,
dataColumn.AutoIncrement,
dataColumn.DefaultValue.ToString(),
false);
if (IsSupported(column))
{
storedProcedure.AddColumn(column);
}
}
}
//private void ProcessProsWithTempReferences(List<StoredProcDef> storedProcs)
//{
// bool added = true;
// while (added)
// {
// added = false;
// for (int i = 0; i < storedProcs.Count; i++)
// {
// string name = storedProcs[i].Name;
// name = name.Substring(0, name.IndexOf("|"));
// if (StoredProcsWithTempReferences.BinarySearch(name) >= 0)
// {
// continue;
// }
// for (int procCounter = 0; procCounter < StoredProcsWithTempReferences.Count; procCounter++)
// {
// string badProc = StoredProcsWithTempReferences[procCounter];
// if (storedProcs[i].Body.IndexOf(badProc) > 0)
// {
// StoredProcsWithTempReferences.Add(name);
// StoredProcsWithTempReferences.Sort();
// added = true;
// break;
// }
// }
// }
// }
//}
private Model.StoredProcedure GetNewStoredProcedure(string storedProcedureName, string schema)
{
if (string.IsNullOrEmpty(schema))
throw new ArgumentException("Schema must be specified to get stored proc.");
Interfaces.Events.RaiseObjectBeingProcessedEvent(storedProcedureName, "SP");
//_columns = null;
//_parameters = null;
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(storedProcedureName, false);
storedProcedure.Enabled = false;
storedProcedure.Schema = schema;
DataRow[] parameterRows = Parameters.Select(string.Format("ProcedureName = '{0}'", storedProcedureName));
foreach (DataRow parameterRow in parameterRows)
{
Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(
parameterRow["PARAMETER_NAME"].ToString().Replace("@", ""),
parameterRow["DATA_TYPE"].ToString(),
parameterRow["PARAMETER_MODE"].ToString(),
int.Parse(parameterRow["ORDINAL_POSITION"].ToString()),
parameterRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : int.Parse(parameterRow["CHARACTER_MAXIMUM_LENGTH"].ToString()),
parameterRow.IsNull("NUMERIC_PRECISION") ? 0 : int.Parse(parameterRow["NUMERIC_PRECISION"].ToString()),
parameterRow.IsNull("NUMERIC_SCALE") ? 0 : int.Parse(parameterRow["NUMERIC_SCALE"].ToString()));
storedProcedure.AddParameter(parameter);
}
FillStoredProcedureColumns(storedProcedure);
return storedProcedure;
}
private Model.StoredProcedure GetNewStoredProcedure(string storedProcName)
{
ArchAngel.Interfaces.Events.RaiseObjectBeingProcessedEvent(storedProcName, "SP");
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(storedProcName, false);
storedProcedure.Enabled = false;
// Columns
//int ordinalPosition = 0;
//Microsoft.SqlServer.Management.Smo.Column[] smoColumns = GetColumns(smoStoredProcedure);
//foreach (Microsoft.SqlServer.Management.Smo.Column smoColumn in smoColumns)
//{
/*if (UnsupportedDataTypes.ToLower().IndexOf("'" + smoColumn.PhysicalDatatype.ToLower() + "'") >= 0)
{
continue;
}
Column column = new Column(smoColumn.Name, Script.GetSingular(smoColumn.Name), smoColumn.Name, storedProcedure, ordinalPosition, smoColumn.Nullable, smoColumn.PhysicalDatatype, smoColumn.Length,
smoColumn.InPrimaryKey, false, false, smoColumn.Identity);
storedProcedure.Columns.Add(column);
ordinalPosition++;*/
//}
// Parameters
string sql = string.Format(@"
SELECT PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, PARAMETER_MODE
FROM {0}.INFORMATION_SCHEMA.PARAMETERS
WHERE SPECIFIC_NAME = '{1}' ", DatabaseName, storedProcName);
System.Data.SqlClient.SqlDataReader dr = null;
try
{
dr = RunQuerySqlClient(sql);
while (dr.Read())
{
//Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(parameterRow["PARAMETER_NAME"].ToString(), parameterRow["PARAMETER_NAME"].ToString().Replace("@", ""), parameterRow["DATA_TYPE"].ToString(),
//parameterRow["PARAMETER_MODE"].ToString(), System.Convert.ToInt32(parameterRow["ORDINAL_POSITION"]), System.Convert.ToInt32(parameterRow["CHARACTER_MAXIMUM_LENGTH"]));
//storedProcedure.AddParameter(parameter);
}
}
finally
{
if (dr != null) { dr.Close(); }
}
return storedProcedure;
}
public static Model.StoredProcedure GetStoredProcedure(IList <Model.StoredProcedure> storedProcedures, Model.StoredProcedure storedProcedureToFind)
{
return(GetStoredProcedure(storedProcedures, storedProcedureToFind, true));
}
public static Model.StoredProcedure GetStoredProcedure(IList <Model.StoredProcedure> storedProcedures, Model.StoredProcedure storedProcedureToFind, bool throwException)
{
foreach (Model.StoredProcedure storedProcedure in storedProcedures)
{
if (storedProcedure.Name == storedProcedureToFind.Name &&
storedProcedure.Schema == storedProcedureToFind.Schema)
{
return(storedProcedure);
}
}
if (throwException)
{
throw new Exception(string.Format("Cannot find storedProcedure {0}.{1}", storedProcedureToFind.Schema, storedProcedureToFind.Name));
}
return(null);
}
public static Model.StoredProcedure.Parameter GetParameter(IList <Model.StoredProcedure> storedProcedures, Model.StoredProcedure storedProcedureToFind, string parameterName)
{
return(GetParameter(storedProcedures, storedProcedureToFind, parameterName, true));
}
public static Model.StoredProcedure.Parameter GetParameter(IList <Model.StoredProcedure> storedProcedures, Model.StoredProcedure storedProcedureToFind, string parameterName, bool throwException)
{
foreach (Model.StoredProcedure storedProcedure in storedProcedures)
{
if (storedProcedure.Name == storedProcedureToFind.Name &&
storedProcedure.Schema == storedProcedureToFind.Schema)
{
foreach (Model.StoredProcedure.Parameter parameter in storedProcedure.Parameters)
{
if (parameter.Name == parameterName)
{
return(parameter);
}
}
}
}
if (throwException)
{
throw new Exception(string.Format("Cannot find column {0} of stored procedure {1}.{2}", parameterName, storedProcedureToFind.Schema, storedProcedureToFind.Name));
}
return(null);
}
public Model.StoredProcedure[] GetStoredProcedures()
{
//StoredProcsWithTempReferences.Clear();
const string sql1 = @"
SELECT
ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_DEFINITION,
(
SELECT
CAST(CASE
WHEN tbl.is_ms_shipped = 1 THEN 1
WHEN (SELECT major_id FROM sys.extended_properties WHERE major_id = tbl.object_id AND minor_id = 0 AND class = 1 AND name = N'microsoft_database_tools_support') IS NOT NULL THEN 1 ELSE 0
END AS bit
) AS IsSystemObject
FROM
sys.procedures AS tbl
WHERE
(name = T.ROUTINE_NAME)) AS IsSystemObject
FROM
INFORMATION_SCHEMA.ROUTINES AS T
WHERE
ROUTINE_TYPE = 'PROCEDURE'
ORDER BY
ROUTINE_NAME";
// For the databases that are missing some system tables
const string sql2 = @"
SELECT
ROUTINE_NAME, ROUTINE_SCHEMA, ROUTINE_DEFINITION,
CAST(CASE
WHEN OBJECTPROPERTY(sp.id, N'IsMSShipped') = 1 THEN 1
WHEN OBJECTPROPERTY(sp.id, N'IsSystemTable') = 1 THEN 1 ELSE 0
END AS bit) AS IsSystemObject
FROM
INFORMATION_SCHEMA.ROUTINES AS T
INNER JOIN dbo.sysobjects SP ON SP.name = T.ROUTINE_NAME
WHERE
ROUTINE_TYPE = 'PROCEDURE'
ORDER BY
ROUTINE_NAME";
List <StoredProcDef> storedProcedureNames = new List <StoredProcDef>();
//OleDbDataReader oleDbDataReader = null;
System.Data.SqlClient.SqlDataReader sqlDataReader = null;
try
{
try
{
//oleDbDataReader = RunQuerySQL(sql1);
sqlDataReader = RunQuerySQL(sql1);
}
catch (Exception ex)
{
string message = ex.Message.ToLower();
if (message.IndexOf("schema_name") > 0 || message.IndexOf("invalid object name") >= 0)
{
//oleDbDataReader = RunQuerySQL(sql2);
sqlDataReader = RunQuerySQL(sql2);
}
else
{
throw;
}
}
// Exclude system storedProcedures
int isSysObjectColumnOrdinal = sqlDataReader.GetOrdinal("IsSystemObject");
while (sqlDataReader.Read())
{
bool isSystemObject = sqlDataReader.IsDBNull(isSysObjectColumnOrdinal) ? false : (bool)sqlDataReader[isSysObjectColumnOrdinal];
if (!isSystemObject || Model.Database.IncludeSystemObjects)
{
//storedProcedureNames.Add(oleDbDataReader["ROUTINE_NAME"].ToString() + "|" + oleDbDataReader["ROUTINE_SCHEMA"].ToString());
storedProcedureNames.Add(new StoredProcDef(sqlDataReader["ROUTINE_NAME"].ToString() + "|" + sqlDataReader["ROUTINE_SCHEMA"].ToString(), sqlDataReader["ROUTINE_DEFINITION"].ToString()));
//if (oleDbDataReader["ROUTINE_DEFINITION"].ToString().IndexOf("#") > 0)
//{
// StoredProcsWithTempReferences.Add(oleDbDataReader["ROUTINE_NAME"].ToString());
//}
}
}
}
finally
{
if (sqlDataReader != null && !sqlDataReader.IsClosed)
{
sqlDataReader.Close();
}
}
//StoredProcsWithTempReferences.Sort();
//ProcessProsWithTempReferences(storedProcedureNames);
List <Model.StoredProcedure> storedProcedures = new List <Model.StoredProcedure>();
foreach (StoredProcDef storedProcedureNameEx in storedProcedureNames)
{
string storedProcedureName = storedProcedureNameEx.Name.Split('|')[0];
string schema = storedProcedureNameEx.Name.Split('|')[1];
Model.StoredProcedure storedProcedure = GetNewStoredProcedure(storedProcedureName, schema);
storedProcedures.Add(storedProcedure);
}
return(storedProcedures.ToArray());
}
public Model.StoredProcedure[] GetStoredProcedures()
{
string sql1 = @"
SELECT
ROUTINE_NAME, ROUTINE_SCHEMA
(
SELECT
CAST(CASE
WHEN tbl.is_ms_shipped = 1 THEN 1
WHEN (SELECT major_id FROM sys.extended_properties WHERE major_id = tbl.object_id AND minor_id = 0 AND class = 1 AND name = N'microsoft_database_tools_support') IS NOT NULL THEN 1 ELSE 0
END AS bit
) AS IsSystemObject
FROM
sys.procedures AS tbl
WHERE
(name = T.ROUTINE_NAME) AND (SCHEMA_NAME(schema_id) = N'dbo')) AS IsSystemObject
FROM
INFORMATION_SCHEMA.ROUTINES AS T
WHERE
ROUTINE_TYPE = 'PROCEDURE'
ORDER BY
ROUTINE_NAME";
// For the databases that are missing some system tables
string sql2 = @"
SELECT
sp.name AS ROUTINE_NAME, '' AS ROUTINE_SCHEMA
CAST(CASE
WHEN OBJECTPROPERTY(sp.id, N'IsMSShipped') = 1 THEN 1
WHEN OBJECTPROPERTY(sp.id, N'IsSystemTable') = 1 THEN 1 ELSE 0
END AS bit) AS IsSystemObject
FROM
dbo.sysobjects sp INNER JOIN
dbo.sysusers ssp ON ssp.uid = sp.uid
WHERE
sp.type = 'P'
ORDER BY
ROUTINE_NAME";
List <string> storedProcedureNames = new List <string>();
OleDbDataReader oleDbDataReader = null;
try
{
try
{
oleDbDataReader = RunQuery(sql1);
}
catch (Exception ex)
{
if (ex.Message.IndexOf("SCHEMA_NAME") > 0)
{
oleDbDataReader = RunQuery(sql2);
}
else
{
throw;
}
}
// Exclude system storedProcedures
int isSysObjectColumnOrdinal = oleDbDataReader.GetOrdinal("IsSystemObject");
while (oleDbDataReader.Read())
{
bool isSystemObject = oleDbDataReader.IsDBNull(isSysObjectColumnOrdinal) ? false : (bool)oleDbDataReader[isSysObjectColumnOrdinal];
if (!isSystemObject)
{
storedProcedureNames.Add(oleDbDataReader["ROUTINE_NAME"].ToString() + "|" + oleDbDataReader["ROUTINE_SCHEMA"].ToString());
}
}
}
finally
{
if (oleDbDataReader != null && !oleDbDataReader.IsClosed)
{
oleDbDataReader.Close();
}
}
List <Model.StoredProcedure> storedProcedures = new List <Model.StoredProcedure>();
foreach (string storedProcedureNameEx in storedProcedureNames)
{
string storedProcedureName = storedProcedureNameEx.Split('|')[0];
string schema = storedProcedureNameEx.Split('|')[1];
Model.StoredProcedure storedProcedure = GetNewStoredProcedure(storedProcedureName);
storedProcedure.Schema = schema;
storedProcedures.Add(storedProcedure);
}
return((Model.StoredProcedure[])storedProcedures.ToArray());
}
public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
{
IStoredProcedure dal = DALFactory.DataAccess.CreateStoredProcedure(DalAssemblyName, ConnectionString);
dal.FillStoredProcedureColumns(storedProcedure);
}
private Model.StoredProcedure GetNewStoredProcedure(string storedProcedureName)
{
ArchAngel.Interfaces.ProjectHelper.RaiseObjectBeingProcessedEvent(storedProcedureName, "SP");
_parameters = null;
Model.StoredProcedure storedProcedure = new Model.StoredProcedure(storedProcedureName, false);
storedProcedure.Enabled = false;
DataRow[] parameterRows = Parameters.Select(string.Format("ProcedureName = '{0}'", storedProcedureName));
foreach (DataRow parameterRow in parameterRows)
{
Model.StoredProcedure.Parameter parameter = new Model.StoredProcedure.Parameter(parameterRow["PARAMETER_NAME"].ToString(), parameterRow["DATA_TYPE"].ToString(),
parameterRow["PARAMETER_MODE"].ToString(), System.Convert.ToInt32(parameterRow["ORDINAL_POSITION"]), parameterRow.IsNull("CHARACTER_MAXIMUM_LENGTH") ? 0 : System.Convert.ToInt32(parameterRow["CHARACTER_MAXIMUM_LENGTH"]));
storedProcedure.AddParameter(parameter);
}
return storedProcedure;
}
public Model.StoredProcedure[] GetStoredProcedures()
{
List <Model.StoredProcedure> storedProcedures = new List <Model.StoredProcedure>();
string sql = string.Format(@"SELECT ROUTINE_NAME,
(
SELECT
CAST(
CASE
WHEN tbl.is_ms_shipped = 1 THEN 1
WHEN (
SELECT
major_id
FROM
sys.extended_properties
WHERE
major_id = tbl.object_id and
minor_id = 0 and
class = 1 and
name = N'microsoft_database_tools_support')
IS NOT NULL THEN 1
ELSE 0
END
AS bit) AS [IsSystemObject]
FROM
sys.procedures AS tbl
WHERE
(tbl.name = T.ROUTINE_NAME and SCHEMA_NAME(tbl.schema_id)=N'dbo')
) AS IsSystemObject
FROM {0}.INFORMATION_SCHEMA.ROUTINES T
WHERE T.ROUTINE_TYPE = 'PROCEDURE'
ORDER BY T.ROUTINE_NAME", DatabaseName);
System.Data.SqlClient.SqlDataReader dr = null;
try
{
dr = RunQuerySqlClient(sql);
System.Collections.ArrayList arrStoredProcNames = new System.Collections.ArrayList();
bool isSystemObject;
int isSysObjectColumnOrdinal = dr.GetOrdinal("IsSystemObject");
while (dr.Read())
{
isSystemObject = dr.IsDBNull(isSysObjectColumnOrdinal) ? false : (bool)dr[isSysObjectColumnOrdinal];
if (!isSystemObject || Model.Database.IncludeSystemObjects)
{
arrStoredProcNames.Add((string)dr["ROUTINE_NAME"]);
}
}
dr.Close();
for (int i = 0; i < arrStoredProcNames.Count; i++)
{
Model.StoredProcedure storedProcedure = GetNewStoredProcedure((string)arrStoredProcNames[i]);
storedProcedures.Add(storedProcedure);
}
}
finally
{
if (dr != null)
{
dr.Close();
}
}
return((Model.StoredProcedure[])storedProcedures.ToArray());
}
public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
{
}
public void FillStoredProcedureColumns(Model.StoredProcedure storedProcedure)
{
string sql = string.Format("SET FMTONLY ON EXEC [{0}].[{1}] ", storedProcedure.Schema, storedProcedure.Name);
for (int i = 0; i < storedProcedure.Parameters.Length; i++)
{
if (Providers.Database.Helper.SQLServer.IsDataTypeText(storedProcedure.Parameters[i].DataType))
{
sql += "\"0\", ";
}
else
{
sql += "0, ";
}
}
sql = sql.TrimEnd(EndChars);
DataTable dataTable;
try
{
dataTable = RunQueryDataTable(sql);
}
catch (Exception ex)
{
//ResetConnection();
sql = sql.Replace("SET FMTONLY ON", "SET FMTONLY OFF");
try
{
using (System.Transactions.TransactionScope scope = new System.Transactions.TransactionScope(System.Transactions.TransactionScopeOption.RequiresNew))
{
dataTable = RunQueryDataTable(sql);
// We must ALWAYS rollback because we are executing stored procs with FMTONLY = OFF, so just don't call scope.Complete()
}
}
catch
{
storedProcedure.Errors.Add(string.Format("Error getting returned columns: {0}. SQL: {1}", ex.Message, sql));
return;
}
}
while (storedProcedure.Columns.Length > 0)
{
storedProcedure.RemoveColumn(storedProcedure.Columns[0]);
}
foreach (DataColumn dataColumn in dataTable.Columns)
{
Column column = new Column(
dataColumn.ColumnName,
false,
storedProcedure,
dataColumn.Ordinal,
dataColumn.AllowDBNull,
GetSqlDataType(dataColumn.DataType),
0,
false,
dataColumn.AutoIncrement,
dataColumn.DefaultValue.ToString(),
false,
false,
0,
0);
if (IsSupported(column))
{
storedProcedure.AddColumn(column);
}
}
}
