Click here to Skip to main content
Click here to Skip to main content

SQL Builder for Microsoft SQL Server

, 24 Apr 2007
Rate this:
Please Sign up or sign in to vote.
This is a SQL Query Builder which is could be used for further purposes. This was developed under C#.NET. This application will allow us to design a query. It will check any relationship of the tables so there's a little possibility generating query from unrelated tables
Screenshot - Tab1.jpg

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;
}

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Arek Suroboyo
Web Developer
Indonesia Indonesia
No Biography provided

Comments and Discussions

 
GeneralMy vote of 1 PinmemberVolrick1-Apr-10 3:54 
GeneralLooks interesting PinmemberBritney_Spears8-Mar-09 23:11 
GeneralVS2003 Pinmemberpradomillan22-Nov-07 6:18 
GeneralA word of advice: Never query system tables directly. Pinmembersonny-bob1-May-07 12:36 
GeneralRe: A word of advice: Never query system tables directly. PinmemberArek Suroboyo1-May-07 20:04 
GeneralA good addition would be to add filters PinmemberKevin Jensen30-Apr-07 20:28 
GeneralCode style PinmemberCode dependent26-Apr-07 20:13 
GeneralRe: Code style PinmemberArek Suroboyo30-Apr-07 17:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.141220.1 | Last Updated 25 Apr 2007
Article Copyright 2007 by Arek Suroboyo
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid