Using SQL Variables 1

[jcs1953]

I'm having a real brain fart on this. I know how to use variables for column values in an sql statement in ASP.Net 2.0 but how do I word the function if the table name is the variable?

Public Shared Function GetSQLTQResults(ByVal strtablename As String) As DataTable

Dim strCon As String = GetConnectionString("FMC_ACCESSConnectionString")
Dim conn As New SqlConnection(strCon)
Dim cmdSelect As New SqlCommand("SELECT Convert(varchar(10),tablename.Date,101) As Date,tablename.Result As Result,tablename.Test As Test FROM tablename Where tablename = @tablename ", conn)
cmdSelect.Parameters.Add("@tablename", SqlDbType.NVarChar, 20).Value = strtablename
Dim dtrTable As New DataTable
conn.Open()
dtrTable.Load(cmdSelect.ExecuteReader(CommandBehavior.CloseConnection))
Return dtrTable
End Function

??

 

[jmeckley]

you can't. for that you build the sql by hand

var sql = "SELECT [put fields here] FROM " + strtablename +" the_alias;"

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jmeckley]

in this scenario though a variable table name doesn't make sense.
1. you columns are static but the table name is dynamic. this means you need to manage how the user selects the table and manage the schema of the table is correct. too much "you just know" knowledge
2. you are not gaining anything by making the tablename dynamic. other than complexity.

I would approach this differently.
1. create a common Model

class Model
{
   public DateTime Date {get;set;}
   public string Result {get;set;}
   public string Test {get;set;}
}

2. create a strategies to retrieve the model

interface IStrategy
{
   IEnumerable<Model> GetData();
}

class TableAStrategy : IStrategy
{
   public IEnumerable<Model> GetModels()
   {
      DataTable table = GetRowsFromDatabase();
      foreach(DataRow row in table.Rows)
      {
          yield return new Model
                 {
                    Date = (DateTime) row["..."];
                    Result = (string) row["..."]
                    Test = (string) row["..."]
                 }
      }
   }
}

class TableBStrategy : IStrategy
{
   public IEnumerable<Model> GetModels()
   {
        yield return new Model{...};
        yield return new Model{...};
        yield return new Model{...};
   }
}

etc....

3. select the appropriate strategy and display data

class MyPage : Page
{
   private readonly IStrategy[] strategies = new IStrategy[]
          {
               new TableAModelStrategy(),
               new TableBModelStrategy(),
          };
}

public void Some_Event_Handler(...)
{
      int index = GetIndexOfStrategyToUse();
      AControl.DataSource = strategies[index].GetData();
      DataBind();
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[jcs1953]

Wow! Complex answer to what I thought was a simple to answer question (although not so simple I could answer it).
Here's my senerio. I have to pull data fro an Oracle Database (read only) and an sql database and combine the results. I used table.load() to combine the results into one table. The worked on my developement servers (databases on different servers) but on the production servers I kept getting "constraints" errors. I found that if I used bulkcopy to copy the data from each query (2 queries each 1 for Oracle and 1 for SQL) to a table in SQL that I no longer had that problem. The data combined just fine. I have 5 tables (tempQuery-tempQuery4). The origional function returns the needed data from tempQuery table only but I thought that if I could set the tablename as a variable I could use just one Function as all the queries to the tempTables are the same.
Oh Well. Thanks taking the time to try and help me out.

 

[jmeckley]

Complex != Complicated. Complexity can be encapsulated through segregation and validated with automated testing. Complicated means it's prone to error and difficult to test.
case in point: breaking each query into a separate operation. you can then test each command independent from the reset of the system.

what kind of "constraint" errors where you getting? solving this problem may be easier then a series of temp tables.
I also don't understand why you would have tables tempQuery[1-4]? if they really are temporary tables why not use true temporary table

create table #foo
(
  id long,
  field varchar(50),
  ...
)
select into #foo  select ...
drop table #foo

I'm sure Oracle has something similar.
I would also consider a different approach to you're connection mangement. no error handling and connection per query is very inefficient.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[kss444]

Not sure if this is what you are looking for but here you can pass a table name to a stored proc.

declare @tableName varchar(50)
declare @strSQL varchar(3000)
select @strSQL = "SELECT * FROM " + @tablename
exec(strSQL)


Ordinary Programmer