1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

How to display information from database using C#.NET?

Discussion in 'C#' started by fluid, Jun 1, 2006.

  1. #1
    I've got a table and the fields im interested in is client_name and client_url.

    I want to display the output as a link as follows:

    <a href="http://www.testclient.com/">John Smith</a><br />

    That's pretty simple to do is classic asp and php but im getting confused with ASP.NET. What is the most efficient way to do this? Bind the data to a control? If so, how?

    Btw im trying to write it in C#.NET. I've managed to do it using Response.Write() but i dont think that's the best way to do it. Anyone out there who can point me in the right direction?
     
    fluid, Jun 1, 2006 IP
  2. fhirzall

    fhirzall Guest

    Messages:
    124
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    If you're not looking for paging and updating built in support, just use a repeater, the code should look something like this.

    <asp:Repeater id="myRepeater" runat="server">
    <ItemTemplate>
    <a href='<%# Eval("client_url") %>'><%# Eval("client_name") %> </a>
    </ItemTemplate>
    </asp:Repeater>

    In your Code Behind...

    protected void Page_Load(object sender, EventArgs e)
    {
    if (IsPostBack == false)
    {
    string strConnection = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString; // Add your connection string here if you have it in your web.config file, if not, just type it out manually.
    string mySelectQuery = "SELECT client_url, client_name FROM Clients";
    SqlConnection myConnection = new SqlConnection(strConnection);
    SqlCommand myCommand = new SqlCommand(mySelectQuery, myConnection);
    myConnection.Open();
    SqlDataReader myReader;
    myReader = myCommand.ExecuteReader();
    myRepeater.DataSource = myReader;
    myRepeater.DataBind();
    myReader.Close();
    myConnection.Close();

    }
    }


    If you're using Visual Web developer or visual studio, you can drop a SQLDATASOURCE on the page, and configure the query with the wizard on it, then sets the DataSourceID property of the repeater to the ID of the SqlDataSource, instead of typing the actual code to connect .net just does it all for you! Let me know if you need any more help.

    Forgot to add, if you have it in a table, it'll be something like this...

    <asp:Repeater id="myRepeater" runat="server">
    <HeaderTemplate><table></HeaderTemplate>
    <ItemTemplate>
    <tr><td>
    <a href='<%# Eval("client_url") %>'><%# Eval("client_name") %> </a>
    </td></tr>
    </ItemTemplate>
    </FooterTemplate></table></FooterTemplate>
    </asp:Repeater>

    The footer and header templates are optional.
     
    fhirzall, Jun 1, 2006 IP
  3. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #3
    Exactly what i needed. Thanks!

    Since you're offering your help, i'll ask another question then.

    Say i need to retrieve the client_name and client_description, so i have a page like this view_client.aspx?client_id=4

    string client_id = Request.QueryString["client_id"];
    sql = "SELECT client_name, client_description FROM clients WHERE client_id = " + client_id;

    Do i create two labels and bind the data individually to them? If yes, how? If no, what's the solution then?

    The question seems very basic, i know, i just cant get the concept of data binding right now cos i'm used to looping and response.writing data.
     
    fluid, Jun 2, 2006 IP
  4. fhirzall

    fhirzall Guest

    Messages:
    124
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #4
    You can use a repeater to bind 1 record and if you run it, and view source, you'll see that there is no html or code generated by the repeater, so you can pretty much use that for the most basic things since you can define your own html.
    You have different controls you can use for this, you can use a Formview control, or a DetailsView, or a repeater, the repeater is the most basic control you have, you define your own html and it doesn't generate any code on its own. In the detailsview and formview, they have built in editing and updating and paging so you can just drop those on a page, and bind them to a datasource without writing any code, I don't like all the html they generate sometimes though.
    For your problem, you have to declare a parameter in your sql query, so it would be:
    sql = "SELECT client_name, client_description FROM clients WHERE client_id = @ClientID //You can name this anything you want but you have to have the @ before it to tell .net its a parameter
    Then in your code after you declare your command, you do this:
    myCommand.Parameters.Add("@ClientID", client_id); //This adds the client_id query string value to the @ClientID in your sql query

    For the aspx page, you can bind the text property of a placeholder or a label inside a repeater, or you could just define your own html like this
    <asp:Repeater id="myRepeater" runat="server">
    <HeaderTemplate><table></HeaderTemplate>
    <ItemTemplate>
    <tr>
    <td><%# Eval("client_name") %></td>
    </tr>
    <tr>
    <td>
    <%# Eval("client_Description") %>
    </td>
    </tr>
    </ItemTemplate>
    <FooterTemplate></table></FooterTemplate>
    </asp:Repeater>

    So in that code, when you check source on the page, it just has your table that you defined up there with the name and description under each other, so the repeater doesn't render any of its own html or any fancy stuff.
    <%# Eval("client_Description") %> is the basic binding syntax inside any ItemTemplate, and you only use the ' ' when its inside a tag, like our link we had above, we used the '' around it like this :'<%# Eval("client_Description") %>' .
    I typed too much, but i'm bad at explaining things :), any more problems let me know, i learn when i explain things.
     
    fhirzall, Jun 2, 2006 IP
  5. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #5
    Superb! I've got it to work.

    1. However, i've used the following:

    So what is the difference between the above and this:

    They both seem to return the same result.

    2. In classic ASP ...

    Say i want to display the client_name and number of times the client has called (something like contact history), i will use:

    <%
    sqltext = "SELECT client_id, client_name FROM clients"
    set rstemp = conntemp.execute(sqltext)
    while not rstemp.eof
    client_id = rstemp("client_id")
    client_name = rstemp("client_name")

    sql2 = SELECT COUNT(*) FROM contact_history WHERE client_id = " & client_id
    set rs2 = conntemp.execute(sql2)
    this_count = rs2(0)
    set rs2 = nothing
    %>
    <tr>
    <td><%=client_name%> - <%=this_count%></td>
    </tr>
    <%
    rstemp.movenext
    wend
    set rstemp = nothing
    %>

    Now if i want to achieve the same result in C#.NET, do i need to have 2 repeaters or just one? And how do i bind the results from these two resultsets to the control?
     
    fluid, Jun 3, 2006 IP
  6. fhirzall

    fhirzall Guest

    Messages:
    124
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #6
    Well, check that out http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag2/html/paght000002.asp
    it talks about using parameters instead of directly putting the value into the query so people won't inject SQL and maybe crash it, but for this, you could either use 2 repeaters and 2 sql queries, or the same repeater you have and you can use 1 sql query with an inner join. Its really personal preference, but the databinding is just the same, in your code...
    myrepeater.DataSource = myReader;
    myRepeater.databind();
    and in the item template its the same binding syntax
    <%# Eval("columnName") %>

    When you're counting, just give the column an alias with AS, SELECT COUNT(*) AS columnName from clientRecords.

    Kind of hard at first to wrap your mind around the whole databinding thing, but when you understand it completely you'll start cranking out pages fast.
     
    fhirzall, Jun 3, 2006 IP
  7. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #7
    I'm starting to get the idea now :)

    I've placed all the logic in the Page_Load() method inside the <head></head> of my page. Is that the best place to place it or should i do code behind instead?
     
    fluid, Jun 4, 2006 IP
  8. fhirzall

    fhirzall Guest

    Messages:
    124
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #8
    Thats also personal preference, I like seperating the code into another file and visual studio and web developer make it easy, u just hit 'place my code in a seperate file' and it creates the file for you and the page directive, its fine to leave it in your head tag though.
    I didn't use asp, but I heard coming from asp to asp.net is harder than just learning asp.net from scratch because your mind will just stick to asp haha, check out those videos on the msdn learning center, they're pretty good.
     
    fhirzall, Jun 4, 2006 IP
  9. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #9
    I guess it's true, i just need to change the way that i think (classic asp coding-wise). I've got everything to work though, thanks a lot. But i'm still not satisfied with the way i've coded it. The reason i chose .net was to make my applications more modular and object oriented but i guess until i segregate my logic from my display, i wont be able to do that. I'm on to checking the msdn videos now ...
     
    fluid, Jun 5, 2006 IP
  10. fhirzall

    fhirzall Guest

    Messages:
    124
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #10
    Yeah, check out those videos, and try reading up on the objectdatasource and datasets to seperate different layers in the application, sqldatareader is pretty much jus ta quick and dirty way of displaying the data right away without much seperation, there's also lots of books out there that are kind of targetted towards developers coming from asp.
     
    fhirzall, Jun 5, 2006 IP
  11. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #11
    I've noticed that when i display information from a table, the line breaks do not show up. So i've searched for a replace function but C# doesnt support it. Do you know the alternative?
     
    fluid, Jun 7, 2006 IP
  12. benjymouse

    benjymouse Peon

    Messages:
    39
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #12
    string mystring2 = mystring1.Replace("\n","<br />")
     
    benjymouse, Jun 14, 2006 IP
  13. fluid

    fluid Active Member

    Messages:
    679
    Likes Received:
    29
    Best Answers:
    0
    Trophy Points:
    70
    #13
    Because i was using a repeater and getting the values using <%# Eval("") %>, i had to pass the value returned to a method which does the replace for me.

    Thanks anyway benjymouse.
     
    fluid, Jun 15, 2006 IP
  14. benjymouse

    benjymouse Peon

    Messages:
    39
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #14
    <%# Eval("").ToString().Replace("from","to") %>

    or

    <%# ((string)Eval("")).Replace("from","to") %>

    ?
     
    benjymouse, Jun 15, 2006 IP
  15. Crypth

    Crypth Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #15

    My alias isn't quite the what you were discussing, however i'm hoping for a hint anyway. I'm joining three tables, and naturally wants to make an alias for each table. The query i'm using is as follows:

    SELECT * FROM tablea a LEFT JOIN tableb b ON (a.id=b.id) LEFT JOIN tablec c ON (c.id=b.id);

    The databinders datasource is set by using a mysqldatareaded that returns a DataSet. If i'm not mistaken, i should be able to do something like:
    <%#Eval("a.namn") %> but it seems the table alias is lost when filling the dataset?

    The reason why i want to specify from which table is naturally since some columns have the same name, and it IS possible to select either one using indexing, but is this really necessary?
     
    Crypth, Jun 19, 2006 IP
  16. benjymouse

    benjymouse Peon

    Messages:
    39
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #16
    Never use select * in a program/script. This is but one of the problems that you'll encounter.

    You need to spell out the columns you want to return to the script. In doing so SQL allows you to define alias'es for specific columns like

    select id as customer_id from customers ...
    Code (markup):
    From the outside of an select statement (like with a view) the actual "source" is hidden.
     
    benjymouse, Jun 20, 2006 IP
  17. Crypth

    Crypth Peon

    Messages:
    2
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    0
    #17
    Thank, i'll try that out, it's just quite alot of columns :rolleyes: I've been working with oracle forms too much, noticed my mistake when you pointed it out, the alias is only available within the database :p

    I also noticed a rather interesting flaw regarding the navigateurl attribute in a hyperlink object.

    In the article: http://www.sitepoint.com/article/asp-net-repeater-control by James Horan he recommends to use the following syntax:

    <asp:HyperLink ID="hl1" runat="server" NavigateUrl="<%#Eval("myLink") %>" Text="<%#Eval("myText") %>">

    This will not work within Visual Studio 2005 in any event. To properly be able to use evaluations within the tag, you must use the ' char instead of " .. ie.
    <asp:HyperLink ID="hl1" runat="server" NavigateUrl='<%#Eval("myLink") %>' Text='<%#Eval("myText") %>'>

    (Sidenote, but probably causing frustration for more people than me, finding this out after i've made a onItemDataBound eventhandler to change the url's navigateurl tag :eek: )
     
    Crypth, Jun 20, 2006 IP