Jump to content

C# objects and Postgresql

- - - - -

This topic has been archived. This means that you cannot reply to this topic.
2 replies to this topic

#1
pykie101

pykie101

    Newbie

  • Members
  • Pip
  • 2 posts
Hi, this is my first post, I'm pretty new to all this stuff, infact I haven't really tried programming in 4 years so please excuse if this is easy because for me it's stupid hard .... anyway

I'm having a problem, I'm using PostgreSQL V8.3.3-1, Npgsql and Visual studio 2008 c#.

I'm passed a dictionary of key, value (key been a string, value been an object). The code works fine with key and value both been strings but postgresql doesn't seem to like it when the value is an object. How can I correct my code to work when passed an object? I realise that rowsaffected is currently only storing the first value, that's fine. I just want to be able to run a query using an object datatype for now.
I feel DbType.Object might be the problem.

current error is: "InvalidCastException is unhandled by user code"

sql is a query

"SELECT username FROM users WHERE age = :Value1"



public static string GetSql(string sql, Dictionary<string, object> d)

        {

            string connection = System.Configuration.ConfigurationManager.Connecti  onStrings["POSTGRESQL_LOCAL"].ConnectionString;

            

            using (NpgsqlConnection conn = new NpgsqlConnection(connection))

            {

                conn.Open();

                using (NpgsqlCommand command = new NpgsqlCommand(sql, conn))

                {

                    int i = 0;

 

                    foreach (KeyValuePair<string, object> kvp in d)

                    {

                        command.Parameters.Add(new NpgsqlParameter(kvp.Key, DbType.Object));

                        command.Parameters[i].Value = kvp.Value;

                        i++;

                    }

                    string rowsaffected = "error: No data";

                    using (NpgsqlDataReader dr = command.ExecuteReader())

                    {

                        while (dr.Read())

                        {

                            rowsaffected = dr[0].ToString();

                        }

                    }

 

                    conn.Close();

                    return rowsaffected;

 

                }

 

            }

            

        }

I hope I placed this on the correct forum.

Thanks

#2
pykie101

pykie101

    Newbie

  • Members
  • Pip
  • 2 posts
For clarity, when I say both key and value are strings I mean I change the datatype to string for the value as well.

#3
gaylo565

gaylo565

    Programming Professional

  • Members
  • PipPipPipPipPip
  • 268 posts
I haven't used protege but I know that ms sql doesn't support object type data. You need to use something like Sql_Variant (a nondescript data type.) Most SQL is pretty similar so I'm guessing that that is your problem.