Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » Data Access » Downloads
 
Add your own
alternative version

Abstract Data Access Layer Design

, 5 Sep 2009 CPOL
The present document tries to describe the architecture of a specific layer of access to data for relational databases. This document tries to present/display a form to automate tasks of access to data.
demoproject.zip
DemoProject
Test
(ClassDiagram1).cd
bin
Debug
Test.exe
Test.vshost.exe
Test.vshost.exe.manifest
XPeriment.Persistence.dll
Properties
Settings.settings
Test.csproj.user
XPeriment.Architect
bin
Debug
BizAgi.Architect.vshost.exe
BizAgi.Architect.vshost.exe.manifest
Experiment.Architect.exe
Experiment.Architect.vshost.exe
Experiment.Architect.vshost.exe.manifest
Properties
Settings.settings
XPeriment.Management.suo
XPeriment.Persistence
(ClassDiagram).cd
bin
Debug
XPeriment.Persistence.dll
DataAcessTasks
(ClassDiagram).cd
(ClassDiagram1).cd
SqlServer
PersistentObjects
(ClassDiagram).cd
Attributes
Properties
Settings.settings
XPeriment.Persistence.csproj.user
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;

namespace XPeriment.Architect
{
    public partial class Main : Form
    {
        private Dictionary<string, List<FieldData>> classes = new Dictionary<string, List<FieldData>>();
        private List<string> keywords = new List<string>();
        private DataTable fktable = null;
        public Main()
        {
            InitializeComponent();
        }

        private void SetupKeyWords()
        {
            keywords.Clear();
            keywords.Add("time");
            keywords.Add("class");
            keywords.Add("wf");
            keywords.Add("form");
            keywords.Add("job");
            keywords.Add("task");
            keywords.Add("sch");
            keywords.Add("schedul");
            keywords.Add("schema");
            keywords.Add("working");
            keywords.Add("definition");
            keywords.Add("dependenc");
            keywords.Add("user");
            keywords.Add("data");
            keywords.Add("access");
            keywords.Add("log");
            keywords.Add("rule");
            keywords.Add("render");
            keywords.Add("state");
            keywords.Add("type");
            keywords.Add("work");
            keywords.Add("class");
            keywords.Add("skill");
            keywords.Add("role");
            keywords.Add("property");
            keywords.Add("position");
            keywords.Add("attribute");
            keywords.Add("visual");
            keywords.Add("XPeriment");
            keywords.Add("info");
            keywords.Add("parameter");
            keywords.Add("value");
            keywords.Add("range");
            keywords.Add("list");
            keywords.Add("item");
            keywords.Add("mapping");
            keywords.Add("policy");
            keywords.Add("subproc");
            keywords.Add("interface");
            keywords.Add("system");
            keywords.Add("event");
            keywords.Add("query");
            keywords.Add("exit");
            keywords.Add("counter");
            keywords.Add("precondition");
            keywords.Add("condition");
            keywords.Add("assig");
            keywords.Add("entity");
            keywords.Add("meta");
            keywords.Add("group");
            keywords.Add("language");
            keywords.Add("config");
            keywords.Add("obj");
            keywords.Add("message");
            keywords.Add("template");
            keywords.Add("binding");
            keywords.Add("letter");
            keywords.Add("stack");
            keywords.Add("family");
            keywords.Add("action");
            keywords.Add("behavior");
            keywords.Add("number");
            keywords.Add("area");
            keywords.Add("fact");
            keywords.Add("phase");
            keywords.Add("diagram");
            keywords.Add("model");
            keywords.Add("process");
            keywords.Add("auth");
            keywords.Add("settings");
            keywords.Add("transition");
            keywords.Add("relation");
            keywords.Add("environment");
            keywords.Add("param");
            keywords.Add("cache");
            keywords.Add("case");
            keywords.Add("abort");
            keywords.Add("reason");
            keywords.Add("key");
            keywords.Add("evaluation");
            keywords.Add("measure");
            keywords.Add("context");
            keywords.Add("report");
            keywords.Add("kpi");
            keywords.Add("manager");
            keywords.Add("sequence");
            keywords.Add("assig");
            keywords.Add("component");
            keywords.Add("library");
            keywords.Add("business");
            keywords.Add("folder");
            keywords.Add("resource");
            keywords.Add("file");
            keywords.Add("theme");
            keywords.Add("context");
            keywords.Add("macro");
            keywords.Add("wizard");
            keywords.Add("error");
            keywords.Add("table");
            keywords.Add("filter");
            keywords.Add("dimension");
            keywords.Add("cube");
            keywords.Add("upload");
            keywords.Add("flag");
            keywords.Add("code"); 
            keywords.Add("provider");
            keywords.Add("param");
            keywords.Add("package");
            keywords.Add("deploy");
            keywords.Add("deliver");
            keywords.Add("elapse");
            keywords.Add("lapse");
            keywords.Add("mode");
            keywords.Add("send");
            keywords.Add("recur");
            keywords.Add("principle");
            keywords.Add("alloc");
            keywords.Add("method");
            keywords.Add("option");
            keywords.Add("note");
            keywords.Add("close");
            keywords.Add("baseline");
            keywords.Add("merge");
            keywords.Add("rebase");
            keywords.Add("instance");
            keywords.Add("version");
            keywords.Add("url");
            keywords.Add("scope");
            keywords.Add("zone");
            keywords.Add("trace");
            keywords.Add("activity");
            keywords.Add("path");
            keywords.Add("step");
            keywords.Add("running");
            keywords.Add("frequency");
            keywords.Add("field");
            keywords.Add("request");
            keywords.Add("response");
            keywords.Add("execution");
            keywords.Add("test");
            keywords.Add("return");
            keywords.Add("dply");
            keywords.Add("detail");
            keywords.Add("master");
            keywords.Add("recipient");
            keywords.Add("document");
            keywords.Add("alarm");

        }

        private void button2_Click(object sender, EventArgs e)
        {
            BuildCode();
        }

        private DataTable GetTablesTable()
        {
            DataTable retval = new DataTable("Results");
            string  query = "SELECT so.Name AS [Table] , sc.name AS [Column], st.name AS [Type] ";
                    query += "FROM SYSOBJECTS so ";
                    query += "INNER JOIN SYSCOLUMNS sc on so.id = sc.id ";
                    query += "INNER JOIN SYSTYPES st ON st.xtype = sc.xtype ";
                    query += "WHERE so.xtype = 'U'";
            SqlDataAdapter adapter = new SqlDataAdapter(query, DatabaseBox.Text);
            adapter.Fill(retval);
            adapter.SelectCommand.Connection.Close();
            adapter.Dispose();
            return retval;
        }

        private string GetDataType(string text)
        {
            switch(text.ToLower())
	        {
                case "varchar":
                    return "string";
                case "int":
                    return "int";
                case "float":
                    return "float";
                case "tinyint":
                    return "int";
                case "uniqueidentifier":
                    return "Guid";
                case "bit":
                    return "bool";
                case "image":
                    return "byte[]";
                case "datetime":
                    return "DateTime";
                case "text":
                    return "string";
                case "numeric":
                    return "decimal";
                case "nvarchar":
                    return "string";
                case "sysname":
                    return "string";
                case "char":
                    return "char[]";
                case "smallint":
                    return "Int16";
                case "smalldatetime":
                    return "DateTime"; 
                case "money":
                    return "decimal";
            }
            throw new Exception("El tipo de datos " + text + " no se encontró");
        }

        private void GroupRow(DataRow row)
        {
            FieldData fielddata = new FieldData(row["Column"].ToString(), GetDataType(row["Type"].ToString()));
            if (!classes.ContainsKey(row["Table"].ToString()))
                classes.Add(row["Table"].ToString(), new List<FieldData>());
            classes[row["Table"].ToString()].Add(fielddata);
        }

        private void BuildCode()
        {
            SetupKeyWords();
            DataTable table = GetTablesTable();
            fktable = GetForaignKeysTable();
            foreach (DataRow row in table.Rows)
                GroupRow(row);
            AddSubObjects();
            AddCollectionsToClasses();
            GenerateClasses();
        }

        private void AddSubObjects()
        {
            foreach (string key in classes.Keys)
            {
                List<FieldData> toadd = new List<FieldData>();
                foreach (FieldData fd in classes[key])
                {
                    string targetTable = GetTargetTable(key, fd.FieldName);
                    if (targetTable != "")
                    {
                        FieldData fkfield = new FieldData("Obj"+PascalCase(fd.FieldName), PascalCase(targetTable));
                        toadd.Add(fkfield);
                    }
                }
                foreach (FieldData fdd in toadd)
                    classes[key].Add(fdd);
            }
        }

        private string GetTargetTable(string key, string p)
        {
            DataRow[] rows = fktable.Select("FK_Table = '" + key + "' AND FK_Column = '" + p + "'");
            if (rows.Length == 0)
                return "";
            return rows[0]["PK_Table"].ToString();
        }



        private void AddCollectionsToClasses()
        {
            foreach (string key in classes.Keys)
            {
                List<FieldData> toadd = new List<FieldData>();
                List<string> childtables = GetChildTables(key);
                foreach (string ct in childtables)
                {
                    FieldData fd = new FieldData(PascalCase(ct) + "List", "List<" + PascalCase(ct) + ">");
                    fd.IsList = true;
                    classes[key].Add(fd);
                }
            }
        }

        private List<string> GetChildTables(string key)
        {
            List<string> retval = new List<string>();
            DataRow[] rows = fktable.Select("PK_Table = '" + key + "' AND PK_Column = FK_Column");
            foreach (DataRow row in rows)
                retval.Add(row["FK_Table"].ToString());
            return retval;
        }

        private DataTable GetForaignKeysTable()
        {
            DataTable retval = new DataTable("Results");
            string query = "SELECT "+ 
                "FK_Table  = FK.TABLE_NAME, "+
                "FK_Column = CU.COLUMN_NAME, "+
                "PK_Table  = PK.TABLE_NAME, "+
                "PK_Column = PT.COLUMN_NAME, "+
                "Constraint_Name = C.CONSTRAINT_NAME "+
            "FROM "+
                "INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C "+
                "INNER JOIN "+
                "INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK "+
                "    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME "+
                "INNER JOIN "+
                "INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK "+
                "    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME "+
                "INNER JOIN "+
                "INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU "+
                "    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME "+
                "INNER JOIN"+ 
                "( "+
                "    SELECT "+
                "        i1.TABLE_NAME, i2.COLUMN_NAME "+
                "    FROM "+
                "        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1 "+
                "        INNER JOIN "+
                "        INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 " +
                "        ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME "+
                "        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY' "+
                ") PT " +
                "ON PT.TABLE_NAME = PK.TABLE_NAME "+
            "ORDER BY " +  
            "    1,2,3,4";
            SqlDataAdapter adapter = new SqlDataAdapter(query, DatabaseBox.Text);
            adapter.Fill(retval);
            adapter.Dispose();
            return retval;
        }

        private string SinglePascalCase(string text)
        {
            string retval = text;
            if(retval.StartsWith("BA"))
                retval = retval.Substring(2, retval.Length - 2);
            retval = retval.ToLower();
            char firstchar = char.ToUpper(retval[0]);
            retval = retval.Substring(1, retval.Length - 1);
            retval = firstchar + retval;
            return retval;
        }

        private string PascalCase(string text)
        {
            string retval = SinglePascalCase(text);
            foreach (string toreplace in keywords)
                retval = retval.Replace(toreplace, SinglePascalCase(toreplace));
            return retval;
        }

        private void GenerateClasses()
        {
            progressBar1.Maximum = classes.Keys.Count;
            progressBar1.Value = 0;
            foreach (string classname in classes.Keys)
            {
                string code = "";
                code += "\r\nusing System;";
                code += "\r\nusing System.Collections.Generic;";
                code += "\r\nusing System.Data;";
                code += "\r\nusing XPeriment.Persistence.PersistentObjects;";
                code += "\r\nnamespace " + NamespaceBox.Text;
                code += "\r\n{";
                code += "\r\n    [MapTo(\""+ classname +"\")]";
                code += "\r\n    public class " + PascalCase(classname) + ":PersistentObject";
                code += "\r\n    {\r\n";
                foreach (FieldData fd in classes[classname])
                {
                    string nullstr = "? ";
                    if (fd.DataType == "string" || fd.IsList || fd.DataType.Contains("[]"))
                        nullstr = " ";
                    code += "\r\n        private " + fd.DataType + nullstr + fd.FieldName.ToLower() + ";";
                }
                code += "\r\n\r\n";
                foreach (FieldData fd in classes[classname])
                {
                    string nullstr = "? ";
                    if (fd.DataType == "string" || fd.IsList || fd.DataType.Contains("[]"))
                        nullstr = " ";

                    code += "\r\n        [MapTo(\"" + fd.FieldName + "\")]";
                    code += "\r\n        public " + fd.DataType + nullstr + PascalCase(fd.FieldName);
                    code += "\r\n        {";
                    code += "\r\n            get";
                    code += "\r\n            {";
                    code += "\r\n                return " + fd.FieldName.ToLower() + ";" ;
                    code += "\r\n            }";
                    code += "\r\n            set";
                    code += "\r\n            {";
                    code += "\r\n                "+ fd.FieldName.ToLower() + " = value;" ;
                    code += "\r\n            }";
                    code += "\r\n        }";
                    code += "\r\n";
                }
                code += "    \r\n}";
                code += "\r\n}";

                StreamWriter writer = new StreamWriter(Path.Combine(DirectoryBox.Text, PascalCase(classname) + ".cs"), true);
                writer.Write(code);
                writer.Close();
                progressBar1.Value++;
            }
            MessageBox.Show("Process Completed!", "Condirmation", MessageBoxButtons.OK, MessageBoxIcon.Information);
        }

    }
}

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Anwar Ibáñez O'Kamell
Systems Engineer
Colombia Colombia
http://www.construirsoftware.blogspot.com/

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.141223.1 | Last Updated 5 Sep 2009
Article Copyright 2009 by Anwar Ibáñez O'Kamell
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid