Create TableAdapter SQL Syntax In Code

Question

I have been using typed datasets and tableadapters created with the designer wizard and have been doing ok.

I now have a big issue. The database schema changed quite a bit and I cannot for the life of me get the typed dataset (DAL) to recreate the changes as I am getting errors like objects missing from the dataset (There have been quite a few changes outside my knowledge). There are also over 100 tables.

Anyway there are say 20 select SQL queries that have been created by hand and I read somewhere that I could create these in code that would allow me to regenerate the typed dataset with the wizard and not lose these 20 tableadapter queries. The insert/edit/delete statements I can reuse in the newly created typed dataset. In the past when say a new column was created in a table rerunning the wizard would pick it up but this time it's a mess.

Can anyone direct me where to hold these tableadapters and how to code them with a simple SQL statement?

 

 

---

John F Lundy

Answer 1

Hello,

Thank you for posting.

Based on you've said, I don't know what your program was. Generally speaking, I suggest you can check the following two articles about TableAdapter.

How to: Update Records in a Database

TableAdapter COnfiguration Wizard

In addition, if the above information didn't help you solve your question, please provide more detailed code snippet and error information, which may help us analyze your question easier.

Best Regards,

---

Larcolais Gong[MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

I have checked the articles but cannot find what I'm looking for.

I want to be able to be able to save TableAdapter queries that I create outside of the wizard in a .cs file so that I do not loose them if I have to recreate the typed dataset schema again.

Something like this.

MyCustomerTableAdapter

FillByState = 'Select * from Customers where state = @State'

I want to save this in a partial class so I don't loose it if I have to create the complete schema again.

Not sure of the syntax and where/how to create it.

Any help would be appreciated.

 

 

---

John F Lundy

Answer 3

John,

First -- I don't have a definitive and immediate answer to your questions, so I hope that someone can chime in and provide a bit more help for you other than what I am going to suggest.

I think you are experiencing some of the limitations of the designer thingies.  Honestly, I am unsure if the designers are an appropriate choice for a production tier'ed/layered application -- personally, I DO NOT think they are and do not use them in ANY production application environment.  I am sure that there are instances/cases where this is true, but I simply do not see this to be the norm in my neck of the woods. 

It seems that you have considered the value in creating seperation between your data and app tiers, but are now running into those pesky issues that the wizards generate.  Have a look at Bonnie' series of posts regarding techniques to break you free from the designers, yet retain the benefits of Strongly Typed Datasets:

http://geek-goddess-bonnie.blogspot.com/2009/09/dataaccess-part-i.html
http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-ii.html
http://geek-goddess-bonnie.blogspot.com/2009/10/dataaccess-part-iii.html

I have been using this technique for some time now and it works wonderfully in a UI-BL-DAL architecture, allowing those Datasets to be passed between the tiers of the app.  I won't mislead you though -- it requires some work on the database end too, so you will either have to get your T-SQL skills built up a little or work with the DBA (if you have one) to build the T-SQL that the designers were doing for you in the past.

---

James Crandall ~ http://javitechnologies.com Spatial Database Solutions

Answer 4

Thanks James, I read Bonnies articles a while back and got stuck on creating a basic typed dataset outside the designer.

At the moment I am having to work within time parameters to build this WPF app and will get into the finer detail of creating all my SQL Statements another time. For the time being the generated typed dataset is working fine if I do not have to recreate it from scratch and lose all my tailor made tableadapters.

I did read somewhere (Cannot find it now) that you can create tableadapters with the appropriate SQL statements outside the designer in a .cs fiile in the DAL project. I have managed to drop a table adapter on the design surface of the .cs file and get the code below automatically generated. However I do not know how to create a new SQL Select statement inside this code.

I need a new FillByState method and the appropriate command text.

Any help would get me out of the proverbial.

 

public partial class MyDataSet {
    private MyDataSetTableAdapters.CUSTOMERTableAdapter CUSTOMERTableAdapter1;

    private void InitializeComponent()
    {
      this.CUSTOMERTableAdapter1= new MyNamespace.MyDataSetTableAdapters.CUSTOMERTableAdapter ();
      ((System.ComponentModel.ISupportInitialize)(this)).BeginInit();
      // 
      // CUSTOMERTableAdapter1
      // 
      this.CUSTOMERTableAdapter1.ClearBeforeFill = true;
      ((System.ComponentModel.ISupportInitialize)(this)).EndInit();

    }
  }

 

 

---

John F Lundy

Answer 5

James -- Thanks for plugging my blog articles! I appreciate it.

John -- Not sure where you got stuck reading my stuff ... but I've got some pretty helpful posts on creating a Typed DataSet without all the TableAdapter junk (http://geek-goddess-bonnie.blogspot.com/2010/04/create-xsd.html) ... I don't know if that's the one you read and got stuck on (I had some comment exchanges with someone named John ... was it you?)

And now, to get to your actual question about the dreaded TableAdapters ... everything you need has been put into your DataSet Designer-generated file. You could take the TableAdapter classes out of that Designer.cs file and put them elsewhere in a separate .cs file. If you do that, you can use my techniques described in that link to my blog post to create an .xsd and a Typed DataSet without any TableAdapters. When you do this, the DataSet.Designer.cs file will contain only the DataSet stuff. But, since you've saved the TableAdapter classes that the designer had previously generated for you in another class, then you can still use the TableAdapters *and* they're available to you to modify yourself ... that sounds like that's what you wanted to do, right?

---

~~Bonnie Berent [C# MVP]

geek-goddess-bonnie.blogspot.com

Answer 6

Hi Bonnie

Yep that was me and your articles got me going on seperation of the entities DAL,BAL,UI.

I have about 100 tables and could not work out on the article posted on the blog how to create all the tables in one go to create the xsd. So I started down the designer wizard route with the idea that I could create the typed dataset within the wizard and create any additions to the typed dataset (New Tableadapters outside) I'm now hitting a brick wall with issues. Firstly any new tableadapters I create inside the wizard the code seems to be all over the place so I cannot lift it easily.

I'm plodding away trying to point the dataset to an exact copy of the database with a different name. I have updated the connection string in the settings and app.config file to point to a different catalog:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <configSections>
  </configSections>
  <connectionStrings>
    <add name="AlpsNamespace.Properties.Settings.AlpsConnection"
      connectionString="Data Source=LAPTOP\SQL2008;Initial Catalog=DAL-DB;Integrated Security=True"
      providerName="System.Data.SqlClient" />
  </connectionStrings>
</configuration>

 

but now am getting the same issue as this below and cannot find an answer, I could scream!

http://stackoverflow.com/questions/5253667/element-x-in-the-dataset-references-an-object-missing-from-the-database

Any help please :-(

 

 

 

 

 

---

John F Lundy

Answer 7

I have about 100 tables and could not work out on the article posted on the blog how to create all the tables in one go to create the xsd.

---

John F Lundy

John,

You do this by setting up your DataAdapter and SQL String, or what I do is use the StoredProcedures that are on the database (if this is for SQLServer) -- this is the best approach probably.  Here is the full code behind a WindowsForm as done in Bonnie's suggested approach.

CommandText = Name of the Sproc

fireConn = just another Class that contains my db connection params.

 

Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class frm_dsPrint_SubRpt_EBU
  Private sqlCn As New SqlConnection()
  Private connSvc As fireConn

  Public Sub New()
    ' This call is required by the Windows Form Designer.
    InitializeComponent()

    ' Add any initialization after the InitializeComponent() call.
    connSvc = New fireConn
    sqlCn = connSvc.sqlConnFire
    CreateDataSet()
  End Sub
  Private Sub CloseConnection()
    If sqlCn.State = ConnectionState.Open Then
      sqlCn.Close()
    End If
  End Sub
  Private Sub CreateDataSet()

    Try

      'Fire_DALC_PrintSubRptEBUS
      Dim spCmdPrintEBU As SqlCommand = New SqlCommand()
      spCmdPrintEBU.CommandText = "sido.Fire_DALC_PrintSubRptEBUS"
      spCmdPrintEBU.CommandType = CommandType.StoredProcedure
      spCmdPrintEBU.Parameters.Add("@LocID", SqlDbType.Int, 4).Value = 1

      Dim da As New SqlDataAdapter()
      Dim ds As New DataSet()
      ds.DataSetName = "dsPrint_FireEBU"

      Using spCmdPrintEBU
        spCmdPrintEBU.Connection = Me.sqlCn
        da.SelectCommand = spCmdPrintEBU
        da.Fill(ds, "dsPrint_FireEBU")
      End Using


      Me.CloseConnection()
      Dim filename As String = ds.DataSetName.ToString & ".xsd"
      ds.WriteXmlSchema(filename)

    Catch ex As Exception
      MsgBox(ex.ToString)
    End Try
  End Sub

End Class

 

---

James Crandall ~ http://javitechnologies.com Spatial Database Solutions

Answer 8

I have about 100 tables and could not work out on the article posted on the blog how to create all the tables in one go to create the xsd. So I started down the designer wizard route with the idea that I could create the typed dataset within the wizard and create any additions to the typed dataset (New Tableadapters outside)

I'd forget the damn wizards ... IMHO, they are really more trouble than they're worth. Use SqlCommand and SqlDataAdapter and forget the TableAdapter approach.

Those 100 database tables do NOT (and SHOULD not) all need to be in one humongous Typed DataSet! As we discussed in the comments in my blog post ... you need to have a separate Typed DataSet for each functionality within your app. You might have a CustomerDataSet, an OrderDataSet, etc. Those DataSets might contain data from some of the same tables in the database, but that doesn't matter. Your DAL will know how to save the data in your DataSets ... the DataSets themselves don't need to know anything about it.

---

~~Bonnie Berent [C# MVP]

geek-goddess-bonnie.blogspot.com