public void CreateStoredProcedureIfNotExistsQueryShouldGiveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var subquery = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Select(). From("test"). IfExists(). WithCondition(). Where(IGNConditionWithParameter.FromConfig("name", Enums.IGNSqlCondition.Eq, 0)). Go(); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Create(). StoredProcedure("sp_test", subquery, new List <IGNParameter>() { IGNParameter.FromConfig(0, typeof(string), 255) }). IfNotExists(). Go(); var expected = "CREATE PROCEDURE [sp_test] @p0 NVARCHAR(255)\nAS\nSELECT * FROM [test] WHERE [name] = @p0 \nGO \nGO"; Assert.AreEqual(expected, query.ToString()); }
public DataTable ReadData(IGNQueriable query) { if (query.CanExecute) { return(ReadDataWithParameters(query, query.ParamValues)); } return(null); }
private void SetStoresProcedureExists(string name, IGNQueriable queriable, ExistsEnum existsFunc) { var checkQuery = $"SELECT * FROM [INFORMATION_SCHEMA].[ROUTINES] WHERE [ROUTINE_NAME] = '{name}'"; var query = IGNQueriable.FromQueryString(checkQuery, this.email, this); var result = ReadDataWithParameters(query, new List <IGNParameterValue>()); IGNQueriable.SetExists(result.Rows.Count > 0, queriable); IGNQueriable.SetCanExecute(existsFunc, queriable); }
private void SetViewExists(string name, IGNQueriable queriable, ExistsEnum existsFunc) { var checkQuery = $"SELECT * FROM `INFORMATION_SCHEMA`.`VIEWS` WHERE `TABLE_SCHEMA` = database() AND `TABLE_NAME` = '{name}'"; var query = IGNQueriable.FromQueryString(checkQuery, this.email, this); var result = ReadDataWithParameters(query, new List <IGNParameterValue>()); IGNQueriable.SetExists(result.Rows.Count > 0, queriable); IGNQueriable.SetCanExecute(existsFunc, queriable); }
private void SetIndexExists(string name, string table, IGNQueriable queriable, ExistsEnum existsFunc) { var checkQuery = $"SELECT * FROM sysindexes WHERE name='{name}'"; var query = IGNQueriable.FromQueryString(checkQuery, this.email, this); var result = ReadDataWithParameters(query, new List <IGNParameterValue>()); IGNQueriable.SetExists(result.Rows.Count > 0, queriable); IGNQueriable.SetCanExecute(existsFunc, queriable); }
private void SetColumnExists(string name, string table, IGNQueriable queriable, ExistsEnum existsFunc) { var checkQuery = $"SELECT * FROM [INFORMATION_SCHEMA].[COLUMNS] WHERE [TABLE_CATALOG] = DB_NAME() AND [TABLE_NAME] = '{table}' AND [COLUMN_NAME] = '{name}'"; var query = IGNQueriable.FromQueryString(checkQuery, this.email, this); var result = ReadDataWithParameters(query, new List <IGNParameterValue>()); IGNQueriable.SetExists(result.Rows.Count > 0, queriable); IGNQueriable.SetCanExecute(existsFunc, queriable); }
public void ADeleteAllUsers() { var dataProvider = new MySqlDataDriver("*****@*****.**"); var query = IGNQueriable.Begin("*****@*****.**", dataProvider). Delete(). From("ignusers"). Go(); dataProvider.Execute(query); }
public void CDeleteTable() { var dataProvider = new MySqlDataDriver("*****@*****.**"); var query = IGNQueriable.Begin("*****@*****.**", dataProvider). Drop(). Table("ignusers"). IfExists(). Go(); dataProvider.Execute(query); }
public void UseQueryShouldGiveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).Use("testauth").Go(); var expected = "USE [testauth] \nGO"; Assert.AreEqual(expected, query.ToString()); }
public void ExecuteWithParameters(IGNQueriable query, IEnumerable <IGNParameterValue> args) { if (query.CanExecute) { using (var connection = OpenConnection()) { var dbc = PrepareDbCommand(query.ToString(), connection); AddParameters(dbc, args); dbc.ExecuteNonQuery(); } } }
public void CreateViewIfNotExistsQueryShouldGiveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Create(). View("dm_test", IGNQueriable.FromQueryString("SELECT * FROM test WHERE name = 'test'", "*****@*****.**", dbDriverMock.Object)). IfNotExists(). Go(); var expected = "CREATE VIEW [dm_test]\nAS \nSELECT * FROM test WHERE name = 'test' \nGO"; Assert.AreEqual(expected, query.ToString()); }
public void CreateDatabaseQueryShouldGiveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Create(). Database("testdb"). IfNotExists(). Go(); var expected = "CREATE DATABASE [testdb] \nGO"; Assert.AreEqual(expected, query.ToString()); }
public void BAlterTableIfExists() { var dataProvider = new MySqlDataDriver("*****@*****.**"); var query = IGNQueriable.Begin("*****@*****.**", dataProvider). Alter(). Table("ignusers"). IfExists(). Add(). Column(TableColumnConfiguration.FromConfig("test1", typeof(string), 25, false, false, false, string.Empty)). IfNotExists(). Add(). Column(TableColumnConfiguration.FromConfig("test2", typeof(string), 25, false, false, false, string.Empty)). IfNotExists(). Go(); dataProvider.Execute(query); query = IGNQueriable.Begin("*****@*****.**", dataProvider). Alter(). Table("ignusers"). IfExists(). Alter(). Column(TableColumnConfiguration.FromConfig("test1", typeof(string), 50, false, false, false, string.Empty)). IfExists(). Go(); dataProvider.Execute(query); query = IGNQueriable.Begin("*****@*****.**", dataProvider). Alter(). Table("ignusers"). IfExists(). Drop("test1"). IfExists(). Drop("test2"). IfExists(). Go(); dataProvider.Execute(query); var dataDriver = new MySqlDataDriver("*****@*****.**"); var column = TableColumnConfiguration.FromConfig("createdOn", typeof(DateTime), 0, false, true, false, string.Empty); var altquery = IGNQueriable.Begin("*****@*****.**", dataDriver). Alter(). Table("ignusers"). IfExists(). Add(). Column(column). IfNotExists(). Go(); dataDriver.Execute(altquery); }
public void CreateTableQueryShouldGiveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); dbDriverMock.Setup(x => x.GetDbAutoGenFor(It.IsAny <Type>(), It.IsAny <int>())).Returns(" IDENTITY(1,1)"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object).Create().Table("test", new List <TableColumnConfiguration>() { TableColumnConfiguration.FromConfig("id", typeof(long), 0, true, true, true, null), TableColumnConfiguration.FromConfig("name", typeof(string), 255, false, false, false, null) }).IfNotExists().Go(); var expected = "CREATE TABLE [test]([id] BIGINT NOT NULL IDENTITY(1,1),[name] NVARCHAR(255) NULL,CONSTRAINT PK_test PRIMARY KEY([id])) \nGO"; Assert.AreEqual(expected, query.ToString()); }
public void UpdateQueryShouldHaveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Update(). Table("users"). IfExists(). SetParametrizedWithCondition("loggedInDateTime", 0). Where(IGNConditionWithParameter.FromConfig("Id", Enums.IGNSqlCondition.Eq, 1)). Go(); var expected = "UPDATE [users] SET [loggedInDateTime] = @p0 WHERE [Id] = @p1 \nGO"; Assert.AreEqual(expected, query.ToString()); }
public void SelectDistinctQueryShouldHaveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Select(new List <string> { "test.id", "test2.test", "test.test" }, true). From("test"). IfExists(). Join("test", "test2", "test2id", "id", true). Go(); var expected = "SELECT DISTINCT [test].[id],[test2].[test],[test].[test] FROM [test] INNER JOIN [test2] ON [test].[test2id] = [test2].[id] \nGO"; Assert.AreEqual(expected, query.ToString()); }
public DataTable ReadDataWithParameters(IGNQueriable query, IEnumerable <IGNParameterValue> args) { DataTable result = null; if (query.CanExecute) { using (var connection = OpenConnection()) { var dbc = PrepareDbCommand(query.ToString(), connection); AddParameters(dbc, args); using (var dbReader = dbc.ExecuteReader()) { result = InitDataTable(dbReader); } } } return(result); }
public void AlterColumnsQueryShouldGiveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Alter(). Table("test"). IfExists(). Alter(). Column(TableColumnConfiguration.FromConfig("test1", typeof(string), 25, false, false, false, "")). IfExists(). Go(); var expected = "ALTER TABLE [test] ALTER COLUMN [test1] NVARCHAR(25) NULL \nGO"; Assert.AreEqual(expected, query.ToString()); }
public void ACreateTableIfNotExists() { var dataProvider = new MySqlDataDriver("*****@*****.**"); var paramList = new List <TableColumnConfiguration>() { TableColumnConfiguration.FromConfig("id", typeof(long), 0, true, true, true, string.Empty), TableColumnConfiguration.FromConfig("userId", typeof(long), 0, true, false, false, null), TableColumnConfiguration.FromConfig("mail", typeof(string), 254, true, false, false, string.Empty), TableColumnConfiguration.FromConfig("active", typeof(bool), 0, true, false, false, true) }; var query = IGNQueriable.Begin("*****@*****.**", dataProvider). Create(). Table("ignusers", paramList). IfNotExists(). Go(); dataProvider.Execute(query); }
public void AlterDropColumnsQueryShouldGiveCorrectSyntax() { var dbDriverMock = new Mock <IDataDriver>(); dbDriverMock.Setup(x => x.Dialect).Returns(Enums.DialectEnum.MSSQL); dbDriverMock.Setup(x => x.GoTerminator()).Returns("\nGO"); var query = IGNQueriable.Begin("*****@*****.**", dbDriverMock.Object). Alter(). Table("test"). IfExists(). Drop("test1"). IfExists(). Drop("test2"). IfExists(). Go(); var expected = "ALTER TABLE [test] DROP COLUMN [test1], [test2] \nGO"; Assert.AreEqual(expected, query.ToString()); }
public void BCreateUserIfNotExists() { var dataProvider = new MySqlDataDriver("*****@*****.**"); var query = IGNQueriable.Begin("*****@*****.**", dataProvider). Insert(). Into("ignusers", new List <string>() { "mail", "userId" }). IfExists(). ValuesWithParams(new List <int> { 0, 1 }). Go(); dataProvider.ExecuteWithParameters(query, new List <IGNParameterValue> { IGNParameterValue.FromConfig(0, "*****@*****.**"), IGNParameterValue.FromConfig(1, -1) }); }
public void BCreateStoredProcedureIfNotExists() { var dataProvider = new MySqlDataDriver("*****@*****.**"); var query = IGNQueriable.Begin("*****@*****.**", dataProvider). Drop(). StoredProcedure("testProc"). IfExists(). Go(); dataProvider.Execute(query); var spQuery = IGNQueriable.Begin("*****@*****.**", dataProvider). Select(). From("ignusers"). IfExists(). Go(); query = IGNQueriable.Begin("*****@*****.**", dataProvider). Create(). StoredProcedure("testProc", spQuery). IfNotExists(). Go(); dataProvider.Execute(query); }
public override void IfViewNotExists(string name, IGNQueriable queriable) { SetViewExists(name, queriable, ExistsEnum.NotExists); }
public override void IfTableNotExists(string name, IGNQueriable queriable) { SetTableExists(name, queriable, ExistsEnum.NotExists); }
public override void IfStoredProcedureNotExists(string name, IGNQueriable queriable) { SetStoresProcedureExists(name, queriable, ExistsEnum.NotExists); }
public override void IfIndexNotExists(string name, string table, IGNQueriable queriable) { SetIndexExists(name, table, queriable, ExistsEnum.NotExists); }
public override void IfDatabaseExists(string name, IGNQueriable queriable) { SetDatabaseExists(name, queriable, ExistsEnum.Exists); }
public override void IfColumnNotExists(string name, string table, IGNQueriable queriable) { SetColumnExists(name, table, queriable, ExistsEnum.NotExists); }
public void Execute(IGNQueriable query) { ExecuteWithParameters(query, query.ParamValues); }
public virtual void IfIndexExists(string name, string table, IGNQueriable queriable) { throw new NotImplementedException(this.nonSpecDriverErr); }