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