public DbsyncTvStoredProcType(SProcedure spp)
{
this.sp = spp;
this.Text = spp.NazovProcedury;
this.typeOfIcon = StoredProcedureIcon;
this.Name = this.Text;
}
public DbSyncStoredProcedureDiff(SProcedure procAin, SProcedure procBin)
{
this.procA = procAin;
this.procB = procBin;
storedProcDiffListA = new List<ObjectAtribute>();
storedProcDiffListB = new List<ObjectAtribute>();
privilegeDifList = new List<DbSyncPrivilegeDiff>();
grantsMissingDb1 = new List<Privilege>();
grantsMissingDb2 = new List<Privilege>();
grantsDifferent = new List<DbSyncPrivilegeDiff>();
fulfillLists();
ComparatorOfSQL compSQL;
if (procA == null || procB == null)
{
if (procA != null)
{
procedureName = procA.NazovProcedury;
storedProcDiffListA.Add(new ObjectAtribute("Procedure name", procedureName, false));
storedProcDiffListA.Add(new ObjectAtribute("Sql text ", "Click button", false, true));
compSQL = new ComparatorOfSQL(procA.SqlText, null);
sqlTextListA = compSQL.TextA;
sqlTextListB = null;
}
else if (procB != null)
{
procedureName = procB.NazovProcedury;
storedProcDiffListB.Add(new ObjectAtribute("Procedure name", procedureName, false));
storedProcDiffListB.Add(new ObjectAtribute("Sql text ", "Click button", false, true));
compSQL = new ComparatorOfSQL(null, procB.SqlText);
sqlTextListA = null;
sqlTextListB = compSQL.TextB;
}
else procedureName = "UNDEFINED";
}
if (procA != null && procB != null)
{
compSQL = new ComparatorOfSQL(procA.SqlText, procB.SqlText);
sqlTextListA = compSQL.TextA;
sqlTextListB = compSQL.TextB;
procedureName = procB.NazovProcedury;
storedProcDiffListA.Add(new ObjectAtribute("Procedure name", procedureName,false));
storedProcDiffListB.Add(new ObjectAtribute("Procedure name", procedureName,false));
if (procA.NazovProcedury != procB.NazovProcedury) diffNameOfProcedure = true;
if (compSQL.IsDifferent) diffSqlText = true;
if (!ComparePrivileges(procA.Privieges, procB.Privieges)) diffPrivileges = true;
if (diffNameOfProcedure || diffPrivileges || diffSqlText) different = true;
else different = false;
//vytvorim privileges
foreach (Privilege privA in procA.Privieges)
{
bool found = false;
foreach (Privilege privB in procB.Privieges)
{
if (privA.DBSyncCompareTO(privB))
{
found = true;
this.privilegeDifList.Add(new DbSyncPrivilegeDiff(privA, privB));
}
}
if (!found) this.privilegeDifList.Add(new DbSyncPrivilegeDiff(privA, null));
}
foreach (Privilege privB in procB.Privieges)
{
bool found = false;
foreach (DbSyncPrivilegeDiff priv in privilegeDifList)
{
if (privB.getName() == priv.getName())
{
found = true;
}
}
if (!found) this.privilegeDifList.Add(new DbSyncPrivilegeDiff(null, privB));
}
if (diffSqlText)
{
ObjectAtribute sqlA = new ObjectAtribute("Sql text ", "Click button", true, true);
storedProcDiffListA.Add(sqlA);
ObjectAtribute sqlB = new ObjectAtribute("Sql text ", "Click button", true, true);
storedProcDiffListB.Add(sqlB);
}
else
{
ObjectAtribute sql = new ObjectAtribute("Sql text ", "Click button", false, true);
storedProcDiffListA.Add(sql);
storedProcDiffListB.Add(sql);
}
}
else different = true;
}
//functions to generate scripts //scripts to create objects public abstract List<string> createProcedure(SProcedure procIn);
public abstract List<string> alterProcedure(SProcedure procIn);
//scripts to remove objects public abstract List<string> removeProcedure(SProcedure procIn);
public override List<string> removeProcedure(SProcedure procIn)
{
List<string> output = new List<string>();
output.Add("DROP PROCEDURE [" + procIn.NazovProcedury+"]");
return output;
}
//private void nacitajFKPreTab(Tablee tab)
//{
// SqlCommand prikaz = pripojenie.CreateCommand();
// prikaz.CommandType = CommandType.StoredProcedure;
// prikaz.CommandText = "dbo.sp_fkeys";
// prikaz.Parameters.Add("@fktable_name", SqlDbType.NVarChar, 128);
// prikaz.Parameters["@fktable_name"].Value = tab.NazovTabulky;
// SqlDataReader red = prikaz.ExecuteReader();
// while (red.Read())
// {
// string FKcolumnName = red["FKCOLUMN_NAME"].ToString();
// string PKTable = red["PKTABLE_NAME"].ToString();
// string PKTableCol = red["PKCOLUMN_NAME"].ToString();
// string FKName = red["FK_NAME"].ToString();
// foreach (Columnn s in tab.Stlpce)
// {
// if (s.COULUMN_NAME1 == FKcolumnName)
// {
// s.Name_of_FK = FKName;
// s.FK_nameOFPKTab = PKTable;
// s.FK_NameOFPKCol = PKTableCol;
// }
// }
// }
// red.Close();
//}
//nacitanie procedur
public override List<SProcedure> ReadProcedures()
{
List<SProcedure> procedury = new List<SProcedure>();
SqlCommand com = pripojenie.CreateCommand();
com.CommandText = "select * from sys.procedures";
SqlDataReader red = com.ExecuteReader();
List<string> nazvyproc = new List<string>();
while (red.Read())
{
string nazovProc = red["name"].ToString();
nazvyproc.Add(nazovProc);
}
red.Close();
//mam nacitane nazvy procedur
foreach (string nazov in nazvyproc)
{
//nacitanie parametrov pre procedury
/* SqlCommand com2 = pripojenie.CreateCommand();
com2.CommandText = " SELECT " +
" SO.name AS [ObjectName], " +
" P.parameter_id AS [ParameterID], " +
" P.name AS [ParameterName], " +
" TYPE_NAME(P.user_type_id) AS [ParameterDataType], " +
" P.max_length AS [ParameterMaxBytes], " +
" P.is_output AS [IsOutPutParameter] " +
" FROM sys.objects AS SO " +
" INNER JOIN sys.parameters AS P " +
" ON SO.OBJECT_ID = P.OBJECT_ID " +
" WHERE SO.OBJECT_ID IN ( SELECT OBJECT_ID " +
" FROM sys.objects " +
" WHERE TYPE IN ('P')) " +
" and SO.name = 'Procedura1' " +
" ORDER BY SO.name, P.parameter_id ";
com2.CommandType = CommandType.Text;
SqlDataReader red2 = com2.ExecuteReader();
List<string> parametre = new List<string>();
while (red2.Read())
{
}
red2.Close();
*/ //asi to cele nebude potrebne.
SqlCommand com2 = pripojenie.CreateCommand();
com2.CommandText = "sp_helptext";
com2.CommandType = CommandType.StoredProcedure;
com2.Parameters.Add(new SqlParameter("@objname", nazov));
SqlDataReader red2 = com2.ExecuteReader();
List<string> text = new List<string>();
while (red2.Read())
{
text.Add(red2["Text"].ToString());
}
red2.Close();
SProcedure sp = new SProcedure();
sp.NazovProcedury = nazov;
sp.SqlText = text;
//nacitam privileges pre sp
com2.CommandText = " SELECT "+
" USER_NAME(dppriper.grantee_principal_id) AS [UserName], "+
" dppri.type_desc AS principal_type_desc, "+
" dppriper.class_desc, "+
" OBJECT_NAME(dppriper.major_id) AS object_name, "+
" dppriper.permission_name, "+
" dppriper.state_desc AS permission_state_desc "+
" FROM sys.database_permissions dppriper "+
" INNER JOIN sys.database_principals dppri "+
" ON dppriper.grantee_principal_id = dppri.principal_id "+
" where OBJECT_NAME(dppriper.major_id) = '"+nazov+"'";
com2.CommandType = CommandType.Text;
red2 = com2.ExecuteReader();
while (red2.Read())
{
Privilege priv = new Privilege();
priv.Grantee = red2["UserName"].ToString();
priv.Privilege_type = red2["permission_name"].ToString();
priv.Table_name = red2["object_name"].ToString();
sp.Privieges.Add(priv);
}
procedury.Add(sp);
}
return procedury;
}
public override List<string> createProcedure(SProcedure procIn)
{
List <string> output = new List<string>();
string proctext = "";
foreach (string s in procIn.SqlText)
{
proctext += s;
}
output.Add(proctext);
foreach (Privilege priv in procIn.Privieges)
{
output.Add(createPrivilege(priv));
}
return output;
}
public override List<string> alterProcedure(SProcedure procIn)
{
List<string> output = new List<string>();
output.Add("DROP PROCEDURE [" + procIn.NazovProcedury+"]");
output.AddRange(createProcedure(procIn));
return output;
}
public override List<string> alterProcedure(SProcedure procIn)
{
throw new NotImplementedException();
}