Overview

Iota is a C# code generator that creates classes and methods from SQL Server stored procedures or queries.

Features and benefits

• Generate classes based on columns selected by a procedure
• Generate classes based on parameters used by a procedure
• Generate static instances for tables with rows that rarely change (i.e. CustomerStatus)
• Generate methods that call procedures
• Generate synchronous or asynchronous methods
• Handle common input or output parameters (i.e. @ID_CURRENT_USER, @MODIFIED_ON, @ERROR_MESSAGE etc.)
• Generated code works on .Net Framework and .Net Core
• Very fast

Usage

Use it in three simple steps:

1. Create an instance of Iota.SqlServer.CSharp.ProgramGenerator
2. Add the stored procedures and what code you want to generate from them
3. Write the generated code

Examples

Create the program generator

using Iota.Metadata; using Iota.SqlServer.CSharp; ... ProgramGenerator generator = new ProgramGenerator(sqlServerConnectionString) { DefaultNamespace = "MyProject.Entities", // Set namespace for generated classes DefaultAsyncMode = AsyncMode.Asynchronous, // Choose from synchronous or synchronous methods DefaultTimeout = 120, // Set default timeout (in seconds) for SQL commands AdapterNamespace = "MyProject.Services", // Set namespace for generated adapter AdapterName = "DataAdapter", // Set class name for generated adapter NullableReferenceTypes = false, // Set to true to generate nullable reference types SqlClientPackage = SqlClientPackage.Microsoft, // Choose the SQL client package to use };

Create types from stored procedures

Generate class Customer from columns selected by the procedure and class CustomerFilter from procedure's parameters:

generator .WithProcedure("dbo", "usp_Customers_Select") .AddType("Customer") .AddTypeFromParameters("CustomerFilter");

Handle common parameters

When most of the procedures have the same parameter (i.e. a parameter called @ID_CURRENT_USER that holds the logged in user id) use this syntax to automatically handle the parameter(s) for all procedure calls:

generator.AddDefaultParameterService( new ParameterService { ParameterNames = new string[] { "IdCurrentUser" }, Initializer = true, // Initialize the parameter before calling the procedure HideParameters = true // Hide the parameter when generating classes and methods });

After this, you can write a method to initialize the current user for all commands:

namespace MyProject.Services { partial class DataAdapter { void InitializeIdCurrentUser(ref int? idCurrentUser) { // TODO: Add your logic here } } }

Map table types

Create a class for a table type and map all appearances of the table type to the new class:

generator.MapTableType("dbo", "TblTypCustomers", "TblCustomer");

Map table types with a single column

Map a table type that contains a single column to a basic C# type:

generator.MapTableType("dbo", "TblTypIntKeys");

Create list methods

Generate method CustomersSelect that receives a CustomerFilter, calls the stored procedure and returns a list of Customer instances:

generator .WithProcedure("dbo", "usp_Customers_Select") .AddList("Customer", "CustomersSelect", "CustomerFilter");

Create single object methods

Generate method CustomersGet that calls the stored procedure and returns a single Customer instance:

generator .WithProcedure("dbo", "usp_Customers_Get") .AddFirstObject("Customer", "CustomersGet");

Create non query methods

Generate method CustomersSave that receives a Customer and then calls the stored procedure passing all properties as parameters:

generator .WithProcedure("dbo", "usp_Customers_Save") .AddNonQuery("CustomersSave", "Customer");

Generate method CustomersDelete that calls the stored procedure:

generator .WithProcedure("dbo", "usp_Customers_Delete") .AddNonQuery("CustomersDelete");

Create scalar methods

Generate method CustomersGetRevenue that calls the stored procedure and returns a single decimal value:

generator .WithProcedure("dbo", "usp_Customers_GetRevenue") .AddScalar("System.Decimal", "CustomersGetRevenue");

Generate method CustomerSelectContactNames that calls the stored procedure and returns a list of string values:

generator .WithProcedure("dbo", "usp_Customers_SelectContactNames") .AddScalarList("System.String", "CustomerSelectContactNames");

Create static instances

Generate static instances of type CustomerStatus from rows returned by a stored procedure, and place them in an array named Instances

generator .WithProcedure("dbo", "usp_CustomerStatus_Select") .AddType("CustomerStatus") .AddInstances("CustomerStatus", "Name", "Instances");

Create constants

Generate constant fields in type CustomerStatus from rows returned by a stored procedure. Each constant will have the same name as the status and the same value equal to the status id.

generator .WithProcedure("dbo", "usp_CustomerStatus_Select") .AddInstances("CustomerStatus", "Name", "IdStatus");

Write the generated code

Use this sample to write all generated code to a single file:

generator.WriteAll("Generated.cs");

Customization

• Use the Write method overrides to write the generated code to multiple files.
• Use the optional parameters for the Add methods to customize individual stored procedure calls.