Sunday, April 18, 2010

Create An XSD

In a post I wrote back in September about why I dislike TableAdapters (see TableAdapters Are Crap), I mentioned that the TableAdapter wizard puts a lot of excess stuff in your .xsd that doesn’t belong there. So, how do you avoid that? Well, first you’ve got to have already created an .xsd. I’ll show an easy little utility you can write yourself to use to do this.

Secondly, you’ve got to avoid using the DataSet Designer in such a way that it puts all that excess stuff into your .xsd. How do you do that? That’s pretty easy actually … as long as you don't open an .xsd with the DataSet Designer, but open it with the XML Editor instead, you won't have to worry about getting all the extra stuff for support of TableAdapters generated in your .xsd (it's not the opening, but the saving of changes that generates the code).

So, use the code I’ll show you in a moment to create an .xsd. Then, simply add that .xsd to your DataSet project, right-click on the .xsd and choose "Run Custom Tool". This is what will generate the Typed DataSet for you. If that option doesn't show up in your context menu, choose Properties instead and type "MSDataSetGenerator" in the Custom Tool property. After that, any time you make a change to the .xsd in the XML Editor and save the change, the Typed DataSet gets regenerated.

Now, on to the code … this example is fairly simple but quite usable. You can make it more robust if you want to (for example, add code to find other SQL Servers rather than use only a default) . Create a Form, put 3 textboxes and a button on it. Don’t forget that you need to add a “using System.Data.SqlClient;” in order to use the SQL stuff.

public partial class FormCreateXsd : Form
{
private string TestConnection = "server=(local);uid=sa;pwd=MyPassword";

public FormCreateXsd()
{
InitializeComponent();
}

private void CreateDataSet()
{
// set up the connection with the database name
string connectionString = this.TestConnection + ";database=" + this.txtDatabaseName.Text;

try
{
// set up the Sql Command and DataAdapter with the Stored Proc name
SqlCommand sc = new SqlCommand(this.txtStoredProcName.Text, new SqlConnection(connectionString));
sc.CommandType = CommandType.StoredProcedure;
SqlDataAdapter da = new SqlDataAdapter(sc);

// set up the DataSet with the DataSet name
DataSet ds = new DataSet();
string[] parts = this.txtDataSetName.Text.Split('.');
ds.DataSetName = parts[0];

// and fill the DataSet
da.Fill(ds);

// set the filename and write out the schema
string filename = this.txtDataSetName.Text;
if (parts[parts.Length - 1].ToLower() != "xsd")
filename += ".xsd";
ds.WriteXmlSchema(filename);
MessageBox.Show(filename + " successfully created");
}
catch (Exception ex)
{
string msg = ex.Message;
if (ex.InnerException != null)
msg += "\r\n" + ex.InnerException.Message;
MessageBox.Show("The following error occurred: " + ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
if (this.txtDatabaseName.Text == "" || this.txtDataSetName.Text == "" || this.txtStoredProcName.Text == "")
return;
else
this.CreateDataSet();
}
}

That’s it. Happy coding!

29 comments:

  1. Hi Bonnie

    Not having much luck using this, probably my knowledge.

    Can you please tell me what the stored proc text box is doing? I have no stored procs in the db and even if I did I would have many, unlike the database name and the datasetname that I want to create.

    John

    ReplyDelete
  2. Hi John,

    This code is used for creating a specific DataSet (.xsd) from a specific Stored Proc (so you enter the Proc name in the TextBox). IOW, say that you have a usp_CustomersGetByID proc and it returns several tables, all having to do with a Customer. You want a CustomerDataSet containing all these tables. This code will run that Stored Proc, Fill a DataSet and write out the schema for you, creating the .xsd, which you'd then add to your project.
    You can modify this code however you want ... to use in-line SELECTs instead of Procs, for example.

    ReplyDelete
  3. Thanks bonnie. So it's not doing what the designer wizard does and creates the full db schema into a single dataset without tableadapters etc. If I use the designer to create the dataset but do not use any of the features like tableadapters and do all my db access in code then I would have thought this would be ok and you have a typed dataset for your whole project. Was concerned about creating many datasets for different forms or just creating one for the whole application.

    ReplyDelete
  4. Putting your entire database schema into one huge DataSet is NOT a good idea. It's perfectly ok (and actually preferable) to have many different Typed DataSet schemas. Think of it in terms of different DataSets for different functionality. That doesn't necessarily mean a different DataSet for every Form either ... a DataSet that represents a Customer, for example, could easily be used in several different Forms. It doesn't matter ... think of the DataSet as simply a class to pass your data around in.

    ReplyDelete
  5. Thanks for that answer Bonnie.

    Where would you normally put your typed dataset schemas a seperate project containing many typed xsd files? I have read alot of your stuff now and am adopting the 3 projects route that you recommend for DAL/BAL/UI (WPF).

    ReplyDelete
  6. Hi John!
    Yes, the DataSets should go into separate project(s). If your application only has a few .xsd schemas, then you can have just one DataSet project and put them there. However, if it's a large application, and you have many schemas, I'd recommend breaking the DataSets into different DataSet projects, based on functionality. For example, I used to work at several companies where we developed Fire/Police Records Management software. We had separate DataSet projects for schemas for Incidents, EMS, Personnel, Property, etc.etc. ...

    ReplyDelete
  7. Oh, and one more thing ... since you're using WPF for your UI, you also might want to look into the MVVM pattern. I have not really gotten into WPF, but played with it a for a little bit about a year ago, and MVVM is the way to go. Here's a few links to get you started:

    http://joshsmithonwpf.wordpress.com/a-guided-tour-of-wpf/
    http://joshsmithonwpf.wordpress.com/category/mvvm/
    http://msdn.microsoft.com/en-us/magazine/dd419663.aspx

    There are other experts on the topic, but I like the way Josh Smith explains stuff. Feel free to Google MVVM yourself!

    ReplyDelete
  8. Bonnie

    I've been trying this.
    I created the tables and datasets in separate projects.
    Now I want to access the data in one project.
    I added the projects to the solution and I can access the datasets by creating an object of the project that contains them i.e

    >>
    GenerateTypedDataSet.item itemds = new GenerateTypedDataSet.item();

    string x = itemds.icitem[0].itmdesc;

    <<

    That's good for code, but when I want to find those datasets for Crystal Reports, I only see the datasets associated with the current project.

    Any suggestions?

    ReplyDelete
  9. That was Bill Fitzgerald, not
    Not, anon!

    ReplyDelete
  10. Hi Bill ... I don't use Crystal Reports, but I would think that all you would have to do is add a reference to the DataSet project ... but, it sounds like you did that already? Maybe KG might know ... he was big into using Crystal with Typed DataSets before he got crazy with SQL Server. I'll send him a UT PM and ask him if he could help out here.

    ReplyDelete
  11. Bonnie
    I did some digging round in CR and found that they let you select .xsd's as the data source and then that exposes all the fields in the report designer.
    It seems to be a lot cleaner if all of the tables in the report are in one .xsd tho, so I'll put together one .xsd that contains all those tables and select that as the single data source for designing the report.
    I'm having a ball with writexmlschema. I wish I had done this a while back.
    This is so good that I'm re-engineering an app that I was deeply into using the IDE datasets.

    Bill Fitzgerald

    ReplyDelete
  12. Glad that you figured that out, Bill. Now that you mention it, it sounds vaguely familiar (from hearing KG mention it in the past).
    It *is* better to have all the Tables you need for a report in one DataSet. DataSets should be based on functionality. If your reporting needs a different DataSet than your Form, then you'd have two different DataSets, one for each functionality (they can use the same DataSet if it's appropriate, but it sounds like it's not in your case).

    ReplyDelete
  13. Bonnie
    What's the best procedure for me to follow when changing table structure (I do it a lot?)
    It seems in order to see the new structure that I have to regen the .xsd, delete it from the generation project and then re-add it.
    When I open the application project, it sees the changes and asks if it should reload and it does and it sees the new structure.
    Is that the best way?

    Bill Fitzgerald

    ReplyDelete
  14. Depends on what you mean by "see the new structure" I guess. I normally don't do anything when the .xsd changes, other than to re-gen the Typed DataSet. Where are you "not seeing the new structure"?

    ReplyDelete
  15. Bonnie

    I have s project called GenDs that generates the .xsd's selectively. There's a button for each dataset.
    I use it to generate the datasets whose tables have changed structures.

    I have added those .xsd's to the GenDs project and I can see their structure on the left on the data sources panel.

    When I regenerate an .xsd, the structure on the left doesn't change, nor can I see it intellisense, till I delete the old.xsd from the GenDs project and re-add the new .xsd.

    If I don't do that, I don't see the new structure in the application project either.


    When I do that, I see the new structure on the panel and in intellisense and when I open the application project that includes the dataset project, I get a message saying the GenDs project has changed and it reloads it and all is well.

    That's still better than what I had to do with the IDE datasets, but I'd like to streamline it

    Bill Fitzgerald

    ReplyDelete
  16. Hi Bill,

    I don't use the Data Sources panel, never have. As you may know (or may have guessed), I'm not a fan of drag-and-drop programming ... at least not when it pertains to DataAccess and DataSets. However, I just tried this & everything was fine. I went into an .xsd in my project & modified it in the XML Editor and saved it (which then re-generates the Typed DataSet). The changes showed up in the Data Sources panel.

    I bet your problem might be that you are probably recreating your .xsd everytime you make changes. I simply use the .xsd generator as a tool, it's not part of the application solution, no should it be. Make it a standalone utility (you can compile it and then run the EXE). Once you generate a new XSD with the utility, you can copy/paste the XML into the application's XSD in the XML Editor in Visual Studio or, outside of Visual Studio in Windows Explorer, simply copy the XSD into your application's folder replacing the existing one there. Then, back in Visual Studio, open the XSD in the XML Editor & re-gen your Typed DataSet. I usually make an inconsequential change ... type a space or something, because then when you Save it the Typed DataSet automatically gets re-generated and then everything will show up in the Data Sources panel.
    Let me know if that helps ...

    ReplyDelete
  17. Hi Bonnie
    I tried what you suggested and it works, but I think I like the method I've been using - deleting the .xsd from the app and re-adding it

    Here's an interesting little factoid about the two methods of generating .xsd's:

    When i was using the IDE-generated datasets, when I added a blank row to a datable, the identity column was set to -1 by .NET in every case.
    I use that later on to distinguish between rows that I added and rows that were pulled from the database. (Is there a better way?)

    With the datasets made by WriteXMLSchema, the identity column is set to null when I add a row to a datatable, and that caused a bunch of crashes till I put in a workaround.

    Bill Fitzgerald

    ReplyDelete
  18. What was your workaround? Personally, I'd handle it with a partial class of the DataSet. You'll probably find other reasons to extend your DataSets with partial classes anyway.

    public partial class MyDataSet
    {
    public MyDataSet()
    {
    this.MyTable.MyPKColumn.AutoIncrement = true;
    this.MyTable.MyPKColumn.AutoIncrementStep = -1;
    this.MyTable.MyPKColumn.AutoIncrementSeed = -1;
    }
    }

    ReplyDelete
  19. Hi Bonnie
    I wrote a function that checks for NULL. Not very pretty but it works.

    I like your approach.
    Will that code be overwritten if I regenerate the dataset?

    Bill Fitzgerald

    ReplyDelete
  20. No, it won't get overwritten as long as you don't put it in the Designer.cs file.

    I have some NULL-checking classes on this blog somewhere. Can't remember where they are off the top of my head. Will look tomorrow (Monday), if you're interested. Right now it's bedtime! ;0)

    ReplyDelete
  21. Bonnie
    Don't bother with the NULL checking classes.
    I put in your method above and as you say, if I just change the .xml it stays put.
    It's all working quite smoothly now.
    Thamks again.

    Bill Fitzgerald

    ReplyDelete
  22. Hi Bonnie,
    I am new to VS and after playing with the data sources panel and dragging and dropping and it constantly locking up on me, I stumbled across this page. I like your approach and have started using it.

    I have a 2 project solution (VS2010), a DAL dll and a presentation project with grids etc.
    I added the DAL dll as a reference in the main project and all works well but I cannot select the datasources from the DAL in the DataGridView.DataSource property in the VS designer (I can only select datasources in the current project). I would like to do this to set up the columns etc. in the grid.
    I can access them from code ok.
    Do you know if this is possible, or must I also add the datasources to the presentation project?
    Also, is this the right approach anyway?

    I am trying to get the fundamental structure correct before moving on. Any suggestion welcome.

    Regards
    John Dinning

    ReplyDelete
  23. Hi John,

    I don't usually utilize the DataSources stuff in the VS designer (I'm not fond of wizards), but I thought I'd take a look and see if I could figure out what you need to do.

    When you click on the DataSource property of your DGV in the designer, at the bottom, there's an "Add Project Data Source" link. Clicking that will bring up a "Data Source Configuration Wizard". Instead of choosing "Database" (which you might typically be tempted to do), choose "Object". You'll then be presented with a list of the other projects in your solution and can choose your DataSet there. It may be that it only shows projects to which you have a reference, but there's also an "Add Reference..." button. Since you've already got the reference to your DAL, you'll be all set.

    You should consider separating out your DataSets into a separate project, apart from your DAL project. That way, if you ever decide to go 3-tier instead of 2 (throwing in a Web Service layer, for example), then because your DataSets aren't tied in with your DAL, you can pass DataSets between the UI and DAL through a Service ... in this way, the UI won't need (and shouldn't have) a reference to the DAL, only to the Service.

    ReplyDelete
  24. Thanks for that Bonnie.
    I don't like wizards either, I feel like I am losing control and it's hard to know what they are doing or how to undo whatever they did. Maybe I should try harder not to use them.

    You were quite right about selecting the "Object" option, I had missed that one. That is what I was looking for.

    I will have a look at separating out my DataSets tomorrow. I don't think this project will ever go 3-tier but it would do no harm anyway.

    Many thanks again.

    John.

    ReplyDelete
  25. You're welcome John! Let me know if you have any other questions ...

    Good luck with your project!

    ReplyDelete
  26. Very nice.

    I found this looking around on how to handle SPs that return more than one recordset with a custom dataset, but I can see myself using this technique a lot more for handling SPs that come from the IBM DB2 system that I have to support. The dataset designer doesn't recognize all of the SPs in the database, and I even wrote some code to generate more streamlined datasets or dataset parts to accomodate what the designer can't handle.

    ReplyDelete
  27. Hi Alfetta -- glad you like my ideas. So, have you utilized my utility idea yet and if so, how do you like it?

    ReplyDelete
  28. Hi, Bonnie
    Can i, and How can I set primay key in partial class of the DataSet?
    Please example
    best regards

    ReplyDelete
    Replies
    1. Yes, you can do that if you wish. However, don't ever modify the generated code. Create another partial class of your own and set the primary key there, perhaps with a method or a property ... your choice. A method would be better, I think ... call it SetPrimaryKeys() and set all the primary keys for all the DataTables there. For example, say your DataSet is called CustomerDataSet. The generated partial class would be in a file called CustomerDataSet.Designer.cs. Add a new class file to your project and call the file CustomerDataSet.cs and put your own partial class in there.

      Delete