AnjLab.FX.SQLServer

AnjLab.FX is .NET framework which includes many various useful tools for NET developers. This is open source project, hosted on GitHub. We, AnjLab team, use FX library in the most of our NET based projects and each new project supplements the library with new functions. You can use it without any restrictions.

AnjLab.FX.SQLServer is an independent part of the project, it can be used separately. It is set of tables, T-SQL based procedures, functions and scripts for Microsoft SQL Server 2005/2008. FX objects can be installed on any existing database on SQL Server, the code includes command line setup routine. In result, in given database will be created separate scheme FX where all objects will be located. No other objects or data will be affected.

Below is a list and short descriptions of the most interesting tools from my point of view:

DDL tools. Note, some FX functions duplicates standard tools, but do the same things in different style and with many options.

• Stored procedure ScriptObject – returns DDL script for creating given programmability object (stored procedure, view, function, trigger)
  
• Stored procedure ScriptTable – returns DDL script for creating given table, including keys, constraints and indexes
  
• Stored procedure ScriptEncryptedObject – returns DDL script for creating given encrypted programmability object (stored procedure, view, function, trigger). The procedure works in DAC (dedicated admin access) mode.
  
• Stored procedure ScriptDiagram – returns script for creating given diagram
  
• Stored procedure ScriptData – scripts all data of given table as set of INSERTS. The procedure has many options.
  
• Stored procedure SearchObjectByText - searches SQL Server programmability objects (stored procedures, functions, triggers,     views) by substring in object's body.
  
• Script Indexing foreign key fields - creates non-clustered indexes for all fields which are used in foreign keys, if they are not indexed.
  

Date and time

• Function CheckLeapYear - returns true if year of given date is leap and false in other case.
  
• Function ReduceDate - for given datetime returns start or end of current hour, day, month, etc, in other words, truncates datetime.
  
• Function GetPeriods - returns set of date and time periods (hours, days, months, etc) between given start and end dates.
  

Table valued tools

• Function GetEmptyRowSet - returns rowset with defined number of record.
  
• Function GetStringAsRowSet - returns rowset from string with delimiters
  

Other

• Function CalculateYield - returns yield (in %%), based on time period (taking into account leap years) and revenue.
  
• Function ConvertVarbinaryToVarcharHex - returns hexadecimal representation of binary data, using chars [0-0a-f].
  
• Function Div – just divides one number by another, but handles dividing by zero and converts numbers to float
  

Tables

• Countries - world countries dictionary (in Russian and English) according to ISO 3166 and Russian ОКСМ standards
  
• Currencies - world currencies dictionary according to Russian ОКВ standard (Russian language only)
  
• MeasurementUnits - Russian classification of units of measurement according to Russian ОКЕИ standard (Russian language only)
  
• BusinessLegalForms - Russian Classification of Organizational and Legal Forms according to Russian ОКПФ standard (Russian language only)
  
• IdentityCardTypes - Russian personal ID types dictionary (Russian language only)
  

Note, some tools are based on code created by other authors, but all copyrights and information are in place. All scripts have XML style headers with common information.