Package database with a Criteria ?

compvis

Hi,

In database, I have a table with some fields such as: ID, NameProducts, Price, Date. I have a question is how can i create a package for that table with condition Date is greater than 22/12/2011 (for example) to update database on other machine ?

My goal is to package a portion of data with a criteria from a database to update to another database (two database with the same schema structure).

Is it possible ?

In your worked sample, to package an upgrade, we have to compare from two databases. With my question above, we can create an upgrade from one database with detail criteria. I do hope you can help me to implement this task.

Thank you so much !

Brian Donahue

Hi,

There is no supported way of filtering data in upgrade packages, but what you could do is, first, create a package and save the script, then add the statements to populate your seed data and then just run packager to package the script.

compvis

hi Brian Donahue,
Thank you so much for help.
Could you please tell me in details ? especially in code (a sample), i think some people also needs this feature like me.

Sorry for my post above, i didn't say my question is in Package API. I want to implement in Package API.

Brian Donahue

There doesn't appear to be support for this in the Packager API. I will have to get the latest version of Packager and work out how to do it. Sorry our SDK is not kept up to date with all of the latest features in the UI tools.

Brian Donahue

Looks like we solve the problem by creating a new, empty ExecutionBlock and then reading the script file into the block. Then you specify the schema and data blocks in the PackagerEngine constructor and run the Package() method. Here is a method that will convert a SQL script file to an execution block object:

public ExecutionBlock LoadExecutionBlocksFromFile(string filename)
        {
            executionBlock = new ExecutionBlock(true);
            executionBlockSchema.AddBatchMarker();

            try
            {
                FileInfo fi = new FileInfo(filename);
              
                using (FileStream fs = fi.OpenRead())
                {
                    using (StreamReader sr = new StreamReader(fs))
                    {
                        string s;
                        while((s = sr.ReadLine()) != null)
                        {
                            if (s.StartsWith("GO"))
                            {
                                executionBlock.AddBatchMarker();
                            }
                            else
                            {
                                executionBlock.AddBatch(s);
                            }
                        }
                    }
                }
            }
            catch
            {
                executionBlock = new ExecutionBlock(true);
                executionBlock.AddBatchMarker();
            }
return executionBlock;
        }

compvis

Hello Brian Donahue,

i'm not clearly, but my problem is:

- Create a package from database in one or more tables with a criteria (example here is date from one day to up), i don't want to compare from two databases then package.
- Use this package, copy to other machine (by CD, USB...) to update data, not install database on this machine, only data updated.

Package API could do this task ? I think you will help us by creating a full sample of this. It will be useful for us, that need this feature from Package API.

Thank you so much ! Hope you help !

Brian Donahue

Maybe... but this is tricky. I guess, first, you would use the SqlDataCompareEngine to create a comparison with a where clause in the table you want to filter, then comare the old database to the new one and include the resulting ExecutionBlock in the package. Actually there are a number of ways you could use to filter the data:

http://sdk.red-gate.com/index.php/Filte ... a_overview

compvis

Hi Brian Donahue,

It means, i have to compare from two databases on local machine
You will support this feature for SQL Package API ? Package data from one database with a criteria !

Thank you so much !

Brian Donahue

The Packager API is just the bit that compiles the code and data into a "package", the actual comparisons are done using SQL Compare and Data Compare and the results are given to Packager. So this is kind of supported already in the API. You can use a WhereClause or SelectionDelegate to filter the data.