Project Description
Generates c# classes from MS-SQL database.Each class has it's Constructors,CRUD Methods and methods fetches data according foreign keys.

Requirements
.NET Framework 3.5 with SQL Server Management Objects (SMO)

Features
#GUI with profiling support database's preferences
#Generates classes from mssql database

Roadmap

Sample Database Scheme
db_scheme.jpg

Generating Classes
1111.jpg

Output
NewsCategory.cs
using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace Foo.Data
{
    public partial class NewsCategory
    {
        public int Id { get; set; }
        public string Title { get; set; }

        string _InsertQuery = "INSERT INTO NewsCategory (Title) VALUES (@Title);SELECT @@IDENTITY;";
        string _UpdateQuery = "UPDATE NewsCategory SET Title=@Title WHERE Id=@Id";
        string _DeleteQuery = "DELETE NewsCategory WHERE Id=@Id";

        public NewsCategory()
        {
            Id = 0;
            Title = string.Empty;
        }
        public NewsCategory(int pId)
        {
            SqlCommand scLoad = new SqlCommand("SELECT TOP 1 * FROM NewsCategory WHERE Id=@Id");
            scLoad.Parameters.AddWithValue("@Id", pId);
            Fill(DBLayer.ExecuteDataReader(scLoad));
        }

        void Fill(SqlDataReader pRecord)
        {
            if (pRecord.Read())
            {
                Id = pRecord["Id"].ToInt32();
                Title = pRecord["Title"].ToString();
            }
            pRecord.Close();
        }
        public int AddNew()
        {
            SqlCommand scAddNew = new SqlCommand(_InsertQuery);
            scAddNew.Parameters.AddWithValue("@Title", Title);
            return Id = DBLayer.ExecuteScalar(scAddNew).ToInt32();
        }
        public void Update()
        {
            SqlCommand scUpdate = new SqlCommand(_UpdateQuery);
            scUpdate.Parameters.AddWithValue("@Id", Id);
            scUpdate.Parameters.AddWithValue("@Title", Title);
            DBLayer.ExecuteNonQuery(scUpdate);
        }
        public void Delete()
        {
            SqlCommand scDelete = new SqlCommand(_DeleteQuery);
            scDelete.Parameters.AddWithValue("@Id", Id);
            DBLayer.ExecuteNonQuery(scDelete);
        }


        public List<News> ListFK_News_NewsCategory()
        {
            List<News> list = new List<News>();
            DataTable dtNews = DBLayer.ExecuteDataTable("SELECT * FROM News WHERE NewsCategoryNo = " + Id.ToString() + "");
            foreach (DataRow dr in dtNews.Rows)
            {
                News record = new News();
                record.Id = dr["Id"].ToInt32();
                record.Title = dr["Title"].ToString();
                record.Description = dr["Description"].ToString();
                record.Text = dr["Text"].ToString();
                record.NewsCategoryNo = dr["NewsCategoryNo"].ToInt32();
                list.Add(record);
            }
            return list;
        }

    }
    public partial class NewsCategoryManager
    {
        public static List<NewsCategory> GetList(string pQuery)
        {
            SqlCommand pCommand = new SqlCommand(pQuery);
            return GetList(pCommand);
        }
        public static List<NewsCategory> GetList(SqlCommand pCommand)
        {
            List<NewsCategory> list = new List<NewsCategory>();
            DataTable dtNewsCategory = DBLayer.ExecuteDataTable(pCommand);
            foreach (DataRow dr in dtNewsCategory.Rows)
            {
                NewsCategory record = new NewsCategory();
                record.Id = dr["Id"].ToInt32();
                record.Title = dr["Title"].ToString();
                list.Add(record);
            }
            return list;
        }
        public static List<NewsCategory> Liste()
        {
            return GetList(new SqlCommand("SELECT * FROM NewsCategory"));
        }

        public static NewsCategory GetSingle(string pQuery)
        {
            SqlCommand pCommand = new SqlCommand(pQuery);
            return GetSingle(pCommand);
        }
        public static NewsCategory GetSingle(SqlCommand pCommand)
        {
            DataRow drRecord = DBLayer.ExecuteDataRow(pCommand);
            if (drRecord != null)
            {
                NewsCategory record = new NewsCategory();
                record.Id = drRecord["Id"].ToInt32();
                record.Title = drRecord["Title"].ToString();
                return record;
            }
            else
            {
                return null;
            }
        }

        public static DataTable GetDataTable()
        {
            return DBLayer.ExecuteDataTable("SELECT * FROM NewsCategory");
        }

        public static int AddNewNewsCategory(NewsCategory pNewsCategory)
        {
            if (pNewsCategory != null)
            {
                NewsCategory pObject = new NewsCategory(pNewsCategory.Id);
                return pObject.AddNew();
            }
            return 0;
        }

        public static void UpdateNewsCategory(NewsCategory pNewsCategory)
        {
            if (pNewsCategory != null)
            {
                pNewsCategory.Update();
            }
        }

        public static void DeleteNewsCategory(NewsCategory pNewsCategory)
        {
            if (pNewsCategory != null)
            {
                pNewsCategory.Delete();
            }
        }

    }
}

News.cs
using System;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
namespace Foo.Data
{
    public partial class News
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string Text { get; set; }
        public int NewsCategoryNo { get; set; }

        string _InsertQuery = "INSERT INTO News (Title,Description,Text,NewsCategoryNo) VALUES (@Title,@Description,@Text,@NewsCategoryNo);SELECT @@IDENTITY;";
        string _UpdateQuery = "UPDATE News SET Title=@Title,Description=@Description,Text=@Text,NewsCategoryNo=@NewsCategoryNo WHERE Id=@Id";
        string _DeleteQuery = "DELETE News WHERE Id=@Id";

        public News()
        {
            Id = 0;
            Title = string.Empty;
            Description = string.Empty;
            Text = string.Empty;
            NewsCategoryNo = 0;
        }
        public News(int pId)
        {
            SqlCommand scLoad = new SqlCommand("SELECT TOP 1 * FROM News WHERE Id=@Id");
            scLoad.Parameters.AddWithValue("@Id", pId);
            Fill(DBLayer.ExecuteDataReader(scLoad));
        }

        void Fill(SqlDataReader pRecord)
        {
            if (pRecord.Read())
            {
                Id = pRecord["Id"].ToInt32();
                Title = pRecord["Title"].ToString();
                Description = pRecord["Description"].ToString();
                Text = pRecord["Text"].ToString();
                NewsCategoryNo = pRecord["NewsCategoryNo"].ToInt32();
            }
            pRecord.Close();
        }
        public int AddNew()
        {
            SqlCommand scAddNew = new SqlCommand(_InsertQuery);
            scAddNew.Parameters.AddWithValue("@Title", Title);
            scAddNew.Parameters.AddWithValue("@Description", Description);
            scAddNew.Parameters.AddWithValue("@Text", Text);
            scAddNew.Parameters.AddWithValue("@NewsCategoryNo", NewsCategoryNo);
            return Id = DBLayer.ExecuteScalar(scAddNew).ToInt32();
        }
        public void Update()
        {
            SqlCommand scUpdate = new SqlCommand(_UpdateQuery);
            scUpdate.Parameters.AddWithValue("@Id", Id);
            scUpdate.Parameters.AddWithValue("@Title", Title);
            scUpdate.Parameters.AddWithValue("@Description", Description);
            scUpdate.Parameters.AddWithValue("@Text", Text);
            scUpdate.Parameters.AddWithValue("@NewsCategoryNo", NewsCategoryNo);
            DBLayer.ExecuteNonQuery(scUpdate);
        }
        public void Delete()
        {
            SqlCommand scDelete = new SqlCommand(_DeleteQuery);
            scDelete.Parameters.AddWithValue("@Id", Id);
            DBLayer.ExecuteNonQuery(scDelete);
        }

        public NewsCategory NewsCategoryNoReference
        {
            get
            {
                return new NewsCategory(NewsCategoryNo);
            }
        }

    }
    public partial class NewsManager
    {
        public static List<News> GetList(string pQuery)
        {
            SqlCommand pCommand = new SqlCommand(pQuery);
            return GetList(pCommand);
        }
        public static List<News> GetList(SqlCommand pCommand)
        {
            List<News> list = new List<News>();
            DataTable dtNews = DBLayer.ExecuteDataTable(pCommand);
            foreach (DataRow dr in dtNews.Rows)
            {
                News record = new News();
                record.Id = dr["Id"].ToInt32();
                record.Title = dr["Title"].ToString();
                record.Description = dr["Description"].ToString();
                record.Text = dr["Text"].ToString();
                record.NewsCategoryNo = dr["NewsCategoryNo"].ToInt32();
                list.Add(record);
            }
            return list;
        }
        public static List<News> Liste()
        {
            return GetList(new SqlCommand("SELECT * FROM News"));
        }

        public static News GetSingle(string pQuery)
        {
            SqlCommand pCommand = new SqlCommand(pQuery);
            return GetSingle(pCommand);
        }
        public static News GetSingle(SqlCommand pCommand)
        {
            DataRow drRecord = DBLayer.ExecuteDataRow(pCommand);
            if (drRecord != null)
            {
                News record = new News();
                record.Id = drRecord["Id"].ToInt32();
                record.Title = drRecord["Title"].ToString();
                record.Description = drRecord["Description"].ToString();
                record.Text = drRecord["Text"].ToString();
                record.NewsCategoryNo = drRecord["NewsCategoryNo"].ToInt32();
                return record;
            }
            else
            {
                return null;
            }
        }

        public static DataTable GetDataTable()
        {
            return DBLayer.ExecuteDataTable("SELECT * FROM News");
        }

        public static int AddNewNews(News pNews)
        {
            if (pNews != null)
            {
                News pObject = new News(pNews.Id);
                return pObject.AddNew();
            }
            return 0;
        }

        public static void UpdateNews(News pNews)
        {
            if (pNews != null)
            {
                pNews.Update();
            }
        }

        public static void DeleteNews(News pNews)
        {
            if (pNews != null)
            {
                pNews.Delete();
            }
        }

    }
}

Notes
DBLayer and type converter (contains extension methods) classes included in source code and binary

Onur "Silmaril" SELAMET

Last edited Aug 20, 2010 at 3:37 AM by Silmaril_, version 11