<%@ WebService language="C#" class="AdvocateServer" %>

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Threading;
using System.Web.Services;
using System.Xml.Serialization;
using Mono.Data.SqliteClient;


public struct Note
{
    private string title;
    private string body;
	private int id;
	
	public int ID
	{
		get{ return id; }
		set{ id = value;}
	}

    public string Title
    {
        get{ return title; }
        set{ title = value; }
    }

    public string Body
    {
        get{ return body; }
        set{ body = value; }
    }
}


public class NoteList
{
	private string[] notes;
	
	public NoteList( )
	{
		
	}
	
	public NoteList( string[] names )
	{
		this.notes = names;
	}
	
	public string[] Names
	{
		get{ return notes; }
		set{ notes = value; }
	}
	
}


public class AdvocateServer {

    private IDbConnection dbcon;
    private IDbCommand dbcmd;
	//private SqliteConnection dbcon;
	//private SqliteCommand dbcmd;
	//private OleDbCommand dbcmd;
	private string windowsURI;
	private string linuxURI;
	private string currentURI;

    public AdvocateServer( )
    {

		windowsURI = "URI=file:c:/temp/svn/Advocate/web/Advocate.db";
		linuxURI = "URI=file:/home/blursoft/public_html/devFarm/sprouts/web/Advocate.db";		           
		//
		// EDIT ME: set to indicate which system you're testing on
		//
	//	currentURI = windowsURI;
		currentURI = linuxURI;

		dbcon = null;
		dbcmd = null;
		
    }

	private void OpenDatabase( )
	{
		dbcon = new SqliteConnection( currentURI );
        dbcon.Open();
		dbcmd = dbcon.CreateCommand();
	}
	
	private void CloseDatabase( )
	{
		if( dbcon.State == ConnectionState.Open )
			dbcon.Close( );
	}
	
	private string EscapeString( string s )
	{
		//s = s.Replace("\\","\\\\");
		s = s.Replace("\"","\"\"");
		return s;
		
	}
	
    [WebMethod]
    public void AddNote( Note note )
    {
        OpenDatabase( );

		string author = "steven";
		string body = EscapeString(note.Body);
		string title = EscapeString(note.Title);
        string sql = "INSERT INTO Note VALUES (NULL,\""+author+"\",\""+body+"\",\""+title+"\")";
		/*
		string sql = "INSERT INTO Note VALUES (NULL, @author , @body , @title)";
        dbcmd.CommandText = sql;
		dbcmd.Parameters.Add("Author",OleDbType.WChar);
		dbcmd.Parameters[0].Value = author;
		dbcmd.Parameters.Add("Body",OleDbType.WChar);
		dbcmd.Parameters[1].Value = body;
		dbcmd.Parameters.Add("Title",OleDbType.WChar);
		dbcmd.Parameters[2].Value = title;
		*/
		dbcmd.CommandText = sql;
		dbcmd.ExecuteNonQuery();
		
		
		CloseDatabase( );

    }


	[WebMethod]
	public void UpdateNote( Note note )
	{
		OpenDatabase( );
		note.Body = EscapeString( note.Body );
		string sql = "UPDATE Note SET Body=\""+note.Body+"\" WHERE NoteID=\""+note.ID+"\"";
		dbcmd.CommandText = sql;
		dbcmd.ExecuteNonQuery( );
		CloseDatabase( );

	}


    [WebMethod]
    public Note GetNote( string name )
    {
       
		Note n = new Note();
        OpenDatabase( );
		string sql = "select NoteID, Body from Note where Title=\""+name+"\"";
		dbcmd.CommandText = sql;
		IDataReader reader = dbcmd.ExecuteReader();
		
		while(reader.Read())
		{
			//n.ID = Convert.ToInt32((string)reader[0]);
			//n.Body = (string)reader[1];
			n.ID = reader.GetInt32( 0 );
			n.Body = reader.GetString( 1 );
			n.Title = name;
		}
		
		
		CloseDatabase( );
        if( reader != null )
		{
			reader.Close();
        }
		
		
		return n;

    }

    [WebMethod]
    public void DeleteNote( int noteID )
    {
   		OpenDatabase();
		string sql = "DELETE FROM Note WHERE NoteID=\""+noteID+"\"";
		dbcmd.CommandText = sql;
		dbcmd.ExecuteNonQuery();
		CloseDatabase(); 
    }


    [WebMethod]
    public NoteList GetNoteListing( )
    {
		
        int i = 0;
		string[] names = null;
		string sql = "select Title from Note";
		ArrayList strings;
		NoteList notes = new NoteList( );
		OpenDatabase( );
		dbcmd.CommandText = sql;
		
		IDataReader reader = dbcmd.ExecuteReader();
		
		strings = new ArrayList();
		
		while( reader.Read() )
		{
			strings.Add( reader.GetString(0) );
			//strings.Add( (string)reader[0] );
		}
		
		
		if( reader != null )
		{
			reader.Close();
        }
		CloseDatabase( );
		
		
		if( strings.Count != 0 )
		{
			names = new string[ strings.Count ];
				
			IEnumerator iter = strings.GetEnumerator( );
				
			while( iter.MoveNext())
			{
				names[i] = (string)iter.Current;
				i++;
			}
		}
		
		notes.Names = names;
		return notes;
		
    }


}