Need advice on how to get data from a database into a list of objects, without knowing beforehand what database I will be connecting to

Question

I'm trying to write a function in an MVC C# controller into which I can pass in the table name, server name, database name, username and password. This function is being called from an Ajax call, so it needs to return JSON. I'm using to using entity framework, so I'm sort of new to this - I've been trying to use SqlDataReader, and then automatically put all data return into a list of objects, which I can then return to the Ajax, but I'm not even getting close - all of the methods using SqlDataReader seem to require knowing what rows you want to select in advance, so I have no real clue what do to or try next. Has anybody got any advice on how to achieve this?

Basically, it's for a project I've been tasked with where someone can fill in a form with the connection string, and sql query, and the scripts will go to the controller and return the data. The user can then pick what column(s) they want to use, using dc.js, I will create whatever chart they chose based on whatever columns they chose, based on the returned data. It's melting me head...

So you are asking user to select database details from UI, which you pass that to server using AJAX and expect selected data returned from the SQL server via JSON format ? Is it like you are creating Sql Management Studio in Web? — Chetan, Mar 27 '17 at 13:41
Kind of - it's supposed to be a reporting tool, where a user can feed in database details - the UI will return a list of the available columns in the table - the user will select chart type and columns to be used in the charts, and at the end of it all, get a nice display of the data in chart form. — ailinmcc666, Mar 27 '17 at 14:01
Did any of the below answer solved the problem? I would suggest the same approach. — Chetan, Mar 27 '17 at 14:04
Your MVC API adds no value. Someone who has all those details can connect directly to the DB and get their table. What possible reason is there to oblige them to use your API? — bbsimonbb, Mar 28 '17 at 14:01

score 1 · Accepted Answer · edited May 23 '17 at 10:30

1

This is something I have in a project:

    /// <summary>
    /// Get all of the SQL data from "tableName" using "connectionString" 
    /// </summary>        
    public static DataTable GetSqlDataAsDataTable(string tableName, string connectionString)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand cmd = new SqlCommand(string.Format("SELECT * FROM [{0}]", tableName), connection))
            {
                cmd.CommandType = CommandType.Text;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        try
                        {
                            sda.Fill(dt);
                        }
                        catch (Exception)
                        {
                            // handle it
                        }
                        return dt;
                    }
                }
            }
        }
    }

Once you have that, you can convert the DT to JSON as described in this other answer: https://stackoverflow.com/a/17398078/4842817

edited May 23 '17 at 10:30

Community

1
1

answered Mar 27 '17 at 13:37

Forklift

949
8
20

That's not really what I was looking for - I can get it so the data is returned as a datatable, but from there I was unable to serialize the data as JSON, which is part of what I need to happen. – ailinmcc666 Mar 27 '17 at 13:40
You should just be able to `.Serialize()` Are you seeing an error with that? – Forklift Mar 27 '17 at 13:44
No, there is no .Serialize() option on dt. – ailinmcc666 Mar 27 '17 at 13:58
Link to how to do that added to answer. – Forklift Mar 27 '17 at 14:01
Thanks a lot, this done exactly what I needed, you are a legend. – ailinmcc666 Mar 27 '17 at 14:18
Awesome! Happy coding, friend :) – Forklift Mar 27 '17 at 14:20

score 0 · Answer 2 · answered Mar 27 '17 at 13:48

public static string GetJSON(string connectionString, string tableName)
{
    try
    {
        SqlConnection connection = new SqlConnection(connectionString);
        connection.Open();
        SqlCommand command = new SqlCommand($"SELECT * FROM {tableName}", connection);
        SqlDataAdapter adapter = new SqlDataAdapter(command);
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        connection.Close();
        string json = Newtonsoft.Json.JsonConvert.SerializeObject(dataTable.Rows);
        return json;
    }
    catch { return string.Empty; }
}

The code above requires references to System.Data and System.Data.SqlClient as well as the open-source Nuget package called Newtonsoft.Json.

It will open up a connection based on the connection string provided, select all columns from the specified table and populate a DataTable object with this information.

Newtonsoft.Json's JsonConvert.SerializeObject(object) method will serialize the DataRowCollection (dataTable.Rows) to a JSON string to be returned.

score 0 · Answer 3 · answered Mar 27 '17 at 14:06

I would use Dapper and Newtonsoft.Json from nuget and do it like this:

public string GetTableContentsAsJson(string serverName, string databaseName, string userName, string password, string tableName)
{
    System.Data.SqlClient.SqlConnectionStringBuilder builder =  new System.Data.SqlClient.SqlConnectionStringBuilder();
    builder["Data Source"] = serverName;
    builder["integrated Security"] = false;
    builder["Initial Catalog"] = databaseName;
    builder["User ID"] = userName;
    builder["Password"] = password;
    Console.WriteLine(builder.ConnectionString);

    using (var connection = new SqlConnection(builder.ConnectionString))
    {
        connection.Open();
        var images = connection.Query($"SELECT * FROM {tableName}");
        string s = JsonConvert.SerializeObject(images);
        return s;
    }
}

score 0 · Answer 4 · answered Mar 28 '17 at 13:54

0

Hi Entity framework itself providing the freedom to choose databases dynamically. Write a procedure for fetching the column details and return the result, it will solve your issue.

answered Mar 28 '17 at 13:54

Sudheesh

61
7

How can I dynamically chose the database and table and return that data using entity framework? – ailinmcc666 Mar 28 '17 at 14:01
`using (NorthwindEntities context = new NorthwindEntities(entityConnection)) { // do whatever on default database foreach (var product in context.Products) { Console.WriteLine(product.ProductName); } // switch database sqlConnection.ChangeDatabase("Northwind"); Console.WriteLine("Database: {0}", connection.Database); }` – Sudheesh Mar 29 '17 at 10:51

Need advice on how to get data from a database into a list of objects, without knowing beforehand what database I will be connecting to

4 Answers4