Need some advice on how to get data from the database into a list of objects without knowing in advance which database I will connect to
I am trying to write a function in an MVC C # controller where I can pass the table name, server name, database name, username and password. This function is called from an Ajax call, so it needs to return JSON. I am using an entity framework to use, so I am new to this - I tried to use SqlDataReader and then automatically put all data in a list of objects which I can then return to Ajax, but I don't even get close - all methods using SqlDataReader seem to require know which lines you want to select in advance, so I don't know what to do or try next. Anyone got any advice on how to achieve this?
Basically, for a project I was tasked with where someone can fill out a form with a connection string and sql query and the scripts will go to the controller and return the data. The user can then select which column they want to use using dc.js, I will create any graph they choose based on any columns they choose based on the data returned. He melted my head ...
source to share
This is what I have in the 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;
}
}
}
}
}
After that, you can convert the DT to JSON as described in this other answer: fooobar.com/questions/88995 / ...
source to share
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 above code requires references to System.Data
and System.Data.SqlClient
, as well as an open source Nuget package called Newtonsoft.Json
.
It will open a connection based on the provided connection string, highlight all columns from the specified table, and populate this object with DataTable
this information.
Newtonsoft.Json JsonConvert.SerializeObject(object)
method will serialize DataRowCollection
( dataTable.Rows
) to JSON string that will be returned.
source to share
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;
}
}
source to share