public string GetSqlScriptContentsFromEmbeddedResource(string scriptFile, Assembly callingAssembly = null, string callerNamespace = null, string resourcePath = null)
{
if (callingAssembly == null)
{
callingAssembly = Assembly.GetCallingAssembly();
}
if (callerNamespace == null)
{
callerNamespace = ReflectionProvider.GetCallerNamespace(new StackFrame(1));
}
if (scriptFile.ToLowerInvariant().EndsWith(".sql"))
{
throw new ArgumentException("scriptFile name should not include sql extension", nameof(scriptFile));
}
string query = null;
if (!string.IsNullOrWhiteSpace(resourcePath))
{
query = GetEmbeddedResource(callingAssembly, resourcePath + "." + scriptFile);
}
if (query == null)
{
query =
GetEmbeddedResource(callingAssembly, GetResourceName(callerNamespace, scriptFile))
??
GetEmbeddedResource(callingAssembly, GetResourceName(callingAssembly.GetName().Name, scriptFile));
}
if (query == null)
{
var currentAssembly = Assembly.GetExecutingAssembly();
query = GetEmbeddedResource(currentAssembly, GetResourceName(currentAssembly.GetName().Name, scriptFile));
if (query == null)
{
throw new Exception($"Resource '{scriptFile}' not found.");
}
}
return(query);
}
public int ExecuteScriptFileNonQuery(string sqlScriptName, List <SqlParameter> parameters = null, bool allowDangerousDirectParamInsert = false, string scriptResourcePath = null, Assembly callingAssembly = null)
{
if (callingAssembly == null)
{
callingAssembly = Assembly.GetCallingAssembly();
}
var callerNamespace = ReflectionProvider.GetCallerNamespace(new StackFrame(1));
using (var conn = new SqlConnection(_connectionString))
{
using (var cmd = BuildSqlTextCommand(sqlScriptName, conn, callingAssembly, callerNamespace, scriptResourcePath))
{
AddParameters(parameters, cmd);
conn.Open();
var rows = cmd.ExecuteNonQuery();
conn.Close();
return(rows);
}
}
}
public DataTable ExecuteScriptFileGetDataTable(string sqlScriptName, List <SqlParameter> parameters = null, string scriptResourcePath = null, Assembly callingAssembly = null)
{
if (callingAssembly == null)
{
callingAssembly = Assembly.GetCallingAssembly();
}
var callerNamespace = ReflectionProvider.GetCallerNamespace(new StackFrame(1));
using (var conn = new SqlConnection(_connectionString))
{
using (var cmd = BuildSqlTextCommand(sqlScriptName, conn, callingAssembly, callerNamespace, scriptResourcePath))
{
AddParameters(parameters, cmd);
conn.Open();
var dt = new DataTable();
using (var da = new SqlDataAdapter(cmd))
{
da.Fill(dt);
}
return(dt);
}
}
}
