Create Own Data Version Control For Sql server database change

Data Version Control We can create a Data Version Control ( working as VSS) for change in Sql Server Database by using SQLDMO. In this Version control we can synchronize both Sql Server srv1 to Sql Server srv2 and than get latest from database on srv1 to destination database on srv2. By using create log table as IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ddl_log]') AND type in (N'U')) BEGIN CREATE TABLE [dbo].[ddl_log]( [PostTime] [datetime] NU

private void btnGenerate_Click(object sender, System.EventArgs e)
  {
   string strSPName;
   int Flag;
   StringBuilder strGeneratedScript=new StringBuilder(4000);

   SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();                
   srv.Connect(strConnectedServerF,strLoginIdF ,strPasswordF);
   SQLDMO._Database db=new SQLDMO.DatabaseClass();


   for(int i=0;i<srv.Databases.Count;i++)
   {
    if(srv.Databases.Item(i+1,this.strdbOwnerF).Name == this.strDatabaseF)
    {
     db= srv.Databases.Item(i+1,this.strdbOwnerF); 
    }
   }
   for(int j=0;j<lstViewLog.Items.Count;j++)
   {
    if(lstViewLog.Items[j].Selected)
    {
     //strSPName  =  lstViewLog.Items[j].SubItems[1].Text.ToString();
     strGeneratedScript.Remove(0,strGeneratedScript.Length);
     System.Windows.Forms.Application.DoEvents();
     strGeneratedScript.Append(this.GenerateScript(db,"PROCEDURE",lstViewLog.Items[j].SubItems[1].Text.ToString()));
     //update on destination server
     SQLDMO.SQLServer srvT = new SQLDMO.SQLServerClass();                
     srvT.Connect(strConnectedServerT,strLoginIdT ,strPasswordT);
     SQLDMO._Database dbT=new SQLDMO.DatabaseClass();
     
     for(int i=0;i<srvT.Databases.Count;i++)
     {
      if(srvT.Databases.Item(i+1,this.strdbOwnerT).Name == this.strDatabaseT)
      {
       dbT= srvT.Databases.Item(i+1,this.strdbOwnerT); 
       Flag = 0;
       for(int k=0;k<dbT.StoredProcedures.Count;k++)
       {
        if(dbT.StoredProcedures.Item(k+1,this.strdbOwnerT).Name.ToUpper() == lstViewLog.Items[j].SubItems[1].Text.ToString().Trim().ToUpper())
        {
         dbT.StoredProcedures.Item(k+1,this.strdbOwnerT).Alter(strGeneratedScript.ToString());
         Flag = 1;
        }
       }
       if(Flag == 0)
       {
        SQLDMO._StoredProcedure objSqlDmoSp = new SQLDMO.StoredProcedureClass();
        objSqlDmoSp.Text = strGeneratedScript.ToString();
        dbT.StoredProcedures.Add(objSqlDmoSp);
       }
      }
     }


    }
   }
  }
 
 private string GenerateScript(SQLDMO._Database sqlDB, string objectType,string objectName)
  {
   SQLDMO.SQLDMO_SCRIPT_TYPE objScriptType= SQLDMO.SQLDMO_SCRIPT_TYPE.SQLDMOScript_Default;

   SQLDMO.SQLDMO_SCRIPT2_TYPE objScript2Type = SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_ExtendedProperty
   | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_FullTextCat
   | SQLDMO.SQLDMO_SCRIPT2_TYPE.SQLDMOScript2_FullTextIndex;

   string strReturn="";

   if(objectType.Trim().ToUpper()=="PROCEDURE")
   {
    for(int j=0;j<sqlDB.StoredProcedures.Count;j++)
    {
     if(sqlDB.StoredProcedures.Item(j+1,this.strdbOwnerF).Name.ToUpper()==objectName.Trim().ToUpper())
     {
     strReturn= sqlDB.StoredProcedures.Item(j+1,this.strdbOwnerF).Script(objScriptType,null,objScript2Type).ToString();
     }
    }

   }

   if(objectType.Trim().ToUpper()=="VIEW")
   {
    for(int j=0;j<sqlDB.Views.Count;j++)
    {
     if(sqlDB.Views.Item(j+1,this.strdbOwnerF).Name.ToUpper()==objectName.Trim().ToUpper())
     {
      strReturn= sqlDB.Views.Item(j+1,this.strdbOwnerF).Script(objScriptType,null,objScript2Type).ToString();
     }
    }

   }

   return strReturn;

  }

  private void btnClose_Click(object sender, System.EventArgs e)
  {
   this.Close();
  }
 }

By Web Star   Popularity  (1441 Views)
Biography - Web Star
Visit my Blog Web developer using Asp.net,C#.net,Sql Server, Silverlight, Javascript, CSS, AJAX etc.