I have done some light research into this topic and I would like to know what are all the pro's and con's or enabling/registering this particular .dll within SQL Server?
Back information - we are integrating with a third party application (not my decision, unfortunately) which requires this .dll for some of it others .dlls. What I need the CLR Function for is to be able to write SQL Queries in SSMS and have that data sent to the third party application's API which then would in turn do the correct data load/changes (inserts and deletes to/from this application has to be done via its API).
EDIT - maybe I shouldve included this detail
When trying to register my c# class I obviously got the error "system.web not registered blah blah blah" which has then in turn prompted my research on this topic.
end edit
So, my conundrum is that to be able to register my C# class/.dll, I have to register all the dependent .dll's, however based on my research I know that this particular one can be quite problematic.
So seeing in how I am not terribly familiar with the pitfalls outside of my google research, I was wondering if one of you fine people could help me understand how to make the best decision in regards to this.
Also, what else can I add to this post so that being able to give insight is easier? I wasnt too sure the C# code was relevant? I understand this might be a bit broad, but I was hoping that its specific enough to not get flagged?
To be more specific to whats occurring here (per Solomon's request)
The 3rd party app uses an "API" (used loosely because I am told it is not a great API) to send data back and forth. You'll notice it calls an Importer function which only takes a data table or an excel file which it converts. I have no other option as the the company told me that inserts and deletes via normal XML is terribly slow and has unexpected behavior.
The .DLL that references System.Web is referenced within my C# class, which is required to be able to send it data in the first place.
In regards to:
Why would you not be able to use the methods I mentioned? They already exist in SQL Server's CLR host. This is for a web service, right
I am not sure I know enough about API's in general to answer this. I very would could and just lack the knowledge and experience to do so. It also might be due to the fact I have limitations on how I can interact with this particular API and I am not sure how those limitations apply to these methods. (I will investigate further and see if I can answer this question myself).
Though the more I think about it, I could create a "middle man" class which SQL Server can call, which then would call another class, which would have all the correct references and that might get me past my current situation. I still, however, I am interested in the specific feedback so that I can learn from this.
here is my c# class:
using Perfion.Api; -- this .DLL references System.Web.
using Perfion.Api.Import;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
namespace PerfInsert
{
public static class PerfionInsert
{
public static bool CreateCommand(string tblString, string featureName, string connectionString, string perfionConnectionString, string logFile)
{
StringBuilder logInfo = new StringBuilder();
try
{
var wList = new Regex(@"[^0-9a-z\.\[\]_]#", RegexOptions.IgnoreCase);
if (wList.IsMatch(tblString))
{
logInfo.AppendLine($"{DateTime.UtcNow} - Regex Validation Failed for Table Name!");
return false;
}
using (SqlConnection connection = new SqlConnection(connectionString))
{
var qryString = "SELECT * FROM " + tblString;
using (SqlCommand command = new SqlCommand(qryString, connection))
{
connection.Open();
using (var dataReader = command.ExecuteReader())
using (var dataTable = new DataTable())
{
dataTable.Load(dataReader);
PerfionApi api = new PerfionApi(perfionConnectionString);
Importer importer = new Importer(api.Connection);
importer.Status += (sender, e) => { logInfo.AppendLine($"{DateTime.UtcNow} - {e.Title}"); };
importer.LoadData(dataTable);
importer.ImportToDatabase(featureName);
}
}
}
return true;
}
catch (Exception ex)
{
logInfo.AppendLine($"{DateTime.UtcNow} - {ex.ToString()}");
}
finally
{
File.AppendAllText(logFile, logInfo.ToString());
}
return false;
}
}
}
