2007年11月小记

1、使用SQL2005的XML类型分拆字符串。

DECLARE @TagNames XML;

SET @TagNames = '<?xml version="1.0" encoding="utf-8"?><ArrayOfString xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><string>asp.net</string><string>sql</string><string>ajax</string></ArrayOfString>';

DECLARE @TagNameTable TABLE([IndexId] INT IDENTITY(1,1) NOT NULL PRIMARY KEY, [TagName] NVARCHAR(200) NOT NULL)

INSERT INTO @TagNameTable

SELECT tab.col.value('text()[1]','nvarchar(200)') AS [TagName]

FROM @TagNames.nodes('/ArrayOfString/string') AS tab(col);

select * from @TagNameTable order by [IndexId]

2、使网页中所有链接都另页打开

<head>

</head>

3、Lambda表达式一例

static void Main(string[] args)

{

var strs = new List<string> { "A1", "A2", "A3" };

List<string> list = new List<string>();

//使用匿名委托

strs.ForEach(delegate(string str) { Console.WriteLine(str); });

//使用Lambda表达式

strs.ForEach(x => list.Add(x.TrimStart('A')));

list.ForEach(x => Console.WriteLine(x));

Console.ReadLine();

}

4、WCF:ABC 从何地以何种方式绑定何种契约
5、如何成功调用wsHttpBinding邦定的WCF服务?
服务器定义了dns，如：

<system.serviceModel>

</identity>

</endpoint>

</service>

</services>

</behavior>

</serviceBehaviors>

</behaviors>

</system.serviceModel>

客户端也要配置dns，此dns可以为服务器计算机名称，如：

<system.serviceModel>

<endpoint address="http://passport2.ruiya.com/WcfService/PassportService.svc"

binding="wsHttpBinding" bindingConfiguration="" contract="CJB.Passport.Contract.IPassportService"

                name="iisHostEndpoint">
                <identity>
                    <dns value="cjb"/>
                </identity>

</endpoint>

</client>

</system.serviceModel>

或者<dns value="localhost"/>
6、注意between ... and ...在使用变量和使用拼接字符串中不过的执行计划导致的性能问题：

declare @handleTime datetime;

declare @beginTime datetime;

declare @endTime datetime;

set @handleTime = DateAdd(day, -1, getdate());

set @beginTime = CONVERT(datetime, CONVERT(char(10), @handleTime, 120));

set @endTime = DateAdd(day, 1, @beginTime);

declare @sql nvarchar(1000);

set @sql = 'SELECT * FROM [UserPosts] WITH(NOLOCK) WHERE ([AddTime] between ''' +

cast(@beginTime as nvarchar(100)) + ''' AND ''' + cast(@endTime as nvarchar(100)) + ''')';

--print @sql

declare @st datetime

declare @et datetime

set @st = getdate();

exec(@sql);

set @et = getdate();

select datediff(millisecond, @st, @et);

set @st = getdate();

select * from [UserPosts] WITH(NOLOCK) WHERE ([AddTime] between @beginTime and @endTime);

set @et = getdate();

select datediff(millisecond, @st, @et);

拼接字符串: 0ms
使用变量：23106ms
7、SQL拆分字符串

CREATE PROCEDURE [dbo].[ec_System_SplitString]

@strs nvarchar(4000),

@separator nchar(1)=','

BEGIN

SET NOCOUNT ON;

DECLARE @tbNames table([Name] nvarchar(256) NOT NULL PRIMARY KEY)

DECLARE @Num int;

DECLARE @Pos int;

DECLARE @NextPos int;

DECLARE @Name nvarchar(256);

SET @Num = 0;

SET @Pos = 1;

WHILE(@Pos <= LEN(@strs))

BEGIN

SELECT @NextPos = CHARINDEX(@separator, @strs, @Pos)

IF (@NextPos = 0 OR @NextPos IS NULL)

SELECT @NextPos = LEN(@strs) + 1

SELECT @Name = RTRIM(LTRIM(SUBSTRING(@strs, @Pos, @NextPos - @Pos)))

SELECT @Pos = @NextPos+1

INSERT INTO @tbNames VALUES (@Name)

SET @Num = @Num + 1

END

SELECT [Name] FROM @tbNames

END