Package database with a Criteria ?

Packages and compresses schema and contents of SQL Server databases

Moderators: JonathanWatts, Chris Auckland, David Atkinson, david connell, Anu Deshpande, Michelle Taylor, james.billings, Marianne

Package database with a Criteria ?

Postby compvis » Wed Mar 23, 2011 1:10 pm

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 !
compvis
 
Posts: 9
Joined: Mon Aug 02, 2010 7:43 am

Postby Brian Donahue » Wed Mar 23, 2011 4:21 pm

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby compvis » Thu Mar 24, 2011 2:11 am

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.
compvis
 
Posts: 9
Joined: Mon Aug 02, 2010 7:43 am

Postby Brian Donahue » Thu Mar 24, 2011 2:06 pm

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
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby Brian Donahue » Thu Mar 24, 2011 2:34 pm

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:
Code: Select all
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;
        }
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby compvis » Fri Mar 25, 2011 2:18 am

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 !
compvis
 
Posts: 9
Joined: Mon Aug 02, 2010 7:43 am

Postby Brian Donahue » Fri Mar 25, 2011 10:13 am

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
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am

Postby compvis » Sat Mar 26, 2011 3:58 am

Hi Brian Donahue,

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

Thank you so much !
compvis
 
Posts: 9
Joined: Mon Aug 02, 2010 7:43 am

Postby Brian Donahue » Mon Mar 28, 2011 9:15 am

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.
Brian Donahue
 
Posts: 6668
Joined: Mon Aug 23, 2004 10:48 am


Return to SQL Packager 6

Who is online

Users browsing this forum: No registered users and 0 guests