Tag: Database

LINQ to SQL Inserts and Deletes

Inserting and Deleting records in a database using LINQ to SQL is just as easy as selecting information. What's not so easy is actually finding out how to do it. There are lots of excellent blog posts around such as this one by Scott Guthrie http://weblogs.asp.net/scottgu/archive/2007/07/11/linq-to-sql-part-4-updating-our-database.aspx, however most of them we're all written for the Beta version of LINQ to SQL which let you do a .Add() or .Remove() on your table, which was  changed on the final release. 

So to insert do something like this: 

DataClassesDataContext dataContext = new DataClassesDataContext(); 

//Create my new Movie record
Movie movie = new Movie();
movie.Name = "Tim's movie"; 

//Insert the movie into the data context
dataContext.Movies.InsertOnSubmit(movie); 

//Submit the change to the database
dataContext.SubmitChanges();

And to delete do something like this:

DataClassesDataContext dataContext = new DataClassesDataContext();

var movies = from m in dataContext.Movies
                where m.Name == "Tim's movie"
                select m;

dataContext.Movies.DeleteAllOnSubmit(movies);

dataContext.SubmitChanges();

How to search inside Stored Procedures?

A common problem faced by many developers when it comes to databases and SQL Server is how to search the text inside a stored procedure.

In many systems particularly older Classic ASP solutions, functional code has been moved from the actual application to stored procedures inside the database. This is usually because it will either run faster here, or because it was just a lot easier to perform the necessary task using TSQL. Following this though comes the problem of how you can search what's in all those stored procedures, especially when you're getting into the hundreds of them. Let's say there was a Users table that contained fields for an address, but that now needs to be moved to a table of its own, you would need to search all the code for things accessing those table columns but SQL Server Management Studio certainly doesn't provide any search box's with the power to do this.

Never fear though syscomments is here. Syscomments contains the original text from amongst other things all the Stored Procedures in the DB s all you need to do is search that for what you're looking for:

Select OBJECT_NAME(id), [text]
From syscomments
Where [text] like '%Create%'

The function OBJECT_NAME will also help you by converting the id number in the result set into the actual name of the stored procedure (or view, function etc). If you wanted to limit the result to just stored procedures you can add the following line to the where clause:

AND OBJECTPROPERTY(id, 'IsProcedure') = 1