public void ExecuteNonQuery(String query, List <SqlParameter> paramerters = null, SqlConnection cnn = null)
{
if (paramerters == null)
{
paramerters = new List <SqlParameter>();
}
switch (DBType)
{
case DatabaseType.SqlCompactEdition35:
SQLCompactEdition35.ExecuteNonQuery(query);
break;
case DatabaseType.SqlServer:
if (cnn != null)
{
SQLServer.ExecuteNonQuery(cnn, query, paramerters);
}
else
{
SQLServer.ExecuteNonQuery(query, paramerters);
}
break;
}
}
public DataTable ExecuteDataTable(String query, List <SqlParameter> paramerters = null, SqlConnection cnn = null)
{
if (paramerters == null)
{
paramerters = new List <SqlParameter>();
}
switch (DBType)
{
case DatabaseType.SqlCompactEdition35:
return(SQLCompactEdition35.ExecuteDataTable(query));
case DatabaseType.SqlServer:
if (cnn != null)
{
return(SQLServer.ExecuteDataTable(cnn, query, paramerters));
}
else
{
return(SQLServer.ExecuteDataTable(query, paramerters));
}
default:
return(null);
}
}
public void Initialize()
{
switch (DBType)
{
case DatabaseType.SqlServer:
//Do nothing database should be created by the provided MSSQLGenerate.sql file
break;
case DatabaseType.SqlCompactEdition35:
//TODO: THE GENERATION QUERIES ARE OUTDATED!!! UPDATE FROM THE MSSQL SCHEMA
String dbWorkingCopy = ConfigurationManager.AppSettings["SQLCE3.5"];
if (!File.Exists(dbWorkingCopy))
{
File.Delete(dbWorkingCopy);
SqlCeEngine dbInstance = new SqlCeEngine(ConnectionString);
dbInstance.CreateDatabase();
List <String> sqlCEGenerateQueries = new List <string>();
sqlCEGenerateQueries.Add(@"CREATE TABLE [Product] (
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[Title] [nvarchar](1000) NULL,
[Description] [ntext] NULL
);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [Review] (
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[ProductId] [int] NOT NULL,
[Title] [nvarchar](1000) NULL
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [Review] ADD CONSTRAINT [FK_Review_Product] FOREIGN KEY([ProductId]) REFERENCES [Product] ([Id]);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [Sentence] (
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[ReviewId] [int] NOT NULL,
[Text] [ntext] NULL,
[TextPOS] [ntext] NULL,
[ManualResults] [ntext] NULL
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [Sentence] ADD CONSTRAINT [FK_Sentence_Review] FOREIGN KEY([ReviewId]) REFERENCES [Review] ([Id]);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [Term] (
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[Text] [nvarchar](500) NULL
);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [Aspect] (
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[Text] [nvarchar](1500) NULL
);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [AspectTermMapping](
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[AspectId] [int] NOT NULL,
[TermId] [int] NOT NULL
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [AspectTermMapping] ADD CONSTRAINT [FK_AspectTermMapping_Aspect] FOREIGN KEY([AspectId]) REFERENCES [Aspect] ([Id]);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [AspectTermMapping] ADD CONSTRAINT [FK_AspectTermMapping_Term] FOREIGN KEY([TermId]) REFERENCES [Term] ([Id]);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [SentenceAspectMapping] (
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[SentenceId] [int] NOT NULL,
[AspectId] [int] NOT NULL,
[IsGenerated] [bit] NOT NULL DEFAULT(0)
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [SentenceAspectMapping] ADD CONSTRAINT [FK_SentenceAspectMapping_Sentence] FOREIGN KEY([SentenceId]) REFERENCES [Sentence] ([Id]);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [SentenceAspectMapping] ADD CONSTRAINT [FK_SentenceAspectMapping_Aspect] FOREIGN KEY([AspectId]) REFERENCES [Aspect] ([Id]);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [SentenceAspectTermMapping](
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[SentenceAspectId] [int] NOT NULL,
[AspectTermId] [int] NOT NULL,
[TermOrder] [int] NULL
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [SentenceAspectTermMapping] ADD CONSTRAINT [FK_SentenceAspectTermMapping_SentenceAspectMapping] FOREIGN KEY([SentenceAspectId]) REFERENCES [SentenceAspectMapping] ([Id]);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [SentenceAspectTermMapping] ADD CONSTRAINT [FK_SentenceAspectTermMapping_AspectTermMapping] FOREIGN KEY([AspectTermId]) REFERENCES [AspectTermMapping] ([Id]);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [AspectProductMapping](
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[AspectId] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[AspectText] [nvarchar](1500) NULL,
[Frequency] [int] NOT NULL DEFAULT(0),
[Compactness] [int] DEFAULT(0),
[PSupport] [int] DEFAULT(0),
[IsPruned] [bit] NOT NULL DEFAULT(0)
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [AspectProductMapping] ADD CONSTRAINT [FK_AspectProductMapping_Aspect] FOREIGN KEY([AspectId]) REFERENCES [Aspect] ([Id]);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [AspectProductMapping] ADD CONSTRAINT [FK_AspectProductMapping_Product] FOREIGN KEY([ProductId]) REFERENCES [Product] ([Id]);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [ManualAspectProductMapping](
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[AspectId] [int] NOT NULL,
[ProductId] [int] NOT NULL,
[AspectText] [nvarchar](1500) NULL,
[Frequency] [int] NOT NULL DEFAULT(1)
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [ManualAspectProductMapping] ADD CONSTRAINT [FK_ManualAspectProductMapping_Aspect] FOREIGN KEY([AspectId]) REFERENCES [Aspect] ([Id]);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [ManualAspectProductMapping] ADD CONSTRAINT [FK_ManualAspectProductMapping_Product] FOREIGN KEY([ProductId]) REFERENCES [Product] ([Id]);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [OpinionWord] (
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[Text] [nvarchar](500) NULL
);");
sqlCEGenerateQueries.Add(@"CREATE TABLE [OpinionWordProductMapping](
[Id] [int] PRIMARY KEY IDENTITY(1,1),
[OpinionWordId] [int] NOT NULL,
[ProductId] [int] NOT NULL
);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [OpinionWordProductMapping] ADD CONSTRAINT [FK_OpinionWordProductMapping_OpinionWord] FOREIGN KEY([OpinionWordId]) REFERENCES [OpinionWord] ([Id]);");
sqlCEGenerateQueries.Add(@"ALTER TABLE [OpinionWordProductMapping] ADD CONSTRAINT [FK_OpinionWordProductMapping_Product] FOREIGN KEY([ProductId]) REFERENCES [Product] ([Id]);");
for (int i = 0; i < sqlCEGenerateQueries.Count; i++)
{
SQLCompactEdition35.ExecuteNonQuery(sqlCEGenerateQueries[i]);
}
}
break;
}
}
