Dynamic table name and fields

I need to get the field values of a table (name will be dynamic).
Then assign those values to properties in a class.

Let's say I will get the table name dynamically.

dim tblName as string = "tablea"

The 2 tables can each have 25 fields or so.

I need a way to select the amt and email field values from tblName.
Without saying "select job_amt, job_email from ...

Is there someway to get the values based on the column name.
So if the column name has amt and email, then give me those values.

Maybe loop through the datatable - then for each column --
if col.ColumnName.IndexOf("Amt") = 0 or col.ColumnName.IndexOf("email") = 0 then
then drop that column from the datatable.

ex of table structure

<u>tablea</u>
job_id
job_amt
job_email

<u>tableb</u>
dance_id
amt_dance
dance_email

I recommend you to use reflection to solve this task. You can create a simple object relational mapper to map automatically the table columns on class properties. Basic idea you can see at following code snippet (your concrete class will inherit from BusinessObject class):

public abstract class BusinessObject{...protected bool Select(string storedProcedureName,params SqlParameter[] parameters){bool status =false;Type objType = GetType();using (SqlConnection conn =new SqlConnection(ConnectionString.GetConnectionString())){using (SqlCommand cmd =new SqlCommand(storedProcedureName, conn)){cmd.CommandType = CommandType.StoredProcedure;cmd.Parameters.AddRange(parameters);cmd.Parameters.Add("@.ReturnValue", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;conn.Open();using (SqlDataReader reader = cmd.ExecuteReader()){if (reader.Read()){int count = reader.FieldCount;for (int i = 0; i < count; i++){if (reader.IsDBNull(i))continue;PropertyInfo property = objType.GetProperty(reader.GetName(i));if (property !=null) property.SetValue(this, reader.GetValue(i),null);}status =true;}}returnValue = Convert.ToInt32(cmd.Parameters["@.ReturnValue"].Value);conn.Close();}}return status;}...}

|||Don't I need to create a class for this first. What if I want to do this without creating a class.|||OK, if you can make sure there is only one table which contains?all?given?columns,?try?such?stored?procedure:

CREATE PROC sp_GetDataForCols
AS
BEGIN
DECLARE @.tblName sysname

SELECT @.tblName=OBJECT_NAME(c.id) FROM syscolumns c join sysobjects o
on o.id=c.id
WHERE c.name='OrderID'
AND o.type='U'
AND EXISTS(SELECT 1 FROM syscolumns i
WHERE c.id=i.id AND i.name='CustomerID')
EXEC('SELECT OrderID,CustomerID FROM '+@.tblName)
END

You can modify the stored procedure to add a varchar parameter for inputting column names delimited by some char(s), which can be broken down into individual column name. Then use the column names to build dynamic statement to find the table name. Well, seems not so easy? Yeah it's always not so easy to do some "strange" thing with T-SQL. BTW, the performance of executing dynamic T-SQL is not so good.?