private static string GetConnection(ProcedureDefinition proc)
{
netConnect.Info info = new netConnect.Info();
string DSNName = proc.Connection; //"BigM1DMStagingRW";
string setting = ConfigurationManager.AppSettings["Environment"];
if (string.IsNullOrEmpty(setting))
{
setting = string.Empty;
}
else
{
if (!setting.EndsWith("."))
{
setting += ".";
}
}
string sAppName = ConfigurationManager.AppSettings["NetConnect.ApplicationName"];
string sCertName = ConfigurationManager.AppSettings[setting + "NetConnect." + DSNName + ".CertificateName"];
if (sCertName == null)
{
sCertName = ConfigurationManager.AppSettings["NetConnect." + DSNName + ".CertificateName"];
if (sCertName == null)
{
sCertName = ConfigurationManager.AppSettings[setting + "NetConnect.CertificateName"];
if (sCertName == null)
{
sCertName = ConfigurationManager.AppSettings["NetConnect.CertificateName"];
}
}
}
return(info.Get(DSNName, sAppName, sCertName, netConnect.ConnectTypeEnum.CONNECT_TYPE_NET));
}
public static Hashtable LoadStoredProcedureCache()
{
XmlDocument xmlDoc = new XmlDocument();
Hashtable oStoredProcedureHash = new Hashtable();
Uri xmlPath = GetUriXmlPath(@"StoredProcedures.xml");
try
{
xmlDoc.Load(xmlPath.LocalPath);
XmlNodeList oNodeList = xmlDoc.SelectNodes("StoredProcedures/StoredProcedure");
foreach (XmlNode oNode in oNodeList)
{
ProcedureDefinition oProcedureDefinition = new ProcedureDefinition(oNode.Attributes["ProcName"].Value, oNode.Attributes["ProcCommand"].Value, oNode.Attributes["ProcDatabase"].Value, oNode.Attributes["ProcConnection"].Value);
oStoredProcedureHash.Add(oProcedureDefinition.Name, oProcedureDefinition);
}
// put into app cache
using (CacheDependency dependency = new CacheDependency(xmlPath.LocalPath))
{
HttpRuntime.Cache.Insert("StoredProcedures", oStoredProcedureHash, dependency, Cache.NoAbsoluteExpiration, Cache.NoSlidingExpiration, OnUpdate);
}
}
catch (Exception ex)
{
throw new Exception("Unable to load StoredProcedures XML at " + xmlPath, ex);
}
return(oStoredProcedureHash);
}
public static void ExecuteNonQuery(string StoredProcedureName, List <KeyValuePair <string, object> > paramlist)
{
ProcedureDefinition oProc = StoredProcedures.GetProcedureDefinition(StoredProcedureName);
SQLUtil utils = new SQLUtil();
if (!Object.Equals(paramlist, null))
{
foreach (KeyValuePair <string, object> param in paramlist)
{
utils.AddParameter(param.Key, param.Value);
}
}
utils.ExecuteNonQuery(oProc.FullyQualifiedCommand, oProc.Connection);
}
public static DataSet GetDataSetByName(string StoredProcedureName, params object[] paramlist)
{
ProcedureDefinition oProc = StoredProcedures.GetProcedureDefinition(StoredProcedureName);
SQLUtil utils = new SQLUtil();
DataSet ds = new DataSet();
if (!Object.Equals(paramlist, null))
{
foreach (KeyValuePair <string, object> param in paramlist)
{
utils.AddParameter(param.Key, param.Value);
}
}
ds = utils.ExecuteDataSet(oProc.FullyQualifiedCommand, oProc.Connection);
if (Object.Equals(ds, null) || ds.Tables.Count <= 0)
{
ds = null;
}
return(ds);
}
/// <summary>
/// Executes storedprocedure with a datatable as a parameter (for bulk merges)
/// </summary>
/// <param name="procname"></param>
/// <param name="dt"></param>
public static void ExecuteNonQueryTable(string procname, DataTable dt)
{
ProcedureDefinition oProc = StoredProcedures.GetProcedureDefinition(procname);
using (SqlConnection con = new SqlConnection(GetConnection(oProc)))
{
try
{
con.Open();
SqlCommand sqlCmd = new SqlCommand(oProc.FullyQualifiedCommand, con);
sqlCmd.CommandType = CommandType.StoredProcedure;
SqlParameter tvpParam = sqlCmd.Parameters.AddWithValue("@TableVariable", dt);
tvpParam.SqlDbType = SqlDbType.Structured;
sqlCmd.ExecuteNonQuery();
}
catch (Exception e)
{
throw;
}
}
}
public static string GetProcCommand(string sProcName)
{
ProcedureDefinition oProcedureDefinition = GetProcedureDefinition(sProcName);
return(oProcedureDefinition.Command);
}