19 July, 2010

LINQPad: How to add/update using LINQPad

I’ve been using LINQPad for an (extended) while now, and am really liking it.
It is a really useful tool for making quick lookups and ad-hoc queries. But, it seems to be missing out when it comes to inserting/updating a database?

I set out today to find out how to use LINQPAD for this, and it turns out to be quite easy; but not that intuitive after all.

You seem to be missing the <datacontext>.SubmitChanges() method when in the UI of LINQPad? But, it turns out that the missing <datacontext> actually is implied by the active dataconnection. Hence, you just call SubmitChanges() as a simple method call. That is all!

See code below (SubmitChanges() in the last section)…

    void Main()
{
byte[] bytes = null;
//load file
var fileInfo = new FileInfo(@"D:\files\DBFileStream.exe");
using (var fs = fileInfo.OpenRead())
{
//load into memory stream
bytes = new byte[fs.Length];
int read = fs.Read(bytes, 0, bytes.Length);

Debug.WriteLine(string.Format("Bytes read: '{0}'", read));

fs.Close();
}

//insert file info database
var p = Plugins.Where(x => x.PluginIdentity == "DBStream.exe").FirstOrDefault();
Debug.WriteLine(string.Format("Plugin: '{0}'", p.PluginIdentity));

//file exists?
var f = Files.Where(fi => fi.PluginId == p.PluginId).FirstOrDefault();
Debug.WriteLine(string.Format("File found in Files table: '{0}'", f != null));

if (f != null)
{
//update
f.FileData = new Binary(bytes);
f.Updated = DateTime.Now;
f.FileName = fileInfo.Name;
f.Version = "1.2.0.0";
}
else
{
//insert
f = new Files();
f.Updated = f.Created = DateTime.Now;
f.FileName = fileInfo.Name;
//f.FileLength = fileInfo.Length; (Calculated field!)
f.PluginId = p.PluginId;
f.Version = "1.0.0.0";

//TODO: Insert into files table
}

//Just submit here - you ARE the context yourself by means of the active DB-connection!
SubmitChanges();
}
}

2 comments:

Ben said...

Awesome, thanks!

Didn't think of SubmitChanges being implied.

Was driving me nuts...

blobber said...

umm didn't work for me.. but this worked

var ctx = new TypedDataContext();
ctx.[TABLE].InsertOnSubmit([table]);
ctx.SubmitChanges();

iPhone/XCode - not all cases are equal!

This bit me! Having made some changes to an iPhone application (Obj-C); everything worked fine in the simulator. But, when deploying the s...