Accessing databases with Hummingbird and scripting part II

On this post you will see how to create the midleware layer for our database sample application. we will use asp.net+fluorineFX framework.
If you had never used fluorineFX, you can visit:

http://www.fluorinefx.com/

if you want to learn how to preparing an asp.net project in order to use the fluorineFX framework visit:

http://www.fluorinefx.com/docs/fluorine/index.html

is very simple using fluorineFX with asp.net, and like you will see on the official documentation, you must to add a reference on the asp.net project to the fluorineFX framework, and then you are ready to coding.
You must to create a new project called 'WebServPortalFlex'.
Once you have your project configured to use fluorineFX, you must to create a new class called 'servicios' like you see on the following picture:

write a function called ejecuta_query and a function called obtenerPaginasUsuario inside the class servicios, with the following code:

private DataSet ejecuta_query(String query)
{

// you must to change the conexion variable to use your own conexion string
String conexion="server=localhost;database=dbtest;uid=dbuserid;pwd=dbpassword;";

SqlConnection con=new SqlConnection(conexion);
DataSet ds=new DataSet();
SqlDataAdapter da = new SqlDataAdapter(query,con);
try
{
da.Fill(ds);
}
catch(Exception er)
{
con.Close();
return ds;
};

con.Close();
return ds;

}



public DataSet obtenerPaginasUsuario(string username)
{
DataSet ds;
int regs;
string query="exec sp_obtenerPaginasUsuario '"+username+"'";
ds=ejecuta_query(query);
regs=ds.Tables[0].Rows.Count;
if (regs==0)
{
// return some info to the front end to indicate that there is no rows
}

return ds;

}

That's all, now you have finished the midleware layer, it has only a remote procedure (function) called obtenerPaginasUsuario. this function will be called for the front end.


Now you will must write your backend logic in the sql server database.

first you must be sure that there is a database on the sql server engine, with the name that you pretend to use in the conexion string of the asp.net project. next you must be sure that inside this database there is a user account with the username and password that you are using in the conexion string.
The next step is to create the table that will store the data. this table has the following structure:

CREATE TABLE tb_pagina (
cod_pag varchar (100) ,
correlativo int ,
titulo varchar(255),
username varchar (50) ,
fecha_creacion datetime ,
fecha_modificacion datetime
)

Then you must to insert some records for testing later our app.

Then you must to create a new stored procedure with the following code:

create proc sp_obtenerPaginasUsuario(@username varchar(50))
as
begin

create table #paginas(cod_pag varchar(100),titulo varchar(255),fecha_creacion datetime,
fecha_modificacion datetime);

insert into #paginas select distinct cod_pag,titulo,fecha_creacion,fecha_modificacion
from tb_pagina where username=@username

select '0' as cod,'' as men,a.cod_pag,a.titulo,convert(varchar,a.fecha_creacion,103) as fecha_creacion,
convert(varchar,a.fecha_modificacion,103) as fecha_modificacion
from #paginas a order by convert(datetime,a.fecha_creacion)

end

you must give to the above stored procedure the permissions to get records from the database, and you must be sure that the user's account that you are using on the 'conexion' string variable is granted to execute the stored procedure.

That's all, now you finished the back end logic. now is time for test our front end:

open a web browser, and visit the Hummingbird page created, you must see something like this:

Remember that the hb_grid widget is invisible in visit mode, that's the reason why it no appears on the above image. Now click on the 'get rows' text and you will see a grid with the records retrieved from the database like showed on this image:

Great, on these posts you have learned how to access a backend, retrieve records from it, create a grid dinamically and show data on it. Remember that this is only a very simple sample and there is very much that you can make with HB.
On the next post i will show you the hb_grid widget with more details, so you will have a more complete understanding about it and how it integrates inside HB.

Have a nice day... :)