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); }