using System; using System.Collections.Generic; using System.Collections.Specialized; using System.Data.SqlClient; using System.IO; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Web.Configuration; using System.Web.Mvc; using System.Web.Security; using System.Xml; using Vrh.XmlProcessing; using Vrh.OneReport.Lib.Areas.OneReport.Helpers; using Vrh.OneReport.Lib.Areas.OneReport.XMLParametersFileProcessor; using Vrh.OneReport.Lib.Areas.OneReport.DbModels; namespace Vrh.OneReport.Lib.Areas.OneReport.Models { /// /// Structure to hold the data of a report. /// public class Report { #region Static private variables /// /// Object to handle XMl document. /// protected static XmlDocument Xml = null; /// /// XML namespace. /// protected const string XmlNamespace = "http://www.vonalkod.hu/Log4Pro"; /// /// XML namespace manager object. /// protected static XmlNamespaceManager XmlNSManager = null; #endregion //Static private variables #region private variables /// /// If true, the report can be generated. /// private bool _isCompleted = false; /// /// The list of report (rdl) parameters. /// protected Dictionary RdlParameters = new Dictionary(); /// /// The report ID used in html environment. /// protected string ID; /// /// Report is OneReport /// private bool isOneReport = false; #endregion //private variables #region public variables /// /// Report is OneReport /// public bool IsOneReport { get { return isOneReport; } set { isOneReport = value; } } /// /// The name of the report. /// public string Name { get; set; } /// /// The description of the report. /// public string Description { get; set; } /// /// The filename of the report. /// public string FileName { get; set; } /// /// The parameters of the report (must be filled by the user). /// public Dictionary Parameters = new Dictionary(); /// /// List of the parameters (in the displaying sequence). /// public List ParameterList = null; /// /// List of the datasets used by the report. /// public List DataSets = new List(); /// /// The number of columns in displaying the parameters' dialog. /// public int MaxCol = 0; /// /// If true, the report can be generated. /// public bool isCompleted { get { return Parameters.Count == 0 || _isCompleted; } set { _isCompleted = value; } } /// /// The list of report (rdl) parameters in whitch the parameters are replaced by their values. /// public Dictionary RdlEvaluatedParameters = new Dictionary(); #endregion //public variables #region Static public methods /// /// Creates a report list from the report definitions XML file. /// The report list grouped by user roles. /// public static List ReadReports() { List rv = new List(); List roles = (Roles.Enabled ? new List(Roles.GetRolesForUser()) : new List()); Dictionary ugm_map = new Dictionary(); roles.Insert(0, "*"); foreach (var role in roles) { ReadReportsForRole(role, rv, ugm_map); } for (int i = 0; i < rv.Count; i++) { if (rv[i].Items.Count == 0) { rv.RemoveAt(i); i--; } } if (rv != null && rv.Count > 0 && !rv.Any(x => x.GroupState == UserRoleGroupModel.ItemGroupState.Opened)) { var item = rv.FirstOrDefault(x => x.GroupState == UserRoleGroupModel.ItemGroupState.Unknown); if (item != null) item.GroupState = UserRoleGroupModel.ItemGroupState.Opened; } return rv; } /// /// Creates a report list belonging to the given user role from the report definitions XML file. /// A user role. /// List of the usergroups. /// Hashmap of the used userroles (internal use). /// public static void ReadReportsForRole(string userrole, List groups, Dictionary ugm_map) { UserRoleGroupModel ugm = null; ItemDescriptor item; XmlNode general; LangHelperNS langHelper = LangHelperNS.ReportXML; string defaultName = string.Format(LangHelperNS.ReportView.GetTranslation("Report.UsersReportsText", "{0} listái"), (userrole == "*" ? LangHelperNS.QueryView.GetTranslation("Report.UserText", "Felhasználó") : userrole)); try { if (Xml == null) { Xml = new XmlDocument(); Xml.Load(GetReportFileName()); XmlNSManager = new XmlNamespaceManager(Xml.NameTable); XmlNSManager.AddNamespace("vlp", XmlNamespace); } if ((general = Xml.SelectSingleNode("vlp:userroles/vlp:general", XmlNSManager)) != null) { foreach (XmlNode node in general.ChildNodes) { if (node.NodeType == XmlNodeType.Element) { switch (node.Name.ToLower()) { case "wordcodeseparator": string sep = node.InnerText.Trim(); if (sep.Length > 0) langHelper = new LangHelperNS(langHelper, sep, sep, sep); break; } } } } foreach (XmlNode node in Xml.SelectNodes("vlp:userroles/vlp:userrole", XmlNSManager)) { string rolename = node.GetAttributeValue("name"); string groupname = node.GetAttributeValue("ReportGroupName", node.GetAttributeValue("GroupName")); string isopened = node.GetAttributeValue("IniStateOpen"); bool bOK = false; XmlNode subnode; string itemname, itemid; if (rolename == null) { bOK = (userrole == "*"); } else { string[] rolelist = rolename.Split(new char[] { ';' }); foreach (var r in rolelist) { if (r.Equals(userrole, StringComparison.CurrentCultureIgnoreCase)) { bOK = true; break; } } } if (bOK) { if (string.IsNullOrEmpty(groupname)) { groupname = defaultName; } else { groupname = langHelper.TranslateText(null, groupname); } if (ugm_map.ContainsKey(groupname)) { ugm = ugm_map[groupname]; } else { ugm = new UserRoleGroupModel(); ugm.Name = groupname; ugm.Items = new List(); ugm.GroupState = UserRoleGroupModel.ItemGroupState.Unknown; ugm_map.Add(ugm.Name, ugm); groups.Add(ugm); } if (isopened != null) { if ((isopened.ToLower().StartsWith("t") || isopened.ToLower().StartsWith("i"))) ugm.GroupState = UserRoleGroupModel.ItemGroupState.Opened; else if (ugm.GroupState == UserRoleGroupModel.ItemGroupState.Unknown) ugm.GroupState = UserRoleGroupModel.ItemGroupState.Closed; } foreach (XmlNode reportnode in node.SelectNodes("vlp:reports/vlp:report", XmlNSManager)) { if ((subnode = reportnode.SelectSingleNode("vlp:id", XmlNSManager)) == null) { //log: név nélküli report #if (DEBUG) itemid = "Nevtelen!!!!!!!!!!!!!"; #else itemid = null; #endif } else { itemid = subnode.InnerText.Trim(); } if (itemid != null) { item = new ItemDescriptor(); item.Action = "Display"; item.Controller = "Report"; if ((subnode = reportnode.SelectSingleNode("vlp:name", XmlNSManager)) == null) { itemname = langHelper.GetTranslation(itemid); } else { itemname = langHelper.TranslateText(null, subnode.InnerText); } if ((subnode = reportnode.SelectSingleNode("vlp:description", XmlNSManager)) == null) { item.Description = itemname; } else { item.Description = langHelper.TranslateText(null, subnode.InnerText); } item.Name = itemname; item.Params = new { role = userrole, report = itemid }; ugm.Items.Add(item); } } } } } catch (Exception e) { Log.Error(e.Message); } Xml = null; XmlNSManager = null; } /// /// Creates a report object representing the given report from the report definitions XML file. /// A user role. /// The ID of the report. /// The value of the report parameters. /// Object to hold the error messages. /// A report. /// public static Report ReadReport(string userrole, string listname, NameValueCollection parameters = null, ModelStateDictionary modelstate = null) { LangHelperNS langHelper = LangHelperNS.ReportXML; if (string.IsNullOrEmpty(userrole)) userrole = "*"; List directories = new List(); string generalconnectionstring = null; bool bOK = (userrole == "*"); Report rv = null; XmlNode general; if (!bOK) { foreach (var r in Roles.GetRolesForUser()) { if (r.Equals(userrole, StringComparison.CurrentCultureIgnoreCase)) { bOK = true; break; } } } if (bOK) { rv = new Report(); if (Xml == null) { Xml = new XmlDocument(); Xml.Load(GetReportFileName()); XmlNSManager = new XmlNamespaceManager(Xml.NameTable); XmlNSManager.AddNamespace("vlp", XmlNamespace); } if ((general = Xml.SelectSingleNode("vlp:userroles/vlp:general", XmlNSManager)) != null) { foreach (XmlNode node in general.ChildNodes) { if (node.NodeType == XmlNodeType.Element) { switch (node.Name.ToLower()) { case "directory": directories.Add(node.InnerText); break; case "connectionstring": generalconnectionstring = node.InnerText; break; case "wordcodeseparator": string sep = node.InnerText.Trim(); if (sep.Length > 0) langHelper = new LangHelperNS(langHelper, sep, sep, sep); break; default: Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownTypeInGeneralText", "Ismeretlen típus: {0}, helye 'userroles/general'"), node.Name)); break; } } } } foreach (XmlNode node in Xml.SelectNodes("vlp:userroles/vlp:userrole", XmlNSManager)) { string rolename = node.GetAttributeValue("name"); if (rolename == null) { bOK = (userrole == "*"); } else { string[] rolelist = rolename.Split(new char[] { ';' }); foreach (var r in rolelist) { if (r.Equals(userrole, StringComparison.CurrentCultureIgnoreCase)) { bOK = true; break; } } } if (bOK) { XmlNode report = node.SelectSingleNode(string.Format("vlp:reports/vlp:report[vlp:id=\"{0}\"]", listname), XmlNSManager); if (report != null) { List dirs = new List(directories); //connectionstring = generalconnectionstring; //if ((general = report.SelectSingleNode("vlp:general", XmlNSManager)) != null) //{ // foreach (XmlNode n in general.ChildNodes) // { // if (n.NodeType == XmlNodeType.Element) // { // switch (n.Name) // { // case "directory": // dirs.Add(n.InnerText); // break; // case "connectionstring": // connectionstring = n.InnerText; // break; // default: // Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownTypeInListText", "Ismeretlen típus: {0}, lista ID: '{1}'"), node.Name, listname)); // break; // } // } // } //} //rv = ReadReport(report, dirs, connectionstring, listname); rv = ReadReport(report, dirs, generalconnectionstring, listname, langHelper); break; } } } Xml = null; } else { Log.Info(string.Format(LangHelperNS.ReportView.GetTranslation("Report.NoAccessRightText", "Nincs a '{0}' felhasználónak joga a '{1}/{2}' listához."), System.Web.HttpContext.Current.User.Identity.Name, userrole, listname)); } return rv; } #endregion //Static public methods #region Static private methods /// /// Gets the report definitions XML file name with path. /// The report definitions XML file name with path. /// private static string GetReportFileName() { string reportfile = WebConfigurationManager.AppSettings.Get("ReportsDescriptorsXMLFile"); if (!string.IsNullOrEmpty(reportfile) || !File.Exists(reportfile)) reportfile = System.Web.HttpContext.Current.Server.MapPath(reportfile); if (string.IsNullOrEmpty(reportfile) || !File.Exists(reportfile)) reportfile = System.Web.HttpContext.Current.Server.MapPath("~/App_Data/Reports/Reports.xml"); return reportfile; } /// /// Gets the report definitions from an XML structure. /// A node containing the report definition. /// A list of directories where the report file (rdlc) can be searched for. /// A default connectionstring. /// The searched name of the report. /// The (default) translator object. /// A report. /// private static Report ReadReport(XmlNode report, List dirs, string connectionstring, string listname, LangHelperNS defaultLangHelper) { Report rv = new Report(); string connstr = connectionstring, file, paramname, paramvalue, filepath; string[] fileinfo = new string[2]; List directory_list = new List(dirs); XmlNode general; LangHelperNS langHelper = defaultLangHelper; rv.Name = listname; if ((general = report.SelectSingleNode("vlp:general", XmlNSManager)) != null) { foreach (XmlNode n in general.ChildNodes) { if (n.NodeType == XmlNodeType.Element) { switch (n.Name) { case "directory": directory_list.Add(n.InnerText); break; case "connectionstring": connstr = n.InnerText; break; case "wordcodeseparator": string sep = n.InnerText.Trim(); if (sep.Length > 0) langHelper = new LangHelperNS(langHelper, sep, sep, sep); break; default: Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownTypeInReportText", "Ismeretlen típus: {0}, lista ID: '{1}'"), n.Name, listname)); break; } } } } foreach (XmlNode node in report.ChildNodes) { if (node.NodeType == XmlNodeType.Element) { switch (node.Name.ToLower()) { case "reportfile": foreach (XmlNode n in node.ChildNodes) { if (n.NodeType == XmlNodeType.Element) { switch (n.Name.ToLower()) { case "name": case "filename": file = langHelper.TranslateText(null, n.InnerText.Trim()); if (File.Exists(file)) { rv.FileName = file; } else { fileinfo[1] = file; foreach (string dir in dirs) { fileinfo[0] = dir; filepath = Path.Combine(fileinfo); if (File.Exists(filepath)) { rv.FileName = filepath; break; } else if (File.Exists(System.Web.HttpContext.Current.Server.MapPath("~/" + filepath))) { rv.FileName = System.Web.HttpContext.Current.Server.MapPath("~/" + filepath); break; } } } break; case "param": paramname = n.GetAttributeValue("name"); if (!string.IsNullOrEmpty(paramname)) { paramvalue = n.GetNodeValue(); if (rv.RdlParameters.ContainsKey(paramname)) { Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.DuplicateParameterNameText", "Duplikált paraméter (reportfile/param): {0}, lista ID: '{1}'"), paramname, listname)); } else { rv.RdlParameters.Add(paramname, paramvalue); } } else { Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.MissingParameterNameText", "Hiányzó paraméternév (reportfile/param), lista ID: '{0}'"), listname)); } break; default: Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownTypeInReportText", "Ismeretlen típus: {0}, lista ID: '{1}'"), n.Name, listname)); break; } } } break; case "name": rv.Name = langHelper.TranslateText(null, node.InnerText.Trim()); break; case "id": rv.ID = node.InnerText.Trim(); break; case "description": rv.Description = langHelper.TranslateText(null, node.InnerText.Trim()); break; case "datasets": ReadDataSetDescriptor(node, rv, connstr, langHelper); break; case "parameters": ReadParameterDescriptor(node, rv, connstr, langHelper); break; case "general": //Már korábban megtörtént. break; default: Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownTypeInReportText", "Ismeretlen típus: {0}, lista ID: '{1}'"), node.Name, listname)); break; } } } return rv; } /// /// Reads the parameter descriptor part of the report definition. /// A node containing the parameter descriptors. /// A default connectionstring. /// The translator object. /// protected static void ReadParameterDescriptor(XmlNode parameters, Report report, string connectionstring, LangHelperNS langHelper) { ReportParameter rp; XmlNode subnode; string val, optiontextfield, optionidfield, filter, id; ParameterReplace pr; foreach (XmlNode parameter in parameters.SelectNodes("vlp:parameter", XmlNSManager)) { val = parameter.GetAttributeValue("name"); if (string.IsNullOrEmpty(val)) { Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.NoParameterNameText", "Nincs neve a paraméternek (lista ID: '{0}')!"), report.Name)); } else { rp = new ReportParameter(); rp.Label = rp.Name = val; if (!string.IsNullOrEmpty(rp.Name)) { rp.Paramtype = parameter.GetAttributeValue("type", rp.Paramtype); rp.ReplaceID = parameter.GetAttributeValue("replacestring", "@" + rp.Name + '@'); while (report.Parameters.ContainsKey(rp.ID)) { rp.Name += "_"; } report.Parameters.Add(rp.ID, rp); foreach (XmlNode node in parameter.ChildNodes) { if (node.NodeType == XmlNodeType.Element) { switch (node.Name.ToLower()) { case "label": rp.Label = langHelper.TranslateText(null, node.GetNodeValue()); break; case "value": rp.DefaultValue = node.GetAttributeValue("default", node.GetNodeValue()); rp.MinValue = node.GetAttributeValue("min"); rp.MaxValue = node.GetAttributeValue("max"); break; case "required": rp.Required = true; break; case "showseconds": rp.ShowSeconds = true; break; case "length": val = node.GetNodeValue(); if (!string.IsNullOrEmpty(val)) rp.MinLength = rp.MaxLength = int.Parse(val); val = node.GetAttributeValue("min"); if (!string.IsNullOrEmpty(val)) rp.MinLength = int.Parse(val); val = node.GetAttributeValue("max"); if (!string.IsNullOrEmpty(val)) rp.MaxLength = int.Parse(val); break; case "display": val = node.GetAttributeValue("row"); if (!string.IsNullOrEmpty(val)) rp.Row = int.Parse(val); val = node.GetAttributeValue("col"); if (!string.IsNullOrEmpty(val)) rp.Column = int.Parse(val); break; case "spincontrol": if (rp.Paramtype.Equals("I", StringComparison.CurrentCultureIgnoreCase)) { rp.SpinControl = new SpinControl(); val = node.GetAttributeValue("inc"); if (!string.IsNullOrEmpty(val)) rp.SpinControl.SpinIncrementValue = int.Parse(val); val = node.GetAttributeValue("dec"); if (!string.IsNullOrEmpty(val)) rp.SpinControl.SpinDecrementValue = int.Parse(val); if (rp.SpinControl.SpinIncrementValue == 0) rp.SpinControl.SpinIncrementValue = 1; if (rp.SpinControl.SpinDecrementValue == 0) rp.SpinControl.SpinDecrementValue = 1; if (rp.MinValue == null) rp.MinValue = int.MinValue; if (rp.MaxValue == null) rp.MaxValue = int.MaxValue; } else { Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.SpinControlMisuseText", "Spincontrol jelenleg csak egész számoknál használható (paraméter '{0}', lista ID: '{1}')"), rp.Name, report.Name)); } break; case "sqlreplace": id = node.GetAttributeValue("replacestring", rp.ReplaceID); if (rp.Replaces.ContainsKey(id)) { Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.DuplicateSQLReplaceText", "Duplikált 'sqlreplace' azonosító: {0} (paraméter: '{1}', lista ID: '{2}')"), id, rp.Name, report.Name)); } else { pr = new ParameterReplace(id); foreach (XmlNode value in node.SelectNodes("vlp:replace", XmlNSManager)) { val = value.InnerText.Trim(); if (value.Attributes.GetNamedItem("value") == null) filter = null; else filter = value.Attributes.GetNamedItem("value").Value; pr.AddItem(filter, val); } rp.Replaces.Add(id, pr); } break; case "values": if (rp.SelectOptions == null) rp.SelectOptions = new List(); foreach (XmlNode value in node.SelectNodes("vlp:value", XmlNSManager)) { val = value.GetNodeValue(); id = value.GetAttributeValue("id", val); rp.SelectOptions.Add(new SelectListItem() { Value = id, Text = val }); } if (rp.SelectOptions.Count > 0) { rp.WidgetType = "DropDownList"; } break; case "reference": optiontextfield = optionidfield = null; if ((subnode = node.SelectSingleNode("vlp:optiontext", XmlNSManager)) != null) { optiontextfield = subnode.GetNodeValue(); } if ((subnode = node.SelectSingleNode("vlp:optionid", XmlNSManager)) != null) { optionidfield = subnode.GetNodeValue(); } if (optionidfield == null) optionidfield = optiontextfield; if (optiontextfield == null) optiontextfield = optionidfield; if ((subnode = node.SelectSingleNode("vlp:sql", XmlNSManager)) == null) { Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.InvalidReferenceDefinitionText", "Hibás referencia definíció, nincs 'sql' rész (paraméter: '{0}', lista ID: '{1}')"), rp.Name, report.Name)); #if (DEBUG) if (rp.SelectOptions == null) { rp.SelectOptions = new List(); rp.SelectOptions.Add(new SelectListItem() { Text = "Invalid reference!", Value = "Error", Selected = true }); rp.WidgetType = "DropDownList"; } #endif } else { rp.Reference = new ParameterReference(); rp.Reference.Command = new DatabaseCommand(subnode.InnerText.Trim(), connectionstring, DatabaseCommand.DBCommandType.SQL); rp.Reference.IDField = optionidfield; rp.Reference.NameField = optiontextfield; rp.WidgetType = "AutocompleteTextBox"; val = subnode.GetAttributeValue("type"); if (!string.IsNullOrEmpty(val) && val.Equals("proc", StringComparison.CurrentCultureIgnoreCase)) { rp.Reference.Command.CommandType = DatabaseCommand.DBCommandType.StoredProcedure; } if ((subnode = node.SelectSingleNode("vlp:connectionstring", XmlNSManager)) != null) { rp.Reference.Command.ConnectionString = subnode.GetNodeValue(); } if (String.IsNullOrEmpty(rp.Reference.Command.ConnectionString)) { rp.Reference.Command.ConnectionString = ConnectionStringStore.Get(QueryContext.APM_CONTEXT_NAME); } } break; default: Log.Warning(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownTypeInReportText", "Ismeretlen típus: {0}, lista ID: '{1}'"), node.Name, report.Name)); break; } } } } } } report.ArrangeParams(); report.SetDefaultValues(); } /// /// Reads the dataset descriptor part of the report definition. /// A node containing the dataset descriptors. /// A default connectionstring. /// The translator object. /// protected static void ReadDataSetDescriptor(XmlNode datasets, Report report, string connstr, LangHelperNS langHelper) { DataSetDescriptor ds; string connectionstr, value, id, command; XmlNode sqlnode; foreach (XmlNode dataset in datasets.SelectNodes("vlp:dataset", XmlNSManager)) { value = dataset.GetAttributeValue("name"); if (string.IsNullOrEmpty(value)) { Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.InvalidDatasetDefinitionText", "Hibás adatkapcsolatleíró (lista ID = {0}): nincs megadva név!"), report.ID)); } else { ds = new DataSetDescriptor(); ds.Name = value; connectionstr = !String.IsNullOrEmpty(connstr) ? connstr : ConnectionStringStore.Get(QueryContext.APM_CONTEXT_NAME); foreach (XmlNode node in dataset.ChildNodes) { if (node.NodeType == XmlNodeType.Element) { switch (node.Name) { case "initcommand": sqlnode = node.SelectSingleNode("vlp:sql", XmlNSManager); command = (sqlnode == null ? node.GetNodeValue() : sqlnode.GetNodeValue()); if (!string.IsNullOrEmpty(command)) { System.Data.DbType type; command = langHelper.TranslateText(null, command); ds.InitCommand = new DatabaseCommand(command, connectionstr, DatabaseCommand.DBCommandType.SQL); value = node.GetAttributeValue("type"); if (!string.IsNullOrEmpty(value) && value.Equals("proc", StringComparison.CurrentCultureIgnoreCase)) { ds.InitCommand.CommandType = DatabaseCommand.DBCommandType.StoredProcedure; } foreach (XmlNode param in node.SelectNodes("vlp:param", XmlNSManager)) { id = param.GetAttributeValue("name"); if (string.IsNullOrEmpty(id)) { Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.NoSQLParameterNameText", "Névtelen SQL paraméter (adatkapcsolat neve: '{0}', lista ID: '{1}')!"), ds.Name, report.ID)); } else { value = param.GetNodeValue(); type = DatabaseCommand.GetDBTypeFromName(param.GetAttributeValue("type")); ds.InitCommand.AddParameter(id, type, value); } } } break; case "datacommand": sqlnode = node.SelectSingleNode("vlp:sql", XmlNSManager); command = (sqlnode == null ? node.GetNodeValue() : sqlnode.GetNodeValue()); if (!string.IsNullOrEmpty(command)) { System.Data.DbType type; command = langHelper.TranslateText(null, command); ds.DataCommand = new DatabaseCommand(command, connectionstr, DatabaseCommand.DBCommandType.SQL); value = node.GetAttributeValue("type"); if (!string.IsNullOrEmpty(value) && value.Equals("proc", StringComparison.CurrentCultureIgnoreCase)) { ds.DataCommand.CommandType = DatabaseCommand.DBCommandType.StoredProcedure; } foreach (XmlNode param in node.SelectNodes("vlp:param", XmlNSManager)) { id = param.GetAttributeValue("name"); if (string.IsNullOrEmpty(id)) { Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.NoSQLParameterNameText", "Névtelen SQL paraméter (adatkapcsolat neve: '{0}', lista ID: '{1}')!"), ds.Name, report.ID)); } else { value = param.GetNodeValue(); type = DatabaseCommand.GetDBTypeFromName(param.GetAttributeValue("type")); ds.DataCommand.AddParameter(id, type, value); } } } break; case "connectionstring": connectionstr = node.GetNodeValue(); break; default: Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownTypeInDatasetDescriptionText", "Hibás adatkapcsolatleíró (name = {0}): ismeretlen típus: {1} (lista ID: '{2}')!"), ds.Name, node.Name, report.ID)); break; } } } if (ds.DataCommand == null) { Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.NoDataCommandInDatasetDescriptionText", "Hibás adatkapcsolatleíró (name = {0}): nincs 'datacommand' elem (lista ID: '{1}')!"), ds.Name, report.ID)); } else { ds.DataCommand.ConnectionString = connectionstr; if (ds.InitCommand != null) ds.InitCommand.ConnectionString = connectionstr; report.DataSets.Add(ds); } } } } #endregion //Static private methods #region private methods /// /// Arranges the parameters into a table to display in the dialog. /// private bool ArrangeParams() { int idx, colidx; SortedList> sl = new SortedList>(); SortedList columns = new SortedList(); SortedList row; List orphans = new List(); ReportParameter last = null; foreach (var item in Parameters) { if (item.Value.WidgetType != "hidden") { if (item.Value.Row >= 0) { if (sl.ContainsKey(item.Value.Row)) { row = sl[item.Value.Row]; } else { row = new SortedList(); sl.Add(item.Value.Row, row); } } else { row = columns; } if (item.Value.Column >= 0) { if (item.Value.Column > MaxCol) MaxCol = item.Value.Column; row.Add(item.Value.Column, item.Value); } else { orphans.Add(item.Value); } } } foreach (var column in columns) { idx = 0; while (sl.ContainsKey(idx) && sl[idx].ContainsKey(column.Key)) idx++; if (!sl.ContainsKey(idx)) { sl.Add(idx, new SortedList()); } sl[idx].Add(column.Key, column.Value); column.Value.Row = idx; } columns = null; idx = 0; foreach (var item in orphans) { while (sl.ContainsKey(idx) && sl[idx].Count > MaxCol) idx++; if (!sl.ContainsKey(idx)) { sl.Add(idx, new SortedList()); } row = sl[idx]; colidx = 0; while (row.ContainsKey(colidx)) colidx++; row.Add(colidx, item); item.Row = idx; item.Column = colidx; } ParameterList = new List(); foreach (var r in sl) { foreach (var c in r.Value) { if (last != null) { if (c.Value.Row == last.Row) last.ColSpan = c.Value.Column - last.Column; else last.ColSpan = MaxCol - last.Column + 1; } last = c.Value; ParameterList.Add(c.Value); } } if (last != null) { last.ColSpan = MaxCol - last.Column + 1; } return true; } /// /// Sets the default values of the dropdownlist widgets. /// private void SetDefaultValues() { ReportParameter rp; object dv; foreach (var item in Parameters) { rp = item.Value; if ((dv = rp.DefaultValue) != null && rp.SelectOptions != null) { foreach (var option in rp.SelectOptions) { if (option.Value.Equals(dv)) option.Selected = true; } } } } #endregion //private methods #region internal methods /// /// Checks the parameter values and prepares the report for displaying. /// The parameter values given by the user. /// The object for the error messages. /// internal void CheckValues(NameValueCollection parameters, ModelStateDictionary modelstate, bool bForceParamDisplay) { ReportParameter rp; if (parameters == null) { _isCompleted = !Parameters.Any(p => p.Value.WidgetType != "hidden"); } else { for (int i = 0; i < parameters.Count; i++) { if (Parameters.ContainsKey(parameters.GetKey(i))) { Parameters[parameters.GetKey(i)].ResetError(); Parameters[parameters.GetKey(i)].DefaultValue = parameters.Get(i); } } _isCompleted = !bForceParamDisplay && string.IsNullOrEmpty(parameters.Get("paramButton")); if (_isCompleted) foreach (var item in Parameters) { if (!item.Value.CheckValue(modelstate)) _isCompleted = false; } } if (_isCompleted) { Dictionary sql_replaces = new Dictionary(); Dictionary param_replaces = new Dictionary(); //composing sqls RdlEvaluatedParameters.Clear(); foreach (var item in Parameters) { rp = item.Value; if (rp.Replaces.Count > 0) foreach (var repl in rp.Replaces) { sql_replaces.Add(repl.Key, repl.Value.GetReplaceValue(rp, false)); param_replaces.Add(repl.Key, repl.Value.GetReplaceValue(rp, true)); } if (!sql_replaces.ContainsKey(rp.ReplaceID)) sql_replaces.Add(rp.ReplaceID, rp.GetSQLValue(false)); if (!param_replaces.ContainsKey(rp.ReplaceID)) param_replaces.Add(rp.ReplaceID, rp.GetSQLValue(true)); } foreach (var ds in DataSets) { if (ds.DataCommand != null) { ds.DataCommand.PrepareSQLCommand(sql_replaces, param_replaces); } if (ds.InitCommand != null) { ds.InitCommand.PrepareSQLCommand(sql_replaces, param_replaces); } } foreach (var rdlparam in RdlParameters) { if (rdlparam.Value != null) { if (RdlEvaluatedParameters.ContainsKey(rdlparam.Key)) RdlEvaluatedParameters[rdlparam.Key] = RdlEvaluatedParameters[rdlparam.Key].Replace(param_replaces); else RdlEvaluatedParameters.Add(rdlparam.Key, rdlparam.Value.Replace(param_replaces)); } } //Nullozza az üreseket. var RdlKeys = new List(RdlEvaluatedParameters.Keys); foreach (var key in RdlKeys) { if (string.IsNullOrEmpty(RdlEvaluatedParameters[key])) { RdlEvaluatedParameters[key] = null; } } } if (string.IsNullOrEmpty(FileName)) { string msg = string.Format(LangHelperNS.ReportView.GetTranslation("Report.NoReportFileNameText", "Nncs megadva vagy hibás a report fájl neve (lista ID: '{0}')"), this.ID); Log.Warning(msg); modelstate.AddModelError(string.Empty, msg); _isCompleted = false; } } #endregion //internal methods } /// /// Structure to hold data for a parameter description. /// public class ReportParameter { #region private variables /// /// The error indicator. /// private bool _bError; /// /// The minimum value if any. /// private string _MinValue; /// /// The maximum value if any. /// private string _MaxValue; /// /// The data type of the parameter. /// Valid options are: C (text), I (integer), F (float), B (bool), D (date), T (time), DT (datetime). /// private string _Paramtype = "C"; /// /// The (default) value of the parameter in string format. /// private string _DefaultValue; /// /// The (default) code value of the parameter in string format. /// private string _DefaultCodeValue; #endregion private variables #region public variables /// /// The possible values of caracter case. /// public enum FieldCaseType { Normal, Uppercase, Lowercase } /// /// The name of the parameter. /// public string Name; /// /// The text representing the given parameter in texts. It will be replaced with the value. /// public string ReplaceID; /// /// The ID of the parameter used in html environment. /// public string ID { get { return Name.Trim(new char[] { '@', '[', ']', ' ', '\t', '{', '}', '(', ')', '&', '#' }); } } /// /// The label of the parameter used in the parameter window. /// public string Label = null; /// /// If the value of the property is true than the seconds are displayed and used. /// Valid only for datetime and time in DateTimeBox and TimeBox. /// public bool ShowSeconds = false; /// /// Stores the spincontrol extra data if the control is to be displayed. Valid only for integer. /// public SpinControl SpinControl = null; /// /// The type of the widget used for data input. /// Values are: TextBox (default), CheckBox, DateBox, DateTimeBox, TimeBox, DropDownList, AutocompleteTextBox. /// public string WidgetType = "TextBox"; /// /// The case attribute of the widget text. Valid only for texts. /// public FieldCaseType FieldCase = FieldCaseType.Normal; /// /// The list of the choosable options. Valid only for 'DropDownList'. /// public List SelectOptions = null; /// /// The list of characters that can be used in the given control. Empty string means no restriction. /// public string FilterCharacterSet = ""; /// /// The row number where the control is to be placed (starts from 0). If its value is -1, the control will be placed automatically. /// public int Row = -1; /// /// The column number where the control is to be placed (starts from 0). If its value is -1, the control will be placed automatically. /// public int Column = -1; /// /// The number of columns used by the control. /// public int ColSpan = 1; /// /// The minimum value if any. /// public object MinValue { get { return GetValue(_MinValue); } set { _MinValue = (string)value; } } /// /// The maximum value if any. /// public object MaxValue { get { return GetValue(_MaxValue); } set { _MaxValue = (string)value; } } /// /// If its value is true, the field is required. /// public bool Required = false; /// /// Minimum value of text's length if any. /// public int MinLength; /// /// Maximum value of text's length if any. /// public int MaxLength; /// /// The data type of the parameter. /// Valid options are: C (text), I (integer), F (float), B (bool), D (date), T (time), DT (datetime), X:Name (extra, rejtett paraméterek). /// public string Paramtype { get { return _Paramtype; } set { _Paramtype = value.ToUpper(); switch (_Paramtype) { case "C": //text WidgetType = "TextBox"; FieldCase = ReportParameter.FieldCaseType.Normal; break; case "E": //expression WidgetType = "TextBox"; FieldCase = ReportParameter.FieldCaseType.Normal; break; case "I": //integer WidgetType = "TextBox"; FieldCase = ReportParameter.FieldCaseType.Normal; FilterCharacterSet = "0123456789 +-"; break; case "F": //float WidgetType = "TextBox"; FieldCase = ReportParameter.FieldCaseType.Normal; FilterCharacterSet = "0123456789 .,+-"; break; case "B": //bool WidgetType = "CheckBox"; break; case "D": //date WidgetType = "DateBox"; break; case "DT": //datetime WidgetType = "DateTimeBox"; break; case "T": //time WidgetType = "TimeBox"; break; default: if (_Paramtype.StartsWith("X:")) { WidgetType = "hidden"; SetInternalParameter(_Paramtype.Substring(2)); } else { //hibás típus } break; } } } /// /// The (default) value of the parameter in native format (corresponding to Paramtype). /// public object DefaultValue { get { return GetValue(_DefaultValue); } set { _DefaultValue = (string)value; if (this.SelectOptions != null) { _DefaultCodeValue = _DefaultValue; } else if (this.Reference != null) { _DefaultCodeValue = this.GetCodeValue(); } else { _DefaultCodeValue = _DefaultValue; } } } /// /// The (default) code value of the parameter in native format (corresponding to Paramtype). /// public object DefaultCodeValue { get { return GetValue(_DefaultCodeValue); } } /// /// The list of the replaces of the parameter. /// public Dictionary Replaces = new Dictionary(); /// /// The ParameterReference object if any. /// public ParameterReference Reference; #endregion //public variables /// /// Sets the value of an internal parameters. /// The name of the parameter. /// private void SetInternalParameter(string p) { MembershipUser user; switch (p.ToUpper()) { case "USERID": _Paramtype = "G"; user = System.Web.Security.Membership.GetUser(); if (user != null) DefaultValue = user.ProviderUserKey.ToString(); break; case "USERNAME": _Paramtype = "C"; user = System.Web.Security.Membership.GetUser(); if (user != null) DefaultValue = user.UserName; break; case "NOW": _Paramtype = "DT"; DefaultValue = "now"; break; case "TODAY": _Paramtype = "D"; DefaultValue = "today"; break; } } /// /// Converts the given string value to the parameters's native format. /// The string value to be converted. /// The converted value. /// private object GetValue(string p) { object rv = null; string s = (Paramtype.Equals("C") || p == null ? p : p.Split(new char[] { ',' })[0]); if (!string.IsNullOrEmpty(p)) { try { LangHelperNS langHelper = LangHelperNS.ReportView; System.Globalization.CultureInfo cultureInfo = System.Threading.Thread.CurrentThread.CurrentUICulture; string timeFormat = (ShowSeconds) ? (langHelper.GetTranslation("DateTime.LongTimeFormat", cultureInfo.DateTimeFormat.LongTimePattern)) : (langHelper.GetTranslation("DateTime.ShortTimeFormat", cultureInfo.DateTimeFormat.ShortTimePattern)), dateFormat = langHelper.GetTranslation("DateTime.DateFormat", cultureInfo.DateTimeFormat.ShortDatePattern); switch (Paramtype) { case "C": //text rv = s; break; case "I": //integer rv = int.Parse(s); break; case "F": //float rv = float.Parse(s, System.Globalization.CultureInfo.InvariantCulture); break; case "B": //bool rv = bool.Parse(s); break; case "D": //date rv = RelativeDateParser.Parse(s, dateFormat, null); break; case "DT": //datetime rv = RelativeDateParser.Parse(s, dateFormat, timeFormat); break; case "T": //time rv = RelativeDateParser.Parse(s, null, timeFormat); break; case "G": //guid rv = Guid.Parse(s); break; default: Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownDataTypeText", "Ismeretlen adattípus: {0}, paraméter neve: {1}"), this.Paramtype, this.Name)); this._bError = true; break; } } catch (Exception e) { Log.Error(e.Message); this._bError = true; } } return rv; } #region internal (public) methods /// /// Checks whether the given filter value matches to the value of the parameter. /// The filter value. /// True if the match is succeded. /// internal bool Matches(string p) { bool rv = false; object val = DefaultCodeValue; try { if (p == null) rv = true; else if (p.Length == 0) { rv = (val == null || (val is string && ((string)val).Length == 0)); } else { int i; string op = "="; object pval; for (i = 0; i < p.Length && ">=<".IndexOf(p[i]) >= 0; i++) ; if (i > 0) { op = p.Substring(0, i); p = p.Remove(0, i); } pval = GetValue(p); switch (op) { case "=": rv = pval.Equals(val); break; case "<": if (pval is string) { rv = (((string)pval).CompareTo(val) > 0); } else if (pval is int) { rv = (((int)pval).CompareTo(val) > 0); } else if (pval is float) { rv = (((float)pval).CompareTo(val) > 0); } else if (pval is DateTime) { rv = (((DateTime)pval).CompareTo(val) > 0); } else if (pval is bool) { rv = (((bool)pval).CompareTo(val) > 0); } break; case "<=": if (pval is string) { rv = (((string)pval).CompareTo(val) >= 0); } else if (pval is int) { rv = (((int)pval).CompareTo(val) >= 0); } else if (pval is float) { rv = (((float)pval).CompareTo(val) >= 0); } else if (pval is DateTime) { rv = (((DateTime)pval).CompareTo(val) >= 0); } else if (pval is bool) { rv = (((bool)pval).CompareTo(val) >= 0); } break; case ">": if (pval is string) { rv = (((string)pval).CompareTo(val) < 0); } else if (pval is int) { rv = (((int)pval).CompareTo(val) < 0); } else if (pval is float) { rv = (((float)pval).CompareTo(val) < 0); } else if (pval is DateTime) { rv = (((DateTime)pval).CompareTo(val) < 0); } else if (pval is bool) { rv = (((bool)pval).CompareTo(val) < 0); } break; case ">=": if (pval is string) { rv = (((string)pval).CompareTo(val) <= 0); } else if (pval is int) { rv = (((int)pval).CompareTo(val) <= 0); } else if (pval is float) { rv = (((float)pval).CompareTo(val) <= 0); } else if (pval is DateTime) { rv = (((DateTime)pval).CompareTo(val) <= 0); } else if (pval is bool) { rv = (((bool)pval).CompareTo(val) <= 0); } break; default: Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownOperatorText", "Ismeretlen operátor: '{0}', paraméter neve: {1}"), op, this.Name)); this._bError = true; break; } } } catch (Exception e) { rv = false; Log.Error(e.Message); this._bError = true; } return rv; } /// /// Gets the value of the parameter whitch can be used in an SQL expression. /// If true, the value will be used in a SQL parameter. /// The converted value. /// internal string GetSQLValue(bool bForSQLparams) { string rv = ""; object val = DefaultCodeValue; if (val != null) { try { switch (Paramtype) { case "C": //text if (bForSQLparams) rv = val as string; else rv = "'" + ((string)val).Replace("'", "''") + "'"; break; case "E": //expression rv = (string)val; break; case "I": //integer rv = ((int)val).ToString(); break; case "F": //float rv = ((float)val).ToString(); break; case "B": //bool rv = (((bool)val) ? "1" : "0"); break; case "D": //date rv = ((DateTime)val).ToString("s"); rv = rv.Remove(rv.IndexOf('T')); if (!bForSQLparams) { rv = "'" + rv + "'"; } break; case "DT": //datetime rv = ((DateTime)val).ToString("s").Replace('T', ' '); if (!ShowSeconds) rv = rv.Remove(rv.Length - 3); if (!bForSQLparams) { rv = "'" + rv + "'"; } break; case "T": //time rv = ((DateTime)val).ToString("s"); rv = rv.Remove(0, rv.IndexOf('T') + 1); if (!bForSQLparams) { rv = "'" + rv + "'"; } break; case "G": //guid rv = val.ToString(); if (!bForSQLparams) { rv = "'" + rv + "'"; } break; default: Log.Error(string.Format(LangHelperNS.ReportView.GetTranslation("Report.UnknownDataTypeText", "Ismeretlen adattípus: {0}, paraméter neve: {1}"), this.Paramtype, this.Name)); this._bError = true; break; } } catch (Exception e) { Log.Error(e.Message); this._bError = true; rv = ""; } } return rv; } /// /// Checks if the given values of parameters meet the criterias. /// The object used to collect the error messages. /// True if the parameters fulfill the conditions. /// internal bool CheckValue(ModelStateDictionary modelstate) { bool rv = true; if (this.DefaultCodeValue == null) { if (this.Required) { modelstate.AddModelError(this.ID, LangHelperNS.ReportView.GetTranslation("Report.FieldRequiredText", "A mezőt ki kell tölteni!")); rv = false; } } else if (this.MinLength > 0 && this._DefaultCodeValue.Length < this.MinLength) { modelstate.AddModelError(this.ID, string.Format(LangHelperNS.ReportView.GetTranslation("Report.StringTooShortText", "Túl rövid a mező tartalma (min. {0} karakter kell)!"), this.MinLength)); rv = false; } else if (this.MaxLength > 0 && this._DefaultCodeValue.Length > this.MaxLength) { modelstate.AddModelError(this.ID, string.Format(LangHelperNS.ReportView.GetTranslation("Report.StringTooLongText", "Túl hosszú a mező tartalma (max. {0} karakter lehet)!"), this.MaxLength)); rv = false; } else if (this.MinValue != null && Matches("<" + _MinValue)) { modelstate.AddModelError(this.ID, string.Format(LangHelperNS.ReportView.GetTranslation("Report.ValueTooSmallText", "Nagyobb értéket kell megadni (min. {0} kell)!"), this.MinValue)); rv = false; } else if (this.MaxValue != null && Matches(">" + _MaxValue)) { modelstate.AddModelError(this.ID, string.Format(LangHelperNS.ReportView.GetTranslation("Report.ValueTooLargeText", "Kisebb értéket kell megadni (max. {0} lehet)!"), this.MaxValue)); rv = false; } return rv && !this._bError; } /// /// Gets the text value of an autocomplete fields. /// The value or null. /// internal string GetCodeValue() { string rv = null; if (!string.IsNullOrEmpty(_DefaultValue) && Reference != null) { SqlConnection mySqlConnection = null; SqlCommand mySqlCommand = null; SqlDataReader mySqlDataReader = null; try { Reference.Command.PrepareSQLCommand(); mySqlConnection = new SqlConnection(); mySqlConnection.ConnectionString = Reference.Command.ConnectionString; mySqlCommand = new SqlCommand(); mySqlCommand.CommandText = Reference.Command.PreparedCommand; mySqlCommand.Parameters.Add(new SqlParameter("filter", _DefaultValue)); mySqlCommand.CommandType = System.Data.CommandType.Text; mySqlCommand.Connection = mySqlConnection; CommandParameter.SetCommandTimeout(mySqlCommand); mySqlCommand.Connection.Open(); mySqlDataReader = mySqlCommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection | System.Data.CommandBehavior.SingleRow); int iKod = mySqlDataReader.GetOrdinal(Reference.IDField); int iName = mySqlDataReader.GetOrdinal(Reference.NameField); if (mySqlDataReader.HasRows) { while (mySqlDataReader.Read()) { //rv = mySqlDataReader.GetString(iKod); //WIND 2014.05.28 Ezzel nem lehet részértéket megadni rv = string.Format("{0}", mySqlDataReader.GetValue(iKod)); //TG } //WIND 2014.05.28 Azért, hogy részértéket is meg lehessen adni if (rv.Contains(_DefaultValue)) rv = _DefaultValue; } else { Log.modelstate.AddModelError(this.ID, string.Format(LangHelperNS.ReportView.GetTranslation("Report.ValueNotFoundInDatabaseText", "Nincs ilyen érték az adatbázisban: {0}!"), _DefaultValue)); this._bError = true; } } catch (System.Exception e) { Log.Error(e.Message); this._bError = true; } if (mySqlDataReader != null) mySqlDataReader.Dispose(); if (mySqlCommand != null) mySqlCommand.Dispose(); if (mySqlConnection != null) mySqlConnection.Dispose(); } return rv; } /// /// Resets the error flag. /// internal void ResetError() { this._bError = false; } #endregion //internal (public) methods } /// /// Structure to hold the dataset descriptor values. /// public class DataSetDescriptor { /// /// The name of the dataset. Must meet to the dataset name in the report file (rdlc). /// public string Name; /// /// The command used to get the data. /// public DatabaseCommand DataCommand; /// /// The command used to init the data. /// public DatabaseCommand InitCommand; } /// /// Holds the data need for a spincontrol. /// public class SpinControl { /// /// The value of increment. /// public int SpinIncrementValue; /// /// The value of decrement. /// public int SpinDecrementValue; } /// /// Holds information and methods to handle parameter replacement. /// public class ParameterReplace { /// /// A filter - value pair. /// private class ReplaceItem { /// /// A filter text. The '>', '>=', '<', '<=' operators can be used. Lack of any operators means '=' operator. /// public string Filter; /// /// A text to replace if the value of the parameter matches the filter. /// public string Value; } #region private variables /// /// A string (id) to be replaced. /// private string ParamID; /// /// A list of filter - value pairs. /// private List Replaces; #endregion //private variables /// /// A constructor. /// The text to be replaced (parameter id). /// public ParameterReplace(string paramID) { this.ParamID = paramID; this.Replaces = new List(); } #region public Methods /// /// Add a filter - value pair to the list. /// The filter. /// A text to replace if the value of the parameter matches the filter. /// public void AddItem(string filter, string value) { Replaces.Add(new ReplaceItem() { Filter = filter, Value = value }); } /// /// Replaces the ID of the given parameter with the parameter's value in the first replace string where the filter /// matches the value of the parameter. /// A ReportParameter object. /// If true the value will be used in a SQL parameter. /// The found string in where the parameter§s ID is replaced with the parameter's value or null if no match is found. /// public string GetReplaceValue(ReportParameter rp, bool bForSQLParams) { foreach (var repl in Replaces) { if (rp.Matches(repl.Filter)) { return repl.Value.Replace(rp.ReplaceID, rp.GetSQLValue(bForSQLParams)); } } return null; } #endregion // public Methods } /// /// Holds the data for a parameter reference, i.e. an sql to collecting a table's data. /// public class ParameterReference { /// /// The command to get the reference data. /// public DatabaseCommand Command; /// /// The name of the 'ID' field. /// public string IDField; /// /// The name of the 'name' field. /// public string NameField; } /// /// Holds the data needed for a database command. /// public class DatabaseCommand { /// /// Possible types of a database command. /// public enum DBCommandType { None, SQL, StoredProcedure } #region private variables /// /// The SQL command or stored procedure. /// private string Command; /// /// The parameters of the command. /// private Dictionary CommandParameters; #endregion //private variables #region public variables /// /// The prepared command, ready to be executed. /// public string PreparedCommand; /// /// The prepared parameters, ready to be used in execution. /// public Dictionary PreparedCommandParameters; /// /// The type of the database command. /// public DBCommandType CommandType; /// /// The connection string. /// public string ConnectionString; #endregion //public variables /// /// The constructor. /// public DatabaseCommand(string Command, string ConnectionString, DBCommandType CommandType = DBCommandType.SQL) { this.Command = Command; this.ConnectionString = ConnectionString; this.CommandType = CommandType; } /// /// The empty. /// public DatabaseCommand() { this.CommandType = DBCommandType.None; } /// /// Prepares the command string and parameters for use. /// The key-value pairs used in the replacement of SQL statement. /// The key-value pairs used in the replacement of parameters. /// public void PrepareSQLCommand(Dictionary sql_values = null, Dictionary param_values = null) { if (sql_values == null) PreparedCommand = Command; else PreparedCommand = Command.Replace(sql_values); if (CommandParameters == null) { PreparedCommandParameters = null; } else { if (PreparedCommandParameters == null) PreparedCommandParameters = new Dictionary(); else PreparedCommandParameters.Clear(); if (param_values != null) { foreach (var item in CommandParameters) { PreparedCommandParameters.Add(item.Key, new CommandParameter(item.Value.Name, item.Value.Type, item.Value.Value.Replace(param_values))); } } else { PreparedCommandParameters = new Dictionary(CommandParameters); } } } /// /// Adds a SQL parameter to the parameter list. /// The parameter id. /// The parameter's type. /// The parameter value. /// public void AddParameter(string id, System.Data.DbType type, string val) { if (CommandParameters == null) CommandParameters = new Dictionary(); if (!CommandParameters.ContainsKey(id)) CommandParameters.Add(id, new CommandParameter(id, type, val)); } /// /// Gets the data type name. /// The data type name as string. /// The data type corrensponding the given parameter. /// public static System.Data.DbType GetDBTypeFromName(string type) { System.Data.DbType rv = System.Data.DbType.String; if (!string.IsNullOrEmpty(type)) { switch (type.ToLower()) { case "ansistring": //A variable-length stream of non-Unicode characters ranging between 1 and 8,000 characters. rv = System.Data.DbType.AnsiString; break; case "binary": //A variable-length stream of binary data ranging between 1 and 8,000 bytes. rv = System.Data.DbType.Binary; break; case "byte": //An 8-bit unsigned integer ranging in value from 0 to 255. rv = System.Data.DbType.Byte; break; case "boolean": //A simple type representing Boolean values of true or false. rv = System.Data.DbType.Boolean; break; case "currency": //A currency value ranging from -2 63 (or -922,337,203,685,477.5808) to 2 63 -1 (or +922,337,203,685,477.5807) with an accuracy to a ten-thousandth of a currency unit. rv = System.Data.DbType.Currency; break; case "date": //A type representing a date value. rv = System.Data.DbType.Date; break; case "datetime": //A type representing a date and time value. rv = System.Data.DbType.DateTime; break; case "decimal": //A simple type representing values ranging from 1.0 x 10 -28 to approximately 7.9 x 10 28 with 28-29 significant digits. rv = System.Data.DbType.Decimal; break; case "double": //A floating point type representing values ranging from approximately 5.0 x 10 -324 to 1.7 x 10 308 with a precision of 15-16 digits. rv = System.Data.DbType.Double; break; case "guid": //A globally unique identifier (or GUID). rv = System.Data.DbType.Guid; break; case "int16": //An integral type representing signed 16-bit integers with values between -32768 and 32767. rv = System.Data.DbType.Int16; break; case "int32": //An integral type representing signed 32-bit integers with values between -2147483648 and 2147483647. rv = System.Data.DbType.Int32; break; case "int64": //An integral type representing signed 64-bit integers with values between -9223372036854775808 and 9223372036854775807. rv = System.Data.DbType.Int64; break; case "object": //A general type representing any reference or value type not explicitly represented by another DbType value. rv = System.Data.DbType.Object; break; case "sbyte": //An integral type representing signed 8-bit integers with values between -128 and 127. rv = System.Data.DbType.SByte; break; case "single": //A floating point type representing values ranging from approximately 1.5 x 10 -45 to 3.4 x 10 38 with a precision of 7 digits. rv = System.Data.DbType.Single; break; case "string": //A type representing Unicode character strings. rv = System.Data.DbType.String; break; case "time": //A type representing a SQL Server DateTime value. If you want to use a SQL Server time value, use Time. rv = System.Data.DbType.Time; break; case "uint16": //An integral type representing unsigned 16-bit integers with values between 0 and 65535. rv = System.Data.DbType.UInt16; break; case "uint32": //An integral type representing unsigned 32-bit integers with values between 0 and 4294967295. rv = System.Data.DbType.UInt32; break; case "uint64": //An integral type representing unsigned 64-bit integers with values between 0 and 18446744073709551615. rv = System.Data.DbType.UInt64; break; case "varnumeric": //A variable-length numeric value. rv = System.Data.DbType.VarNumeric; break; case "ansistringfixedlength": //A fixed-length stream of non-Unicode characters. rv = System.Data.DbType.AnsiStringFixedLength; break; case "stringfixedlength": //A fixed-length string of Unicode characters. rv = System.Data.DbType.StringFixedLength; break; case "xml": //A parsed representation of an XML document or fragment. rv = System.Data.DbType.Xml; break; case "datetime2": //Date and time data. Date value range is from January 1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. rv = System.Data.DbType.DateTime2; break; case "datetimeoffset": //Date and time data with time zone awareness. Date value range is from January 1,1 AD through December 31, 9999 AD. Time value range is 00:00:00 through 23:59:59.9999999 with an accuracy of 100 nanoseconds. Time zone value range is -14:00 through +14:00. rv = System.Data.DbType.DateTimeOffset; break; } } return rv; } } }