C# and Runtime Datasets

[wendas]

I am just learning Datasets, and as luck ALWAYS has it can not start out simple. I am pulling from an ODBC file which can be connected to on the Dataserver or Table adaptor, but then they come in with no tables.

So I am forced to work with runtime datasets. My first attempt is interesting. I created one SQL statement.. If put into a Datareader it works fine. If put into a Strong typed dataset (either built in the Dataset Designer window or built with a Fillschema) I get nothing. But if I put it to an untyped dataset I pull the info.

Here's the questions that my studies have not answered.

Does the strong Typed column field names HAVE to match the names in the DataAdaptor?.. I guess my fillschema would, but my other one does not since the SQL statement is a cross join, so the column names are TR.CustomerID,TR.TranNumber etc.. (as shown with the display of the untyped Dataset that pulls info).. I would prefer to have them move into fields simply labeled CustomerID, TranNumber..

Here is an example of one of the two Typed Datasets that is missing info.. Do you see anything that maybe the typed DS needs the untyped did not?

InitializeComponent();
            // create an OdbcConnection object and connect it to our sample data source
            OdbcConnection DbConnection = new OdbcConnection("Provider=MSDASQL.1;DSN=Vision Data;UID=user;PWD=pswd");

            // Using that command, we can issue a Query and create an OdbcDataReader: 
 
            string strSQL = "SELECT TR.Tran_num, AM.Acct_no, AM.Cust_type ”
            + "FROM artransaction  TR "
            + "INNER JOIN armaster  AM ON TR.Acct_no = AM.Acct_no "
            + "WHERE TR.Profit_no = 1 and TR.Tran_num = 842049";

            OdbcDataAdapter da = new  OdbcDataAdapter(strSQL, DbConnection);
  
            //DataSet ds = new DataSet("dsarTransaction");
            dsTestDS1 ds = new dsTestDS1();

            // need to move Table adaptor info into the dataset HERE 
 // fields typed in the dataset designer does not work.
            da.Fill(ds, "typedDS");
 
            if (ds.HasChanges())
            {
               CreateViewer(ds.Tables[0]);
            }

            DataTableReader dr = ds.CreateDataReader(ds.Tables[0]);
            dr = ds.CreateDataReader(ds.Tables[0]);
            MessageBox.Show("Dataset contains " + dr.FieldCount.ToString() + " Fields " +
                    " and " + dr.RecordsAffected.ToString() + " Records ");

            while (dr.Read())
            {
                MessageBox.Show(dr.GetInt32(0).ToString());
            }

[Ramy Mahrous]

Does the strong Typed column field names HAVE to match the names in the DataAdaptor?

Try that a lot to know the answer.

I don't know your problem with DataSets, but try to generate someone from SQL Server or Access File, and tell me what you got.

[JerryShaw]

<<Does the strong Typed column field names HAVE to match the names in the DataAdaptor>>

The matching column names of a typed datatable will get populated, and the others (non-matching) will not get populated.

All column names must be unique, and they do not contain the alias prefix.

You can manually create the typed datatable and populate it from the adapter. Columns in a typed datatable do not need to be in the same order as what is returned from the server.

OdbcConnection DbConnection = new OdbcConnection("Provider=MSDASQL.1;DSN=Vision Data;UID=user;PWD=pswd");

             string strSQL = "SELECT TR.Tran_num, AM.Acct_no, AM.Cust_type ”
            + "FROM artransaction  TR "
            + "INNER JOIN armaster  AM ON TR.Acct_no = AM.Acct_no "
            + "WHERE TR.Profit_no = 1 and TR.Tran_num = 842049";

            OdbcDataAdapter da = new  OdbcDataAdapter(strSQL, DbConnection);
  
            DataTable untypedDT = new DataTable();
            da.Fill(untypedDT);
            
            foreach(DataRow row in untypedDT.Rows)
            {
                MessageBox.Show(untypedDT[0].ToString());
            }

[wendas]

Well there is my problem.. If my untyped dataset creates the name of TR.trannum... I expect that is what my typed dataset is trying to join up with..

In SQl I tried to change the name to Trannum by doing

Select TR.Trannum AS trannum etc.. But the DB in this odbc, doesn't know what AS is... The DB is "UNIFY" not surprised if you never heard of it, I don't think anyone has except the vender of this software package. The ODBC couldn't do joins and filters until very recently, with the upgrade of that I was so hopeful that we could now get to the data without having to pull every record we need out table by table into SQL so we could join and filter... Still hitting perplexing road blocks though.

I am unsure why though if I use the FillSchema to get the strong datatype.. Or create the Dataset with the TR.Trannum extension added, it can't find it's matching name, unless it figures the TR on the dataset in a different way then the TR on the dataadaptor data, although they look the same visually.

1 step forward, two steps back.

[wendas]

PS.. Thanks for the suggestion about trying the same commands in SQL.. I have though of that also, but got pulled off of the "playing" to get this thing working and onto a hot item at work, so just haven't been able to get back to it.

i take it that if this was SQL it would know to strip the TR extension off of the name, and/or I could use the "TR.Trannum as Trannum" in my SQL Statement.