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 :) 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();
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
{
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)
{
}
}
public void RefreshDatas()
{
label1.Text = "Changes Happened";
GetActualData();
}
public SqlConnection ConnectionStringMethod()
{
SqlConnection con = new SqlConnection(connection);
con.Open();
return con;
}
private void Inventory_InventoryLog_Load(object sender, EventArgs e)
{
GetActivateData();
}