locked
sql table dependency in wcf service get error RRS feed

  • Question

  • User-1634604574 posted

    i have this project

    https://github.com/zhyan-ise/wcf-demo

    when i run the project i get this error

    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    System.Configuration.ConnectionStringSettingsCollection.this[string].get returned null.

    here is my table in sql server 

    create table Stocks
    (
    Code varchar(200) null,
    Name varchar(200) null,
    Price decimal(18,0) null
    )
    

    Thursday, May 28, 2020 12:50 PM

All replies

  • User475983607 posted

    i have this project

    https://github.com/zhyan-ise/wcf-demo

    when i run the project i get this error

    System.NullReferenceException: 'Object reference not set to an instance of an object.'

    System.Configuration.ConnectionStringSettingsCollection.this[string].get returned null.

    here is my table in sql server 

    create table Stocks
    (
    Code varchar(200) null,
    Name varchar(200) null,
    Price decimal(18,0) null
    )

    The error is abundantly clear.   

    You coded the connection manager to look for the connectionString key...

     _connectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;

    but the actual key is "con"

      <connectionStrings>
        <add name="con" connectionString="Data Source=DESKTOP-QHRMT92\SQLEXPRESS01;Initial Catalog=LST_Last;Integrated Security=False;User ID=sa3;Password=123" providerName="System.Data.SqlClient" />
      </connectionStrings>

    Honestly, you're been on these forums for a long time and it is very clear that you do not debug your code.  Your coding life will be a lot easier if you learn how to use standard debugging tools.

    Thursday, May 28, 2020 1:53 PM
  • User-1634604574 posted

    now i have this error

    System.Reflection.TargetInvocationException: 'Exception has been thrown by the target of an invocation.'

    inner exception

    UserWithNoPermissionException: User without permissions.

    This exception was originally thrown at this call stack:
    TableDependency.SqlClient.SqlTableDependency<T>.CheckIfUserHasPermissions()
    TableDependency.SqlClient.Base.TableDependency<T>.TableDependency(string, string, string, TableDependency.SqlClient.Base.Abstracts.IModelToTableMapper<T>, TableDependency.SqlClient.Base.Abstracts.IUpdateOfModel<T>, TableDependency.SqlClient.Base.Abstracts.ITableDependencyFilter, TableDependency.SqlClient.Base.Enums.DmlTriggerType, bool)
    TableDependency.SqlClient.SqlTableDependency<T>.SqlTableDependency(string, string, string, TableDependency.SqlClient.Base.Abstracts.IModelToTableMapper<T>, TableDependency.SqlClient.Base.Abstracts.IUpdateOfModel<T>, TableDependency.SqlClient.Base.Abstracts.ITableDependencyFilter, TableDependency.SqlClient.Base.Enums.DmlTriggerType, bool, bool)
    ConsoleApplication.PriceChangeService.PriceTicker.PriceTicker() in PriceTicker.cs

    Friday, May 29, 2020 7:31 AM
  • User475983607 posted

    The error is very clear.  The user does not have proper SQL permissions.  

    The 3rd party TableDependency package documentation clearly and openly explains what permissions are needed or how to override the permissions check using the constructor.  All you have to do is read the reference documentation.  I copied and pasted the relevant section below.  I recommend that you read the entire linked documentation because mostly likely there are other bits of information you need to know.

    https://github.com/christiandelbianco/monitor-table-change-with-sqltabledependency 

    To use notifications, you must be sure to enable Service Broker for the database. To do this run the SQL command:

    ALTER DATABASE MyDatabase SET ENABLE_BROKER

    In case the user specified in the connection string is not database Administratordb owner or neither has db_owner role, please make sure to GRANT the following permissions to your login user:

    • ALTER
    • CONNECT
    • CONTROL
    • CREATE CONTRACT
    • CREATE MESSAGE TYPE
    • CREATE PROCEDURE
    • CREATE QUEUE
    • CREATE SERVICE
    • EXECUTE
    • SELECT
    • SUBSCRIBE QUERY NOTIFICATIONS
    • VIEW DATABASE STATE
    • VIEW DEFINITION

    It is possible skip permissions test done by SqlTableDependency setting executeUserPermissionCheck constructor parameter to false. Nevertheless a SQL server exception will be thrown if user have not sufficient permissions.

    Friday, May 29, 2020 10:34 AM
  • User-1634604574 posted

    i don't know how can do this on sql db

    How Track record table change is done

    SqlTableDependency's record change audit, provides the low-level implementation to receive database record table change notifications creating SQL Server triggers, queues and service broker that immediately notifies your application when a record table change happens.

    Assuming we want to monitor the Customer table content, we create a SqlTableDependency object specifying the Customer table and the following database objects will be generated:

    • Message types
    • Contract
    • Queue
    • Service Broker
    • Trigger on table to be monitored
    • Stored procedure to clean up the created objects in case the application exits abruptly (that is, when the application terminate without disposing the SqlTableDependency object)

    can you do it by video ?

    Sunday, May 31, 2020 5:56 AM
  • User-1634604574 posted

    now i get this error

    System.InvalidOperationException: 'The HttpGetEnabled property of ServiceMetadataBehavior is set to true and the HttpGetUrl property is a relative address, but there is no http base address. Either supply an http base address or set HttpGetUrl to an absolute address.'

    Monday, June 1, 2020 12:00 PM
  • User475983607 posted

    now i get this error

    System.InvalidOperationException: 'The HttpGetEnabled property of ServiceMetadataBehavior is set to true and the HttpGetUrl property is a relative address, but there is no http base address. Either supply an http base address or set HttpGetUrl to an absolute address.'

    The error message can not be any clearer.  You forgot the base address when making service call.  A base address looks like https:\\www.domain.com.

    Troubleshoot and debug your code.

    Monday, June 1, 2020 12:12 PM
  • User-1634604574 posted

    i don't understand of this link https://localhost:8090/PriceTickerService/

    Monday, June 1, 2020 12:36 PM
  • User-1634604574 posted

    can you check this project  https://github.com/zhyan-ise/wcf-demo (name of file ConsoleApplication) on your side?

    Monday, June 1, 2020 1:06 PM
  • User475983607 posted

    i don't understand of this link https://localhost:8090/PriceTickerService/

    The base address is...

    https://localhost:8090

    Monday, June 1, 2020 1:09 PM
  • User-1634604574 posted

    also i get this error

    System.InvalidOperationException: 'The HttpGetEnabled property of ServiceMetadataBehavior is set to true and the HttpGetUrl property is a relative address, but there is no http base address.  Either supply an http base address or set HttpGetUrl to an absolute address.'

    This exception was originally thrown at this call stack:
    System.ServiceModel.Description.ServiceMetadataBehavior.CreateHttpGetEndpoints(System.ServiceModel.Description.ServiceDescription, System.ServiceModel.ServiceHostBase, System.ServiceModel.Description.ServiceMetadataExtension)
    System.ServiceModel.Description.ServiceMetadataBehavior.ApplyBehavior(System.ServiceModel.Description.ServiceDescription, System.ServiceModel.ServiceHostBase)
    System.ServiceModel.Description.ServiceMetadataBehavior.System.ServiceModel.Description.IServiceBehavior.ApplyDispatchBehavior(System.ServiceModel.Description.ServiceDescription, System.ServiceModel.ServiceHostBase)
    System.ServiceModel.Description.DispatcherBuilder.InitializeServiceHost(System.ServiceModel.Description.ServiceDescription, System.ServiceModel.ServiceHostBase)
    System.ServiceModel.ServiceHostBase.InitializeRuntime()
    System.ServiceModel.ServiceHostBase.OnBeginOpen()
    System.ServiceModel.ServiceHostBase.OnOpen(System.TimeSpan)
    System.ServiceModel.Channels.CommunicationObject.Open(System.TimeSpan)
    System.ServiceModel.Channels.CommunicationObject.Open()
    ConsoleApplication.Program.Main(string[]) in Program.cs

    Monday, June 1, 2020 1:14 PM