Allegro.pl WebApi for .Net

I just published my open source project: AllegroWebApi for .Net. - it really simplifies usage of Allegro.pl web api.
Project is available at https://code.google.com/p/allegro-web-api/
Here is sample code:

 // Dont forget about usings!  
 uses AllegroWebAPI;  
 // This is main class  
 var api = new AllegroClient("YOUR API KEY HERE");  
 // Some functions needs your login/password to work properly  
 api.Login("YOUR ALLEGRO LOGIN", "YOUR ALLEGRO PASSWORD");  
 // Get my data   
 MyAllegro my = new MyAllegro(api);        
 Console.WriteLine(my.MyData.Email);  
 // Get user Id by user login  
 long uid = api.GetUser("ja-biel").Id;  
 // Retrieve user feedback  
 Feedback feedback = new Feedback(api);  
 foreach (var qq in feedback.GetFeedback(uid, CommentTypeAll.All))  
 {  
   Console.WriteLine(qq.fDesc);  
 }  

Hope someone like it.

EDIMAX 6200n - linux command line

Since 2 months I have new cool toy - Edimax 6200n
Edimax 6200n wasn't expensive, it cost about 40$
it supports most of 3G modems, firmware is often updated, based on linux 2.6.21 (linux sources available on edimax site) but ..
No linux shell :(

After I downloaded sources and make little research i found this:
http://192.168.2.1/syscmd.asp

Thank you Edimax developers

Sybase to MSSQL: Getting stored procedure parameters

I started job in new company! This is good news.
Now i will work on Sybase - this is not so good but ok..
Version of db i will use is:
Adaptive Server Enterprise/12.5.0.2/EBF
And this is definitely not good.. its from 2001 :) .. a bit old. 

I'm MS SQL Server fan. As a database developer I really appreciate work done by MS guys in developing SQL server tools: Profiler, Managment studio..
Also some usefull objects are commonly used in my daily work.
Lack of INFORMATION_SCHEMA views was one of first thing I noticed.
And this is my today task: INFORMATION_SCHEMA.PARAMETERS

 CREATE VIEW dbo.INFORMATION_SCHEMA_PARAMETERS  
 AS  
 SELECT db_name() SPECIFIC_CATALOG,  
 user_name(sp.uid) SPECIFIC_SHEMA,  
 sp.name SPECIFIC_NAME,  
 colid ORDINAL_POSITION,  
 param.name PARAMETER_NAME,  
 CASE param.status2 WHEN 2 THEN 'OUT' ELSE 'IN' END PARAMETER_MODE,  
 t.name AS DATA_TYPE,  
 t.length LENGTH  
 FROM dbo.sysobjects AS sp  
 INNER JOIN syscolumns AS param ON (param.number = 1) AND (param.id=sp.id)  
 LEFT JOIN systypes AS t ON t.usertype = param.usertype  
 GO 

 CREATE VIEW INFORMATION_SCHEMA_TABLES  
 AS  
 SELECT  
 db_name() AS TABLE_CATALOG,  
 user_name(uid) AS TABLE_SCHEMA,  
 o.name AS TABLE_NAME,  
 CASE o.type  
 WHEN 'U' THEN 'BASE TABLE'  
 WHEN 'V' THEN 'VIEW'  
 END AS TABLE_TYPE  
 FROM sysobjects o  
 WHERE o.type IN ('U', 'V')  
 GO 

 CREATE VIEW INFORMATION_SCHEMA_COLUMNS  
 AS  
 SELECT db_name() TABLE_CATALOG,  
 user_name(o.uid) TABLE_SHEMA,  
 o.name TABLE_NAME,  
 c.name COLUMN_NAME,  
 colid ORDINAL_POSITION,  
 t.name AS DATA_TYPE,  
 t.length LENGTH,  
 CASE WHEN allownulls = 1 THEN 'YES' ELSE 'NO' END IS_NULLABLE  
 FROM dbo.sysobjects AS o  
 INNER JOIN syscolumns AS c ON (c.number = 0) AND (c.id=o.id)  
 LEFT JOIN systypes AS t ON t.usertype = c.usertype  
 GO  

As you can see my version of INFORMATION_SCHEMA_PARAMETERS is pretty simpler than the MS version, but most impotent things are in ..

COOL SP: Export data into SQL script

You prabobly can find many versions of this kind of SP. It takes table name and return SQL script which will fill table with data.
It has one extra flag which change script form

INSERT INTO .. VALUES (..)
INSERT INTO .. VALUES (..)
..

TO

INSERT INTO .. SELECT ..
UNION ALL
SELECT ..
UNION ALL
SELECT ..
..

I'm not shure but in Microsoft SQL Server 2008 this feature is buildin.
The code:

IF object_id(N'dbo.TECH_ScriptData', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_ScriptData
GO

CREATE PROCEDURE dbo.TECH_ScriptData
@tabName varchar(100), -- table to script data
@ex bit = 0 -- if 1 (smaller script, but ) = INSERT INTO .. SELECT ... UNION ALL SELECT ... UNION ALL
-- if 0 = INSERT INTO .. INSERT INTO ..
AS
BEGIN
SET NOCOUNT ON

DECLARE @s nvarchar(max)
DECLARE @sql nvarchar(max)
DECLARE @cols nvarchar(max)
DECLARE @ins nvarchar(max)

SET @cols = ''
SET @ins = ''

------------------------------------------------------
-- Genereate column list
SELECT @cols = @cols + column_name+', ',
@ins = @ins + CASE WHEN data_type IN ('uniqueidentifier','varchar', 'nvarchar', 'nchar', 'char', 'datetime')
THEN '''''''''+'+CASE WHEN data_type = 'uniqueidentifier'
THEN 'CAST('+column_name+' as varchar(50))'
ELSE column_name END+'+'''''''''
ELSE 'CAST('+column_name+' as varchar(50))'
END+' + '','' + '
FROM information_schema.columns
WHERE table_name = @tabName AND COLUMNPROPERTY( OBJECT_ID(@tabName),column_name,'IsIdentity') = 0 -- we dont wont identity columns
ORDER BY ORDINAL_POSITION
SET @s = 'INSERT INTO '+@tabName+' ('+LEFT(@cols, LEN(@cols)-2)+') '

------------------------------------------------------
-- Genereate data
SET @sql = 'SELECT '+LEFT(@ins, LEN(@ins)-9)+' FROM '+@tabName

CREATE TABLE #tt_vals (vals varchar(max))
INSERT INTO #tt_vals
EXEC (@sql)

IF @ex = 0
BEGIN
SELECT @s+' VALUES ('+vals+')' FROM #tt_vals
END ELSE
BEGIN
--SET @s = @s + char(13) + char(10)
PRINT @s
SELECT 'UNION ALL'+char(13)+char(10)+' SELECT '+vals FROM #tt_vals

--PRINT LEFT(@s, LEN(@s)-11)
END

END
GO

COOL SP: Get multiple table size with filter on table name

U can find many versions of this kind of SP in the Internet.
It will return a rowset with table name, table data sizes, sizes of indexes etc.
All (except row count)) in Kilobytes.
The reason why I wrote another version of this SP is my version do not use sp_spaceused system procedure. It also free of loops and cursors.
Its fast, it can filter table names, and by default it do not show empty tables.
I use it to check table size in KB versus row count. It really helps when U have limited DB size.


IF object_id(N'dbo.TECH_MassSpaceUsed', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_MassSpaceUsed
GO

CREATE PROCEDURE dbo.TECH_MassSpaceUsed
@filter varchar(100) = '',
@showNoEmpty bit = 1
AS
BEGIN
SET NOCOUNT ON

CREATE TABLE #tt_sizes (obId int, resPage int, usedPage int, page int, rCount int)

INSERT INTO #tt_sizes
select p.object_id, sum(a.total_pages), sum(a.used_pages),
sum(CASE
When a.type <> 1 Then a.used_pages
When p.index_id <>
Else 0
END),
sum(CASE
When (p.index_id < type =" 1)">
Else 0
END)
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id = p.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE o.type = 'U ' AND (ISNULL(@filter, '') = '' OR o.name LIKE @filter)
GROUP BY p.object_id


UPDATE s
SET resPage = it.resPage,
usedPage = it.usedPage
FROM #tt_sizes s
INNER JOIN (
SELECT it.parent_id obId, sum(a.total_pages) resPage, sum(a.used_pages) usedPage
FROM sys.internal_tables it
INNER JOIN sys.partitions p ON p.object_id = it.object_id
INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id
) it ON s.obId = it.obId


SELECT table_name = object_name(obId),
rows = convert(char(11), rCount),
reserved_in_kb = ltrim(str(resPage * 8192 / 1024.,15,0)),
data_in_kb = ltrim(str(page * 8192 / 1024.,15,0) ),
index_size_in_kb = ltrim(str((usedPage - page) * 8192 / 1024.,15,0) ),
unused_in_kb = ltrim(str((resPage - usedPage) * 8192 / 1024.,15,0))
FROM #tt_sizes
WHERE @showNoEmpty = 0 OR resPage > 0
ORDER BY resPage DESC

END
go


This SP is rewriten version of sp_spaceused sys SP. It uses same queries but i do not filter data for only one table. Thats all.

COOL SP: Get comma separated column names

This one is really useful.
There is one parameter where U set table name.
Result is in messages tab, so don't expect a resultset.


IF object_id(N'dbo.TECH_Columns', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_Columns
GO

CREATE PROCEDURE dbo.TECH_Columns
@table varchar(100) = ''
AS
BEGIN
SET NOCOUNT ON

DECLARE @s nvarchar(max)
SET @s = ''
SELECT @s = @s + column_name+', '
FROM information_schema.columns
WHERE table_name LIKE @table
ORDER BY ordinal_position

PRINT LEFT(@s, LEN(@s)-2)
END
GO

This Stored Procedure uses Information schema view: information_schema.columns to retrieve columns for table. Another way is to use system view sys.all_columns but information_schema views are more human readable.

COOL SP: Mass count - get count(*) from multiple tables with table name filtering

This is first of my COOL SP :)
It will get U list of table name with number of rows in that table.
It has a table name filtering based on T-SQL's LIKE key-word.
It uses dynamic SQL feature (sp_executeSql) to execute query concated in first select statment

Here it is:

IF object_id(N'dbo.TECH_MassCount', N'P') IS NOT NULL DROP PROCEDURE dbo.TECH_MassCount
GO

CREATE PROCEDURE dbo.TECH_MassCount
@filter varchar(100) = ''
AS
BEGIN
SET NOCOUNT ON

DECLARE @s nvarchar(max)
SET @s = ''

SELECT @s = @s + 'SELECT '''+table_name +''' as tabName, COUNT(*) as cnt FROM '+table_name+' UNION ALL '
FROM information_schema.tables
WHERE @filter = '' OR table_name LIKE @filter
ORDER BY table_name

IF ISNULL(@s, '')=''
RETURN -1
SET @s = LEFT(@s, LEN(@s)-11)
EXEC sp_executeSql @s
END
GO

How to use it:
EXEC TECH_MassCount
OR
EXEC TECH_MassCount 'tablename%' -- if U wont to filter output, and speed up query

Now few words about the SP.

New idea - Browser based MMORPG

New idea - again..