using System;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;

namespace SMOAdmin
{
    class Program
    {
        static void Main(string[] args)
        {
            //ListProperties();
            //StartAJob();
            //Backup();
            //Partitioning();
            TransferDB();
            //ColumnConstraint();
        }

        static void ListProperties()
        {
            // This sample lists all properties, and the version information. There is more information available, such as: if a property is writeable (it is always readable), or if it is expensive (means that it will be retrieved on demand only, due to high cost). 
            // Server svr = new Server(instance);
            Server svr = new Server();
            Database db = svr.Databases[0];
            foreach (SqlPropertyInfo i in db.Properties.EnumPropertyInfo())
            {
               Console.WriteLine("{0,-35} {1,-25} {2}", i.Name, i.PropertyType.Name, i.Versions.ToString().Replace("Version", ""));
            }
        }

        static void StartAJob()
        {
            // Create simple job and start the job after creation

            // Server svr = new Server(instance);
            Server svr = new Server();
            JobServer agent = svr.JobServer;
            if (agent.Jobs.Contains("New Smo Job"))
            {
               agent.Jobs["New Smo Job"].Drop();
            }
            Job j = new Job(agent, "New Smo Job");
            JobStep js = new JobStep(j, "Step 1");
            js.SubSystem = AgentSubSystem.TransactSql;
            js.Command = "select 1";
            j.Create();
            j.ApplyToTargetServer(svr.Name);
            j.Start();

        }
        
        static void Backup()
        {
            //Purpose of sample: Backup database to various mirrors
            
            Server svr = new Server();
            Backup b = new Backup();
            b.BackupSetDescription = "Set description";
            b.Action = BackupActionType.Database;
            b.BackupSetName = "Backup set #1";
            b.Database = "msdb";
            b.MediaDescription = "Media description";
            b.MediaName = "Media name";
            b.Devices.AddDevice(@"c:\back1a.bak", DeviceType.File);
            b.Devices.AddDevice(@"c:\back1b.bak", DeviceType.File);
            BackupDeviceList[] la = new BackupDeviceList[2];
            BackupDeviceList l1 = new BackupDeviceList();
            l1.AddDevice(@"c:\back2a.bak", DeviceType.File);
            l1.AddDevice(@"c:\back2b.bak", DeviceType.File);
            BackupDeviceList l2 = new BackupDeviceList();
            l2.AddDevice(@"c:\back3a.bak", DeviceType.File);
            l2.AddDevice(@"c:\back3b.bak", DeviceType.File);
            la[0] = l1;
            la[1] = l2;
            b.Mirrors = la;
            b.FormatMedia = true;
            b.Initialize = true;
            b.SkipTapeHeader = true;
            b.SqlBackup(svr);

        }
        
        static void Partitioning()
        {

            // Purpose of sample: create a partition function and partition scheme on an existing database
            
            // Server svr = new Server(instance);
            Server svr = new Server();
            if (svr.Databases.Contains("smotestdb")) // change the name if needed
            {
               svr.Databases["smotestdb"].Drop();
            }
            Database db = new Database(svr, "smotestdb");
            db.Create();
            db.FileGroups.Add(new FileGroup(db, "PART1_FG"));
            db.FileGroups.Add(new FileGroup(db, "PART2_FG"));
            db.FileGroups[0].Files.Add(new DataFile(db.FileGroups[0], "datafile1", @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db_1.mdf"));
            db.FileGroups[1].Files.Add(new DataFile(db.FileGroups[1], "datafile2", @"C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db_2.mdf"));
            db.Alter();
            PartitionFunction pf = new PartitionFunction(db, "MyPartitionFunction");
            pf.PartitionFunctionParameters.Add(new PartitionFunctionParameter(pf, DataType.Int));
            pf.RangeType = RangeType.Left;
            pf.RangeValues = new object[] { 5000 };
            pf.Create();
            PartitionScheme ps = new PartitionScheme(db, "MyPartitionScheme");
            ps.PartitionFunction = "MyPartitionFunction";
            ps.FileGroups.Add("PART1_FG");
            ps.FileGroups.Add("PART2_FG");
            ps.Create();
            Table t = new Table(db, "MyTable");
            t.Columns.Add(new Column(t, "c1", DataType.Int));
            t.Columns.Add(new Column(t, "c2", DataType.VarChar(100)));
            t.PartitionScheme = "MyPartitionScheme";
            t.PartitionSchemeParameters.Add(new PartitionSchemeParameter(t, "c1"));
            t.Create();

        }

        static void TransferDB()
        {
            // Purpose of sample: Transfer a database; all objects and data
            
            // Setup source connection (in this sample source is .\inst1 and target is '.' (the default instance)
            Server target = new Server(@".\sql1");
            Server svr = new Server(".");
            Database db = svr.Databases["xmltest"];
            // Setup transfer
            Transfer t = new Transfer(db);
            t.CopyAllObjects = true;
            t.DropDestinationObjectsFirst = true;
            t.CopySchema = true;
            t.CopyData = true;
            t.DestinationServer = "zmv17";
            t.DestinationDatabase = "testdb";
            t.Options.IncludeIfNotExists = true;
            // Do the work
            //t.TransferData();  // Or use ScriptTransfer() if you need to capture the script (without data)
            
            StringCollection c = t.ScriptTransfer();
            foreach (String str in c)
                Console.WriteLine(str);
             

        }

        static void ColumnConstraint()
        {
            //Purpose of sample: show how a column constraint can be created.
 
            Server svr = new Server();
            Database db = svr.Databases["testdb"];

            Table tbl = new Table (db, "MyTestTable");
            Column c1 = new Column(tbl, "c1", DataType.DateTime);
            c1.AddDefaultConstraint(/*name optional*/);
            c1.DefaultConstraint.Text = "getdate()";
            tbl.Columns.Add(c1);
            tbl.Create();

        }
    }
}
