Project DescriptionGenerates 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 databaseRoadmapSample Database Scheme
Generating Classes
OutputNewsCategory.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();
}
}
}
}
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();
}
}
}
}
DBLayer and type converter (contains extension methods) classes included in source code and binary
Onur "Silmaril" SELAMET