我正在从Source_Test数据库Destination_Test数据库复制表。
使用Microsoft.SqlServer.Management.Smo.Transfer
。
复制所有表格 from Source_Test to Destination_Test
但是我想要具有模式“ TestSchema”的表。
主要问题是,它复制了具有的所有表dbo schema
。即使我设置this.CopyAllTables = false;
它仍然复制它。如何限制它。我试过以下:
public class CopyTable : Transfer, ISchemaCopy
{
private void CopyTables()
{
this.CopyAllTables = false;
var server = new Server(new ServerConnection(connection));
var script = this.ScriptTransfer().Cast<string>();
}
}
我认为以下代码可以实现您想要实现的目标。如果通过提供SchemaName设置仅从一个模式复制表,它将仅添加该模式的表进行复制。
关键是建立要复制的表的集合,并将该集合显式添加到ObjectList。不要忘记将ServerName和DatabaseName更改为真实的名称。
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;
/* Needs references to:
*
* System.Data
* Microsoft.SqlServer.ConnectionInfo
* Microsoft.SqlServer.Management.Sdk.Sfc
* Microsoft.SqlServer.Smo
* Microsoft.SqlServer.SmoExtended
*/
namespace CopyTablesExample
{
class Program
{
public class CopyTables : Transfer
{
public string SchemaName { get; set; }
public string DatabaseName { get; set; }
public string ServerName { get; set; }
public IEnumerable<string> GetScript()
{
//This is what we will return
IEnumerable<string> scriptStrings = null;
if (this.DatabaseName == null) {
throw new Exception("DatabaseName property not set.");
}
if (this.ServerName == null) {
throw new Exception("ServerName property not set.");
}
var server = new Server(new ServerConnection(ServerName));
this.Database = server.Databases[this.DatabaseName];
//Turn off all objects. Below we will start turning on what we want.
//You may wish to add more object types.
this.CopyAllObjects = false;
if (this.SchemaName == null) {
//No schema means all tables
this.CopyAllTables = true;
}
else {
//A specific schema means all tables from that schema
this.CopyAllTables = false;
//We only want to copy tables in a specific schema.
List<Table> tablesToCopy = new List<Table>();
foreach (Table t in this.Database.Tables) {
if (t.Schema.Equals(this.SchemaName)) {
tablesToCopy.Add(t);
}
}
//Add specifically the tables we want which are from the schema we want
this.ObjectList.AddRange(tablesToCopy);
}
try {
scriptStrings = this.ScriptTransfer().Cast<string>();
}
catch (Exception ex) {
Console.WriteLine("We got an exception.");
throw;
}
return scriptStrings;
}
}
static void Main(String[] Args)
{
//Only set a SchemaName in line below, when you want to restrict yourself to copying that schema's tables.
CopyTables ct = new CopyTables() { ServerName = "xyz", DatabaseName = "abc", SchemaName = "junk" } ;
var copyTablesScript = ct.GetScript();
//For validation, display the script as generated
foreach (var item in copyTablesScript) {
Console.WriteLine(item);
}
var discard = Console.ReadKey();
}
}
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句