Needed to bind data from multiple data source (not database)

Question

I am working on a project which requires listing of all datasources, connecting them and then fetch the list of tables from them, their columns and later a query builder to query them and fetch the data. The tricky part here is the query Builder and to pass the query and fetch the data directly from the datasources (say oracle , ms sql server, ms access etc)

currently I am fetching all the data(as a dataset or datatable) and parsing them to fetch the output which is not reliable.

Is that possible or a Best approach to achieve this?

Code which I tried.

 public DataTable GetDataTableforMSAccess(string strSQL, string Connstr = "")
    {
        DataTable dt = new DataTable();
        try
        {
            if ((Connstr == null) || (Connstr.ToString() == ""))
            {
                //Sample connstr "Provider=Microsoft.ACE.OLEDB.12.0;data source=F:\\NWind.accdb";
                Connstr = GetDefaultConnectionString();
            }
            using (OleDbConnection conn = new OleDbConnection(Connstr))
            {
                OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                conn.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter(cmd);
                adapter.Fill(dt);
            }
        }
        catch (Exception ex)
        {
            LogWrite("GetDataSetforMSAccess :" + ex.Message, true);
        }
        return dt;
    }

Similarly I tried seperately for Oracle , MS SQL, My Sql. I need to merge the queries and run against in a single interface so that I can fetch the desired result.

EDIT: I have fetched the list, the tricky part is i need to query the columns from multiple sources and then run the query which should comprise all the databases required.

Can you please provide some examples of what you have already tried? — DVK
– DVK, Commented May 3, 2016 at 13:40
So if your question, "How do I get a list of tables/columns from multiple databases?" — Jonathan Allen
– Jonathan Allen, Commented May 4, 2016 at 7:31
@JonathanAllen : I have fetched the list, the tricky part is i need to query the columns from multiple sources and then run the query which should comprise all the databases required. — Ruban J
– Ruban J, Commented May 4, 2016 at 9:02

Raul A. · Accepted Answer · 2016-05-04 07:41:57Z

1

I would just make a model if all data can be mapped to the same class and then read from the sources and just put all the data together in an IEnumerable but if there is different data I'm not sure I understand your question. Simply read from each location and take what you need, at least I am not aware of any other solution.

answered May 4, 2016 at 7:41

Raul A.

3334 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ruban J Over a year ago

If the data is known, we could do as you say. here the user needs to choose their own databases and manipulate themselves.

Ggalla1779 · Accepted Answer · 2016-07-26 11:54:48Z

For this kind of thing you need to be clear on what you want, be clear on what they want, and be clear how the heck your trying to do it.

If your saying your user wants to write a query across multiple data sources then tell them it cant be done without alot of work (insert scary figure). Unless all the cloumns and table names are the same?

If your pulling back data and merging it then doing query that designed to be used against 1 data source then thats better.

So the approach I would take is I would write an iterface that had all the methods required to conttect to database. Then create classes that used interface ie MSSQL, Oracle, Access. so now when defining your object you can instanciate what you need ie MSSQL or Oracle...then the methods are all the same.

So once you get each datasource you then would propbably merge all the data..or just go through and add / merge the data to a grid or something. I would recommend a mapping method ie Oracle fieldx = MSSQL= field101 both become First Name on grid eh.

so then user enters SQL and you filter each datasources SQL by that new SQL and redisplay

Collectives™ on Stack Overflow

Needed to bind data from multiple data source (not database)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related