C# and updating 3 linked tables in SQL server

[Matt_Hirst]

I have the following situation whereby I have three linked tables in sql server:

Table1:
ProjectId
ProjectTitle

Table2:
Id
ProjectId
DateAdded
ColRef
SPos
SCode

Table3:
AmmendId
ProjectId
ColRef
SPos
SCode
DateAmmended
ByWho

What I want to do is update these tables with the relevant data but not quite sure how? Do I have to do three seperate insert queries or just one?

Am new to all this so any help would be greatfully received.

Matt

 

[Haircut]

There will always have to be three insert statements that happen when it gets down to the database level.

In C# you could manage this with an ORM or something to avoid you needing to know explicitly to do these three inserts.

 

[Goksly]

Have a look at linq to sql or entity framework. Takes some of the pain out!

 

[PsychoDuck]

You might want to consider wrapping the inserts within a transaction scope so that either all or none of the tables are updated depending on success/failure.

 

[Matt_Hirst]

Thanks for the replys guys.

Have done a little Linq work but just queryinig tables, how do I get it to write to a database?? As I said am new to this.

ORM???transaction scope??? soorry don't understand these.


Matt

 

[Goksly]

If you use linq to sql (via linq data classes) then you just get the object from the database, alter the value and call submit changes on the data context.

ORM -> object relationship mapping... basically means it creates object for you from the database.

Transaction scope - makes all actions atomic... meaning if you update something and then update something else, if the second one fails due to a constraint (for example) then the change to the first item is undone.

 

[Matt_Hirst]

Ok, thanks for help think I have it now.

One other question regarding LINQ, if I use the following to return a projectID:

 var results = (from p in db.tbl_ShearFix_Projects
                           select p.ProjectId ).Distinct();

            Array ar = results.ToArray();

I can then pass that array to a combobox and all is well.

However I want to be able to search for a specific projectId or all projectID's so have tried this:

 var results = (from p in db.tbl_ShearFix_Projects
                           select new { p.ProjectId }).Distinct();

if (pid == "%")
            {// Select everything
                results = results.AsQueryable().Where(x =>                                
                    x.ProjectId != "%");
            }
            else
            {
                results = results.AsQueryable().Where(x =>
                    x.ProjectId == pid);
            }

This works but the resultant array is as follows:

ar[0] { ProjectId = 111111 }
ar[1] { ProjectID = 222222 }

Thus the array is filled with the column name as well as the ID. How do I just return the ID???

regards,

Matt

 

[Yakyb]

im not quite sure what you are attempting to do however my syntax would be slightly different


i would write that as

 var results = db.tbl_ShearFix_Projects
                                   .Select(x=>x.projectid)
                                   .Distinct()
                                   .ToArray();


//Or
 var results = db.tbl_ShearFix_Projects
                          .Where(x=>x.projectid == pid)
                          .Select(x=>x.projectid)
                          .Distinct()
                          .ToArray();

oh and i think your being caught out by selecting a new { p.projectid} when it is not required


as for inserting i like to wrap that up in static methods

   public static void CreateNewTaskReason(TaskReason reason)
        {
            var dataContext = new CentralDataContext();
            dataContext.TaskReasons.InsertOnSubmit(reason);
            dataContext.SubmitChanges();
            dataContext.Dispose();
        }

        public static void InsertSites(IEnumerable<Site> items)
        {
            var dataContext = new CentralDataContext();
            dataContext.Sites.InsertAllOnSubmit(items);
            dataContext.SubmitChanges();
            dataContext.Dispose();
        }

 

[Goksly]

If it supports dispose... Use a using statement.

You can do it by passing in the I'd you want to select, or -1 for all and in the linq where clause put where(p=>p.projectid == parameter ¡¡ parameter == -1).... Can't do or statement on my phone

 

[Matt_Hirst]

Thanks for all of your help, got it working, all apart from updating a record in a database.

The fields I am trying to update form part of a composite key, but when I submit the changes it errors. Reading various websites it seems this is a limitation with LINQ. Doing the same with an SQL command or using Access the changes are fed to the backend sql server database with no errors.

What work arounds do you guys use when updating database fields that are part of the key?

Matt

P.S. Can anyone point me to a good website explaining how to generate a report as I am bemused by this.

 

[Goksly]

I always use just an Id column... Not often you need composite keys.

 

[Matt_Hirst]

Thanks for that, what do you use for reporting or how do you generate reports using c#?

regards,

Matt

 

[Dj_Jestar]

Absolutely do NOT use linq to sql. It is a hideous feature that even the author has abandoned and called a "failure" himself.

Use MEF or NHibernate if you feel an ORM is suitable.

 

[Goksly]

It's not that bad. To accomplish a task like this thread is fine. If I was building a dal Id use the entity framework.

 

[Dj_Jestar]

It is that bad. It's horrific.

Just avoid all head ache and pretend L2S doesn't even exist. Really.

 

[Randomface74]

3 queries in a transaction scope

 

[Goksly]

I've used it a fair bit and the only issue was too many many to many relations when returning associated data resulted in a lot of queries instead of one. As I said, for simple updates and queries its fine.

 

[Dj_Jestar]

Or, you could take the wise choice and not opt to use it at all.

For now, it might do the job. Later, it might need to do stuff it can't. Then what? Re-write the whole thing?

Why? Just avoid it. Seriously, just avoid it.

It's horrific because it forces DataContract/DataMember on all properties (and you cannot override them without manually editing the designer files - which results in many moments of head slapping when you don't remember to apply these changes every time you update the DBML).

DataContext cannot be mocked/unit tested.

Link table relationships and horribly modelled. ("Foo.LinkFooBars.Select(x => x.Bars)" instead of just "Foo.Bars" for example)

DebugStepThroughAttribute on every class, so if you write some behaviour on the partial, you cannot debug it without again manually editing deisgner.

It is woefully inadequate for any project, big or small. In short: Avoid.