TableAdapters.....

TableAdapters.....

• BMWSauberF1

  Hi Bill,

  First, there's a small error in my code above. The class CustomersTableAdapter should have been declared partial -- but it looks like you already caught that and corrected it in your code, so on to the more important issue...

  You still need to wrap your partial class declaration inside a namespace declaration:

  Namespace NorthwindDataSetTableAdapters

  <Your partial class code>

  End Namespace

  I believe that otherwise you end up with the TableAdapter classes inside a nested namespace (NorthwindDataSetTableAdapters.NorthwindDataSetTableAdapters) while your partial class is in the root namespace (NorthwindDataSetTableAdapters).

  
  
  

• Cyber Sinh

  Hi again Bill,

  You're right that wiring up classes to your data source requires some extra work. This is one of the scenarios that we've spent some time thinking about for future versions of Visual Studio. Hopefully the new DLinq features along with the DLinq Designer will go a long way toward removing this burden -- although it won't be within the typed DataSet model. Currently there is a plan to release a CTP of these new features in late April. If you have time, you should check it out and send us feedback on whether you find it helpful.

  I tried your scenario with the ObjectBindingSource.Updated event and I do see that it returns -1 for AffectedRows. Interestingly, I am able to call the same Update method directly on the DataSet and I do get the correct return value. I'm not a web expert as my team deals mostly with client apps, but it looks like you've found a bug. Could I ask you to you file a bug at http://lab.msdn.microsoft.com/productfeedback/ That way it will be routed to the correct team, and they can provide you information about whether this is a known issue and if there are easy workarounds. The repro information you posted here should help the web team to see the issue.

  Thanks,

  
  
  

• Luis Simoes

  although it is not recommended nor supported by MS...you can go into the designer generated code and change the scope yourself!

  (as I now see you did )

  
  
  

• TwilightsZone

  Jay, I've progressed where I've written all my code in my project and testing was going flawlessly except for one major sticking point I see about TableAdapters...

  There is not a TableMapping property I can easily modify to sync up columns in the datatable with a parameter list.

  For instance, while using the Component Designer and creating a dataadapter, there is a TableMapping collection where you can set the table columns with the associatged datasource columns. This creates a parameter list where you just need to pass down the contents of the datatable and when the "Update" statement of the dataadapter takes place, the appropriate updates inside the datatable will take place in accordance with the rows' rowstate.

  <code>

  'generated code

  Me.dapSrchBatch.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "SrchBatch", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("BatchID", "BatchID"), New System.Data.Common.DataColumnMapping("SrchID", "SrchID"), New System.Data.Common.DataColumnMapping("EngineID", "EngineID"), New System.Data.Common.DataColumnMapping("RecrdSrchd", "RecrdSrchd"), New System.Data.Common.DataColumnMapping("RecrdsFound", "RecrdsFound"), New System.Data.Common.DataColumnMapping("SrchTime", "SrchTime")})})

  'non-generated cocde

  Dim intRecdsAffected As Int16 = SQLServerDataAccess.ExecuteSQLNonQuery(Connection, dapSrchBatch, DstSrchBatch1)

  </code>

  On the contrary, the TableAdapter has no associated TableMapping property that is easily editable like the one in the Component Designer. See below generated code:

  <code>

  tableMapping.SourceTable = "Table"

  tableMapping.DataSetTable = "SrchBatch"

  tableMapping.ColumnMappings.Add("SrchID", "SrchID")

  Me._adapter.TableMappings.Add(tableMapping)

  Me._adapter.InsertCommand = New System.Data.SqlClient.SqlCommand

  Me._adapter.InsertCommand.Connection = Me.Connection

  Me._adapter.InsertCommand.CommandText = "dbo.InsertSrchBatch"

  Me._adapter.InsertCommand.CommandType = System.Data.CommandType.StoredProcedure

  Me._adapter.InsertCommand.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, 10, 0, Nothing, System.Data.DataRowVersion.Current, false, Nothing, "", "", ""))

  </code>

  As you can see, there is no column collection inside the tablemapping property. I suppose I could expose the TableMapping property of the TableAdapter (like we discussed before), and add the column collection to the TableAdapter, but that amounts to a lot of manual coding that I thought the TableAdapter would have been able to handle through generated code.

  Is this the only way to go with it Will it be enhanced in a future release

  Thanks in advance,

  Bill

  
  
  

• Luu Sinh Ngoc

  Jay, I tried that and it didn't help. I'm still getting the exact same design time compilation error as in my previuous post.

  My partial class now looks like this:

  <code>

  Namespace NorthwindDataSetTableAdapters
  Partial Public Class CustomersTableAdapter

  Public ReadOnly Property DataAdapter() As SqlClient.SqlDataAdapter
  Get
  Return Me.Adapter
  End Get
  End Property

  End Class
  End Namespace

  </code>

  Part of the generated code looks like this:

  <code>

  Namespace CustomersTableAdapterTableAdapters
  
  <System.CodeDom.Compiler.GeneratedCodeAttribute("System.Data.Design.TypedDataSetGenerator", "2.0.0.0"), _
  System.ComponentModel.DesignerCategoryAttribute("code"), _
  System.ComponentModel.ToolboxItem(true), _
  System.ComponentModel.DataObjectAttribute(true), _
  System.ComponentModel.DesignerAttribute("Microsoft.VSDesigner.DataSource.Design.TableAdapterDesigner, Microsoft.VSDesigner"& _
  ", Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"), _
  System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")> _
  Partial Public Class CustomersTableAdapter1
  Inherits System.ComponentModel.Component
  
  Private WithEvents _adapter As System.Data.SqlClient.SqlDataAdapter
  
  Private _connection As System.Data.SqlClient.SqlConnection
  
  Private _commandCollection() As System.Data.SqlClient.SqlCommand
  
  Private _clearBeforeFill As Boolean
  
  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
  Public Sub New()
  MyBase.New
  Me.ClearBeforeFill = true
  End Sub
  
  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
  Private ReadOnly Property Adapter() As System.Data.SqlClient.SqlDataAdapter
  Get
  If (Me._adapter Is Nothing) Then
  Me.InitAdapter()
  End If
  Return Me._adapter
  End Get
  End Property
  

  </code>

  I tried encapsulating it inside the CustomersTableAdapterTableAdapters namespace as well which also didn't help.

  I can zip up and email the whole project if you like. It's not big at all.

  If I can expose that data adapter, I'd be able to use TableAdapters. Otherwise, I'm forced to code the way I have been in my initial post.

  Any help would be appreciated.

  
  
  

• LesioS

  That's correct, but I do not want to do this since every time I regenerate the adapter, it will revert back to the original code.
  
  

• ivalley

  Jay, any ideas yet on my latest post

  Thanks so much....

  
  
  

• Ben Griffiths

  Hi Bill,

  Sorry for the slow response... I don't make it out to the forums every day.

  In your particular case, you would want to place the partial class inside of the namespace CustomersTableAdapterTableAdapters. From looking at your posted code, I think you also need to make the class declaration be Partial Class CustomersTableAdapter1. Both the namespace and the class name have to match exactly -- that's how the compiler figures out which class you are extending.

  If that still doesn't work, post back here and I'll get the zipped project from you. I'm sure that we can make this work.

  
  
  

• ntintel

  Jay, it just so happened that the datatable I was dealing with, didn't have some of the columns mapped to the datasource, so I manually mapped them like you suggested.

  The rest of tableadapters automatically generated the appropriate datasource columns.

  I did see in the generated code, that it did place them in the tablemapping column collection, so I'll try my test again, but I'm pretty sure it will work this time based on the change I made.

  My mistake....

  
  
  

• Mic-1

  Jay, everything works fine now... Thanks a lot for your insight.

  However, there are a couple of things I wish to note and get your feedback on.

  When using a STD (strongly typed dataset), the SELECT functionality works as expected as evidenced in the code below:

  <code>

  <DataObjectMethod(DataObjectMethodType.Select)> _

  Public Function SelectCustomers() As dstCustomer

  Try

  Dim DstCustomers1 As New dstCustomer

  dapCustomer.SelectCommand = cmdSelectCustomers

  SQLServerDataAccess.GetSQLData(Connection, dapCustomer, DstCustomers1.Customers)

  Return DstCustomers1

  Catch ex As Exception

  Throw ex

  Finally

  dapCustomer.Dispose()

  End Try

  End Function

  </code>

  However, when using the other update methods, you still must use a "Helper" class in order for the ObjectDataSource to interact with. You simply can't use the STD by itself, which I feel would make it a lot more efficient if the ObjectDataSource was able to interact with a STD. Check out the code below of what I needed to do to get an UPDATE to work:

  <code>

  Namespace dstCustomerTableAdapters

  Partial Public Class CustomersTableAdapter

  .

  .

  .

  <DataObjectMethod(DataObjectMethodType.Update)> _

  Public Function UpdateCustomer(ByVal clsCustomer As dstCustomer) As Boolean

  Try

  Dim intRecdsAffected As Integer

  Dim DstCustomers1 As New dstCustomer

  Dim dr As dstCustomer.CustomersRow = DstCustomers1.Customers.NewCustomersRow

  dr.Address = clsCustomer.Address

  dr.City = clsCustomer.City

  dr.CompanyName = clsCustomer.CompanyName

  dr.ContactName = clsCustomer.ContactName

  dr.ContactTitle = clsCustomer.ContactTitle

  dr.Country = clsCustomer.Country

  dr.CustomerID = clsCustomer.CustomerID

  dr.Fax = clsCustomer.Fax

  dr.Phone = clsCustomer.Phone

  dr.PostalCode = clsCustomer.PostalCode

  DstCustomers1.Customers.AddCustomersRow(dr)

  DstCustomers1.Customers.AcceptChanges()

  DstCustomers1.Customers.Item(0).SetModified()

  Dim blnOK As Boolean = SQLServerDataAccess.SaveSQLData(Connection, dapCustomer, DstCustomers1.Customers, SQLServerDataAccess.UpdateType.ErrorsOccurred, intRecdsAffected, Nothing)

  Return blnOK

  Catch ex As Exception

  Throw ex

  End Try

  End Function

  End Class

  End Namespace

  Partial Public Class dstCustomer

  Private sCustomerID As String

  Private sCompanyName As String

  Private sContactName As String

  Private sContactTitle As String

  Private sAddress As String

  Private scity As String

  Private sRegion As String

  Private sPostalCode As String

  Private sCountry As String

  Private sPhone As String

  Private sFax As String

  Public Property CustomerID() As String

  Get

  Return sCustomerID

  End Get

  Set(ByVal value As String)

  sCustomerID = value

  End Set

  End Property

  Public Property CompanyName() As String

  Get

  Return sCompanyName

  End Get

  Set(ByVal value As String)

  sCompanyName = value

  End Set

  End Property

  Public Property ContactName() As String

  Get

  Return sContactName

  End Get

  Set(ByVal value As String)

  sContactName = value

  End Set

  End Property

  Public Property ContactTitle() As String

  Get

  Return sContactTitle

  End Get

  Set(ByVal value As String)

  sContactTitle = value

  End Set

  End Property

  Public Property Address() As String

  Get

  Return sAddress

  End Get

  Set(ByVal value As String)

  sAddress = value

  End Set

  End Property

  Public Property City() As String

  Get

  Return scity

  End Get

  Set(ByVal value As String)

  scity = value

  End Set

  End Property

  Public Property Region() As String

  Get

  Return sRegion

  End Get

  Set(ByVal value As String)

  sRegion = value

  End Set

  End Property

  Public Property PostalCode() As String

  Get

  Return sPostalCode

  End Get

  Set(ByVal value As String)

  sPostalCode = value

  End Set

  End Property

  Public Property Country() As String

  Get

  Return sCountry

  End Get

  Set(ByVal value As String)

  sCountry = value

  End Set

  End Property

  Public Property Phone() As String

  Get

  Return sPhone

  End Get

  Set(ByVal value As String)

  sPhone = value

  End Set

  End Property

  Public Property Fax() As String

  Get

  Return sFax

  End Get

  Set(ByVal value As String)

  sFax = value

  End Set

  End Property

  End Class

  </code>

  While interacting with a STD (as sampled below in a 1.1 code snippet):

  <code>

  Public Function UpdateCustomer(ByVal DstCustomer1 As dstCustomer) As Boolean

  </code>

  the presentation layer code simply created the datarow and passed it down to the middle tier logic. This could be achieved because you could set a STD as the datasource to a datagrid. Now, I know you can still do that with the gridview, but if you do, you would lose all the "new" functionality that the objectdatasource coupled with the gridview supports. You would have to do this all manually.

  Thus, you need to use the Helper class in order to populate the STD datarow (which already has its parameters set up via the tablemapping).

  It would be great if you didn't need this Helper class and the objectdatasource/gridview would simply interact with the STD.

  Another separate issue is in the code below I use to update the data:

  <code>

  Dim blnOK As Boolean = SQLServerDataAccess.SaveSQLData(Connection, dapCustomer, DstCustomers1.Customers, SQLServerDataAccess.UpdateType.ErrorsOccurred, intRecdsAffected, Nothing)

  </code>

  The intRecdsAffected comes back with a value of 1 (as expected) from my DB I/O layer. However it is not properly reflected in the prestation layer code with the ObjectDataSource as evideneced below:

  <code>

  Protected Sub ObjectDataSource1_Updated(ByVal sender As Object, _

  ByVal e As System.Web.UI.WebControls.ObjectDataSourceStatusEventArgs) _

  Handles ObjectDataSource1.Updated

  e.AffectedRows = CType(e.ReturnValue, Integer)

  End Sub

  </code>

  The e.AffectedRows comes back as a -1 (e.ReturnValue is True). If I use the above code without a STD and simply with the use of Generics and the Helper class, it comes back as a 1 (as expected).

  I could check my intRecdsAffected on my middle tier function and throw and error if it's not a 1.

  Anyway, the e.AffectedRows property is not coming back correctly.

  Thanks so much for your help and insight. I wanted to point this out as the only "sticking" points left with using STD's and the ObjectDataSource.

  Any comments would be appreciated.

  
  
  

• zepgrirl

  Jay, that did it!

  Here's a sample of the code I created for the partial class hanging off the xsd file

  <code>

  Namespace CustomersTableAdapterTableAdapters
  Partial Public Class CustomersTableAdapter1

  Public ReadOnly Property DataAdapter() As SqlClient.SqlDataAdapter
  Get
  Return Me.Adapter
  End Get
  End Property

  End Class
  End Namespace

  </code>

  The following code is just a sample of what I'm doing in a winform:

  <code>

  Imports DataAccess.Data

  Public Class Form1

  Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

  'retrieve data
  Dim ta As New CustomersTableAdapterTableAdapters.CustomersTableAdapter1
  Dim tblCustomers As New CustomersTableAdapter
  SQLServerDataAccess.GetSQLData(ta.Connection, ta.DataAdapter, tblCustomers.Customers)

  'update data
  Dim intrecdsaffected As Int16 = SQLServerDataAccess.ExecuteSQLNonQuery(ta.Connection, ta.DataAdapter.UpdateCommand)

  'update data with a transaction
  Dim trns As SqlClient.SqlTransaction
  trns = ta.Connection.BeginTransaction(IsolationLevel.Serializable)
  Dim bln As Boolean = SQLServerDataAccess.SaveSQLData(ta.Connection, ta.DataAdapter, tblCustomers.Customers, SQLServerDataAccess.UpdateType.ErrorsOccurred, intrecdsaffected, trns)
  trns.Commit()

  End Sub
  End Class

  </code>

  Now, I'll be able to use TableAdapters in my project with the DataAdapter exposed as an object to be passed down to a DB I/O layer.

  Thanks so much...

  
  
  

• sean_morrison

  Hi Bill,

  You could expose the private Adapter member by writing a partial class that returned it through a public property. For example, if you were using the Northwind Customers table, the code would look like this:

  Namespace NorthwindDataSetTableAdapters

  Public Class CustomersTableAdapter

  Public ReadOnly Property DataAdapter() As SqlClient.SqlDataAdapter
  Get
  Return Me.Adapter
  End Get
  End Property

  End Class
  End Namespace

  As long as you put this in a separate class file, it will never be overwritten by the generator.

  I hope this helps.

  
  
  

• saisiri

  Jay, I tried that, but I still am getting a design-time compile error saying that "Adapter is not a member of NorthwindDataSetTableAdapters.CustomersTableAdapter"

  I have the following code in a Partial class which hangs off the CustomersTableAdapter.xsd file:

  <code>

  Partial Public Class CustomersTableAdapter

  Public ReadOnly Property DataAdapter() As SqlClient.SqlDataAdapter
  Get
  Return Me.adapter
  End Get
  End Property

  End Class

  </code>

  Inside the generated file is the following code:

  <code>

  
  <System.Diagnostics.DebuggerNonUserCodeAttribute()> _
  Private ReadOnly Property Adapter() As System.Data.SqlClient.SqlDataAdapter
  Get
  If (Me._adapter Is Nothing) Then
  Me.InitAdapter()
  End If
  Return Me._adapter
  End Get
  End Property
  

  </code>

  The project name is called "NorthwindDataSetTableAdapters" and the TaqbleAdapter is called "CustomersTableAdapter"

  It still appears that the dataadapter property still needs to be generated as a Public object in order for me to access it.

  Isn't this true or am I doing something incorrectly defining the Partial class

  
  
  

• Ed209

  Hi Bill,

  I'm not sure I understand your scenario but here's generally how it works with TableAdapters...

  We actually do add the table mappings to the underlying DataAdapter, but it's done automatically for you. In Visual Studio 2005, when you change a column name on the DataTable (that's the top part of the object on the designer) we create a table mapping that maps your DataTable column to the column in the database. You can manually change that mapping by changing the Source property for the column in the DataSet designer.

  Any queries you add to the TableAdapter (by right-clicking on it in the designer and selecting Add Query) will correctly fill the DataTable using this mapping. The underlying DataAdapter should have the TableMappings property set after initializing. From the code you posted above, were you expecting more than just the "SrchID" column to be mapped

  
  
  
• TableAdapters.....