Build schema + Build commands sample

gistfile1.cs

using System;
using System.Text;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Linq;
using RemObjects.DataAbstract.Server;
using RemObjects.DataAbstract.Schema;

namespace BuildService
{
    class Program
    {
        const string TEMPLATE = @"
using System;
using System.Data;

using RemObjects.SDK;
using RemObjects.SDK.Types;
using RemObjects.SDK.Server;
using RemObjects.DataAbstract;
using RemObjects.DataAbstract.Server;

namespace BestSellerCloud
{
    public partial class DbApi
    {
        public LocalCommand newCommand()
        {
            LocalCommand cmd = new LocalCommand();
            
            cmd.ServiceInstance = this.srv;

            return cmd;
        }
	##METHODS
	}
}
";
        static void Main(String[] args)
        {
            // Needed in any Data Abstract application
            // Load configuration
            if (!RemObjects.DataAbstract.Server.Configuration.Loaded)
                RemObjects.DataAbstract.Server.Configuration.Load();

            String connectionString = @"DEVART_POSTGRESQL.NET?Server=intersec;Database=BestSeller;UserID=postgres;Password=pinky;";
            String schemaFileName = System.IO.Path.Combine(System.IO.Directory.GetParent(".").FullName, "Data", "BestSeller.daSchema");

            ServiceSchema lSchema = GenerateSchema(connectionString);

            lSchema.Serialization.SaveToFile(schemaFileName);
        }

        static ServiceSchema GenerateSchema(String connectionString)
        {
            ServiceSchema lSchema = new ServiceSchema();
            StringBuilder b = new StringBuilder();
            Console.WriteLine("Start..");
            // 0. Determine connection type
            String connectionType = "PostgreSQL";//connectionString.Substring(0, connectionString.IndexOf('?'));
           
            // 1. Connect to the database
            using (BaseConnection connection = new BaseConnection(connectionType, connectionString, true))
            {
                connection.NewCommand("SELECT public.set_schema('template')", System.Data.CommandType.Text).ExecuteNonQuery();

                // 2. Get Database Table names
                StringCollection tables = new StringCollection();
                connection.GetTableNames(ref tables);

                // 3. Get Database View names
                StringCollection views = new StringCollection();
                connection.GetViewNames(ref views);

                // 4. Combine table and name collections
                List<String> entities = tables.Cast<String>().ToList<String>();
                entities.AddRange(views.Cast<String>());

                // 5. Create Schema Data Tables
                for (Int32 i = 0; i < entities.Count; i++)
                {
                    String entityName = entities[i];

                    if ((entityName.IndexOf("template") > -1) || (entityName.IndexOf("public") > -1))
                    {
                        entityName = entityName.Split('.')[1];
                    }
                    else
                    {
                        continue;
                    }

                    Console.WriteLine(entityName);

                    SchemaDataTable schemaTable = new SchemaDataTable { Name = entityName, IsPublic = true };

                    // Load table fields info
                    SchemaFieldCollection tableFields = new SchemaFieldCollection();
                    connection.GetTableFields(entityName, ref tableFields, true);

                    // Add all fields to the Schema Table definition
                    foreach (SchemaField field in tableFields)
                        schemaTable.Fields.Add(field);

                    // Add AutoSQL statement
                    SchemaSQLStatement statement = new SchemaSQLStatement { ConnectionType = connectionType, StatementType = StatementType.AutoSQL, TargetTable = entityName };
                    foreach (SchemaField field in tableFields)
                        statement.ColumnMappings.Add(new SchemaColumnMapping(field.Name, field.Name, field.Name));

                    schemaTable.Statements.Add(statement);

                    // Add created Schema Data Table to the Schema
                    lSchema.DataTables.Add(schemaTable);
                }

                // 6. Add table relationships
                SchemaRelationshipCollection relationships = connection.GetForeignKeys();
                foreach (SchemaRelationship relationship in relationships)
                {
                    String relationshipName;

                    if ((relationship.DetailDataTableName.IndexOf("template") > -1) || (relationship.DetailDataTableName.IndexOf("public") > -1))
                    {
                        relationshipName = String.Format("FK_{0}_{1}", relationship.DetailDataTableName.Split('.')[1], relationship.MasterDataTableName.Split('.')[1]);
                    }
                    else
                    {
                        continue;
                    }

                    Console.WriteLine(relationshipName);
                    SchemaRelationship schemaRelationship;
                    if (lSchema.Relationships.FindItem(relationshipName, out schemaRelationship))
                    {
                        schemaRelationship.MasterFields = schemaRelationship.MasterFields + "," + relationship.MasterFields;
                        schemaRelationship.DetailFields = schemaRelationship.DetailFields + "," + relationship.DetailFields;
                    }
                    else
                    {
                        relationship.Name = relationshipName;
                        lSchema.Relationships.Add(relationship);
                    }
                }

                // 7. Add StoredProcedures (as Commands)
                StringCollection storedProcedures = new StringCollection();
                connection.GetStoredProcedureNames(ref storedProcedures);

                foreach (String storedProcedure in storedProcedures)
                {
                    String procedure;

                    if ((storedProcedure.IndexOf("template") > -1) || (storedProcedure.IndexOf("public") > -1))
                    {
                        procedure = storedProcedure.Split('.')[1];
                    }
                    else
                    {
                        continue;
                    }

                    SchemaCommand schemaCommand = new SchemaCommand { Name = procedure, IsPublic = true };

                    Console.WriteLine(procedure);
                    
                    SchemaParameterCollection commandParameters = new SchemaParameterCollection();
                    connection.GetCommandParams(storedProcedure, ref commandParameters, true);

                    foreach (SchemaParameter parameter in commandParameters)
                        schemaCommand.Parameters.Add(parameter);

                    SchemaSQLStatement statement = new SchemaSQLStatement { ConnectionType = connectionType, StatementType = StatementType.StoredProcedure, SQL = storedProcedure };
                    schemaCommand.Statements.Add(statement);

                    if (procedure == "login_user")
                    {
                        schemaCommand.Parameters.Clear();
                        schemaCommand.Statements.Clear();

                        schemaCommand.Parameters.Add(new SchemaParameter("user_name", DataType.WideMemo, RemObjects.SDK.ParameterDirection.In,-1,BlobType.Unknown));
                        schemaCommand.Parameters.Add(new SchemaParameter("pass", DataType.WideMemo, RemObjects.SDK.ParameterDirection.In, -1, BlobType.Unknown));
                        schemaCommand.Parameters.Add(new SchemaParameter("client_version", DataType.WideMemo, RemObjects.SDK.ParameterDirection.In, -1, BlobType.Unknown));

                        statement = new SchemaSQLStatement { ConnectionType = connectionType, StatementType = StatementType.SQL, SQL = "SELECT * FROM public.login_user(:user_name, :pass, :client_version)" };

                        schemaCommand.Statements.Add(statement);
                    }
                    lSchema.Commands.Add(schemaCommand);
                    b.Append(buildcommand(schemaCommand));
                };
            };

            //Console.ReadLine();

            String codeFileName = System.IO.Path.Combine(System.IO.Directory.GetParent(".").FullName, "src", "Commands.cs");

            System.IO.File.WriteAllText(codeFileName,TEMPLATE.Replace("##METHODS",b.ToString()));

            return lSchema;
        }
        static string buildcommand(SchemaCommand cmd)
        {
            StringBuilder b = new StringBuilder();
            string result = "void";
            string name = cmd.Name;
            string inparams ="";
            string header = " \tpublic {0} {1} ({2})";
            int totalparams = 0;
            int count=0;

            List<string> col = new List<string>();

            int pos = cmd.Parameters.IndexOfName("returns");

            if (pos > -1)
            {
                result = mapdatatype(cmd.Parameters[pos].DataType);
                totalparams = cmd.Parameters.Count-1;
            } else {
                totalparams =cmd.Parameters.Count;
            }

            foreach (SchemaParameter p in cmd.Parameters)
            {
                if (p.Direction == RemObjects.SDK.ParameterDirection.In)
                {
                    col.Add(string.Format("{0} {1}", mapdatatype(p.DataType), p.Name));
                }
            }
            inparams = string.Join(", ",col.ToArray());

            b.AppendLine(string.Format(header, result, name, inparams));
            b.AppendLine("\t{");

            b.AppendLine("\t\tLocalCommand cmd = this.newCommand();");

            if (totalparams > 0)
            {
                b.AppendLine(string.Format("\t\tDataParameter[] inparams = new DataParameter[{0}];", totalparams));
            }
            else
            {
                b.AppendLine("\t\tDataParameter[] inparams = null;");
            }

            foreach (SchemaParameter p in cmd.Parameters)
            {
                if (p.Direction == RemObjects.SDK.ParameterDirection.In)
                {
                    b.AppendLine(string.Format("\t\tinparams[{0}] = new DataParameter(\"{1}\", {1});",count, p.Name));
                    count++;
                }
            }

            if (pos > -1)
            {
                b.AppendLine();
                b.AppendLine("\t\tDataParameter[] outparams = new DataParameter[1];");
                b.AppendLine("\t\toutparams[0] = new DataParameter(\"returns\",null);");
                b.AppendLine();
                b.AppendLine(string.Format("\t\tcmd.Execute(\"{0}\", inparams, out outparams);",name));            
            }
            else
            {
                b.AppendLine();
                b.AppendLine(string.Format("\t\tcmd.Execute(\"{0}\", inparams);", name));
            }

            if (result != "void")
            {
                b.AppendLine();
                b.AppendLine(string.Format("\t\treturn ({0})outparams[0].Value;",result));
            }

            b.AppendLine("\t}");
            b.AppendLine();

            return b.ToString();
        }

        static string mapdatatype(DataType t)
        {
            string result=null;

            switch (t)
            {
                case DataType.String: result = "string"; break;
                case DataType.WideMemo: result = "string"; break;
                case DataType.WideString: result = "string"; break;
                case DataType.AutoInc: result = "int"; break;
                case DataType.Integer: result = "int"; break;
                case DataType.LargeAutoInc: result = "int"; break;
                case DataType.SmallInt: result = "int"; break;
                case DataType.Boolean: result = "bool"; break;
                case DataType.DateTime: result = "DateTime"; break;
                case DataType.Decimal: result = "Decimal"; break;
            }
            
            return result;
        }
    }
}