public NIntegrate.ServiceModel.Configuration.WcfService GetWcfService(string serviceType, string serverName, string loadBalancePath) { var getWcfService = new GetWcfService(); var criteria = getWcfService.CreateSprocCriteria( getWcfService.ServiceType == serviceType, getWcfService.ServerName == serverName, getWcfService.LoadBalancePath == loadBalancePath); using (var cmd = _commandfactory.CreateCommand(criteria, false)) { using (var conn = cmd.Connection) { conn.Open(); var rdr = cmd.ExecuteReader(); var service = new WcfService { ServiceType = serviceType }; var endpointList = new List <WcfServiceEndpoint>(); while (rdr.Read()) { //int ordAddress = rdr.GetOrdinal("Address"); //int ordBindingXml = rdr.GetOrdinal("BindingXml"); //int ordBindingTypeCode = rdr.GetOrdinal("BindingTypeCode"); //int ordEndpointBehaviorXml = rdr.GetOrdinal("EndpointBehaviorXml"); //int ordIdentityXml = rdr.GetOrdinal("IdentityXml"); //int ordServiceContractType = rdr.GetOrdinal("ServiceContractType"); //var endpoint = new WcfServiceEndpoint(); //endpoint.Address = rdr.GetString(ordAddress); //endpoint.BindingXml = new BindingXml(rdr.GetString(ordBindingTypeCode), rdr.GetString(ordBindingXml)); //endpoint.EndpointBehaviorXml = rdr.IsDBNull(ordEndpointBehaviorXml) ? null : new EndpointBehaviorXml(rdr.GetString(ordEndpointBehaviorXml)); //endpoint.IdentityXml = rdr.IsDBNull(ordIdentityXml) ? null : new IdentityXml(rdr.GetString(ordIdentityXml)); //endpoint.ServiceContractType = rdr.GetString(ordServiceContractType); var dataReaderToEndpointMapper = _mapperFac.GetMapper <IDataReader, WcfEndpoint>(); var serviceEndpointMapper = _mapperFac.GetMapper <WcfEndpoint, WcfServiceEndpoint>(); var endpoint = serviceEndpointMapper(dataReaderToEndpointMapper(rdr)); endpointList.Add(endpoint); } service.Endpoints = endpointList.ToArray(); return(service); } } }
public DataTable Query(QueryCriteria criteria) { var cmd = _cmdFac.CreateCommand(criteria, false); using (var conn = cmd.Connection) { if (conn.State != ConnectionState.Open) { conn.Open(); } var result = new DataTable(criteria.TableName) { Locale = CultureInfo.InvariantCulture }; var adapter = _cmdFac.GetDbProviderFactory(criteria).CreateDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill(result); return(result); } }
public void TestBinaryColumn() { var cmdFac = new QueryCommandFactory(); var table = new TestTable2(); var criteria = table.Insert(table.Data.Set(new byte[] { 0, 1, 2 })).Where(table.ID == 123); var cmd = cmdFac.CreateCommand(criteria, false); Assert.AreEqual("@p1", cmd.Parameters[0].ParameterName); Assert.AreEqual(DbType.Binary, cmd.Parameters[0].DbType); Assert.AreEqual(1, (cmd.Parameters[0].Value as byte[])[1]); Assert.AreEqual("@p2", cmd.Parameters[1].ParameterName); Assert.AreEqual(123, cmd.Parameters[1].Value); }
public void TestOracleQueryCommandFactory() { var table = new OracleJobTable(); var criteria = table.Select(); var fac = new QueryCommandFactory(); criteria.SortBy(table.JOB_ID, true).ThenSortBy(table.JOB_TITLE, false); criteria.And(table.JOB_ID == null).Or(table.JOB_TITLE.Like("test")); var predefinedColumnsCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("SELECT \"JOB_ID\", \"JOB_TITLE\", \"MIN_SALARY\", \"MAX_SALARY\" FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p1 ORDER BY \"JOB_ID\" DESC, \"JOB_TITLE\"", predefinedColumnsCmd.CommandText); Assert.AreEqual(1, predefinedColumnsCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p1", fac.CreateCommand(criteria, true).CommandText); criteria.SetMaxResults(10); criteria.Select((table.JOB_ID + 1).As("ID"), table.JOB_TITLE.As("Name")); var noPagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("SELECT \"__T\".\"ID\", \"__T\".\"Name\" FROM (SELECT \"JOB_ID\" + :p1 AS \"ID\", \"JOB_TITLE\" AS \"Name\", ROW_NUMBER() OVER (ORDER BY \"JOB_ID\" DESC, \"JOB_TITLE\") AS \"__Pos\" FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p2) \"__T\" WHERE \"__T\".\"__Pos\" > 0 AND \"__T\".\"__Pos\" <= 10", noPagingCmd.CommandText); Assert.AreEqual(2, noPagingCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p1", fac.CreateCommand(criteria, true).CommandText); criteria.SetSkipResults(10); var pagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("SELECT \"__T\".\"ID\", \"__T\".\"Name\" FROM (SELECT \"JOB_ID\" + :p1 AS \"ID\", \"JOB_TITLE\" AS \"Name\", ROW_NUMBER() OVER (ORDER BY \"JOB_ID\" DESC, \"JOB_TITLE\") AS \"__Pos\" FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p2) \"__T\" WHERE \"__T\".\"__Pos\" > 10 AND \"__T\".\"__Pos\" <= 20", pagingCmd.CommandText); Assert.AreEqual(2, pagingCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p1", fac.CreateCommand(criteria, true).CommandText); criteria.SetIsDistinct(true); pagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("SELECT \"__T\".\"ID\", \"__T\".\"Name\" FROM (SELECT DISTINCT \"JOB_ID\" + :p1 AS \"ID\", \"JOB_TITLE\" AS \"Name\", ROW_NUMBER() OVER (ORDER BY \"JOB_ID\" DESC, \"JOB_TITLE\") AS \"__Pos\" FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p2) \"__T\" WHERE \"__T\".\"__Pos\" > 10 AND \"__T\".\"__Pos\" <= 20", pagingCmd.CommandText); Assert.AreEqual(2, pagingCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM (SELECT DISTINCT \"JOB_ID\" + :p1 AS \"ID\", \"JOB_TITLE\" AS \"Name\" FROM \"HR\".\"JOBS\" WHERE \"JOB_ID\" IS NULL OR \"JOB_TITLE\" LIKE :p2) \"__T\"", fac.CreateCommand(criteria, true).CommandText); Assert.AreEqual(1, pagingCmd.Parameters[0].Value); Assert.AreEqual("test", pagingCmd.Parameters[1].Value); pagingCmd.Parameters[0].Value = null; pagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual(1, pagingCmd.Parameters[0].Value); Assert.AreEqual("test", pagingCmd.Parameters[1].Value); Assert.AreNotEqual(pagingCmd, fac.CreateCommand(criteria, false)); }
public AppointmentCollection Query(QueryCriteria criteria) { if (criteria == null) { throw new ArgumentNullException("criteria"); } var cmd = _cmdFac.CreateCommand(criteria, false); using (var conn = cmd.Connection) { if (conn.State != ConnectionState.Open) { conn.Open(); } using (var rdr = cmd.ExecuteReader()) { var mapper = _mapperFac.GetMapper <IDataReader, List <DataTypes.Appointment> >(); var list = mapper(rdr); return(new AppointmentCollection(list)); } } }
public void TestSqlQueryCommandFactory() { var table = new TestTable(); var criteria = table.Select(); var fac = new QueryCommandFactory(); criteria.SetMaxResults(10); criteria.SortBy(table.Int32Column, true).ThenSortBy(table.StringColumn, false); criteria.And(table.Int32Column == null).Or(table.StringColumn.Like("test")); var predefinedColumnsCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("SELECT TOP 10 [BooleanColumn], [ByteColumn], [Int16Column], [Int32Column], [Int64Column], [DateTimeColumn], [StringColumn], [GuidColumn], [DoubleColumn], [DecimalColumn] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1 ORDER BY [Int32Column] DESC, [StringColumn]", predefinedColumnsCmd.CommandText); Assert.AreEqual(1, predefinedColumnsCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1", fac.CreateCommand(criteria, true).CommandText); criteria.Select((table.Int32Column + 1).As("ID"), (table.StringColumn.As("Name"))); var noPagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("SELECT TOP 10 [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2 ORDER BY [Int32Column] DESC, [StringColumn]", noPagingCmd.CommandText); Assert.AreEqual(2, noPagingCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1", fac.CreateCommand(criteria, true).CommandText); criteria.SetSkipResults(10); var pagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("WITH [__T] AS (SELECT TOP 20 [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name], ROW_NUMBER() OVER (ORDER BY [Int32Column] DESC, [StringColumn]) AS [__Pos] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2) SELECT [__T].[ID], [__T].[Name] FROM [__T] WHERE [__T].[__Pos] > 10 AND [__T].[__Pos] <= 20", pagingCmd.CommandText); Assert.AreEqual(2, pagingCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p1", fac.CreateCommand(criteria, true).CommandText); criteria.SetIsDistinct(true); pagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual("WITH [__T] AS (SELECT DISTINCT TOP 20 [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name], ROW_NUMBER() OVER (ORDER BY [Int32Column] DESC, [StringColumn]) AS [__Pos] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2) SELECT [__T].[ID], [__T].[Name] FROM [__T] WHERE [__T].[__Pos] > 10 AND [__T].[__Pos] <= 20", pagingCmd.CommandText); Assert.AreEqual(2, pagingCmd.Parameters.Count); Assert.AreEqual("SELECT COUNT(1) FROM (SELECT DISTINCT [Int32Column] + @p1 AS [ID], [StringColumn] AS [Name] FROM [TestTable] (NOLOCK) WHERE [Int32Column] IS NULL OR [StringColumn] LIKE @p2) [__T]", fac.CreateCommand(criteria, true).CommandText); Assert.AreEqual(1, pagingCmd.Parameters[0].Value); Assert.AreEqual("test", pagingCmd.Parameters[1].Value); pagingCmd.Parameters[0].Value = null; pagingCmd = fac.CreateCommand(criteria, false); Assert.AreEqual(1, pagingCmd.Parameters[0].Value); Assert.AreEqual("test", pagingCmd.Parameters[1].Value); Assert.AreNotEqual(pagingCmd, fac.CreateCommand(criteria, false)); var insertCriteria = table.Insert(table.Int32Column.Set(1), table.DateTimeColumn.Set(DateTime.Now)); var insertCmd = fac.CreateCommand(insertCriteria, false); Assert.AreEqual("INSERT INTO [TestTable] ([Int32Column], [DateTimeColumn]) VALUES (@p1, @p2)", insertCmd.CommandText); Assert.AreEqual(2, insertCmd.Parameters.Count); var updateCriteria = table.Update(table.Int32Column.Set(1), table.DateTimeColumn.Set(DateTime.Now)).Where(table.GuidColumn == Guid.NewGuid()); var updateCmd = fac.CreateCommand(updateCriteria, false); Assert.AreEqual("UPDATE [TestTable] SET [Int32Column] = @p1, [DateTimeColumn] = @p2 WHERE [GuidColumn] = @p3", updateCmd.CommandText); Assert.AreEqual(3, updateCmd.Parameters.Count); var deleteCriteria = table.Delete(table.GuidColumn == Guid.NewGuid()); var deleteCmd = fac.CreateCommand(deleteCriteria, false); Assert.AreEqual("DELETE FROM [TestTable] WHERE [GuidColumn] = @p1", deleteCmd.CommandText); Assert.AreEqual(1, deleteCmd.Parameters.Count); }