none
Simple retrieve one item from database RRS feed

  • Question

  • Hi, Im using c# visual studio and am creating a desktop application.  I have stored text to a table in sql server and now want  to retrieve this one item and display it in a blank text box on my form.

    Alot of examples online are either, .asp or retrieving a list of items from teh database...  I want to retrieve one! but cant figure out how to do this.. here is what i have tried;

    dataBaseDataContext context = new dataBaseDataContext();
    TableName value = (from s in context.TableName
                     where s.sessID == '2'
                     select s.TextValue);

    textbox.text = value;  

    but im getting a syntex error with the word 'select'...     So i do not know if this is almost the correct way to do this

    Can anyone help.  (i am new to linq to sql)

    Thanks!

    Monday, April 4, 2011 9:20 PM

Answers

  • Hi Emack,

     

    Like a normal sql query your select is returning a set of rows, even though you've specified an ID this only means that your set contains only one row.

     

    To access the row you can use (in your case) the extension method Single() but you can also use First(). Single returns the only object in the set, First returns the first object in the set...

    Because I don't know what is in your table (except the ID) I will print the ID to the form. For example:

     

    dataBaseDataContext context = new dataBaseDataContext();
    var value = from s in context.TableName
                     where s.sessID == 2
                     select s.TextValue;

    textbox.text = value.Single().sessID.ToString();  

     

    I am assuming sessID is a int and not a character as you had marked.

    Also always use var as type-inference will make your life easier (espicially when you need to refactor).

    I hope this helps.


    Allan Merolla | BEng, JD, MCP | .NET/Sharepoint | My Blog at http://www.parallelfun.com/
    • Marked as answer by Emack84 Tuesday, April 12, 2011 10:50 PM
    Monday, April 4, 2011 11:27 PM

All replies

  • Hi Emack,

     

    Like a normal sql query your select is returning a set of rows, even though you've specified an ID this only means that your set contains only one row.

     

    To access the row you can use (in your case) the extension method Single() but you can also use First(). Single returns the only object in the set, First returns the first object in the set...

    Because I don't know what is in your table (except the ID) I will print the ID to the form. For example:

     

    dataBaseDataContext context = new dataBaseDataContext();
    var value = from s in context.TableName
                     where s.sessID == 2
                     select s.TextValue;

    textbox.text = value.Single().sessID.ToString();  

     

    I am assuming sessID is a int and not a character as you had marked.

    Also always use var as type-inference will make your life easier (espicially when you need to refactor).

    I hope this helps.


    Allan Merolla | BEng, JD, MCP | .NET/Sharepoint | My Blog at http://www.parallelfun.com/
    • Marked as answer by Emack84 Tuesday, April 12, 2011 10:50 PM
    Monday, April 4, 2011 11:27 PM
  • Thanks, it works!   Can you tell me is it much the same if you want to retrieve an image with dataType: binary?  Im aware that this will have to be converted to bitmap once retrieved from datatbase.

    Lets say:

    var value = from s in context.TableName
                            where s.sessID == 2
                            select s.imageFile;

           string imageValue = value.Single()imagefile.ToString();

     

    Then pass the imageValue into a convertor method to convert to bitmap.   Can you tell if im on teh right track?

    Thanks for you time.  Emack.

    Tuesday, April 5, 2011 10:02 AM
  • Hi Emack,

    I need more information. What do you want to do with the image? Converting it ToString doesn't seem very helpful you will only get garble.

     

    Do you want to render the image? Is this a win/web application?

     

    Basically if the column is an image column. you can return a memory stream. For example:

     

    var value = from s in context.TableName
                            where s.sessID == 2
                            select s.imageFile;

    MemoryStream imageValue = new MemoryStream(value.Single().imagefile.ToArray());

     

    Hope this helps, post again if you need more help (always happy to help) a clearer picture of what you are trying to achieve would allow me to give better assistance.


    Allan Merolla | BEng, JD, MCP | .NET/Sharepoint | My Blog at http://www.parallelfun.com/
    Wednesday, April 6, 2011 6:39 AM
  • There are 6 methods for this.

    .Single()  : Throws error if count does not equal 1 for the query

    .SingleOrDefault()  : Returns null for class, defaults for struct (zero for int)  if count does not eqaul 1 for the query

    .First() : Throws error if count equals zero

    .FirstOrDefault() : Returns null for class, defaults for struct (zero for int)  if count does equals zero for the query

    .Last() : Throws error if count equals zero

    .LastOrDefault() : Returns null for class, defaults for struct (zero for int)  if count does equals zero for the query

    So the best solution maybe

    var x = (from .... select ...).FirstOrDefault();
    
    if( x == null ) // if x is a class
    {
       Textbox.Text = x.ToString();
    }
    else
    {
       Textbox.Text = String.Empty;
    }
    
    
    
    Wednesday, April 6, 2011 3:11 PM
  • hi Alan,    It is a windows application I am creating.  Basically I have an empty image control on my xaml screen were i want to fill using an image from the database, (which can be any image depending on a choice by user) 

    to upload image to database i managed to convert the filename to binary to store it :

    string filename = openFile.FileName;
     byte[] fileByte = System.IO.File.ReadAllBytes(filename);
     Binary binaryObj = new Binary(fileByte);

    Then added the 'binaryObj' to database.  its stores as <Binary data> in sql database so i cant actually tell whether it has properly worked.  So i assume i would be taking this data and have to convert it back into image type someHow like so;

     

    var value = from s in context.TableName
                            where s.sessID == 2
                            select s.imageFile;

           string imageValue = value.Single()imagefile.ToString();

    public string value { get; set; }

    public object ImageSource      ///this method taking directly from a internet source

    { get { BitmapImage image = new BitmapImage();

    try { image.BeginInit();
         image.CacheOption = BitmapCacheOption.OnLoad;
         image.CreateOptions = BitmapCreateOptions.IgnoreImageCache;
         image.UriSource = new Uri(value, UriKind.Absolute);
         image.EndInit(); }
    catch { return DependencyProperty.UnsetValue; } return image; }
         }

     

    Then add it to the image control.

    hope this isn't to confusing,    (p.s.  i tried using the memoryStream as you suggested but there is syntex error)

    Thanks for your time. Emack

    Thursday, April 7, 2011 4:44 PM
  • I'm abit tight on time, in a couple of days I will try this with a WPF control but I know I have done it for web. Here is a posting that might be helpful:

    http://social.msdn.microsoft.com/Forums/en-US/wpf/thread/cbe45d88-5698-4613-9f05-533c117f5ca0

     

    The key difference I see to what you were writing is that you need to call ToArray() on the binary database feild not ToString. ToArray will return you an array of bytes which form the image, ToString I imagine will return some ASCII mess (i think).

    Hope this helps


    Allan Merolla | BEng, JD, MCP | .NET/Sharepoint | My Blog at http://www.parallelfun.com/
    Friday, April 15, 2011 3:35 AM