Dynamic SQL

G

gfard

Member
Joined
Apr 9, 2003
Messages
11
I want to have Dynamic query in my program that I develop it by vb.net and sql server 2000.
I want to make a sql query when my fields and my tables are different from case to other case, but I dont know how can I do it.
if anybody knows about solution of this problem, please tell me.
 
dynamic query

You could use CommandBuilder because it automatically generate the DeleteCommand, InsertCommand, and UpdateCommand of the DataAdapter.
As a minimum requirement, you must set the SelectCommand property in order for automatic command generation to work.
Also CommandBuilder have some retrictions and disadvatages like:


i) If you have many records CommandBuilder is very slow

ii) Automatic command generation logic fails if column names or table names contain any special characters, such as spaces, periods, quotation marks, or other nonalphanumeric characters, even if delimited by brackets. Fully qualified table names in the form of schema.owner.table are supported.

iii) The automatic command generation logic generates INSERT, UPDATE, or DELETE statements for standalone tables without taking into account any relationships to other tables at the data source.


iv) your datatable must have PRIMARY KEY
 
thanx for your recommendations,

my data base include over 10000000 records, and I want to have select command with where cluse, as I understand, so I cant use automation builder, however my system will be slow.

now I want to try a way like:


declare @vSQL varchar(1000), @TableName varchar(25)
select @TableName = @TableNameInput
select @vSQL = select * from + @TableName + Order by DateColumn
Execute (@vSQL)


but first of all I must test this method. what is your Idea my friend about this method?
 
Last edited by a moderator:
thanx for your recommendations,

my data base include over 10000000 records, and I want to have select command with where cluse, as I understand, so I cant use automation builder, however my system will be slow.

now I want to try a way like:


declare @vSQL varchar(1000), @TableName varchar(25)
select @TableName = @TableNameInput
select @vSQL = select * from + @TableName + Order by DateColumn
Execute (@vSQL)


but first of all I must test this method. what is your Idea my friend about this method?
 
Back
Top