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!