c# - How can I use SqlDataSource properly with parameters? -
here code i'm using (with oracle developer tools):
<asp:sqldatasource id="firstnamesql" runat="server" connectionstring="<%$ connectionstrings:userqueries %>" providername="<%$ connectionstrings:userqueries.providername %>" selectcommand="select "firstname" "users" ("username" = '%?')"> <selectparameters> <asp:controlparameter controlid="usernamelabel" name="firstname" propertyname="text" type="string" /> </selectparameters> </asp:sqldatasource> what i'm trying make query parameter label's text. doesn't return anything. maybe data retrieving method wrong, here:
dataview dv4 = (dataview)firstnamesql.select(datasourceselectarguments.empty); foreach (datarow r in dv4.table.rows) { firstnamelabel.text = (string)r[0]; } any suggestions?
instead of using sqldatasource programatically retrieve data, should use oraclecommand class. sqldatasource should used if you're binding directly gridview or other control. really, sqldatasource shouldn't used @ all, because data access shouldn't happen in ui layer in designed system.
using official oracle nuget package.
public static datatable getdatatable(oraclecommand command) { datatable dt = new datatable(); using (var connection = getdefaultoracleconnection()) { command.connection = connection; connection.open(); dt.load(command.executereader()); } return dt; } public static oracleconnection getdefaultoracleconnection() { return new oracleconnection(configurationmanager.connectionstrings["defaultconnection"].connectionstring); //just connection string somewhere } //usage var command = new oraclecommand("select firstname users username=:username"); command.parameters.add("username", usernamelabel.text.trim()); command.bindbyname = true; //incredibly important if more 1 parameter var dt = getdatatable(command); dataview dv = dt.asdataview(); //now results. prefer getting datatable instead of dataview. easier use linq if(dt.rows.count > 0) { /* below line requires reference system.data.datasetextensions */ firstnamelabel.text = dt.asenumerable().first().select(r => r.field<string>("firstname")); } else { firstnamelabel.text = "not found"; } the above should have shown how can data database programatically. it's still not following architecture, because we've used database access code in our code behind. our code behind should have no idea database implementation details. let's start scratch , architect little better. first, should define class contains user details. i'm going call applicationuser don't conflict page.user property.
public class applicationuser { public string username {get; set;} public string firstname {get; set;} public string lastname {get; set;} } that's cool, we've defined need our applicationuser. need way load users database. should not happen on our code behind or in our model, should happen in data layer.
public class mysiteoraclerepository { private string connectionstring {get; set;} public mysiteoraclerepository(string connectionstring) { connectionstring = connectionstring; } public applicationuser getuserbyusername(string username) { oraclecommand command = new oraclecommand("select firstname, lastname, username users username=:username"); datatable dt = oracledatabasehelper.getdatatable(command, connectionstring); applicationuser user = dt.asenumerable().select(r => new applicationuser(){ firstname = r.field<string>("firstname"), lastname = r.field<string>("lastname"), username = r.field<string>("username") }).single(); return user; } } that's good. need implement oracledatabasehelper.getdatatable() perform actual access our database.
public class oracledatabasehelper { public static datatable getdatatable(oraclecommand command, string connectionstring) { datatable dt = new datatable(); using (var connection = new oracleconnection(connectionstring)) { command.connection = connection; connection.open(); dt.load(command.executereader()); } return dt; } } now, give us? easy , reusable way information users. since have code, don't need repeat ourselves everytime need retrieve user information, following dry (don't repeat yourself) principle. let's see our page's code behind can now.
private mysiteoraclerepository repo {get; set;} protected void page_load(object sender, eventargs e) { repo = new mysiteoraclerepository(configurationmanager.connectionstrings["defaultconnection"].connectionstring); if(!ispostback) { loaduserinfo(); } } protected void loaduserinfo() { applicationuser user = repo.getuserbyusername(usernamelabel.text); firstnamelabel.text = user.firstname; lastnamelabel.text = user.lastname; } see how simple our page has become? , notice it'd pretty easy write new mysitemysqlrepository, mysitepostgresrepository, mysitemongorepository etc, , need make new class. have modify page use different repository types, problem can solved dependency injection. won't go here, because through enough new stuff @ is.
Comments
Post a Comment