SQL access with data source control

Erik Severiens · August 15th, 2013, 06:50 AM

Hello All,

for some time I am struggling with following: I want to access a SQl record and do (only) some arithmetic operation on it in a code behind page in c#.

I imagine I should use SQLDataSource but then what? Using DetailsView or FormView is not what I want since I don't want to show something.

Though everywhere it states that Linq/EF lets you " access data in your code" apparently it means through data bound controls and not in C#.

Is there a data bound control that not shows anything. Can anyone point me in the right direction for this?

Thanks in advance, all help is appreciated!

Erik

Imar · August 15th, 2013, 08:45 AM

>> though everywhere it states that Linq/EF lets you " access data in your code" apparently it means through data bound controls and not in C#.

Nope, not true. Take a look at the LINQ chapters; you'll see many examples that don't use data controls. With LINQ you can query all kinds of data (sets, single objects, scalar values like a number or the count of records, and work with that.

What is it exactly you're trying to do? Can you describe your SQL table and LINQ model along with the expected actions against the database?

Cheers,

Imar

Erik Severiens · August 15th, 2013, 09:43 AM

In SQL I have a very straightforward table, With an Id , several text fields and one SQL field called

Distribution of type binary(936)

In a cs file I have defined:

Code:

public static class Guide
{
 // Definieer Locatie array
     static Boolean [,] _Loc = new Boolean[144,52];
     public static Boolean[,] Location { get { return _Loc; } set { _Loc = value; } }

I want, with a button, get Distribution out of dbase and map it onto Location. With another button I would like to return the result to the dbase.
I understand boolean array's can not be mapped directly so I converted the boolean to a Byte[936] array which has same length and should be mappable to the SQL binary.

However I can't get a good looking handle to do this SQL interaction and need some advice how to continu: With SQL, LinQ or EF. I will now first look at LinQ possibilities.

Imar · August 15th, 2013, 11:12 AM

Have you tried generating an EDMX model based on the database table? Then you can do something like:

var location = db.Locations.First(x => x.Id == id);

and work with the location and its properties. (Note: just making up some code and name,s but I hope you get the idea).

Cheers,

Imar

Erik Severiens · August 15th, 2013, 12:38 PM

Thanks Imar,

On the route of edmx I try to make a test entity and have following in a button click:

Code:

 using (GuideEntities MyEntities = new GuideEntities())
        {
            var CR = from Guide in MyEntities.Guides
                     where Guide.Id == 1
                     select Guide.TestText;
            label1.text = CR;
        }

I get an conversion error for the label, if I do ToString() the query is not performed: How do I pickup the result of above select in a variable?

Best Regards,

Erik

Imar · August 15th, 2013, 01:43 PM

Hi Erik,

(Nederlands?)

By default, a query returns an IEnumerable / IQueryable which is a collection, even though you query a single object. So this:

Code:

var CR = from Guide in MyEntities.Guides
                     where Guide.Id == 1
                     select Guide.TestText;

returns a collection that contains a single item. You can use Single, or SingleOrDefault if it's possible that the ID doesn't exist:

Code:

var CR = (from Guide in MyEntities.Guides
                     where Guide.Id == 1
                     select Guide.TestText).SingleOrDefault();

Then you can assign the text as follows:

Code:

if (CR != null)
{
  label1.Text = CR;
}

You can assign CR directly, as you're selecting its TestText property. The alternative is this:

Code:

var CR = (from Guide in MyEntities.Guides
                     where Guide.Id == 1
                     select Guide).SingleOrDefault();

Then you can assign the text as follows:

Code:

if (CR != null)
{
  label1.Text = CR.TestText;
}

There are many other ways to interact with LINQ and the underlying database, so you'll see different syntax as well.

I would recommend checking out the chapter on LINQ as well as getting a dedicated resource (book, online tutorial) on LINQ to learn more.

Cheers,

Imar

Erik Severiens · August 15th, 2013, 02:07 PM

Hallo Imar,

Ja Nederlands, en het voorbeeld werkt.

Is het boek : Professional Linq | Scott Klein Engels - Paperback | 2008 aan te bevelen? Ik zag dat het niet meer bij wrox te koop was (uitverkocht). Of weet je betere boeken?

Heel erg bedankt,

Erik

Imar · August 15th, 2013, 02:21 PM

Het is wat verouderd, maar veel kern concepten zijn nog wel hetzelfde.

Voor een "deep-dive" zou je ook het Entity Framework boek van Julia Lerman kunnen aanschaffen. Dat gaat echter flink de diepte in op het EF vlak.

Groeten,

Imar