C# - Updating a row using LINQ

Question

I am in the process of improving a console app and at the moment I cant get it to update rows instead of just creating a new row with the newer information in it.

class Program
{
    List<DriveInfo> driveList = DriveInfo.GetDrives().Where(x => x.IsReady).ToList<DriveInfo>(); //Get all the drive info
    Server server = new Server();  //Create  the server object
    ServerDrive serverDrives = new ServerDrive();

    public static void Main()
    {
        Program c = new Program();
        c.RealDriveInfo();
        c.WriteInToDB();
    }

    public void RealDriveInfo()
    {


        //Insert information of one server
        server.ServerID = 0; //(PK) ID Auto-assigned by SQL
        server.ServerName = string.Concat(System.Environment.MachineName);

        //Inserts ServerDrives information.
        for (int i = 0; i < driveList.Count; i++)
        {

            //All Information used in dbo.ServerDrives                
            serverDrives.DriveLetter = driveList[i].Name;
            serverDrives.TotalSpace = driveList[i].TotalSize;
            serverDrives.DriveLabel = driveList[i].VolumeLabel;
            serverDrives.FreeSpace = driveList[i].TotalFreeSpace;
            serverDrives.DriveType = driveList[i].DriveFormat;
            server.ServerDrives.Add(serverDrives);

        }
    }

    public void WriteInToDB()
    {
        //Add the information to an SQL Database using Linq.
        DataClasses1DataContext db = new DataClasses1DataContext(@"sqlserver");
        db.Servers.InsertOnSubmit(server);
        db.SubmitChanges();

What I would like it to use to update the information would be the RealDriveInfo() Method so instead of creating new entries it updates the currently stored information by running the method then inserting the information from the method and if needed will enter a new entry instead of simply entering new entries every time it has newer information.

At the moment it is running the method, gathering the relevant data then entering it in as a new row in both tables.

Any help would be appreciated :)

Neil Thompson · Accepted Answer · 2012-08-08 15:50:38Z

4

It's creating a new db entry each time because you are making a new server object each time, then calling InsertOnSubmit() - which inserts (creates) a new record.

I'm not entirely sure what you are trying to do, but a db update would involve selecting an existing record, modifying it, then attaching it back to the data context and calling SubmitChanges().

This article on Updating Entities (Linq toSQL) might help.

answered Aug 8, 2012 at 15:50

Neil Thompson

6,4652 gold badges33 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Ghostyy Over a year ago

Just to clarify, What I am trying to do is have my database update with the information gathered by the RealDriveInfo method. What it is doing is creating a new entry with the updated TotalSpace and FreeSpace variables added into the database, what I want it to do is find the name of the server (for example Server1) and then update the information instead of add a new entry.

Neil Thompson Over a year ago

So first you might select all the server entries saved in your DB. Then you would loop through the driveList (as you do). If driveList[i] does not exist in the list of server entries you selected = Insert the new server; otherwise update the existing server list entry with the current divelist data.

Ghostyy Over a year ago

Thats ideally what I would like to do but I just dont know how to get a query to loop the driveList..

J0HN · Accepted Answer · 2012-08-08 15:52:21Z

The problem is that you are trying to achieve Update functionality with a tool that is designed to provide object-oriented quering. LINQ allows for updating exisitng records, but you have to use it in a proper way to achieve this.

The proper way is to fetch data you want to update from the DB, perform modifications and then flush it back to the DB. So, assuming there are table named Servers in your data context, here's an abstract example:

DataClasses1DataContext db = new DataClasses1DataContext(@"sqlserver");
var servers = db.Servers.Where(srv=>srv.ID>1000); //extracting all servers with ID > 100 using lambda expression
foreach (server in servers){
    server.Memory *=2; //let's feed them up with memory
}
db.Servers.SubmitChanges();

Another way to achieve this is to create an entity, than attach it to the DataContext using Table.Attach method, but it's quite a slippery slope, so I wouldn't recommend you taking it unless you have your LINQ skills improved.

For a detailed description, see

Bob · Accepted Answer · 2015-03-04 12:40:28Z

I understand what is being asked, and I do not have an easy answer.

Example, you have a form of values, several of the values are changed, maybe some calculated. Or the form can contain a new record.

You create a record of the values

myrecord = new MyRecord()

Then fill in myRecord. doing what ever validation/calculations you want before you even touch the database itself.

//GetID either returns an existing ID or it returns a zero if this is a new record.

myrecord.id = GetIDForRecordOrZeroIfANewRecord(uniqueName);
myrecord.value1 = txtValue1.text;
myrecord.value2 = (DateTime)dtDate.value;

and so on through the fields.

You now have a record, if id is zero you can add it as a new record. But if id is an existing record you seem to have no choice with Linq except to have a function that writes each value from myrecord, so you have to have a function that contains something like -

var thisRecord = from n in mydatacontext.MyTable
  where n.id == myrecord.id
  select n;

thisrecord.value1 = myrecord.value1;
thisrecord.value2 = myrecord.value2;

and so on through all fields.

I do it, but it seems long winded when I already have all of the information ready in myrecord. A simple function of

mydatacontext.MyTable.Update(myrecord);

Would be ideal. Simmilar in fact to what I do with stored SQL functions in other databases, it simplifies the transfer of a record that is an update rather than new.

Collectives™ on Stack Overflow

C# - Updating a row using LINQ

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related