Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C# SQL
I am creating a an application which has a Sql Query Notification but i'ved encountered a problem .. The System is kept on looping that goes onto infinite loop.The Query that i'ved used is in Stored Procedures and I'ved used 7 tables in my Query. What seems to be the problem of my query or my application ? Kindly Help me to resolved this thanks Smile | :) I'll post my codes here This is the Stored Procedures i am using
    ALTER PROCEDURE [dbo].[INVENTORYLOG_VIEW]
    AS
        BEGIN
        SELECT     
         ACCOUNT.Emp_Fname + ' ' + ACCOUNT.Emp_Lname AS [USERS],
         INVENTORYLOG.ACTIVITYDATE AS [ACTIVITY DATE], 
         INVENTORYLOGDETAILS.ACTIVITYTIME AS [ACTIVITY TIME],
         INVENTORYLOGDETAILS.LOG_ACTION AS [USER LOG], 
         CATEGORY.CATEGORY_NAME, 
         BRAND.Brand_Name AS BRAND, 
         PRODUCTDESCRIPTION.DESCRIPTION_TYPE, 
         COLOR.Color_Name AS [COLOR NAME], 
         SIZES.SIZE, 
         INVENTORY.QUANTITY_ON_HAND AS [CURRENT QTY], 
         INVENTORYLOGDETAILS.QUANTITY_LOG_ACTION AS [USER LOG QTY]
    FROM         
    dbo.ACCOUNT INNER JOIN
    dbo.INVENTORYLOG ON dbo.ACCOUNT.ACCOUNT_ID = dbo.INVENTORYLOG.Account_Id INNER JOIN
    dbo.INVENTORYLOGDETAILS ON dbo.INVENTORYLOG.INVENTORYLOGID = dbo.INVENTORYLOGDETAILS.INVENTORYLOGID INNER JOIN
    dbo.INVENTORY ON dbo.INVENTORYLOGDETAILS.INVENTORY_ID = dbo.INVENTORY.INVENTORY_ID INNER JOIN
    dbo.PRODUCT_DTL ON dbo.INVENTORY.PRODUCT_DTL_ID = dbo.PRODUCT_DTL.PRODUCT_DTL_ID INNER JOIN
    dbo.COLOR ON dbo.PRODUCT_DTL.COLOR_ID = dbo.COLOR.Color_Id INNER JOIN
    dbo.BRAND ON dbo.PRODUCT_DTL.BRAND_ID = dbo.BRAND.Brand_Id INNER JOIN
    dbo.CATEGORY ON dbo.PRODUCT_DTL.CATEGORY_ID = dbo.CATEGORY.CATEGORY_ID INNER JOIN
    dbo.PRODUCTDESCRIPTION ON dbo.PRODUCT_DTL.DESCRIPTION_ID = dbo.PRODUCTDESCRIPTION.DESCRIPTION_ID INNER JOIN
    dbo.SIZES ON dbo.PRODUCT_DTL.SIZE_ID = dbo.SIZES.SIZE_ID
    END
 
and this is my code
 
string connect;
        String connection;
        private SqlConnection cons = null;
        private SqlCommand command = null;
        private DataSet myDataSet = null;
        public Inventory_InventoryLog()
        {
            InitializeComponent();
            this.gridView1.OptionsView.ColumnAutoWidth = false;
            this.gridView1.ScrollStyle = ScrollStyleFlags.LiveHorzScroll | ScrollStyleFlags.LiveVertScroll;
            ConnectionString access = new ConnectionString();
            connection = access.AccessConnection(connect).ToString();
 
            // Sql Notifications Automatic
            EnoughPermission();
        }
 
        public bool EnoughPermission()
        {
            SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
            try
            {
                perm.Demand();
                MessageBox.Show(""+perm);
                return true;
            }
            catch(System.Exception)
            {
                return false;
            }
        }
        private void GetActivateData()
        {
            try
            {
                //String query = "SELECT [USER], [ACTIVITY DATE], [ACTIVITY TIME], [USER LOG], CATEGORY_NAME, Brand_Name, DESCRIPTION_TYPE, Color_Name, SIZE, [ CURRENT QTY], [USER LOG QTY] FROM INVENTORY_INVENTORY_LOG_DETAILS";
                SqlDependency.Stop(connection);
                SqlDependency.Start(connection);
                if (cons == null)
                  cons = new SqlConnection(connection);
                if (command == null)
                {
                    command = new SqlCommand("dbo.INVENTORYLOG_VIEW", cons);
                    command.CommandType = CommandType.StoredProcedure;
                }
                if (myDataSet == null)
                    myDataSet = new DataSet();
                GetActualData();
            }
            catch (Exception p)
            {
                MessageBox.Show(""+p);
            }
            MessageBox.Show("Being called");
        }
        private void GetActualData()
        {
            myDataSet.Clear();
            command.Notification = null;
            SqlDependency dependecy = new SqlDependency(command);
            dependecy.OnChange +=new OnChangeEventHandler(dependecy_OnChange);
            using (SqlDataAdapter adapter = new SqlDataAdapter(command))
            {
                adapter.Fill(myDataSet, "dbo.ACCOUNT, dbo.INVENTORYLOG, dbo.INVENTORYLOGDETAILS, dbo.INVENTORY, dbo.PRODUCT_DTL,dbo.COLOR,dbo.BRAND,dbo.CATEGORY,dbo.PRODUCTDESCRIPTION, dbo.SIZES");
                gridControl1.DataSource = myDataSet.Tables["dbo.ACCOUNT, dbo.INVENTORYLOG, dbo.INVENTORYLOGDETAILS, dbo.INVENTORY, dbo.PRODUCT_DTL,dbo.COLOR,dbo.BRAND,dbo.CATEGORY,dbo.PRODUCTDESCRIPTION, dbo.SIZES"];
                gridView1.BestFitColumns();
                gridView1.BestFitMaxRowCount = 10;
            }
 
        }
 
        delegate void UIDelegate();
        public void dependecy_OnChange(object sender, SqlNotificationEventArgs e)
        {
            try
            {
                UIDelegate uidel = new UIDelegate(RefreshDatas);
                this.Invoke(uidel, null);
                SqlDependency dependency = (SqlDependency)sender;
                dependency.OnChange -= dependecy_OnChange;
            }
            catch (Exception es)
            {
             //   XtraMessageBox.Show(es.Message);
            }
        }
        public void RefreshDatas()
        { 
            ////////////////////////
            //---------------------------------------   NOTIFICATION INSERTS HERE ----------------------------------- //////
            label1.Text = "Changes Happened";
            GetActualData();
        }
 
        /// <summary>
        /// //////////////////////////////////////////////////////////////////////////////////////////////////
        /// //--------------------------------------------------------  SQL NOTIFICATION ------------------------------------
        /// </summary>
        /// <param name="args"></param>

 
        public SqlConnection ConnectionStringMethod()
        {
            SqlConnection con = new SqlConnection(connection);
            con.Open();
            return con;
        }
        private void Inventory_InventoryLog_Load(object sender, EventArgs e)
        {
            GetActivateData();           
        }
Posted 7-Apr-13 19:49pm
Edited 7-Apr-13 19:56pm
(no name)16.6K
v2
Comments
Vani Kulkarni at 8-Apr-13 0:56am
   
[EDIT]Formatted code. [/EDIT]
John Mark Olesco at 8-Apr-13 11:11am
   
hello Vani Kulkarni please help me with my problem..

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.1411022.1 | Last Updated 8 Apr 2013
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100