HomeActive Query BuilderHow to remove the particular field from the table in ActiveQueryBuilder

How to remove the particular field from the table in ActiveQueryBuilder


Hi

How to remove the particular field from the table in ActiveQueryBuilder
Srinivasan M | email
March 12 2009, 05:11 AM
Each object in the Metadata container has Fields collection. You may add or remove items with it.
Sergey Smagin | email
42 mins since original post
Can u give me a example?. bcoz i am using the following line

int i =queryBuilder.MetadataContainer.FindObjectByName("AccountCategory", "dbo", msSqlProvider.Connection.Database).Fields.Count;

it is returning the count 0. but the table having the 3 fields

Srinivasan M | email
1 hour, 33 mins since original post
Sorry, I forgot to mention that you should call the LoadFields method of metadata object before getting access to the Fields collection.
Sergey Smagin | email
1 hour, 50 mins since original post
Thanks Smagin,

I have another one doubt.
If i assign the query manualy to querybuilder (ex.querybuilder.SQL = "Select * from Emp") it is assigned.
but this querybuilder should not allow the Update,Insert and Delet (if i assign like this querybuilder.SQL = "Delete from Emp", it should not allow. now it is allow this query). How to restrict the INSERT,UPDATE and DELETE?
Srinivasan M | email
4 hours, 13 mins since original post
A query builder does not "allow" the other types of statements, but switches to the Sleep mode to let the users to type these statements manually if needed. Reading from the QueryBuilder.SQL property you can get nothing but valid SELECT statements.

You may set the QueryBuilder.AllowSleepMode property to False to disable Sleep mode and raise an error in case of typing other types of statements.
Sergey Smagin | email
6 hours, 38 mins since original post
Hi

I am using SQL Server 2005, which syntax provider we can use (need best one)?


Now i am tested with Universal syntax provider. but
Select Top 10 from tablename query also not supported by this universal syntax provider. Let me know the best one for Sql server (should be support for all the version for Sql server)?
Srinivasan M | email
1 day, 1 hour since original post
You should use MSSQLSyntaxProvider to work with MS SQL Server. It available in the Standard and the Trial versions of Active Query Builder. You may request the trial at http://www.activequerybuilder.com/download.html
Sergey Smagin | email
1 day, 7 hours since original post
Hi

I have created the following query in QueryBUilder

"Select AccountID,AccountName,AccountCategory From Account INNER JOIN AccountCategory ON Account.AccountID = AccountCategory.AccountID"

Now i need fieldlist,tabllist from this query.

for ex.

Selected Table
=============
Account
AccountCategory


Selected Field
==============
AccountID
AccountName
AccountCategory

How we can get like this field and table list ?. Kindly let me know details.
Srinivasan M | email
5 days, 5 hours since original post
Use the QueryBuilder.QueryStatistics.UsedDatabaseObjects, QueryBuilder.QueryStatistics.UsedDatabaseObjectColumns and QueryBuilder.OutputColumns collections to retrieve this information.

The OutputColumns gives you the list of columns of result dataset. There you may find single fields among with complex expressions (if any). The UsedDatabaseObjectColumns gives you the list of single fields that were used in the query (not only in the select list, but anywhere in the query). If you want to get only those fields that were used for output, check the Select property of each UsedDatabaseObjectColumns item.

The code example is given in the Non-visual sample project.
Sergey Smagin | email
5 days, 10 hours since original post
Hi

I have used the following query

Select Account.AccountID,Account.AccountName, AccountRole.AccoutRoleID
From Account, AccountRole

I need to get how many fields (count(outputcolumn)) i have used from Account table in the query.
Let me know your thoughts ?
Srinivasan M | email
1 week since original post
Below is the modified QueryStatistics procedure that solves your problem. You may replace it with the following and see how it works.

public void QueryStatistics()
{
// these two lines are necessary only if you didn't load metadata from database
qb.OfflineMode = true;
qb.MetadataContainer.AddTable("", "Account", false, "");

try
{
// Use 'SyncSQL' property instead of the 'SQL' one.
// The SQL property is asynchronous and works only in visual mode
qb.SyncSQL = textBox1.Text;
}
catch (System.Exception e)
{
MessageBox.Show(e.Message, "Parsing error");
return;
}

int cnt;
string stats = "";

QueryStatistics qs = qb.QueryStatistics;

cnt = 0;
for (int i = 0; i < qs.UsedDatabaseObjectColumns.Count; i++)
{
if (qs.UsedDatabaseObjectColumns[i].ObjectName.Token == "Account")
{
stats += "\r\n" + qs.UsedDatabaseObjectColumns[i].FullName.QualifiedName;
if (qs.UsedDatabaseObjectColumns[i].Selected)
{
stats += " - Selected for output";
}
cnt++;
}
}
stats += "\r\n" + "Total Used Columns from Account table (" + cnt + ").\r\n\r\n";

cnt = 0;
for (int i = 0; i < qs.OutputColumns.Count; i++)
{
if ((qs.OutputColumns[i].MetadataObject != null) &&
(qs.OutputColumns[i].MetadataObject.Name.Token == "Account"))
{
stats += "\r\n" + qs.OutputColumns[i].Expression;
cnt++;
}
}
stats += "\r\n" + "Total Output Columns taken from Account table (" + cnt + ").";

using (QueryStatisticsForm f = new QueryStatisticsForm())
{
f.textBox.Text = stats;
f.ShowDialog();
}
}
Sergey Smagin | email
1 week, 1 day since original post
Hi

I tries your code, but i unable to find the "MetadataObject" for this following line.

qs.OutputColumns[i].MetadataObject.Name.Token == "Account"

Kindly let me know your thoughts.
Srinivasan M | email
1 week, 3 days since original post
Please download the newest version. This property was added a couple weeks ago.
Sergey Smagin | email
1 week, 3 days since original post
Hi,
I am having the Account table and this fields are AccountId,AccountName.

If i select * from Account in querybuilder, i have to get query from querybuilder like select AccountId,AccountName from Account.

any thoughts...
Srinivasan M | email
3 weeks, 6 days since original post
This can't be done automatically. You should modify your query programmatically. Have a look at the Non-visual demo to find examples of SQL query analysis and modification.
Sergey Smagin | email
3 weeks, 6 days since original post
This topic is closed