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...
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
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.
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;
}
}