0

Fluent Interface for more readable codes

Code confusion and complexity is problem in most software project.Especially we can clearly see this case in enterprise projects.The reason for this confusion might be fast and unplanned developing , bad-designed structure and undefined architecture.But we have to know this.Codes is inherently complex.We can write code with fluent interface principle to make more readable and intelligible in this complex structure and codes in some cases.

Fluent interface ;

  • More discoverable and readable codes
  • There is business domain information in code (we are actually composing mini-DSL )
  • If we want to create a domain-specific language, we can use it. And so business analysts can comment it when they looked in codes
  • it providers a easy structure to use

In other words, It providers meaning and fluent (ordered or unordered) for code clients.We’ll see difference between traditional coding and coding with fluent interface.

Capture_13

Fluent Interface consist of two significant principles

  • Method chaining
  • More readable API (Domain Spesific Language-DSL)

Note : Method chaining and fluent interface is completely different thing.Fluent interface is a method-chaining application using to composing mini-DSL or readable APIs

Which case can we use this ?

  • Framework geliştirmesi yapılırken ; bir şirketin yazılım altyapısını oluşturduğunuzu düşünün.Bu altyapıda şirketdeki tüm uygulamaların kullanacağı bir uygulama framework ü geliştireceksiniz.Geliştirdiğini bu uygulama çatısını bir çok yazılım müh. kullnacak ve bu çatı ile yeni uygulamalar üretilecek.Bu durumda Altyapı takımları geliştirdikleri kütüphaneleri bu prensibi dikkate alarak geliştirebilirler
  • In classes that we use frequently
  • We want to hide legacy codes or third-party library’s codes with Gateway pattern

Forms of using

We can create instances of classes that use in test automation applications in this way.

Capture

Class properties defined as readonly and are not set operation.We added separate methods for set operation and this method was named more understandable with regard to business domain.Then we make itself class type as return value of these method to create method-chaining as you can see in above

Let’s take a look use

Capture_12

We can use a process when we want to carrying out any process step by step.For instance ; You are developing a test automation application and you’ll need running other platform and environments  tests in it.You’ll develop a class library for this.Here’s we should develop our codes with fluent interface in this case.In fact this library is code client and we should provider more readable classes it.


public class TestProcessorOutput
{
    public string ScreenTestOutputMessage { get; set; }
    public string StoredProcedureTestOutputMessage { get; set; }
    public string RunOutPutFileValidationTestOutputMessage { get; set; }
    public string RunInputFilevalidationTestOutputMessage { get; set; }
}

public interface ITestProcessor
{
    ITestProcessor RunScreenTests();
    ITestProcessor RunStoredProcedureTests();
    ITestProcessor RunOutPutFileValidationTests();
    ITestProcessor RunInputFilevalidationTests();
    TestProcessorOutput TakeResults();
}

public class TestProcessor
        : ITestProcessor
{
    public TestProcessorOutput TestOutput { get; private set; }
    public TestProcessor(TestProcessorOutput testOutput)
    {
        this.TestOutput = testOutput;
    }

    public ITestProcessor RunScreenTests()
    {
        // testleri çalıştırır
        this.TestOutput.ScreenTestOutputMessage = "";
        return this;
    }

    public ITestProcessor RunStoredProcedureTests()
    {
        // testleri çalıştırır
        this.TestOutput.StoredProcedureTestOutputMessage = "";
        return this;
    }

    public ITestProcessor RunOutPutFileValidationTests()
    {
        // testleri çalıştırır
        this.TestOutput.RunOutPutFileValidationTestOutputMessage = "";
        return this;
    }

    public ITestProcessor RunInputFilevalidationTests()
    {
        // testleri çalıştırır
        this.TestOutput.RunInputFilevalidationTestOutputMessage = "";
        return this;
    }

    public TestProcessorOutput TakeResults()
    {
        return this.TestOutput;
    }
}

public static class FluentTestFactory
{
    public static ITestProcessor Init()
    {
        return new TestProcessor(new TestProcessorOutput());
    }
}

// using of it
var testOutputs = FluentTestFactory
                        .Init()
                        .RunInputFilevalidationTests()
                        .RunOutPutFileValidationTests()
                        .RunScreenTests()
                        .RunStoredProcedureTests()
                        .TakeResults();

Let’s see class diagram of our sample

Untitled

Popular libraries that used fluent interface principle ;  Moq , Fluent NHibernate , FluentData , Nbuilder , Automapper , StructureMap. You’ll see similar code samples in the below

fleunt_interface_diagram1

fleunt_interface_diagram2

As a result,Fluent Interface used to create  better codebase and also it is the best way that you can write more readable codes.

You can help about topic in following links

  • http://www.martinfowler.com/bliki/FluentInterface.html
  • http://russelleast.wordpress.com/tag/fluent-interfaces/
  • http://www.youtube.com/watch?v=lVgz-DeTJhM
  • http://www.youtube.com/watch?v=m63k7UOMweA
  • http://visualstudiomagazine.com/articles/2013/11/01/when-to-build-fluent-interfaces-for-re-use-and-clarity.aspx

It contains code samples in github about this topic.I would recommend review it.

have a good coding

0

Useful sql queries and projects

In this article,i will be telling example projects and queries which may be useful in order to automatize and reduce the time spent of operational jobs on database

There are a lot operational jobs on database in data-centric applications.This operational jobs may depend number of database,size of  data, organizational structure.Let’s think about it

  • If you as firm have more software developments,you have more database servers (database) as the same time.You can be importing and exporting data for data synchronization between databases
  • You may be deploying manually your database developments
  • You may be doing text search in database objects
  • You might prepare scripts insert and update statements for data that you added in database
  • If there are open transactions on database , you must list it and then finish.
  • You may be changing hard-coded places in all stored procedures

and much more business scenario.We have spent time such operational jobs.I think that’s problem.We shouldn’t spent time for it.What can we do to solve this problems ? Let’s see

  • We can develop a console applications for specific yearly and monthly works on database
  • We can prepare scripts for daily works on database
  • We can use existing plug-in or programs.For instance ; Redgate firm’s free tools is in here
  • We can develop web or windows application that we use in all database works. You can use effectively MS Sql server SMO kütüphane

Now,We’ll review sample stored procedures , queries and projects item by item  in the following.By the way ,i written stored procedures , queries and projects(C#) with transact-sql

Scripts

1 – Search.sql

You will see select queries that you use for text searching  in database objects (stored procedure, trigger, function … ).We use system tables for searching in the below and i should review system tables (really a helpful queries)

[sql] — database object contents
SELECT * FROM sys.syscomments(NOLOCK)

— For schema names of tables
SELECT * FROM sys.schemas(NOLOCK)

— All database objects’ names
SELECT * FROM sys.all_objects(NOLOCK)

— main select query
SELECT S.name AS SP_SCHEMA,
O.name AS SP_NAME,
C.text AS SP_TEXT
FROM sys.syscomments(NOLOCK) AS C
JOIN sys.all_objects(NOLOCK) AS O
ON C.id = O.object_id
JOIN sys.schemas AS S
ON S.schema_id = O.schema_id
WHERE O.type in (‘P’,’FN’,’IF’,’FS’,’AF’,’X’,’TF’,’TR’,’PC’) AND
C.text like ‘%’ + ‘ARANACAK KELIME’ + ‘%’
[/sql]

2-  Nested_Search.sql

T-sql script for nested search.You can easily create stored procedure for this

[sql] DECLARE @text1 VARCHAR(MAX),
@text2 VARCHAR(MAX),
@text3 VARCHAR(MAX),
@text4 VARCHAR(MAX),
@text5 VARCHAR(MAX),
@dbname VARCHAR(64)

SET @dbname=’DB_NAME’
SET @text1=’TEXT1_TO_SEARCH’
SET @text2=’TEXT2_TO_SEARCH’
SET @text3=’TEXT3_TO_SEARCH’
SET @text4=’TEXT4_TO_SEARCH’
SET @text5=’TEXT5_TO_SEARCH’

DECLARE @sql VARCHAR(MAX)
SELECT @sql = ”

SELECT @sql = @sql + ‘SELECT * FROM (‘
SELECT @sql = @sql + ‘SELECT * FROM (‘
SELECT @sql = @sql + ‘SELECT * FROM (‘
SELECT @sql = @sql + ‘SELECT * FROM (‘

select @sql = @sql + ‘SELECT ”’ + @dbname + ”’ AS db, o.name,m.definition ‘
select @sql = @sql + ‘ FROM ‘+@dbname+’.sys.sql_modules m ‘
select @sql = @sql + ‘ INNER JOIN ‘+@dbname+’..sysobjects o on m.object_id=o.id’
select @sql = @sql + ‘ WHERE [definition] LIKE ”%’+@text1+’%”’

SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text2+’%”’
SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text3+’%”’
SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text4+’%”’
SELECT @sql = @sql + ‘ ) X WHERE [definition] LIKE ”%’+@text5+’%”’

–PRINT @sql
execute (@sql)
[/sql]

3- OpenTransactionListingAndKill.sql

it works more professional and team on corporation database .They can run commands or queries (stored procedures) that can be critical.These transaction may sometimes unfinished and open transactions effects directly database’s performance.You can list open transactions with the following select query on database and then you must finish these transactions with KILL command.

[sql] — Açık olan transactionları listeler

SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

— We get KILL to open transaction with SPID

–KILL SPID
[/sql]

4-  INSERT.sql

This stored procedure preparing INSERT INTO statements for data that you inserted table.it’s really a practical stored procedure when you need deploy data to different environments’ database.You will also find it in the below

[sql] GO
/****** Object: StoredProcedure [dbo].[INSERT] Script Date: ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
–drop proc [dbo].[INSERT]

CREATE procedure [dbo].[INSERT] (
@Query Varchar(MAX)
)

AS

SET nocount ON

DECLARE @WithStrINdex AS INT
DECLARE @WHEREStrINdex AS INT
DECLARE @INDExtouse AS INT

DECLARE @SchemaANDTAble VArchar(270)
DECLARE @Schema_name varchar(30)
DECLARE @Table_name varchar(240)
DECLARE @Condition Varchar(MAX)

SET @WithStrINdex=0

SELECT @WithStrINdex=CHARINDEX(‘With’,@Query )
, @WHEREStrINdex=CHARINDEX(‘WHERE’, @Query)

IF(@WithStrINdex!=0)
SELECT @INDExtouse=@WithStrINdex
ELSE
SELECT @INDExtouse=@WHEREStrINdex

SELECT @SchemaANDTAble=LEFT (@Query,@INDExtouse-1)
SELECT @SchemaANDTAble=LTRIM (RTRIM( @SchemaANDTAble))

SELECT @Schema_name= LEFT (@SchemaANDTAble, CharINdex(‘.’,@SchemaANDTAble )-1)
, @Table_name = SUBSTRING( @SchemaANDTAble , CharINdex(‘.’,@SchemaANDTAble )+1,LEN(@SchemaANDTAble) )

, @Condition=SUBSTRING(@Query,@WHEREStrINdex+6,LEN(@Query))–27+6

DECLARE @COLUMNS table (Row_number SmallINT , Column_Name VArchar(Max) )
DECLARE @CONDITIONS AS varchar(MAX)
DECLARE @Total_Rows AS SmallINT
DECLARE @Counter AS SmallINT

DECLARE @ComaCol AS varchar(MAX)
SELECT @ComaCol=”

SET @Counter=1
SET @CONDITIONS=”

INsert INTO @COLUMNS
SELECT Row_number()Over (Order by ORDINAL_POSITION ) [Count] ,Column_Name FROM INFORMATION_SCHEMA.columns WHERE Table_schema=@Schema_name
AND table_name=@Table_name
AND Column_Name NOT IN (‘FTP_PASSWORD’,’FTP_ENCRYPTED_PASSWORD’)

SELECT @Total_Rows= Count(1) FROM @COLUMNS

SELECT @Table_name= ‘[‘+@Table_name+’]’

SELECT @Schema_name='[‘+@Schema_name+’]’

While (@Counter<=@Total_Rows )
begIN
–PRINT @Counter

SELECT @ComaCol= @ComaCol+'[‘+Column_Name+’],’
FROM @COLUMNS
WHERE [Row_number]=@Counter

SELECT @CONDITIONS=@CONDITIONS+ ‘ +Case When [‘+Column_Name+’] is null then ”Null” Else ””””+

Replace( Convert(varchar(Max),[‘+Column_Name+’] ) ,””””,”” )

+”””” end+’+”’,”’

FROM @COLUMNS
WHERE [Row_number]=@Counter
AND Column_name NOT IN (‘FTP_PASSWORD’,’FTP_ENCRYPTED_PASSWORD’)

SET @Counter=@Counter+1

End

SELECT @CONDITIONS=RIGHT(@CONDITIONS,LEN(@CONDITIONS)-2)

SELECT @CONDITIONS=LEFT(@CONDITIONS,LEN(@CONDITIONS)-4)
SELECT @ComaCol= substrINg (@ComaCol,0, len(@ComaCol) )

SELECT @CONDITIONS= ”’INSERT INTO ‘+@Schema_name+’.’+@Table_name+ ‘(‘+@ComaCol+’)’ +’ Values( ‘+”” + ‘+’+@CONDITIONS

SELECT @CONDITIONS=@CONDITIONS+’+’+ ”’)”’

–PrINt(@Condition)
SELECT @CONDITIONS= ‘SELECT ‘+@CONDITIONS +’FROM ‘ +@Schema_name+’.’+@Table_name+’ With(NOLOCK) ‘ + ‘ WHERE ‘+@Condition
–prINt(@CONDITIONS)
Exec(@CONDITIONS)

/****** let’s use it ******/
EXEC [dbo].[INSERT] ‘dbo.TABLE WHERE COLUMN_NAME=”TEXT”’
[/sql]

Projeler

1 – Db.ProcedureDeployer

You as firm have a more software environment (DEVUATPREPORD).You have to change commonly used table’s name and changed as result. You have to update stored procedures,views,triggers that used this table as the same time.This is a  really bothersome work.it’s not reason doing particularly this work.I dedicated make a console application for this.I want to do automatically this and i developed this project. I used SQL Server Management Objects (SMO) for database access.You can probably find Microsoft.SqlServer.Smo.dll in the directory ‘C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies\Microsoft.SqlServer.Smo.dll

visit here for more detail :D  http://technet.microsoft.com/en-us/library/ms162557.aspxa

2 –  Db.JunkFinder

I designed this project as console application.it find temporary or junk database objects(stored procedure,table,trigger,view,function) that we genarated on database and then export it as list.If you want after testing,you can delete these objects.Developer teams especially should do this types jobs and manage process on corporation database.This work increase database performance.

You can access github and gist pages by clicking on the title.That’s all for now. Have a good coding