public async Task <IEnumerable <FullTextCatalog> > SelectCatalogsAsync()
{
IList <FullTextCatalog> output = null;
using (var context = _dbContext)
{
output = await context.ExecuteReaderAsync(
CommandType.Text,
BySql,
async reader =>
{
if ((reader != null) && (reader.HasRows))
{
output = new List <FullTextCatalog>();
while (await reader.ReadAsync())
{
var catalog = new FullTextCatalog();
catalog.PopulateModel(reader);
output.Add(catalog);
}
}
return(output);
});
}
return(output);
}
public void TestSetup()
{
//start with default options
string connectionString = ConfigurationManager.AppSettings["ConnectionString"];
string[] options = { connectionString };
RunOptions.Current.Logger = new TestLogger();
RunOptions.Current.Init(options);
SqlConnection connection = new SqlConnection(connectionString);
ServerConnection serverConnection = new ServerConnection(connection);
Server server = new Server(serverConnection);
Microsoft.SqlServer.Management.Smo.Database database = server.Databases[connection.Database];
if (database == null)
{
throw new ApplicationException("Database " + connection.Database + " not found.");
}
server.SetDefaultInitFields(typeof(View), true);
database.PrefetchObjects(typeof(View));
for (int i = database.Views.Count - 1; i >= 0; i--)
{
View view = database.Views[i];
if (view.IsSystemObject)
{
continue;
}
view.Drop();
}
server.SetDefaultInitFields(typeof(Table), true);
database.PrefetchObjects(typeof(Table));
foreach (Table table in database.Tables)
{
if (table.IsSystemObject)
{
continue;
}
for (int i = table.ForeignKeys.Count - 1; i >= 0; i--)
{
table.ForeignKeys[i].Drop();
}
}
for (int i = database.Tables.Count - 1; i >= 0; i--)
{
Table table = database.Tables[i];
if (table.IsSystemObject)
{
continue;
}
table.Drop();
}
server.SetDefaultInitFields(typeof(StoredProcedure), true);
database.PrefetchObjects(typeof(StoredProcedure));
for (int i = database.StoredProcedures.Count - 1; i >= 0; i--)
{
StoredProcedure procedure = database.StoredProcedures[i];
if (procedure.IsSystemObject)
{
continue;
}
procedure.Drop();
}
server.SetDefaultInitFields(typeof(UserDefinedFunction), true);
database.PrefetchObjects(typeof(UserDefinedFunction));
for (int i = database.UserDefinedFunctions.Count - 1; i >= 0; i--)
{
UserDefinedFunction function = database.UserDefinedFunctions[i];
if (function.IsSystemObject)
{
continue;
}
function.Drop();
}
server.SetDefaultInitFields(typeof(UserDefinedTableType), true);
database.PrefetchObjects(typeof(UserDefinedTableType));
for (int i = database.UserDefinedTableTypes.Count - 1; i >= 0; i--)
{
UserDefinedTableType tableType = database.UserDefinedTableTypes[i];
if (!tableType.IsUserDefined)
{
continue;
}
tableType.Drop();
}
server.SetDefaultInitFields(typeof(FullTextCatalog), true);
for (int i = database.FullTextCatalogs.Count - 1; i >= 0; i--)
{
FullTextCatalog catalog = database.FullTextCatalogs[i];
catalog.Drop();
}
}
/// <summary>
/// This method will create a full-text index on a table with an associated stoplist
/// </summary>
public static void CreateFullTextIndex()
{
Console.WriteLine("Running CreateFullTextIndex Sample...");
try
{
Server server = new Server(".");
server.ConnectionContext.Connect();
Console.WriteLine("Connected to '{0}' server", server.Name);
Database db = new Database(server, "iFTSSampleDB2");
db.Create();
Console.WriteLine("Database '{0}' created", db.Name);
// Create a default full-text catalog for the database
FullTextCatalog ftcat = new FullTextCatalog(db, "ftcatalog");
ftcat.IsDefault = true;
ftcat.Create();
Console.WriteLine("FullTextCatalog '{0}' created", ftcat.Name);
// Add 2 columns to the table
Table tab = new Table(db, "tab");
tab.Columns.Add(new Column(tab, "col_fti", DataType.NVarChar(1000)));
Column col = new Column(tab, "col_unique", DataType.Int);
col.Nullable = false;
tab.Columns.Add(col);
// Create a table with a unique index on it
Index idxUnique = new Index(tab, "tab_unique_idx");
idxUnique.IndexKeyType = IndexKeyType.DriUniqueKey;
idxUnique.IndexedColumns.Add(new IndexedColumn(idxUnique, col.Name));
tab.Indexes.Add(idxUnique);
tab.Create();
Console.WriteLine("Table '{0}' created", tab.Name);
// Add a column to the full-text index and associate it with a unique index
FullTextIndex fti = new FullTextIndex(tab);
fti.IndexedColumns.Add(new FullTextIndexColumn(fti, "col_fti"));
fti.UniqueIndexName = idxUnique.Name;
string stopword = "goodbye";
string language = "English";
// Create an empty full-text stoplist
FullTextStopList stoplist = new FullTextStopList(db, "sampleStoplist");
stoplist.Create();
Console.WriteLine("FullTextStoplist '{0}' created", stoplist.Name);
// Add a stopword to the full-text stoplist
stoplist.AddStopWord(stopword, language);
Console.WriteLine("Stopword '{0}' added to Stoplist '{1}'", stopword, stoplist.Name);
fti.StopListName = "sampleStoplist";
// Create the full-text index and associate the full-text stoplist with it
fti.Create();
Console.WriteLine("FullTextIndex on Table '{0}' created", tab.Name);
fti.Drop();
Console.WriteLine("FullTextIndex on Table '{0}' dropped", tab.Name);
ftcat.Drop();
Console.WriteLine("FullTextCatalog '{0}' dropped", ftcat.Name);
tab.Drop();
Console.WriteLine("Table '{0}' created", tab.Name);
db.Drop();
Console.WriteLine("Database '{0}' dropped", db.Name);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
Console.WriteLine();
}
}
/// <summary>
/// This method will create a full-text index on a table with an associated stoplist
/// </summary>
public static void CreateFullTextIndex()
{
Console.WriteLine("Running CreateFullTextIndex Sample...");
try
{
Server server = new Server(".");
server.ConnectionContext.Connect();
Console.WriteLine("Connected to '{0}' server", server.Name);
Database db = new Database(server, "iFTSSampleDB2");
db.Create();
Console.WriteLine("Database '{0}' created", db.Name);
// Create a default full-text catalog for the database
FullTextCatalog ftcat = new FullTextCatalog(db, "ftcatalog");
ftcat.IsDefault = true;
ftcat.Create();
Console.WriteLine("FullTextCatalog '{0}' created", ftcat.Name);
// Add 2 columns to the table
Table tab = new Table(db, "tab");
tab.Columns.Add(new Column(tab, "col_fti", DataType.NVarChar(1000)));
Column col = new Column(tab, "col_unique", DataType.Int);
col.Nullable = false;
tab.Columns.Add(col);
// Create a table with a unique index on it
Index idxUnique = new Index(tab, "tab_unique_idx");
idxUnique.IndexKeyType = IndexKeyType.DriUniqueKey;
idxUnique.IndexedColumns.Add(new IndexedColumn(idxUnique, col.Name));
tab.Indexes.Add(idxUnique);
tab.Create();
Console.WriteLine("Table '{0}' created", tab.Name);
// Add a column to the full-text index and associate it with a unique index
FullTextIndex fti = new FullTextIndex(tab);
fti.IndexedColumns.Add(new FullTextIndexColumn(fti, "col_fti"));
fti.UniqueIndexName = idxUnique.Name;
string stopword = "goodbye";
string language = "English";
// Create an empty full-text stoplist
FullTextStopList stoplist = new FullTextStopList(db, "sampleStoplist");
stoplist.Create();
Console.WriteLine("FullTextStoplist '{0}' created", stoplist.Name);
// Add a stopword to the full-text stoplist
stoplist.AddStopWord(stopword, language);
Console.WriteLine("Stopword '{0}' added to Stoplist '{1}'", stopword, stoplist.Name);
fti.StopListName = "sampleStoplist";
// Create the full-text index and associate the full-text stoplist with it
fti.Create();
Console.WriteLine("FullTextIndex on Table '{0}' created", tab.Name);
fti.Drop();
Console.WriteLine("FullTextIndex on Table '{0}' dropped", tab.Name);
ftcat.Drop();
Console.WriteLine("FullTextCatalog '{0}' dropped", ftcat.Name);
tab.Drop();
Console.WriteLine("Table '{0}' created", tab.Name);
db.Drop();
Console.WriteLine("Database '{0}' dropped", db.Name);
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
finally
{
Console.WriteLine();
}
}
