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; } }