Archive for March, 2006

Are you using the new Transactional feature available with VS2005 namely the System.transaction??Not you are not using ??However you might opt for it once you know the feature provided thru it ..
I had a simply write up sometimes back on the usage of system.transaction here
However that post was mere an introduction into system.transaction and its capabilities.In short what System.Transaction provides you is An LTM(LIghtWeight Transaction Manager) and it will be auto upgraded to a MSDTC upon getting more RMs(ResourceManager).Not only this with the new TransactionScope you can wrap up the transactionscope Nicely.
The biggest wonder with System.transaction in WindowsVista timeframe would be the TRansactionalFileSystem without you to ship ur own CRM .You can read the msdnmag issue on the same subject line.
The system.transaction also provides you a commitable transaction which can span multiple threads.I had posted the post by Jim JhonSon on the same few days back.If you are looking for a link on this check JIM’s Blog here
However not only this the system.transaction support for PSPE(promotablesinglephaseenlistment) with the LTM to MSDTC is probably the faster tracsaction.
Further in the same line you can see that it builts-in a view of providing the next generation of transaction thus you can perform the task creation of your own CRM or BYOT features in a non-legacy way(no com+ here).
For more on transaction you can check florin’s blog here:-
 
 
Howvere my today’s post is not related to the faetures associated with system.transaction but what i am going to discuss is if you are running a code usign system.transaction your obvious aim would be that to use the LTM based transaction feature so that you ll get promoted to the expensive MSDTC as less  as possible.
Often due to a simple mistake and/or limitation with current transaction support  while dealing with system.transaction you can ends up with MSDTC transaction:-
See the below code :-
if you are using something like the following or trying to reuse the connection you ll endsup with MSDTC:-
try
            {
                using (TransactionScope scope = new TransactionScope())
                {
                    using (SqlConnection con = new SqlConnection("Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True"))
                    {
                        con.Open();
                        using(SqlCommand cmd1=new SqlCommand("update dbo.employee set lastname=’Suyama5′ where employeeid=6",con))
                        {
                            cmd1.ExecuteNonQuery();
                        }
                    }
                    using(SqlConnection con1=new SqlConnection ("Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True"))
                    {
                        con1.Open();
                        using(SqlCommand cmd2=new SqlCommand("update dbo.employee set lastname=’Suyama’ where employeeid=6",con1))
                        {
                            cmd2.ExecuteNonQuery();
                        }
                    }
                    Thread.Sleep(5000);
                    // use to see the transaction in dtc console
                    scope.Complete();
                }
            }
            catch(TransactionAbortedException ex)
            {
                MessageBox.Show(ex.Message);
            }
 
Now the workaround :-
 
The workaround is if you have multiple connection with same connectionstring(diff connection in most of the scenarios(although not always) ll ends up with multiple RM thus promoted to DTC anyway) pulls the conn and pass the same from the same local transaction and use it .
See Alazel’s post on connectionscope on the same.
For a detail library also see John Doty’s TransactionAdapter
 
 
More on different types of transactions and usage in future post
 
 
Advertisements

What I am going to post here is a  simple beauty of the SqlServer2005 Notification.I am goign to display How can you have a Self Updated Grid when the DB changes.We ll levearage the SqlServer Notification and Little Bit of thread scenario to ceate a simpe app here :-

However before proceeding with the code parts be sure to read the limitation of query notification.That you need to have your query confor to the Indexed view requisite and also always check to see the necessary permission requirement .Liek you might need to enable brokerif its not,also you might need to set the permission for the notification.

Another important point is the notification seems to be happens in a different thrad and once notification happens the same wiped out .Further as a last point you need to also consider taht notification not only happens for row updates.

Finally the thread techniques i am using here is mere to get the thing work and the recommnedation here would be to read JUVAL LOWY at idesign and MSDN .Further giving few ref here for the notification.Hope that would help too.

  SqlConnection

con; 

SqlCommand cmd; 

DataTable dt; 

SqlDataReader dr; 

SqlDependency depend; 

privatevoid Form1_Load(object sender, EventArgs e){

DataTable dt = newDataTable(); 

SqlDependency.Start(“Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True”);dt=configuredata();

dataGridView1.DataSource = dt;

}

privateDataTable configuredata(){

using (con = newSqlConnection(“Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True”)){

con.Open();

using (cmd = newSqlCommand(“select employeeid,lastname,city from dbo.employee”, con)){

depend =

newSqlDependency(cmd);depend.OnChange +=

newOnChangeEventHandler(MyOnChanged);dr = cmd.ExecuteReader(

CommandBehavior.CloseConnection);dt =

newDataTable();dt.Load(dr);

dr.Close();

}

return dt;}

}

delegatevoidsendmessage(DataTable msg); 

privatevoid MyOnChanged(object sender, SqlNotificationEventArgs e){

sendmessage handler = newsendmessage(this.ShowMessage); 

DataTable dt = newDataTable();dt = configuredata();

Object[] args = { dt }; 

if (this.InvokeRequired){

this.BeginInvoke(handler, args);}

}

privatevoid ShowMessage(DataTable dt){

dataGridView1.DataSource = dt;

}

privatevoid button1_Click(object sender, EventArgs e){

DataTable dt = newDataTable();dt = configuredata();

}

privatevoid Form1_FormClosing(object sender, FormClosingEventArgs e){

SqlDependency.Stop(“Data Source=SHREEMAN;Initial Catalog=test;Integrated Security=True”);}

Just Wait for my updates on the same using Web Scenario.Recommended reading on the Sqldependency is on :-

http://blogs.msdn.com/dataaccess/archive/2005/09/27/474447.aspx

http://msdn2.microsoft.com/en-us/library/a52dhwx7.aspx

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

 

http://www.sqljunkies.com/WebLog/nielsb/archive/2004/09/21/4292.aspx

http://www.thedatafarm.com/blog/PermaLink.aspx?guid=94659547-f5f7-44e9-ab57-1371d710d477

http://weblogs.asp.net/wallym/archive/2004/09/22/233120.aspx

http://www.thedatafarm.com/blog/PermaLink.aspx?guid=68701804-b4fb-41a9-a06f-09a503c6aea0

http://www.codeproject.com/useritems/SqlDependencyPermissions.asp

 

threading :-

http://www.idesign.net/idesign/uploads/Background%20with%201.1.zip

http://www.microsoft.com/belux/nl/msdn/community/columns/himschoot/backgroundprocessing2.mspx

http://blogs.msdn.com/brada/archive/2005/01/14/353132.aspx

http://www.yoda.arachsys.com/csharp/threads/

 http://www.code-magazine.com/article.aspx?quickid=0403071?

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>0</empid><name>raj</name><sal>500</sal><location>DEL</location></Employee>

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

<location>BLR</location></Employee>

</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 …

 

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=’

<RootXml>

 <Parent>

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

            <Child><Rowid>1</Rowid><ChildCodeId>39</ChildCodeId><CardNo>1</CardNo><versionCodeid>181</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>40</ChildCodeId><CardNo>1</CardNo><versionCodeid>182</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>41</ChildCodeId><CardNo>1</CardNo><versionCodeid>184</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>42</ChildCodeId><CardNo>1</CardNo><versionCodeid>198</versionCodeid></Child>

            <Child><Rowid>1</Rowid><ChildCodeId>43</ChildCodeId><CardNo>1</CardNo><versionCodeid>203</versionCodeid></Child>

</Parent>

 <Parent>

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

            <Child><Rowid>2</Rowid><ChildCodeId>29</ChildCodeId><CardNo>5</CardNo><versionCodeid>171</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>30</ChildCodeId><CardNo>5</CardNo><versionCodeid>172</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>31</ChildCodeId><CardNo>5</CardNo><versionCodeid>104</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>32</ChildCodeId><CardNo>5</CardNo><versionCodeid>198</versionCodeid></Child>

            <Child><Rowid>2</Rowid><ChildCodeId>33</ChildCodeId><CardNo>5</CardNo><versionCodeid>283</versionCodeid></Child>

</Parent>

 </RootXml>’

 

–USE the Same Table structure

 

  INSERTINTO @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)

 

INSERTINTO @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

                                    forxmlpath(‘Childs’),Type)

from @Parents P

FORXMLPATH(‘Parent’)  ,ROOT(‘RootXml’)

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″>

<Child RowID=”1″ ChildCodeId=”39″ CardNo=”1″ versionCodeid=”181″ />

<Child RowID=”1″ ChildCodeId=”40″ CardNo=”1″ versionCodeid=”182″ />

<Child RowID=”1″ ChildCodeId=”41″ CardNo=”1″ versionCodeid=”190″ />

<Child RowID=”1″ ChildCodeId=”42″ CardNo=”1″ versionCodeid=”201″ />

<Child RowID=”1″ ChildCodeId=”43″ CardNo=”1″ versionCodeid=”203″ />

</Parent>

<Parent

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

<Child RowID=”2″ ChildCodeId=”39″ CardNo=”1″ versionCodeid=”181″ />

<Child RowID=”2″ ChildCodeId=”40″ CardNo=”1″ versionCodeid=”182″ />

<Child RowID=”2″ ChildCodeId=”41″ CardNo=”1″ versionCodeid=”190″ />

<Child RowID=”2″ ChildCodeId=”42″ CardNo=”1″ versionCodeid=”201″ />

<Child RowID=”2″ ChildCodeId=”43″ CardNo=”1″ versionCodeid=”203″ />

</Parent>

</RootXml>’

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

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

 

  INSERTINTO @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)

 

INSERTINTO @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

                                    forxmlpath(‘Childs’),Type)

from @Parents P

FORXMLPATH(‘Parent’)  ,ROOT(‘RootXml’)

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:-

CREATEproc tnxt as 

declare @ptr1 binary(16) 

declare @ptr2 binary(16) 

createtable #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 

droptable #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>

  <Employee>

    <empid>1</empid>

    <name>shreeman</name>

    <sal>1070</sal>

    <deptno>10</deptno>

    <deptname>admin</deptname>

    <location>Hyd</location>

  </Employee>

  <Employee>

    <empid>0</empid>

    <name>raj</name>

    <sal>500</sal>

    <location>del</location>

  </Employee>

 </EMPOBJ>’

DECLARE @idoc int

EXECsp_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)

                                                  )

EXECsp_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>

<Employee empid=”1″ name=”shreeman” sal=”1070″ deptno=”10″  deptname=”admin” location=”HYD”/> 

<Employee empid=”0″ name=”raj” sal=”500″  location=”DEL”/>   

</EMPOBJ>’

DECLARE @idoc int

EXECsp_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)

                                                  )

EXECsp_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.