T-SQL can be a powerful tool for developers if exploited. It allows you to programatically manipulate your data on the “database-level”. This post will show a simple T-SQL script on how to create a recursive user-defined function [UDF] that can be used application-wide via a simple query. To demonstrate, we will use one of the most common mathematical problems in which a recursive algorithm can be applied - solving for factorial.

Create a UDF

Create Function [dbo].[Factorial]
(
@n int
)
Returns int

AS
Begin

declare @factorial int

if @n = 0
set @factorial = 1
else
set @factorial = @n * dbo.Factorial(@n - 1) –call it recursively

return @factorial

You can then simply call it in any of your queries like:

select dbo.Factorial(6) –returns 720

• 0 Comments

Tags: mssql sample script • t-sql • user-defined function

Sometimes, we wanted to return our query result as datatable for easy handling of data [probably making it as a datasource of a control].

Here’s a simple function:

Public Function GetDataTable(ByVal conStr As String, ByVal qryStr As String) As DataTable

Dim dt As DataTable = New DataTable()
Dim con As SqlConnection = New SqlConnection(conStr)

Dim adapter As SqlDataAdapter = New SqlDataAdapter(qryStr, con)

Try
‘fill datatable
adapter.Fill(dt)
Catch ex As SqlException
System.Web.HttpContext.Current.Response.Write(”SQL Error!!!”)
Finally
con.Close()
End Try

‘return the data table.
Return dt

End Function

Then, you can use it like this:

myDataGrid.DataSource = GetDataTable(myDbConnection, myQuery)
myDataGrid.DataBind()

• 0 Comments

Tags: .Net asp .net tutorial • mssql sample script • vb .net sample code

Ever get tired of setting up a database server everytime you transfer from machine to machine.Maybe when you deploy your code from development to production. Well, i just found out this amazing thing called SQLite.

I’m not sure wether this is built-in or an add-on to php but it can be used together with it. What i’ve tested on the other hand is its relationship with Java. It can be easily appended to your java application through the use of a jar file that can be found here. It’s simple to use since you don’t need to learn some new syntax to get started. If you know JDBC, then you’re set to go.

      Class.forName("org.sqlite.JDBC");
      Connection conn = DriverManager.getConnection("jdbc:sqlite:test.db");

Just specify that you’ll be using the SQLite driver and point out the database that you want to use (in this case, test.db). If the database doesn’t exist, it’ll create a file named “test.db”. That file serves as the database itself, so take care that you don’t delete it. For every new database, it’ll create a file just like that, depending on the database name that you’ve supplied. To run the program, make sure that the jar file is in the classpath.

Now you’ve got a serverless database!

update (5/13/09):

I was searching for a database manager for SQLite and good news! I found one and it’s just a plug-in for Firefox. How cool is that? Download it here.

• 1 Comment

Tags: java sample code