Generate .NET DataSet file from a query?

Question

I use a Postgres database for my backend for multiple web applications. These applications are hosted on a third party server that I have limited access to. I currently use npgsql and nhibernate for all my data connection needs, and this works quite well.

Well, now I need to write some Crystal Reports. For performance's sake (and because the results data will not fit into any entities) I can't use my nhibernate entities as a data source for the reports; likewise I can't create an ODBC connection for the Crystal Reports because there is no driver or DSN on the destination server. So, I thought, maybe I can build datasets based off of queries I write, and fill them using the Npgsql data provider, and feed these to the Crystal Reports. I have performed a proof of concept, and it works pretty well.

The problem is, I have a lot of reports, and a lot of datasets to build, and it's very time consuming to manually build the schema in each of these. The dataset automation interfaces won't let me choose my npgsql data provider in the connection selector, which is rather annoying.

I was hoping there might be a fairly straightforward way of throwing together a little code to get a dataset schema via the npgsql data provider at runtime, and then serialize the schemas into files I could then import into my reporting project in design time.

Can this practically be done? Is there an easier way? There's a bunch of reports and a lot of columns, and hand-coding the schema for them will be extremely time-consuming.

score 1 · Answer 1 · edited May 23 '17 at 11:56

1

The dataset automation interfaces won't let me choose my npgsql data provider

Any .NET data provider must be registered within DDEX to be supported in Visual Studio designers (although, this is not necessary for using particular provider for building and running applications).

There was similar question about npgsql, but it almost 2 years old.
Maybe, something changed since 2011, because official documentation says:

2.2 Installing binary package

...
Note that placing Npgsql in the GAC is required for Npgsql design time support in Visual Studio .Net.

edited May 23 '17 at 11:56

Community

1
1

answered Nov 12 '13 at 05:25

Dennis

37,026
10
82
150

If I place Npgsql in the GAC on my development machine for design-time stuff, do you think that will have any consequences using it in a production environment in runtime where the library is just in the local bin directory? It would seem that should work. – Jeremy Holovacs Nov 12 '13 at 11:31
GAC and DDEX setting on your machine doesn't affect production environment. – Dennis Nov 12 '13 at 11:46

Jeremy Holovacs · Accepted Answer · 2013-11-12T14:21:13.013

Here's what I ended up doing.

I made a quick little WinForms App that with three multiline text boxes and a button. The button click event had the following code attached to it:

        var query = txtQuery.Text;
        var connectionString = ConfigurationManager.ConnectionStrings["connection"].ConnectionString;

        try
        {
            string data;
            string schema;
            GetSchema(connectionString, query, out data, out schema);
            txtXML.Text = data;
            txtXSD.Text = schema;
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }

The GetSchema method looked like this:

    private void GetSchema(string connectionString, string query, out string data, out string schema)
    {
        using (var conn = new Npgsql.NpgsqlConnection(connectionString))
        using (var da = new Npgsql.NpgsqlDataAdapter(query, conn))
        using (var ds = new DataSet())
        using (var dataStream = new MemoryStream())
        using (var schemaStream = new MemoryStream())
        {
            conn.Open();
            da.Fill(ds);
            ds.WriteXml(dataStream);
            ds.WriteXmlSchema(schemaStream);
            dataStream.Position = 0;
            schemaStream.Position = 0;
            using (var dataReader = new StreamReader(dataStream))
            using (var schemaReader = new StreamReader(schemaStream))
            {
                data = dataReader.ReadToEnd();
                schema = schemaReader.ReadToEnd();
            }
        }
    }

When I ran it, I got my data XML and my schema XML. With this I was able to build my result sets.

Generate .NET DataSet file from a query?

2 Answers2