The SQL Server Service Broker for the current database is not enabled, and as a result query notifications are not supported

Recently I have faced this problem while I am accessing Sqlserver from my application.

I got this problem because of Service Broker has been disabled for my database. By following below steps I have resolved my problem.

-- Enabling Service Broker --

Depending on how your model database is set up, Service Broker may be disabled by default when you create a new database. Or, you may want to enable it in a database that already exists but in which it is not enabled.

Service Broker is turned on or off on a per-database basis, and you can find out its current state for your database by querying the sys.databases catalog view.

Turning on Service Broker is easy enough, but there is a slight twist: there must either be zero users connected to the database, or you must be the only user connected, when you actually flip the switch. ALTER DATABASE can help us in that regard, but it doesn't change the fact that somehow, those users have to go.

--Let's find out if Service Broker is turned on. SELECT is_broker_enabled  FROM sys.databases  WHERE name = 'DataBase Name'

If result is 0 means your service broker not enabled, by running following query we can enable Service Broker.

ALTER DATABASE DatabaseName SET ENABLE_BROKER  WITH ROLLBACK IMMEDIATE

"Service Broker is a feature in Microsoft SQL Server, internal or external processes can send and receive guaranteed, asynchronous messages by using extensions to Transact-SQL Data Manipulation Language (DML). Messages can be sent to a queue in the same database as the sender, to another database in the same SQL Server instance, or to another SQL Server instance either on the same server or on a remote server."

Adding and updating column to an Existing XML file in C#

// creating dataset

DataSet ds = new DataSet();

       

// Reading XML file through ReadXml method which reads XML file and converts into Data Tables

ds.ReadXml("XMLFilePath");

// adding Required Column to an existing Table in dataset, here I am assuming my table is in '0' position in Dataset.

 ds.Tables[0].Columns.Add("ColumnName", typeof(string));

 // Here assigning value to Added Column

 for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

  {

   ds.Tables[0].Rows[i]["ColumnName"] = "Test";

  }

// writing dataset to xml file, xml file will be update with new column (Recently Added)

ds.WriteXml("XMLFilePath");

Microsoft Visual C# 2010 Step by Step

Loading relational tables into DataGridView in C#.NET and Winforms

In this post we can see how to show relational tables in the datagridview.

For this sample I have taken Nortwind as my database and I have selected Customers and Orders Tables.

o    Let’s take a new windows application.

o    Add one form to it.

o    Copy and paste the below code in form.cs file

 private DataGridView customerDataGridView = new DataGridView();

 private BindingSource customerBindingSource = new BindingSource();

 private DataGridView ordersDataGridView = new DataGridView();

 private BindingSource ordersBindingSource = new BindingSource();

 public Form1()

   {

 InitializeComponent();

 customerDataGridView.Dock = DockStyle.Fill;

 customerDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

 customerDataGridView.AllowUserToAddRows = false;

 customerDataGridView.AllowUserToDeleteRows = false;

 ordersDataGridView.Dock = DockStyle.Fill;

 ordersDataGridView.SelectionMode = DataGridViewSelectionMode.FullRowSelect;

 customerDataGridView.AllowUserToAddRows = false;

 customerDataGridView.AllowUserToDeleteRows = false;

 SplitContainer splitContainer1 = new SplitContainer();

 splitContainer1.Dock = DockStyle.Fill;

 splitContainer1.Orientation = Orientation.Horizontal;

 splitContainer1.Panel1.Controls.Add(customerDataGridView);

 splitContainer1.Panel2.Controls.Add(ordersDataGridView);

 this.Controls.Add(splitContainer1);

 this.Load += new System.EventHandler(Form1_Load);

 this.Text = "Loading relational tables into DataGridView";

   }

 private void Form1_Load(object sender, EventArgs e)

        {

 // Bind the DataGridView controls to the BindingSource

 // components and load the data from the database.

 customerDataGridView.DataSource = customerBindingSource;

 ordersDataGridView.DataSource = ordersBindingSource;

GetData();

 // Resize the master DataGridView columns to fit the newly loaded data.

 customerDataGridView.AutoResizeColumns();

 // Configure the details DataGridView so that its columns automatically

 // adjust their widths when the data changes.

 ordersDataGridView.AutoSizeColumnsMode =

 DataGridViewAutoSizeColumnsMode.AllCells;

        }

private void GetData()

    {

 try

  {

    // Specify a connection string. Replace the given value with a

// valid connection string for a Northwind SQL Server sample

// database accessible to your system.

String connectionString = "Server=localhost; uid=sa;pwd=sa@123;       database=Northwind";

SqlConnection connection = new SqlConnection(connectionString);

// Create a DataSet.

DataSet data = new DataSet();

data.Locale = System.Globalization.CultureInfo.InvariantCulture;

// Add data from the Customers table to the DataSet.

SqlDataAdapter masterDataAdapter = new

SqlDataAdapter("select * from customers", connection);

masterDataAdapter.Fill(data, "Customers");

 // Add data from the Orders table to the DataSet.

 SqlDataAdapter detailsDataAdapter = new

 SqlDataAdapter("select * from Orders", connection);

 detailsDataAdapter.Fill(data, "Orders");

 // Establish a relationship between the two tables.

 DataRelation relation = new DataRelation("CustomersOrders",

 data.Tables["Customers"].Columns["customerID"],

 data.Tables["Orders"].Columns["customerID"]);

 data.Relations.Add(relation);

 // Bind the master data connector to the Customers table.

 customerBindingSource.DataSource = data;

 customerBindingSource.DataMember = "Customers";

  // Bind the details data connector to the master data connector,

  // using the DataRelation name to filter the information in the

  // details table based on the current row in the master table.

  ordersBindingSource.DataSource = customerBindingSource;

  ordersBindingSource.DataMember = "CustomersOrders";

    }

  catch (SqlException ex)

   {

                MessageBox.Show("sql connection has not been established”);

    }

 }

Dynamic Accordion control with Paging

jaIn this post we are going to learn how to bind the data to accordion control dynamically with paging option.

The Accordion is a web control that allows you to provide multiple panes and display them one at a time. It is like having several CollapsiblePanels where only one can be expanded at a time. The Accordion is implemented as a web control that contains AccordionPane web controls. Each AccordionPane control has a template for its Header and its Content. We keep track of the selected pane so it stays visible across postbacks.

Create data table as follows and insert 10 records as you like

CREATE TABLE Projects(

    ProjectId int identity(1,1) NOT NULL,

    ProjectName nvarchar(500) ,

    [Description] nvarchar(max)

)

Create new Website named as DynamicAccordionPaging

Copy and paste below code in Default.aspx

<head runat="server">

    <title></title>

    <style>

    /* Accordion */

.accordionHeader

{

    border: 1px solid #2F4F4F;

    color: white;

    background-color: #2E4d7B;

       font-family: Arial, Sans-Serif;

       font-size: 12px;

       font-weight: bold;

    padding: 5px;

    margin-top: 5px;

    cursor: pointer;

}

#master_content .accordionHeader a

{

       color: #FFFFFF;

       background: none;

       text-decoration: none;

}

#master_content .accordionHeader a:hover

{

       background: none;

       text-decoration: underline;

}

.accordionHeaderSelected

{

    border: 1px solid #2F4F4F;

    color: white;

    background-color: #5078B3;

       font-family: Arial, Sans-Serif;

       font-size: 12px;

       font-weight: bold;

    padding: 5px;

    margin-top: 5px;

    cursor: pointer;

}

    </style>

</head>

<div>

        <asp:ScriptManager ID="ScriptManager1" runat="server">

        </asp:ScriptManager>

    <table>

<tr>

<asp:HiddenField ID="txtCount" runat="server" />

<td>

    <asp:UpdatePanel ID="UpdatePanel1" runat="server">

    <ContentTemplate >

  <asp:Panel ID="pnlShowProjects" runat="server">

        <asp:Accordion ID="acc" runat="server"

         SelectedIndex="0" EnableViewState="false"

         FadeTransitions="false" FramesPerSecond="40"

         TransitionDuration="250" AutoSize="None"

         RequireOpenedPane="false" SuppressHeaderPostbacks="true"

         HeaderCssClass="accordionHeader"

        HeaderSelectedCssClass="accordionHeaderSelected"

        ContentCssClass="accordionContent">

             <HeaderTemplate>

             <table width="100%">

             <tr>

             <td align="left">

             <asp:Label ID="lblProjectName" runat="server" Text='<%#Eval("ProjectName") %>'></asp:Label>

             </td>

             </tr>

             </table>

            </HeaderTemplate>

            <ContentTemplate>

            <table>

            <tr>

            <td align="left">

               <%# Eval("Description") %>

            </td>

            </tr>

            </table>

          </ContentTemplate>

        </asp:Accordion>

        </asp:Panel>

        <table align="right">

        <tr>

        <td >

        <asp:ImageButton ID="imgPrevious" runat="server"

                 ImageUrl="~/Images/previous.gif" onclick="imgPrevious_Click"

                  AlternateText="Previous" />

        </td>

        <td>

         <asp:ImageButton ID="imgNext" runat="server"

                 ImageUrl="~/Images/next.gif" onclick="imgNext_Click"

                AlternateText="Next" />

        </td>

        <td>

           <asp:Label ID="lblTotal" runat="server"></asp:Label>

        </td>

        </tr>

        </table>

        </ContentTemplate>

        </asp:UpdatePanel>

</td>

</tr>

</table>

    </div>

 Copy and paste below code in  Default.aspx.cs

PagedDataSource _pagedsource;

    SqlConnection conn = new SqlConnection("Data Source=.;Database=Sample;trusted_connection=true");

    protected override void OnInit(EventArgs e)

    {

        _pagedsource = new PagedDataSource();

       

        // creating handler for page unload event

        Unload += new EventHandler(Default_Unload);

        base.OnInit(e);

    }

    void Default_Unload(object sender, EventArgs e)

    {

        // we save the data source in a session to reload when posback

        Session["newslist"] = _pagedsource;

    }

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            getallprojects();

            lblTotal.Text = setpaging();

        }

        else

            if (Session["newslist"] != null)

            {

                _pagedsource = (PagedDataSource)Session["newslist"];

            }

    }

    void getallprojects()

    {

        conn.Open();

        SqlDataAdapter da = new SqlDataAdapter("select ProjectName,[Description] from Projects", conn);

        DataSet ds = new DataSet();

        da.Fill(ds);

        // you fill the PagedDataSource by set the DataSource property with the content of the data to display and page (e.g. DataTable, Collection...)

        _pagedsource.DataSource = ds.Tables[0].DefaultView;

        // CurrentPageIndex - what page to display

        _pagedsource.CurrentPageIndex = 0;

        txtCount.Value = ds.Tables[0].Rows.Count.ToString();

        initPagedDataSource();

        if (_pagedsource.PageSize == int.Parse(txtCount.Value))

        {

            imgPrevious.Visible = false;

            imgNext.Visible = false;

        }

        else

        {

            imgPrevious.Visible = true;

            imgNext.Visible = true;

        }

    }

    public string setpaging()

    {

        int from = _pagedsource.FirstIndexInPage + 1;

        int to = _pagedsource.FirstIndexInPage + _pagedsource.PageSize;

        if (to > int.Parse(txtCount.Value))

            to = int.Parse(txtCount.Value);

        return "Showing " + from.ToString() + " - " + to.ToString() + " of " + txtCount.Value;

    }

    private void initPagedDataSource()

    {

        // it is necessary to set this property true

        _pagedsource.AllowPaging = true;

        // how many items to show on one page

        _pagedsource.PageSize = 5;

        // now, bind the Accordion control to the PagedDataSource object

        acc.DataSource = _pagedsource;

        acc.DataBind();

        // disable Previous (Last) button when showing the first page of the PageDataSource

        imgPrevious.Enabled = !_pagedsource.IsFirstPage;

        // disable Next button when showing the last page of the PageDataSource

        imgNext.Enabled = !_pagedsource.IsLastPage;

    }

    protected void imgPrevious_Click(object sender, ImageClickEventArgs e)

    {

        // when clicking Last button, decrement the CurrentPageIndex

        _pagedsource.CurrentPageIndex--;

        initPagedDataSource();

        lblTotal.Text = setpaging();

    }

    protected void imgNext_Click(object sender, ImageClickEventArgs e)

    {

        // when clicking Next button, increment the CurrentPageIndex

        _pagedsource.CurrentPageIndex++;

        initPagedDataSource();

        lblTotal.Text = setpaging();

    }

Finally we are able to implement paging with Accordion control 

Foundations of ASP.NET AJAX (Expert's Voice in .NET)