How to add Textbox value to dynamic sql , so that value appears in Dataset
i brand new c# , bare me. trying add textbox field section of dynamic sql.
but every time add field errors. correct code add field in dataset?
i have posted full dynamic sql used dataset. how pull texboxsearchtext , rename reporttype, can map dataset?
public string getsummarysql() { string summarysql = "select " + "rtrim(data.cust_hier_d.cust_amid_lvl_4_id) cust_amid_lvl_4_id, " + "case when data.cust_hier_d.cust_amid_lvl_4_nm null '(null)' else rtrim(data.cust_hier_d.cust_amid_lvl_4_nm) end cust_amid_lvl_4_nm, " + "rtrim(data.cust_hier_d.cust_amid_lvl_2_id) cust_amid_lvl_2_id, " + "case when data.cust_hier_d.cust_amid_lvl_2_nm null '(null)' else rtrim(data.cust_hier_d.cust_amid_lvl_2_nm) end cust_amid_lvl_2_nm, " + "case when data.prod_ln_d.ext_seg_nm null '(null)' else rtrim(data.prod_ln_d.ext_seg_nm) end ext_seg_nm, " + "case when data.entprs_prod_d.prod_ln_nm null '(null)' else rtrim(data.entprs_prod_d.prod_ln_nm) end prod_ln_nm, " + "case when data.entprs_prod_d.prod_fmly_dn null '(null)' else rtrim(data.entprs_prod_d.prod_fmly_dn) end prod_fmly_dn, " + "case when data.entprs_prod_d.mdl_dn null '(null)' else rtrim(data.entprs_prod_d.mdl_dn) end mdl_dn, " + "data.dt_day_d.fisc_yr_qtr_dsply_cd fisc_qtr, " + "count(1) total, " +
textboxsearch.text + "as reporttype " +
"from " + "core_install_base.intgr_instld_base_f, " + "data.dt_day_d, " + "data.entprs_prod_d, " + "data.prod_ln_d, " + "data.cust_hier_d " + "where " + "data.prod_ln_d.ext_seg_cd in ('hpba_ww52', 'hpba_ww56') " + "and core_install_base.intgr_instld_base_f.prod_id = data.entprs_prod_d.prod_id " + "and data.entprs_prod_d.prod_ln_id = data.prod_ln_d.prod_ln_id "; if (textboxsearch.text == "best customer amid l2 id") { summarysql = summarysql + "and core_install_base.intgr_instld_base_f.best_cust_id = data.cust_hier_d.cust_id "; } else { summarysql = summarysql + "and core_install_base.intgr_instld_base_f.shpt_cust_id = data.cust_hier_d.cust_id "; } if (textboxsearch.text == "ibsv derived end customer amid l4 id") { summarysql = summarysql + "and data.cust_hier_d.cust_amid_lvl_4_id in ('" + selecteduserlist + "') "; } if (textboxsearch.text == "ibsv derived end customer amid l2 id" | textboxsearch.text == "best customer amid l2 id") { summarysql = summarysql + "and data.cust_hier_d.cust_amid_lvl_2_id in ('" + selecteduserlist + "') "; } if (textboxsearch.text == "warranty country code") { summarysql = summarysql + "and core_install_base.intgr_instld_base_f.warr_shpt_ctry_cd in ('" + selecteduserlist + "') "; } if (textboxsearch.text == "ship country code") { summarysql = summarysql + "and core_install_base.intgr_instld_base_f.ship_shpt_ctry_cd in ('" + selecteduserlist + "') "; } if (textboxsearch.text == "product line id") { summarysql = summarysql + "and cdata.entprs_prod_d.prod_ln_id in ('" + selecteduserlist + "') "; } if (textboxsearch.text == "serial number") { summarysql = summarysql + "and core_install_base.intgr_instld_base_f.srl_nr_id in ('" + selecteduserlist + "') "; } if (textboxdatetype.text == "warranty end date") { summarysql = summarysql + "and core_install_base.intgr_instld_base_f.warr_end_dt = data.dt_day_d.cldr_dt "; summarysql = summarysql + "and cast(core_install_base.intgr_instld_base_f.warr_end_dt date) between cast('" + textboxstart.text + "' date) , cast('" + textboxend.text + "' date) "; } if (textboxdatetype.text == "shipped date") { summarysql = summarysql + "and core_install_base.intgr_instld_base_f.ship_dt = data.dt_day_d.cldr_dt "; summarysql = summarysql + "and cast(core_install_base.intgr_instld_base_f.ship_dt date) between cast('" + textboxstart.text + "' date) , cast('" + textboxend.text + "' date) "; } summarysql = summarysql + "group 1, 2, 3, 4, 5, 6, 7, 8, 9, 11"; summarysql = summarysql + "order total desc"; return summarysql;
personally think you're trying string manipulation , going cause problems. existing code, has been mentioned, ripe sql injection attack. not sure why you're using dataset (suspect old code you're maintaining) ef , linq cleaner solution.
you cannot add parameters without switching string return type command. making change allow use of parameters. query filled magic string literals makes code unmaintainable. recommend separate query logic ui mapping altogether. simplify code , make easier maintain. query pattern may choice here. here's how might go solving problem.
//used code encapsulate query implementation interface iquery { //since you're using sql we'll use sqlcommand here sqlcommand prepare (); } //this poor man's implementation uses simple query building pattern. consider looking @ third-party //libraries more flexible query builder, or ef abstract class query : iquery { public sqlcommand prepare () { //base query sets core values child queries use var cmd = new sqlcommand() { commandtype = commandtype.text }; var parameters = getparameters(); if (parameters?.any() ?? false) cmd.parameters.addrange(parameters.toarray()); cmd.commandtext = getquerytext(); return cmd; } //override add columns select list protected abstract ienumerable<string> getcolumns (); //override add conditions clause protected virtual ienumerable<string> getconditions () { yield return null; } //override add grouping rules protected virtual ienumerable<string> getgroups () { yield return null; } //override add ordering rules protected virtual ienumerable<string> getordering () { yield return null; } //override add parameters protected virtual ienumerable<sqlparameter> getparameters () { yield return null; } //override add tables in clause protected abstract ienumerable<string> gettables (); private string getquerytext ( ) { var columns = string.join(",", getcolumns()); var tables = string.join(",", gettables()); var builder = new system.text.stringbuilder(); builder.append($"select {columns} {tables}"); var conditions = string.join(" , ", getconditions()); if (conditions?.any() ?? false) builder.append($" {conditions}"); var groups = string.join(",", getgroups()); if (groups?.any() ?? false) builder.append($" group {groups}"); var ordering = string.join(",", getordering()); if (ordering?.any() ?? false) builder.append($" order {ordering}"); return builder.tostring(); } } //not sure you're querying base class wraps base query posted class installquery : query { // required parameters passed part of ctor there no way mess public installquery ( string reporttype ) { reporttype = reporttype; } public bool usebestcustomer { get; set; } //could make settable if want able change after creation validate changes if public string reporttype { get; private set; } //override add columns select list protected override ienumerable<string> getcolumns ( ) { yield return "rtrim(data.cust_hier_d.cust_amid_lvl_4_id) cust_amid_lvl_4_id"; yield return "case when data.cust_hier_d.cust_amid_lvl_4_nm null '(null)' else rtrim(data.cust_hier_d.cust_amid_lvl_4_nm) end cust_amid_lvl_4_nm"; yield return "rtrim(data.cust_hier_d.cust_amid_lvl_2_id) cust_amid_lvl_2_id"; yield return "case when data.cust_hier_d.cust_amid_lvl_2_nm null '(null)' else rtrim(data.cust_hier_d.cust_amid_lvl_2_nm) end cust_amid_lvl_2_nm"; yield return "case when data.prod_ln_d.ext_seg_nm null '(null)' else rtrim(data.prod_ln_d.ext_seg_nm) end ext_seg_nm"; yield return "case when data.entprs_prod_d.prod_ln_nm null '(null)' else rtrim(data.entprs_prod_d.prod_ln_nm) end prod_ln_nm"; yield return "case when data.entprs_prod_d.prod_fmly_dn null '(null)' else rtrim(data.entprs_prod_d.prod_fmly_dn) end prod_fmly_dn"; yield return "case when data.entprs_prod_d.mdl_dn null '(null)' else rtrim(data.entprs_prod_d.mdl_dn) end mdl_dn"; yield return "data.dt_day_d.fisc_yr_qtr_dsply_cd fisc_qtr"; yield return "count(1) total"; yield return "@reporttype reporttype"; } protected override ienumerable<string> gettables () { yield return "core_install_base.intgr_instld_base_f"; yield return "data.dt_day_d"; yield return "data.entprs_prod_d"; yield return "data.prod_ln_d"; yield return "data.cust_hier_d"; } protected override ienumerable<string> getconditions () { yield return "data.prod_ln_d.ext_seg_cd in ('hpba_ww52', 'hpba_ww56')"; yield return "core_install_base.intgr_instld_base_f.prod_id = data.entprs_prod_d.prod_id"; yield return "data.entprs_prod_d.prod_ln_id = data.prod_ln_d.prod_ln_id"; if (usebestcustomer) yield return "core_install_base.intgr_instld_base_f.best_cust_id = data.cust_hier_d.cust_id"; else yield return "core_install_base.intgr_instld_base_f.shpt_cust_id = data.cust_hier_d.cust_id"; } protected override ienumerable<string> getgroups () { //only grouping 1 yield return "1"; } protected override ienumerable<string> getordering () { yield return "total desc"; } protected override ienumerable<sqlparameter> getparameters () { yield return new sqlparameter("@reporttype", reporttype); } } //separate query class each specific query care class l4customerquery : installquery { public l4customerquery ( ienumerable<string> users ) : base("ibsv derived end customer amid l4 id") { users = users; } public ienumerable<string> users { get; private set; } protected override ienumerable<string> getconditions () { foreach (var item in base.getconditions()) yield return item; //add new rulues yield return "data.cust_hier_d.cust_amid_lvl_4_id in ('@users')"; } protected override ienumerable<sqlparameter> getparameters () { foreach (var parm in base.getparameters()) yield return parm; yield return new sqlparameter("@users", string.join(",", users)); } } class productlinequery : installquery { public productlinequery ( ienumerable<string> lines ) : base("product line id") { lines = lines; } public ienumerable<string> lines { get; private set; } protected override ienumerable<string> getconditions () { foreach (var item in base.getconditions()) yield return item; yield return "cdata.entprs_prod_d.prod_ln_id in ('@lines')"; } protected override ienumerable<sqlparameter> getparameters () { foreach (var parm in base.getparameters()) yield return parm; yield return new sqlparameter("@lines", string.join(",", lines)); } } //in ui var query = getqueryfromui(); var cmd = query.prepare(); //stub impl static iquery getqueryfromui ( ) { //if (textboxsearch.text == "best customer amid l2 id") // return new installquery(textboxsearch.text) { usebestcustomer = true }; //else if (textboxsearch.text == "ibsv derived edn customer amid l4") // return new l4customerquery(selecteduserlist); return new l4customerquery(new[] { "bob", "june" }); } michael taylor
http://www.michaeltaylorp3.net
Visual Studio Languages , .NET Framework > Visual C#
Comments
Post a Comment