For a little project I'm coding, I needed to programatically post new rows to a Google spreadsheet. Luckily, there are .NET bindings to the Google Data APIs. But they're still too low-level if you want to use the spreadsheet as a database. I looked around the source repo for awhile and found this little gem in python. In the words of its creator, it helps:
Make the Google Documents API feel more like using a database.
This module contains a client and other classes which make working with the
Google Documents List Data API and the Google Spreadsheets Data API look a
bit more like working with a heirarchical database. Using the DatabaseClient,
you can create or find spreadsheets and use them like a database, with
worksheets representing tables and rows representing records.
Just what I needed! So I ported it to .net, and here is the result. The biggest difference with the python version is that tables in .net are strongly typed. When you define a Table<Entity>, the Entity's public properties are serialized when posting to the spreadsheet.
The sample app is pretty much self-explanatory. You can do CRUD operations on the rows stored on a worksheet. The structured query operators are limited, but I didn't need more. I even threw in a LINQ provider (base classes courtesy of Matt Warren), so you can do strongly typed queries:
public int Amount { get; set; }
}
...
Table<Entity> t = ...
var rows = from e in t.AsQueryable()
where e.Amount == 5
select e;
It's still a bit rough around the edges, but usable.
UPDATE 11/03/2010: released GDataDB 0.2
12 comments:
This is really exciting, great to see a user friendly library for the Google Spreadsheets API. Have you considered contacting Frank to add this to the .NET library? We can talk about it over email if you like, j.s@google.com.
Hi.
do you have any samples on #GDataDB that can Update a Row in a sheet?
@Nullstring: try row.update
Thanks a lot!
Great job @mausch!
Great Job. Thanks )
Sergey
http://mausch.googlecode.com/svn/trunk/GDataDB/Sample/Program.cs
FAils me , thx
@Admirador: see http://bugsquash.blogspot.com/2010/10/gdatadb-02-released.html
i have problem mr.Mausch
please help me
-----------------------
The remote server returned an error: (400) Bad Request.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Net.WebException: The remote server returned an error: (400) Bad Request.
Source Error:
Line 94:
Line 95: private IList> Find(FeedQuery q) {
Line 96: var feed = (ListFeed) svc.Query(q);
Line 97: var l = new List>();
Line 98: foreach (ListEntry e in feed.Entries) {
my code is so simple ....
web page with 3 Text Fields and btn to save the data in spreadsheet as row as you said before ....
my code .cs is
---------------------
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using GDataDB;
using GDataDB.Linq;
using TestGDataDB_DeepCodeDev;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
IDatabaseClient client = new DatabaseClient("myemail@yahoo.com", "mypass");
const string dbName = "RetestdeepDB";
IDatabase db = client.GetDatabase(dbName);
//static IDatabase db = client.GetDatabase(dbName) ?? client.CreateDatabase(dbName);
const string tableName = "Employees";
// ITable table = db.GetTable(tableName) ?? db.CreateTable(tableName);
ITable table = db.GetTable(tableName);
// now I can fill a Person object and add it
var person = new Class_Emp();
person.EmpName = TextBox1.Text;
person.age = Convert.ToInt32(TextBox2.Text);
person.phone = Convert.ToInt32(TextBox3.Text);
//...
// see if row exists first by matching on Email
IList> rows = table.FindStructured(string.Format("EmpName=\"{0}\"", person.phone));
if (rows == null || rows.Count == 0)
{
// Email does not exist yet, add row
table.Add(person);
}
else
{
// Email was located, edit the row with the new data
IRow row = rows[0];
row.Element = person;
row.Update();
}
}
}
-----------------------
that was my simple page
Ahmed: try GData.Linq instead, or try removing the quotes from the condition.
Post a Comment