Shreeman: February, 2006

February 28

Although Late back to ASP.NET 2.0 again

Last Month I didn't post a single post in Asp.net 2.0 Rather i had few post on SqlServer2005

actually i am not an Expert in Sqlserver2005 (not yet i think) but was tryign my hands with while got an oppurtunity and was quite satisfied(might be you are not as those were very basics posts on Xquery but I found quite a few fellow asked for the Xquery and I thought why not put few works(I swear i d implemented more

but i am now again back to my favourite tpics and this time around first i would liek to post a great series of Video resources .So here you go down load the new asp.net 2.0 videos below:-

Caching (Part 1)

Caching (Part 2)

Create a Full-Featured Customer Login Portal

Data

Form Building for a "Contact Us" Page

Localization

Master Pages and Site Navigation

Membership and Roles

Profiles and Themes

Tips and Tricks

Web Parts and Personalization

11:13 PM | Add a comment | Permalink | Blog it

Bit of Xquery in SqlServer2005 :-Final part(Introductory)

Part1 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!508.entry

Part2 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!509.entry

Part3 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!510.entry

We already have covered the basics of xml method like nodes and Values to complete the Xml methods i am going to show a little of the other 2 Query and the DML of Insert attribute and element as well as modify and finally delete to complete the introductory series.

declare @inxml xml

Set

@inxml='<EMPOBJ>

<Employee><empid>1</empid><name>shreeman</name><sal>1070</sal><deptno>10</deptno>

<deptname>admin</deptname><location>HYD</location></Employee>

<Employee><empid>2</empid><name>ram</name><sal>2000</sal><deptno>30</deptno><deptname>PRocess</deptname>

</EMPOBJ>'

select

@inxml.query('/EMPOBJ/Employee')

select

@inxml.query('/EMPOBJ/Employee/empid')

--use same xml

declare

@f bit

set

@f = @inxml.exist('/EMPOBJ/Employee[1][deptno[1] eq 30]')-- now enter 10--you understabd i am comparing the top row now

select

@f

set

@f = @inxml.exist('/EMPOBJ/Employee[deptno[1] eq 30]')

select

@f

set

@f = @inxml.exist('/EMPOBJ/Employee/deptno[1]')

select

@f

-- insert

SET

@inxml.modify('

insert (

<newelement1>i am new element1</newelement1>,

<newelement2>i am new element2</newelement2>

)

into (/EMPOBJ/Employee)[1] '

)

SELECT

@inxml;

-- insert new attribute

SET

@inxml.modify('

insert attribute newattribute1 {"i m new attribute" }

into (/EMPOBJ/Employee)[1] '

)

SELECT

@inxml

-- multiple insert

SET

@inxml.modify('

insert (

attribute attr1 {"attrib1" },

attribute attr2 {"attrib2"}

)

into (/EMPOBJ/Employee/location)[1] '

)

SELECT

@inxml;

-- delete an attribute

SET

@inxml.modify('

delete /EMPOBJ/Employee/@location --put @ for attribute centirc xml

'

)

SELECT

@inxml

-- delete an element

SET

@inxml.modify('

delete /EMPOBJ/Employee/location

')

SELECT @inxml

Further this is going to be my final post ona introductory note and as i mention before i am not an expert and often getting similar queries on this same lines from my friend i decided to put a post describing the basic s of the Xpath query in SQlServer2005.

Hope that you found this series helpful .

Plese comment ...

10:36 PM | Add a comment | Permalink | Blog it

Little Xquery with sqlserver2005:Part3

Part1 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!508.entry

Part2 :-http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!509.entry

Parent Child RelationShip With Elementric Centric Way:-

declare @inxml xml

set @inxml='

<Rowid>1</Rowid><PCodeId>31953</PCodeId><CardNo>1</CardNo><Action>R</Action><ErrorMessage>what a error</ErrorMessage>

</Parent>

<Rowid>2</Rowid><PCodeId>31954</PCodeId><CardNo>3</CardNo><Action>R</Action><ErrorMessage>what a error</ErrorMessage>

</Parent>

</RootXml>'

--USE the Same Table structure

INSERT INTO @Parents

SELECT U.value ('./Rowid[1]','Int') AS [RowID]

, U.value ('./Action[1]','Char(1)') AS [Action]

, U.value ('./PCodeId[1]','Int') AS [PCodeId]

, U.value ('./CardNo[1]','Int') AS [CardNo]

, U.value ('./ErrorMessage[1]','Varchar(50)') AS [ErrorMessage]

FROM @inxml.nodes('/RootXml/Parent') AS T(U)

INSERT INTO @Childs

SELECT T.U.value ('./Rowid[1]','Int') AS [RowID]

, T.U.value ('./ChildCodeId[1]','Int') AS [ChildCodeId]

, T.U.value ('./CardNo[1]','Int') AS [CardNo]

, T.U.value ('./versionCodeid[1]','Int') AS [versionCodeid]

FROM @inxml.nodes('/RootXml/Parent/Child') AS T(U)

select P.[RowID] AS '@RowID'

, P.[Action] AS '@Action'

, P.[PCodeId] AS '@PCodeId'

, P.[CardNo] AS '@CardNo'

, P.[ErrorMessage] AS '@ErrorMessage'

,(SELECT

C.[RowID] AS '@RowID'

, C.[ChildCodeId] AS '@ChildCodeId'

, C.[CardNo] AS '@CardNo'

, C.[versionCodeid] AS '@versionCodeid'

FROM @Childs C

where P.Rowid=C.RowId

for xml path('Childs') ,Type)

from @Parents P

FOR XML PATH ('Parent') ,ROOT('RootXml')

9:45 PM | Add a comment | Permalink | Blog it

Little Xquery With SqlServer2005:-Part2

Part1 :- http://spaces.msn.com/shreeman/blog/cns!E1AF7EB63FAA1FF!508.entry

Below is the code where I am going to show is the Xquery way of retrieve from the xml insert into the table and finally we ll get the same xml back from the table all without using the XML explicit mode and openxml and sp_xml_preparedocument .

Parent Child XMl With Attribute Centric way:-

declare @inParameterXML xml

set @inParameterXML=’ <RootXml>

<Parent

RowID="1" PCodeId = "31953" CardNo = "1" Action="R" ErrorMessage="what a error" STPUPNo="247">

</Parent>

<Parent

RowID="2" PCodeId = "31953" CardNo = "1" Action="R" ErrorMessage="what a error" STPUPNo="247">

</Parent>

</RootXml>'

DECLARE @Parents TABLE ( RowID Int , Action Char(1) , PCodeId Int , CardNo Int , ErrorMessage Varchar(50) )

DECLARE @Childs TABLE ( ManageCOId int IDENTITY(1,1) , RowID Int , ChildCodeId Int , CardNo Int , versionCodeid Int )

INSERT INTO @Parents

SELECT T.U.value ('@RowID','Int') AS [RowID]

, T.U.value ('@Action','Char(1)') AS [Action]

, T.U.value ('@PCodeId','Int') AS [PCodeId]

, T.U.value ('@CardNo','Int') AS [CardNo]

, T.U.value ('@ErrorMessage','Varchar(500)') AS [ErrorMessage]

FROM @inParameterXML.nodes('/RootXml/Parent') AS T(U)

INSERT INTO @Childs

SELECT T.U.value ('@RowID','Int') AS [RowID]

, T.U.value ('@ChildCodeId','Int') AS [ChildCodeId]

, T.U.value ('@CardNo','Int') AS [CardNo]

, T.U.value ('@versionCodeid','Int') AS [versionCodeid]

FROM @inParameterXML.nodes('/RootXml/Parent/Child') AS T(U)

select P.[RowID] AS '@RowID'

, P.[Action] AS '@Action'

, P.[PCodeId] AS '@PCodeId'

, P.[CardNo] AS '@CardNo'

, P.[ErrorMessage] AS '@ErrorMessage'

,(SELECT

C.[RowID] AS '@RowID'

, C.[ChildCodeId] AS '@ChildCodeId'

, C.[CardNo] AS '@CardNo'

, C.[versionCodeid] AS '@versionCodeid'

FROM @Childs C

where P.Rowid=C.RowId

for xml path('Childs') ,Type)

from @Parents P

FOR XML PATH ('Parent') ,ROOT('RootXml')

9:44 PM | Add a comment | Permalink | Blog it

little Bit Of XQuery in SqlServer2005:-Part1

A little Bit Of XQuery in SqlServer2005:-

Before I am going to blog about the Xquery feature in SqlSever2005 let me Clarify that I am not an expert in this arena and nor do I am a Xquery Expert. Further What I am going to post is really can’t cover in one post but I ll try to cover as much as possible giving examples of the sqlserver2000 way of doing things as well as how the Xquery makes your life easier with the XML data type and how you can achieve the same result in more efficient way without using OPenXml and For Xml Explicit.

Let me start with the Xml data type in SqlServer.

With the New XMl data type what we get in hand is no need to use the Text and Ntext data types and the pointers .Not only that it provides a great deal of flexibility .Not only with optimization but faster execution too. No longer you have to think about the limitation of you can declare a Text and Ntext type to manipulate your data.

Example: - Update a Ntext Column:-

CREATE proc tnxt as

declare @ptr1 binary(16)

declare @ptr2 binary(16)

create table #t(id int,data ntext )

insert #t values(1,'new ntext test today 4th april')

select @ptr1=textptr(bigdata) from testntext

select @ptr2=textptr(data) from #t

updatetext testntext.bigdata @ptr1 NULL 0 #t.data @ptr2

drop table #t

Further you can have the Xml DML(you can modify ur xml ..) statements to manipulate ur xml as well as you can defined Index over ur xml columns and you can ve the schema too built-in and stored in ur database Thus you can have both the typed and untyped Xml .

I am now not going to write the details of the usefulness of Xml Datatype further you can get the details here: http://msdn.microsoft.com/sql/learning/prog/xml/default.aspx

However what I am going to cover here is in simplistic way I am going to show how you can leverage the Xml features to implement your task much simpler way then the sqlserver2000 says. I am going to show the FOR XML EXPLICIT vs FOR XML PATH and how you can replace (almost) the OPENXML and sp_xml_prepareDoc & removed (if you forgot to release the doc you are leaking huge memory).

I am going to show you how you can handle Element Centric Data as well as AttributeCentirc data as well as some sort of ParentChild Relationship. Further if I get time I am going to show you the basics of Xml Methods: - Exists, Query, Value and Nodes etc;

I know this is very little for the vast topic but this will provide a starting platform for beginners handle the new features and for the details you can always refers to the above link.

Old technique (How you handle Xml in Sql2000) with Element centric xml:-

declare @xml xml -–even this is the new datatype and you need to fallback to varchar or nvarchar in sql2000

set @xml='<EMPOBJ>

<name>shreeman</name>

<deptname>admin</deptname>

</Employee>

</Employee>

</EMPOBJ>'

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT *

FROM OPENXML (@idoc, '/EMPOBJ/Employee',2)

WITH ( empid int ,

name varchar(20) ,

sal int ,

deptno int ,

deptname varchar(20) ,

locatoion varchar(20)

)

EXEC sp_xml_removedocument @idoc

New(SQLSERVER 2005) Equivalent With ELementCentric :-

declare @inxml xml

Set @inxml=’Use the same Xml as Above ’

SELECT U.value ('./empid[1]','Varchar(20)') AS [empid]

, U.value ('./name[1]','Varchar(20)') AS [name]

, U.value ('./sal[1]','Int') AS [sal]

, U.value ('./deptno[1]','Int') AS [deptno]

, U.value ('./deptname[1]','Varchar(50)') AS [deptname]

, U.value ('./location[1]','Varchar(20)') AS [location]

FROM @inXML.nodes('/EMPOBJ/Employee') AS T(U)

Old technique(Sqlserver2000) with attribute centric xml:-

declare @xml xml -–even this is the new datatype and you need to fallback to varchar or nvarchar in sql2000

set @xml='<EMPOBJ>

</EMPOBJ>'

DECLARE @idoc int

EXEC sp_xml_preparedocument @idoc OUTPUT, @xml

SELECT *

FROM OPENXML (@idoc, '/EMPOBJ/Employee',1)

WITH ( empid int ,

name varchar(20) ,

sal int ,

deptno int ,

deptname varchar(20) ,

locatoion varchar(20)

)

EXEC sp_xml_removedocument @idoc

New Equivalent(SqlServer 2005) With Attribute Centric :-

declare @inxml xml

Set @inxml=' Use the same Xml as Above '

SELECT T.U.value ('@empid','int') AS [empid]

, T.U.value ('@name','Varchar(20)') AS [name]

, T.U.value ('@sal','Int') AS [sal]

, T.U.value ('@deptno','Int') AS [deptno]

, T.U.value ('@deptname','Varchar(20)') AS [deptname]

, T.U.value ('@location','Varchar(20)') AS [location]

FROM @inXML.nodes('/EMPOBJ/Employee') AS T(U)

Due to the space limitation per post I am keeping this post smaller and I ll 2 other posts soon to complete(J I should better not use this word ) the introduction of Xml features in sqlserver2005.

9:29 PM | Add a comment | Permalink | Blog it

February 25

you should care few : if u r using SQL SMO

If you are using SMO to fetch the data from SQLSERVER2005 and getting a big performance hit its probably because you are loading the SMO as like you did with DMO .The reason why teh performance hit you get is there is due to the way SMO fetch the data from SQLServer.That is SMO doesnot fetch records at one go from SQLSERVER and when you are doing a Foreach loop for let say manipulating all tables you cna see lot of bandwidth(put a profiler).

Since the same was already discussed by Michiel Wories I am not goign to Explain it more here but rather pointing you to his post on the same.

Part1:- http://blogs.msdn.com/mwories/archive/2005/04/22/smoperf1.aspx

Part2 :- http://blogs.msdn.com/mwories/archive/2005/05/02/smoperf2.aspx

Also read " SMO and object-relational mapping " by Bob Beauchemin

Note:-I am not sure if this fixed already and i had a older version of sql05 box and i used profile to verify that the tips Michiel is providing is correct only.

Also You can find more useful entries on Michiele's Blog for SMO:-

SMO Connection details :-http://blogs.msdn.com/mwories/archive/2005/05/02/smoconnections.aspx

SMO SAMPLES :-http://blogs.msdn.com/mwories/archive/category/12133.aspx

8:11 PM | Add a comment | Permalink | Blog it

February 12

Asp.Net 2.0: Final word on Encrypting ConnectionString in Web.config

Often its a common requirement to store the connection string in some global place and often web.config is the place where you ll ends up with.There are many +ve points storing connection string in web.config like you can change the same to poitns to a different server ,you ll get inbuilt caching of te connection string and a great place to maintain your data application wide.Now comes the next question Since web.config is nothing but simply a xml file how can I restrict the users to see what my connectionstring or password is ?? and this question lands you into few more questions based on the approach you choosed.

Coming to the approach the ideal scenario would I should able to encrypt my data before i store and store into a place from where I can easily pick the decrypted values to be used by my application.Further question introduced are will the same would be applicable userwide or systemwide that what the storage would be and what would eb the encryption ,Decryption methods and finally what would be the process to fetch the data in usable format from the encrypted secure store.

In asp.net 1.1 the approach to achieve the above goals are to

- use a routine for the encrytion and decryption using the DPAPI

-use a Routine to Write the encrypted data to Registry(aspnet_setreg is what going to help you here).

-Finally pick the data ,decrypt it and get the connectionstring back

However you need to aware with with what account you are running asp.net and whether that account should have the access to the registry or to the store .To overcome that you normally endsup with another account which did this for you like write a com+(or ES) apps and run the apps in an account to perfom this task for you and you are calling the com+ to get the secured data back.Finaly whatever approach you follow you need to spent quite a few effort to make this happen.

To overcome all these While Shipping ASP.NET2.0 the teams provides a lot more functionality to handle this scenario effectively.Before writing how can you handle the same in ASP.NET 2.0 let me clarify you that now you have a Protected datasection in Webconfig where you can store your enncrypted values and even you can dynamically modify the web.config (new feature in asp.net2.0 thru the config API.).

Stepwise Approach to Secure Connection String in Asp.net 2.0:-

1. Create a connectionstring section in web.config:-

</connectionStrings>

2. Run aspnet_regiis –pe <the section you want to configure> -app <the virtual path starts with a forward slash> optionally you can provide the machine or user store.

3. Get the connection string:-

Response.Write(ConfigurationManager.ConnectionStrings["Myconnstr"].connectionString.ToString());

4.If you need to provide a provider for the same you can provide .you can also choose the machine level and User level setting for storign the config data.

5.You can encrypt not only connectionstring but also

<appSettings>

<identity> Web application identity. Can contain impersonation credentials.

<sessionState>

6.If you want to revert back to the Cleartext (for getting the web.config back to older state)

aspnet_regiis –pd with same params

7.you can also dynamically write into the web.config:-

Configuration config= ConfigurationManager.OpenExeConfiguration("");

config.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("myconnstr","Data Source=shreeman;Initial Catalog=Northwind;Integrated Security=True"));

config.Save(ConfigurationSaveMode.Modified);

8.Finally play with the other option of config data section in aspnet_regiis under Configuration Encryption Section

Lioke asp_regiis –pef,aspnet_regiis -pdf ..etc;

NOTE : Remember that if you are using the machine store or the default store the account under which asp.net is running should have the access to the provider.

For Example if you are running the same under netwrok service account and using RSA encryption .Then Network Service should have the permissin to to the folder" C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA"

Still You wants to explore more on this .If yes have alook into the following :-

patterns & practices Security How Tos Index(MUST READ FOR ALL)

Channel9 screen Cast video

Building Secure ASP.NET Applications: Authentication, Authorization, and Secure Communication

How to use the ASP.NET utility to encrypt credentials and session state connection strings

http://support.microsoft.com/default.aspx?scid=kb;en-us;329290

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT11.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsecure/html/windataprotection-dpapi.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetch12.asp

Overview of Protected Configuration

Security Guidelines: ASP.NET 2.0

Encrypting the connection string in ASP.NET V2.0

Walkthrough: Encrypting Configuration Information Using Protected Configuration :-LinkI and LinkII

How To: Encrypt Configuration Sections in ASP.NET 2.0 Using RSA

How To: Encrypt Configuration Sections in ASP.NET 2.0 Using DPAPI

Few More:-

http://weblogs.asp.net/scottgu/archive/2006/01/09/434893.aspx

http://weblogs.asp.net/owscott/archive/2005/07/29/421063.aspx

Hope this provides an overview of the topic.

Update:-

I observed little interesting behavior with the secure connectionstring that is to encrypt the connection string using RSA.

I was getting error that the Decryption was failed and the web server timed out.

For the remedy I provide the "Network Service" account access to folder “C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys” and everything works like a charm. I thought that the decryption was failing because the Account under which ASP.Net is running is not having the write perm thus the error I solved my problem but that was not end of the story.

When I came back the next dayand start using my old 1.1 application I starts getting exception as Cryptographic failure for loading the assembly as well as Com Interop Errors .I solved the Interop Errors by re adding the REF but the cryptographic errors was gone by once I removed the NETWORK SERVICE permission from the above mentioned folder.

REMEDIES

Renaming the MachineKeys to MachineKey Fix the problem with 1.1 applications but Now the 2.0 Protected Configuration is failing as it will create a new folder named MachineKeys and place the updated file (MachineKey) while doing

Aspnet_regiis -pe “sectionnameyouwanttosecure” /app “/virtualpathofapp”

Now rename back to MachineKeys and provide the access to NetWorkService and 1.1 will failed at compile, build time. Interestingly though I didn’t found the Cryptographic error if I am running the EXE alone

Note: - I don’t think I am hitting the CS1548 as the assembly signing is correct and it uses the relative path. ..\\..\\..\\ .Although the error desc are the same.

5:40 PM | Add a comment | Permalink | Blog it