/// <summary> /// Get stored procedures from indicated database /// </summary> /// <param name="database">Database</param> /// <param name="sqlServer"></param> /// <returns></returns> public Dictionary <string, MySmoObjectBase> GetStoredProceduresInfo( Microsoft.SqlServer.Management.Smo.Database database, Server sqlServer) { var myStoredProcedures = new Dictionary <string, MySmoObjectBase>(); const string commandText = " select ROUTINE_NAME " + " from INFORMATION_SCHEMA.ROUTINES " + " where ROUTINE_DEFINITION <> 'NULL' and ROUTINE_TYPE = 'PROCEDURE'" + " order by ROUTINE_NAME"; string connectionString = "server=" + sqlServer.Name + ";database=" + database.Name + ";uid=" + sqlServer.ConnectionContext.Login + ";password=" + sqlServer.ConnectionContext.Password; using (var cn = new SqlConnection(connectionString)) using (var cmd = new SqlCommand(commandText, cn)) { cn.Open(); cmd.CommandType = CommandType.Text; SqlDataReader sqlDR = cmd.ExecuteReader(); if (sqlDR != null) { while (sqlDR.Read()) { string name = sqlDR.GetString(0); MySmoObjectBase myStoreProcedure = new MyStoredProcedure( database.StoredProcedures[name], name, database.Name, database.StoredProcedures[name].TextBody); myStoredProcedures.Add(myStoreProcedure.Name, myStoreProcedure); } } } return(myStoredProcedures); }
/// <summary> /// Collect list for drop/create/alter of stores /// </summary> /// <param name="dropItems">Drop items list</param> /// <param name="createItems">Create items list</param> private void CollectStoreProcedureItems( ICollection <MySmoObjectBase> dropItems, ICollection <MySmoObjectBase> createItems) { Trace.TraceInformation(string.Format("Database={0} Collecting items for stored procedures...\r\n", mProductionDatabase.Name)); var productionProcedures = Database.GetStoredProceduresInfo(mProductionDatabase, mSqlServer); var sourceProcedures = Database.GetStoredProceduresInfo(mSourceDatabase, mSqlServer); DifferentSplitItems( createItems, dropItems, null, productionProcedures, sourceProcedures, null); // // Add NET stores from production database to drop list // Trace.TraceInformation(string.Format("Database={0} Drop old clr stored procedures...\r\n", mProductionDatabase.Name)); foreach (StoredProcedure storedProcedure in mProductionDatabase.StoredProcedures) { // New stored procedure has no IsSystemObject parameter try { if (storedProcedure.IsSystemObject) { continue; } } catch (Microsoft.SqlServer.Management.Smo.PropertyNotSetException) { continue; } if (!productionProcedures.ContainsKey(storedProcedure.Name)) { dropItems.Add(new MySmoObjectBase( storedProcedure, storedProcedure.Name, storedProcedure.Parent.Name)); } } // // Add NET stores from source database to create list // Trace.TraceInformation(string.Format("Database={0} Create new clr stored procedures...\r\n", mProductionDatabase.Name)); foreach (StoredProcedure storedProcedure in mSourceDatabase.StoredProcedures) { if (storedProcedure.IsSystemObject) { continue; } if (!sourceProcedures.ContainsKey(storedProcedure.Name)) { MySmoObjectBase myStoredProcedure = new MyStoredProcedure( storedProcedure, storedProcedure.Name, storedProcedure.Parent.Name, storedProcedure.TextBody); createItems.Add(myStoredProcedure.CreateSplitItem(mProductionDatabase, 0, null)); } } }