multiple updates in one round trip?

  • Thread starter Thread starter Anon
  • Start date Start date
A

Anon

Hi all.

Is there any way using ADO.net that I can send an entire datatable
(with multiple rows) through the adapter and in one round trip
accomplish multiple updates/inserts (using stored procedures)?

If not, is there any other way to accomplish that? I have seen people
send strings like "aa,bb,cc,dd;gg,hh,ii,jj;" and parse it in the
stored procedure. That seems pretty messy to me and probably not
worth it in all but the most extremely performance-sensitive
situations.

What does everyone think?

Thanks!
 
You should do it via XmlSerialization in C#, and then inserting/updating via
XPath query in your database storedPRocedure.
This way its absolutely clean and accomplishes the work with good
perfomance.

I think following the steps I am putting down could solve your purpose.

1. Serialize all the Data in your grid into XML using XmlSerializer and
MemoryStream, This will serialize the complete collection to XML format. You
need to check the objects in the collection are serializable.
/// <summary>

/// Takes a serializable object and serializes it to its XML string
representation.

/// </summary>

/// <param name="objToSerialize">The object to be serialized. This object
must be serializable or the method will fail.</param>

/// <returns>A string of XML representing the serialized version of the
object.</returns>

public static string SerializeToXML(Object objToSerialize)

{

System.Xml.Serialization.XmlSerializer mySerializer = null;

MemoryStream msSerializedXML = null;

try

{

// Instantiate the Serializer with the type of object that is being
deserialized.

mySerializer = new
System.Xml.Serialization.XmlSerializer(objToSerialize.GetType());

// Serialize the object to xml

msSerializedXML = new MemoryStream();

mySerializer.Serialize(msSerializedXML, objToSerialize);

// Get XML as string

string xmlStr =
System.Text.ASCIIEncoding.ASCII.GetString(msSerializedXML.ToArray());

msSerializedXML.Close();

if (xmlStr == null || xmlStr.Length == 0)

{

return String.Empty;

}

return xmlStr;

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (msSerializedXML != null) { msSerializedXML.Close(); }

}

}

2. In the stored Proc, take this in a varchar(max) or nText datatype,
convert to XML and use xPath Queries to Insert/Update the table you want to.


This way you could to this in one call.

Regards,
Sugandh
 
You should do it via XmlSerialization in C#, and then inserting/updating via
XPath query in your database storedPRocedure.
This way its absolutely clean and accomplishes the work with good
perfomance.

I think following the steps I am putting down could solve your purpose.

1. Serialize all the Data in your grid into XML using XmlSerializer and
MemoryStream, This will serialize the complete collection to XML format. You
need to check the objects in the collection are serializable.
/// <summary>

/// Takes a serializable object and serializes it to its XML string
representation.

/// </summary>

/// <param name="objToSerialize">The object to be serialized. This object
must be serializable or the method will fail.</param>

/// <returns>A string of XML representing the serialized version of the
object.</returns>

public static string SerializeToXML(Object objToSerialize)

{

System.Xml.Serialization.XmlSerializer mySerializer = null;

MemoryStream msSerializedXML = null;

try

{

// Instantiate the Serializer with the type of object that is being
deserialized.

mySerializer = new
System.Xml.Serialization.XmlSerializer(objToSerialize.GetType());

// Serialize the object to xml

msSerializedXML = new MemoryStream();

mySerializer.Serialize(msSerializedXML, objToSerialize);

// Get XML as string

string xmlStr =
System.Text.ASCIIEncoding.ASCII.GetString(msSerializedXML.ToArray());

msSerializedXML.Close();

if (xmlStr == null || xmlStr.Length == 0)

{

return String.Empty;

}

return xmlStr;

}

catch (Exception ex)

{

throw ex;

}

finally

{

if (msSerializedXML != null) { msSerializedXML.Close(); }

}
}

2. In the stored Proc, take this in a varchar(max) or nText datatype,
convert to XML and use xPath Queries to Insert/Update the table you want to.

This way you could to this inonecall.

Regards,
Sugandh









- Show quoted text -
Click to expand...


Sugandh ,

Thanks for that excellent and detailed reply - I appreciate it. That
sounds like a great technique. I'm going to try that.
 
If the intent is to upload a batch of data from another data source (even an
array) then consider SqlBulkCopy. ADO.NET does not make a good batch update
interface.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
Visit www.hitchhikerguides.net to get more information on my latest book:
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
and Hitchhiker's Guide to SQL Server 2005 Compact Edition (EBook)
-----------------------------------------------------------------------------------------------------------------------
 
Back
Top