SQL Builder for Microsoft SQL Server

• Download SQLBuilder_1.zip - 386.8 KB

Introduction

This SQL Builder is developed to build a SQL query statement which will be used for further usage. This tool is still in Beta Version and runs only on Databases which are managed in Microsoft SQL Server.

Background

I have searched and compare many things SQL Builder on the web. But I couldn't find a suitable one. Usually the problem is on the relation between tables included on the project. When we want to create a query, it has to be checked the relationship and it has to be easier so a common user could use this tool without limited SQL syntax knowledge.

Using the code

There are two main files on this project. The first thing is it's form and the last is a query builder class. There is also a simple documentation of this project. The description below is very simple description. You can directly using this class or reading di documentation file including on zip file.

Let's begin with a query builder class.

There are some important classes. Before we generate a query we have to define our connection string and also generate all table on this database. Here is the code to collect all table.

public DataTable GetListOfDbcTables()

{
if (this.SQLConnect == null)
{
this.SQLConnect = this.CreateSqlConnection();
}
SqlDataAdapter adapter1 = (SqlDataAdapter)this.CreateDataAdapter("SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' " +
"AND TABLE_NAME NOT IN ('dtproperties') AND TABLE_NAME NOT IN ('Sysdiagrams') ORDER BY TABLE_TYPE", this.SQLConnect);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

public DataTable GetListOfTableFields(string tableName)

{

this.CurrentTableName = tableName;
this.GetTableSchema = "SELECT COLUMN_NAME,DATA_TYPE,isnull(CHARACTER_MAXIMUM_LENGTH,0) AS 'LENGTH',IS_NULLABLE, " +
"ISNULL((SELECT 'Y' FROM SYSFOREIGNKEYS WHERE FKEYID =ID AND FKEY=COLID),'N') as 'IsForeignKey'," +
"Ordinal_Position, SYSCOLUMNS.IsComputed, IsIdentity, IsRowGuidCol " +
"FROM SYSCOLUMNS, " +
"(SELECT COLUMN_NAME, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, Ordinal_Position, " +
"COLUMNPROPERTY(OBJECT_ID('" + tableName + "'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsIdentity') AS IsIdentity, " +
"COLUMNPROPERTY(OBJECT_ID('" + tableName + "'), INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, 'IsRowGuidCol') AS IsRowGuidCol " +
"FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='" + tableName + "') AS A " +
"WHERE ID IN (SELECT ID FROM SYSOBJECTS WHERE TYPE='U' AND NAME ='" + tableName + "') AND A.COLUMN_NAME =NAME " +
"Order By Ordinal_Position";
if (this.SQLConnect == null)
{
this.SQLConnect = this.CreateSqlConnection();
}
SqlDataAdapter adapter1 = (SqlDataAdapter)this.CreateDataAdapter(this.GetTableSchema, this.SQLConnect);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
table1.DefaultView.Sort = "COLUMN_NAME ASC";
return table1;
}

/// <summary>
/// Get All Parent from each of Foreign Key
/// </summary>
/// <param name="TableName"></param>
/// <returns></returns>

public DataTable GetParentForeignKey(string TableName)

{
this.currentTableName = TableName;
this.DefineForeignKey = "Select b.Name As TableName, c.column_name As ForeignKeyField, " +
"(Select Name from SysObjects Where ID = a.rkeyid) As ForeignTable," +
"(Select Column_Name from INFORMATION_SCHEMA.COLUMNS Where Table_Name IN " +
"(Select Name from SysObjects Where ID = a.rkeyid) " +
"AND Ordinal_Position = a.rkey) As ForeignField " +
"FROM Sysforeignkeys a, sysobjects b, information_schema.columns c " +
"Where a.fkeyid = b.id AND b.name ='" + this.currentTableName + "' AND a.fkey = c.ordinal_position AND " +
"c.Table_Name = '" + this.currentTableName + "'";

if (this.SQLConnect == null)
{
this.SQLConnect = this.CreateSqlConnection();
}
SqlDataAdapter adapter1 = (SqlDataAdapter)this.CreateDataAdapter(this.DefineForeignKey, this.SQLConnect);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

public DataTable GetHostedForeignKey(string TableName)

{
this.currentTableName = TableName;
this.DefineForeignKey = "Select b.Name As TableName, c.column_name As ForeignKeyField, " +
"(Select Name from SysObjects Where ID = a.fkeyid) As ForeignTable," +
"(Select Column_Name from INFORMATION_SCHEMA.COLUMNS Where Table_Name IN " +
"(Select Name from SysObjects Where ID = a.fkeyid) " +
"AND Ordinal_Position = a.fkey) As ForeignField " +
"FROM Sysforeignkeys a, sysobjects b, information_schema.columns c " +
"Where a.rkeyid = b.id AND b.name ='" + this.currentTableName + "' AND a.rkey = c.ordinal_position AND " +
"c.Table_Name = '" + this.currentTableName + "'";
if (this.SQLConnect == null)
{
this.SQLConnect = this.CreateSqlConnection();
}

SqlDataAdapter adapter1 = (SqlDataAdapter)this.CreateDataAdapter(this.DefineForeignKey, this.SQLConnect);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

/// <summary>
/// Get Parent from a foreign key
/// </summary>
/// <param name="TableName"></param>
/// <param name="FKFieldName"></param>
/// <returns></returns>

public DataTable GetParentForeignKey(string TableName, string FKFieldName)
{
this.currentTableName = TableName;
this.DefineForeignKey = "Select b.Name As TableName, c.column_name As ForeignKeyField, " +
"(Select Name from SysObjects Where ID = a.rkeyid) As ForeignTable," +
"(Select Column_Name from INFORMATION_SCHEMA.COLUMNS Where Table_Name IN " +
"(Select Name from SysObjects Where ID = a.rkeyid) " +
"AND Ordinal_Position = a.rkey) As ForeignField " +
"FROM Sysforeignkeys a, sysobjects b, information_schema.columns c " +
"Where a.fkeyid = b.id AND b.name ='" + this.currentTableName + "' AND a.fkey = c.ordinal_position AND " +
"c.Table_Name = '" + this.currentTableName + "' AND c.Column_Name = '" + FKFieldName + "'";
if (this.SQLConnect == null)
{
this.SQLConnect = this.CreateSqlConnection();
}
SqlDataAdapter adapter1 = (SqlDataAdapter)this.CreateDataAdapter(this.DefineForeignKey, this.SQLConnect);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}

/// <summary>
/// Get foreign key relation from two tables
/// </summary>
/// <param name="TableName1"></param>
/// <param name="TableName2"></param>
/// <returns></returns>

public DataTable GetForeignKeyRelation(string TableName1, string TableName2)
{
this.currentTableName = TableName1;
this.DefineForeignKey = "Select b.Name As TableName, c.column_name As ForeignKeyField, " +
"(Select Name from SysObjects Where ID = a.rkeyid) As ForeignTable, " +
"(Select Column_Name from INFORMATION_SCHEMA.COLUMNS Where Table_Name IN " +
"(Select Name from SysObjects Where ID = a.rkeyid) " +
"AND Ordinal_Position = a.rkey) As ForeignField " +
"FROM Sysforeignkeys a, sysobjects b, information_schema.columns c " +
"Where a.fkeyid = b.id AND b.name ='"+TableName1+"' AND a.fkey = c.ordinal_position AND " +
"c.Table_Name = '" + TableName1 + "' AND a.rkeyid IN (Select ID from sysobjects where name = '" + TableName2 + "') ";
if (this.SQLConnect == null)
{
this.SQLConnect = this.CreateSqlConnection();
}
SqlDataAdapter adapter1 = (SqlDataAdapter)this.CreateDataAdapter(this.DefineForeignKey, this.SQLConnect);
DataTable table1 = new DataTable();
adapter1.Fill(table1);
return table1;
}