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