DateSet and DataTable connection

DateSet and DataTable connection

Postby degas » Mon 06 Dec 2010 14:26

I am using the dataset wizard to generate dataset with its datatables. I set the ShareConnection property to true.
The problem i have is that when i create a DataSet and set it's connection property, the datatables contained in it, don't change the connection. The connection of the datatable is the one that i use in the DataSetWizard.


Code: Select all
TestDataSet dsTest = new TestDataSet () { Connection = this.Connection };
dsTest.Connection.Open();               
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = dsTest.Table1.SelectCommand;
adapter.Fill(dsTest.Table1);


As you can see, i set the connection to the dataset, expecting that the Table1's connection property wil be set as well, but this is not the case. I have also set the Table1 connection like this

Code: Select all
TestDataSet dsTest = new TestDataSet () { Connection = this.Connection };
dsTest.Table1.Connection = this.Connection;
dsTest.Connection.Open();               
OracleDataAdapter adapter = new OracleDataAdapter();
adapter.SelectCommand = dsTest.Table1.SelectCommand;
adapter.Fill(dsTest.Table1);


but, if i debug it, i still see that the Select, Insert, Update and DeleteCommand, still have the connection set to the one in the DataSetWizard.

The version i am using is 5.20.33.0
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby Shalex » Wed 08 Dec 2010 16:26

I have extended your sample to reproduce the problem with 5.20.33:
Code: Select all
    //DDL:
    //CREATE TABLE DEPT (
    //  DEPTNO NUMBER(4) CONSTRAINT PK_DEPT PRIMARY KEY,
    //  DNAME VARCHAR2(14) ,
    //  LOC VARCHAR2(13)
    //);

    OracleConnection conn = new OracleConnection();
    //dataset was created with a different connection string
    conn.ConnectionString = "server=orcl1020;uid=scott;pwd=tiger;";

    DataSet1 dsTest = new DataSet1();
    dsTest.Connection = conn;
    dsTest.DEPT.Connection = conn;
    dsTest.Connection.Open();
    OracleDataAdapter adapter = new OracleDataAdapter();
    adapter.SelectCommand = dsTest.DEPT.SelectCommand;
    adapter.Fill(dsTest.DEPT);
    DataRow row = dsTest.DEPT.NewRow();
    row[0] = 50;
    row[1] = "asdf";
    row[2] = "asdf";
    dsTest.DEPT.AddDEPTRow((DataSet1.DEPTRow)row);
    dsTest.Update();

The insert, delete, and update commands were generated on the dsTest.Update(); line with the new connection object (conn).
1. How should we modify this sample to reproduce the problem?
2. On what line in the debug mode the connection of your update commands is incorrect?
I am using the dataset wizard to generate dataset with its datatables. I set the ShareConnection property to true.

The Share connection option is applied to the DataSet objects that are added to Form Designer.
Shalex
Devart Team
 
Posts: 7470
Joined: Thu 14 Aug 2008 12:44

Postby degas » Thu 09 Dec 2010 14:35

Ok, i didn't know that ShareConnection applied to the dataSet in the FormDesigner.

I have tried the code below and using the DbMonitor i can see that the SelectCommand is done with the connection define above (conn object) but the dsTest.Update() is done with the connection that the DataSet was generated from. I think this is wrong, because it is not using the proper connection.

In other words, the Select is done with the defined Connection and the Insert is done with the connection defined inside the DataSet.
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby degas » Fri 10 Dec 2010 20:44

Reading the code generated in the dataset, in the method InitClass it is creating a OracleConnection thre is no way to avoid this, so avery time a dataset is created, it uses this connection. If you change it to the dataset, it does not change the one in the datatables. And if you set the one in the datatables, you have to set it for all the OracleCommands.


Code: Select all
private void InitClass() {
            this.DataSetName = "DataSet1";
            this.Prefix = "";
            this.EnforceConstraints = true;
            this.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
            this.Connection = new Devart.Data.Oracle.OracleConnection("User Id=tgn;Password=mistraltg;Server=xxxx;Home=OraClient10g_home4;Per" +
                    "sist Security Info=True;");
            this.tableDept = new DeptDataTable(this.Connection);
            base.Tables.Add(this.tableDept);
        }
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby degas » Fri 10 Dec 2010 20:59

Could you please send me a sample project with a TypedDataSet and how to use it?
Thanks very much :D
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby Shalex » Tue 14 Dec 2010 17:43

I cannot reproduce the behaviour with 5.20.33 when the InitClass() method of the generated DataSet contains the initialization of the this.Connection property:
Code: Select all
            this.Connection = new Devart.Data.Oracle.OracleConnection("User Id=tgn;Password=mistraltg;Server=xxxx;Home=OraClient10g_home4;Per" +
                    "sist Security Info=True;");

1. Please make sure that your version of dotConnect for Oracle is 5.20.33. You can check it via the Tools > Oracle > About menu of Visual Studio.
2. Try generating your DataSet using the exact steps (settings) that are described in the Using DataSet Wizard tutorial. The DDL/DML script for the mentioned objects is available at \Program Files\Devart\dotConnect\Oracle\Samples\tables.sql. Is generated in this case DataSet without the initialised this.Connection property in the InitClass() method?

The DataSet documentation is here: http://www.devart.com/dotconnect/oracle/docs/?DataSets.html.
The corresponding sample is available at \Program Files\Devart\dotConnect\Oracle\Samples\General\.
Shalex
Devart Team
 
Posts: 7470
Joined: Thu 14 Aug 2008 12:44

Postby degas » Tue 14 Dec 2010 19:23

Shalex, i am sorry for the incovinience. Let me say that i have tried to get this working for a very long time, with no success.
Let me answer your question

1) The version is 5.20.33.0 Professional Edition
2) I generated the typed dataset for the 2 tables emp and dept using the wizard (next to the end of the wizard). With out touching a single line of code, the InitClass method looks like the folowing


Code: Select all
        [System.Diagnostics.DebuggerNonUserCodeAttribute()]
        private void InitClass() {
            this.DataSetName = "DataSet";
            this.Prefix = "";
            this.EnforceConstraints = true;
            this.SchemaSerializationMode = System.Data.SchemaSerializationMode.IncludeSchema;
            this.Connection = new Devart.Data.Oracle.OracleConnection("User Id=sistema;Password=mistralsi;Server=SZICOM.LIT.DESA;Home=OraClient10g_home4" +
                    ";Persist Security Info=True;");
            this.tableDEPT = new DEPTDataTable(this.Connection);
            base.Tables.Add(this.tableDEPT);
            this.tableEMP = new EMPDataTable(this.Connection);
            base.Tables.Add(this.tableEMP);
            System.Data.ForeignKeyConstraint fkc;
            fkc = new System.Data.ForeignKeyConstraint("FK_DEPTNO", new System.Data.DataColumn[] {
                        this.tableDEPT.DEPTNOColumn}, new System.Data.DataColumn[] {
                        this.tableEMP.DEPTNOColumn});
            this.tableEMP.Constraints.Add(fkc);
            fkc.AcceptRejectRule = System.Data.AcceptRejectRule.None;
            fkc.DeleteRule = System.Data.Rule.None;
            fkc.UpdateRule = System.Data.Rule.None;
            this.relationFK_DEPTNO = new System.Data.DataRelation("FK_DEPTNO", new System.Data.DataColumn[] {
                        this.tableDEPT.DEPTNOColumn}, new System.Data.DataColumn[] {
                        this.tableEMP.DEPTNOColumn}, false);
            this.Relations.Add(this.relationFK_DEPTNO);
        }


The problem comes when i want to use a different connection than the one that is is beaing created in the InitClass method. I have concluded (i may be wrong) that the DataSet's connection is pass to the DataTable in their constructor. Then each datatable constructor's passes this connections to each command (SelectCommand, InsertCommand, UpdateCommand and DeleteCommand ), as seen in the code generated in the following method:


Code: Select all
          public EMPDataTable(Devart.Data.Oracle.OracleConnection connection) {
                this.TableName = "EMP";
                this.Name = "EMP";
                this.Connection = connection; <--This connection is referencing the one created for the dataset.
                this.BeginInit();
                this.InitClass();
                this.EndInit();
            }



This causes that the OracleDataTable (Emp and Dept) passes to each of the four OracleCommand (Select, Insert, Update,Delete) the connection of the dataset.
So when at runtime you create a dataset and set the DataSet's Connection property to a new OracleConnection. The DataTables inside the DataSet still reference the old OracleConnection (created in the InitClass method). Then (according to your example) i have to set the SelectCommand.Connectio to the new OracleConnection. But the problem is that when you call the Update method, the underlaying commands (Insert, Update, Delete) still reference the old connection and not the new one.

As an example i will post the code you wrote and the one i have to write to achieve what i want

Code: Select all
OracleConnection conn = new OracleConnection();
    //dataset was created with a different connection string
    conn.ConnectionString = "server=orcl1020;uid=scott;pwd=tiger;";

    DataSet1 dsTest = new DataSet1();
    dsTest.Connection = conn;
    dsTest.DEPT.Connection = conn;
    dsTest.Connection.Open();
    OracleDataAdapter adapter = new OracleDataAdapter();
    adapter.SelectCommand = dsTest.DEPT.SelectCommand;
    adapter.Fill(dsTest.DEPT);
    DataRow row = dsTest.DEPT.NewRow();
    row[0] = 50;
    row[1] = "asdf";
    row[2] = "asdf";
    dsTest.DEPT.AddDEPTRow((DataSet1.DEPTRow)row);
    dsTest.Update();



and this is my code

Code: Select all
OracleConnection conn = new OracleConnection();
    //dataset was created with a different connection string
    conn.ConnectionString = "server=orcl1020;uid=scott;pwd=tiger;";

    DataSet1 dsTest = new DataSet1();
    dsTest.Connection = conn;
    dsTest.DEPT.Connection = conn;

    dsTest.DEPT.InsertCommand.Connection = conn; <--Otherwise it point's to the old connection.
    dsTest.DEPT.UpdateCommand.Connection = conn; <-- If you debug, you will see that the underlaying connection
    dsTest.DEPT.DeleteCommand.Connection = conn; <-- is the one define in the InitClass

    dsTest.Connection.Open();
    OracleDataAdapter adapter = new OracleDataAdapter();
    adapter.SelectCommand = dsTest.DEPT.SelectCommand;
    adapter.Fill(dsTest.DEPT);
    DataRow row = dsTest.DEPT.NewRow();
    row[0] = 50;
    row[1] = "asdf";
    row[2] = "asdf";
    dsTest.DEPT.AddDEPTRow((DataSet1.DEPTRow)row);
    dsTest.Update();
[/quote]
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby AndreyR » Thu 16 Dec 2010 12:12

We failed to reproduce the problem. Could you please send us (support * devart * com, subject "DataSet Shared Connection") a small test project illustrating the issue? This should help in our investigation.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby degas » Fri 17 Dec 2010 13:03

I have already sent you a test proeject. Could you please confirm this here or an email.
Thanks
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby AndreyR » Fri 17 Dec 2010 13:34

Thank you for the project, we have found the error. I have sent you a workaround already.
AndreyR
Devart Team
 
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Postby degas » Wed 29 Dec 2010 13:26

Are you planning to fix version this? If so for what versions?
Thanks
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby Shalex » Mon 03 Jan 2011 13:07

Have you received our e-mail with a temporary workaround?

The problem is fixed. We plan to release a new build next week. I will post here when the next build is available to download.
Shalex
Devart Team
 
Posts: 7470
Joined: Thu 14 Aug 2008 12:44

Postby degas » Mon 03 Jan 2011 13:54

Yes i have received the email with the workaround and have implemented with no problem.

The only thing is that when i must remember not to use the DataSet.Connection property and use a method that i have implemented with your code (the one you send me was using the constructor)
degas
 
Posts: 77
Joined: Mon 16 Feb 2009 18:36
Location: Argentina

Postby Shalex » Wed 19 Jan 2011 17:42

The latest build of dotConnect for Oracle (6.00.86) includes a fix for the connection problem in DataSet.

It can be downloaded from http://www.devart.com/dotconnect/oracle/download.html (trial version) or from Registered Users' Area (for users with valid subscription only): http://secure.devart.com/ .
For more information, please refer to http://www.devart.com/forums/viewtopic.php?t=19968 .
Shalex
Devart Team
 
Posts: 7470
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for Oracle