Using SMO to script PARTIAL data content (only rows matching a WHERE clause)

Question

I use SMO to fill a SQL Compact database with the data of a SQL server database.

Here is the code I actually use:

foreach(Table l_tblCurrent in l_dbDatabase.Tables)
{
  if(l_tblCurrent.IsSystemObject) continue;

  ScriptingOptions l_scOptions = new ScriptingOptions();

  l_scOptions.NoIdentities        = true;
  l_scOptions.NoCollation         = true;
  l_scOptions.NoCommandTerminator = true;
  l_scOptions.NoFileGroup         = true;
  l_scOptions.ScriptSchema        = true;
  l_scOptions.ScriptData          = true;

  foreach(string l_strCurrent in l_tblCurrent.EnumScript(l_scOptions))
  {
    l_sccDBFCommand.CommandText = l_strCurrent.Replace("[dbo].", "");
    l_sccDBFCommand.ExecuteNonQuery();
  }
}

It works perfectly, but for several tables, I don't want to copy all the rows. I want to be able to select only rows matching a WHERE clause to be copied.

Is it possible ?

Evadnee

Why not simply use the SQL Server Compact Toolbox (or related scripting API) to do this for you - the API supports a "WHERE" clause – Mewl

Hello ErikEJ, thank you for your comment. I cannot use a toolbox, the code in the sample is a little part of a big software which automates the creation of data packages to be used on specials handheld devices. – Evadnee

"or related scripting API" - ie a library that you can include in your "big software" nuget.org/packages/ErikEJ.SqlCeScripting – Mewl

did you get alternative solution? source code sample maybe... – Anam

Hello Kiquenet, my solution was to populate all the data in the SQL Compact database and then connect to the compact database and delete unwanted items with SQL commands. Not really fast, but works. – Evadnee

Answer 1

Doesn't look like SMO supports WHERE clause or any other mechanism to limit the number of records. My suggested workaround is to create a new table containing the subset of records, script it, then drop it. Everything can be done programmatically.

Edie

any script programmatically sample about it? maybe powershell... – Anam