public static XmlDocument GetXML_Form(string moduleId)
{
moduleId = moduleId.ToUpper();
if (ModuleToXmlLookup.ContainsKey(moduleId))
{
return(ModuleToXmlLookup[moduleId]);
}
string fileName = moduleId + ".xml";
string xmlType = "Form";
try
{
SqlDataReader reader = null;
using (var con = DBHelper.GetNewConnection())
using (SqlCommand cmd = new SqlCommand("usp_XMLFORMCUDGetForm", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ModuleID", SqlDbType.VarChar).Value = moduleId;
cmd.Parameters.Add("@FileName", SqlDbType.VarChar).Value = fileName;
cmd.Parameters.Add("@FileContents", SqlDbType.VarBinary).Value = null;
cmd.Parameters.Add("@XMLType", SqlDbType.NVarChar).Value = xmlType;// "Form";
cmd.Parameters.Add("@IsTemplate", SqlDbType.Bit).Value = false;
cmd.Parameters.Add("@Mode", SqlDbType.Char).Value = 'G';
con.Open();
reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
byte[] file = null;
if (reader.Read())
{
file = (byte[])reader["FileContents"];
XmlDocument xmlDoc = new XmlDocument();
using (MemoryStream memStream = new MemoryStream(file))// Convert.FromBase64String(extractedBaseString)))
{
xmlDoc.Load(memStream);
}
ModuleToXmlLookup[moduleId] = xmlDoc;
return(xmlDoc);
}
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public static DataTable GetDataTable_With_LastCreatedRow(string tableName, string idFieldName, string hintFieldName = null, string hintFieldValue = null)
{
string retValue = string.Empty;
string sSQL_Template = "SELECT TOP 1 {0} as ID_ID_ID ,* from {1} ORDER BY ID_ID_ID DESC";//"WHERE SettingName= 'CurrentTimeZone'";
string sSQL;
if (hintFieldName != null && hintFieldValue != null)
{
sSQL_Template = "SELECT TOP 1 {0} as ID_ID_ID ,* from {1} WHERE {2} LIKE '%{3}%' ORDER BY ID_ID_ID DESC";
sSQL = string.Format(sSQL_Template, idFieldName, tableName, hintFieldName, hintFieldValue);
}
else
{
sSQL = string.Format(sSQL_Template, idFieldName, tableName);
}
try
{
using (var con = DBHelper.GetNewConnection())
using (var adapter = new SqlDataAdapter(sSQL, con))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
}
catch (Exception ex)
{
throw ex;
}
if (string.IsNullOrWhiteSpace(retValue))
{
throw new ArgumentException("LastCreatedId coulud not be determined.");
}
return(null);
}
public static DataTable Execute_SelectStatement(string selectStatement)
{
string retValue = string.Empty;
string refinedSQL = selectStatement;
try
{
using (var con = DBHelper.GetNewConnection())
using (var adapter = new SqlDataAdapter(refinedSQL, con))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
}
catch (Exception ex)
{
throw ex;
}
return(null);
}
public static string GetLastCreatedIdForTable(string tableName, string idFieldName, string hintFieldName = null, string hintFieldValue = null, EnumHintFieldSearchTechnique hintFieldSearchTechnique = EnumHintFieldSearchTechnique.Contains)
{
string retValue = string.Empty;
string sSQL_Template = "SELECT TOP 1 {0} from {1} ORDER BY 1 DESC";//"WHERE SettingName= 'CurrentTimeZone'";
string sSQL;
if (hintFieldName != null && hintFieldValue != null)
{
string searchString = "";
switch (hintFieldSearchTechnique)
{
case EnumHintFieldSearchTechnique.BeginWith: searchString = string.Format(" LIKE '%{0}' ", hintFieldValue); break;
case EnumHintFieldSearchTechnique.EndsWith: searchString = string.Format(" LIKE '{0}%' ", hintFieldValue); break;
case EnumHintFieldSearchTechnique.ExactMatch: searchString = string.Format(" = '{0}' ", hintFieldValue); break;
case EnumHintFieldSearchTechnique.NotContains: searchString = string.Format(" NOT LIKE '%{0}%' ", hintFieldValue); break;
default:
case EnumHintFieldSearchTechnique.Contains: searchString = string.Format(" LIKE '%{0}%' ", hintFieldValue); break;
}
sSQL_Template = "SELECT TOP 1 {0} from {1} WHERE {2} {3} ORDER BY 1 DESC";
sSQL = string.Format(sSQL_Template, idFieldName, tableName, hintFieldName, searchString);
}
else
{
sSQL = string.Format(sSQL_Template, idFieldName, tableName);
}
SqlConnection con;
SqlDataReader reader;
try
{
con = DBHelper.GetNewConnection();
con.Open();
reader = new SqlCommand(sSQL, con).ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
retValue = reader[0].ToString();
break;
}
}
else
{
throw new ArgumentException("LastCreatedId coulud not be determined.");
}
reader.Close();
}
catch (Exception ex)
{
throw ex;
}
if (string.IsNullOrWhiteSpace(retValue))
{
throw new ArgumentException("LastCreatedId coulud not be determined.");
}
return(retValue);
}
