Hi ,
In first piece of code I get the tables that are searchable than for each one i need to pass those table as parameters
in the second query and get the values any ideas about how this can be done ?
Regards
ArrayList values = new ArrayList();
SqlConnection connection = Connection.GetDBConnection();
SqlCommand cmd = new SqlCommand("select * from searchabletable ", connection);
SqlDataAdapter da = new SqlDataAdapter(cmd);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
values.Add(reader[0].ToString());
}
foreach (Object objReader in values)
{//this variable gets the search tables
string aux = objReader.ToString();
SqlCommand command = new SqlCommand("select FIELD from @Tabel ", connection);
SqlDataAdapter d = new SqlDataAdapter(command);
SqlDataReader rd= cmd.ExecuteReader();
}
Certainly, I am not able to make my self clear about WHAT you want to search for ?
If you meant, a value in any table, I will vote for doing the same way using the stored procedure as described in
this post (http://forums.asp.net/post/5219462.aspx) ...
Or If, you meant, you want to search a column for each table in database , plase refer to this query
USE AdventureWorks
GO
SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%EmployeeID%'
ORDER BY schema_name, table_name;
Or If, you meant to say, you want to search for a table itself in the database, use this simple query:
using Northwind
SELECT *
FROM sys.Tables
WHERE name LIKE '%Customer%'
Mojorz
Member
3 Points
22 Posts
Search for each searchable table
Dec 07, 2012 06:13 PM|LINK
Hi ,
In first piece of code I get the tables that are searchable than for each one i need to pass those table as parameters
in the second query and get the values any ideas about how this can be done ?
Regards
ArrayList values = new ArrayList(); SqlConnection connection = Connection.GetDBConnection(); SqlCommand cmd = new SqlCommand("select * from searchabletable ", connection); SqlDataAdapter da = new SqlDataAdapter(cmd); SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { values.Add(reader[0].ToString()); } foreach (Object objReader in values) {//this variable gets the search tables string aux = objReader.ToString(); SqlCommand command = new SqlCommand("select FIELD from @Tabel ", connection); SqlDataAdapter d = new SqlDataAdapter(command); SqlDataReader rd= cmd.ExecuteReader(); }RichardY
Star
8376 Points
1573 Posts
Re: Search for each searchable table
Dec 07, 2012 07:45 PM|LINK
You must use dynamic sql.
http://www.sqlteam.com/article/introduction-to-dynamic-sql-part-1
aarsh
Participant
1543 Points
427 Posts
Re: Search for each searchable table
Dec 10, 2012 02:27 AM|LINK
Certainly, I am not able to make my self clear about WHAT you want to search for ?
If you meant, a value in any table, I will vote for doing the same way using the stored procedure as described in this post (http://forums.asp.net/post/5219462.aspx) ...
Or If, you meant, you want to search a column for each table in database , plase refer to this query
Or If, you meant to say, you want to search for a table itself in the database, use this simple query:
I hope, this helps ...
vacuum
Member
3 Points
8 Posts
Re: Search for each searchable table
Dec 13, 2012 06:56 AM|LINK
SqlCommand command = new SqlCommand("select FIELD from "+aux, connection);