Welcome to AspAdvice Sign in | Join | Help

Alessandro Gallo

.NET & Beyond
Quick Reference for the Atlas DataTable

This is a list of Q&A about the Web.Data.DataTable control provided by the Atlas framework. All the examples provided refer to a DataTable instance named dataTable.

 

  1. How can I get a DataTable instance from the server?
  2. How can I pass a DataTable instance to the server?
  3. How many rows has my DataTable?
  4. I want to get the value in row x, column y.
  5. How can I add a new row to the DataTable?
  6. I want to update an existing row.
  7. I want to delete an existing row.
  8. Did I insert/update/delete any rows?
  9. I want to get my inserted/updated/deleted rows.
  10. What about columns?
  11. How can I clear my DataTable?
  12. How can I get a DataTable contained in a DataSet?

 

1. How can I get a DataTable instance from the server?
Just return it from your Page/Service method. For example, consider this WebMethod that returns a System.Data.DataTable instance:

#using System.Data;

[WebMethod]
public DataTable GetDataTable()
{
    DataTable dt = new DataTable();

    dt.Columns.Add(new DataColumn("Name", typeof(string)));
    dt.Columns.Add(new DataColumn("LastName", typeof(string)));
    dt.Columns.Add(new DataColumn("Email", typeof(string)));

    dt.Rows.Add("John", "Doe", "john.doe@example.com");
    dt.Rows.Add("Joe", "Smith", "jsmith@example.com");

    return dt;
}

For example, if you declare this method in a Page, you can obtain the client DataTable instance by invoking the server method from the client proxy:

function getData() {
    PageMethods.GetDataTable(onGetComplete);
}
function onGetComplete(result) {
    var dataTable = result;
}

2. How can I pass a DataTable instance to the server?
When you are done with a client DataTable instance, you can pass it to any Page/Service method that accepts a System.Data.DataTable as an argument; the Atlas framework will take care of the serialization/conversion between types.
Note: actually it's not possible to pass a DataTable back to the server without building a custom type converter.

3. How many rows has my DataTable?
The number of rows in the DataTable can be retrieved with the statement

var nRows = dataTable.get_length();

 

4. I want to get the value in row x, column y.
This can be done use the getItem() and getProperty() methods of the Web.Data.DataTable class. The getItem() method returns a Web.Data.DataRow object, while getProperty() returns a value given a column name. Putting these two methods together, if we want to obtain the value in row x, column name 'y', we have to write a statement like

var theValue = dataTable.getItem(x).getProperty('y');

 

5. How can I add a new row to the DataTable?
A very simple way to add a new row is to create a row object using Javascript's object notation. Each field in the row object has the name of the corresponding column, while the value is the one to be inserted. For example, if our DataTable schema has the three columns 'Name', 'LastName', 'Email' of type String, we can build a row object for that schema with the statement:

var oRow = { Name:'John', LastName:'Doe', Email:'jdoe@example.com' }

and then pass this object to the add() method of the DataTable class:

dataTable.add(oRow);

to add the new row to the DataTable instance.

 

6. I want to update an existing row.
To update an existing row, we have to retrieve it using the getItem() method and then set the value for a particular column using the setProperty() method. For example, the statement

dataTable.getItem(1).setProperty('Name', 'Mary');

will set the value in the second row, column 'Name', to 'Mary'.

 

7. I want to delete an existing row.
To delete an existing row, just retrieve it with the getItem() method and then pass it to the remove() method. For example, the statement

dataTable.remove(dataTable.getItem(2));

will remove the second row from the DataTable.

 

8. Did I insert/update/delete any rows?
By writing a statement like:

var hasChanges = dataTable.get_isDirty();

we can know if the DataTable has been modified.

 

9. I want to get my inserted/updated/deleted rows.
This can be done with the statement

var oChanges = dataTable.getChanges();

that returns an object with three properties:

 

  • inserted, that exposes an array containing the inserted rows;
  • updated, that returns an array containing the updated rows;
  • deleted, that returns an array containing the deleted rows;
Thus, to retrieve the updated rows we have to write:

var updatedRows = dataTable.getChanges().updated;

10. What about columns?
Info on a particular column can be retrieved with the statement

var colName = dataTable.getColumn('Name');

that returns the Web.Data.DataColumn instance for the 'Name' column. The DataColumn class exposes three methods:
  • get_columnName(), returns the name of the column;
  • get_dataType(), returns the type of data stored in the column;
  • get_defaultValue(), returns the default value for the column.

 

11. How can I clear my DataTable?
A DataTable can be cleared with the statement

dataTable.clear();

12. How can I get a DataTable contained in a DataSet?
The Atlas framework represents a System.Data.DataSet instance as an array of Web.Data.DataTable objects. If we are dealing with a Page/Service method that returns a DataSet, we can for example get the first DataTable with the statement

var dataTable = dataSet[0];

Go Back to the Questions

Posted: Sunday, March 05, 2006 4:00 PM by Garbin

Comments

Inventisity said:

Good work :)
# March 8, 2006 10:43 PM

Steve said:

Can this be bound then to a gridview?
# March 15, 2006 9:49 PM

Garbin said:

Steve,
the Atlas framework doesn't provide a gridview client control at the moment. What you could do is bind the client DataTable to a ListView with tabular layout.
# March 16, 2006 7:04 PM

Eric said:

This is a great example. I am having trouble sending a datatable back to a web method as a parameter. I defined a webmethod on the page

[WebMethod]
public bool SendDT(DataTable dt)
{
if (dt.Rows.Count == 3)
{
return true;
}
else
{
return false;
}
}

with the same GetDataTable method in your example. I then get a datatable, add a row and pass it back to the method

var dataTable;
function getData() {
PageMethods.GetDataTable(onGetComplete);
}
function onGetComplete(result) {
dataTable = result;
debug.trace(dataTable.get_length());
}
function sendback(){
var oRow = { Name:'Eric', LastName:'E', Email:'eric@example.com' };
dataTable.add(oRow);
PageMethods.SendDT(dataTable,sendbackcomplete,sendbackto,sendbackerr);
}
function sendbackcomplete(result){
alert(result);
}
function sendbackto(result){
alert('to' + result);
}
function sendbackerr(result){
alert('err' + result);
}

it fires the error callback with a null result. Any suggestions?
# March 17, 2006 3:29 PM

Steve said:

Is it possible to bind this DataTable to a Sys.UI.Select object (ie. a listbox) ?
# April 4, 2006 10:38 AM

Garbin said:

Steve,

I've posted an example here:
http://forums.asp.net/thread/1247369.aspx
# April 4, 2006 1:53 PM

Welly said:

Is it possible to bind this client dataset on Gridview?
# May 3, 2006 2:28 PM

Garbin said:

Welly, a GridView is a server control thus the recommended way to bind it is using a server DataSet. While it is possible to pass a DataSet from server to client side, at the moment it's not possible to serialize it from client to server.
Garbin
# May 5, 2006 1:04 PM

Dmitry Gromov said:

I've troubles accesing my DataBese on server.
what's wrong?
# May 18, 2006 3:31 AM

Garbin said:

Dmitry, I suggest you to open a thread in the Atlas forums:
http://forums.asp.net/1007/ShowForum.aspx
and describe your problem in detail. I'm sure that someone will help you.
Garbin
# May 18, 2006 5:01 AM

Pratibk said:

garbin,
one more question on the datatable. If i am binding the value of the first row,first column to a label , what should be set as the set_path property. SHould it be the column name ? I have tried that, it doesnt work for me.
# May 19, 2006 1:40 AM

Hamed Alinassab said:

Hi,

I think The Problem is Solved.

You can use this Generic Type 'System.Collections.Generic.Dictionary<string,object>' For dt parameter instead of DataTable it self in SendDT Web Method
as:

public bool SendDT( System.Collections.Generic.Dictionary<string,object> dt)
{
           if (dt.Count == 3)
           {
               return true;
           }
           else
           {
               return false;
           }
       }

# May 19, 2006 11:07 AM

Ivan Porto Carrero said:

# May 20, 2006 8:32 PM

NET Development Blog said:

# May 31, 2006 12:50 PM

Bill said:

Thanks for the great reference. On sending Datatables from client to server you mention :

"not possible to pass a DataTable back to the server without building a custom type converter"

Could you elaborate (generally speaking) on what would need to be done and has anyone done it yet?

To me this seems to be the biggest missing piece of the puzzle.  Would be huge if I could send the dataset back.

Thanks
# June 2, 2006 8:11 PM

Garbin said:

Bill, the Atlas framework performs serialization/deserialization of complex server types using a converter. A converter is a class that inherits from a base JavascriptConverter class. The purpose of a converter is to override two methods: Serialize() that takes an instance and outputs the corresponding JSON, Deserialize() that takes JSON and outputs a server type instance. Actually the framework defines a DataSet converter, but only its Serialize() method is implemented, while Deserialize() throws a NotSupportedException. This is the reason why a "client dataset" can't be passed back to server.
Garbin
# June 5, 2006 8:35 AM

IBenRush::MyBlog said:

# June 7, 2006 2:32 AM

Fabian said:

Great article, Thanks
# July 17, 2006 2:32 PM

Sebastian Brand said:

I think Deserialize() is supported now. I can do a save() on the datasource object and it sends the changed data to the server.
# August 2, 2006 11:30 AM

Garbin said:

Sebastian: this is interesting, have you tried to round-trip a data table using a web service? It seems that the Deserialize() method of the DataTable converter has not yet been implemented in the July CTP.
# August 3, 2006 6:53 PM

Anuj Pandey said:

Hello

I wanted to know if i can set the entire DataRow to a Updated Row

ie.

dataTable.getItem(i)=dataTable.getItem(j)

thanks

Anuj Kumar Pandey

VMS Inc

# August 24, 2006 3:24 AM

Garbin said:

Hi,

the above statement won't run, since it's not possible to assign to a function result.

I believe the only way to get the correct events fired and to correctly update the list of updated rows is to loop through the item j and call setProperty on item i.

# August 24, 2006 4:20 AM

Andy said:

as we are handling data tables in asp.net 1.1 and 2.0. the same approch will be applicable in this case also

Cheers.......

Andy

# August 24, 2006 4:28 AM

roland roos said:

I have even problems serializing my DT from server to client.

I get :

The server method 'GetAllTasksAsDataTable' failed with the following error:

System.InvalidOperationException: A circular reference was detected while serializing an object of type 'System.Reflection.Module'

I have a straight forward webmethod function, and it works if I test the method from the browser as a plain webservice:

However, if I call it from Javascript, I get this error. Any clue?

[WebMethod]

   public DataTable GetAllTasksAsDataTable(string strUser)

   {

       Task[] oArrayTsk = GetAllTasks(strUser);

       DataTable oDt = new DataTable();

       oDt.TableName = "AllTasks";

       oDt.Columns.Add(new DataColumn("Id", typeof(string)));

       oDt.Columns.Add(new DataColumn("Name", typeof(string)));

       oDt.Columns.Add(new DataColumn("User", typeof(string)));

       foreach (Task oTsk in oArrayTsk)

       {

           oDt.Rows.Add(oTsk.Id, oTsk.Name, oTsk.User);

       }

       return oDt;

   }

Webservice is returning:

 <?xml version="1.0" encoding="utf-8" ?>

- <DataTable xmlns="http://tempuri.org/">

- <xs:schema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">

- <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:MainDataTable="AllTasks" msdata:UseCurrentLocale="true">

- <xs:complexType>

- <xs:choice minOccurs="0" maxOccurs="unbounded">

- <xs:element name="AllTasks">

- <xs:complexType>

- <xs:sequence>

 <xs:element name="Id" type="xs:string" minOccurs="0" />

 <xs:element name="Name" type="xs:string" minOccurs="0" />

 <xs:element name="User" type="xs:string" minOccurs="0" />

 </xs:sequence>

 </xs:complexType>

 </xs:element>

 </xs:choice>

 </xs:complexType>

 </xs:element>

 </xs:schema>

- <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">

- <DocumentElement xmlns="">

- <AllTasks diffgr:id="AllTasks1" msdata:rowOrder="0" diffgr:hasChanges="inserted">

 <Id>3</Id>

 <Name>Task 3</Name>

 <User>Roland</User>

 </AllTasks>

 </DocumentElement>

 </diffgr:diffgram>

 </DataTable>

# September 26, 2006 2:59 PM

charles Bretana said:

In sample web.config. it lists

<jsonSerialization maxJsonLength="500">

  <converters>

    <add name="ConvertMe" type="Acme.SubAcme.ConvertMeTypeConverter"/>

  </converters>

</jsonSerialization>

Obviously, Acme.SubAcme.ConvertMeTypeConverter does not exist... All I want to do is get a datatable back from server... Do I need to write my own converter ?

# November 17, 2006 8:34 PM

charles bretana said:

Ok, to eliminate the cicular reference error, add the folloowing to the web config...

<jsonSerialization maxJsonLength="50000">

 <converters>

   <add name="ConvertMe" type="Microsoft.Web.Preview.Script.Serialization.Converters.DataTableConverter"/>

 </converters>

</jsonSerialization>

But I am still not able to see the datatable in Javascript.  The return value in the callback is null/undefined.

Ideas anyone ?

# November 18, 2006 4:41 PM

Raj said:

It accessing datatable on client like this is JSON or st different

# November 28, 2006 5:54 AM

Seema said:

i am trying to return a datatable using pagemethods, i got an error

System.StackOverflowException was unhandled

# March 11, 2008 2:36 AM

Garbin said:

Hi,

please be sure that your code doesn't contain any recursive loops (such as accessing the property itself - instead of a field - inside a getter).

# March 15, 2008 11:59 AM
New Comments to this post are disabled