PgsqlDataAdapter Command Null reference exception

PgsqlDataAdapter Command Null reference exception

Postby marc » Wed 02 Aug 2006 11:02

Hi

I'm evaluate different ways to connect postgresql and your product looks really good for me. But there are still some questions.

First when i will update my database from a table i need allways to set the PgsqlCommand property UpdateRowSource to NONE. Why do i need to do this?

Second, i'm not sure whats about this problem. When i create a insert command with the PgsqlCommand object and then set this command object to the Pgsql DataAdapter i receive allways a command text null reference exception. This error does not occure if i initialize the DataAdapter with a SELECT command and set the command object by the InsertCommand property?

Code: Select all
   

DataSet ds = new DataSet("Test");
            DataTable dt = new DataTable("MyTable");

            dt.Columns.Add("test");

            dt.Rows.Add("Franz");
            dt.Rows.Add("Hans");
            dt.Rows.Add("Elke");
            dt.Rows.Add("Martin");
            ds.Tables.Add(dt);

            PgSqlConnection conn = new PgSqlConnection("User Id=postgres;Password=pypwd;Host=localhost;Database=TestDb;Schema=public;");
            conn.Open();

            PgSqlCommand cmd = new PgSqlCommand("INSERT INTO \"Testtable\"(\"Name\") VALUES(?)");
            cmd.Parameters.Add(":p1", PgSqlType.VarChar, 10, "test");
            cmd.Connection = conn;
            cmd.UpdatedRowSource = UpdateRowSource.None;
            cmd.Connection = conn;

            //generate null reference exception
            try
            {
                PgSqlDataAdapter da1 = new PgSqlDataAdapter(cmd);//"SELECT * FROM \"Testtable\"",conn);
                //da.InsertCommand = cmd;
                da1.RowUpdating += new PgSqlRowUpdatingEventHandler(da_RowUpdating);
                da1.Update(ds.Tables[0]);
            }
            catch (Exception e1)
            {
                Console.WriteLine(e1.Message);
            }

            //works fine
            try
            {
                PgSqlDataAdapter da2 = new PgSqlDataAdapter("SELECT * FROM \"Testtable\"",conn);
                da2.InsertCommand = cmd;
                da2.RowUpdating += new PgSqlRowUpdatingEventHandler(da_RowUpdating);
                da2.Update(ds.Tables[0]);
            }
            catch (Exception e2)
            {
                Console.WriteLine(e2.Message);
            }



beste regards
marc[/code]
marc
 

Postby Alexey » Wed 02 Aug 2006 12:00

First is definitely a bug. We will fix it as soon as possible. To resolve it set UpdateBatchSize property to 1.
Second is not a bug. When you write this
Code: Select all
PgSqlDataAdapter da1 = new PgSqlDataAdapter(cmd);

you set only SelectCommand property, but need to set InsertCommand property as well.
Last edited by Alexey on Thu 03 Aug 2006 06:16, edited 1 time in total.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby marc » Wed 02 Aug 2006 12:29

Thats interesting because i used Npgsql and the M$ sql dataadapter as well For this other adapters the roule is. When i set there a command like this
Code: Select all
PgSqlDataAdapter da1 = new PgSqlDataAdapter(cmd);
it is set as the main command autonomous which type (SELECT, INSERT, UPDATE) of command it is.

About the rest i have to say thank you for your quick anwser.
marc
 

Postby Alexey » Thu 03 Aug 2006 06:25

Code: Select all
public SqlDataAdapter (SqlCommand selectCommand)

This is constructor for SqlDataAdapter. It initializes a new instance of the SqlDataAdapter class with the specified SqlCommand as the SelectCommand property. If you pass there some insert statement, SelectCommand property will be set up with this statement. So there is no difference between SqlDataAdapter and PgSqlDataAdapter in this case.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby marc » Thu 03 Aug 2006 13:25

Ok my fault, what i ment was your adapter requiers allways a select statement. For example in the case when i only update or insert new records a select statement is in my point of view not required. But when the select statement is not set a null reference exception occure.

An other unclear thing for me is when i craete a data imort dll for storing my data to the database. I created a small test program which is using my dll. But i get a license error altough i added the license file to the dll. To solve this problem i had to add the license file also to my test application that is calling my dll.

Maybe it's because i have only a trial version, then it's ok when this requierment not occure in productivity.

Sorry for this amount of questions we evaluate different Postgre DataAdapter for our application.
marc
 

Postby Alexey » Thu 03 Aug 2006 13:56

Regarding PgSqlDataAdapter, you are wrong again. Change your code like this:
Code: Select all
                PgSqlDataAdapter da2 = new PgSqlDataAdapter();
                da2.InsertCommand = cmd;
                da2.Update(ds.Tables[0]);
and it will work without any exception.

Regarding license. In case where it is impossible to put the license information straight to executable file, MySQLDirect .NET allows to create class libraries that can be used with certain executable files. To do this perform the following steps:

Create and setup file named licenses.licx (you no what to do with it).
Create a text file named licenses.config and place it next to licenses.licx.
In this file enumerate names of executable files that will work with the library, every file name on separate line, for example:

MyApplication.exe
MyAnotherApp.exe
ExtraTool.exe

You do not have to add the file licenses.config to project.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Postby marc » Thu 03 Aug 2006 15:17

thanks for the info about the license, it is a pity that the license.licx can not be bound inependently from a exe to a dll but doesn't matter.

I tryed the way you wrote and found that if the UpdateBatch size is not set to 1 at least this error occure.

I hope you can reproduce it with my code

Code: Select all
            DataSet ds = new DataSet("Test");
            DataTable dt = new DataTable("MyTable");

            dt.Columns.Add("test");

            dt.Rows.Add("Franz");
            dt.Rows.Add("Hans");
            dt.Rows.Add("Elke");
            dt.Rows.Add("Martin");
            ds.Tables.Add(dt);

            PgSqlConnection conn = new PgSqlConnection("User Id=postgres;Password=mypwd;Host=localhost;Database=TestDb;Schema=public;");
            conn.Open();

            PgSqlCommand cmd = new PgSqlCommand("INSERT INTO \"TestTable\"(\"name\") VALUES(?)");
            cmd.Parameters.Add(":p1", PgSqlType.VarChar, 10, "test");
            cmd.Connection = conn;

            ////generate null reference exception
            try
            {
                PgSqlDataAdapter da1 = new PgSqlDataAdapter();
                da1.InsertCommand = cmd;
                da1.RowUpdating += new PgSqlRowUpdatingEventHandler(da2_RowUpdating);
                da1.RowUpdated += new PgSqlRowUpdatedEventHandler(da2_RowUpdated);
                da1.Update(ds.Tables[0]);
            }
            catch (PgSqlException pg)
            {
                Console.WriteLine(pg.DetailMessage);
            }
            catch (Exception e2)
            {
                Console.WriteLine("Command 1: " + e2.Message);
            }



            //works fine
            //try
            //{
            //    PgSqlDataAdapter da2 = new PgSqlDataAdapter();
            //    da2.InsertCommand = cmd;
            //    da2.UpdateBatchSize =1;
            //    da2.RowUpdating += new PgSqlRowUpdatingEventHandler(da2_RowUpdating);
            //    da2.RowUpdated += new PgSqlRowUpdatedEventHandler(da2_RowUpdated);
            //    da2.Update(ds.Tables[0]);
            //}
            //catch (PgSqlException pg)
            //{
            //    Console.WriteLine(pg.DetailMessage);
            //}
            //catch (Exception e2)
            //{
            //    Console.WriteLine("Command 2: " + e2.Message);
            //}

            ////generate When batching, the command's UpdatedRowSource property value of UpdateRowSource.FirstReturnedRecord or UpdateRowSource.Both is invalid.
            try
            {
                PgSqlDataAdapter da3 = new PgSqlDataAdapter();
                da3.InsertCommand = cmd;
                da3.UpdateBatchSize = 2;
                da3.RowUpdating += new PgSqlRowUpdatingEventHandler(da2_RowUpdating);
                da3.RowUpdated += new PgSqlRowUpdatedEventHandler(da2_RowUpdated);
                da3.Update(ds.Tables[0]);
            }
            catch (PgSqlException pg)
            {
                Console.WriteLine(pg.DetailMessage);
            }
            catch (Exception e2)
            {
                Console.WriteLine("Command 3: " + e2.Message);
            }
        }

        void da2_RowUpdated(object sender, PgSqlRowUpdatedEventArgs e)
        {
           
            //throw new Exception("The method or operation is not implemented.");
        }

        void da2_RowUpdating(object sender, PgSqlRowUpdatingEventArgs e)
        {
           
           // throw new Exception("The method or operation is not implemented.");
        }
marc
 

Postby Alexey » Fri 04 Aug 2006 05:59

Yes, i now see this behaviour. As i wrote in one of the previous posts:
set UpdateBatchSize property to 1.

We are working on this.
Alexey
 
Posts: 2756
Joined: Mon 13 Mar 2006 07:43


Return to dotConnect for PostgreSQL