I have grown very found of using LINQ in my applications. Everything with LINQ was working great until I wanted to use it with a SQL Server Compact database. I created a .sdf database with all my tables, all was well until I tried dragging objects from the server explorer to the .dbml designer. Then I hit this:
Picture
Well what now? "The selected object(s) use an unsupported data provider" - does this mean you can't even use LINQ with a compact database? No, calm your fears. It only means that the designer does not support graphically creating the dbml. LINQ supports a huge array of data sources, some of which are not supported by the designer. 

So we just get the fun of creating our object mapping at the command line! Yeah! Really, in all seriousness, it is not that bad. The following is performed with Visual Studio 2010 in Visual Basic, but should work with other languages and versions. Here are the steps:
  1. We are going to be using SqlMetal.exe to create the object map. So go to the Visual Studio Command prompt. This can be accessed by going to the Microsoft Visual Studio 2010 folder in the start menu, then Visual Studio Tools > Visual Studio Command Prompt.
  2. Now type in the command. This will be:

    SqlMetal.exe "C:\DatabasePath\theDatabase.sdf" /dbml:"C:\DatabasePath\theDatabase.dbml"

    Hit enter and execute the command. This will create the .dbml file in the specified location.
    Note: You will obviously want to substitute the file paths for your own paths, this will often be a path to the folder where the Visual Studio project is stored. 
  3. Now go back to your project in Visual Studio. Go to the Project menu and select "Add Existing Item.." (Ctrl+D). Now select the .dbml file that was just created, and add it to the project. 
  4. Your about set, you now have a class in you project that represents your database schema. However, one addition step must be performed when using LINQ with the class.

    Normally with LINQ you can just specify a new data source as follows:

Dim theData As New theDatabase

When you create a .dbml with SqlMetal it does not have the data connection saved with the .dbml like it does when you create it with the designer. So you need to supply the connection string when creating a new instance of the object:

Dim theData As New Vocabulary("Data Source=|DataDirectory|\theDatabase.sdf")

Have a great day! You should now be back up and running with LINQ.
 
I was recently needing to search an entire database for a particular string. I needed a script that would do this, so I went searching. I found a really useful script here:  http://vyaskn.tripod.com/search_all_columns_in_all_tables.htm. I am reposting the code here for my own future reference, and to share the script with you. But all the credit goes to the original creator.


Here is an example of how you would use the stored procedure to search the entire database for the string "Computer"
EXEC SearchAllTables 'Computer'
GO


Here is the code you need to execute against the database. It will create a stored procedure in the database that can then be used to perform the search. 
  CREATE PROC SearchAllTables ( 	@SearchStr nvarchar(100) ) AS BEGIN  	-- Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved. 	-- Purpose: To search all columns of all tables for a given search string 	-- Written by: Narayana Vyas Kondreddi 	-- Site: http://vyaskn.tripod.com 	-- Tested on: SQL Server 7.0 and SQL Server 2000 	-- Date modified: 28th July 2002 22:50 GMT   	CREATE TABLE #Results (ColumnName nvarchar(370), ColumnValue nvarchar(3630))  	SET NOCOUNT ON  	DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110) 	SET  @TableName = '' 	SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')  	WHILE @TableName IS NOT NULL 	BEGIN 		SET @ColumnName = '' 		SET @TableName =  		( 			SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) 			FROM 	INFORMATION_SCHEMA.TABLES 			WHERE 		TABLE_TYPE = 'BASE TABLE' 				AND	QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName 				AND	OBJECTPROPERTY( 						OBJECT_ID( 							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) 							 ), 'IsMSShipped' 						       ) = 0 		)  		WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL) 		BEGIN 			SET @ColumnName = 			( 				SELECT MIN(QUOTENAME(COLUMN_NAME)) 				FROM 	INFORMATION_SCHEMA.COLUMNS 				WHERE 		TABLE_SCHEMA	= PARSENAME(@TableName, 2) 					AND	TABLE_NAME	= PARSENAME(@TableName, 1) 					AND	DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar') 					AND	QUOTENAME(COLUMN_NAME) > @ColumnName 			) 	 			IF @ColumnName IS NOT NULL 			BEGIN 				INSERT INTO #Results 				EXEC 				( 					'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)  					FROM ' + @TableName + ' (NOLOCK) ' + 					' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2 				) 			END 		END	 	END  	SELECT ColumnName, ColumnValue FROM #Results END