Basically, I am trying to make a dynamic SQL controller for Web API. I am having an issue trying to just type the normal URL path of the Web API and using the insert columns to see if I will get the data inserted successfully message but I keep getting a HTTP 405 error.
However, if I used Swagger and implemented the insert columns there, it worked. Why is that? What did I do wrong here and how can I fix this so that if I type in the web URL it would insert the data?
Update 1:
If I enter a breakpoint inside the function InsertData and add a URL where I use urlpath:Port/api/Main/InsertData?insertcolumns=(column1=value1)(column2=value2)(column3=value3) it wont even enter the function. On the other hand, using Swagger I entered the area where I placed the breakpoint. What could be the reason that I can't even access the function using the URL? Also if I will be using this in (say: Windows Form) how can I modify this to enter the function and get to the breakpoint?
In one file:
public class SQLDatabase
{
// This class is to handle all SQL functions and they are listed below:
// Here are the initialised variables:
public SqlConnection DBconn = null;
public SQLDatabase(Sqlconnection sqlConnection)
{
DBconn = sqlConnection.SQLconn;
}
/// <summary>
/// This function handles the execution of queries such as insert, update and delete:
/// </summary>
/// <param name="sqlquery"></param>
public async Task<List<Dictionary<string,object>>> ExecuteSQLqueries(string basesqlquery, string tablename, string columns)
{
// Initialisation of the columns:
//List<string> selectedColumns = new List<string>();
List<object> fullresults = new List<object>();
List<Dictionary<string, object>> results = new List<Dictionary<string, object>>();
string fullsqlquery;
string selectedColumns;
// if there are no optional columns, then get all:
if (string.IsNullOrWhiteSpace(columns))
{
selectedColumns = "*";
}
// collecting the optional columns to insert into the data
else
{
selectedColumns = columns;
}
// creating full sql query:
fullsqlquery = $@"{basesqlquery} {selectedColumns} from {tablename}";
//await DBconn.OpenAsync();
using (SqlCommand DBcom = new SqlCommand(fullsqlquery, DBconn))
{
using (SqlDataReader reader = await DBcom.ExecuteReaderAsync())
{
// Creating output of the queries dynamically:
while (await reader.ReadAsync())
{
Dictionary<string, object> row = new Dictionary<string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
string columnName = reader.GetName(i);
object columnValue = reader[i];
row[columnName] = columnValue;
}
results.Add(row);
}
return results;
}
}
}
/// <summary>
/// This function handles the execution of queries such as insert, update and delete with params:
/// </summary>
/// <param name="sqlquery"></param>
public async Task<int> ExecuteSQLqueriesWithParams(string basesqlquery, string tablename, Dictionary<string, object> insertcolumns)
{
// Initialisation of the columns:
List<Dictionary<string, object>> results = new List<Dictionary<string, object>>();
string fullsqlquery;
string selectedColumns;
string selectedColumnsValues;
// Creating the parameters for the data to be inserted:
selectedColumns = string.Join(", ", insertcolumns.Keys);
selectedColumnsValues = string.Join(", ", insertcolumns.Keys.Select(key => $"@{key}"));
// creating full sql query:
fullsqlquery = $@"{basesqlquery} into {tablename} ({selectedColumns}) values ({selectedColumnsValues})";
//await DBconn.OpenAsync();
using (SqlCommand DBcom = new SqlCommand(fullsqlquery, DBconn))
{
foreach (KeyValuePair<string, object> columnskv in insertcolumns)
{
DBcom.Parameters.AddWithValue($"@{columnskv.Key}", columnskv.Value);
}
return await DBcom.ExecuteNonQueryAsync();
}
}
}
In the controller, I have:
[HttpPost]
public async Task<IActionResult> InsertData(string insertcolumns)
{
// Obtaining the connection string:
string jsonFilePath = jsonFilePath;
string targetdatabase = "xxxxx";
// Getting the Data details:
DatabaseSettings connstr = new DatabaseSettings();
string sqlconnecitonstring = connstr.DatabaseConnectionString(jsonFilePath, targetdatabase);
Sqlconnection sqlconn = new Sqlconnection(sqlconnecitonstring);
// Accessing the database:
await sqlconn.OpenConnectionAsync();
bool sqlconnstatus = await Task.Run(() => sqlconn.ConnectionStatus());
if (sqlconnstatus)
{
// Here is the SQL query:
string SQLQuery = "insert";
// Splitting up the string into keys and values:
string[] columns = insertcolumns.Split(new[] { '(', ')' }, StringSplitOptions.RemoveEmptyEntries);
Dictionary<string, object> columnsdictionary = columns.Select(item => item.Split('=')).ToDictionary(s => s[0], s => (object) s[1]);
// Executing the Query:
SQLDatabase sqldata = new SQLDatabase(sqlconn);
int Data = await sqldata.ExecuteSQLqueriesWithParams(SQLQuery, tableName, columnsdictionary);
// Closing the connection:
sqlconn.CloseConnectionAsync();
// Returning the output data:
//return Ok(Data);
return Ok("Data inserted successfully");
}
else
{
return NotFound("Database connection not successful.");
}
}
Update 2:
If I used the following code, it accessed the function the InsertData function but it always caused an HTTP error in the return await DBcom.ExecuteNonQueryAsync();. Error Code is 500 (Internal Server Error) when using following URL urlpath:Port/api/Main/InsertData?insertcolumns=(column1=value1)(column2=value2)(column3=value3)
private async void btn_datainsert_Click(object sender, RoutedEventArgs e)
{
string httprequest = HTTPrequest;
using (var reader = new StreamReader(jsonFilePath))
{
jsonFile = reader.ReadToEnd();
}
var clientbaseURL = JsonConvert.DeserializeObject<urltype>(jsonFile);
using (HttpClient client = new HttpClient())
{
var Postdatas = $@"(data_no={data_value.Text})(datetime={DateTime.ParseExact(datetime.SelectedDate.Value.ToString("M/dd/yyyy") + ' ' + txtbx_datetime.Text, "M/dd/yyyy hh:mm tt", CultureInfo.InvariantCulture)})(status={Convert.ToBoolean(txtbx_databox3.Text)})";
string fullrequest = clientbaseURL.ClientBaseURLIIS.ToString() + httprequest + "InsertData?insertcolumns=" + Postdatas;
// Getting values inserted in textbox and use them to post data:
//var Postdata = $@"(data_no={data_value.Text})(datetime={DateTime.ParseExact(datetime.SelectedDate.Value.ToString("M/dd/yyyy") + ' ' + txtbx_datetime.Text, "M/dd/yyyy hh:mm tt", CultureInfo.InvariantCulture)})(status={Convert.ToBoolean(txtbx_databox3.Text)})";
var Postdata = new dataModel()
{
data_no = data_value.Text,
txtbx_datetime = DateTime.ParseExact(datetime.SelectedDate.Value.ToString("M/dd/yyyy") + ' ' + txtbx_datetime.Text, "M/dd/yyyy hh:mm tt", CultureInfo.InvariantCulture),
status = Convert.ToBoolean(txtbx_databox3.Text)
};
var PostdataJSON = JsonConvert.SerializeObject(Postdata);
var payLoad = new StringContent(PostdataJSON, Encoding.UTF8, "application/json");
using (var response = await client.PostAsync(fullrequest, payLoad))
{
if (response.IsSuccessStatusCode)
{
MessageBox.Show("Data has been Inserted to database", "Information", MessageBoxButton.OK, MessageBoxImage.Information);
await RefreshDatagridTable();
}
else
{
MessageBox.Show($"Data has not been Inserted to database! {response.StatusCode}", "Error", MessageBoxButton.OK, MessageBoxImage.Error);
}
}
}
Update 3:
I managed to be able to insert data into the database using the InsertData function with Windows form now. However, when I try to run through the web browser and type the URL 'urlpath:Port/api/Main/InsertData?insertcolumns=(column1=value1)(column2=value2)(column3=value3)' it always comes back to HTTP Code error 405. How can I resolve this to be able to use a web browser (if needed) to insert data into the database?