/* ------------------------ My Meta Content Here SEO ------------------------ */

Pages

Main Menu




Wednesday, February 19, 2014

How to Create a Linked Server

http://msdn.microsoft.com/en-us/library/aa560998.aspx


Copy table from one database to another

Create a linked server to the source server. The easiest way is to right click "Linked Servers" in Management Studio; it's under Management -> Server Objects.
Then you can copy the table using a 4-part name, server.database.schema.table:
select  *
into    DbName.dbo.NewTable
from    LinkedServer.DbName.dbo.OldTable
This will both create the new table with the same structure as the original one and copy the data over.
Read More »

Tuesday, February 11, 2014

Thursday, January 9, 2014

Import Contacts from Yahoo, Gmail, Hotmail Asp.Net: Import Contacts From Gmail, Yahoo, Hotmail Asp.Net...

Import Contacts from Yahoo, Gmail, Hotmail Asp.Net: Import Contacts From Gmail, Yahoo, Hotmail Asp.Net...: Import contacts from Gmail, Yahoo, Hotmail. I spend a few days to configure how to import contacts in my asp.net application. You can find...
Read More »

Monday, September 30, 2013

Linq to SQL Like Operator Using String.StartsWith or String.Endswith


Linq to SQL Like Operator

As a response for customer's question, I decided to write about using Like Operator in Linq to SQL queries.
Starting from a simple query from Northwind Database;
var query = from c in ctx.Customers
            where c.City == "London"
            select c;
The query that will be sent to the database will be:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City = [London]
There are some ways to write a Linq query that reaults in using Like Operator in the SQL statement:

1. Using String.StartsWith or String.Endswith

Writing the following query:
var query = from c in ctx.Customers
            where c.City.StartsWith("Lo")
            select c;
will generate this SQL statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers 
WHERE  City LIKE [Lo%]
which is exactly what we wanted. Same goes with String.EndsWith.
But, what is we want to query the customer with city name like "L_n%"? (starts with a Capital 'L', than some character, than 'n' and than the rest of the name). Using the query
var query = from c in ctx.Customers
            where c.City.StartsWith("L") && c.City.Contains("n")
            select c;
generates the statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers 
WHERE  City LIKE [L%] 
AND      City LIKE [%n%]
which is not exactly what we wanted, and a little more complicated as well.

2. Using SqlMethods.Like method

Digging into System.Data.Linq.SqlClient namespace, I found a little helper class called SqlMethods, which can be very usefull in such scenarios. SqlMethods has a method called Like, that can be used in a Linq to SQL query:
var query = from c in ctx.Customers
            where SqlMethods.Like(c.City, "L_n%")
            select c;
This method gets the string expression to check (the customer's city in this example) and the patterns to test against which is provided in the same way you'd write a LIKE clause in SQL.
Using the above query generated the required SQL statement:
SELECT CustomerID, CompanyName, ...
FROM    dbo.Customers
WHERE  City LIKE [L_n%]
Enjoy!
Read More »

Recent Posts

My Blog List