HTML 5 and SQL Lite Sample Code

Here is a sample code that uses sqllite to store data locally in the browser that supports HTML5.

make sure to set this on the page event: body onload="createDatabase()"

html code... i replaced all <> characters to [ and ]...

sql statements:[br /] [textarea cols=50 rows=3 id="txtSQLStatement"][/textarea] [br /] [input type="button" id="Button1" value="execute" onclick="clickExecuteQuery()" /] [br /] [div id="status"][/div] [br /]

javascripts code:

var sampledb;
function createDatabase(){
try {
if (!window.openDatabase) {
alert('works on HTML5 only');
} else {
var name = 'sampledb';
var version = '1.0';
var description = 'Sample Database';
var maxSize = 32768; // in bytes
sampledb = openDatabase(name, version, description, maxSize);
}
} catch(e) {
// Error handling code goes here.
alert("Unknown error "+e+".");
return;
}
}


function executeSQL(sqlStatement)
{
sampledb.transaction(
function (transaction) {
transaction.executeSql(sqlStatement, [], nullDataHandler, errorHandler);
}
);
}

function executeQuery(sqlStatement)
{
sampledb.transaction(
function (transaction) {
transaction.executeSql(sqlStatement, [], dataHandler, errorHandler);
}
);
}

function nullDataHandler(transaction, results)
{
var d = new Date();
updateTransactionStatus("last statement: " + document.getElementById("txtSQLStatement").value + " ran at " + d.toUTCString());
}

function errorHandler(transaction, error)
{
// Error is a human-readable string.
updateTransactionStatus(' Error Message '+error.message+' (Code '+error.code+')');
return false;
}

function dataHandler(transaction, results)
{
// Handle the results
var string = "
results
";
for (var i=0; i}

var d = new Date();
updateTransactionStatus("last statement: " + document.getElementById("txtSQLStatement").value + " ran at " + d.toUTCString() + "
" + string);
//updateTransactionStatus(string);
}

function clickExecuteQuery(){
executeQuery(document.getElementById("txtSQLStatement").value);

}

function updateTransactionStatus(message){
document.getElementById("status").innerHTML = message;

}

Sample Statements

Create table
CREATE TABLE [Employee]( [ID] [integer] NOT NULL PRIMARY KEY AUTOINCREMENT, [EmployeeID] [int] NULL, [FirstName] [varchar](20) NULL, [LastName] [varchar](20) NULL)

Delete a table
Drop table employee

Insert Records
Insert into employee (employeeid,firstname,lastname) values(1,'z','a');

Update Records
Update employee set firstname='zaldy'

Remove Records
Delete from employee where firstname='zaldy'

Read Records
Select ID, FirstName, LastName from employee

Call Web Service Function via Javascript using AJAX

The implementation of calling a web service function via javascript has become so tremendously easy with Microsoft’s AJAX components. That’s why I’m writing this blog for me to be reminded that I no longer have to worry of writing a special handler to manage the different implementations if the client is using IE or Firefox or Safari or Chrome. Everything is pretty much taken care of by the AJAX components. The beauty part of it also is I can use the dot notation to access the values in the returned objects.

First step is to create a web project in VS 2008 - Target framework: .NET 3.5

Add New Web Service Item

Configure the web service to allow a javascript call

[System.Web.Script.Services.ScriptService]
public class WebService : System.Web.Services.WebService {
…
}

Inside the web service item, I created a custom struct as my data holder
public struct Note
{
public DateTime DateTimeStamp;
public string Comments;
}

I created a couple of functions (getDateStampedNote and getNotes)
[WebMethod]
[XmlInclude(typeof(Note))]
public Note getDateStampedNote(string comments)
{
if (comments == string.Empty)
throw new Exception("Exception: comments must not be empty.");

Note note;
note.DateTimeStamp = DateTime.Now;
note.Comments = comments;
return note;
}

[WebMethod]
[XmlInclude(typeof(Note))]
public ArrayList getNotes()
{
ArrayList ar = new ArrayList();
ar.Add(new Note
{
DateTimeStamp = DateTime.Now,
Comments = "First Note"
});

ar.Add(new Note
{
DateTimeStamp = DateTime.Now,
Comments = "Second Note"
});

ar.Add(new Note
{
DateTimeStamp = DateTime.Now,
Comments = "Third Note"
});

return ar;
}

Note: import XML serialization library in the web service.
using System.Xml.Serialization;

In default.aspx, I added a script manager and inside that I referenced the web service that I just created.

Same aspx file, I added a bunch of javascript functions (but reusable):
function dateStampMyComments()
{
//get comments value
var comments = document.getElementById("comments").value;
WebService.getDateStampedNote(comments,processResultSucess,processResultFailed,Object);
}

function getNotes()
{
WebService.getNotes(processResultGetNotes,processResultFailed,Object);
}

function processResultGetNotes(result)
{
for(index in result){
note = result[index];

document.getElementById("notes").innerHTML = document.getElementById("notes").innerHTML + "
" + note.Comments + " - " + note.DateTimeStamp;
}
}

function processResultSucess(result){
alert(result.DateTimeStamp + " - " + result.Comments );
}

function processResultFailed()
{
alert("Webservice Failed \n" + result._message);
}

Automatically mapping a user to a login in SQL Server

Automatically mapping a user to a login, creating a new login if it is required

The following example shows how to use Auto_Fix to map an existing user to a login of the same name, or to create the SQL Server login Mary that has the password B3r12-3x$098f6 if the login
Mary does not exist.

EXEC sp_change_users_login 'Auto_Fix', 'Mary', NULL;
GO

for more info, go to msdn website at:

http://msdn.microsoft.com/en-us/library/ms174378.aspx