Sql Dependency onchange event not firing every time c# - C# Discussion Boards

c# opening database to array

25-Apr-15 10:55

So, how many times are you going to post this?

I would suggest you don't do it again unless you really want to be ignored.

When someone with experience sees your question, they'll answer.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

ruspj25-Apr-15 8:03

ruspj

25-Apr-15 8:03

hey guys

trying to convert an access database to array to sort it by a calculated value.
the database connection is working frin in other functions
i keep getting the error message refering to the reader.GetString lines

An unhandled exception of type
'System.InvalidOperationException' occurred in System.Data.dll

Additional information: No data exists for the row/column.

can anyone help

static void displayinarray()
{
    int rows = 0;
    int averagewins;
    //in global     static OleDbConnection connection;
    //in main       connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\connected\\users.accdb");
    try
    {
        OleDbCommand command = new OleDbCommand("select * from users", connection);
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();
        while (reader.Read()) {rows++;};
        string[,] results = new string[6,rows];
        for (int loop = 0; loop <= rows; loop++)
        {
            results[0,loop] = reader.GetString(0);
            results[1,loop] = reader.GetString(1);
            results[2,loop] = reader.GetString(2);
            results[3,loop] = reader.GetInt32(3).ToString();
            results[4,loop] = reader.GetInt32(4).ToString();
            averagewins = (reader.GetInt32(4) / reader.GetInt32(3))*100;
            results[5,loop] = Convert.ToString(averagewins);
        }

        Console.Clear();
        Console.WriteLine("{0}", rows);
        Console.WriteLine("  ┌──────────────┬──────────────┬──────────────┬────────┬────────┬─────────┐");
        Console.WriteLine("  │ username     │ name         │ surname      │ played │ won    │ % won   │");
        Console.WriteLine("  ├──────────────┼──────────────┼──────────────┼────────┼────────┼─────────┤");

            for (int loop = 0; loop <= rows ; loop++)
        {
            Console.SetCursorPosition(2, loop + 4);
            Console.WriteLine("│ {0} ", results[0,loop]);
            Console.SetCursorPosition(17, loop + 4);
            Console.WriteLine("│ {0} ", results[1,loop]);
            Console.SetCursorPosition(32, loop + 4);
            Console.WriteLine("│ {0} ", results[2,loop]);
            Console.SetCursorPosition(47, loop + 4);
            Console.WriteLine("│ {0} ", results[3,loop]);
            Console.SetCursorPosition(56, loop + 4);
            Console.WriteLine("│ {0} ", results[4,loop]);
            Console.SetCursorPosition(65, loop + 4);
            Console.WriteLine("│ {0} ", results[5,loop]);
            Console.SetCursorPosition(75, loop + 4);
            Console.WriteLine("│");
        }
        Console.WriteLine("  └──────────────┴──────────────┴──────────────┴────────┴────────┴─────────┘");

        reader.Close();
        connection.Close();
    }
    catch (OleDbException e)
    {
        Console.WriteLine("error: {0}", e.Errors[0].Message);
    }
    Console.ReadLine();
}}

modified 25-Apr-15 14:10pm.

Re: c# opening database to array

Sascha Lefèvre25-Apr-15 8:16

Sascha Lefèvre

25-Apr-15 8:16

A DataReader only allows for reading the result-set once. Where you count the lines with while (reader.Read()) {rows++;}; you're advancing the position of the DataReader to the end of the result-set and then it considers its job done, no further reading possible.

Instead of an array (for which you have to know the size in order to allocate it) just use a List (for which you don't have to know the amount of items beforehand). And I would suggest to create a class with the columns you're reading as properties, to use it as the items for the List.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

Re: c# opening database to array

ruspj25-Apr-15 8:31

ruspj

25-Apr-15 8:31

thanks for the quick reply
have tried removing the while (reader.Read()) {rows++;}; line and setting to read just a few lines by changing the rows by int rows = 3; as wasnt sure if it was interfering with reading the database.
was still getting the same error
prety new at c# & so was storing in an array as used them before
not used list yet & no idea how to create a class with columns as properties

using this still has the same error with not reading

static void displayinarray()
{
    int rows = 3;
    int averagewins;
    //in global     static OleDbConnection connection;
    //in main       connection = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\\connected\\users.accdb");
    try
    {
        OleDbCommand command = new OleDbCommand("select * from users", connection);
        connection.Open();
        OleDbDataReader reader = command.ExecuteReader();
        //while (reader.Read()) {rows++;};
        string[,] results = new string[6,rows];
        for (int loop = 0; (reader.Read()); loop++)
        {
            results[0,loop] = reader.GetString(0);
            results[1,loop] = reader.GetString(1);
            results[2,loop] = reader.GetString(2);
            results[3,loop] = reader.GetInt32(3).ToString();
            results[4,loop] = reader.GetInt32(4).ToString();
            averagewins = (reader.GetInt32(4) + reader.GetInt32(3))*100;
            results[5,loop] = Convert.ToString(averagewins);
        }

modified 25-Apr-15 14:40pm.

Re: c# opening database to array

Sascha Lefèvre25-Apr-15 8:50

Sascha Lefèvre

25-Apr-15 8:50

1) Don't do a select * , instead something like this: "select col1, col2, col3, col4, col5 from users"
That way you a) can be sure that you actually get 5 columns or a helpful exception and b) that you get the columns in the order you want them.

2) You're setting rows = 3 but you're reading all the returned records because rows is not part of the predicate of your for-loop.

3) Please check that under Visual Studio Menu: Debug -> Exceptions -> Common Langugage Runtime Exceptions the option Thrown is checked. Then, after applying the above modifications, run your program in debug mode, observe at which line an Exception is being thrown and please tell me.

If the brain were so simple we could understand it, we would be so simple we couldn't. — Lyall Watson

SharpDevolp 4.3

Member 465445225-Apr-15 3:41

Member 4654452

25-Apr-15 3:41

First of all, thanks. I've been coding for a while now and don't ask many question here but I do get a lot of answers here. I like to research my problems -- I learn more that way.

I have always used Visual Studio Pro for my C#, but lately I have been giving SharpDevolop a test drive. Does anybody here have any likes/dislikes about this software? Are there any major shortcomings?

Just wondering. Thanks.

Re: SharpDevolp 4.3

Eddy Vluggen25-Apr-15 7:32

Eddy Vluggen

25-Apr-15 7:32

Member 4654452 wrote:
Are there any major shortcomings?

Yes, it is not Visual Studio.

It takes a while to get used to it, but it can run from USB, is rather fast, and doesn't take much resources. There's even a TFS-plugin, but that is still not enough to challenge Visual Studio Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

Registering SQL Dependency again from onchange event

Tridip Bhattacharjee24-Apr-15 4:48

Tridip Bhattacharjee

24-Apr-15 4:48

i like to know is it right approach to registering SQL Dependency again from onchange event ?

one guy review my code and told me registering everything again and again after each notification. As I can recall this will create a queue each time

so i comment this code in onchange event

void OnDataChange(object sender, SqlNotificationEventArgs e)
        {
            BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);

<pre>
        if (e.Source == SqlNotificationSource.Timeout)
        {
            BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
            Environment.Exit(1);
        }
        else if (e.Source != SqlNotificationSource.Data)
        {
            BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
            Environment.Exit(1);
        }
        else if (e.Type == SqlNotificationType.Change)
        {
            BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
        }
        else
        {
            BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
        }

        CallWebService();
        //((SqlDependency)sender).OnChange -= OnDataChange;
        //RegisterNotification();
    }</pre>

just see i comment this line //((SqlDependency)sender).OnChange -= OnDataChange; //RegisterNotification(); but after commenting i found onchange event is firing first time and it is not firing from second time.

guide me with right approach. here is my partial code

void OnDataChange(object sender, SqlNotificationEventArgs e)
{
    BBALogger.Write("PartIndexer Service OnDataChange called start", BBALogger.MsgType.Info);

    if (e.Source == SqlNotificationSource.Timeout)
    {
    BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);
    Environment.Exit(1);
    }
    else if (e.Source != SqlNotificationSource.Data)
    {
    BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);
    Environment.Exit(1);
    }
    else if (e.Type == SqlNotificationType.Change)
    {
    BBALogger.Write("PartIndexer Service Data changed detected", BBALogger.MsgType.Info);
    }
    else
    {
    BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
    }

    CallWebService();
    //((SqlDependency)sender).OnChange -= OnDataChange;
    //RegisterNotification();
}

private void RegisterNotification()
    {
        string tmpdata = "";
        BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);

        System.Data.SqlClient.SqlDependency.Stop(connectionString);
        System.Data.SqlClient.SqlDependency.Start(connectionString);

        try
        {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT ActivityDate FROM [dbo].tablename";
            dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler(OnDataChange);
            SqlDataReader dr = cmd.ExecuteReader();
            {
            if (dr.HasRows)
            {
                dr.Read();
                tmpdata = dr[0].ToString();
            }
            }
            dr.Dispose();
            cmd.Dispose();
        }
        }
        catch (Exception ex)
        {
        BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
        }
        finally
        {
        BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

        }

}

thanks

tbhattacharjee

Sql Dependency onchange event not firing every time c#

Tridip Bhattacharjee24-Apr-15 3:48

Tridip Bhattacharjee

24-Apr-15 3:48

public partial class PartIndexer : ServiceBase
{
    static string connectionString = "server=xxx;uid=xxx;password=xxx;database=xxx;Pooling=true;Connect Timeout=20;";
    SqlDependency dep;

<pre>
public PartIndexer()
{
    InitializeComponent();
}

private string GetLoggedInUser()
{
    string userName = "";
    if (System.Security.Principal.WindowsIdentity.GetCurrent() != null)
    {
        userName = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
    }
    return userName;
}

#region OnStart
protected override void OnStart(string[] args)
{
    BBALogger.Write("PartIndexer Service OnStart called start", BBALogger.MsgType.Info);
    RegisterNotification();
    MailSend(); // notification mail send
    BBALogger.Write("PartIndexer Service OnStart called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
}
#endregion

#region RegisterNotification
/// <summary>
/// RegisterNotification
/// this is main routine which will monitor data change in ContentChangeLog table
/// </summary>
private void RegisterNotification()
{
    string tmpdata = "";
    BBALogger.Write("PartIndexer Service RegisterNotification called start", BBALogger.MsgType.Info);

    System.Data.SqlClient.SqlDependency.Stop(connectionString);
    System.Data.SqlClient.SqlDependency.Start(connectionString);

    try
    {
        using (SqlConnection conn = new SqlConnection(connectionString))
        {
            conn.Open();
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT TestTable FROM [dbo].ContentChangeLog";
            dep = new SqlDependency(cmd);
            dep.OnChange += new OnChangeEventHandler(OnDataChange);
            SqlDataReader dr = cmd.ExecuteReader();
            {
                if (dr.HasRows)
                {
                    dr.Read();
                    tmpdata = dr[0].ToString();
                }
            }
            dr.Dispose();
            cmd.Dispose();
        }
    }
    catch (Exception ex)
    {
        BBALogger.Write("PartIndexer Service RegisterNotification Error "+ex.Message.ToString(), BBALogger.MsgType.Error);
    }
    finally
    {
        BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

    }

}
#endregion

#region OnDataChange
/// <summary>
/// OnDataChange
/// OnDataChange will fire when after data change found in ContentChangeLog table
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
void OnDataChange(object sender, SqlNotificationEventArgs e)
{
    ((SqlDependency)sender).OnChange -= OnDataChange;

    BBALogger.Write("PartIndexer Service RegisterNotification called end", BBALogger.MsgType.Info);

    if (e.Source == SqlNotificationSource.Timeout)
    {
MailSend(); // notification mail send
BBALogger.Write("PartIndexer Service SqlNotificationSource.Timeout error", BBALogger.MsgType.Error);

        Environment.Exit(1);
    }
    else if (e.Source != SqlNotificationSource.Data)
    {
    MailSend(); // notification mail send
        BBALogger.Write("PartIndexer Service SqlNotificationSource.Data", BBALogger.MsgType.Error);

        Environment.Exit(1);
    }
    else if (e.Type == SqlNotificationType.Change)
    {
        StartIndex();
        BBALogger.Write("PartIndexer Service Data changed", BBALogger.MsgType.Info);
    }
    else
    {
        BBALogger.Write(string.Format("Ignored change notification {0}/{1} ({2})", e.Type, e.Info, e.Source), BBALogger.MsgType.Warnings);
    }

    RegisterNotification();
}
 #endregion

#region StartIndex
/// <summary>
/// StartIndex
/// this routine will call web service in bba reman website which will invoke routine to re-index data
/// </summary>
void StartIndex()
{
    // calling web service if change is notified

}
#endregion

#region MailSend
/// <summary>
/// MailNotify
/// fire mail when apps start & exit
/// </summary>

/// <param name="strStatus" />
void MailSend()
{
/* mail send code */
}
#endregion

#region OnStop
protected override void OnStop()
{
BBALogger.Write("PartIndexer Service StartIndex called end, logged in user " + GetLoggedInUser(), BBALogger.MsgType.Info);
System.Data.SqlClient.SqlDependency.Stop(connectionString);
MailNotify("STOPPED");
}
#endregion
}

another issue i noticed that when i start my windows service and leave it run in one day when i try to stop or restart the the windows service then i saw windows can not stop the service. it means definitely there is some flaw in my code which i am not being able to point out. so please help me.

another review my code and told me every time a new queue will be created at db side which may causes the problem. what is wrong in code for which every time a new queue will be created at db. thanks
thanks

tbhattacharjee

Unable to connect Oracle database from C#

meeram3924-Apr-15 3:47

24-Apr-15 3:47

I have an applications that pulls data from Sql Server and push to Oracle database which is in remote server. The remote server can be connected through VPN. Initially I tried to install Oracle client version 11g, and then different versions, but everything failed. My OS is windows 8.1, googled and find out that 11G will not support on Win8.1. So I tried in Windows 7 OS without installing client,using the example given in this link. However I am still getting an error message saying as "Attempt to load Oracle client libraries threw BadImageFormatException. This problem will occur when running in 64 bit mode with the 32 bit Oracle client components installed".

My OS is Windows 7 64 bit. I have downloaded and installed the Instant Client Package - Basic Lite: Smaller version of the Basic, with only English error messages and Unicode, ASCII, and Western European character set support from the Oracle site.

I am not sure how to proceed with this. My question is:
1) The connection string provided in the article - I put the host name as Server IP. Is this correct? So where should I put the db name?
2) Should again require a tnsnames.ora file? If so, should I create a new one and put it in the same folder of exe?

I am stuck at this point. Please provide any help on this. Thanks in advance

Dave Kreskowiak24-Apr-15 4:03

24-Apr-15 4:03

You MUST have an Oracle Client installed for your app to work. Preferrably, one that is compatible with your Oracle server version.

Next, you got the bad image format exception because your Oracle Client is 32-bit and your code is compiled to target AnyCpu. This means your code will run as 32-bit on a 32-bit O/S and 64-bit on a 64-bit O/S. Since you cannot mix 32 and 64-bit code in the same process, you got that error message.

The fix is simple. Recompile your app to target x86 only. Go into your Project Properties, Build tab and look for the Platform Target option.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

meeram3924-Apr-15 14:53

24-Apr-15 14:53

Thanks Dave,

I have installed Oracle Client earlier and tried. I have installed 64 bit as well. Then, in one of the forums, it is found that I need to install 32 bit as well, so I installed both. What I will do now is, I will remove everything and start afresh, let me try once again. Meanwhile, could you please let me know where should I find the file tnsnames.ora?

Dave Kreskowiak24-Apr-15 17:21

24-Apr-15 17:21

You don't need a TNSNAMES.ORA file. All it is is a text file with predefined connection strings in it. That's all.

You can put the very same connection string in your OracleConnection object and it'll work.

But, it's found under the path %ORACLE_HOME%/network/admin.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

meeram3924-Apr-15 19:28

24-Apr-15 19:28

Ok. Thanks. Let me do the exercise once again and will update.

meeram3925-Apr-15 0:39

25-Apr-15 0:39

I removed everything and installed fresh everything in C drive itself. So I got tnsnames and sqlnames ora files automatically. I tried ping from DOS prompt using ping and tnsping which was also successful. However, When I tried from dotnet, I am getting an error message, "The procedure entry point ons_init_wconfig_ctx could not be loaded in the dynamic link library oraons.dll". I have installed win64_11gR2_client in Windows 7, and the dotnet version is Visual Studio 2012 Enterprise Edition (Trial).

My code is as below:

 private void ConnectOracleDB()
        {
            string sqlstring = string.Empty;
            Logger myLogger = new Logger();
            

            string myConnectionString = GenerateConnectionString();
            OracleConnection myConnection = new OracleConnection();
            myConnection.ConnectionString = myConnectionString;

            try
            {
                myLogger.AddtoLogFile("Trying to connect Oracle DB", "Page Load");
                myConnection.Open();                
            }
            catch (Exception ex)
            {
                myLogger.AddtoLogFile(ex.Message, "Connecting to Oracle DB");
            }

            //execute queries
            myConnection.Close();

        }

private string GenerateConnectionString()
        {
            return "Data Source=( DESCRIPTION = ( ADDRESS_LIST = ( ADDRESS = ( PROTOCOL = TCP )( HOST = {server ip} )( PORT = 1521 ) ) )( CONNECT_DATA = ( SERVER = DEDICATED )( SERVICE_NAME = {db name}) ) ); User Id= abcd; Password = 1234;";
        }

Update:
I fixed this now, as I copied the same to the bin directory on the Application root directory. But now another error comes as "Object doesn't exist" .. Cry | :((

modified 25-Apr-15 9:30am.

Dave Kreskowiak25-Apr-15 3:42

25-Apr-15 3:42

All you have to do is Google the error message "The procedure entry point ons_init_wconfig_ctx could not be loaded in the dynamic link library oraons.dll"and start reading.

A guide to posting questions on CodeProject

Click this: Asking questions is a skill.
Seriously, do it.

Dave Kreskowiak

meeram3925-Apr-15 5:46