Red Gate forums :: View topic - Package database with a Criteria ?
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Packager 6
SQL Packager 6 forum

Package database with a Criteria ?

Search in SQL Packager 6 forum
Post new topic   Reply to topic
Jump to:  
Author Message
compvis



Joined: 02 Aug 2010
Posts: 9

PostPosted: Wed Mar 23, 2011 1:10 pm    Post subject: Package database with a Criteria ? Reply with quote

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 !
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Wed Mar 23, 2011 4:21 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
compvis



Joined: 02 Aug 2010
Posts: 9

PostPosted: Thu Mar 24, 2011 2:11 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Thu Mar 24, 2011 2:06 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Thu Mar 24, 2011 2:34 pm    Post subject: Reply with quote

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:

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;
        }
Back to top
View user's profile Send private message
compvis



Joined: 02 Aug 2010
Posts: 9

PostPosted: Fri Mar 25, 2011 2:18 am    Post subject: Reply with quote

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 !
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Fri Mar 25, 2011 10:13 am    Post subject: Reply with quote

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/Filtering_data_overview
Back to top
View user's profile Send private message
compvis



Joined: 02 Aug 2010
Posts: 9

PostPosted: Sat Mar 26, 2011 3:58 am    Post subject: Reply with quote

Hi Brian Donahue,

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

Thank you so much !
Back to top
View user's profile Send private message
Brian Donahue



Joined: 23 Aug 2004
Posts: 6644

PostPosted: Mon Mar 28, 2011 9:15 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group